To get the best results in your Power BI model, use the following below as a checklist
- Data Model changes do not impact the source. If you need source data always changed, you will need to modify that directly or through Power Query
- For the Data Model, only pull the data you need
- If you need the database to do some work, push it back down to the database to do it. Save your local workstation the hassle.
- Data warehouse…build one
- Set your data types (date, decimel, etc)
- Disable the default summarization
- Categorize your Fields
- Hide items you don’t need
- If you don’t need certain data, remove it…. For instance, if you have Date and Time together in one column, split Time out from the Date, and remove it.
- Reduce the number of distinct values in a field as much as possible. For example if you have Date and Time together in one column, and you need both, split them out, and keep both. It will reduce the number of distinct values altogether and improve the efficiency of your model.
- Fact Table – Large Tables that has relationships out to Dimensions
- Dimension Table – These are Small tables (filter on these and it will improve the efficiency of your model)
- Star Schema – Set your Fact Table in the middle, and your Dimension Tables around the Fact Table
- Snowflake Schema – Multiple Fact Tables and Multiple Dimension Tables
- Your Tables must flow to the Fact Table in a single direction. If the flow is away from your fact table, your data will not be accurate
- Oversampling and/or Ambiguity occurs when you have a table relationship set to both, or bidirectional, instead of in a single direction. Do not set the relationship to both as it increases performance overhead and can cause confusion in the engine
- Within Power BI take the time to Categorize the fields that can be categorized (Select Field > Column Tools > Data Category > Select what category it is)
- Within Power BI, in the fields section, you may see summarized data that shouldnt be summarized. You will need to change Summarized Data in the Summarization dropdown, to Don’t Summarize, where it makes sense…such as in the Key field.
