Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  creating a student data warehouse

Author  Topic  MrFreeze Starting Member 1 Post Posted - 2006-02-07 : 13:51:25 Hi all! I apologize that my first post here is a question for help, but I'm in a bit of a bind with the timeframe of the project (though aren't we all! =) My data is supplied to me as follows: Term UID Class Ethnicity Residence Sex Campus Credits Level Coll Dept Crs CrsNum -------------------------------------------------------------------------------- 200508 454545 Junior White Non-Hispanic Res M Campus 1 3 UG EP Elementary Education Tching LEP Students K-12 4317 200508 454545 Junior White Non-Hispanic Non-Res M Campus 2 3 UG EP Elementary Education Literature in Childhood Ed 4414 200508 145680 Post Bachelors White Non-Hispanic Res F Campus 1 3 UG BP Accounting Auditing II 4642 200508 956743 Non-Degree White Non-Hispanic Non-Res F Campus 1 3 ND EP Administration/Supervision School Finance 6242 Basically, there is one entry for every course that every student takes during the semester (UID is the student's unique ID, similar to an SSN). I need to run a number of reports from this data, which in its current format as described above contains approx 6,000 rows. Though I've successfully run the reports via pivot tables and then exported the results to MS SQL, I will probably need to run upwards of 30-40 of these a few times a year which will be a real PITA to do with Excel's pivot tables and my kludgy SQL. For an example of the reports I've generated (and what I'll need to generate), take a look at [url]http://www.ire.usf.edu/SDP/gender.asp[/url] and [url]http://www.ire.usf.edu/SDP/ethnicity.asp[/url]. The reports can be "drilled down" through college and into department. Although the drop-down select boxes on the above examples do not work, you can see the types of reports I'll need to create. The question then becomes how: Do I normalize the data up front by creating new tables in a batch process, then figure out how to do pivot tables via SQL (which I know I'll have a hard time doing)? ...or do I do an over-night batch process 3-4 times a year that will generate the reports from the format in which the data is supplied to me as described above? I appreciate all input...although I've been doing web devel for a few years, database design and SQL has been on the backburner in my projects until recently. Thanks, Travis