Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 15, 2006

Mastering OLAP Reporting: Reporting with Analysis Services KPIs - Page 3

By William Pearson

Ascertain Connectivity of the Shared Relational Data Source

Let's first ensure we have a working shared data source. 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 as a server / instance combination, versus a server name alone (the default for the Adventure Works DW project sample's connection is localhost).

1.  Double-click Adventure Works.ds, within the Data Sources folder seen in Solution Explorer.

The Data Source Designer opens, defaulted to the General tab, and appears with default settings as shown in Illustration 10.

Illustration 10: The Data Source Designer with Default Settings ...

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

The Connection Manager opens, and appears with default settings depicted in Illustration 11.

Illustration 11: The Connection Manager with Default Settings ...

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 the case of many of our installations, the requirement here 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 whether we need to make this change).

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

Illustration 12: The Connection Manager, with Corrected Settings ...

4.  Ensure that authentication settings are correct for the local environment.

5.  Click the Test Connection button.

A Connection Manager message box appears, indicating that the Test connection succeeded, assuming that our changes (or lack of same, as appropriate) are proper. The message box appears as depicted in Illustration 13.

Illustration 13: Testing Positive for Connectivity ...

6.  Click OK to dismiss the message box.

7.  Click OK to accept changes, as appropriate, and to dismiss the Connection Manager.

The Data Source Designer appears, with our modified settings, similar to that shown in Illustration 14.

Illustration 14: The Data Source Designer with Modified Settings ...

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

Deploy the Analysis Services Project

Before going further, let's deploy our Analysis Services project to ensure that we are all "in the same place" with regard to its status. This will ensure issue-free access for Reporting Services as we progress within our practice preparation and procedures.

1.  Right-click the Adventure Works DW Analysis Services project in the Solution Explorer.

2.  Select Deploy from the context menu that appears, as depicted in Illustration 15.

Illustration 15: Initiating Analysis Services Project Deployment ...

The Build and Deployment processes get underway, as we note the Deployment Progress window appears (by default underneath the Solution Explorer, in the bottom right corner of the development environment.)

NOTE: Be sure to properly configure Properties of the Analysis Services project to ensure that these processes can occur. For more information on configuration, and guidance for settings in the local environment, see the appropriate references in the MSSQL Server 2005 Books Online.

The Build and Deployment processes continue, as each event is logged in the Deployment Progress window. Finally, the processes complete, and we see the Deployment Completed Successfully status announced, as shown in Illustration 16.

Illustration 16: Successful Deployment is Indicated

We are now ready to "clone" a sample report project and proceed with the practice exercise. To prepare for this, we will add a new Report Server project to our existing solution.

Add a New Reporting Services Project

Much in the same manner that we cloned the Adventure Works project, we will add a copy of an existing Report Server project, together with its sample reports, primarily to save time in getting to the focus of our session, working with KPIs within Reporting Services

1.  Right-click the Solution 'Adventure Works level, atop the tree within the Solution Explorer.

2.  Select Add --> Existing Project ... from the cascading context menus that appear, as depicted in Illustration 17.

Illustration 17: Adding a Project to the Solution ...

The Add Existing Project dialog appears.

3.  Navigate to the actual location of the AdventureWorks sample reports.

The reports are installed, by default (and, therefore, subject to be installed in a different location on our individual machines), in the following location

C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\

4.  Right-click the AdventureWorks Sample Reports folder within the Reports Samples folder.

5.  Select Copy from the context menu that appears, as shown in Illustration 18.

Illustration 18: Copying the Sample Reports Folder ...

6.  Navigate back into the RS029 MSAS KPIs in ReportServices folder, which contains the Analysis Services project clone we created earlier.

7.  Right-click the area within the folder, as we did earlier.

8.  Select Paste from the context menu that appears, as depicted in Illustration 19.

Illustration 19: Pasting the Sample Folder in the RS029 MSAS KPIs in ReportServices Folder

The AdventureWorks Sample Reports folder appears, via the Add Existing Project dialog, in the new location, as shown in Illustration 20.

Illustration 20: AdventureWorks Sample Reports Folder in Its New Location ...

9.  Click the AdventureWorks Sample Reports folder to open it.

10.  Select the AdventureWorks Sample Reports.rptproj file that appears, as depicted in Illustration 21.

Illustration 21: Select the AdventureWorks Sample Reports.rptproj File

11.  Click Open to add the Report Server project to our solution.

The AdventureWorks Sample Reports project appears, with associated objects, in Solution Explorer, as shown in Illustration 22.

Illustration 22: The New Solution, Project and Objects within the Solution Explorer

While we have saved many steps with our cloning approach, we still have to ascertain connectivity with the Analysis Services data source, just as we did with the relational data source earlier.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM