Create A Support Ticket

Please use the form below to submit a support request.

    Upload a file (optional)

    View Categories

    Power BI – ADOMD Connection

    7 minute(s) read

    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 #

    SettingValue / Notes
    Enable datasource connectionTurns this connection on or off.
    Datasource NameThe friendly name referenced from RW.Relational.Export.
    Authentication ModeOAuth Authentication (service principal) or Password Authentication (user login).
    Connection StringThe XMLA endpoint / server string. Supports the %USERNAME% and %PASSWORD% placeholders.
    %USERNAME% VariableValue substituted for %USERNAME% in the connection string. Stored encrypted.
    %PASSWORD% VariableValue substituted for %PASSWORD% in the connection string. Stored encrypted.
    Validation QueryQuery run by Test Connection. Defaults to SELECT * FROM $SYSTEM.DISCOVER_CATALOGS, a lightweight DMV that works against Power BI XMLA and Analysis Services.
    Command Timeout (Advanced Options)Query timeout in seconds. Default 30; range 1–3600.

    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://.asazure.windows.net/;Initial Catalog=

    On-premises SQL Server Analysis Services (tabular):

    Data Source=\;Initial Catalog=

    %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:

    SettingValue
    Tenant IDThe Entra (Azure AD) tenant (directory) ID.
    Client ID (Application ID)The app registration’s Application (client) ID.
    Client SecretThe app registration’s client secret. Stored encrypted.
    OAuth Scope (optional)Leave blank to use the default Analysis Services scope, https://analysis.windows.net/powerbi/api/.default. Override only if your environment requires a different scope.

    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:

    SettingValue
    %USERNAME% VariableThe user’s organizational account, e.g. analyst@contoso.com.
    %PASSWORD% VariableThe account password.

    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:

    ConnectionSupported query languages
    PowerBI XMLA – SQL (CDATA)SQL
    PowerBI XMLA – ADOMDDAX, DMV, and MDX

    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 #

    SymptomLikely causeResolution
    Test connection fails with an authorization errorBad credentials, or the principal lacks workspace accessFor OAuth, verify the Tenant/Client ID and secret and that the service principal is a member of the workspace; for password, verify the account and password.
    OAuth fails: service principals not allowedTenant admin setting disabledEnable Allow service principals to use Power BI APIs and set the XMLA endpoint to Read/Read-Write in the Power BI admin portal.
    Cannot connect to a Power BI dataset at allWorkspace is not on Premium/PPU/Fabric capacityThe XMLA endpoint requires a Premium, PPU, or Fabric capacity; shared/Pro workspaces have no XMLA endpoint.
    A Connection String is requiredThe Connection String field is blankProvide the Data Source=… endpoint string.
    Tenant ID / Client ID / Client Secret is requiredOAuth mode selected but a field is blankFill in all three OAuth fields, or switch to Password Authentication.
    Password mode fails with an MFA / interactive sign-in errorThe account requires interactive MFAUse a service principal (OAuth) or an account exempt from interactive MFA.
    An MDX query returns an error instead of dataThe MDX result is not tabularRewrite the MDX so it returns a flat row-and-column result, or express the query as DAX.

    Related articles #