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.
