You may not know that I have been travelling the country interviewing community leaders from the Microsoft data platform, on a new video blog: Data On The Road (dataontheroad.blog). Earlier this year, we travelled across the US in our motorhome, “The Data Bus” and stopped in several cities to chat with friends and colleagues who speak at conferences, write books, lead user groups and teach people about thriving in the data field. I had a chance to chat with folks like Bob Ward, Buck Woody, Adam Saxton, Kelly Kaye, Chris Finlan, Matthew Roche, Kellyn Pot’Vin Gorman, Leslie Andrews, Oz Du Soliel and many others. We also talk about how to work effectively from home and remotely, the importance of community and how these intense data experts unplug to balance work and life.
Power BI is architected to consume data in a dimensional model, with narrow fact tables and related dimensions. Introducing a big, wide table in a tabular model is extremely inefficient. It takes up space and memory resources, impacts performance, and complicates measure coding. Flattening records into a flat table is one of the worst things you can do in Power BI and a common mistake made by novice Power BI users.
Table partitioning has long been an important task in semantic model design using SQL Server Analysis Services. Since SSAS Tabular and Power BI models are built on top of the SSAS architecture, the pattern of partitioning remains the same as it has been for twenty years. However, the specific methods for implementation have been fine-tuned and improved. The reasons for partitioning large fact tables mainly include:
Improve refresh speed,
Prevent reloading historical records,
Capture updated history,
Reduce database resource load
You don’t have to have massive tables to benefit from partitioning. Even tables with a few hundred thousand records can benefit from partitioning, to improve data refresh performance and to detect source data changes. There is little maintenance overhead, so the benefits usually outweigh the cost, in terms of effort and management.
When planning a Power BI solution, how can we plan for scale and growth? Like any technology, Power BI has limits but Power BI can manage a surprising large value of analytic data. We also have tools and companion technologies capable of handling workloads of data that alongside Power BI.
The Right Tool for the Job
You wouldn’t use a screwdriver to hammer a nail and you wouldn’t use a chainsaw to assemble an Ikea cabinet; so why would you use Power BI Desktop to create a detailed shipping manifest? It’s not the right tool for the job. The Power BI platform contains tools that are appropriate to use for different reporting scenarios and using them together will yield far better results than forcing tools to behave differently than they were intended.