Search code examples
data-warehousedimensional-modeling

Track multiple status in Transaction Fact Table

I have to track the status of my business process for analysis purpose. I have seen a post where it is mentioned that we can keep the status in Transaction Fact Table against time/transaction type/service center and we can use the Accumulated fact table to study the process lag, I am wondering if few transactions have multiple status in a single day should I store all the status in Transaction Fact Table? Here I am assuming that my ETL is done at end of the business day.

Secondly should i keep all my key dimensions keys into Transaction Fact Table. Keys in this case are Transaction Type, Department id, Service_type, Service_id, Submission Channel or should I divide them in multiple fact tables?

Third if I need to report which department is meeting its SLA what would be the best approach, Calculate and keep track of Within SLA and Not Within SLA in Transaction Fact Table or I should compute this value at run time?

Thanks in advance for your help and assistance.

Solution

  • For status tracking you should have:

    1. A transaction table where ony events show up (but does not provide event tracing)

    2. An accumulating snapshot table where each process's status are tracked/updated as they happen.

    As for the keys, you should keep as much detail as possible. No need to delete keys if they may hold valuable information in the future.