Other MDX Entities: Perspectives

Monday Aug 14th 2006 by William Pearson
Share:

Leverage Perspectives, new to Analysis Services 2005, through MDX.  BI Architect Bill Pearson leads hands-on practice with this means of visually focusing data retrieved from the UDM.

About the Series ...

This article is a member of the series, MDX Essentials. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each session progressively adding features and capabilities designed to meet specific real-world needs.

Virtually all of the MDX we have constructed in earlier articles can now be used in the SQL Server Management Studio, SQL Server Business Intelligence Studio, and various other areas within the Microsoft integrated Business Intelligence solution. In addition, much of what we construct going forward can be executed in the Analysis Services 2000 MDX Sample Application (assuming connection to an appropriate Analysis Services data source).  MDX as a language continues to evolve and expand:  we will focus on many new features in articles to come, while still continuing to examine business uses of MDX in general. The use of MDX to meet the real-world needs of our business environments will continue to be my primary concentration within the MDX Essentials series.

For more information about the series in general, as well as the software and systems requirements for getting the most out of its member lessons, please see Set Functions: The DrillDownMember() Function, where important information is detailed regarding the applications, samples and other components required to complete our practice exercises.

Overview

As I state in Mastering Enterprise BI: Introduction to Perspectives, a member article of my series Introduction to MSSQL Server Analysis Services at Database Journal, Analysis Services 2005 extends the concept of a cube, and the more "geometrical" basis upon which it once rested, with the concept of the Universal Dimension Model ("UDM").  A UDM provides a bridge / abstract layer between users and one or more physical data sources, and combines OLAP and relational realms.  Queries are, in turn, executed against the UDM through various client applications, providing the primary advantage of insulating the users from the multitudes of structural details that might exist within heterogeneous backend data sources. In addition to supplying a more intuitive data model with which to work, and enhanced performance for summary type queries, the UDM can also provide myriad additional benefits.

The characteristic that brings the UDM into our current field of consideration is its potential size and complexity.  As we noted in Mastering Enterprise BI: Introduction to Perspectives, the UDM allows for significant enrichment of the more basic user models we found in working with Analysis Services 2000, permitting business rules to be captured within it to support richer analysis, among much other sophistication.  Real-world models of enormous scope might realistically be defined, containing potentially scores of measures and dimensions, with each dimension including myriad attributes, as an illustration.  A single UDM can represent the contents of a complete data warehouse, with multiple Measure Groups in a cube representing multiple fact tables, and multiple dimensions based upon multiple dimension tables.

Such models can be very complex and powerful, but daunting to users who may only need to interact with a small part of a cube in order to satisfy their business intelligence and reporting requirements. For this reason, Analysis Services 2005 introduces "views" of the model, called Perspectives. Perspectives allow for the presentation of relevant subsets of the model to given groups of users, narrowing the model's focus to the measures, dimensions, attributes and so forth needed to support the groups in the accomplishment of their specific missions.

In Analysis Services 2005, we can use Perspectives to define these subsets of the model to provide focused, business-specific or application-specific viewpoints. The Perspective controls the visibility of objects, among which the following can be displayed or hidden:

  • Dimensions
  • Attributes
  • Hierarchies
  • Measure Groups
  • Measures
  • Key Performance Indicators (KPIs)
  • Calculations (Calculated Members, Named Sets, and Script Commands)
  • Actions

Perspectives represent subsets of our cubes, as we have stated, consisting of "views" of dimensions and measures that are relevant to the audiences for which they are created. In this article, we will extend 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.  Along with introducing the concepts behind Perspectives, this session will include:

  • Accessing the sample Adventure Works DW Analysis Services Database from within Business Intelligence Development Studio to examine existing Perspectives;
  • An examination of the syntax involved in using a Perspective within an MDX query;
  • Illustrative examples of uses of Perspectives within practice exercises;
  • A brief discussion of the results obtained within each of the practice examples.

Perspectives within MDX

Introduction

In Mastering Enterprise BI: Introduction to Perspectives, we discussed a simple illustration of a possible use for a Perspective within the Adventure Works cube, which makes its home in the Adventure Works DW sample Analysis Services 2005 database.  The sample ships with twenty-one cube dimensions and eleven Measure Groups, representing sales, sales forecasting and financial data.  While a client application might directly access the whole cube, such an all-encompassing viewpoint might overwhelm a consumer whose business needs are limited, say, to basic sales forecasting information.  Instead of subjecting the consumer to such an overload scenario, not to mention exposing data, perhaps, for which the consumer has no "need to know," we can implement a Sales Targets Perspective to narrow this consumer's view to the objects relevant to doing his job, providing support for forecasting sales.

It is important to remember, as we create and assign Perspectives within our local environments, that their purposes are to afford easier navigation, querying and other interaction with the cube.  Perspectives do not physically restrict access to cube objects, nor do they prevent direct referencing or retrieval of the objects through MDX, XML, or DMX statements, as we shall see, in many cases.  Because these viewpoints comprise read-only views of the cubes with which they are associated, users cannot change (rename, etc.), or modify the behavior or features of, cube objects through the use of Perspectives.

We will examine the simple MDX syntax for using a Perspective after our customary overview in the Discussion section that follows.  Following that, we will conduct practice examples within a couple of scenarios, constructed to support hypothetical business needs that illustrate uses for (and limitations of) Perspectives in general.  This will afford us an opportunity to explore some of the delivery options that Perspectives can offer the knowledgeable user.  Hands-on practice with Perspectives, where we will create queries that employ them as "sources", will help us to activate what we have learned in the Discussion and Syntax sections.

Discussion

To restate our initial explanation of their primary intended function, Perspectives allow us to designate portions or sections of the UDM for specific focus, which we can then use for access to the defined slice / section of the UDM.  As we noted earlier, we can include Dimensions, Attributes, Hierarchies, Measure Groups, Measures, and other objects within a Perspective – it really becomes a process of what, from the default set of "all objects" that we wish to eliminate from the Perspective. Perspectives can thus be likened to a "virtual subcube," or perhaps to a "view" of a UDM.  

To further illustrate the nature of a Perspective, let's take a quick look at several that have been created within the Adventure Works DW sample Analysis Services Database that ships with MSSQL Server 2005

NOTE:  The Adventure Works DW database and companion samples are not installed by default in MSSQL Server 2005. The samples can be installed during Setup, or at any time after MSSQL Server has been installed.

The topics "Running Setup to Install AdventureWorks Sample Databases and Samples" in SQL Server Setup Help or "Installing AdventureWorks Sample Databases and Samples" in the Books Online (both of which are included on the installation CD(s), and are available from www.Microsoft.com and other sources), provide guidance on samples installation.  Important information regarding the rights / privileges required to accomplish samples installation, as well as to access the samples once installed, is included in these references.

Access the Sample Analysis Services Database in Business Intelligence Development Studio

We will access the sample Adventure Works DW database within the Business Intelligence Development Studio, where we typically accomplish design and development work in general.  Our intent is simply to examine sample Perspectives before querying them, to have a grasp of the structures with which we are interacting.

1.  Click the Start button on desktop.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Business Intelligence Development Studio, as depicted in Illustration 1.


Illustration 1:  Opening SQL Server Business Intelligence Development Studio

The Microsoft Visual Studio 2005 development environment opens, beginning with the Start page, as shown in Illustration 2.


Illustration 2:  The Start Page, Microsoft Visual Studio 2005 Development Environment (Compressed View)

4.  Close the Start Page tab.

5.  Select File -à Open on the Visual Studio main menu.

6.  Select Analysis Services Database from the cascading menu, as depicted in Illustration 3.


Illustration 3:  Opening an Analysis Services Database ...

The Connect to Database dialog appears.

7.  Ensure that the radio button to the immediate left of Connect to existing database is selected.

8.  Type the name of the server / server with instance into the Server box. (Mine is MOTHER1\MSSQL2K5 as seen in various illustrations throughout my articles)

9.  Select the Adventure Works DW database in the Database selector underneath the Server box.

The relevant portion of the Connect to Database dialog appears, with our input, similar to that shown in Illustration 4.


Illustration 4:  The Connect to Database Dialog with our Input / Selections

10.  Click OK to accept our input, and to connect to the Analysis Services database.

The Connect to Database dialog closes, as a brief message box appears, indicating "Reading data from the server." We see the sample Analysis Services database open, complete with all member objects, in Solution Explorer, which appears, by default, in the upper right corner of the BI Development Studio. All that remains is to open the Cube Designer for the Adventure Works cube, to allow us access to the Perspectives tab, where its Perspectives were designed, and are maintained.

11.  Right-click the Adventure Works cube in the Solution Explorer.

12.  Select Open, as depicted in Illustration 5.


Illustration 5:  Opening the Cube Designer in the SQL Server BI Development Studio

The Cube Designer opens, and appears as shown in Illustration 6.


Illustration 6:  Cube Designer  - Compressed View

Let's examine the Perspectives to get some experience with the process.  The Adventure Works DW sample database contains an extensive set of sample objects upon which we can rely for training and testing purposes.  Of immediate interest are the several examples of Perspectives that have been created, and can be reviewed within the Cube Designer, to give us more of an idea of what is going on "under the hood," when we target a Perspective with an MDX query.

As we noted earlier, we can select our choice of cube objects (including dimensions, attributes, hierarchies, measure groups, measures, calculations, KPIs and actions) for inclusion within each Perspective.  Let's examine how this was done within the working examples found in our sample database by taking the following steps:

13.  Click the Perspectives tab atop the Cube Designer, as depicted in Illustration 7.


Illustration 7:  Accessing Perspectives ...

The Perspectives page appears. Here, we see a list of the Cube Objects making up each Perspective, grouped by object type, and subgrouped by parent structures, as appropriate.  Here, we can see the details for the following sample Perspectives:

  • Direct Sales

  • Channel Sales

  • Sales Summary

  • Finance

  • Sales Targets

The Perspectives appear together on the Perspectives page, as partially shown in Illustration 8.


Illustration 8:  The Perspectives Page within the Sample Environment

With each of the Perspectives, we can see the objects that have been included, and that thus appear when the Perspective is selected in the Cube Browser, as well as client applications (such as Reporting Services and other query / analysis tools).  As I have stated here and elsewhere, Perspective creation is largely a process of excluding (via de-selection of objects which are all selected by default in a new Perspective) unwanted objects.

NOTE:  For the detailed steps involved in setting up, maintaining and browsing  Perspectives from within the Cube Designer, see my article Mastering Enterprise BI: Introduction to Perspectives.

14.  Examine the Perspectives further, as desired.

15.  Leave the Business Intelligence Development Studio open for easy reference to the Perspective settings as we proceed with the Practice section that follows.

Now that we have a clear idea of what we intend to target via MDX queries within our practice session, let's look at syntax specifics to further clarify the use of Perspectives within our MDX queries.

Syntax

Syntactically, anytime we write an MDX query targeting a Perspective, we need only name the Perspective within the FROM clause, instead of a Cube name – within the context of the query, Perspectives behave in a manner very similar to cubes.  The general syntax, then, is shown in the following string:

FROM [Perspective_Name]

Let's take a look at an illustration.   The following snippet employs one of the Perspectives we saw in the sample UDM above (called Finance), whose purpose is to provide a visually restricted focus to the financial reporting measures and the related dimensions within the Adventure Works cube.


SELECT
   
   {[Measures].[Amount]} ON AXIS(0),
   
   {[Account].[Accounts].[Account Level 02].MEMBERS} ON AXIS(1)
   
FROM
    [Finance]
WHERE
    ([Date].[Fiscal].[Fiscal Year].[FY 2004])

This query, executed against the Finance Perspective, would produce a results dataset similar to that depicted in Illustration 9.


Illustration 9:  Results DataSet of a Basic Query Targeting a Perspective

In the example dataset, we see that the members of Account Level 02 of the Account dimension appear in the order in the row axis, with the corresponding Amounts appearing in the column.  In the present example, although we have used a Perspective as the target within the FROM clause of the query, we would have obtained identical results had we simple targeted the Adventure Works cube itself.

It is important to keep in mind that, while Perspectives behave as cubes within the context of being the made the target of a query, important differences issue from the fact that they are not physical cubes, but serve merely as "visual focuses."   As a result of their nature, we are not prevented, within a given MDX query, from selecting a dimension or dimensions for retrieval that are not included within a targeted Perspective, provided that it / they are accessible through the underlying cube.  The treatment of measures, however, is more along the lines of what the otherwise unenlightened might expect:  a measure that is not included within the Perspective's design will not be retrieved when specified in a query.  We can circumvent this behavior easily enough, via the creation and use of a calculation to bring in the "excluded" measure, as we shall see within our practice session, but any attempt to directly retrieve a measure that is excluded from the Perspective will result in the return of an empty results dataset.

Practice

Preparation:  Access SQL Server Management Studio

To reinforce our understanding of the basics we have covered so far, we will use a Perspective within a couple of queries that illustrate the points we have discussed.  We will do so in simple scenarios wherein we place the concepts within the context of meeting basic requirements similar to those we might encounter within our respective daily environments. The intent is to demonstrate the handling and operation of Perspectives in a straightforward, memorable manner.

We will turn to the SQL Server Management Studio as a platform from which to construct and execute the MDX we examine, and to view the results datasets we obtain.

1.  Click the Start button.

2.  Select Microsoft SQL Server 2005 within the Program group of the menu.

3.  Click SQL Server Management Studio, as shown in Illustration 10.


Illustration 10:  Opening SQL Server Management Studio

The Connect to Server dialog appears, after the brief Management Studio splash screen.

4.  Select Analysis Services in the Server type selector.

5.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

6.  Supply authentication information, as required in your own environment.

7.  Click the Connect button to connect with the specified Analysis Services server.

The SQL Server Management Studio opens.

8.  In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the "+" sign to its immediate left), appearing underneath the Analysis Server with which we are working.

The Databases folder opens, exposing the detected Analysis Services database(s), as depicted in Illustration 11.


Illustration 11:  Exposing the Analysis Services Databases in the Object Browser ...

NOTE:  The Analysis Services databases that appear will depend upon the activities that have taken place in your own environment, and will likely differ from those shown in Illustration 11 above.  For purposes of this practice session, the Adventure Works DW database must be present.  If this is not the case, consult the Books Online for the installation / connection procedures, and complete these procedures before continuing.

9.  Expand the Adventure Works DW database.

The Database expands, exposing the folders for the various objects housed within the Analysis Services database, as shown in Illustration 12.


Illustration 12:  Exposing the Object Folders in the Database ...

10.  Expand the Cubes folder within the Adventure Works DW database.

The Cubes folder opens.  We see two cubes, the first of which, Adventure Works, is the sample cube with which we will be conducting our practice exercises. The cubes appear as depicted in Illustration 13.


Illustration 13:  The Cubes Appear ...

11.  Click the Adventure Works cube to select it.

12.  Click the New Query button just under the main menu, in the upper left corner of the Management Studio, as shown in Illustration 14.


Illustration 14:  Click the New Query Button with the Adventure Works Cube Selected

The Metadata pane for the Adventure Works cube appears, along with the Query pane to its right, as depicted in Illustration 15.


Illustration 15:  Adventure Works Cube Metadata Appears ...

We will be using the Query pane in the practice session that follows, to construct and execute our MDX queries.

As we discover in articles throughout my Introduction to MSSQL Server Analysis Services series, among my other series' at Database Journal, the SQL Server Management Studio serves us in providing a point of interface with all server types in the SQL Server family, including Analysis Services, Reporting Services and Integration Services servers, as well as supporting many additional functions.  Among those functions, I find the capabilities to easily browse data, and to issue queries, highly convenient.  We can accomplish querying in several other ways within the integrated Microsoft BI solution, but this is certainly one of the most direct.  For more information on the use of the Query Editor within SQL Server Management Studio for issuing MDX queries within the practice exercises of the MDX Essentials series, see Set Functions: The DRILLDOWNMEMBER() Function. (Articles within my other series' explore other capabilities and features of the SQL Server Management Studio, as well as the SQL Server Business Intelligence Studio).

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.


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.

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved