SQL Server Analysis Service (SSAS): Part 3

Create Data Source

The very first task of creating an analysis project is to create a data source. The data source contains all the information, like the data provider name, server name, database name, and credentials for connecting with the database that analysis services need to connect to the source database.

Analysis services can read data from SQL Server, Microsoft Access, Teradata, DB2, Oracle, and so on. So for Analysis Services to read data, the data must be in a relational database. When we create a database, BIDS by default uses the SQL Server Native Client data provider. You can change that depending on your database. If you want to see the complete list of data source types then you can find that here.

Now without going into too many theoretical details, I will proceed directly to how to create the data source.

I will now explain how to create a data source in the same SSAS project that we created in my previous article. The procedure for creating a data source is as in the following:

1. In the project's Solution Explorer right-click the data source folder and select "New data source"

2. Now you will get the data source wizard; click on "Next"

3. You will get a "Define the connection" screen that will prompt for the creation of the connection so if you have an existing connection then it will show, otherwise if you are creating it the first time then it will be blank.

4. So to create a new connection, click on "New"; the Connection Manager dialog box will appear

5. Enter the server name: localhost

6. Select database AdventureWorksDW2008R2 from the "Select a Database name" listbox. So your Connection Manager dialog box will look as in the following:

7. Click on "Test Connection"; if the dialog box shows the message "Test connection succeeded" then that means the connection can be established successfully with the information provided otherwise check all the information again.

8. Click "Ok" to close the dialog box.

9. Click "Next" in the data source wizard.

10. Now you will get an Impersonation information page. Here we will provide the credentials that Analysis Services will use to connect with the data source.

    There are four options

    • In Option 1 you need to specify a Windows account's username and password to connect to the data source

    • In Option 2 Analysis Services uses its service logon User ID to connect to the data source

    • In Option 3 it uses the current user Credentials to connect to the data source

    • In Option 4 it uses impersonation information contained in the analysis services DataSourceImpersonation data source property

11. Select Option 2, which is "Use the Service Account".

12. Provide the Data Source name SSAS2008R2DS and click on "Finish" to complete the wizard.

13. Now you can check your data source in Solution Explorer:

We have created the data source successfully and now we need to create the data source view that I will explain in my next article. Thanks for reading.

View or Edit Connection Properties of Data Source

Suppose you have created a data source and later on if you want to change the connection string or other properties then you can change that using the following:

1. Double-click the data source in Solution Explorer.

2. Now you will see the following Data Source Designer Window.

3. Click on "Edit" and you will see the same connection manager that you saw during creation of the data source, so in the same way you can change the connection properties.

4. After changing the properties click on "Test Connection" and ensure that using the new properties, the connection is established successfully.

Summary

This article taught us about how to creating an analysis project is to create a data source.

Reference

• SQL Server Analysis Service (SSAS): Part 1
• SQL Server Analysis Service (SSAS): Part 2