Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Design a snapshot fact table in a DM Expand / Collapse
Posted Wednesday, March 19, 2014 2:22 AM



Group: General Forum Members
Last Login: Friday, November 4, 2016 4:42 AM
Points: 165, Visits: 654
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
Post #1552488
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse