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/
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/
It's also worthwhile to consider the usability of the solution. We find that our clients' end users tend to do better with a star schema, as each dimension encapsulates a single business concept and its related attributes.
When dimensions consist of many attributes, its easier to have them grouped together. In a single very wide fact table, people will need to know all of the attribute fields they're interested in.
In terms of data exploration / discoverability, multiple orthogonal dimensions are easier to understand than a single monolithic table. E.g. a field named [Size]: Does this describe one of several product sizes, or does this describe customer size (revenue), or does this capture a human being's shirt size?
One way to work around this would be to use logical dimension prefixes, so that someone browsing the model would be able to see all Customer attributes together. Now, you've just used a naming convention to do the job of a dimension. There's nothing wrong with this, but I'd look askance at a model implemented this way.
Ninja edit: An filtering on dimensions becomes a bear. Rather than being able to manipulate the context of a dimension, or clear that context with an ALL( 'Dim' ), you'll have to use ALL( 'Monolith', 'Monolith'[logical dim field 1], 'Monolith'[logical dim field 2], 'Monolith'[logical dim field 3], ... ).
You may also want to consider how you track changes to data into account. This can be done a number of ways including using columns in Fact & Dimension tables to manage the tracking.
