Design a snapshot fact table in a DM

• Paul Hernández

  SSCarpal Tunnel

  Points: 4961

  More actions

  March 19, 2014 at 2:22 am

  #305432

  Hi folks,

  I have maybe a basic question for some of you.

  Background: We have a highly normalized Enterprise data warehouse with fact tables that use row versioning (compliance-enabled), that means, the fact tables have validfrom and validto timestamps like a type 2 SCD.

  I want to design a fact table in the DM to aggregate a denormalized some data, in order to simplify the cube design and report creation but I'm little bit lost about how to aggregate these facts with the valid periods.

  Let's say I have orders with different status (open, close, delayed, canceled, etc.) and the users want to determine how many articles are in orders with a given status at a given date or the sum of the net sales of canceled order in a given date.

  I know I can leave the orders table like in the Enterprise data warehouse and then try to fulfill the analysis requirement with MDX but I also know that is better to have the proper design at the data base level.

  Any comment would be appreciated,

  Kind Regards,

  Paul Hernández