Azure Analysis Services database

  • Article

Summary

Item Description
Release State General Availability
Products Power BI (Semantic models)
Power BI (Dataflows)
Fabric (Dataflow Gen2)
Power Apps (Dataflows)
Dynamics 365 Customer Insights
Authentication Types Supported Basic (suported in Power Apps only)
Microsoft account
Organizational account
Windows (supported in Power Apps only)
M Function Reference AnalysisServices.Database
AnalysisServices.Databases

Note

Some capabilities may be present in one product but not others due to deployment schedules and host-specific capabilities.

Prerequisites

  • An Azure subscription. Go to Get Azure free trial.

  • An Azure Analysis Services server. Follow the instructions at Quickstart: Create a server - Portal for creating the server using the Azure portal. Other articles in this section also include information on creating a server using PowerShell, Bicep, or an Azure Resource Manager (ARM) template. This connector article assumes that you've already created an Azure Analysis Services server and have an existing database on the server.

Capabilities Supported

  • Import
  • Connect live (Power BI semantic models)
  • Advanced options
    • MDX or DAX query

Connect to Azure Analysis Services database from Power Query Desktop

To make the connection, take the following steps:

  1. Select the Azure Analysis Services database option in the connector selection. More information: Where to get data

  2. In the SQL Server Analysis Services database dialog that appears, provide the name of the server and database (optional).

    SQL Server database connection builder in Power Query Desktop.

    Note

    Only Power BI Desktop will display the Import and Connect live options. If you're connecting using Power BI Desktop, selecting Connect live uses a live connection to load the connected data directly to Power BI Desktop. In this case, you can't use Power Query to transform your data before loading the data to Power BI Desktop. For the purposes of this article, the Import option is selected. For more information about using a live connection in Power BI Desktop, go to Connect to Analysis Services tabular data in Power BI Desktop.

  3. Select OK.

  4. If you're connecting to this database for the first time, select the authentication type and input your credentials. Then select Connect.

    Analysis Services database authentication.

  5. In Navigator, select the database information you want, then either select Load to load the data or Transform Data to continue transforming the data in the Power Query editor.

    Power Query Navigator showing some of the Sales Targets data.

Connect to Azure Analysis Services database from Power Query Online

To make the connection, take the following steps:

  1. Select the Azure Analysis Services database option in the connector selection. More information: Where to get data

  2. In the Connect to data source page, provide the name of the server and database (optional).

    Azure Analysis Services database connection builder in Power Query Online.

  3. If needed, select an on-premises data gateway.

  4. If you're connecting to this database for the first time, select the authentication kind and input your credentials.

  5. Select Next to continue.

  6. In Navigator, select the data you require, and then select Transform data.

    Power Query Online Navigator showing the Human Resources employee data.

Connect using advanced options

Power Query provides an advanced option that you can add to your query if needed.

Advanced option Description
MDX or DAX statement Optionally provides a specific MDX or DAX statement to the Azure Analysis Services database server to execute.

Once you've entered a value in the advanced option, select OK in Power Query Desktop or Next in Power Query Online to connect to your Azure Analysis Services database.

Connect using Direct Query for composite models

It's possible to convert an Azure Analysis Services Live Connect model on Power BI into a DirectQuery composite model in order to combine the data from the Azure Analysis Services cube with other data in DirectQuery or import mode from other sources. Report authors who want to combine the data from their enterprise Azure Analysis Services models with other data they own, such as an Excel spreadsheet, or want to personalize or enrich the metadata from their enterprise AAS model, will find this functionality especially useful. Refer to Composite models on Power BI semantic models and Analysis Services for additional details.

Troubleshooting

Error indicates “invalid credentials”

If you've enabled the firewall in Azure Analysis Services and you're getting an “invalid credentials” error, you might need to allowlist the IP addresses of the Power Query Online clusters, even if you've allowed access from the Power BI service in your Azure Analysis Services server firewall.

screenshot.

To allowlist the Power Query Online clusters, download the Azure IP ranges and service tags from the Microsoft Download Center. You'll need to allowlist the IP addresses from the PowerQueryOnline service tag. For more information on how to configure the server firewall, go to Quickstart: Configure server firewall - Portal.