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. 

1.  Replace the comment line in query MDX046-001 with the following:


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

2.  Select File -à Save MDX046-001.mdx As ..., name the file MDX046-002.mdx, and place it in the same location as its predecessor, to protect the former query.

3.  Replace the fifth row (the row axis definition of the query), currently:

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

with the following:

[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.


Illustration 21:  "Adjusted" Query in the Query Pane (Modifications Circled)

4.  Execute the query by clicking the Execute button in the toolbar, as before.

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


Illustration 22:  Results Dataset (Partial View) – Excluded Dimension Specified

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.

5.  Select File -à Save MDX046-002.mdx to ensure that that the file is saved.

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.

1.  Select File --à New from the main menu.

2.  Select Query with Current Connection from the cascading menu that appears next, as depicted in Illustration 23.


Illustration 23:  Create a New Query with the Current Connection ...

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.


Illustration 24:  Our Initial Query in the Query Pane ...

4.  Execute the query by clicking the Execute button in the toolbar.

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


Illustration 25:  Results Dataset –Duplicate in Evidence (Circled)

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).

5.  Select File -à Save MDXQuery2.mdx As ..., name the file MDX046-003.mdx, and place it in the same location used to store the earlier queries.

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

6.  Select File -à Exit to leave the SQL Server Management Studio, when ready.

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.

7.  Select File -à Exit, when ready, to leave the Business Intelligence Development Studio.

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.

MDX Essentials Series
The LEVEL_NUMBER Member Property
The LEVEL_UNIQUE_NAME Intrinsic Member Property
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
Further Combination of BottomCount() with Other MDX Functions
Combine BottomCount() with Other MDX Functions to Add Sophistication
Basic Set Functions: The BottomCount() Function, Part I
Intrinsic Member Properties: The MEMBER_VALUE Property
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Intrinsic Member Properties: The MEMBER_NAME Property
Intrinsic Member Properties: The MEMBER_KEY Property
Intrinsic Member Properties: The MEMBER_CAPTION Property
Set Functions: The StripCalculatedMembers() Function
Set Functions: The AddCalculatedMembers() Function
MDX Numeric Functions: The Min() Function
MDX Numeric Functions: The Max() Function
Set Functions: The .AllMembers Function
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
String Functions: The .Properties Function, Part II
String Functions: The .Properties Function
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
MDX Scripting Statements: Introducing the Simple CASE Statement
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
MDX Clauses and Keywords: Use HAVING to Filter an Axis
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Logical Functions: IsSibling(): Conditional Logic within Calculations
MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
MDX Numeric Functions: The .Ordinal Function
Other MDX Entities: Perspectives
MDX Operators: The IS Operator
MDX Set Functions: The Distinct() Function
MDX Set Functions: The ToggleDrillState() Function
Set Functions: The DrillUpLevel() Function
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
MDX Set Functions: DrillDownLevel()
MDX Set Functions: The DRILLUPMEMBER() Function
MDX Essentials: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
MDX Essentials : Set Functions: The DRILLDOWNMEMBER() Function
MDX Essentials: Drilling Through with MDX: The DRILLTHROUGH Statement
MDX Essentials: String Functions: The .UniqueName Function
MDX Essentials: String Functions: The .Name Function
MDX Essentials: String / Numeric Functions: The CoalesceEmpty() Function
MDX Essentials: Basic Set Functions: The TopCount() Function, Part II
MDX Essentials: Basic Set Functions: The TopCount() Function, Part I
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
MDX Essentials: Set and String Functions: The GENERATE() Function
MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks
MDX Essentials: String / Numeric Functions: More on the IIF() Function
MDX Essentials: String / Numeric Functions: Introducing the IIF() Function
MDX Essentials: Logical Functions: The IsEmpty() Function
MDX Essentials: Basic Set Functions: The EXTRACT() Function
MDX Essentials: Numeric Functions: Introduction to the AVG() Function
MDX Essentials: Basic Member Functions: The .Item() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Subset() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Tail() Function
MDX Essentials: Basic Set Functions: Subset Functions: The Head() Function
MDX Essentials: Basic Set Functions: The CrossJoin() Function
MDX Essentials: Basic Numeric Functions: The Count() Function
MDX Essentials: Basic Set Functions: The Filter() Function
MDX Essentials: Basic Set Functions: The EXCEPT() Function
MDX Essentials: Basic Set Functions: The Intersect() Function
MDX Essentials: Basic Set Functions: The Union() Function
MDX Essentials: Basic Set Functions: The Order() Function
MDX Essentials - MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
MDX Essentials - MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
MDX Essentials: MDX Member Functions: "Relative" Member Functions
MDX Member Functions: The Cousin () Function
MDX Essentials: Member Functions: More "Family" Functions
MDX Member Functions: The "Family" Functions
MDX Essentials: MDX Members: Introducing Members and Member
MDX Essentials : MDX Operators: The Basics
MDX Essentials: Structure of the MDX Data Model
MDX at First Glance: Introduction to SQL Server MDX Essentials








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers