Get Help

Dax@1

Dax@1 reads data from tabular models — Power BI and SQL Server Analysis Services (SSAS) — with DAX queries. Needs the MSOLAP driver.

Connects to

Local or remote tabular models in SSAS and Power BI: Power BI Semantic Model, SQL Server Analysis Services. Legacy providers have no technology of their own — use the technology of the replacement, Dax@2.

Example

Name
My Power BI Dataset
Technology
Power BI Semantic Model
Provider
Dax@1
Connection string
Provider=MSOLAP;
Data Source=powerbi://api.powerbi.com/v1.0/your-organization.com/your-workspace-name;
Initial Catalog=Your-Dataset-Name;
User ID=%YOUR_SERVICE_PRINCIPAL_NAME_ENVIRONMENT_VARIABLE_NAME%;
Password=%YOUR_SERVICE_PRINCIPAL_SECRET_ENVIRONMENT_VARIABLE_NAME%;

Data sources:
- Name: My Power BI Dataset
  Provider: Dax@1
  Technology: PowerBISemanticModel
  Connection string: >
    Provider=MSOLAP;
    Data Source=powerbi://api.powerbi.com/v1.0/your-organization.com/your-workspace-name;
    Initial Catalog=Your-Dataset-Name;
    User ID=%YOUR_SERVICE_PRINCIPAL_NAME_ENVIRONMENT_VARIABLE_NAME%;
    Password=%YOUR_SERVICE_PRINCIPAL_SECRET_ENVIRONMENT_VARIABLE_NAME%;

Replace the placeholders (your-organization.com, your-workspace-name and your-dataset-name) with real values.

Connection string

An OLE DB connection string for the MSOLAP provider — Key=Value pairs separated by semicolons. The keys you will use most:

Key Meaning
Provider Always MSOLAP
Data Source IP address or name of your server; for a workspace powerbi://api.powerbi.com/v1.0/<organization>/<workspace>
Initial Catalog, Catalog Name of the database (dataset) you want to run queries against
User ID, Password Service principal name and secret
Integrated Security SSPI for local connections to SSAS

The full list is in the Analysis Services connection string reference.

A password, or the whole string, can come from an environment variable — %NAME% is replaced with the value of NAME; see How every provider is used.

Power BI workspace

You must have Power BI Premium or Power BI Premium per user in order to connect to a Power BI dataset programmatically. You already must have a service principal with a valid secret and with admin access to your Power BI workspace — the example above.

Both ways of testing Power BI data — on your machine and in a Power BI workspace — are walked through in Test Power BI.

SSAS

Name
My Tabular Model
Technology
SQL Server Analysis Services
Provider
Dax@1
Connection string
Provider=MSOLAP;
data source=localhost;
integrated security=SSPI;
Catalog=WideWorldImporters;

Data sources:
- Name: My Tabular Model
  Provider: Dax@1
  Technology: Ssas
  Connection string: >
    Provider=MSOLAP;
    data source=localhost;
    integrated security=SSPI;
    Catalog=WideWorldImporters;

Settings

None beyond Connection string.

Query

Tabular models do not understand SQL. The statement is one DAX query — knowledge of at least the very basics of DAX is required.

A test comparing the data a DAX query returns (a made-up example without much sense) with expected data provided in YAML:

Tests:
- Name: Check zip codes
  First Data Source: MyTabularModel
  First Query: |
    EVALUATE(
    	TOPN(3,VALUES('Customer'[Postal Code]))
    )
    ORDER BY 'Customer'[Postal Code]
  Second data source: yaml
  Second Query: /Expected results
  Expectation: sets match

Prerequisites

The MSOLAP driver from Microsoft: https://learn.microsoft.com/en-us/analysis-services/client-libraries?view=asallproducts-allversions#windows-installer. Download and install MSOLAP (amd64):

MS OLAP driver

The link leads to an .msi file; install it with Next, Next, Next… Windows only.

Limitations

  • Windows only — the MSOLAP OLE DB driver has no Linux build.
  • Cannot serve lists of definitions — DAX returns column names in square brackets (Tests[Test name], [Test name]), which CAT does not match to property names.

Serving test and data source definitions

Dax@1 cannot serve lists of definitions. A DAX query returns its column names in square brackets — Tests[Test name] for a model column, [Test name] for a column the query names itself — and CAT matches definition properties by name, brackets included: [Test name] is not Test name.

← CsvOleDB@1
ExcelOleDB@1 →