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 4

By William Pearson

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
   {[Measures].MEMBERS} 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 16.

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

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.

Illustration 17:  Measures Section, Finance Perspective within the Cube Designer (Compressed View)

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.

Illustration 18:  Dimension Section, Finance Perspective within the Cube Designer (Compressed View)

2.  Execute the query by clicking the Execute button in the toolbar, as depicted in Illustration 19.

Illustration 19:  Click Execute to Run the Query...

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

Illustration 20:  Results Dataset – Perspective Targeted

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

3.  Select File -à Save MDXQuery1.mdx As ..., name the file MDX046-001, and place it in a meaningful location.

4.  Leave the query open for the next step.

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.

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