Fact Table design Decision

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

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

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.

So, go with the history or do both.

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.