I'm struggling with the design phase for a data warehouse. It's not the traditional sales example you find on MSDN or all books etc. I've been tasked with creating an education data warehouse, that tracks student progress over time. There is nothing that quite matches it in Kimball's books either. This is what I've come up with so far. I'm following best practices i.e. Creating primary identity keys on each table and including the business key which I may attempt to make an unique key depending on the quality of data (whether referential integrity can be kept)
DimTime or date
Different format dates, quarters etc. etc.
DimTeacher or tutor
(Obviously there is many more fields in the dimension tables, but just for simplicity)
Student and Subject both have the same business key. Now I'm really struggling with the fact table. The exam grades are stored in the Subject business table for some reason. The ETL I've created splits the grades from the subject table so DimSubject just contains subjects and FactGrades just contains Grades with foreign keys from other tables. The problem I'm having is the relationships and whether or not I need to create multiple fact tables. There are other grades they want analysing that are stored in different places for example i.e. Predicted grades, or admission exam grade. So should I create a fact table for each one, or try and merge them all into one fact table? Also if the relationships are defined by the business key, should I not be creating foreign key relationships between the business keys and not the surrogate keys? So in short.
- Do I define relationships by newly created keys or business keys.
- Do I create multiple fact tables for each type of grade if they are in different parts of the business database or put them all in one table.
- Can I link one dimension to multiple fact tables or does this cause duplication issues.
- How do I define relationship to date dimension, should this relationship be between the fact table and what do I join on? I have an exam start date and end date but then I will have different dates for other grade types.