We are happy to announce that the ExecuteQueries REST API is now GA. By using this REST API, you can query datasets in Power BI from practically any modern environment on any platform, as long as you can authenticate with Azure Active Directory (AAD) and construct a Web request. The query functionality is comparable to XMLA read access, yet avoids dependencies on Analysis Services client libraries, requires no connection to XMLA endpoints, and works in Premium workspaces as well as in ordinary workspaces. Just make sure your workspace is a modern (v2) workspace and upgrade if you are still using a classic (legacy) workspace. For additional details on how to use this API, see Datasets – Execute Queries in the REST API documentation. Our previous public preview announcement also shows how to use the ExecuteQueries REST API in PowerShell scripts and .NET applications.
Along with the GA release, we are introducing the following improvements for the ExecuteQueries REST API:
- Dedicated admin tenant switch. During public preview, we did not provide a separate tenant switch for the ExecuteQueries REST API. Instead, the REST API piggybacked on the tenant switch for XMLA endpoints, but customers told us that a separate tenant switch was necessary. By using the new dedicated tenant switch illustrated in the following screenshot, you can now specify who can use the ExecuteQueries REST API without impacting your XMLA endpoints configuration. The API is enabled by default for the entire organization.
- Audit log trace. The ExecuteQueries REST API now emits an AnalyzedByExternalApplication event so that tenant admins and auditors can see who accessed a dataset through the ExecuteQueries REST API. To signal ExecuteQueries REST API use, we added a new field to this event type, called EndPoint. For the ExecuteQueries Rest API, the value is “Dataset Execute Queries REST API” otherwise the value is null, empty, or completely missing. In combination with the tenant switch, this audit log information gives you the control and visibility necessary to govern the ExecuteQueries REST API effectively.
- Power Automate Action/Connector. Power Automate integration opens entirely new possibilities for the ExecuteQueries Rest API. You only need to add the new Power BI action labeled Run a query against a dataset to your flow, and then you can conveniently select the target workspace and dataset in the UI and post your DAX expression into the Query text textbox. You would be hard pressed to find an easier scenario to query a Power BI dataset programmatically. The output can then be processed using a suitable Power Automate action. The example in the following screenshot simply exports the query results in csv format to a file in SharePoint Online.
- Support for impersonation. If you have dataset write permissions, you can now set an impersonatedUserName property in the request body to run the query as another user if the dataset is RLS-enabled. Among other things, this is a convenient way to test row-level security (RLS) and other security-related dataset features. Make sure you specify the username in UPN format. It works in much the same way as the EffectiveUsername property when connecting to a dataset by using AS client libraries and XMLA endpoints. However, don’t confuse the two. The impersonatedUserName property can only be used for impersonation with RLS-enabled datasets and works similar to the Test as role feature described under Row-level security (RLS) with Power BI in the product documentation. If the dataset is not RLS-enabled, impersonatedUserName is not applied. EffectiveUserName, on the other hand, is a general connection-string property for AS client libraries and is applied regardless of the presence of RLS.
Thanks to impersonatedUserName support in the ExecuteQueries REST API, you can also use impersonation with RLS-enabled datasets by using the Run a query against a dataset action in Power Automate. The Impersonate user textbox is in the advanced options. The following screenshot shows a flow that reads UPNs row-by-row from a table in Excel Online and calls the ExecuteQueries REST API each time with the current UPN in the Impersonate user textbox. A subsequent action could then compare the returned results with expected results.
- Microsoft information protection (MIP) label in query response. MIP helps organizations to classify, label, and protect data based on sensitivity. If a MIP label is assigned to your dataset, you can find the MIP label id and name in the JSON response next to the results collection. By using the id, you can then obtain further information through the MIP SDK and apply appropriate controls on the client side depending on the required protection level. The label name is included for human friendliness.
This GA marks an important milestone for the ExecuteQueries REST API. Power BI admins can effectively govern and audit its usage, and BI developers can broaden the reach of their business solutions and maximize their return of investment in Power BI datasets. Thanks to the ExecuteQueries REST API, you can now leverage the data and business logic that you pack into your datasets in new areas, such as Power Automate. A separate blog post will cover Power Automate integration in greater detail, so stay tuned.