April 17, 2008 at 4:40 am
I'm trying to put together my first SSAS cube. I have successfully developed a Time Dimension for dates and I'm not sure where to go with the next dimension.
We are doing a lot of reporting on a specific measure, which is held in minutes and fractions of minutes. The source column is defined as numeric (18,2). At the detail level the fractions should remain, but the categories that currently dimension the measure are 5 Weeks, or negative. These are the focus for my report, but there may be other categories.
Based on my limited understanding of cube design, and an obsessive need to do things properly, it feels as if I should have a dimension with a row for every minute. The size of this dimension could then be practically infinite. At present, the minimum no of minutes is 0 and the maximum is 1,037,633.
Would it be valid to build the fact table with the original count of minutes and a second column dividing it into hours? That way, the hour could be the key and I could maintain 2 years of hours in 17.5k rows, or 35k if I allow 2 years negative as well.
Is this valid design? Does anyone have a better idea? Can anyone point me in the direction of good theory on cube design?
TIA
B2B
April 17, 2008 at 6:18 am
You already have a date dimension.
So, your time dimension needs to only have hours and minutes - don't include date information. This makes the time dimension finite.
You have 24 hours in a day, so you need 1500 records:
0:00
through 24:59
This, in combination with the date dimension will give you date and time.
April 17, 2008 at 7:58 am
Thank you for the quick response.
My time dimension has 4 years worth of dates (1461 rows). The columns describe yyyy, yy,mmmm, mm, dddd, dd, weekday, weeknumber, and some other attributes of an actual date. I have an integer key, and the full date is the alternate key. I don't see where to add in the hours and minutes to this dimension without generating masses of rows.
Also, my minutes do not have any relation to date. If I have a fact of 300 minutes, it can have a start time in one day and and end time in the next. I need to categorise it for reporting. In this context it is not ' 5 weeks' and not 'negative'. It is in the category, '< 8 hours'.
On reflection, I don't think this can be a dimension at all. I will need to build in the category when I build the fact table. The fact table will then have a duration column, with a value of 300 mins, and a time category column, with a value of '< 8 hours'. I think it will work for all the columns I need. Does that sound OK?
April 17, 2008 at 8:25 am
For dates and times, it is usually best to create two dimensions:
Dates - just dates, not times
Time - I usually create one record per minute of the day
There are some advantages of doing this. One is the reduction of data not having to reproduce times for every day, but more importantly, it is sometimes important to compare "things that happened between 6 and 8 AM on any day".
With your other issue, you have a discretization something like:
< 1 hour
1 hour to 1 day
1 day to 1 week
1 week to 1 month
> 1 month
This is really a difference between two dates / times. These often slide (lengths of time someone has been a customer). I typically include this type of information as it's own dimension - possibly a dimension calculated from the fact data. As another option, you could do this with calculations.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply