Data sources supported in Azure Analysis Services
Data sources and connectors shown in Get Data or Table Import Wizard in Visual Studio with Analysis Services projects are shown for both Azure Analysis Services and SQL Server Analysis Services. However, not all data sources and connectors shown are supported in Azure Analysis Services. The types of data sources you can connect to depend on many factors such as model compatibility level, available data connectors, authentication type, and On-premises data gateway support. The following tables describe supported data sources for Azure Analysis Services.
Azure data sources
| Data source | In-memory | DirectQuery | Notes |
|---|---|---|---|
| Azure SQL Database | Yes | Yes | 2, 3 |
| Azure Synapse Analytics (SQL DW) | Yes | Yes | 2 |
| Azure Blob Storage | Yes | No | 1 |
| Azure Table Storage | Yes | No | 1 |
| Azure Cosmos DB | Yes | No | 1 |
| Azure Data Lake Store Gen1 | Yes | No | 1 |
| Azure Data Lake Store Gen2 | Yes | No | 1, 5 |
| Azure HDInsight HDFS | Yes | No | 1 |
| Azure HDInsight Spark | Yes | No | 1, 4 |
Notes:
1 - Tabular 1400 and higher models only.
2 - When specified as a provider data source in tabular 1200 and higher models, both in-memory and DirectQuery models require Microsoft OLE DB Driver for SQL Server MSOLEDBSQL (recommended) or .NET Framework Data Provider for SQL Server.
3 - Azure SQL Managed Instance is supported. Because SQL Managed Instance runs within Azure VNet with a private IP address, public endpoint must be enabled on the instance. If not enabled, an On-premises data gateway is required.
4 - Azure Databricks using the Spark connector is currently not supported.
5 - ADLS Gen2 connector is currently not supported, however, Azure Blob Storage connector can be used with an ADLS Gen2 data source.
Other data sources
Connecting to on-premises data sources from an Azure Analysis Services server require an On-premises gateway. When using a gateway, 64-bit providers are required.
| Data source | In-memory | DirectQuery | Notes |
|---|---|---|---|
| Access Database | Yes | No | |
| Active Directory | Yes | No | 6 |
| Analysis Services | Yes | No | |
| Analytics Platform System | Yes | No | |
| CSV file | Yes | No | |
| Dynamics 365 | Yes | No | 6, 12 |
| Excel workbook | Yes | No | |
| Exchange | Yes | No | 6 |
| Folder | Yes | No | 6 |
| IBM Informix | Yes | No | |
| JSON document | Yes | No | 6 |
| Lines from binary | Yes | No | 6 |
| MySQL Database | Yes | No | 13 |
| OData Feed | Yes | No | 6 |
| ODBC query | Yes | No | |
| OLE DB | Yes | No | |
| Oracle | Yes | Yes | 9 |
| PostgreSQL Database | Yes | No | 6 |
| Salesforce Objects | Yes | No | 6 |
| Salesforce Reports | Yes | No | 6 |
| SAP HANA | Yes | No | |
| SAP Business Warehouse | Yes | No | 6 |
| SharePoint List | Yes | No | 6, 11 |
| SQL Server | Yes | Yes | 7, 8 |
| SQL Server Data Warehouse | Yes | Yes | 7, 8 |
| Sybase Database | Yes | No | |
| Teradata | Yes | Yes | 10 |
| TXT file | Yes | No | |
| XML table | Yes | No | 6 |
Notes:
6 - Tabular 1400 and higher models only.
7 - When specified as a provider data source in tabular 1200 and higher models, specify Microsoft OLE DB Driver for SQL Server MSOLEDBSQL (recommended), SQL Server Native Client 11.0, or .NET Framework Data Provider for SQL Server.
8 - If specifying MSOLEDBSQL as the data provider, it may be necessary to download and install the Microsoft OLE DB Driver for SQL Server on the same computer as the On-premises data gateway.
9 - For tabular 1200 models, or as a provider data source in tabular 1400+ models, specify Oracle Data Provider for .NET. If specified as a structured data source, be sure to enable Oracle managed provider.
10 - For tabular 1200 models, or as a provider data source in tabular 1400+ models, specify Teradata Data Provider for .NET.
11 - Files in on-premises SharePoint aren't supported.
12 - Azure Analysis Services doesn't support direct connections to the Dynamics 365 Dataverse TDS endpoint. When connecting to this data source from Azure Analysis Services, you must use an On-premises Data Gateway and refresh the tokens manually.
13 - Azure Analysis Services doesn't support direct connections to MySQL databases. When connecting to this data source from Azure Analysis Services, you must use an On-premises Data Gateway and refresh the tokens manually.
Understanding providers
When creating tabular 1400 and higher model projects in Visual Studio, by default you don't specify a data provider when connecting to a data source by using Get Data. Tabular 1400 and higher models use Power Query connectors to manage connections, data queries, and mashups between the data source and Analysis Services. These are sometimes referred to as structured data source connections in that connection property settings are set for you. You can, however, enable legacy data sources for a model project in Visual Studio. When enabled, you can use Table Import Wizard to connect to certain data sources traditionally supported in tabular 1200 and lower models as legacy, or provider data sources. When specified as a provider data source, you can specify a particular data provider and other advanced connection properties. For example, you can connect to a SQL Server Data Warehouse instance or even an Azure SQL Database as a legacy data source. You can then select the OLE DB Driver for SQL Server MSOLEDBSQL data provider. In this case, selecting an OLE DB data provider may provide improved performance over the Power Query connector.
When using the Table Import Wizard in Visual Studio, connections to any data source require a data provider. A default data provider is selected for you. You can change the data provider if needed. The type of provider you choose might depend on performance, whether or not the model is using in-memory storage or DirectQuery, and which Analysis Services platform you deploy your model to.
Specify provider data sources in tabular 1400 and higher model projects
To enable provider data sources, in Visual Studio, click Tools > Options > Analysis Services Tabular > Data Import, select Enable legacy data sources.
With legacy data sources enabled, in Tabular Model Explorer, right-click Data Sources > Import From Data Source (Legacy).
Just like with tabular 1200 model projects, use Table Import Wizard to connect to a data source. On the connect page, click Advanced. Specify data provider and other connection settings in Set Advanced Properties.
Impersonation
In some cases, it may be necessary to specify a different impersonation account. Impersonation account can be specified in Visual Studio or SQL Server Management Studio (SSMS).
For on-premises data sources:
- If using SQL authentication, impersonation should be Service Account.
- If using Windows authentication, set Windows user/password. For SQL Server, Windows authentication with a specific impersonation account is supported only for in-memory data models.
For cloud data sources:
- If using SQL authentication, impersonation should be Service Account.
OAuth credentials
For tabular models at the 1400 and higher compatibility level using in-memory mode, Azure SQL Database, Azure Synapse, Dynamics 365, and SharePoint List support OAuth credentials. To generate valid tokens, set credentials by using Power Query. Azure Analysis Services manages token refresh for OAuth data sources to avoid timeouts for long-running refresh operations.
Note
Managed token refresh is not supported for data sources accessed through a gateway. For example, one or more mashup query data sources is accessed through a gateway, and/or the ASPaaS\AlwaysUseGateway property is set to true.
Direct Query mode is not supported with OAuth credentials.
Enable Oracle managed provider
In some cases, DAX queries to an Oracle data source may return unexpected results. This might be due to the provider being used for the data source connection.
As described in the Understanding providers section, tabular models connect to data sources as either a structured data source or a provider data source. For models with an Oracle data source specified as a provider data source, ensure the specified provider is Oracle Data Provider for .NET (Oracle.DataAccess.Client).
If the Oracle data source is specified as a structured data source, enable the MDataEngine\UseManagedOracleProvider server property. Setting this property ensures your model connects to the Oracle data source using the recommended Oracle Data Provider for .NET managed provider.
To enable Oracle managed provider:
In SQL Server Management Studio, connect to your server.
Create an XMLA query with the following script. Replace ServerName with the full server name, and then execute the query.
<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object /> <ObjectDefinition> <Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500"> <ID>ServerName</ID> <Name>ServerName</Name> <ServerProperties> <ServerProperty> <Name>MDataEngine\UseManagedOracleProvider</Name> <Value>1</Value> </ServerProperty> </ServerProperties> </Server> </ObjectDefinition> </Alter>Restart the server.