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 4

By William Pearson

Ascertain Connectivity of the Analysis Services Shared Data Source

Let's ensure we have a working shared data source to facilitate reporting from our Analysis Services cube, Adventure Works, for the same reason that we noted when completing this step for the relational data source: Because many of us will be running "side-by-side" installations of MSSQL Server 2000 and MSSQL Server 2005. Our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone, (the default for Analysis Services data source within the Adventure Works Sample Reports project is (local)). We may also need to "repoint" the data source to the Adventure Works Analysis Services database contained within our new solution.

1.  Double-click the second of the two shared data sources within the Adventure Works Sample Reports project, AdventureWorksAS.rds, as depicted in Illustration 23.

Illustration 23: Open the Shared Data Source Dialog for the Analysis Services Data Source ...

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

Illustration 24: The Shared Data Source Dialog with Default Settings ...

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

The Connection Properties dialog opens, and appears with default settings depicted in Illustration 25.

Illustration 25: The Connection Properties Dialog with Default Settings ...

We note that the default Server name is "(local)," once again. 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, again, 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 Properties dialog. (Mine is MOTHER1\MSSQL2K5, as before).

4.  In the Connect to a database section in the bottom half of the dialog, in the selector box labeled Select or enter a database name, select Adventure Works DW.

The Connection Properties dialog appears, with our setting modifications, as shown in Illustration 26.

Illustration 26: The Connection Properties Dialog with Modified Settings ...

5.  Ensure that authentication settings are correct for the local environment, as we did for the relational data source above

6.  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 depicted in Illustration 27.

Illustration 27: Testing Positive for Connectivity ...

7.  Click OK to dismiss the message box.

8.  Click OK to accept changes, and to dismiss the Connection Properties dialog.

The Shared Data Source dialog appears, with modified settings, similar to that shown in Illustration 28.

Illustration 28: The Shared Data Source Dialog with Modified Settings ...

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

We are now ready to modify a sample report and proceed with some exposure to KPIs in Reporting Services.

Modify the OLAP Report to Work with Our Cube

When we copied the installed sample reports into our new project folder, we insulated the originals from modification, to allow their continued use with the tutorial that ships with Reporting Services, as well as within other training endeavors. In this session, we will work only with the OLAP report member of the report set in our project, Sales Reason Comparisons.rdl.

Let's make some modifications to the report to render it more useful to our present focus. First, we will recreate the report and give it a new name. We will then be ready to concentrate on using KPIs within the report in a useful way.

1.  In Solution Explorer, right-click the Reports folder, within the AdventureWorks Sample Reports project that we added earlier

2.  Select Add from the context menu that appears.

3.  Select Existing Item ... from the context menu that cascades from the first, as depicted in Illustration 29.

Illustration 29: Select Add --> Existing Item ...

4.  Within the Add Existing Item dialog that appears (it should open to reveal the contents of our newly copied AdventureWorks Sample Reports folder), right-click the Sales Reason Comparisons.rdl file.

5.  Select Copy from the context menu that appears, as we have done with other objects in previous sections.

6.  Right-click elsewhere in the empty space of the folder.

7.  Select Paste, once again, to create a duplicate of the selected object.

8.  Right-click the new file, named Copy of Sales Reason Comparisons.rdl, by default.

9.  Select Rename from the context menu that appears, as we have done with other objects earlier.

10.  Rename the file to the following:

Analysis Services KPIs.rdl

11.  Select the newly renamed file within the Add Existing Item dialog.

12.  Click Add to add the new file to the AdventureWorks Sample Reports project in Business Intelligence Development Studio, as shown in Illustration 30.

Illustration 30: Adding the New Report to the Report Server Project ...

Analysis Services KPIs.rdl appears in the Solution Explorer.

13.  Double-click Analysis Services KPIs.rdl to open it.

Analysis Services KPIs.rdl opens in the design environment.

14.  Click the Preview tab within Report Designer, to ascertain proper connectivity and general operation of the new report file.

Analysis Services KPIs.rdl executes, and appears as depicted in Illustration 31.

Illustration 31: Analysis Services KPIs.rdl in Preview ...

Having determined that all is "wired correctly," we are ready to add KPIs to our report.

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