April 12, 2012 at 4:55 pm
I have been trying to get the best dimensional model for a scenario below:
Measure: Revenue
Dimension: Dim_Date, Dim_Time
As we can see, Date and Time are separate dimensions (which is what business wants)
Also, there is another requirement to identify a Peak and Off Peak Period
These are also a date and time range e.g. From 01/07/2010 00:00:00 to 31/12/2010 23:59:00 is defined as Peak Period and the rest is defined is Off-Peak Period
Is it best to create another dimension called Peak_OffPeak or use the existing Date and Time from the existing Dim_Date and Dim_Time dimension. If so, what is the best way to achieve this?
Hereare some options I can think of
Option 1: I can create a separate Dimension Table (is it worth it?)
Option 2: I can create a De-Generate Dimension (Peak and Off-Peak values) in the fact table, but this still has to look up a reference table to determine the peaks
Is there any better way of achieving this?
April 12, 2012 at 6:22 pm
It looks like you're basically after the 2nd half of the year as peak period.
Is the time component important? I.e. if it's not critical that peak period ends 1 minute before the new year then you time dimension is basically irrelevant.
In this case I'd simply be including a peak/offpeak flag in your date dimension. The values can be derived based on the dates and it gives you the flexibility of varying your peak period over time. It does limit you to whole day changes though.
I don't think there's a need for a new dimension or degenerates. If the peak/offpeak status of an event is integral to the nature of the transaction then there could be a case for modelling it differently, but I would assume that the date is more pertinent and that dimension should control whether a date is viewed as on or offpeak.
HTH,
Sam
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply