Star Schemas and Date/Time Fields

[jem122974]

I have several tables in MS SQL that are keyed by a datetime field that contains both a date and a time and then a bunch of quantity fields. I thought these would be easy to model to a fact table with a date dimension. But since my date dim only contains dates, no times, the join never finds any matches. So according to "The Data Warehouse Lifecycle Toolkit" by Ralph Kimball on page 245 I need to split the datetime field into a date field and a time field. Then the join to the date dim will work just fine. Well, I thought that sound easy enough.

Problem 1 - Cognos does not support the convert(char(10), datetime field, 101) syntax to pull out just the date nor the similar convert(char(10), datetime field, 108) syntax to pull out just the time. To get around this I had to create a view in MS SQL with these calculated fields. Then my join to the date dim worked great.

Problem 2 - What do I do with the time field? Should it be part of the fact table as the number of seconds as Kimball suggests? Or should it become a separate time dim? My users are going to want to see things like the total quantity per hour.

So basically I'm looking to see how others have handled modeling a datetime field. Did you have to do something outside of Cognos? What did you do with the new time field?

Thanks,
Jon Mitchell

 

[blom0344]

problem1:

These problems are often solved through using a surrogate key for the date (a lookup in the ETL phase to a real date and then use the surrogate key in the fact table)
Your time dimension needs to have this surrogate key as well of course.

You CAN use specific SQL function in the framework, but only if you wrap them in an inline view in the query subject:

SELECT TEMP.A,TEMP.B
FROM
(SELECT ............. AS A,
................ AS B FROM
................) TEMP

problem2:

Storing the time as seconds after midnight is a possible solution. It is an efficient way of storing the time component and is great for efficient calculations for durations (dividing 2 integers by resp. 60,3600, 86.400 for establishing minutes_diff,hours_diff,days_diff)




Ties Blom

 

[jem122974]

Problem 1 - In this case my users want to query live data so I can't use an ETL tool. I need to just model this in FM into a star schema. Currently I'm using the function in a MS SQL view and then importing this view into FM instead of the original table. Is there a better way, short of creating a little data mart?

Problem 2 - Currently I have the time portion as part of my fact table. It is stored as the seconds from midnight. I don't think this will be to helpful to my report authors. I don't think they will know what to do with it. Should I create a time of day dimension?

 

[blom0344]

You need to ask yourselve what the benefit would be of creating an additional time of day dimension?

In similar situations I simply created the needed calculations IN the framework for report authors to work with.
If you create it there, then you can take into account such niceties as calculations over several days (To me the only sensible place to define them, since the expressions needed are quite extensive)


Ties Blom