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 2

By William Pearson

Perspectives within MDX


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.


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.

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