Modeling time dimension

View previous topic View next topic Go down

Modeling time dimension

Post  dianaantova on Mon May 11, 2009 3:05 pm

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

dianaantova

Posts : 9
Join date : 2009-05-05

View user profile

Back to top Go down

Re: Modeling time dimension

Post  VHF on Mon May 11, 2009 5:51 pm

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).

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Modeling time dimension

Post  dianaantova on Mon May 11, 2009 6:29 pm

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.

dianaantova

Posts : 9
Join date : 2009-05-05

View user profile

Back to top Go down

Re: Modeling time dimension

Post  BoxesAndLines on Tue May 12, 2009 9:41 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Modeling time dimension

Post  VHF on Tue May 12, 2009 10:33 am

For purposes of discussion, how about a single date (time) dimension with attributes for each of the relevant quarters?

DateKeyDateQuarterPass1Pass2
1001/19/2009Fall 2008Summer 2009Spring 2009
1011/20/2009Spring 2009Fall 2009Summer 2009
1021/21/2009Spring 2009Fall 2009Summer 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

View user profile

Back to top Go down

Re: Modeling time dimension

Post  VHF on Tue May 12, 2009 10:38 am

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

View user profile

Back to top Go down

Re: Modeling time dimension

Post  dianaantova on Tue May 12, 2009 12:32 pm

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

View user profile

Back to top Go down

Re: Modeling time dimension

Post  VHF on Tue May 12, 2009 2:57 pm

Maybe you need a "quarter-step" dimension table!

QuarterStepKeyQuarterStepQuarterStep
101Spring 2009 Pass1 Spring 2009Pass1
102Spring 2009 Pass2Spring 2009Pass2
103Spring 2009 ActualSpring 2009Actual
104Summer 2009 Pass1Summer 2009Pass1

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

View user profile

Back to top Go down

Re: Modeling time dimension

Post  dianaantova on Tue May 12, 2009 4:31 pm

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?


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

View user profile

Back to top Go down

Re: Modeling time dimension

Post  tropically on Tue May 12, 2009 4:46 pm

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

View user profile

Back to top Go down

Re: Modeling time dimension

Post  dianaantova on Tue May 12, 2009 4:51 pm

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

View user profile

Back to top Go down

Re: Modeling time dimension

Post  tropically on Tue May 12, 2009 5:56 pm

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

View user profile

Back to top Go down

Re: Modeling time dimension

Post  VHF on Tue May 12, 2009 6:50 pm

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.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Modeling time dimension

Post  dianaantova on Wed May 13, 2009 2:00 pm

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

View user profile

Back to top Go down

Date Dimension suggestion

Post  Jeff Smith on Thu May 14, 2009 11:11 am

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

View user profile

Back to top Go down

Re: Modeling time dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum