How to: Define a Data Source Using the Data Source Wizard

Use the Data Source Wizard in Business Intelligence Development Studio to define one or more data sources in a Microsoft SQL Server 2005 Analysis Services (SSAS) database or project. A data source can be defined as a new data source, can be based on an existing connection, or can be based on an existing object in the same project or solution.

To define a new data source

1. In Business Intelligence Development Studio, open the Analysis Services project or connect to the Analysis Services database in which you want to define the data source.

2. In Solution Explorer, right-click the Data Sources folder, and then click New Data Source.

   The Data Source Wizard opens.

3. On the Welcome to the Data Source Wizard page, click Next.

4. On the Select how to define the connection page, verify that Create a data source based on an existing or new connection is selected and then click New.

   The Connection Manager dialog box appears, with Native OLE DB\SQL Native Client selected in the Provider drop-down list.

5. To change the default provider, browse the provider list and select another provider.

6. Enter the information requested by the selected provider to connect to the underlying data source. If the Native OLE DB\SQL Native Client provider is selected, enter the following information:

   1. Type the server name in the Server name box.
   2. Specify the type of authentication for this data source in the Log on to the server section of the dialog box.
   3. Select either Select or enter a database name or Attach a database file and provide the requested information.
   4. In the left side of the dialog box, click All to view all of the settings for this connection, including all default settings for this provider.
   5. 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.

7. Click Next.

   The Impersonation Information page appears.

8. Click the option appropriate to the use of the data source. For example, for a data source used for OLAP processing, you would generally click Use a specific user name and password and for a data source used for data mining queries you click Use the credentials of the current user or Use a specific user name and password. If you click Default, the default impersonation setting for the database will be used (which is Use the service account by default). For more information on default database settings, see Configuring Database Properties in Business Intelligence Development Studio and New Database Dialog Box (Analysis Services).

9. Click Next.

   The Completing the Wizard page appears, and a default data source name is displayed in the Data source name box. You can type a different name, or you can use the default name. The default name is the name of the database or the database file that the connection uses. The Preview pane displays the connection string for this new data source.

10. Click Finish.

    The new data source appears in the Data Sources folder in Solution Explorer.

To define a data source based on an existing connection

1. In Business Intelligence Development Studio, open the Analysis Services project or connect to the Analysis Services database in which you want to define the data source.

2. In Solution Explorer, right-click the Data Sources folder, and then click New Data Source.

   The Data Source Wizard opens.

3. On the Welcome to the Data Source Wizard page, click Next.

4. On the Select how to define the connection page, verify that Create a data source based on an existing or new connection is selected.

5. Click a data connection in the Data Connections pane, and then click Next.

   The Impersonation Information page appears.

6. Click the option appropriate to the use of the data source. For example, for a data source used for OLAP processing, you would generally click Use a specific user name and password and for a data source used for data mining queries you click Use the credentials of the current user or Use a specific user name and password. If you click Default, the default impersonation setting for the database will be used (which is Use the service account by default). For more information on default database settings, see Configuring Database Properties in Business Intelligence Development Studio.

7. Click Next.

   The Completing the Wizard page appears, and a default data source name is displayed in the Data source name box. You can type a different name, or you can use the default name. The default name is the name of the database or the database file that the connection uses. The Preview pane displays the connection string for this new data source.

8. Click Finish.

   The new data source appears in the Data Sources folder in Solution Explorer.

To define a data source based on an existing data source in your solution

1. In Business Intelligence Development Studio, open the Analysis Services project in which you want to define the data source.

2. In Solution Explorer, right-click the Data Sources folder, and then click New Data Source.

   The Data Source Wizard opens.

3. On the Welcome to the Data Source Wizard page, click Next.

4. On the Select how to define the connection page, click Create a new data source based on another object, and then click Next.

   The Data sources from existing objects page appears.

5. Select Create a data source based on an existing data source in your solution. In the Data source drop-down list, select the data source to which you want the new data source synchronized.

6. Click Next.

   The Impersonation Information page appears.

7. Click the option appropriate to the use of the data source For example, for a data source used for OLAP processing, you would generally click Use a specific user name and password and for a data source used for data mining queries you click Use the credentials of the current user or Use a specific user name and password. If you click Default, the default impersonation setting for the database will be used (which is Use the service account by default). For more information on default database settings, see Configuring Database Properties in Business Intelligence Development Studio.

8. Click Next.

   The Completing the Wizard page appears, and a default data source name is displayed in the Data source name box. You can type a different name, or use the default name. The default name is the name of the database or the database file that the connection uses. The Preview pane displays the connection string for this new data source.

9. Click Finish.

   The new data source appears in the Data Sources folder in Solution Explorer.

10. Double-click the data source in the Data Sources folder in Solution Explorer.

    Notice that the Maintain a reference to another object in the solution check box is selected. Notice also that the contents of the Connection string text box appears dimmed, but that the connection string is the same as the original data source. You can only edit connection string properties in the original data source.

11. Click Cancel.

To define a data source based on an Analysis Services project in your solution

1. In Business Intelligence Development Studio, open the Analysis Services project in which you want to define the data source.

2. In Solution Explorer, right-click the Data Sources folder, and then click New Data Source.

   The Data Source Wizard opens.

3. On the Welcome to the Data Source Wizard page, click Next.

4. On the Select how to define the connection page, click Create a new data source based on another object and click Next.

   The Data sources from existing objects page appears.

5. Select Create a data source based on an Analysis Services Project and then, in the Project drop-down list, select the project to which you want the new data source synchronized.

6. Click Next.

   The Impersonation Information page appears.

7. Click the option appropriate to the use of the data source. For example, for a data source used for OLAP processing, you would generally click Use a specific user name and password and for a data source used for data mining queries you click Use the credentials of the current user or Use a specific user name and password. If you click Default, the default impersonation setting for the database will be used (which is Use the service account by default). For more information on default database settings, see Configuring Database Properties in Business Intelligence Development Studio.

8. Click Next.

   The Completing the Wizard page appears, and a default data source name is displayed in the Data source name box. You can type a different name, or use the default name. The default name is the name of the database or the database file that the connection uses. The Preview pane displays the connection string for this new data source.

9. Click Finish.

   The new data source appears in the Data Sources folder in Solution Explorer.

10. Double-click the data source in the Data Sources folder in Solution Explorer.

    Notice that the Maintain a reference to another object in the solution check box is selected. Notice also that the contents of the Connection string text box appears dimmed, but that the connection string is using the MSOLAP.3 provider with its Data Source property and Initial Catalog property acquired from the TargetServer and TargetDatabase properties of the source project. You can only edit connection string properties in the original data source, in this case by modifying the TargetServer and TargetDatabase properties of the source project.

11. Click Cancel.

See Also

Tasks

How to: Modify a Data Source in Data Source Designer
How to: Delete a Data Source in Solution Explorer

Concepts

Defining a Data Source Using the Data Source Wizard
Working with Remote Partitions

Other Resources

Working with Data Sources (Analysis Services)
Defining a Data Source
Working with Data Sources How-to Topics (SSAS)

Help and Information

Getting SQL Server 2005 Assistance