Edit

Lesson 1-2 - Defining a Data Source

  • Article

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

After you create an SQL Server Analysis Services project, you generally start working with the project by defining one or more data sources that the project will use. When you define a data source, you are defining the connection string information that will be used to connect to the data source. For more information, see Create a Data Source (SSAS Multidimensional).

In the following task, you define the AdventureWorksDW2019 sample database as the data source for the project. While this database is located on your local computer for the purposes of this tutorial, source databases are frequently hosted on one or more remote computers.

To define a new data source

  1. In Solution Explorer (on the right of the Microsoft Visual Studio window), right-click Data Sources, and then click New Data Source.

  2. On the Welcome to the Data Source Wizard page of the Data Source Wizard, click Next to open the Select how to define the connection page.

  3. On the Select how to define the connection page, you can define a data source based on a new connection, based on an existing connection, or based on a previously defined data source object. In this tutorial, you define a data source based on a new connection. Verify that Create a data source based on an existing or new connection is selected, and then click New.

  4. In the Connection Manager dialog box, you define connection properties for the data source. In the Provider list box, verify that Native OLE DB\SQL Server Native Client 11.0 is selected.

    SQL Server Analysis Services also supports other providers, which are displayed in the Provider list.

  5. In the Server name text box, type localhost.

    To connect to a named instance on your local computer, type localhost\<instance name>. To connect to the specific computer instead of the local computer, type the computer name or IP address.

  6. Verify that Use Windows Authentication is selected. In the Select or enter a database name list, select AdventureWorksDW2019.

  7. Click Test Connection to test the connection to the database.

  8. Click OK, and then click Next.

  9. On the Impersonation Information page of the wizard, you define the security credentials for SQL Server Analysis Services to use to connect to the data source. Impersonation affects the Windows account used to connect to the data source when Windows Authentication is selected. SQL Server Analysis Services does not support impersonation for processing OLAP objects. Select Use the service account, and then click Next.

  10. On the Completing the Wizard page, accept the default name, Adventure Works DW 2019, and then click Finish to create the new data source.

Note

To modify the properties of the data source after it has been created, double-click the data source in the Data Sources folder to display the data source properties in Data Source Designer.

Next Task in Lesson

Defining a Data Source View

See Also

Create a Data Source (SSAS Multidimensional)