Modeling time dimension
5 posters
Page 1 of 1
Modeling time dimension
I am in the process of building a student data warehouse and our data is based on quarters. Each quarter has a lot of dates associated with it. Some of these dates can overlap - like one date can be pass one for one quarter and pass two for another quarter. Users will need to look at data snapshots based on quarter dates. Initially I am thinking to have a quarter dimension and a time dimension and in the time dimension next to each date to specify what it is for which quarter. But with this overlap of dates this designation has to be flexible. How do I model the time dimension to accommodate this date overlap?
Thank you,
Diana
Thank you,
Diana
dianaantova- Posts : 9
Join date : 2009-05-05
Re: Modeling time dimension
Will your fact records be at 'quarter' or 'date' granularity? Or both?
You could have a 'quarter' dimension that contains FKs into a 'date' dimension for each relevant date. (PassOneDateKey, PassTwoDateKey, QuarterStartDateKey, QuarterEndDateKey, etc.) That would make the 'date' dimesion an outrigger to the 'quarter' dimension (a roll-playing outrigger dimension, no less!)
You would need to get a feel for what typical user queries would look like with this design... the joins required to use a outrigger table might make using the DW too complex.
Would a simpler design with just a 'quarter' dimension work? It would have a database datetime field for each relveant date (PassOneDate, etc.) and optionally attributes related to each date (PassOneDateCalendarYear, PassOneDateCalendarMonth, PassOneDateCalendarDay).
You could have a 'quarter' dimension that contains FKs into a 'date' dimension for each relevant date. (PassOneDateKey, PassTwoDateKey, QuarterStartDateKey, QuarterEndDateKey, etc.) That would make the 'date' dimesion an outrigger to the 'quarter' dimension (a roll-playing outrigger dimension, no less!)
You would need to get a feel for what typical user queries would look like with this design... the joins required to use a outrigger table might make using the DW too complex.
Would a simpler design with just a 'quarter' dimension work? It would have a database datetime field for each relveant date (PassOneDate, etc.) and optionally attributes related to each date (PassOneDateCalendarYear, PassOneDateCalendarMonth, PassOneDateCalendarDay).
VHF- Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US
Re: Modeling time dimension
The fact table will have snapshots of quarter records for each student. So each snapshot will have a date that the snapshot was taken and also each record will have a quarter for which the snapshot is. Users will need to select snapshots for running reports or to compare data between snapshots. So it is important if the date that the snapshot was taken is pass1 for Spring 2009 or pass2 for Winter 2009.
If we have just the quarter dimension with the dates in date fields it will be very difficult to tell for each snapshot date what it is exactly.
Would it work if I have a quarter dimension, a time dimension that has an entry for each date and also another dimension that has entries of what this date is only if I took a snapshot on that date?
So it would look like this:
Time
(1, 1/20/2009)
(2, 1/21/2009)
Time specification
(1, Pass1, Fall 2009)
(1, Begin Quarter, Spring 2009)
(2, Pass2, Summer 2009)
Then the fact table will have a pointer to the quarter dimension and to the time dimension.
If we have just the quarter dimension with the dates in date fields it will be very difficult to tell for each snapshot date what it is exactly.
Would it work if I have a quarter dimension, a time dimension that has an entry for each date and also another dimension that has entries of what this date is only if I took a snapshot on that date?
So it would look like this:
Time
(1, 1/20/2009)
(2, 1/21/2009)
Time specification
(1, Pass1, Fall 2009)
(1, Begin Quarter, Spring 2009)
(2, Pass2, Summer 2009)
Then the fact table will have a pointer to the quarter dimension and to the time dimension.
dianaantova- Posts : 9
Join date : 2009-05-05
Re: Modeling time dimension
A quarter is normally defined as a period of time, not just a single date. Since you are not interested in the series of dates that comprise a quarter, build a separate quarter dimension.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Modeling time dimension
For purposes of discussion, how about a single date (time) dimension with attributes for each of the relevant quarters?
Not sure if this sample data is an acurate example or not. As long as there is never more than one quarter at a time in a state of Pass1 or Pass2 this design would work. Users would be able to query student records by Quarter or by Pass1 or by Pass2.
DateKey | Date | Quarter | Pass1 | Pass2 |
100 | 1/19/2009 | Fall 2008 | Summer 2009 | Spring 2009 |
101 | 1/20/2009 | Spring 2009 | Fall 2009 | Summer 2009 |
102 | 1/21/2009 | Spring 2009 | Fall 2009 | Summer 2009 |
Not sure if this sample data is an acurate example or not. As long as there is never more than one quarter at a time in a state of Pass1 or Pass2 this design would work. Users would be able to query student records by Quarter or by Pass1 or by Pass2.
VHF- Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US
Re: Modeling time dimension
Will the student records going into the fact table designate what quarter and step they are for (ex: Fall 2009 Pass1, Summer 2009 Pass2) or will they just have a date?
VHF- Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US
Re: Modeling time dimension
Yes, they will. All records will have a quarter (there will be a separate quarter dimension with all relevant dates). Also all records will have a date when the snapshot was extracted. And for this date we need to know if it is Pass1 for Quarter1 or Pass2 for Quarter2, etc. I could designate inside the fact table as part of the snapshot "Pass1 Fall09" but as it could meet multiple criteria I think that it should be outside of the fact table and just the date key to be in the fact table.
dianaantova- Posts : 9
Join date : 2009-05-05
Re: Modeling time dimension
Maybe you need a "quarter-step" dimension table!
Additional attrributes as needed, included possibly FKs into the date dimension as an outrigger. Fact records would have the appropriate FK.
(Perhaps there is better terminology than "step" to describe each stage that an acedemic quarter goes through.)
QuarterStepKey | QuarterStep | Quarter | Step | |
101 | Spring 2009 Pass1 | Spring 2009 | Pass1 | |
102 | Spring 2009 Pass2 | Spring 2009 | Pass2 | |
103 | Spring 2009 Actual | Spring 2009 | Actual | |
104 | Summer 2009 Pass1 | Summer 2009 | Pass1 |
Additional attrributes as needed, included possibly FKs into the date dimension as an outrigger. Fact records would have the appropriate FK.
(Perhaps there is better terminology than "step" to describe each stage that an acedemic quarter goes through.)
VHF- Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US
Re: Modeling time dimension
This looks like a good option. Thanks for your help.
There is a complexity coming from the fact that one date can be multiple things for the same quarter - like Fee deadline and 3rd week. And we want to know both events. So would it work if in the fact table I have the key to the Time and Quarter dimensions and then in the Quarter Step dimension I have a key to the time dimension. Then I would join the fact table to the quarter steps with the time key and the quarter key?
There is a complexity coming from the fact that one date can be multiple things for the same quarter - like Fee deadline and 3rd week. And we want to know both events. So would it work if in the fact table I have the key to the Time and Quarter dimensions and then in the Quarter Step dimension I have a key to the time dimension. Then I would join the fact table to the quarter steps with the time key and the quarter key?
Last edited by dianaantova on Tue May 12, 2009 4:49 pm; edited 1 time in total
dianaantova- Posts : 9
Join date : 2009-05-05
Re: Modeling time dimension
I would worry if you were to create a quarter and a quarter step dimension. I don't see a need for both.
tropically- Posts : 13
Join date : 2009-05-12
Re: Modeling time dimension
The quarter dimension has a lot more dates that we might need for other pursposes (reporting). The quarter steps dimension is just to track the important dates in which we take snapshots. How would you suggest modeling it?
dianaantova- Posts : 9
Join date : 2009-05-05
Re: Modeling time dimension
It could be possible to add the quarter step dimension attributes to the quarter dimension itself. I'm not sure what your reporting requirements are. My only thought is that creating too many dimensions could lead you to the centipede effect. And performance will be slower the more dimensions you join to your fact.
tropically- Posts : 13
Join date : 2009-05-12
Re: Modeling time dimension
OK, these dates are pretty complex! This could be challenging to model relationally let alone dimensionally!
Let's explore a different approach. Maybe the events dates that apply to an academic quarter need to be in a fact table rather than a dimension.
3 dimension tables: Quarter, Date, Occurrence
1 fact table: QuarterDateOccurrenceFact (QuarterKey, OccurrenceKey, DateKey)
The Occurrence dimension would list all the possible occurrences that could happen related to an academic quarter: Begin Pass1, Begin Pass2, Tuition Due, Fees Due, Begin Quarter, Begin Week 3, Begin Final Exams, End Quarter, Grades Due, etc., etc. The QuarterDateOccurance fact table would contain a row for each occurrence for each academic quarter. That should take care of all the overlapping dates and dates with multiple occurrences!
The student registration data (in its own fact table(s)) would have FKs to the Date and/or Quarter dimensions. You should be able to query student data constraining on any occurrence for any quarter, in effect a drill-across query using conformed dimensions. You do need to think about query performance with very large data sets (say >5M student fact records) as you are joining multiple fact tables in your query.
Let's explore a different approach. Maybe the events dates that apply to an academic quarter need to be in a fact table rather than a dimension.
3 dimension tables: Quarter, Date, Occurrence
1 fact table: QuarterDateOccurrenceFact (QuarterKey, OccurrenceKey, DateKey)
The Occurrence dimension would list all the possible occurrences that could happen related to an academic quarter: Begin Pass1, Begin Pass2, Tuition Due, Fees Due, Begin Quarter, Begin Week 3, Begin Final Exams, End Quarter, Grades Due, etc., etc. The QuarterDateOccurance fact table would contain a row for each occurrence for each academic quarter. That should take care of all the overlapping dates and dates with multiple occurrences!
The student registration data (in its own fact table(s)) would have FKs to the Date and/or Quarter dimensions. You should be able to query student data constraining on any occurrence for any quarter, in effect a drill-across query using conformed dimensions. You do need to think about query performance with very large data sets (say >5M student fact records) as you are joining multiple fact tables in your query.
VHF- Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US
Re: Modeling time dimension
Thanks for your help. I think that I will have to setup a test environment and play with it to see what will be better and faster for the reporting.
dianaantova- Posts : 9
Join date : 2009-05-05
Date Dimension suggestion
Just a suggestion - My date dimension has everything you can think of when it comes to dates - the Calendar Date, the Name of the Day, the abbreviation of the day, the day number of the week, the day of the month, the day of the year. It also has the Year and Month, the Year and Quarter, and the Year. Each level - Date, Year/Month, Year/Quarter, has it's own surrogate key. This facilitates creating aggregates based on Dates. I create views of each level by selecting distinct and use the views to join to the Year Month or Year Quarter.
Jeff Smith- Posts : 471
Join date : 2009-02-03
![-](https://2img.net/i/empty.gif)
» Combined Date Time dimension vs. Separate Day and Time dimensions and timezones
» Time dimension design for cross time zone and custom calendar warehouse, 1min granularity
» Time Dimension, NULLs and Time datatype
» Modeling goals which may slowly change over time
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Time dimension design for cross time zone and custom calendar warehouse, 1min granularity
» Time Dimension, NULLs and Time datatype
» Modeling goals which may slowly change over time
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|