×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Accumulated Fact design when status can go either direction

Accumulated Fact design when status can go either direction

Accumulated Fact design when status can go either direction

(OP)
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.

RE: Accumulated Fact design when status can go either direction

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

Ties Blom
 
 

RE: Accumulated Fact design when status can go either direction

(OP)
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.
 

RE: Accumulated Fact design when status can go either direction

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

RE: Accumulated Fact design when status can go either direction

(OP)
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.
 

RE: Accumulated Fact design when status can go either direction

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
 
 

RE: Accumulated Fact design when status can go either direction

(OP)
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


 

RE: Accumulated Fact design when status can go either direction

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
 
 

RE: Accumulated Fact design when status can go either direction

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

RE: Accumulated Fact design when status can go either direction

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close