Modeling defines the relationship between your data sources, allows optimization of the data and the creation of measures.
1. Whenever possible select Import (versus DirectQuery or Live Connection) unless the data being imported is too large.
If the dataset is over 500 MB you may need to use DirectQuery or Live Connection.
2. Remove unused table and columns from the model.
3. Do not use distinct count when there are a high number of groups or cardinality
You wouldn't use distinct count in key fields that are highly unique, for example Task ID, Work Order ID, Equipment ID.
4. Import only the tables and columns needed, additional data imported impacts performance for no reason.
5. Reduce the precision of numeric fields when possible, for example: will results be impacted if 13.29889 is rounded to 13.30?
6. Use integers instead of strings when possible.
When creating a new column that results in a numeric value, set the type to integer instead of string.
7. Use star schema to create relationships for the data model.
Star Schema is more efficient since Dimension tables are connected to central Fact tables
8. Avoid creating relationships on calculated columns.
9. When transforming a table rename the query steps to identify what is occurring.
10. When creating a custom M Query to get data, the .pbix file has to be reviewed by [email protected] for approval.
11. When Using DirectQuery or Live Connection:
- Avoid complex queries in the query editor.
- Don't use relative date filtering in the query editor.
- Keep measures simple to start and only add complexity if needed.
- Set “Assume Referential Integrity” on relationships when possible.
Assume Referential Integrity: allows you to explicitly specify that your model should assume reference integrity in the underlying tables.
12. DirectQuery and Live Connection Troubleshooting
- Index the columns that are commonly filtered or sliced to improve responsiveness.
- To check for responsiveness issues, test the report in a similar way as the users would run the report in Power BI. The DirectQuery and Live Connection sends the query to the source before displaying the report.
- A visual error is produced for long running queries that timeout after running 225 seconds.
0 Comments
Add your comment