Procedure: Ascertain Connectivity of the Analysis Services Data Source

October 11, 2006

Objective:

Use this procedure to ensure connectivity of a project’s Shared Data Source with an Analysis Services database.

Background:

Many of us will be running “side-by-side” installations of MSSQL Server 2000 and MSSQL Server 2005. This means that our installation of the latter will need to be referenced, within a data source context, as a server / instance combination, versus a server name alone (the default for the AdventureWorks Sample Reports project sample’s connection is localhost).

From within the open Adventure Works Reports Sample project, in the SQL Server Business Intelligence Development Studio, take the following steps:

1. Double-click AdventureWorksAS.rds, within the Shared Data Sources folder seen in Solution Explorer.

The Shared Data Source dialog opens, and appears with default settings as depicted in Illustration 1.

2. Click the Edit button on the Shared Data Source dialog.

The Connection Properties dialog opens, and appears with default settings shown in Illustration 2.

We note that the default Server name is “localhost.” While this might prove an adequate setting for a PC with only MSSQL Server 2005 installed (default instance), in many of our environments, the requirement is for the server / instance combination that correctly identifies the correct MSSQL Server 2005 instance. (Clicking the Test Connection button at this point will provide confirmation as to whether we need to make this change).

3. If appropriate, type the correct server / instance name into the Server name box of the Connection Properties dialog. (Mine is MOTHER1\MSSQL2K5, as depicted in Illustration 3.)

4. Ensure that authentication settings are correct for the local environment.
5. Click the Test Connection button.

A message box appears, indicating that the Test connection succeeded, assuming that our changes (or lack of same, as appropriate) are appropriate. The message box appears as shown in Illustration 4.

6. Click OK to dismiss the message box.
7. Click OK to accept changes, as appropriate, and to dismiss the Connection Properties dialog.

The Shared Data Source dialog appears, with our modified settings, similar to that depicted in Illustration 5.

8. Click OK to close the Shared Data Source dialog, and to return to the development environment.