Partially vs Fully denormalized dimension and different facts granularities
2 posters
Page 1 of 1
Partially vs Fully denormalized dimension and different facts granularities
Hi everyone,
It was not easy to find the correct title for this topic, so let me try to explain the situation:
Background:
We have to fact tables: Retail Sales and Finance. The retail sales granularity includes the product level. The financial figures are not related to the products but to the business units.
Each product belongs to a one and only one business unit. The relationships could be represented as follows:
First approach:
Our first approach is to have only one denormalized product dimension, in this case we want to have the business unit description in the product table. The drawback is we cannot join this product dimension table with the financial figures anymore.
For this reason the reference to the business unit is kept in the table. As a result we have a partially normalized dimension table with around 30 normalized attributes and 3 foreign keys to another tables similar to the business unit case.
The question:
Is it a best practice? Is there another option?
We also have some performance issues with our specific OLAP tool during the cube processing, if these relationships are resolve during processing time.
Any advice would be highly appreciated.
Kind Regards,
Paul
It was not easy to find the correct title for this topic, so let me try to explain the situation:
Background:
We have to fact tables: Retail Sales and Finance. The retail sales granularity includes the product level. The financial figures are not related to the products but to the business units.
Each product belongs to a one and only one business unit. The relationships could be represented as follows:
First approach:
Our first approach is to have only one denormalized product dimension, in this case we want to have the business unit description in the product table. The drawback is we cannot join this product dimension table with the financial figures anymore.
For this reason the reference to the business unit is kept in the table. As a result we have a partially normalized dimension table with around 30 normalized attributes and 3 foreign keys to another tables similar to the business unit case.
The question:
Is it a best practice? Is there another option?
We also have some performance issues with our specific OLAP tool during the cube processing, if these relationships are resolve during processing time.
Any advice would be highly appreciated.
Kind Regards,
Paul
Re: Partially vs Fully denormalized dimension and different facts granularities
Reference the business unit dimension from both fact tables.
Re: Partially vs Fully denormalized dimension and different facts granularities
ngalemmo wrote:Reference the business unit dimension from both fact tables.
Thanks so much for your answer. Even when the solution seems to be very obvious I was not able to figure it out.
Then I also need to decide if a duplicate the business units attributes as well in the article dimension, that is, if I want to have two ways to reach the same information. It could be confusing for the users.
Kind Regards,
Paul
Re: Partially vs Fully denormalized dimension and different facts granularities
Generally speaking, duplicating attributes that belong in their own dimension, such as business unit, is a bad idea. It becomes a maintenance and reliability issue. Then, if something happens where things get out of sync, and the business notices, the credibility of the solution begins to suffer.
It also becomes very difficult, if not impossible, to implement and use the hierarchy that goes along with business unit. By keeping its own dimension, business units will have keys that can be used in the hierarchy bridge. The hierarchy can then be applied to any fact table that references business unit.
Just relate facts to the appropriate dimensions. That is the basic foundation of dimensional models.
It also becomes very difficult, if not impossible, to implement and use the hierarchy that goes along with business unit. By keeping its own dimension, business units will have keys that can be used in the hierarchy bridge. The hierarchy can then be applied to any fact table that references business unit.
Just relate facts to the appropriate dimensions. That is the basic foundation of dimensional models.
Similar topics
» Snowflake a dimension if facts are at different granularities?
» EDW contain normalized&denormalized dimension
» single denormalized dimension or 2 separate dimensions?
» Static Facts on Dimension Table?
» Aggregation of facts, use as dimension
» EDW contain normalized&denormalized dimension
» single denormalized dimension or 2 separate dimensions?
» Static Facts on Dimension Table?
» Aggregation of facts, use as dimension
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum