Data Sources Supported (SSAS Tabular)

 

Applies To: SQL Server 2016

This topic describes the types of data sources that can be used with tabular models.

You can import data from the data sources in the following table. When you install SQL Server Data Tools (SSDT), setup does not install the providers that are listed for each data source. Some providers might already be installed with other applications on your computer; in other cases you will need to download and install the provider.

SourceVersionsFile typeProviders
Access databasesMicrosoft Access 2010 and later..accdb or .mdbACE 14 OLE DB provider
SQL Server relational databasesMicrosoft SQL Server 2008 and later, Microsoft SQL Server Data Warehouse 2008 and later, Microsoft Azure SQL Database, Microsoft Analytics Platform System (APS)

 

Note that Analytics Platform System (APS) was formerly known as SQL Server Parallel Datawarehouse (PDW). Originally, connecting to PDW from Analysis Services required a special data provider. This provider was replaced in SQL Server 2012. Starting in SQL Server 2012, the SQL Server native client is used for connections to PDW/APS. For more information about APS, see the web site Microsoft Analytics Platform System.
(not applicable)OLE DB Provider for SQL Server

SQL Server Native Client OLE DB Provider

SQL Server Native 10.0 Client OLE DB Provider

.NET Framework Data Provider for SQL Client
Oracle relational databasesOracle 9i and later.(not applicable)Oracle OLE DB Provider

.NET Framework Data Provider for Oracle Client

.NET Framework Data Provider for SQL Server

OraOLEDB

MSDASQL
Teradata relational databasesTeradata V2R6 and later(not applicable)TDOLEDB OLE DB provider

.Net Data Provider for Teradata
Informix relational databases(not applicable)Informix OLE DB provider
IBM DB2 relational databases8.1(not applicable)DB2OLEDB
Sybase Adaptive Server Enterprise (ASE) relational databases15.0.2(not applicable)Sybase OLE DB provider
Other relational databases(not applicable)(not applicable)OLE DB provider or ODBC driver
Text files(not applicable).txt, .tab, .csvACE 14 OLE DB provider for Microsoft Access
Microsoft Excel filesExcel 2010 and later.xlsx, xlsm, .xlsb, .xltx, .xltmACE 14 OLE DB provider
Power Pivot workbookMicrosoft SQL Server 2008 and later Analysis Servicesxlsx, xlsm, .xlsb, .xltx, .xltmASOLEDB 10.5

(used only with Power Pivot workbooks that are published to SharePoint farms that have Power Pivot for SharePoint installed)
Analysis Services cubeMicrosoft SQL Server 2008 and later Analysis Services(not applicable)ASOLEDB 10
Data feeds

(used to import data from Reporting Services reports, Atom service documents, Microsoft Azure Marketplace DataMarket, and single data feed)
Atom 1.0 format

Any database or document that is exposed as a Windows Communication Foundation (WCF) Data Service (formerly ADO.NET Data Services).
.atomsvc for a service document that defines one or more feeds

.atom for an Atom web feed document
Microsoft Data Feed Provider for Power Pivot

.NET Framework data feed data provider for Power Pivot
Office Database Connection files.odc

DirectQuery is an alternative to in-memory storage mode, routing queries to and returning results directly from backend data systems rather than storing all data inside the model (and in RAM once the model is loaded). Because Analysis Services has to formulate queries in the native database query syntax, a smaller subset of data sources are supported for this mode.

Data sourceVersionsProviders
Microsoft SQL Server2008 and laterOLE DB Provider for SQL Server, SQL Server Native Client OLE DB Provider, .NET Framework Data Provider for SQL Client
Microsoft Azure SQL DatabaseAllOLE DB Provider for SQL Server, SQL Server Native Client OLE DB Provider, .NET Framework Data Provider for SQL Client
Microsoft Azure SQL Data WarehouseAllSQL Server Native Client OLE DB Provider, .NET Framework Data Provider for SQL Client
Microsoft SQL Analytics Platform System (APS)AllOLE DB Provider for SQL Server, SQL Server Native Client OLE DB Provider, .NET Framework Data Provider for SQL Client
Oracle relational databasesOracle 9i and laterOracle OLE DB Provider
Teradata relational databasesTeradata V2R6 and later.Net Data Provider for Teradata

Importing tables from relational databases saves you steps because foreign key relationships are used during import to create relationships between tables in the model designer.

Importing multiple tables, and then deleting the ones you don't need, can also save you steps. If you import tables one at a time, you might still need to create relationships between the tables manually.

Columns that contain similar data in different data sources are the basis of creating relationships within the model designer. When using heterogeneous data sources, choose tables that have columns that can be mapped to tables in other data sources that contain identical or similar data.

OLE DB providers can sometimes offer faster performance for large scale data. When choosing between different providers for the same data source, you should try the OLE DB provider first.

Data Sources (SSAS Tabular)
Import Data (SSAS Tabular)

Community Additions

ADD
Show: