Forum Discussion
Performance considerations when building a Power BI datamodel
There is no one right answer - it depends on your data.
Generally you should aim for a star schema. The more columns in each dimension table, generally the more value in normalising that dimension into a single lookup table. If your lookup table has only 1 primary key and 1 text description column, then get rid of the primary key and use the text description in the fact table.
Generally it is not good to use a snowflake schema, but it depends on your data. Sometimes you have to - it depends.
The biggest issue in power pivot is the cardinality in each column. Keep this low where ever possible.
The next biggest problem is the width of fact tables. The more columns, the bigger the problem.
Duplicate dimensions in a fact table are therefore only a problem (generally) when they have a high cardinality and/or when they contribute to making the fact table really wide. If there are lots of rows, then it adds to the problem, but often less than linearly - but it depends on your data.
I wrote this article that may help. http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/
Thank you all for your input. I agree with that from design aspect a star schema is usually the best approach for a number of valid reasons as pointed out by MattAllington and greggyb. For simple, one purpose data models and where the row count is large and number of dimensions is small I too would probably opt for the practical, no-fuzz approach of djnww.
MattAllington describes the performance aspect I was looking for quite well. I ran some tests and seems to me that when putting the dimesions in a star schema when both row count is large and cardinality of the dimensions is high, there seems to be an increase in performance and reduction in the data model size. When decreasing the cardinality of the dimesions the difference in both size and performance seems to reduce significantly between the star design and the one-table design.
So, all things considered, the star schema still seems to be the best practice also from an performance aspect, especially for more complex data models with dimensions with high cardinality. Not to mention the benefits of readability for end users when data exploration may be involved.
To bad that the Quick Insights - feature seems to not perform very well from a star schema design. This is also one reason i strated thinking of the cosiderations between a one-table design versus the star schema design. Based on this information, however, I may need to consider dividing the reports as:
1) Star(Snowflake) schema for performance optimized, user friendly data exploration reports
2) One-table design for "one purpose" reports, where Quick Insights can provide out of the box data exploration