Configuring a Microsoft Power BI (ADOMD) Datasource

ReportWORQ can connect to Power BI semantic models (datasets) and Azure / SQL Server Analysis Services tabular models over the XMLA endpoint using the native ADOMD client. In the datasource picker this connection type appears as PowerBI XMLA - ADOMD.

There are two ways to connect ReportWORQ to a Power BI XMLA endpoint:

• PowerBI XMLA - SQL — connects through the CDATA driver and accepts SQL queries only. This is covered in the Power BI guide.
• PowerBI XMLA - ADOMD — connects natively and accepts DAX, DMV, and MDX queries. This is the connection described here.

Both connection types return their results through the same worksheet formula, RW.Relational.Export (previously RWSQL).

Before you begin#

• The workspace must be on a Power BI Premium, Premium Per User (PPU), or Fabric capacity. The XMLA endpoint is not available on shared/Pro capacity. (Azure Analysis Services models are always XMLA-addressable.)
• The XMLA endpoint must be set to Read (or Read Write) in the Power BI capacity/tenant settings.
• Have the workspace name and the dataset (semantic model) name you want to query.
• Have the credentials for the authentication method you plan to use:
• OAuth (service principal): an Entra (Azure AD) app registration — Tenant ID, Client ID (Application ID), and Client Secret — granted access to the workspace. For Power BI, the tenant admin must also allow service principals to use the XMLA endpoint.
• User login: an organizational account (user name and password) with access to the workspace/dataset.

Add the connection#

1. In Administration → Datasources, add a new datasource and select PowerBI XMLA - ADOMD.
2. Give the connection a Datasource Name — this is the name you reference from your RW.Relational.Export formulas.
3. Choose an Authentication Mode and complete the fields (see Authentication).
4. Enter the Connection String (see Connection string).
5. Click Test Connection, then Save Changes.

Datasource settings#

Connection string#

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.
• Initial Catalog is the dataset / 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>

%USERNAME% and %PASSWORD% placeholders#

Anywhere in the connection string you can use the tokens %USERNAME% and %PASSWORD%. When the connection is opened, ReportWORQ replaces them with the values from the %USERNAME% Variable and %PASSWORD% Variable fields (both stored encrypted). This keeps credentials out of the visible connection string. See the Password Authentication example below.

Authentication#

OAuth Authentication (service principal)#

Use this for unattended, server-to-server access. 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 and no credentials appear in the connection string. The token is refreshed automatically before it expires.

Select OAuth Authentication and provide:

With OAuth the Connection String contains only the endpoint (no user or password):

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

Before this will work

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

Password Authentication (user login)#

Use this to connect as a specific organizational user. The credentials travel in the connection string; use the %USERNAME% and %PASSWORD% placeholders so the actual values stay in the encrypted variable fields.

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

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

Then fill in:

At run time the placeholders are replaced with the encrypted values and the native client signs in with them.

Note Because report generation runs unattended on the server, "user login" means organizational account credentials supplied through the encrypted variables (Active Directory password sign-in), not an interactive browser prompt. Accounts that require interactive multi-factor authentication cannot be used in this mode — use a service principal (OAuth Authentication) instead.

Running queries#

Reference the connection by its Datasource Name from the RW.Relational.Export formula. RW.Relational.Export runs the query text you provide and imports the result set into an Excel range or table.

RW.Relational.Export accepts whatever query language the connection can execute:

For the ADOMD connection, the query text can be DAX, a DMV statement, or MDX. MDX queries must return a tabular (flat row-and-column) result so they can be imported into the worksheet.

DAX example

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

DMV example (metadata discovery)

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

MDX example (tabular result)

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

If an MDX query returns a shape that cannot be flattened into a table, the formula reports an error — rewrite it so the result is tabular, or express it as DAX.

Behavior and limitations#

• Read-only. This connection is for querying models. It does not support writeback.
• Automatic token refresh (OAuth). Access tokens are cached and refreshed shortly before they expire; if a query fails because a token expired, ReportWORQ acquires a new token and retries once.
• Command timeout. Long-running queries are bounded by the Command Timeout advanced option (default 30 seconds).
• Encrypted credentials. The connection string, the %USERNAME% / %PASSWORD% variables, and the OAuth Tenant ID, Client ID, and Client Secret are all stored encrypted.
• Test Connection runs the Validation Query and reports the row count; it verifies connectivity and authentication but not per-query model permissions.

Troubleshooting#

Related articles#

• Power BI — the SQL (CDATA) connection to the Power BI XMLA endpoint.
• Datasources Overview