MS SQL Server OLAP - Calculating selective time spans

Author

Calculating selective time spans

Greg Fodor

2005-09-27, 8:24 pm

Essentially the problem I'm looking for a solution to is this -- we
have a simple transaction based data warehouse I'm building that for
this simple example can just be considered to have one transactional
fact table that has an entry for an 'event' that took place, linked up
to various dimensions. For example, say I have an Event (with EventType
attribute), Object (with say ObjectState attribute), and Day/Time
dimensions. The fact table just stores a record for each time an event
occurs, and links it to the dimensions (which are SCD.) So at 9PM on
9/15, the object 832 was in state "Active" and has the "Open" event run
on it.

Well, there are a whole slew of queries that I'm trying to figure out
how to do but cannot seem to find addressed at all in any literature.
Essentially, they all deal with time differences. Some of the queries:

"What was the difference in time (days, minutes) between the first
event (any of them) and the last event (any of them)?"
"What was the difference in time between the first 'open' event and the
last 'close' event?"
"What was the difference in time between the first 'open' event and the
last 'close' or 'finish' event, whichever came first?"
"What was the time difference .. you get the idea .. but only including
business hours in the sum."
"What was the time difference .. you get the idea .. but only including
time as sliced along another dimension."

With this transactional data, what I'd like to be able to do, in the
most complex case, is say, "How long was it from the first time the
"Open" event was run on this object, to the last "Close" event,
excluding time that the object was in the "Inactive" state and only
aggregating business hours, etc." If you look at this article:
http://www. intelligententerpris by
Ralph Kimball he starts to talk about the beginnings of what I need,
but falls short of excluding slices of time from the time spans based
upon states of various dimensions. I can get fairly close by recording
a start _and_ and end date for each transaction (by revisiting the
previous record for that object) which will let me quickly see the time
span between events, but this selective exclusion of time is key for me
also.

My cubes are currently built in AS2005, not 2000, so any features
therein that could help are fair game. Thanks for any help you can give!

Darren Gosbell

2005-09-28, 3:24 am

Just a sugestion, but It might be easier to reverse the logic and
subtract non-business time rather than trying to exclude business time.

Could you add events for "Non-Business Hours", with a start and end
time.

If you could calculated the total non-business hours between 2 points in
time you could then subtract that from the total elapsed time for your
other object.

--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Sponsored Links