In an Analysis Services multidimensional model, a data source object represents a connection to the data source (Database or Data warehouse) from which you are processing (or importing (Or load)) data. A multidimensional model must contain at least one data source object, 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 and it is the combination of Provider, Server Name, Database Name and Impersonation Information.
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.
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.
Server Name is the network name of the Database Engine instance. It can be specified as the IP address, the NETBIOS name of the computer, or a fully qualified domain name. If the server is installed as a named instance, you must include the instance name (for example, <computername>\<instancename>).
Log on to the Server specifies how the connection will be authentication. Use Windows Authentication uses Windows authentication. Use SQL Server Authentication specifies a database user login for a Windows Azure SQL databases or a SQL Server instance that supports mixed mode authentication.
Connection Manager includes a Save my password checkbox for connections that use SQL Server authentication. Although the checkbox is always visible, it is not always used.
Select or enter a database name or Attach a database file are used to specify the database.
In the left side of the dialog box, click All to view additional settings for this connection, including all default settings for this provider. Change settings as appropriate for your environment and then click OK.
The new connection appears in the Data Connection pane of the Select how to define the connection page of the Data Source Wizard.
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 options are available in SSAS:
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 Open Queries, Local cubes etc. This option cannot be used for performing server side operations like source data access, processing etc.
Inherit: This option let's the SSAS server decide which impersonation mode is suitable for each type of operation. When this option is set, by default SSAS will use the service account for operations like processing and the credentials of the current user for operations like Local cubes, querying the data mining models, etc...
1. In SQL Server Data Tools, double-click the data source object in Solution Explorer.
2. Click Edit, and then click All on the left navigation pane.
3. The property grid appears, showing available properties of the data provider you are using.
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.
Click Next
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.
Click ok
Click Next to configure Impersonation settings
Impersonation Options Available in SSAS:
Data Source in SSAS contains the connection information and it is the combination of Provider, Server Name, Database Name and Impersonation Information.
Choose a Data Provider:
SSAS Supports both Microsoft .NET Framework or native OLE DB provider. The recommended data provider for SQL Server data sources is SQL Server Native Client because it typically offers better performance.
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.
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.
Server Name:
Server Name is the network name of the Database Engine instance. It can be specified as the IP address, the NETBIOS name of the computer, or a fully qualified domain name. If the server is installed as a named instance, you must include the instance name (for example, <computername>\<instancename>).
Log on to the Server specifies how the connection will be authentication. Use Windows Authentication uses Windows authentication. Use SQL Server Authentication specifies a database user login for a Windows Azure SQL databases or a SQL Server instance that supports mixed mode authentication.
Connection Manager includes a Save my password checkbox for connections that use SQL Server authentication. Although the checkbox is always visible, it is not always used.
Database Name:
Select or enter a database name or Attach a database file are used to specify the database.
In the left side of the dialog box, click All to view additional settings for this connection, including all default settings for this provider. Change settings as appropriate for your environment and then click OK.
The new connection appears in the Data Connection pane of the Select how to define the connection page of the Data Source Wizard.
Impersonation Information:
When we design a cube in which data source is using windows authentication then it will be executed as current user. But, when you deploy the database to the server there will be no current user. In this case when a user requests AS to process an object it needs to know under what security context to connect to the data source. Essentially you need to get the AS service to impersonate another user in order to retrieve data from an external source on a user’s behalf.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 options are available in SSAS:
Use a specific Windows user name and password: This option lets you to specify Windows account credentials which will be used by SSAS to perform operations like source data access, processing etc.
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. 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 Open Queries, Local cubes etc. This option cannot be used for performing server side operations like source data access, processing etc.
Inherit: This option let's the SSAS server decide which impersonation mode is suitable for each type of operation. When this option is set, by default SSAS will use the service account for operations like processing and the credentials of the current user for operations like Local cubes, querying the data mining models, etc...
View or Edit Connection Properties
To edit the connection string1. In SQL Server Data Tools, double-click the data source object in Solution Explorer.
2. Click Edit, and then click All on the left navigation pane.
3. The property grid appears, showing available properties of the data provider you are using.
Creating Data Source in SSAS
After creating the New multidimensional project (Analysis Services project), the solution explorer looks like belowTo 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.
Click Next
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.
Click ok
Click Next to configure Impersonation settings
Impersonation Options Available in SSAS:
