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 the situations, where we have to use multiple databases then we use multiple 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. Following are some of the major 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 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 check mark the option Don’t show this page again 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 can select them here.
Click on the new button from the above screenshot will open another window form of Connection Manager 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 local database so we are using windows credentials but 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 used by SSAS 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 to specify Windows account credentials. These credentials will be used by SSAS to perform operations like source data access, processing etc. Usually every organization provide these details.
- Use the service account: When this option is selected, SSAS uses the credentials of the service account under which the Analysis Services service is configured/running for source data access, processing etc. (Start – search bar – services – MSSQL server – in general it will run under local system so the service account will take the local system as service account. Which won’t work if the server is in other location.
- Use the credentials of the current user: When this option is set, SSAS uses the credentials of the current user for performing operations like DMX Queries, accessing cubes etc. This option cannot be used for performing server side operations like data access, processing etc. Because current user credentials are used to access his computer only. But to access the database located in 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 database. So, it will use the same impersonation option as the database using. When this option is set, 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 solution explorer for newly created data source
Thank you for Visiting Our Blog