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 Aug 21, 2006

Mastering OLAP Reporting: Prototype KPIs in Reporting Services - Page 3

By William Pearson

Ascertain Connectivity of the Relational Data Source

Let's ensure we have a working 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 14.

Illustration 14: 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 15.

Illustration 15: 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 16.)

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

4.  Ensure that AdventureWorksDW is selected in the box labeled Select or enter a database name in the Connect to a database section of the Connection Manager dialog.

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

6.  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 17.

Illustration 17: Testing Positive for Connectivity ...

7.  Click OK to dismiss the message box.

8.  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 18.

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

9.  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. This will ensure issue-free access for Reporting Services as we progress within our practice preparation and procedures.

First, let's ensure alignment of the deployment server, as well as changing the name of the destination Analysis Services database, to prevent write over of the Analysis Services DW database if we have already installed it for education and testing purposes.

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

2.  Select Properties from the context menu that appears, as depicted in Illustration 19.

Illustration 19: Select Properties for the Project ...

The Properties Pages dialog opens.

3.  Within the left-hand pane, under Configuration Properties, select Deployment.

4.  In the Target section of the right-hand pane, ensure that the correct server / server instance combination is in place in the Server box.

5.  Within the Database section, replace the existing Database name, Adventure Works DW, with the following:

RS032_Adventure Works DW

The Properties Pages dialog appears, with our modified settings, similar to that shown in Illustration 20.

Illustration 20: The Properties – Deployment Page, with Modified Settings ...

6.  Click OK to accept changes and close the dialog.

We can now deploy the project.

7.  Right-click the Adventure Works DW Analysis Services project in the Solution Explorer, once again.

8.  Select Deploy from the context menu that appears, as depicted in Illustration 21.

Illustration 21: Initiating Analysis Services Project Deployment ...

9.  Click Yes on the dialog that appears next, asking if we would like to build and deploy the project first, as shown in Illustration 22.

Illustration 22: Click Yes to Rebuild and Deploy ...

Deployment completes, and the Process Database dialog appears, defaulted to Process Full.

10.  Click Run ... on the Process Database dialog that appears next, as depicted in Illustration 23.

Illustration 23: Click Run to Process the Database ...

The Process Progress viewer appears, indicating steps of processing as they transpire. Once processing finishes, we receive a Process Succeeded message in the Status bar at the bottom of the viewer, as shown in Illustration 24.

Illustration 24: "Process Succeeded" Message

11.  Click Close to dismiss the Process Progress viewer.

12.  Click Close to dismiss the Process Database dialog.

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

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