Why you should care about the new Power BI ExecuteQueries API
At the beginning of 2020, I wrote about integrating Azure Analysis Services into custom applications. Essentially, I argued that an Analysis Services model was "just a database" (albeit not an RDBMS) and therefore could be treated as such in terms of query integration, process automation and engineering practices. I outlined 8 reasons why you might consider this approach, some of which were possibly more "should" than "could" - for example adding executable tests over your tabular data models.
These capabilities were largely made possible by the XMLA endpoint available in AAS, and have subsequently become possible in Power BI through the same read/write XMLA endpoint that is now available, as Power BI and Azure Analysis Services converge together into one unified platform.
However, these advantages came with some limitations. In both cases, integrations were essentially reliant on .NET - the SDKs and Powershell cmdlets relied on ADOMD.NET and AMO to open connections and issue commands and queries. In addition, when doing this in Power BI, the XMLA endpoint functionality has always been a Premium Capacity only feature, meaning there's an additional cost to use it if you're not already on Premium.
The recent announcement from the Power BI team about the new ExecuteQueries REST API removes these limitations, meaning it's now possible to connect to a Power BI Tabular model and run a DAX query over your data from any programming language, without needing to pay for Premium Capacity features. This is a significant enhancement to the Power BI service, so it's worth revisiting which of those original scenarios I outlined (and the reasons why) have now been enabled in Power BI.
Ultimate UI flexibility
Power BI is first and foremost a data visualization technology, enabling the rapid building of interactive charts in a familiar Office tool set.
But, if you're building a end-user-facing product, rather than an internal tool, you may have very specific or demanding requirements around data visualisations, design/branding or accessibility that can only be achieved in a custom built web (or native) application.
Being able to execute DAX queries via the REST API now makes this possible, meaning you can build any user interface, in any technology. So, you could support back-office style reporting with Power BI, and a custom UI for you customer facing analytics that are both powered by the same underlying tabular model.
Providing external access
Your data insights and visualisations may be a product in their own right that you want to put in the hands of your end users (outside your own organisation). Equally, they may form just a sub-set of features in a much bigger product. Either way, whilst Power BI Embedded offers a quick way to expose your reports, it's pricing model may not be suitable for your application.
Being able to execute DAX queries directly through the new API from your custom applications means you can now expose your data directly to your audience, wherever they are.
Monetising your data insights
One of the reasons to expose your data externally is, of course, to monetise access to it. If your data, or the insights you provide on top of it, are themselves an asset, then custom applications and APIs offering the same levels of analysis allow new business models to be created based on the Data Economy.
Of course, this isn't just a case of exposing Power BI directly to the outside world. Building custom API layers over your tabular data models means you can provide access to, and charge for, your analysis capabilities and data without exposing your internal IP.
Supporting a developer community
Equally, you may be looking to foster innovation by opening up access to your data models and analysis to developers (internal or external). This might enable new insights to be discovered in your data, or even lead to disruptive business models using your analysis in new and unexpected ways.
Providing them with modern, RESTful APIs that expose the meta data around your semantic model allows them to query data in the way they want, using just the pieces they care about, using the tools and processes that they're familiar with.
Creating data mash-ups
Power BI wants to ingest all the data from all the sources into its model, creating a semantic layer for analysis. However, it might not always be possible to ingest all the data you want to analyse into the same place, despite its flexibility in data connectors. In these scenarios, you may have to build a custom integration into an additional data analysis application using the Power BI API to query the data.
Testing your algorithms
Finally, writing DAX expressions to create calculated columns and measures in your model is no different from developing any other type of code, and the same quality gates and development processes should be applied. Unit testing models in Power BI isn't something that most people think about, but it's absolutely possible. And now with the new ExecuteQueries API, you can do this in any language with your favourite testing framework.
Conclusion
There's numerous reasons why you might want, or need, to integrate Power BI queries into a custom application. This integration could take a variety of forms - from entire applications built over the tabular data model, to background processes that run automated tests to ensure quality. With the new ExecuteQueries REST API that Power BI offers, DAX queries can be executed from any programming language or framework, providing incredible flexibility for your applications and processes.
