In Optimizing a Power BI Data Model we start by investigating the underlying structure and operation of Power BI's VertiPaq database engine. Armed with this knowledge, we investigate various strategies for optimizing performance. Not to give the game away, most of the strategies involve reducing the size of the data model and loading pre-summarized data. We also look at tools within Power BI and Power Query Editor for analyzing report and query performance as well as internal error tracking and diagnosis.
Learning Objectives
- Remove unnecessary rows and tailor data sets for report audiences
- Change data types to improve storage and split columns into multiple columns to reduce the overall number of unique values
- Remove redundant or duplicate data and replace columns with measures where possible
- Use Performance Analyzer to highlight report bottlenecks
- Create aggregations for imported and direct query data models
- Configure and use Power BI's and Power Query's diagnostic tools
Intended Audience
This course is intended for anyone who wants to improve the speed and responsiveness of their reports by improving the underlying data model.
Prerequisites
We would highly recommend taking the Developing a Power BI Data Model and Using DAX to Build Measures in Power BI courses first, as they go into depth about how to implement several of the topics discussed in this course.
Hi, and welcome to this Optimizing a Power BI Data Model course. As the course title suggests, we will be looking at ways to improve the speed and responsiveness of your reports by improving the underlying data model. This course is not focused on model design - you can't optimize a bad design into a good one. We will be looking at ways to optimize your data to play to the strengths of Power BI's database engine. Optimization is essentially reducing the amount of data the Power BI engine has to deal with. Data volume reduction is accomplished in three ways. First, we reduce the amount of raw data by reducing the number of rows and columns in the model. Secondly, we shrink the model by reducing the number of unique values in the data. Finally, we can replace raw data with aggregations and summaries, reducing the size and increasing responsiveness. In optimizing our data model, we will use various diagnostic tools to help us identify performance bottlenecks and where the most significant data reductions can be achieved.
I would highly recommend taking the Developing a Power BI Data Model and Using DAX to Build Measures in Power BI courses first, as they go into depth about how to implement several of the topics discussed in this course.
My name is Hallam Webber, and I'll be your instructor for this course. We welcome all comments and feedback, so please feel free to reach out and get in touch with us at support@cloudacademy.com with any feedback, positive or negative.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.