Fact table design help RRS feed

  • Question

  • I have a DW that has 5 source systems and I am trying to figure out the best way to build out the current solution to a change in business requirements.  Currently the DW contains incident information that has multiple injury/illness record associated with that incident.  There is an incident fact table and an injury/illness fact table with multiple dimensions off each fact table.  Although it doesn’t happen often the injury/illness can be reclassified at a later time (move from life threatening injury to non life threatening).  The way the system handles this now is that each day the ETL drops and reloads the facts tables. 

    Now the business user wants to see the current status of the injury illness as well as the previous values of the injury/illness as well as run a march report that showed the injury/illness at life threatening but if they ran it for September and the value changed it would show the new value.

    What is the best way to build this into my DW to accommodate these reclassifications of data to meet this business need?  I also need the reports to run out of the cube that sits on top the DW?



    Monday, September 20, 2010 10:51 PM

All replies