EDW contain normalized&denormalized dimension

[william-wang]

I am the DW team lead of my Company. We recently implement an EDW/BI system for a diversified company.
There are many bussiness system;first,there are many bussiness, so different bussiness used different business system; secondly, a bussiness for different subsidiary company
used different system.
Our system architecture is : ODS->EDW->Data Mart;
ODS contains all subsidiary company system data source;
Our goal is design an EDW meet the headquarters requirements and the subsidiary company requirements;
In order to meet the headquarters requirements, the model of EDW must be normalized;
but subsidiary company system has individualization dimension,for example:
A susidiay has the recall time dimension,
B susidiay has not the recall time dimension, but has the city dimension;
....
The normalized model not contain the two dimension,but the subsidiary company requirements need the denormalized dimension,
So in Data Mart has three star-model:
DM-1: for headquarters user;
DM-2: for A susidiay company user;(because DM-1 unable to meet their needs)
DM-3: for B susidiay company user;

Now ,the normalized model in EDW , it's can meet the headquarters requirements and part of susidiay company requirements:
Order-MAIN;(25 normalized dimension)
Order-ITEM;(15 normalized dimension,5 measure)
Refund;(20 normalized dimension,3 measure)
For the 10 susidiay company,the average denormalized dimension of each subsidiary company about 15 ,
My question is how to design the EDW model both meet the headquarters requirements and susidiary company requirements?

[min.emerg]

William, I might not be understanding you fully, but why not take the denormalized approach? By this I mean create denormalized dimensions and fact tables that are specific to each business (when there is no overlap - it could be the case that a measure can be used by multiple businesses)?

For instance, if the measure/fact (i.e.: $ Sales) associated with Business B requires the City dimension, but not the Recall dimension, link only to the City dimension. There's nothing forcing you to have every fact table link to every dimension you have.

This will hopefully give you more context: http://www.kimballgroup.com/2003/01/01/fact-tables-and-dimension-tables/

[william-wang]

William, I might not be understanding you fully, but why not take the denormalized approach? By this I mean create denormalized dimensions and fact tables that are specific to each business (when there is no overlap - it could be the case that a measure can be used by multiple businesses)?

For instance, if the measure/fact (i.e.: $ Sales) associated with Business B requires the City dimension, but not the Recall dimension, link only to the City dimension. There's nothing forcing you to have every fact table link to every dimension you have.

This will hopefully give you more context: http://www.kimballgroup.com/2003/01/01/fact-tables-and-dimension-tables/

HI min thanks for your reply.

We want used only one EDW model and different start-model in Data mart.
According to your meaning,add 15(denormalized dimension)*10( ten subsidiary company) in EDW model?
----------------------
Further,What is the positioning of EDW, Whether can contain denormalized data?
In my opinion, the EDW unnecessary contain denormalized data.
The System architecture maybe like this:
ODS->EDW->Data Mart for headquarters
ODS->Data Mart for subsidiary company

[Sponsored content]