Azure Analysis Services 中支持的数据源Data sources supported in Azure Analysis Services

对于 Azure Analysis Services 和 SQL Server Analysis Services,包含 Analysis Services 项目的 Visual Studio 中的“获取数据”或“表导入向导”中显示的数据源和连接器都会显示。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. 但是,并非所显示的所有数据源和连接器在 Azure 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. 下表介绍了 Azure Analysis Services 支持的数据源。The following tables describe supported data sources for Azure Analysis Services.

Azure 数据源Azure data sources

数据源Data source 内存中In-memory 直接连接DirectQuery 注释Notes
Azure SQL 数据库Azure SQL Database Yes Yes 232, 3
Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) Yes Yes 22
Azure Blob 存储Azure Blob Storage Yes No 11
Azure 表存储Azure Table Storage Yes No 11
Azure Cosmos DBAzure Cosmos DB Yes No 11
Azure Data Lake Store Gen2Azure Data Lake Store Gen2 Yes No 151, 5
Azure HDInsight HDFSAzure HDInsight HDFS Yes No 11
Azure HDInsight SparkAzure HDInsight Spark Yes No 11

注意:Notes:

1 - 仅限表格 1400 和更高模型。1 - Tabular 1400 and higher models only.
2 - 在表格 1200 和更高版本的模型中指定为“提供程序”数据源时,内存中模型和 DirectQuery 模型都需要 Microsoft OLE DB Driver for SQL Server MSOLEDBSQL(推荐)、SQL Server Native Client 11.0 或 .NET Framework Data Provider for SQL Server。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), SQL Server Native Client 11.0, or .NET Framework Data Provider for SQL Server.
3 - 支持 Azure SQL 托管实例。3 - Azure SQL Managed Instance is supported. 由于 SQL 托管实例在具有专用 IP 地址的 Azure VNet 中运行,因此必须在该实例上启用公共终结点。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.
5 - 目前不支持 ADLS Gen2 连接器,但是,Azure Blob 存储连接器可以与 ADLS Gen2 数据源一起使用。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

数据源Data source 内存中In-memory 直接连接DirectQuery 注释Notes
Access 数据库Access Database Yes No
Active DirectoryActive Directory Yes No 66
Analysis ServicesAnalysis Services Yes No
分析平台系统Analytics Platform System Yes No
CSV 文件CSV file Yes No
Dynamics 365Dynamics 365 Yes No 66
Excel 工作簿Excel workbook Yes No
ExchangeExchange Yes No 66
文件夹Folder Yes No 66
IBM InformixIBM Informix Yes No
JSON 文档JSON document Yes No 66
二进制文件中的行Lines from binary Yes No 66
MySQL 数据库MySQL Database Yes No
OData 源OData Feed Yes No 66
ODBC 查询ODBC query Yes No
OLE DBOLE DB Yes No
OracleOracle Yes Yes 99
PostgreSQL 数据库PostgreSQL Database Yes No 66
Salesforce 对象Salesforce Objects Yes No 66
Salesforce 报表Salesforce Reports Yes No 66
SAP HANASAP HANA Yes No
SAP Business WarehouseSAP Business Warehouse Yes No 66
SharePoint 列表SharePoint List Yes No 6116, 11
SQL ServerSQL Server Yes Yes 787, 8
SQL Server 数据仓库SQL Server Data Warehouse Yes Yes 787, 8
Sybase 数据库Sybase Database Yes No
TeradataTeradata Yes Yes 1010
TXT 文件TXT file Yes No
XML 表XML table Yes No 66

注意:Notes:
6 - 仅限表格 1400 和更高版本的模型。6 - Tabular 1400 and higher models only.
7 - 在表格 1200 和更高版本的模型中指定为“提供程序”数据源时,请指定 Microsoft OLE DB Driver for SQL Server MSOLEDBSQL(推荐)、SQL Server Native Client 11.0 或 .NET Framework Data Provider for SQL Server。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 - 如果指定 MSOLEDBSQL 作为数据提供程序,则可能需要在本地数据网关所在的同一计算机上下载并安装 Microsoft OLE DB Driver for SQL Server8 - 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 - 对于表格 1200 模型,或者作为表格 1400+ 模型中的“提供程序”数据源时,请指定用于 .NET 的 Oracle 数据提供程序。9 - For tabular 1200 models, or as a provider data source in tabular 1400+ models, specify Oracle Data Provider for .NET. 如果指定为结构化数据源,请务必启用 Oracle 托管提供程序If specified as a structured data source, be sure to enable Oracle managed provider.
10 - 对于表格 1200 模型,或者作为表格 1400+ 模型中的“提供程序”数据源时,请指定用于 .NET 的 Teradata 数据提供程序。10 - For tabular 1200 models, or as a provider data source in tabular 1400+ models, specify Teradata Data Provider for .NET.
11 - 不支持本地 SharePoint 中的文件。11 - Files in on-premises SharePoint are not supported.

从 Azure Analysis Services 服务器连接到本地数据源需要使用本地网关Connecting to on-premises data sources from an Azure Analysis Services server require an On-premises gateway. 使用网关时,需要 64 位提供程序。When using a gateway, 64-bit providers are required.

了解提供程序Understanding providers

在 Visual Studio 中创建表格 1400 和更高版本的模型项目时,默认情况下,在使用“获取数据”连接到数据源时,你不指定数据提供程序。When creating tabular 1400 and higher model projects in Visual Studio, by default you do not specify a data provider when connecting to a data source by using Get Data. 表格 1400 和更高版本的模型使用 Power Query 连接器来管理数据源与 Analysis Services 之间的连接、数据查询和混搭。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. 但是,你可以在 Visual Studio 中为模型项目启用旧数据源。You can, however, enable legacy data sources for a model project in Visual Studio. 启用后,可以使用表导入向导连接到表格 1200 和更低版本模型中传统上受支持的某些数据源,如旧版数据源或提供程序数据源 。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. 例如,可以连接到 SQL Server 数据仓库实例,甚至是连接到作为旧数据源的 Azure SQL 数据库。For example, you can connect to a SQL Server Data Warehouse instance or even an Azure SQL Database as a legacy data source. 然后,你可以选择 OLE DB Driver for SQL Server MSOLEDBSQL 数据提供程序。You can then select the OLE DB Driver for SQL Server MSOLEDBSQL data provider. 在这种情况下,选择 OLE DB 数据提供程序可以通过 Power Query 连接器提供更好的性能。In this case, selecting an OLE DB data provider may provide improved performance over the Power Query connector.

当使用 Visual Studio 中的“表导入向导”时,连接到任何数据源都需要数据提供程序。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. 你选择的提供程序的类型可能取决于性能,无论该模型使用内存中存储还是 DirectQuery,也不管你将模型部署到哪个 Analysis Services 平台。The type of provider you choose can 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.

在表格 1400 和更高版本的模型项目中指定提供程序数据源Specify provider data sources in tabular 1400 and higher model projects

若要启用提供程序数据源,请在 Visual Studio 中单击“工具” > “选项” > “Analysis Services 表格” > “数据导入”,然后选择“启用旧数据源”。 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).

启用旧数据源

与使用表格 1200 模型项目一样,请使用表导入向导连接到数据源。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. 可以在 Visual Studio 或 SQL Server Management Studio (SSMS) 中指定模拟帐户。Impersonation account can be specified in Visual Studio or SQL Server Management Studio (SSMS).

对于本地数据源:For on-premises data sources:

  • 如果使用 SQL 身份验证,则模拟应为服务帐户。If using SQL authentication, impersonation should be Service Account.
  • 如果使用 Windows 身份验证,请设置 Windows 用户/密码。If using Windows authentication, set Windows user/password. 对于 SQL Server,只有内存中数据模型才支持带有特定模拟帐户的 Windows 身份验证。For SQL Server, Windows authentication with a specific impersonation account is supported only for in-memory data models.

对于云数据源:For cloud data sources:

  • 如果使用 SQL 身份验证,则模拟应为服务帐户。If using SQL authentication, impersonation should be Service Account.

OAuth 凭据OAuth credentials

对于在 1400 和更高兼容性级别下使用内存模式的表格模型,Azure SQL 数据库、Azure Synapse(以前称为 SQL 数据仓库)、Dynamics 365 和 SharePoint 列表支持 OAuth 凭据。For tabular models at the 1400 and higher compatibility level using in-memory mode, Azure SQL Database, Azure Synapse (formerly SQL Data Warehouse), Dynamics 365, and SharePoint List support OAuth credentials. Azure Analysis Services 管理 OAuth 数据源的令牌刷新,以避免长时间运行的刷新操作超时。Azure Analysis Services manages token refresh for OAuth data sources to avoid timeouts for long-running refresh operations. 若要生成有效的令牌,请使用 Power Query 设置凭据。To generate valid tokens, set credentials by using Power Query.

OAuth 凭据不支持直接查询模式。Direct Query mode is not supported with OAuth credentials.

启用 Oracle 托管提供程序Enable Oracle managed provider

在某些情况下,对 Oracle 数据源的 DAX 查询可能会返回意外的结果。In some cases, DAX queries to an Oracle data source may return unexpected results. 这可能是由用于数据源连接的提供程序导致的。This can 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. 对于将 Oracle 数据源指定为提供程序数据源的模型,请确保指定的提供程序是适用于 .NET 的 Oracle 数据提供程序 (Oracle.DataAccess.Client)。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).

如果将 Oracle 数据源指定为结构化数据源,请启用 MDataEngine\UseManagedOracleProvider 服务器属性。If the Oracle data source is specified as a structured data source, enable the MDataEngine\UseManagedOracleProvider server property. 设置此属性可确保模型使用建议的适用于 .NET 托管提供程序的 Oracle 数据提供程序连接到 Oracle 数据源。Setting this property ensures your model connects to the Oracle data source using the recommended Oracle Data Provider for .NET managed provider.

若要启用 Oracle 托管提供程序,请执行以下操作:To enable Oracle managed provider:

  1. 在 SQL Server Management Studio 中,连接到你的服务器。In SQL Server Management Studio, connect to your server.

  2. 使用以下脚本创建一个 XMLA 查询。Create an XMLA query with the following script. ServerName 替换为完整的服务器名称,然后执行查询。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>
    
  3. 重新启动服务器。Restart the server.

后续步骤Next steps