Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 8, 2007

Design and Documentation: Introducing the Visio 2007 PivotDiagram

By William Pearson

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.


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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM