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/
Without a doubt, a flat table will be quicker than multiple tables with joins. This is why OLAP technologies predominantly work with a flat file structure.
Others may disagree with me, but I let our data warehouse manage the tables and relationships. PowerBI is about getting the data you need for a specific purpose. It is best practise to do all your complex joins before the data makes it it into PowerBI. I generally only use PowerBI to create joins to reference tables. Hence, I may have a report that has 3 non-related datasets/tables (Sales, Staff, Customer Details). I join all 3 to a date reference table that contains date, hour, year, day type etc. That way, there is no confusion over which date fields to use.
Keep it simple wherever possible without losing too much functionality. Sorry.. I don't actually have a definitive answer for you.
Daniel