• Article

Defining Data Sources (Analysis Services)

A Microsoft SQL Server Analysis Services data source is an object that provides the Analysis Services service with the information needed for it to connect to a source of information for the business intelligence solution. Analysis Services can access data from one or more sources of data, provided that Analysis Services is able to construct the OLAP or data mining queries required by the business intelligence solution. The list of providers that you can utilize in an Analysis Services project is increasing over time as Microsoft and third-party vendors provide support for SQL Server Analysis Services. The following providers and relational databases are supported in Analysis Services projects:

  • SQL Server 7.0 using the SQL OLE DB Provider or the .NET native OLE DB provider (x86, x64, and ia64).

  • SQL Server 2000 using the SQL OLE DB Provider or the .NET native OLE DB provider (x86, x64, and ia64).

  • SQL Server 2005 using the SQL Server Native Client OLE DB provider or the SqlClient.NET managed provider (x86, x64, and ia64). 

  • SQL Server 2008 using the SQL Server Native Client OLE DB provider or the SqlClient.NET managed provider (x86, x64, and ia64). 

  • SQL Server 2008 R2 using the SQL Server Native Client OLE DB provider or the SqlClient.NET managed provider (x86, x64, and ia64).

  • SQL Server 2008 R2 Parallel Data Warehouse (PDW) using the OLE DB provider for SQL Server PDW or the managed provider for SQL Server PDW (x86, x64, and ia64). For more information about SQL Server PDW, see the web site SQL Server 2008 R2 Parallel Data Warehouse.

  • SQL Azure Database using the SQL Server Native Client OLE DB provider or the SqlClient.NET managed provider. For more information about SQL Azure, see the web site SQL Azure.

  • Oracle 9.0 using the Microsoft OLE DB Provider for Oracle or the .NET native OLE DB provider (x86 only).

  • IBM DB2 8.1 using Microsoft OLE DB Provider for DB2 (x86, x64, ia64) - only available for Microsoft SQL Server 2005 Enterprise Edition or Microsoft SQL Server 2005 Developer Edition and downloadable as part of the Feature Pack for Microsoft SQL Server Service Pack 1.

  • Access with Microsoft Jet 4.0 OLE DB provider (x86 only).

  • Teradata v2R6 with OLE DB 1.3 provider from NCR (x86 only).

  • Teradata managed provider (x86, x64, and ia64). 

Note

Third party providers, such as the Oracle OLE DB Provider, may also be used to connect to third party databases, with support provided by those third parties.

At a minimum, a data source includes an identifier, a name, and a connection string. The connection string used to access the source data specifies the following information:

  • The provider name.

  • The information needed to connect to the data source using the specified provider. The property settings for particular data source objects vary according to the provider.

  • Other properties supported or required by the provider.

The Analysis Services service can connect to a data source using its own security credentials or other specified security credentials. For OLAP processing tasks, the Analysis Services service must run in the security context of its service account or a specified user account when connecting to a data source. For data mining queries, the Analysis Services service can run in the context of its service account, in the context of a specified user account, or it can impersonate the current user when connecting to the data source. For more information about impersonation, see Defining a Data Source Using the Data Source Wizard (Analysis Services).

Note

If multiple data sources are defined and if data is queried from multiple sources in a single query, such as for a snow-flaked dimension, you must define a data source that supports remote queries using OpenRowset. Typically, this will be a Microsoft SQL Server data source.

In This Section