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
.

Note: To follow along with the steps we undertake, the following components,
samples and tools are recommended, and should be installed according to the
respective documentation that accompanies MSSQL Server 2005:

  • Microsoft SQL
    Server 2005 Database Engine

  • Microsoft SQL
    Server 2005 Analysis Services

  • Microsoft SQL
    Server 2005 Integration Services

  • Business Intelligence
    Development Studio

  • Microsoft SQL
    Server 2005 sample databases

  • The Analysis Services
    Tutorial sample project and other samples that are available with the
    installation of the above.

To
successfully replicate the steps of the article, you also need to have:

  • Membership
    within one of the following:

    • the Administrators
      local group on the Analysis Services computer

    • the Server
      role in the instance of Analysis Services

  • Read permissions within any SQL
    Server 2005
    sample databases we access within our practice session, as
    appropriate.

Note: Current Service Pack updates are assumed for the operating system, MSSQL
Server 2005
("MSSQL Server"), MSSQL Server 2005 Analysis
Services
("Analysis Services"), MSSQL Server 2005 Reporting
Services
("Reporting Services") and the related Books
Online
and Samples. Images are from a Windows 2003
Server
environment, but the steps performed in the articles, together with
the views that result, will be quite similar within any environment that
supports MSSQL Server 2005 and its component applications.

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.

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