We have designed a schema which has some snowflakes which we implement as Reference dimensions in SSAS. This design seems to be the most logical given that we have a dimension table called Portfolio, which has a number of related dimensions such as Client, Employee and Product. Instead of direcrly joining all these tables to the Positions fact table, we set them up as Reference dimensions via Portfolio.
I have read in a number of places (Lachev, Mundy) that there may be some issues with this approach, particularly on performance. Are there any metrics on what sort of performance degradation there may be? Does it depend on the number of Reference dimensions? Is it generally better if possible to always use the Standard dimensions by denormalising to a star schema - that is making Client, Employee and Product join directly to the fact table? Are there any other advantages or disadvantages, such as in the ease of data maintenance?
I would be grateful for any feedback on this.
I don't know what the performance implications of Reference dimensions are, other than that you should select the "Materialize" option on the Usage dialog, to improve query peformance - could you mention the respective page numbers in the 2 books, since many in this forum may have access to them?
But, regardless of whether you use Reference dimensions, or roll Client, Employee and Product into the Portfolio dimension, there should be no need to denormalize the snowflake to a star. For example, the Product dimension in Adventure Works has 3 tables, but there are other examples of Reference dimensions as well.
No comments:
Post a Comment