The Data source in SQL Server Analysis Services (DS) is a connection to the database or data warehouse from which we import (load) required data. Every Analysis services project must have at least one data source to work. If we have situations where we have to use multiple databases, then we use multiple SSAS data sources. In general, most of the times, we may work with 1 or 2 data sources.
Data Source in SSAS contains the connection information. It is a combination of Provider, Server Name, Database Name, and Impersonation Information.
SSAS Supports both .Net and OLE DB Providers. The following are some of the primary data sources supported by SSAS: SQL Server, MS Access, Oracle, Teradata, IBM DB2, and other relational databases with the appropriate OLE DB provider.
Creating Data Source in SSAS
After creating the New multidimensional project (Analysis Services project), the solution explorer looks like below
To create a data source, Right-click on the Data Sources folder present in the solution explorer and select the New Data Source option from the context menu
It will open the Data source wizard with the welcome page. If you don’t want to see this welcome page again, checkmark the Don’t show this page again option below.
If you observe the above, Data Connections pane is empty because we have no connection managers before. If we created any connection managers before then instead of creating them, again and again, we could select them here.
Click on the new button from the above screenshot will open another window form of Connection Manager. Use it to select the Provider, Server Name and Database Name.
From the above screenshot, you can observe that we are using our localhost windows account as server name and [AdventureWorksDW2014] as the database name.
NOTE: Here, we are working with the local database. So we are using windows credentials. Still, in real-time, you have to select the SQL Server authentication and provide the credentials given by the database admin people or your team leader.
Click on the Test Connection button provided below to check whether the connection is successful or not.
Press the Next button to configure Impersonation settings
Impersonation Options Available in SSAS Data Source
Impersonation allows SSAS to assume the identity/security context of the client application, which is to perform the server-side data operations like data access, processing, etc. As part of impersonation, the following impersonation options are available in SSAS:
- Use a specific Windows user name and password: This option lets you specify Windows account credentials. These credentials will be used by SSAS to perform operations like source data access, processing, etc. Usually, every organization provides these details.
- Use the service account: SSAS uses the service account credentials, under which the Analysis Services service configured/running for source data access, processing, etc. Start – search bar – services – MSSQL server. In general, it will run under a local system. So the service account will take the local system as a service account, which won’t work if the server is in another location.
- Use the credentials of the current user: SSAS uses the credentials of the current user for performing operations like DMX Queries, accessing cubes, etc. This option cannot apply for performing server-side operations like data access, processing. Because existing user credentials are used to access his computer only. But to access the database located on the server, we need separate credentials given by database admin.
- Inherit: This option allows to use the impersonation option of the parent object. For any data source, the parent object will be the database. So, it will use the same impersonation option as the database user. If you set this option, by default SSAS will use the service account for operations like processing and for the operations like querying local cubes, querying the data mining models, etc., it will use the credentials of the current user
Click on finish button and check the SSAS solution explorer for a newly created data source