Other MDX Entities: Perspectives

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.
William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles