Dimension with Surrogate key of other dimension

View previous topic View next topic Go down

Dimension with Surrogate key of other dimension

Post  user2000 on 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

View user profile

Back to top Go down

Re: Dimension with Surrogate key of other dimension

Post  ngalemmo on Fri Aug 28, 2009 11:11 am

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

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

View user profile http://aginity.com

Back to top Go down

Re: Dimension with Surrogate key of other dimension

Post  patrick_lavallee on 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

View user profile http://www.linkedin.com/in/patricklavallee

Back to top Go down

Re: Dimension with Surrogate key of other dimension

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