Learning from others is a great way for me to be proactive in how I approach data modeling. Being aware of common issues and building good modeling practices up-front helps me avoid common pitfalls as my models grow in size and complexity.
SQLBI, Guy in a Cube, Two Alex, and the Saturday Power BI group (referred to as the “Saturday Morning Learning Crew”) have contributed to my growth and maturity in working with Power BI. I highly recommend checking out the links and taking advantage of these great community resources.
This last week we had the pleasure of not only one, but two live streams (Friday and Saturday) with GIAC and Marco and Alberto from SQLBI!
The full two hours were full of such great advice and best practices for working with big data models, that I decided to post a reference list by topic as a guide for upcoming discussions in our Power BI Study group. If you are interested in joining the conversation please reach out to me @markwaltercpa (Twitter/LinkedIn).
Enjoy!
Hour One – SQLBI
12:25 Most common issue with large data models.
14:45 Key drivers of common modeling issues.
20:00 Issues outside of big data performance.
23:10 Working with very large dimensions.
31:35 Learning more about the Vertipaq engine and compression.
36:15 Direct query issues and common solutions.
38:20 Unnecessary uses of direct query.
39:40 Understanding the implications of direct query and best practice.
48:30 Understand your users needs first.
49:25 Incremental refresh as a balance between import and direct query.
51:10 When Power BI desktop users should invest in development tools.
52:30 Improper use of relationships.
58:30 Reducing cardinality in large columns depends on data size.
1:03:30 Common performance issues on the report.
1:05:30 Picking the right report tool… Export vs. paginated reports.
1:07 Performance with many concurrent. Pre-aggregate the data as a final solution.
Hour Two – Guy in a Cube
11:40 Congrats SQLBI on 25K subscribers and the “Fantastic four”!
16:30 Mistakes surrounding partitions.
24:30 What qualifies as big data?
27:00 How far can pro licenses be pushed when working with large data?
33:10 Partitions help procession time not query performance.
35:45 What are segments?
40:00 Design the data model to satisfy your report.
41:40 Models with more than one fact table.
44:00 Star schema or snowflake?
46:45 Snowflakes increase complexity and Auto-exist on dimensions.
50:15 Improving performance when using time intelligence.
55:30 Improving slow performance iterating a conditional IF statement over many rows.
59:30 Performance of Many to Many?
1:09:30 Final thoughts on optimization.
As always, thanks for visiting!
Leave a Reply