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

By William Pearson

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.


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.

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