“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 anlaysis 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.
This was turning into quite a nasty little problem. However, I was determined to push through this and find a solution. “If I can’t get to the data from the front end, and I require no developer interaction… I guess its time to play dirty.”
Continue reading Part 3 to check out my research into intercept and reinterpret methods
Pingback: Power BI Export Limitations – Part 2/4 – Automation and Insight – Thinking Enterprise Solutions