Accumulated Fact design when status can go either direction

[dmishra]

Hi
I have to design a fact table to find the duration for each state.

I have the following states available from source for a customer

draft
intial
open
review
purchase
close
cancel


So I have designed a fact table with following columns
customer_ik,draft_date_time_key, intial_date_time_key,open_date_time_key,review_date_time_key,
purchase_date_time_key,close_date_time_key,cancel_date_time_key

So that by getting the difference , it will give me differences.

But this above approach will work when the status moves forward.
But in my scenario, the status can move either direction from a status.

How can I resolve this type of scenario. Any idea or suggestions.

 

[blom0344]

You describe your fact table, but only the keys.. What measures and calculations do you use? Basically what is your difference calculation.

Ties Blom

 

[dmishra]

Hi
Since I am designing a Fact table and it will be factless fact table. Above after this fact I will create an View or MV over this fact table which will calculate the difference
and store the differences by hours for each status.
But not able to decide how will I design if status goes backward.
Also I am looking for a design approach to handle this scenario.

 

[johnherman]

I recommend differently. Have a customer table and a status table. The status table will track the movement of the customer across the statuses (statii?) across time.

Customer Table
Cust ID
other customer attributes

Status Table
Status ID
Status Description
other status attributes, if any

Customer Status Table
Cust ID
Status ID
Status Effective Date/Time

The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[dmishra]

Thanking you for your suggestion.

But if status changes from
1--> 2
2--> 3
3--> 1
1--> 3
1--> 2

we have
data like
customer_id status_id, eff_dt_tm Active_flag
1 1 20110603112325 N
1 2 20110604112325 N

1 3 20110605112325 N
1 1 20110606112325 N

1 3 20110607112325 N
1 2 20110608112325 y


Taking this example
how will calulcate the duration for status_id = 3


I need to get (Status_time_2 - status_time_3) + (Status_time_1 - status_time_3)

Which is (20110608112325 - 20110607112325) + (20110606112325 - 20110605112325)

What will be our SQL logic to achieve this.

 

[blom0344]

You could write a stored procedure that performs the trick by looping through the recordset or build a construction where table is autojoined to itself ('shifted by 1 record')

The trick would be to define a view on the table adding an indexfield (for instance by using rownumber() OLAP function) and a second view that is identical save for the index being 'out-of-phase' by value of 1. Joining these views over the index allows for calculating datetime differences at the row level.

We use this technique to establish startdates on phases where only enddates are stored in the DB

Ties Blom

 

[dmishra]

Hi
Thank you for your suggestion. I appreciate your time for this forum.

So basically I have to create two views one with sequence no for each row.
The other view will be a sequence + 1 from same table.

Then join the two views by customer id, sequence no and get the difference of effective date time for each row.

Is that it ?

Thanks and Regards

 

[blom0344]

If you use a sequencer for the entire table you will only have to join over this one. However, you need to line up the data properly (suppose sorting by customer/timestamp)

Ties Blom

 

[dmishra]

Thank you. This solution worked. Used Lead function to get the difference.

 

[johnherman]

For ease of calculation, you can also have a status start date/time and a status end date/time instead of a single status effective date/time. Although this is redundant data, that's one of the basic premises of data warehousing: Maximizing performance and usability at the cost of disk space.

The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright