Mastering OLAP Reporting: Reporting with Analysis Services KPIs - Page 3
May 15, 2006
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.
2. Click the Edit button on the Data Source Designer dialog.
The Connection Manager opens, and appears with default settings depicted in Illustration 11.
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.)
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.
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.
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.
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.
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.
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.
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.
The AdventureWorks Sample Reports folder appears, via the Add Existing Project dialog, in the new location, as shown in Illustration 20.
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.
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.
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.