Time Dimension Problem

[mogy]

Hi

I have created a time_dimension containing keys for the day,week and month. But when i link the table to a usage table by the week_key it brings back 7 records per line as the week_key is there 7 time because of the day_key. Is there anyway I can get around this ???

Appreciate the help

Thanks,
mogy

 

[avjoshi]

The grain of fact table doesn’t relate to the base grain of the time dimension. In a similar situation I had to create another dimension for the higher grain.
In your case you may consider creating ‘week_time’ dimension which has a base grain of week, apart from your time dimension whose base grain is day.


Anand

 

[mogy]

Yeh that is the other option. I wanted to keep the time dimension together if at all possible.

Thanks for the reply

 

[johnherman]

Perhaps you can use a DISTINCT clause in the join. From a theoretical point of view, this should be OK since the week info should be the same across each day for that week, so it doesn't matter which day of that week you choose.

Or instead of DISTINCT, you might want to use MAX(day_key) for the week in question, which will bring you the last day of that week, and similarly for MIN and the first day of the week. Again, this assumes that the week info is the same - which it should be from the denormalization process.

Another (Microsoft) option is SELECT TOP 1 which will return one row for that week - there's no telling which day it is, but, again, that shouldn't matter.


-------------------------
John Herman is available for short and long term data warehousing consulting and contracts.

 

[TJRTech]

You say: "when i link the table to a usage table by the week_key"

I think that is your problem right there.

You can create a TIME dimension table with several time part attributes (WeekNo, DayOfWeekNo, DayOfMonthNo, etc).

But, at a certain point, one and only ONE of the attributes of the TIME table has the lowest granularity and it is that attribute that is used as a FK in your fact table.

Maybe it is day, or maybe it is hour within a day, or maybe it is a 15 minute period, but you have to pick one and use it.

For one of our systems we had a time dimension table called: TIME_PERIOD_BY_QUARTER_HOUR. And, its PK were two datetime columns: TIME_PERIOD_START and TIME_PERIOD_END.

And these two columns had values like:

"01/01/2004 00:00:00:00", "01/01/2004 00:14:59:59",
"01/01/2004 00:15:00:00", "01/01/2004 00:29:59:59",

In this way, the TIME dimension is statically populated and we indexed the CRAP out of the attributes.

Good luck,
TR