“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.
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
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!