Mastering OLAP Reporting: Reporting with Analysis Services KPIs - Page 4
May 15, 2006
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.
The Shared Data Source dialog opens, defaulted to the General tab, and appears with default settings as shown in Illustration 24.
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.
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.
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.
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.
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.
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.
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.
Having determined that all is "wired correctly," we are ready to add KPIs to our report.