Enhancement: Run VertiPaq Analyzer against Power BI Pro models

[corycundy]

It would be great to be able to run the VertiPaq Analyzer against semantic models that are hosted in a Power BI Pro environment. While the XMLA endpoint is not available, much of the data might be available through the INFO DAX functions when run against the executeQueries API. After a quick test it doesn't seem possible to run DMV queries against the API, but the INFO functions, such as INFO.TABLES() do return results.

While there are limitations on the API as far as how much data can be returned, the results for the VertiPaq Analyzer use case would likely be well within the limitations. The biggest limitation might be the 1,000,000 values per API call. Even then simple paging logic could be built. With a quick look, it appears that the INFO.COLUMNS() function has the most columns at 41, which would allow for 24,390 rows returned. While there are likely some models with more than that, the paging logic could be as simple as returning the first set of rows with an ID of <= 20,000 and query in chunks until no results come back.

I know this would take a bit of an effort as a new method of extraction would be required, however, this should likely pay off and open more opportunity for users (and owners) of tools such as DAX Optimizer.

This could then ideally be part of a DevOps process and have a much wider spread than the currently required XMLA endpoints.

I haven't thought through every detail, but on the surface this feels very manageable. There might be some catch along the way, but it isn't apparent to me at the moment.

I hope this gets considered so many more can benefit from the amazing value that the VertiPaq Analyzer already offers.

Cory Cundy

[marcosqlbi]

Hi Cory - thank you very much for the comment!
This is already planned (using INFO functions instead of DMV to enable PBI Pro environment).
However, we did not plan to use the ExecuteQueries API, but rather the regular connection as a client - which is good for .NET applications as we can use TOM. The ExecuteQueries API could be more interesting for other environments such as Python.
@albertospelta can you validate what I wrote? I'm never 100% sure about these topics!

[albertospelta]

@marcosqlbi Exactly. We're still testing whether it's possible to get all the same metadata through the INFO functions that we currently get via XMLA, but the plan is to use a standard .NET connection and allow the client to pass either an instance of IDbConnection or a connection string to the library.

[corycundy]

@albertospelta, @marcosqlbi, Thanks for the updates! I'm glad this is being planned. Connecting to a Pro Workspace to get a VPAX file would really open up additional opportunities.

I wasn't aware that the .NET connection could connect to a Pro Workspace. I had the assumption that only the API's exposed the ability to query a Pro (or non-XMLA Endpoint) Workspace. I thought that was the key limitation of Pro and a selling point of PPU or Premium. Either way, the implementation doesn't really matter as long as functionality can be created.

I'm looking forward to seeing these features and am crossing my fingers there are no technical blockers as you investigate further.

Thanks!

Cory

[marcosqlbi]

Have you ever wondered how Bravo connects to Power BI Pro? The DAX query can be sent there (indeed Export is the only feature active in that scenario), so the INFO functions can be sent over that connection.

[corycundy]

@marcosqlbi, I didn't realize that. Thanks for closing the gap for me!