Question about Dimensions with SKeys to join with other dimensions.

View previous topic View next topic Go down

Question about Dimensions with SKeys to join with other dimensions.

Post  cmosquera on Mon Nov 18, 2013 5:16 pm

Hello All:

We are faced with a design decision and I want to make sure that we are following best practices. In our datamart we have some dimensions which have skeys from other dimensions for which history is tracked using type 2 SCD processing. Is it a good practice to join dimensions (snowflaking) using surrogate keys or should these be used to join only to fact tables?

We are finding that everytime there is a change to one dimension attribute, it is creating new records duplicating all of the data from the old surrogate key value into ones containing the new skey value.

Should the ETL create new dimension surrogate keys on just the changed dimension rows from current dimension?


Thanks in Advance!

cmosquera

Posts : 6
Join date : 2013-11-18

View user profile

Back to top Go down

Re: Question about Dimensions with SKeys to join with other dimensions.

Post  BoxesAndLines on Tue Nov 19, 2013 8:55 am

You are doing it correctly. And now you know why snowflaking is a bad idea. So no, it is not a good practice to snowflake dimensions, especially type 2 SCD's.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Question about Dimensions with SKeys to join with other dimensions.

Post  cmosquera on Tue Nov 19, 2013 10:12 am

Thank You for your response BoxesAndLines.


Should I join dimensions (snowflaking) using surrogate keys or should these be used to join only to fact tables? Is it a better idea to put the natural key in the dimension when snowflaking?

In the Dimensional Modeling Technique relating to snowflakes, it mentions that an attribute key be used to perform the join between dimensions is this be the surrogate key or the natural (business id) key?


KimballGroup wrote:When a hierarchical relationship in a dimension table is normalized, low-cardinality attributes appear as secondary tables connected to the base dimension table by an attribute key. When this process is repeated with all the dimension table’s hierarchies, a characteristic multilevel structure is created that is called a snowflake. Although the snowflake represents hierarchical data accurately, you should avoid snowflakes because it is difficult for business users to understand and navigate snowflakes. They can also negatively impact query performance. A flattened denormalized dimension table contains exactly the same information as a snowflaked dimension.  

cmosquera

Posts : 6
Join date : 2013-11-18

View user profile

Back to top Go down

Re: Question about Dimensions with SKeys to join with other dimensions.

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