Daily Snapshots of all facts and Dimensions
2 posters
Page 1 of 1
Daily Snapshots of all facts and Dimensions
Hi All
I am pretty new to the dimensional modeling. I just went to a Ralph Kimball class last month. So please forgive m if my questions are too silly.
I am trying to build a star for our Student Enrollment data. I have a couple of fact tables here
The grain for each of them is
1) One row per student per class per term (the students add date, drop date, grade for the class, credits attempted, credits earned etc. also the student key, department key, class key, Date(term key).)
2) One row per student per term per academic career (student key, Date(term key), Student Academic levelkey, Term GPA, Term total credits, fulltime/parttime status etc..)
3) One row per Student Academic career/degree (student key, CGPA, Total credits, degree status date, degree confer dateCGPA, completion term )
I have dimensions for student, date, Academic level, department, plan, class, course, meeting pattern, facility.
There is a one to many relationship between student and plan and a class and its meeting pattern & facility
So I have factless facts tables for maintaining their relationships
1) Student Plan Fact (Student Key, plan key, department key, primary plan indicator)
2) Class Meeting pattern, facility fact (Class Key, Meeting pattern key, Facility Key)
Now, a student can add/drop into a class multiple times during the term. A student can keep changing plans through out his time at the university. The grades can be changed any time..Lets say from an incomplete to a D or a C. And they want to keep all the history and also be able to keep year to year trends.
Eventually we will have a class-instructor factless fact too which can keep changing through out the term.
We are thinking of a daily/weekly snapshot of all the fact tables and a daily/weekly/monthly snapshots of Dimensions. This might eliminate the need of having too many effective dates, end dates and current flags and I am thinking it will simplify the ETL process too as there wont be a need for SCD2.
We are a school with less than 20000 students. We are thinking of storing daily snapshots for may be 3 years and then roll them off and keep only a couple every term, like the first day of the term, last day and some day in between.
Do you see any problems with our approach here? Do I need to keep the natural key of the dimensions or the natural surrogate keys of the dimensions in my fact tables? Or is it enough to just have one surrogate key for each dimension in the Fact table for comparisons of current , point of time etc..?
Any help and advice is appreciated
Thanks,
Jahnavi
I am pretty new to the dimensional modeling. I just went to a Ralph Kimball class last month. So please forgive m if my questions are too silly.
I am trying to build a star for our Student Enrollment data. I have a couple of fact tables here
The grain for each of them is
1) One row per student per class per term (the students add date, drop date, grade for the class, credits attempted, credits earned etc. also the student key, department key, class key, Date(term key).)
2) One row per student per term per academic career (student key, Date(term key), Student Academic levelkey, Term GPA, Term total credits, fulltime/parttime status etc..)
3) One row per Student Academic career/degree (student key, CGPA, Total credits, degree status date, degree confer dateCGPA, completion term )
I have dimensions for student, date, Academic level, department, plan, class, course, meeting pattern, facility.
There is a one to many relationship between student and plan and a class and its meeting pattern & facility
So I have factless facts tables for maintaining their relationships
1) Student Plan Fact (Student Key, plan key, department key, primary plan indicator)
2) Class Meeting pattern, facility fact (Class Key, Meeting pattern key, Facility Key)
Now, a student can add/drop into a class multiple times during the term. A student can keep changing plans through out his time at the university. The grades can be changed any time..Lets say from an incomplete to a D or a C. And they want to keep all the history and also be able to keep year to year trends.
Eventually we will have a class-instructor factless fact too which can keep changing through out the term.
We are thinking of a daily/weekly snapshot of all the fact tables and a daily/weekly/monthly snapshots of Dimensions. This might eliminate the need of having too many effective dates, end dates and current flags and I am thinking it will simplify the ETL process too as there wont be a need for SCD2.
We are a school with less than 20000 students. We are thinking of storing daily snapshots for may be 3 years and then roll them off and keep only a couple every term, like the first day of the term, last day and some day in between.
Do you see any problems with our approach here? Do I need to keep the natural key of the dimensions or the natural surrogate keys of the dimensions in my fact tables? Or is it enough to just have one surrogate key for each dimension in the Fact table for comparisons of current , point of time etc..?
Any help and advice is appreciated
Thanks,
Jahnavi
jjahnavi- Posts : 1
Join date : 2012-04-25
Re: Daily Snapshots of all facts and Dimensions
I don't see why you would go straight to snapshots. It's better to design to the finest grain, then go for snapshots when process times become unreasonable.
TheNJDevil- Posts : 68
Join date : 2011-03-01
Similar topics
» Daily Snapshots Best Practices
» Snapshots and Date Dimensions
» snapshot facts: daily versus effective from & to
» No of Dimensions and Facts
» Static facts in dimensions?
» Snapshots and Date Dimensions
» snapshot facts: daily versus effective from & to
» No of Dimensions and Facts
» Static facts in dimensions?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum