January 12, 2011 at 9:06 am
I am building an SSAS Cube for the first time and I have a basic question regarding the time dimension.
My fact table contains about 30 days of 15-minute data updated every 15 minutes. Also, each day the fact table will have all rows that are older than 30 days deleted.
Being new to SSAS Cubes, it is unclear to me how to properly build the time dimension for this fact table. Should I build a "static" time dimension will all possible dates/times say for a year (past and future) and just manually update the time dimension periodically with new future dates and times as the need arises? Or should I create some sort of "dynamic" time dimension that is constantly updated (with SSIS or sp) as the fact table is updated with new rows with new dates and times?
Thanks in advance!
January 13, 2011 at 8:22 am
hi Aaron,
What i did was to use the DimTime in AdventureWorks as a template and just added extra dates to it and then
it is always there as a static time table. I also added 4 extra columns to it to give it more meaningful
month and day columns.
on this i created a calendarhierarchy : Calendar Year->YearMonthDesc -> YearMonthDayDesc and worked a treat ( Calendar Year was already existing in DimTime)
YearMonthID YearMonthDesc YearMonthDayID YearMonthDayDesc
200107Jul 2001 2001070101 Jul 2001
200107Jul 2001 2001070202 Jul 2001
200107Jul 2001 2001070303 Jul 2001
200107Jul 2001 2001070404 Jul 2001
200107Jul 2001 2001070505 Jul 2001
200107Jul 2001 2001070606 Jul 2001
200107Jul 2001 2001070707 Jul 2001
200107Jul 2001 2001070808 Jul 2001
200107Jul 2001 2001070909 Jul 2001
i'm not saying it is right ...but worked for me
January 13, 2011 at 8:41 am
Thank you for the response! I had been looking at the AdventureWorks DimDate table as a guide. So if I have this correct, you use a "static" time table that you do not modify all that often.
I have a couple more questions if you don't mind:
1) Since my fact table will be incrementally updated I have a need to add future DateTimes to my DimTime dimension. How many "future" rows would you add to the DimTime table?
2) How does the cube handle dimension rows that do not have a corresponding row in the fact table? Are they ignored when the cube is processed?
3) Within the AdventureWorks DImDate table, a DateKey field is used that is of the Integer data type. Is this standard practice? If so, how would you represent a datetime as an integer when the datetime field is at minute level granularity?
Thanks again!
January 13, 2011 at 4:39 pm
hi aaron,
My experience is only down to using dates to the day level granularity and having a single date key column in my fact table.
From what i have read it is advisable to a have seperate time table that would have hour min and sec
and you would create a time hiearchy from it of hour->min->sec. Having a single table down to the second would be massive.
therefore in yr fact table you would 2 foreign keys one for dateID from yr Date table and and timeid
from yr time table. And the combination of these would then be unique.
I trawled the web to find out examples of this to see what date calculations would look like.
but did not find any. Perhaps somebody out there has some examples of how YTD and MTD functions pan out with seperate date and time hierachies in the same fact table and how date ranges work
January 18, 2011 at 7:04 am
aaron,
I had to do something similar in a previous project but it was very much recording departure times of vehicles.
In the end I used a Time Table that had 1440 rows (60m*24h) and was static.
If you need to go down to seconds then there are 86400 rows required.
The down side is that you need another column all be it a smallint data type.
It theres a protion of user training involved, as they need to understand that to analyse by time, you need to add on an extra dimension.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 18, 2011 at 6:53 pm
Greetings Aaron,
You may want to separate Date and Time as two dim tables, not one. You also may have two AS dims, one for Date and one for Time. Prepopulate both with the static sets and virtually never change them. A cube may care about Date only while another one is more concerned with Time or both. The design I am describing covers for all cases.
Cheers,
M.R.
January 19, 2011 at 3:09 am
You definitely want to seperate the date and the time dimensions. As you only seem to need 15 minute increments, you could do the time that way, but you may later need the minutes for something else. I have a time dimension that uses minutes, but I have one cube that only needs the quarter hour. For that one I created a view that only has the quarter hour entries and created another time dimension based on that view. It works very well.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply