Connect Power BI and Analysis Services#

Reportworq connects to Power BI semantic models (datasets) and to Azure or SQL Server Analysis Services tabular models over the XMLA endpoint, using the native ADOMD client. In the connector catalog this tile is PowerBI XMLA - ADOMD, and once created the connection's type reads Power BI / SSAS (ADOMD).

There are two different ways to reach a Power BI XMLA endpoint, and they are separate connectors:

This page covers the ADOMD connection. For the CData one, see the Connector catalog. Both return their results through the same worksheet formula, RW.Relational.Export.

Before you begin#

• You are an administrator. Only administrators can add or configure datasource connections.
• For Power BI: the workspace is on a Premium, Premium Per User (PPU), or Fabric capacity. The XMLA endpoint is not available on shared or Pro capacity. Azure Analysis Services models are always XMLA addressable.
• For Power BI: the XMLA endpoint is set to Read (or Read Write) in the capacity or tenant settings.
• You know the workspace name and the dataset (semantic model) name you want to query.
• You have credentials for the authentication mode you plan to use. See Choose an authentication mode.

Add the connection#

1. Go to Settings > Integrations, then select Add integration.
2. Find PowerBI XMLA - ADOMD in the catalog and select Add. The connection editor opens.
3. Select Enable datasource connection.
4. Give the connection a Datasource Name. This is the name your RW.Relational.Export formulas refer to, so it must be unique across the instance and is awkward to change later.
5. Choose an Authentication Mode and complete its fields, as described below.
6. Enter the Connection String, as described in Connection strings.
7. Select Test connection, then Save.

Datasource settings#

Connection strings#

The connection string identifies the XMLA endpoint (the Data Source) and, optionally, the model (Initial Catalog).

Power BI

Data Source=powerbi://api.powerbi.com/v1.0/myorg/MyWorkspace;Initial Catalog=MyDataset

MyWorkspace is the Power BI workspace name, and Initial Catalog is the dataset or semantic model name.

Azure Analysis Services

Data Source=asazure://<region>.asazure.windows.net/<servername>;Initial Catalog=<modelname>

On-premises SQL Server Analysis Services (tabular)

Data Source=<server>\<instance>;Initial Catalog=<database>

Keep credentials out of the connection string#

Anywhere in the connection string you can write the tokens %USERNAME% and %PASSWORD%. When the connection opens, Reportworq replaces them with the values from the %USERNAME% Variable and %PASSWORD% Variable fields, both of which are masked in the editor and stored encrypted. This keeps the real credentials out of the visible connection string. The editor's field labels are clickable and copy the token to your clipboard.

Choose an authentication mode#

OAuth (Service Principal)#

Use this for unattended, server-to-server access. It is the right default for scheduled reporting. Reportworq acquires an Entra (Azure AD) access token using the OAuth 2.0 client credentials grant and presents it to the XMLA endpoint. No user is involved, no credentials appear in the connection string, and the token is refreshed automatically before it expires.

Select OAuth (Service Principal) and provide:

With this mode the Connection String contains only the endpoint, with no user or password:

Data Source=powerbi://api.powerbi.com/v1.0/myorg/MyWorkspace;Initial Catalog=MyDataset

Two things must be true before it will work:

• The app registration is granted access to the target Power BI workspace.
• In the Power BI tenant admin settings, Allow service principals to use Power BI APIs is enabled, and the XMLA endpoint is set to Read (or Read Write).

OAuth (Interactive Login)#

Use this when the model should be read as a named person and that person can sign in once. Reportworq uses the OAuth authorization-code grant: you sign in through the browser, and the resulting access and refresh tokens are stored on the connection and refreshed automatically at run time.

Select OAuth (Interactive Login) and provide the same Tenant ID, Client ID, Client Secret, and optional OAuth Scope as the service-principal mode. Two extra fields appear:

• Redirect URI, which is read only. Register this exact URI, of type Web, on the Entra app registration, and grant the app delegated permissions for your Analysis Services or Power BI resource.
• Sign In, with Authenticate... and Sign out... actions and a status line reading either Signed in or Not signed in.

To sign in:

1. Save your changes first. Selecting Authenticate... with unsaved edits returns "Please save changes before performing this operation." The same applies to Sign out....
2. Select Authenticate.... Sign-in opens in a new browser tab, and the status updates to Signed in when it completes.

The browser address must be HTTPS, or localhost. Interactive sign-in cannot complete over plain http:// on a non-localhost address, and Reportworq stops with a message saying so.

Password Authentication#

Use this to connect as a specific organizational user without an app registration. The credentials travel in the connection string, so use the %USERNAME% and %PASSWORD% placeholders and keep the real values in the encrypted variable fields.

Select Password Authentication and set the Connection String with the placeholders:

Data Source=powerbi://api.powerbi.com/v1.0/myorg/MyWorkspace;Initial Catalog=MyDataset;User ID=%USERNAME%;Password=%PASSWORD%

Then fill in %USERNAME% Variable (for example analyst@contoso.com) and %PASSWORD% Variable.

Accounts that require interactive multi-factor authentication cannot use this mode. Report generation runs unattended on the server, so "user login" here means organizational account credentials supplied through the encrypted variables, not a browser prompt. If the account is subject to MFA, use OAuth (Service Principal) for unattended access, or OAuth (Interactive Login) if a person can sign in once.

Query the model#

Reference the connection by its Datasource Name from the RW.Relational.Export worksheet formula. The formula runs the query text you give it and imports the result into an Excel range or table. The ADOMD connection accepts DAX, DMV, and MDX.

DAX

=RW.Relational.Export(A1, "PowerBI ADOMD", "EVALUATE SUMMARIZECOLUMNS('Date'[Year], ""Revenue"", [Total Revenue])")

DMV, for metadata discovery

=RW.Relational.Export(A1, "PowerBI ADOMD", "SELECT * FROM $SYSTEM.DISCOVER_CATALOGS")

MDX

=RW.Relational.Export(A1, "PowerBI ADOMD", "SELECT NON EMPTY [Date].[Year].Children ON ROWS, {[Measures].[Total Revenue]} ON COLUMNS FROM [Model]")

An MDX query must return a flat, tabular result. If the result cannot be flattened into rows and columns, the formula reports an error. Rewrite the query so its result is tabular, or express it as DAX. For genuine matrix output, use the MDX query type's crosstab render mode in a data model rather than RW.Relational.Export.

Behavior and limits#

• Read only. This connection queries models. It does not support write-back.
• Tokens refresh automatically in both OAuth modes. If a query fails because a token expired, Reportworq acquires a new token and retries once.
• Long queries are bounded by Command Timeout, which defaults to 30 seconds. Raise it under Advanced Options for expensive models.
• Credentials are encrypted at rest: the connection string, the %USERNAME% and %PASSWORD% variables, and the OAuth Tenant ID, Client ID, and Client Secret.
• Test connection runs the Validation Query and reports the row count. It confirms connectivity and authentication, but not per-query permissions inside the model.

Troubleshooting#

Related#

• Connector catalog for the full connector list and the per-connector traps.
• Connect a data source for the general add, test, and manage flow.
• Connect SQL, OLE DB, and ODBC for the relational connectors that share the RW.Relational.Export formula.
• Relational database functions for the full RW.Relational.Export signature.