Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Dimension with Surrogate key of other dimension

3 posters

Go down

Dimension with Surrogate key of other dimension Empty Dimension with Surrogate key of other dimension

Post  user2000 Fri Aug 28, 2009 10:36 am

Can one dimension store surrogate key of another dimension?

e.g. Prodcut Dim with Surrogate key of Product Type Dim

If so, is it good practice to have such kind of modelling?

user2000

Posts : 2
Join date : 2009-08-28

Back to top Go down

Dimension with Surrogate key of other dimension Empty Re: Dimension with Surrogate key of other dimension

Post  ngalemmo Fri Aug 28, 2009 11:11 am

That is the definition of a 'snowflake' schema. Best practice is to avoid them when possible.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Dimension with Surrogate key of other dimension Empty Re: Dimension with Surrogate key of other dimension

Post  patrick_lavallee Fri Aug 28, 2009 12:43 pm

My opinion goes favourably with snowflake. I think you should build your conceptual model using the snowflake model as much as you can, and then, once you truly understand your business model, expected usage and behaviour, you will denormalized for performance optimization. And even there, for a lot of cases, I would recommend that you create the physical snowflake tables and the denormalized tables. Today’s ETL tools allow you to automate that denormalization so that you don’t have any duplicate in your ETL process, therefore no risk of data integrity issue. Also, BI tools now support pretty well the snowflake, and they are less a problem with DW enabled RDBMS. In addition to that, a snowflake model will allow you to reuse some part of the snowflake dimension (higher hierarchy) along with your aggregates (ex : the geographic attributes of the customer could be a dimension by itself and usable for an aggregate or another fact table).

patrick_lavallee

Posts : 3
Join date : 2009-03-03
Location : Quebec, Canada

http://www.linkedin.com/in/patricklavallee

Back to top Go down

Dimension with Surrogate key of other dimension Empty Re: Dimension with Surrogate key of other dimension

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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