Other MDX Entities: Perspectives - Page 5

August 14, 2006

Procedure:  Demonstrate that Perspectives Do Not Prevent Selection of Dimensions that Lie Outside the Perspective Design

We continue our exploration of querying Perspectives by informing the Operations representatives with which we are working of a significant difference in behavior that occurs with regard to dimensions.  Unlike the results we noted when specifying excluded measures (that is, direct selection of a measure excluded from the Perspective returns an empty results dataset), we are not similarly restricted by the Perspective in querying dimensions. 

--MDX046-002 MDX Query Targeting a Perspective of 
  -- the Adventure Works Cubes;  Specify Excluded Dimension

{[Account].[Accounts].[Account Level 02].MEMBERS} ON AXIS(1)

[Product].[Product Categories].[All Products] ON AXIS(1)

A quick review of the Perspective tab in the Cube Designer makes it clear that the Product dimension is excluded from the Finance Perspective.  The result of our change, of course, is to intentionally select a dimension that is not included within the Perspective. Once we have accomplished this simple modification, the Query pane appears as depicted in Illustration 21.

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

The fact that the Product dimension, which we know to be excluded from the Finance Perspective, is returned per our row axis specification in the query above makes it clear that we are able to query "around" the Perspective to retrieve dimensions within the underlying cube.  This perhaps serves as an emphasis that, while Perspectives afford useful "focuses" that are great ways to "narrow" visual displays within the Cube Browser and perhaps some client applications, the use of Perspectives to support any form of security / other access control is not viable, as various cube objects can be queried directly from the cube with which the Perspective is associated.

The Operations authors express satisfaction with the results, and confirm their understanding, so far, of the points we have illustrated surrounding the use of Perspectives in MDX queries. They then pose an additional consideration, which we will take up in the final procedural section that follows.

Procedure:  Demonstrate a Means of Selecting Measures Excluded from a Perspective which is the Target of an MDX Query

Our client colleagues next pose a question: "Since we can pull even excluded dimensions into the query to return the associated data, regardless of the object membership within the Perspective we name in the FROM clause, how might we 'go around' the Perspective to retrieve measures that are not included in the Perspective design?"  We answer that, to achieve this, we must add a calculation to embody the measure under consideration.

Let's say that, as an example, the authors wish to see the Sales Quota measure, a member of the Sales Orders Measures Group - which we know to be excluded from the Finance Perspective - within a query similar to the one we crafted in our first example.

We could add the calculation via the Cube Designer, but for purposes of our exercise, we will do so using the WITH clause within our MDX query.  We will begin with a new query.

A new tab, with a connection to the Adventure Works cube (we can see it listed in the selector of the Metadata pane, once again) appears in the Query pane.

--MDX046-003 MDX Query Targeting a Perspective of 
-- the Adventure Works Cubes;  Specify Excluded Measure
WITH
MEMBER
   [Measures].[Sales Order Count]
   
AS
   [Measures].[Order Count]
 
SELECT
   
   {[Measures].[Sales Order Count]} 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 24.

The Results pane is, once again, populated by Analysis Services.  This time, the dataset depicted in Illustration 25 appears.

In the returned dataset, we see that the excluded measure, Sales Quota, is returned via the calculation.  (For the measure to be more meaningful, of course, we might perform some realignment via the Dimension Usage tab of the Cube Designer, as well as perhaps elsewhere).

We have demonstrated the means of retrieving an excluded member when targeting the Perspective from which it is excluded in our MDX query.

The client representatives inform us that their immediate goals have been met, and that the examples we have shared have illustrated the principles of operation behind Perspectives within MDX queries.

Summary ...

In this article, we extended our examination of MDX to concentrate upon the use of Perspectives, as well as considerations that arise when we leverage them within our respective business environments.  As a part of introducing the concepts behind Perspectives, we prepared for our practice session by examining the structure of existing Perspectives within the Adventure Works DW Analysis Services Database.

After introducing our subject matter, we examined the syntax with which we employ Perspectives within MDX queries.  We next undertook illustrative examples whereby we put a Perspective to work, to gain some hands-on practice with the concepts we had explored.  Throughout our practice session, we briefly discussed the results datasets we obtained from each of the queries we constructed or modified.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.