Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 14, 2006

Other MDX Entities: Perspectives - Page 5

By William Pearson

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
   [Measures].[Sales Order Count]
   [Measures].[Order Count]
   {[Measures].[Sales Order Count]} ON AXIS(0),
   { [Account].[Accounts].[Account Level 02].MEMBERS} ON AXIS(1)
    ([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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM