Other MDX Entities: Perspectives - Page 4

Procedure:  Satisfy Business Requirements with MDX

Let's assume, for purposes of our practice example, that we have received a request from representatives of our client, the Adventure Works organization. Having implemented the integrated Microsoft business intelligence solution, including MSSQL Server, Analysis Services, Integration Services, Reporting Services, and other components for the client earlier in the operating year, we are often called upon to assist in the continuing rollout of the various components throughout the organization.

In the present case, a group of report authors in the Operations department has requested assistance with using Perspectives, of which it has become aware through "train the trainer" events we have recently performed covering the implementation and maintenance of Analysis Services.  The Operations authors are aware that the particular need that they are currently expressing will manifest itself in recurring situations as they work to meet the daily requirements of the Adventure Works information consumers, as well as to support business requirements gathering efforts as Analysis Services is implemented throughout other operating entities of the organization.  The authors simply want to know how to use the Perspectives within their MDX queries, most of which will ultimately find a home within Reporting Services as reports are designed and deployed).

In a brief discussion with members of the Operations group, we point out the location and construction of the Perspectives (as we have in the sections above) that exist within the Adventure Works cube, using the Perspectives tab of the Cube Designer to show both the physical location and the design of the Perspectives.  Our next steps will be to provide practice with the use of Perspectives as query targets, so as to enable the authors to craft MDX queries for various analysis and reporting needs. 

We convince the authors that they might best become familiar with the uses and limitations of Perspectives through the construction and execution of queries that illustrate their characteristics. 

Procedure:  Demonstrate that Only Measures Included within a Perspective Can Be Directly Selected within an MDX Query which Targets That Perspective

Let's construct a simple MDX query as a "starting point" for our subsequent work with Perspectives. Our initial objective is, of course, to simply craft a query that targets a Perspective.

1.  Type (or cut and paste) the following query into the Query pane:

--MDX046-001 Basic MDX Query Targeting a Perspective of 
   -- the Adventure Works Cubes;  All Available Measures
SELECT
   
   {[Measures].MEMBERS} ON AXIS(0),
   
   {[Account].[Accounts].[Account Level 02].MEMBERS} ON AXIS(1)
   
FROM
    [Finance]
WHERE
    ([Date].[Fiscal].[Fiscal Year].[FY 2003])

The Query pane appears, with our input, as shown in Illustration 16.

The above query sets the stage for a more thorough appreciation of the potential value we obtain from Perspectives.  In this instance, we have targeted the Finance Perspective via the FROM clause of the query, specifying, within the row axis, all members of Account Level 02 of the Account dimension of the Adventure Works cube. We have also specified, within the column axis, all measure members for the Perspective.

As we can see from an examination of the Perspectives tab in the Cube Designer, the Finance Perspective includes only three measures, Amount, Average Rate, and End of Day Rate.  We see only these measures selected for inclusion within the Finance Perspective column, as depicted in Illustration 17.

We also note, in the dimension section of the Perspectives tab for the Finance Perspective, that the entire Account dimension is selected for inclusion (among others), as shown in Illustration 18.

The Results pane is populated by Analysis Services, and the dataset, as shown in Illustration 20, appears.

The basic query returns what we might expect, with the Finance Perspective targeted via the FROM clause.  We see that only the measures included within the Perspective appear.  Measures not included within the Perspective cannot be directly selected within a basic query, either by using [Measures].MEMBERS or by explicitly specifying a measure that is excluded from the Perspective.  (We obtain an empty results dataset if we attempt the latter within a query.)

Our client colleagues express an understanding of the points we have illustrated in using a Perspective within our initial MDX query. We will continue working with the query we have already constructed in the next procedure.