Power BI Export Limitations - Parts 1 & 2

[aandi]

"We want to be able to export an unlimited number of rows from Power BI from any filtered report, retaining aggregations and totals without the need for a developer to assist."

My heart sank a bit as I heard the request from my new client, a New York City municipal service. I was hoping to leverage Power BI Paginated reports which were released about a year ago. "Yes, we already considered that, but it's too complicated for our business users," the client stated when I mentioned the functionality. "I'll have to get back to you," I replied.

I knew this was going to be more complicated than it should be. Unfortunately, I was right to be cautious. My early research quickly drew me to this article which states unambiguously that Power BI supports export of 30,000 rows to CSV, 150,000 rows to Excel or 16MB of uncompressed data from direct query data sources. But why?

Clearly Microsoft does not want Power BI to be used this way, and I can understand why. Large amounts of data are expensive to retrieve and store in multiple places. Generating this data to be consumed also requires an application server to connect to the database and write the files and then notify the client when the file is ready for download. It's also bad data governance to have core business data proliferate in various Excel files on users hard drives, which can become outdated easily.

To further complicate matters, although you may export limited amounts of data from Power BI, the total row does not come with the data. This has been a feature request from the community for nearly three years. With that sort of a wait time it seems unlikely that Microsoft will ever implement this feature, pretty-much forcing users to use paginated reports for this functionality.

Being a software developer, I hate limitations placed on the functionality of my application. Not exporting totals is a major miss on this piece of functionality.

"Surely the API supports unlimited exports and totals. We'll just write a helper service leveraging the API." Nope. The Power BI REST API is not designed to allow you to get at your data directly. The API is really designed to help automate Power BI dev-ops functions, not for access to data. "No problem," I thought, "I'll just use the Power BI JavaScript API to access the data." No dice. Indeed the JavaScript API makes a call to the data view that is bound to your visualization. The API takes an integer argument which has the exact same limitations as out-of-the-box Power BI.

It was time to explore workarounds... Generally speaking, workarounds for data export fall into 2 categories:

1) Access the underlying data cube

2) Intercept and reinterpret

I will cover several methods for each:

Access the underlying data cube:

Under the hood, Power BI stores data in an analysis services cube. A developer can access this cube directly by determining the port the cube is running on and accessing it using a data tool. For example you can do so with DAX Studio or SSIS.

However, any solution involving this method has two very non user-friendly problems. 1) It requires a high level of technical expertise. 2) Totals cannot be inferred. While you can access the full dataset this way, there is no way to tell what totals you are using within Power BI.