Mastering Enterprise BI: Introduction to Perspectives

About the Series …

This
article is a member of the series Introduction
to MSSQL Server Analysis Services. The series is designed to provide hands-on application of
the fundamentals of MS SQL Server Analysis Services, with each installment
progressively presenting features and techniques designed to meet specific real
– world needs. For more information on the series, please see my initial
article, Creating Our First
Cube.

About the Mastering Enterprise BI Articles …

Having implemented, and developed within, most of the major
enterprise BI applications for many years, and having developed an
appreciation for the marriage of ease of use and analytical power
through my background in Accounting and Finance, I have come to appreciate the
leadership roles Cognos and other vendors have played in the evolution of OLAP
and enterprise reporting. As I have stated repeatedly, however, I have become
convinced that the components of the Microsoft integrated business intelligence
solution (including MSSQL Server, Analysis Services, and Reporting
Services) will commoditize business intelligence. It is therefore easy to
see why a natural area of specialization for me has become the conversion of Cognos
(and other) enterprise business intelligence to the Microsoft solution. In addition to converting formerly
dominant business intelligence systems, such as Cognos, Business Objects / Crystal,
MicroStrategy and others, to the Reporting Services architecture, I
regularly conduct strategy sessions about these conversions with large
organizations in a diverse range of industries – the interest grows daily as
awareness of the solution becomes pervasive. Indeed, the five-to-six-plus
figures that many can shave from their annual IT budgets represent a compelling
sweetener to examining this incredible toolset.

The purpose of the Mastering
Enterprise BI subset of my Introduction to
MSSQL Server Analysis Services series is to focus on techniques for implementing features in
Analysis Services that parallel – or outstrip – those found in the more "mature"
enterprise OLAP packages. In
many cases, which I try to outline in my articles at appropriate junctures, the
functionality of the OLAP solutions within well-established, but expensive,
packages, such as Cognos PowerPlay Transformer and Cognos PowerPlay,
can be met – often exceeded – in most respects by the Analysis Services /
Reporting Services combination – at a tiny fraction of the cost. The
vacuum of documentation comparing components of the Microsoft BI solution to
their counterparts among the dominant enterprise BI vendors, to date,
represents a serious "undersell" of both Analysis Services and
Reporting Services, particularly from an OLAP reporting perspective. I
hope, within the context of the Mastering Enterprise BI articles, to
demonstrate that the ease of replicating popular enterprise BI features in Analysis
Services will be yet another reason that the integrated Microsoft solution
will commoditize business intelligence.

For
more information about the Mastering Enterprise BI articles, see
the section entitled "About the
Mastering
Enterprise BI Articles" in my article
Relative Time Periods in an Analysis Services Cube, Part I.

Introduction

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.

While we will delve into many facets of the UDM in other
articles, the salient characteristic that brings it into our current field of
consideration is its potential size and complexity. The UDM allows for
significant enrichment of the more basic user models of before, 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 on
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

Let’s
imagine a simple illustration 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 purpose is 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. 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.

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 examine Perspectives, and get hands-on exposure to the process of
adding them to a basic cube we construct within the new Business
Intelligence Development Studio. We will overview the creation of Perspectives,
and discuss ways in which they can enable us to offer flexibility to the end
users of our cubes and solutions / applications. As a part of our examination
of the steps, we will:

• Prepare Analysis
  Services, and our environment, by creating an Analysis Services Project
  to house our development steps, and to serve as a platform for the design of a
  quick cube model, within which to perform subsequent procedures in our session;

• Create a Data
  Source containing the information Analysis Services needs to connect
  to a database;

• Create a Data
  Source View containing schema information;

• Build a cube
  based upon our Data Source and Data Source View, containing data
  from our sample relational tables;

• Add examples
  of Measure Groups as part of cube design;

• Create a
  couple of example Perspectives for a hypothetical pair of intended
  audiences;

• Deploy our Analysis
  Services Solution;

• Browse the Cube,
  focusing on the new Perspectives and associated details.

Working with Perspectives

Overview and Discussion

We
will create an Analysis Services Project within the Business Intelligence Development
Studio, to provide the environment and the
tools that we need to design and develop business intelligence solutions based
upon Analysis Services 2005. As we have noted in the past, the
pre-assembled Analysis Services Project that makes its home within the Studio
assists us in organizing and managing the numerous objects that we will
need to support our efforts to create and deploy our Analysis Services
database.

We
will leverage the Cube Wizard in this article to quickly design and
create a cube, allowing us to focus on the subject matter of the article with
minimal peripheral distraction. The Cube Wizard not only helps us simplify
the design and creation of our cubes, as it did within Analysis Services
2000: the Analysis Services 2005 Cube Wizard is more powerful,
leveraging IntelliCube technology to examine and classify many of the
attributes of our data. Analysis Services can determine, for example,
prospective fact tables, dimensions, hierarchies, levels and other structural
members of our cubes from a given database schema at which it is pointed. Regardless
of whether we make a habit of using the wizard in our cube development efforts,
it certainly provides a way to rapidly generate a cube, if only to eliminate part
of the repetitive work involved to create a "starting point" model,
which we can then "prune and groom" to more precisely meet the
business requirements of our employers and customers.

Considerations and Comments

For purposes of the practice
exercises within this series, we will be working with samples that are provided
with MSSQL Server 2005 Analysis Services. The samples with which we are
concerned include, predominantly, the Adventure Works DW Analysis Services
database (with member objects). 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.