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.
» 
Comment and Contribute