Optimizing Big Data Exports, a.k.a. data dumps, with Power BI Paginated Reports

Introduction:

This post shares an option to pull large amounts of data, think more than 150,000 rows, from a Power BI semantic model and export it to Excel.

As a Power BI Administrator I have faced the challenge of trying to optimize Power BI compute usage while helping a business user to analyze a massive amount of data where removing dimensions or measures and reducing the level of granularity is not an option.

Normally the user will gravitate to use the tools he/she is familiar with, namely Excel and Power BI but sometimes using these tools is not feasible due to resources constraints, so what should we do in these scenarios?

In this particular scenario let’s say a user is connecting via XMLA endpoint to a Power BI semantic model and pulling several dimensions and measures from multiple years and as mentioned before trimming the items to analyze is not an option.

Fortunately, we can reach to our Microsoft toolbox and leverage what I consider as the tool of choice: Power BI paginated reports!

Step 1 Create a Paginated Report

Something great about paginated reports is that we can easily created them in the service, we can either go to a workspace a create it from there i.e.

Or go to OneLake data hub and create it from there i.e.

Step 2 Export the results

Once the paginated report is created exporting the results it’s both easy and straightforward, we just need to go to the Home tab and select the export option that we prefer, i.e.

In our scenario, the business user prefers to analyze the data in Excel using Pivot Tables so we can consider this as the final destination, however there is a trick that I want to share with you, instead of selecting Excel, which would normally make sense for this scenario, we should rather select Comma Separated Values. The reason is that the export to csv format is more efficient, for instance, in my experience a report with 662,000 rows took 38 minutes to generate when I chose Microsoft Excel as the format while exporting to Comma Separated Values format took less than 3 minutes!

Once the file in CSV format has been completed and downloaded to your laptop it is easy to convert it to Excel xlsx format.

Step 3 Open the CSV file and save it as Excel File format

And at this point the high resource usage will happen in the laptop which should have enough memory resources to handle Excel Pivot Table operations, it is also important to have the right Excel version, in my case a combination of 16 GB of RAM and Excel 64-bit has been enough.

lastly it is important to mention that we could use DAX Studio instead of Paginated Reports, as usual there are Pros and Cons, in my opinion Paginated Report is more user friendly with a lower learning curve but more seasoned citizen developers might prefer DAX Studio.

Conclusion:

If you need to export a large amount of data from Power BI to Excel fear not, using the right tool of choice (Paginated Report), exporting to the right format (csv) and having minimum configuration requirements in your laptop (i.e. 16 GB RAM and Excel 64-bit) should be enough to overcome the challenge.

I hope you find this useful if you are facing a similar challenge and thanks for reading!