February 7, 2008 at 8:43 pm
This might be a total newb question, but i have to ask - if only to save myself some time...
I'm building a dimensional model of a transaction set. The goal is to analyze the quantity of transactions on a day, duration of transactions, and so on.
I have a fact table that stores the duration of a transaction:
TrxID
TrxDuration
TrxDate_FK
TrxTime_FK
I have corresponding Date & Time dimension tables, and i'm using SQL Server 2005 Enterprise (Analysis Services) to create a cube with Trx Count, TrxDuration facts and the appropriate Date/Time dimensions.
The problem i'm running into is that i can't figure out how to bucket/group Trx counts into time spans.
I need to be able to count all transactions that have a duration that falls within a time span (such as 0 to 5 minutes, 5-20 minutes, 20-60 minutes, 60+, and so on).
one thought i had was creating a separate dimension to store my time spans:
TimeSpanID
TimeSpan
...and then creating an additional field at load time to link the TrxID to a TimeSpanID based on TrxDuration:
TrxID
TrxDuration
TrxDate_FK
TrxTime_FK
TimeSpanID_FK
This solves my problem, but seemingly only superficially. What happens if time spans change? Say, instead of measuring it in 0-5 min buckets i also need to measure in 0-15 min buckets? This would mean that a single TrxID would match at least two TimeSpanIds, which wouldn't work...
Is there a good way of handling this situation? Am i missing something obvious?
thanks!
February 8, 2008 at 6:24 am
First thing you need to so is look at your 'time' dimension table. What fields do you have in it?
February 8, 2008 at 7:27 am
My TimeDimension table has these fields:
TimeID
FullTime
Hour12
Hour24
Minute
Second
AMPMIndicator
I've taken a stab at creating a separate TimeSpanDimension table with the following fields:
TimeSpanID
TimeSpan1
TimeSpan2
TimeSpan3
The sample rowset might be:
TimeSpanID TimeSpan1 Timespan2 TimeSpan3
1 0-5 0-30 0-60
2 5-10 0-30 0-60
3 10-20 0-30 0-60
4 20-30 0-30 0-60
5 30-40 30-60 0-60
..and so on
While this does, at first glance, seem to address my problem, it also presents a minor challenge when trying to decide which TimeSpanID key to assign to a fact table row. I'm considering revising the TimeSpanDimension to use TimeSpanXLower and TimeSpanXUpper fields to capture the "0-5" range... This, obviously, would double the number of fields, and would have unknown (yet) effects on cube browsing experience.
So, after this diversion, i hope my answer to your original question about time dimension fields shows another path i can take...
thanks!
February 8, 2008 at 8:31 am
I'm thinking maybe something like
TimeSpanID
Timespan_Act
Timespan_1Min
Timespan_5Min
Timespan_15Min
Timespan_30Min
TimespanID|Timespan_Act|Timespan_1Min|Timespan_5Min|Timespan_15Min|Timespan_30min
1|00:00:01|00:01:00|00:05:00|00:15:00|00:30:00
2|00:00:02|00:01:00|00:05:00|00:15:00|00:30:00
3|00:00:03|00:01:00|00:05:00|00:15:00|00:30:00
...
90|00:01:30|00:02:00|00:05:00|00:15:00|00:30:00
91|00:01:31|00:02:00|00:05:00|00:15:00|00:30:00
...
301|00:05:01|00:06:00|00:10:00|00:15:00|00:30:00
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply