Optimizing a Power BI data model is conceptually very simple when you understand that the underlying Vertipaq database engine architecture is a column store of unique values. Less unique values, the smaller the dataset and the less work the engine has to do to find a value. This is why column cardinality is intrinsic to Power Bi's performance. There are a variety of strategies to reduce a data set's size, and depending on the nature of your reporting data, you can choose to implement one or any combination of them.

First and foremost, you only want to have data you are reporting on in the data model. There is no point in populating or refreshing a model with data that will never be seen in reports. Start with the minimum amount of data, including only the columns necessary to meet your reporting requirements. It's easy enough to add additional fields later. If your report users are restricted in what they can view, then consider separate and smaller datasets for each audience.

When possible, replace columns with calculated measures to make the data model smaller and improve performance as measures tend to be faster than interrogating large datasets. Measures can also enable you to massage data into more meaningful information. Some fields can be split into multiple columns that reduce the overall cardinality of the data. Splitting date-time into date and time is one way to significantly reduce cardinality, or perhaps drop the time component altogether and have data aggregated only by date.

Aggregation is another way to both reduce the dataset size and have information already summarised for reporting. Power BI has an aggregation feature that pulls data from a direct query source into hidden aggregation tables that Power BI automatically manages.

Power Query Editor has diagnostic tools that help you look into and investigate the processes and queries that populate your data model. You can record actions such as refreshing data and see exactly what is going on in the background and how long each step takes. Performance analysis is not restricted just to the data model. Performance analyzer within the reporting function allows you to see which graphical elements take the longest to retrieve data and render it. 

My name is Hallam Webber, and I've enjoyed presenting this course on optimizing a Power Bi data model. I hope you have also enjoyed it and learned how to optimize your own data models.