Fact Table design Decision

View previous topic View next topic Go down

Fact Table design Decision

Post  grahan007 on Tue Mar 18, 2014 9:45 am

Hi

I am in stage of deciding which type of fact table I should choose with following scenario:

I have a workflow table where users can register a contract cancellation request. This request goes through different stages i.e. approved, realized and processed. The work flow table is updated on any change happens to that request. There is no transaction table to record theses changes. A request can change the status even with in a day. Now I am little confused which type of fact table I should use.

There are two options in my mind:

1. To explode the request in multiple lines for each status as I do have date and time when the status was changed. IN this way I can generate the transaction fact table so I will be able to report on each status. Use accumulating fact table for current status reporting.

2. Forget about the change in status and use accumulating fact table to report the current situation.

Can you please give me your recommendations and suggestion to handle it to its best. Pros and Cons too.

Regards

Harris


grahan007

Posts : 18
Join date : 2009-05-26

View user profile

Back to top Go down

Re: Fact Table design Decision

Post  ngalemmo on Tue Mar 18, 2014 9:50 am

Both are valid options. One has history, the other does not. What does the business want?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact Table design Decision

Post  grahan007 on Tue Mar 18, 2014 9:56 am

Currently business wants to see only the churn amounts for the status approved on wards. But in future they might be interested in lags between approved and processed especially while analyzing the sales people target incentives.

grahan007

Posts : 18
Join date : 2009-05-26

View user profile

Back to top Go down

Re: Fact Table design Decision

Post  ngalemmo on Tue Mar 18, 2014 12:02 pm

So, go with the history or do both.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact Table design Decision

Post  BoxesAndLines on Tue Mar 18, 2014 12:28 pm

In order to do the accumulating snapshot, you will find that you also need the transaction fact as a source. So do both fact tables, transaction fact first.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Fact Table design Decision

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