Waiting time and snapshot fact

View previous topic View next topic Go down

Waiting time and snapshot fact

Post  Lykkestjerne on Tue Feb 28, 2012 12:15 pm

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


avatar
Lykkestjerne

Posts : 2
Join date : 2012-02-28
Location : Denmark

View user profile

Back to top Go down

Re: Waiting time and snapshot fact

Post  ngalemmo on Tue Feb 28, 2012 12:34 pm

Why not simply store the duration of each stage as measures in the fact?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Waiting time and snapshot fact

Post  BoxesAndLines on Tue Feb 28, 2012 1:37 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Waiting time and snapshot fact

Post  ngalemmo on Tue Feb 28, 2012 3:00 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Waiting time and snapshot fact

Post  Lykkestjerne on Tue Feb 28, 2012 6:33 pm

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.
avatar
Lykkestjerne

Posts : 2
Join date : 2012-02-28
Location : Denmark

View user profile

Back to top Go down

Re: Waiting time and snapshot fact

Post  ngalemmo on Tue Feb 28, 2012 7:05 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Waiting time and snapshot fact

Post  Vishy on Wed Feb 29, 2012 2:50 am

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

View user profile

Back to top Go down

Re: Waiting time and snapshot fact

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum