Design and Documentation: Introducing the Visio 2007 PivotDiagram

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
(“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. For the software components, samples and tools
needed to complete the hands-on portion of this article, see Usage-Based Optimization in Analysis Services 2005, another article within this
series.

About the Design and Documentation Articles

The purpose of the Design and
Documentation
subset of my Introduction to
MSSQL Server Analysis Services
series is to focus on tools and techniques for designing and
documenting Analysis Services solutions. As most architects, developers,
report authors and other solution designers and implementers are aware, a body
of best practices, “standard approaches,” and other techniques and methods
evolves within the life of a given product / suite of products, and typically
matures only after the application(s) has been available within the market for
some time, and has experienced wide adoption and implementation. Some general
practices necessarily appear with the introduction of the application to the
developer market, in that minimal, basic procedures for its use are a
requirement even for the earliest adopters. Over time, once the application
has been implemented in a wide range of business and technical environments,
the collection of design standards and best practices naturally matures;
third-party offerings for assisted design and documentation appear; and other
events transpire to add to, and refine, the general body of information.

MSSQL
Server 2005
arrived
with more documentation and practical design information than any enterprise BI
solution to date. The body of knowledge surrounding the integrated Microsoft
BI solution just before, and as a part of, its release consisted of everything
from focused tutorials, tips and walkthroughs to at least one evolving, in-depth
project study (Project REAL, a cooperative effort to build upon actual
customer scenarios, undertaken between Microsoft and a number of technology
partners in the business intelligence industry).

In
addition to other Microsoft applications, whose more recent versions have been
updated to integrate with MSSQL Server 2005 components, many third-party
tools have appeared to assist in the design and documentation of the integrated
Microsoft business intelligence solution. Many among us are familiar with the challenges
involved with determining how best to design, and how to document – ideally in
a manner concurrent with design – the concepts and requirements underlying that
design, as well as the structures and mechanisms that form its ultimate
physical manifestation. One of those challenges is simply being aware of the
options that are available at the times when we need them …

I
hope, within the context of the Design and Documentation articles,
to suggest and explore the use of different tools to support the design and
documentation of various components of the integrated Microsoft business intelligence solution,
focusing predominantly upon the Analysis Services layer of such a
solution.

Introduction

Among
several new applications and server-side tools that make their debut in Microsoft
Office 2007
, many of the tools upon which we, as developers and architects,
have relied in the past have also been enhanced in numerous ways. One of the
key types of enhancement that continues to occur, from the perspective designers,
developers and implementers of Microsoft BI, lies within the integration of
these tools with the components of MSSQL Server 2005. Microsoft
Visio
, a longtime favorite tool for general database design and
documentation (one of whose more popular capabilities has included reverse
engineering and diagramming schemas
for some time), had, until now, been
somewhat limited with regard to providing the same sort of support for the Analysis
Services
environment. While articulate diagrams could be manually created
to present the details of the structure of cubes, dimensions, measures, and
other Analysis Services objects, an “out-of-the-box,” capability to automatically
generate such diagrams, similar to the reverse-engineering capability which
existed for relational databases, did not exist for Analysis Services
databases. This shortcoming is addressed with the new PivotDiagram
feature in Microsoft Visio 2007 (“Visio”).

Similar
to an Excel PivotTable report in appearance, and to some extent action,
a PivotDiagram arranges data in a visual way that, upon first blush,
appears to be thoroughly oriented toward the perspective of an information
consumer. This is, indeed, the intent of the feature, whose reporting
capabilities make it easy for an intended audience to see and understand the
details behind summary values, to drill down and analyze key performance
metrics, and to perform other analysis upon enterprise data. PivotDiagrams,
among other Microsoft Office tools, can contribute to the creation of
high-impact visual reports and presentations of the relational and OLAP data
within the enterprise.

In
this article we will explore the utility of the PivotDiagram from a
perspective which is, perhaps, slightly oblique to the intended use of the
feature as a general reporting tool: we will examine its use within the
context of design and documentation of an Analysis Services data source,
wherein, although we are still technically using the feature as a report
rendering mechanism, we are more focused upon presenting data structure
than the values contained within that structure.

Within the context we
have described, we will gain some familiarity with the PivotDiagram – how it works and some of its
capabilities – while gaining
some hands-on exposure to creating a PivotDiagram based upon the sample Adventure Works development
environment, which can be installed with MSSQL Server 2005. Our examination of the PivotDiagram in this article will include:

  • An
    introduction to the new Visio PivotDiagram, including an overview of its uses, components, and the
    data it can present;

  • A hands-on
    practice exercise, wherein we set up a working PivotDiagram, with a live data connection
    based upon the sample Adventure Works Analysis Services database;

  • Modifications
    of the PivotDiagram
    to illustrate various setting and layout
    options;

  • A running discussion,
    throughout the practice session, surrounding our work with Categories, Levels,
    and Nodes in the PivotDiagram, including our mapping each
    object to its peer object within the Analysis Services environment;

  • The induction
    of Analysis Services Member Properties into the PivotDiagram, together with an introduction to
    their use as supplementary Categories therein;

  • Exposure to
    various actions available within the PivotDiagram, including the application of Merge, Collapse
    and Promote actions to nodes at various levels;

  • A focus upon filtering
    in the PivotDiagram, together with suppression of
    objects that might not be deemed useful to a given presentation.

Introducing the PivotDiagram for Design and Documentation

Overview and Discussion

A PivotDiagram is
a collection of Visio shapes arranged in a hierarchical structure, which,
in its simplest description, begins with a top node, which we can then
break out into underlying levels of sub nodes. The PivotDiagram allows
us a great deal of flexibility in presenting data in a largely visual way;
herein lays its value as a design and documentation tool. We can create a PivotDiagram
as an independent reporting mechanism, or we can insert a PivotDiagram (or
multiple PivotDiagrams) into other diagrams to complement or supplement existing
information, to present a more complete picture of what we are attempting to
relay to the intended audience.

We can create a PivotDiagram
from any of the following data sources:

  • Microsoft
    SQL Server database

  • Microsoft
    SQL Server Analysis Services

  • Other
    OLE DB or ODBC data sources

  • Microsoft
    Office Excel workbook

  • Microsoft
    Office Access database

  • Microsoft
    Windows SharePoint Services list

As we have noted, our focus within this article will be the
creation of a PivotDiagram within the context of design and documentation.
Moreover, we will specifically concentrate upon the use of Analysis Services
data source, keeping in mind that a combination of an Analysis Services
and database data sources, among, perhaps, other sources, might provide an
excellent basis for the presentation of an integrated, multi-layered business
intelligence solution within a single document.

As we shall see, when we create a PivotDiagram, Visio
imports the data it needs from the selected data source, and then it inserts
three separate objects into the new diagram:

  • A
    legend
    – containing information about the data source with which we
    have established a connection;

  • A
    title box – supported by the Title we set within the PivotDiagram
    Options
    dialog;

  • A
    primary shape – which aggregates, by default, all the data in the data
    source.

We begin with the primary shape in defining the rest of
the tree structure that appears within the diagram, as we shall see.
This allows us to leverage the power of the PivotDiagram, and to explore
our data, be it values or structure (our focus in this article
will be the latter, as we have noted), from various perspectives whereby we can
easily establish and study relationships in a way that might be difficult from within
the “flatter” presentations we might encounter within worksheets or tables.
Such flexibility in presentation is highly useful within a design and development
environment, and, needless to say, in documenting the structure that results
from our efforts within those environments.

We will create a basic PivotDiagram tree structure within
the practice session that follows. As we build this example structure, we will
learn the names and purposes of the member components, as well as gaining
hands-on exposure to the manner in which we tie these components to the
underlying Analysis Services objects that they represent. As has been the case within
virtually all the articles of this series, the objects that we create within
the steps of the practice exercises will assist us in reinforcing our
understanding of the various methods and components that we will explore.

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 for use with 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
orInstalling 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. Both the
aforementioned samples and Microsoft Visio 2007 must be installed to
complete the steps of the section that follows.

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