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