Waiting time and snapshot fact
4 posters
Page 1 of 1
Waiting time and snapshot fact
Being a newbie in the BI world I am facing some difficulties on choosing a design for a fact that will help answering questions like:
Average process- or waiting-time in any given time period.
I have read Kimballgroup tips 37, 42 and 130 but don't quite fit our challenge into those suggestions.
We have data for housing offers which holds three important dates:
a) application date
b) offer date (customer is given an offer)
c) delivery date (move in)
d) end date (move out)
Duration from a) to b) is processing time, From a) to c) is waiting time
The delivery date may be recorded before the actual date, in which case it's an expected delivery date.
At any given time (also retrospectively) we want to know what the waiting time is. If c) has not occurred then the waiting time so far.
(grouped by still waiting/finished waiting)
So it seems that a simple accumulated snapshot won't do the trick as there is only one record per application, not giving us the ability to see the accumulated duration retrospectively. (eg. how were we doing in jan, feb ...)
I guess that I need a row every day, but for how long ?
And the facts ? a simple 0/1 for each day for process time and likewise for waiting time?
But if I query for a period starting after application date, how can I make things sum up right?
Please point me in the right direction
Thank you
Average process- or waiting-time in any given time period.
I have read Kimballgroup tips 37, 42 and 130 but don't quite fit our challenge into those suggestions.
We have data for housing offers which holds three important dates:
a) application date
b) offer date (customer is given an offer)
c) delivery date (move in)
d) end date (move out)
Duration from a) to b) is processing time, From a) to c) is waiting time
The delivery date may be recorded before the actual date, in which case it's an expected delivery date.
At any given time (also retrospectively) we want to know what the waiting time is. If c) has not occurred then the waiting time so far.
(grouped by still waiting/finished waiting)
So it seems that a simple accumulated snapshot won't do the trick as there is only one record per application, not giving us the ability to see the accumulated duration retrospectively. (eg. how were we doing in jan, feb ...)
I guess that I need a row every day, but for how long ?
And the facts ? a simple 0/1 for each day for process time and likewise for waiting time?
But if I query for a period starting after application date, how can I make things sum up right?
Please point me in the right direction
Thank you
Lykkestjerne- Posts : 2
Join date : 2012-02-28
Location : Denmark
Re: Waiting time and snapshot fact
Why not simply store the duration of each stage as measures in the fact?
Re: Waiting time and snapshot fact
I'm not following why an accumulating snapshot won't work. If the grain of my fact table is the application, I can easily add all the events and measure accordingly.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Waiting time and snapshot fact
I guess that I need a row every day, but for how long ?
That was the question I was addressing. You only want to touch a fact table when something happens in the business. The Sun setting is usually not considered a business event.
Re: Waiting time and snapshot fact
Everyday nothing happens, the waiting time increases.
We wish to see how many are still waiting and how long thwy have waited so far and not wait for the delivery date before we calculate.
We wish to see how many are still waiting and how long thwy have waited so far and not wait for the delivery date before we calculate.
Lykkestjerne- Posts : 2
Join date : 2012-02-28
Location : Denmark
Re: Waiting time and snapshot fact
Calculating the difference between now and an earlier date doesn't require you to update the fact table on a daily basis.
Assuming you have a date dimension (you should), one of the attributes could be a day sequence. This is populated by counting of the days in chronological sequence from the beginning of the dimension table. If you need business days, you can do the same thing by only counting days defined as business days (use the value of the last business day prior to non-business days for those non-business days). Then it is a simple matter of subtracting the sequence value for the start date from the sequence value for the current date. Business days are usually indicated by putting a flag in the dimension table and it is set based on rules.
Assuming you have a date dimension (you should), one of the attributes could be a day sequence. This is populated by counting of the days in chronological sequence from the beginning of the dimension table. If you need business days, you can do the same thing by only counting days defined as business days (use the value of the last business day prior to non-business days for those non-business days). Then it is a simple matter of subtracting the sequence value for the start date from the sequence value for the current date. Business days are usually indicated by putting a flag in the dimension table and it is set based on rules.
Re: Waiting time and snapshot fact
the columns that you mentioned in your first post, cant you take that as factless fact table ??
Vishy- Posts : 75
Join date : 2012-02-28
Location : India
Similar topics
» Time in fact or dimension? Accumulating snapshot
» Point of time information from accumulating snapshot.
» Type 2 Dimension with accumulating snapshot with time stamp
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Transaction Fact or periodic snapshot fact
» Point of time information from accumulating snapshot.
» Type 2 Dimension with accumulating snapshot with time stamp
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Transaction Fact or periodic snapshot fact
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum