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).
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 MicrosoftSQL Server data source.
In This Section
Defining a Data Source Using the Data Source Wizard (Analysis Services)
Describes how to create a data source object by using the Data Source Wizard.Modifying a Data Source in Data Source Designer (Analysis Services)
Describes how to modify an existing data source by using Data Source Designer.Deleting a Data Source in Solution Explorer (Analysis Services)
Describes how to delete a data source in Business Intelligence Development Studio.