Partially vs Fully denormalized dimension and different facts granularities

View previous topic View next topic Go down

Partially vs Fully denormalized dimension and different facts granularities

Post  hernandezpaul on Mon Mar 02, 2015 8:52 am

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

hernandezpaul

Posts : 9
Join date : 2013-08-26
Age : 37
Location : Herzogenaurach, Germany

View user profile http://hernandezpaul.wordpress.com/

Back to top Go down

Re: Partially vs Fully denormalized dimension and different facts granularities

Post  ngalemmo on Mon Mar 02, 2015 12:58 pm

Reference the business unit dimension from both fact tables.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Partially vs Fully denormalized dimension and different facts granularities

Post  hernandezpaul on Tue Mar 03, 2015 2:37 am

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

hernandezpaul

Posts : 9
Join date : 2013-08-26
Age : 37
Location : Herzogenaurach, Germany

View user profile http://hernandezpaul.wordpress.com/

Back to top Go down

Re: Partially vs Fully denormalized dimension and different facts granularities

Post  ngalemmo on Tue Mar 03, 2015 3:43 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Partially vs Fully denormalized dimension and different facts granularities

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum