My Power BI model is too big

Have you ever found yourself in the situation where your Power BI data model, used with import mode, has grown way out of hand? Maybe you started with a nice, informative dashboard for a portion of the company's customers and then management asked you to expand to all customers (a victim of your own success, as it were). And now you have such a big data model that you are hogging almost all the resources available for your Power BI service.

This is exactly what happened to me some time ago and I want to share my way out of this situation. You basically have 3 options:

1. reduce the data loaded in the model, thus loosing resolution and granularity;
2. keep the current model and hope for the best;
3. redesign the model to fit the data.

To apply the third option, using data reducing techniques, while helpful, is not sufficient anymore and a different approach is needed. Keyword: aggregations!

The idea is not new by any means (Phil Seamark has an excellent article on it here: ), but it's often overlooked. By using aggregations, I was able to split the data into 3 categories:

1. hot data = the current, ongoing data, which is actively used in predictions, ongoing metrics and KPIs, business steering etc;
2. warm data = still relevant to make useful comparisons to the current data, but this is data that is "closed" already;
3. cold data = basically archival data, useful only for longer, time based evaluations.

By leveraging the power of aggregations, I was able to fit billions rows of data into a model with a memory footprint of less than 150 MB. And also, compared to a pure Direct Query model, the loading times are greatly reduced:

Conclusion: sometimes it's quite difficult to balance the commercial requirements and expectations, on the one hand, and the technical limitations and capabilities on the other hand. By adopting a balanced mindset and by leveraging data reduction techniques and available tools, one can achieve great results. Remember that a dashboard is only useful if it's being used.

What are your experiences in dealing with large data models?