Understanding data sources

In order to get started with the development of a cube, a connection has to be done to the underlying data source. SQL Server Data Tools contains a small wizard that will help users to create the correct type of connection.

To connect to the source, you need to specify the provider that should be used as well as the connection string needed to connect to the source. Analysis Services can be used against several different databases apart from SQL Server; in fact, it is one of the most common OLAP engines used with databases such as Oracle and DB2. To connect to a non-Microsoft database, you often need to install special drivers to be able to connect to the database.

When connecting to a Microsoft SQL Server database, there are several options available out of the box. By default, the providers are installed on a Windows system. However, you should use the SQL Server Native Client provider as this gives you the best performance when connecting to the SQL Server.

If you connect to third-party databases, make sure that you use OLEDB providers and not .NET providers as they have the best performance. Analysis Services is written in C++, and if you use a .NET provider, the process needs to switch from unmanaged code to managed code for every request that it does to the .NET provider.

When you define your connection, there are some important settings that you need to consider. The first one is how the server should authenticate to the data source. If you specify that you want to use Windows credentials, you have to decide which Windows account should be used.

In the preceding screenshot, the first option is to specify the Windows username and password that will be used when connecting to the data source. The second option is to use the service account; this is the account that starts the Analysis Services service. In many cases, this is what you use in production scenarios. If this is the option that you decide to go with, you need to create a database user for this account as well. Make sure that is of least privilege both from a Windows and database standpoint.

The third option will use the credentials of the current user, this is not supported for multidimensional databases except when you are dealing with local cubes and out-of-line bindings.

Inherit is a useful option. If you have several connections in your cube and you want to maintain the connection information in one place, you can define it on the database level in your project. The database is something that you do not see by default when you open your project in SQL Server Data Tools. But if you click on the Show All Files button in Solution Explorer, you will see a new file in your project that you can open. This file contains information that is common to all objects within the project. Here you can define security information that will be inherited by all other data source objects in your project. This is defined in the property called <DataSourceImpersonationInfo>.