Other MDX Entities: Perspectives - Page 2

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. 

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.

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

The Connect to Database dialog appears.

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

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.

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

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:

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.

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.

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.