“It’s time to find out if we can ‘jailbreak’ Power BI”

In part 1 and part 2 of this series I discussed how Power BI does not meet my clients stated requirements, of being able to download all of the underlying data, with totals regardless of size limits. In this final part I am going to discuss the several options for intercepting and reinterpreting the requests.

Before I get into the nitty gritty here, I would like to very briefly mention that some of the techniques here are not very secure and I would not recommend them for most installations. That said there are viable workarounds here – here we go!

Intercept and reinterpret

The intercept and reinterpret models vary in terms of security and scalability, however they all share several defining traits. 1) They determine the dataset the user is trying to access. 2) They send information about what dataset is being used in Power BI to an outside service, which accesses the full set of underlying data and compiles it.

What Microsoft wants us to do: Embedded solutions

If paginated reports are not slick enough for your users, Microsoft offers embedded solutions. Embedded solutions essentially display Power BI in an iFrame and you can set up your site navigation (or “chrome” in industry parlance) around the iFrame. In order to meet the client’s requirement, we could move all of the filtering and slicing in the reports to the embedded solution’s chrome and then write a web service that takes the filters as arguments.

Once the web service has all of the contextual data passed from the embedded solution, it is free to spool unlimited data to a file and pass the file back to the user. This would typically be done using an asychronous javascript callback to the browser when the file is prepared.

There are not too many downsides to this approach, except that it is expensive and custom. While this may be an option for some large installations, it was not an option for my client.

Custom Power BI Visualization

Another solution that I came across in my research is to implement (or purchase) a custom Power BI visualization. This visualization displays a “download” icon and works similarly to the embedded solution. The visualization accepts an argument of a web service, which would have to be input by the report author. The visualization then uses the Power BI Javascript API to get contextual information and sends it to the web service.

It seems as though one vendor MAQ Software has implemented a solution of this sort, however our client, who received a demo of the software complained it was very slow (20 minutes for a 50 page report). I called MAQ Software for a demo but I was unable to reach anyone there.

This solution was not performant enough, but perhaps if implemented differently it could work. This however is, again, expensive and custom.

The last hacky workaround – intercept the SQL

I’m not sure if I’m proud of this or not, but it’s my invention… The final workaround I investigated was intercepting Power BI on the database server itself. I attached SQL profiler to the database server (note this will only work in Direct Query mode) to see how Power BI makes its calls. As I had expected, Power BI runs “SELECT TOP 1000” queries when getting data from the server.

At least in theory, although I have not tested this extensively, this could provide an avenue to a frictionless approach for getting the data. Essentially, we run a listener service on the database that monitors these “SELECT TOP” queries and the IPs they are requested from. Based on timing and some other criteria we could, theoretically figure out which data a user wants from clicking a link in the report, and serve them that data without a web service.

I think this would be an interesting product, although I have not done the level of research I would need to do to determine if it was productizable. Certainly it is not very secure (you are essentially performing a man-in-the-middle probe on yourself) and it seems costly in terms of compute resources.

Stay tuned for my final conclusion and thoughts about product direction in the final (I promise) post on this topic!