Defining a Data Source Using the Data Source Wizard (Analysis Services)
You use the Data Source Wizard in Business Intelligence Development Studio to define one or more data sources for a Microsoft SQL Server Analysis Services project.
Choosing How to Define the Connection
Whether you are working with an Analysis Services project or connected directly to an Analysis Services database, you can define a data source based on a new or an existing connection. If you are working with an Analysis Services project, you can also define a data sources based on another object in the project or solution.
Creating a Data Source Based on a New Connection
The default provider for a new connection is the Native OLE DB\SQL Server Native Client provider. This provider is used to connect to a SQL Server Database Engine instance using OLE DB. The Native OLE DB\SQL Server Native Client provider is designed to provide superior performance when connecting to a SQL Server Database Engine instance using OLE DB. For more information, see SQL Server 2008 R2 Native Client Programming.
SQL Server Analysis Services supports many different types of providers. For a list of the providers and relational databases supported by SQL Server Analysis Services, see Defining Data Sources (Analysis Services).
After you select a provider, you provide specific connection information required by that provider to connect to the underlying data. The exact information required depends upon the provider selected, but generally such information includes a server or service instance, information for logging on to the server or service instance, a database or file name, and other provider-specific settings.
If the provider supports an authentication service, you have the option of using Windows Authentication to access the data source. This option typically uses the credentials of the process that is trying to access the data source to give access to the data. Analysis Services provides impersonation capabilities to allow for more flexibility while negotiating the security architecture of your enterprise.
Sometimes, you may want to provide a specific level of access to a data source without regard to permissions assigned to individual users. In these cases, you may be able to supply a user name and a password to use for authentication to the data source, instead of using Windows Authentication. To use this option, type a user name and a password. By default, Business Intelligence Development Studio does not save passwords with the connection string. If the password is not saved, Analysis Services prompts you to enter the password when it is needed. If you choose to save the password, the password is stored in encrypted format in the data connection string. Analysis Services encrypts password information for data sources using the database encryption key of the database that contains the data source. With encrypted connection information, you must use SQL Server Configuration Manager to change the Analysis Services service account or password or the encrypted information cannot be recovered. For more information, see SQL Server Configuration Manager.
Creating a Data Source Based on an Existing Connection
If you have an existing data source defined in an Analysis Services database or project and wish to create a new data source object that connects to the same underlying data source, you can simply copy properties of the first data source object into a new data source object. You can then specify its own impersonation settings and then, after creating the new data source, modify the data source to change one or more of its properties.
Creating a Data Source Based on Another Object
When you work in an Analysis Services project, your data source can be based on an existing data source in your solution, or can be based on an Analysis Services project.
Creating a data source based on an existing data source in your solution lets you define a data source that is synchronized with the existing data source. When the project containing this new data source is built, the data source settings from the underlying data source are used.
Creating a data source based on an Analysis Services project lets you reference another Analysis Services project in the solution in the current project. The new data source uses the MSOLAP.3 provider with its Data Source property and Initial Catalog property acquired from the TargetServer and TargetDatabase properties of the selected project. This feature is useful in solutions where you are using multiple Analysis Services projects to manage remote partitions, because the source and destination Analysis Services databases require reciprocal data sources to support remote partition storage and processing.
When you reference a data source object, you can edit that object only in the referenced object or project. You cannot edit the connection information in the data source object that contains the reference. Changes to the connection information in the referenced object or project appear in the new data source when it is built. The connection string information that appears in the data source (.ds) file in the project is synchronized when you build the project or when you clear the reference in Data Source Designer. No additional synchronization occurs after you clear a reference from a data source.
Defining Impersonation Information
After defining connection information, you define the credentials that the Analysis Services service uses to connect to the data source. These settings determine the user account that the Analysis Services service uses when connecting to the underlying source of data using Windows Authentication. The appropriate setting depends upon how this data source is being used. For more information about default impersonation settings, see New Database Dialog Box (Analysis Services) and Impersonation Information Dialog Box (Analysis Services - Multidimensional Data).
OLAP Objects
You can configure the Analysis Services service to use the credentials of its service account when it connects to the underlying data source for performing OLAP processing or to use a specified account user account that has appropriate permissions. While the service account is most often used, specifying a user account for the data source impersonation account enables you to run the Analysis Services account under a least privileges account and then specify the impersonation account for each data source that has that appropriate privilege level in each source database. This impersonation account may have higher privileges than the service account itself, and any exploits of the Analysis Services service won't have the ability to do much harm.
However, Analysis Services does not support impersonation of the current user for object processing. As a result, if you choose Use the credentials of the current user setting for the data source object, you will receive an impersonation mode error during processing.
Data Mining Objects
Data mining queries may be executed in the context of the Analysis Services service account, but may also be executed in the context of the user submitting the query or in the context of a specified user. The context in which a query is executed may affect query results. For data mining OPENQUERY type operations, you may want the data mining query to execute in the context of the current user or in the context of a specified user (regardless of the user executing the query) rather than in the context of the service account. This enables the query to be executed with limited security credentials. If you want Analysis Services to impersonate the current user or to impersonate a specified user, select either the Use a specific user name and password or Use the credentials of the current user option.