I’ve always thought it would be useful if we could run queries against AAS or Power BI via a simple REST API and I’ve just come across a really nice sample project created by one of my colleagues at Microsoft, David Browne, that allows you to do just that. You can find it here:
https://github.com/microsoft/azure-analysis-services-http-sample
From the ReadMe:
This sample is a HTTP proxy for XMLA endpoints, intended for use with Power BI Premium or Azure Analysis Services.
It’s implemented as ASP.NET Core 5 API Project. The main API is /api/Query which allows you to POST a DAX query and receive the results as a JSON result.
The sample is coded to pass-through authentication from the client to the XMLA endpoint. So to call the API either use HTTP BASIC auth over HTTPS, passing credentials with the request. It’s highly advised that this be a Service Principal, rather than an AAD user. To specify a Service Principal use a UserName of the form app:[ClientID]@[TenantID], and pass a Client Secret as the Password.
For better security, instead of passing a UserName/Password using HTTP BASIC auth, fetch a Bearer token for your XMLA endpoint. To fetch a token use the Resource ID https://analysis.windows.net/powerbi/api for Power BI, or https://*.asazure.windows.net for Azure Analysis Services
I can think of all kinds of cool stuff you could do with this…
I tried to use that code for serverless Azure Functions, which don’t fully support .NET 5 yet (there is a workaround, but it’s just increased complexity). If you don’t need the authentication part (which heavily relies on .NET 5), you can reuse the connection pool and query result processing code.
Hello Marco & Chris, it would be awesome if you could make a video about this topic (querying Power BI datasets using DAX from Http).
Imagine using this technique integrated with Power Automate! It would be amazing! 😊😊
I’m missing this use case. What would you do with Power Automate querying Analysis Services?
Hi Marco,
I’ve described a use case here: https://www.thebiccountant.com/2019/04/01/export-data-from-power-bi-using-microsoft-flow/
Background for all this is, that PA flows are not able to check large amount of data in an acceptable time. So one uses Power Query to check the tables, calculate the alerts and would ideally return the faulty data back to the flow.
You say at the end “As soon as Power Query will be fully integrated in Flow, these tasks don’t need any Power BI-involvement any more: You can do the validations directly in the editor there”
I would have expected to use the analysis of a complex report as a custom alert technique providing data, or extracting a list of users to send them customized content. This seems a workaround for a missing feature and my concern is that adding an additional service (with implied cost and administration effort) for something that could be made with a more straightforward process is not a good idea.
I’m not saying no, I’m just trying to understand whether there are scenario that could justify this additional cost!
Thanks, yes, these might be niche cases but I’ve come across asks for that a couple of other times as well.
I am so happy others are discussing this. As a developer, have been working on this for over a month and I ran across David Brownes solution as well at one point but seemed too complex. Have a look at Phil Seamark’s solutions for querying an AS Model which he recently presented for a presentation with Ted Pattison.
The use case is simple: we need an easy way to take advantage of our existing data models by being able to run DAX queries against them with custom-built applications, website, or with other open source products for data driven alerts or bokeh charts and graphs. There is a huge opportunity in giving developers ability to easily query our PBI models for custom applications. Our Power BI models would then be the engine of so many solutions beyond the PBI Desktop environment.
I have a solution for this using an Azure function .net core to output csv but the data output piece I borrowed from Phil Seamark is as painful as David Brownes solution. However, “Windows” Powershell is much better good at ingesting the data from ADOMD query as an object, convertto-json, send to blob storage with like 10 lines of code. Unfortunately I can’t get this work with Powershell core for the Azure function I think because there is no support for .NET remoting and so am looking for an alternative so it not so painful to read/write the actual data. No issue running the queries in SSMS, but who wants fiddle with linked servers and openquery to store.
Use case: run DMV queries from ADF to get model metadata for use e.g. in intelligent partition processing routines *wherever* the developer wants to put the logic. In particular, ADF has no AAS connector – but web apis can be called.
A person could also use SELECTEDVALUE() to get user selections to build the query string.
Hello Marco & Chris, it would be awesome if you could make a video about this topic (querying Power BI datasets using DAX from Http).
Imagine using this technique integrated with Power Automate! It would be amazing! 😊😊
Hello Chris,
Can you please tell me what will be the URL if I want to Query AAS using DAX.
So I want the URL and for Dax i can use any evaluate query, but I am not able to build the URL