Design and Documentation: Introducing the Visio 2007 PivotDiagram

Monday Jan 8th 2007 by William Pearson
Share:

Use the new Visio 2007 PivotDiagram to support and document the design of the Analysis Services component of your integrated Microsoft Business Intelligence solution. Join BI Architect Bill Pearson as he unveils this new feature, and provides a hands-on introduction to its use.

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.

Hands-On Procedure

We will get some hands-on experience with our subject matter in a practice session. We will proceed from inside Visio 2007, set up our Analysis Services data source (basing it upon the Adventure Works sample cube), and create our PivotDiagram, initially using the PivotDiagram template provided among other Visio 2007 business templates.

We will keep in mind that, while the PivotDiagram can certainly be used as a “refreshable” reporting tool in its own right, it’s value in the present context lies within its use to examine our cube structure (as it evolves, say, within a development environment), to document its design in general, and related possibilities. Moreover, although we can (as we have noted) add PivotDiagram(s) to an existing Visio drawing, we will create our introductory PivotDiagram in standalone fashion, using the template supplied in Visio, to save time within our practice session.

As a manageable practice objective, we will say that we wish to examine a part of the structure of a single dimension of the Adventure Works sample cube, Customers. Our goal is to diagram the Customer Geography hierarchy therein, and to examine only a subset even of that, a couple of States of the Southeast United States (Georgia and Alabama), as a representative subanalysis path we seek to document. (These two States are new to Internet Sales for the Adventure Works organization, and therefore have only a handful of Customer members at the time of our examination of the cube structure.)

Procedure

Create a PivotDiagram

Create a Data Connection, and “Kick Start” the PivotDiagram, using the Wizard

As we mentioned earlier, we will “kick start” the creation of a PivotDiagram; once we have a foundation in place, we will further tailor the PivotDiagram to meet our specific business requirements. As an initial part of using the available wizard to create the basic PivotDiagram, we will establish a data connection to our targeted Analysis Services database.

We will start by opening Microsoft Visio 2007, and moving straight into the Business Diagrams templates that ship with the application.

1.  Click the Start button on the PC.

2.  Select the Microsoft Office Program group of the menu.

3.  Click Microsoft Office Visio 2007, as shown in Illustration 1.


Illustration 1: Opening Microsoft Office Visio 2007

Visio opens at the Getting Started page, by default.

4.  Select File -> New from the main menu.

5.  Select the Business folder from the cascading menu that appears next.

6.  Select PivotDiagram (US units) from the cascading menu that appears, as depicted in Illustration 2.


Illustration 2: Select File -> New -> Business -> PivotDiagram (US Units) ...

The first page of the Data Connection Wizard appears.

7.  Click the radio button to the immediate left of Microsoft SQL Server Analysis Services to select an Analysis Services data source, as shown in Illustration 3.


Illustration 3: Select Microsoft SQL Server Analysis Services

8.  Click Next.

We move to the Connect to Database Server page of the Data Connection Wizard.

9.  Type the appropriate Analysis Server name (server name / instance, if appropriate) into the Server name box.

10.  Supply authentication information, as required in your own environment.

The Connect to Server page appears similar to that depicted in Illustration 4.


Illustration 4: Establishing the Server Connection ...

11.  Click the Next button to connect with the specified Analysis Services server.

The Select Database and Table page of the wizard appears next, similar to that shown in Illustration 5.


Illustration 5: Default Select Database and Table Page

Here we need to select the appropriate Analysis Services database, via the selector that appears.

12.  Select Adventure Works DW within the dropdown selector labeled “Select the database that contains the data you want.”

Once we have made our selection, we note that several individual cubes appear within the list underneath the selector.

13.  Insure that the checkbox labeled “’Connect to a specific cube or table” contains a check mark.

14.  Click the Adventure Works cube within the list, to select it.

The Select Database and Table page, with our selections, appears similar to that depicted in Illustration 6.


Illustration 6: Our Analysis Services Database and Cube Selections

15.  Click Next, once again, to accept selections and proceed.

We arrive at the Save Data Connection File and Finish page.

16.  Leaving the File Name setting at default, type the following into the Description box:

Adventure Works Sample Cube Data Connection

17.  Type the following into the Friendly Name box:

Adventure Works Sample Cube

18.  Click the checkbox at the bottom of the page, labeled Always attempt to use this file to refresh data, to check it.

The Save Data Connection File and Finish page, with our additions, appears similar to that shown in Illustration 7.


Illustration 7: The Save Data Connection File and Finish Page, with Additions

19.  Click Finish to accept our Data Connection File definition, and to finish the steps of the Data Connection Wizard.

The next dialog to appear is labeled Data Selector: Select Data Connection. We will leave the selector at default, and indicating the Data Connection File we have just created, as depicted in Illustration 8.


Illustration 8: Selecting the New Data Connection File ...

20.  Click Next to accept our selection.

The Data Selector page appears next, indicating successful importation of data, as shown in Illustration 9.


Illustration 9: The Data Selector Page Indicates a Successful Data Import ...

21.  Click Finish to close the page.

It is at this point that the PivotDiagram is created, based upon the data that we have imported. The initial PivotDiagram consists of a single Visio shape called a top node, together with the data legend and text box (containing the name of the PivotDiagram), as we noted earlier. Our new PivotDiagram appears, along with the associated PivotDiagram task pane, as depicted in Illustration 10.


Illustration 10: The New PivotDiagram and Associated Task Pane Appears

As is often the case when we start with a wizard, we have assembled a basic foundation quickly. We can now proceed to finesse the results to meet our immediate needs.

Work with Categories, Levels, and Nodes

The top node that has appeared in the new PivotDiagram contains a summary total, based upon a default measure selected from the Adventure Works cube (in this case, the default measure is Internet Sales Amount). In effect, the top node value represents an “All” amount; were we using the PivotDiagram as a reporting mechanism (as we intimated earlier that many will do), we would be able to present a visual “drilldown” of a given value (alone or in combination with other values) by creating underlying levels and categories (referred to in the Analysis Services arena as “members”), based upon our dimensional hierarchy. (We will accomplish the same effect in our practice example, too, for a slightly different reason.)

When we use a PivotDiagram as a design and documentation tool, we want to concentrate more on employing it to present the physical structure of various cube objects; the addition of measures is simple enough, and the use of the wizard has positioned us to get a glance of how the PivotDiagram presents values in general, should we need to do that for another reason at another time. Anytime we do not need to analyze or present measure data, we can modify the PivotDiagram to focus upon structure without measure values. For purposes of this session, we will modify the default measure, substituting a count value in its place that happens to serve as a quick visual check of member completeness. Before we do this, we will save our rudimentary PivotDiagram as it stands.

1.  Select File -> Save As ... from the main menu.

2.  Navigate to a location where it is convenient to save the new Visio file.

3.  Within the Save As dialog that appears, type the following name into the File name box:

DBJ_PivotDiagram_Initial_Foundation.vsd

The relevant portion of the Save As dialog appears as shown in Illustration 11.


Illustration 11: Saving the Initial PivotDiagram File ...

4.  Click Save to save the file and close the dialog.

Now we can make a few alterations to fit our immediate goals of design support and documentation. First, we will change the measure value that appears by default, substituting instead a measure that will coincidentally help to support easy verification of completeness within the PivotDiagram we are creating for our practice session.

5.  In the PivotDiagram task pane, within the Add Total selection list, uncheck the currently selected measure, Internet Sales Amount.

We note that the PivotDiagram updates, refreshing itself to reflect our removal of the default measure.

6.  Within the Add Total selection list, once again, place a checkmark alongside the Customer Count measure to select it.

The PivotDiagram updates once again, and appears as depicted in Illustration 12.


Illustration 12: The PivotDiagram Updates for the Newly Selected Measure ...

We will see that the Customer Count “carries downward,” as we create lower levels, permitting us to visually verify that rollup is occurring as expected.

Next, we will add a category to the diagram. In this case, the category we add, Customer Geography, will become a level under the top node. Categories are typically non-numeric (but certainly not always), and, when acting as levels, can be summarized upon.

7.  Click the top node on the canvas to select it.

We see an outline form around the shape, indicating it selection with regard to the actions we are about to take.

8.  In the PivotDiagram task pane, within the Add Category selection list, click Customer: Country, as shown in Illustration 13.


Illustration 13: Adding a Category to the PivotDiagram ...

Customer Geography is a hierarchy within the Customer dimension of the Adventure Works cube. Customer: Country is a level within the Customer Geography hierarchy. Visio exposes it as a category selection, and from the task pane, we can select and use it as a level within the PivotDiagram.

The PivotDiagram updates once again, and reflects the addition of the Country members, as depicted in Illustration 14.


Illustration 14: The PivotDiagram Updates for the Newly Selected Measure ...

We also note the appearance of the Country level within the PivotDiagram. It is at this point in the object that we will perform our next step, and filter our newly added level.

9.  Right-click the Country level, appearing between the top node and the newly added Country sub nodes.

10.  Select Configure Level ... from the context menu that appears, as shown in Illustration 15.


Illustration 15: Configuring the New Level ...

The Configure Level dialog opens.

11.  Within the Filter section of the dialog, within the selector containing the placeholder expression “(Select Operation),” just under, and to the right of, the label Show data where: Country, select equals from among the available choices.

12.  Type the following into the box to the immediate right of the selector now containing “equals:”

United   States

The Configure Level dialog appears, with our additions, as depicted in Illustration 16.


Illustration 16: Building a Filter Expression for the Level ...

13.  Click OK to accept the new filter definition, and to dismiss the Configure Level dialog.

Note: Anytime we are working within Visio, and the import of external data will be brought about through an action we have initiated, a Microsoft Office Visio Security Notice dialog may appear, containing a warning message. The warning simply reminds us that external data will be imported as a part of refreshment, and asks that we indicate whether this is acceptable. We can turn off the message by clicking the checkbox that appears, as desired.

14.  Click OK to close the Microsoft Office Visio Security Notice dialog (an example of which is shown in Illustration 17), as appropriate.


Illustration 17: Click OK to Dismiss the Security Notice

The PivotDiagram updates as usual, and reflects the effects of the filter upon the members of the recently added Country level, as depicted in Illustration 18.


Illustration 18: The PivotDiagram Updates for the Newly Added Filter ...

As we have seen, we can employ filters to choose which nodes appear in a level that we add to the PivotDiagram. The PivotDiagram affords us the capability to narrow our presentation to specific nodes of interest, by allowing us to conceal nodes that we do not wish to see for given purposes.

Next, we will add the State and City levels, as well another filter, as we move closer toward our desired documentation with the PivotDiagram.

15.  Click the United States sub node on the canvas to select it, as we did earlier for the top node.

16.  In the PivotDiagram task pane, within the Add Category selection list, once again, click Customer: State-Province.

The PivotDiagram updates once again, this time reflecting the addition of the State - Province level.

17.  Right-click the State-Province level, appearing between the United States node and the newly added State-Province nodes.

18.  Select Configure Level ... once again, from the context menu that appears.

The Configure Level dialog opens.

19.  Substitute the following for the current occupant of the Name box:

State

20.  Select “equals,” as before, within the top filter selector.

21.  Type the following into the box to the immediate right of the selector (now containing “equals:”):

Georgia

22.  Select “or” within the selector to the left and below the selector within which we have chosen “equals.”

23.  Select “equals,” within the filter selector to the immediate right, and directly underneath, the selector containing “equals” from before.

24.  Type the following into the box to the immediate right of the selector now containing “equals:”

Alabama

The Configure Level dialog appears, with our modification and additions, as shown in Illustration 19.


Illustration 19: Building a Filter Expression for a (Newly Named) Level ...

25.  Click OK to accept the new filter definition, and to dismiss the Configure Level dialog.

The PivotDiagram updates, as before, and reflects the effects of the filter upon the members of the recently added level, as well as its modified name, as depicted in Illustration 20.


Illustration 20: The PivotDiagram Updates for the Newly Added Filter ...

Next, we will add the City level to our filtered State selection. But before going further, we will make an adjustment to the default Options settings for the PivotDiagram.

26.  Select both the Alabama and Georgia sub nodes on the canvas, by clicking Alabama, holding down the SHIFT key, and then clicking Georgia. (You can also “lasso” them to select both, after the manner used in many Windows applications.)

The outline surrounds the sub nodes as shown in Illustration 21.


Illustration 21: Selecting the Alabama and Georgia Sub Nodes ...

27.  Select PivotDiagram -> Options ... from the main menu, as depicted in Illustration 22.


Illustration 22: Select PivotDiagram -> Options ... from the Main Menu

The PivotDiagram Options dialog opens.

28.  In the Data options section (appearing in the lower half of the PivotDiagram Options dialog), uncheck the Limit items in each breakdown setting. (The default for this setting is “checked,” with the associated Maximum number of items specified as “20.”)

29.  Check the box to the immediate left of Import member properties, in the Data options section just above the Limit items in each breakdown setting we have just deactivated.

30.  In the Diagram options section (appearing in the upper half of the PivotDiagram Options dialog), uncheck the Show data legend setting.

The default for the Show data legend setting is “checked;” We are simply removing the legend for our present purposes. (In the real world, I often prefer customized legends for aesthetic, and other, reasons.)

31.  Substitute the current text in the Title box with the following, more descriptive expression:

SE  United States Subanalysis Path

The PivotDiagram Options dialog appears, with our modifications, as shown in Illustration 23.


Illustration 23: PivotDiagram Options Dialog with Modifications

Note the relative ease with which we are able to entrain member properties into the PivotDiagram, where they will appear with the next refresh, once we take the next step.

32.  Click OK to accept modifications and to dismiss the PivotDiagram Options dialog.

We return to the PivotDiagram, where the two State sub nodes remain selected.

33.  Ensuring that both the Alabama and Georgia sub nodes on the canvas are still selected, click Customer: City within the Add Category selection list of the task pane.

The PivotDiagram updates once again, this time reflecting the addition of the City level, as depicted in Illustration 24.


Illustration 24: The City Level Members Appear – “High Level” View

It hardly escapes notice that the PivotDiagram has exploded at the City level. A close inspection of the Customer Counts within the Cities, however, reveals that many of these contain zeros. This is to be expected because, according to management, Adventure Works has only recently begun selling in these areas. As this is the case, we might take this opportunity to make our PivotDiagram more compact; while we may not necessarily want to completely hide member Cities with no Customers, we can use another feature of the PivotDiagram to at least “consolidate” presentation for the affected Cities.

34.  Simultaneously “lasso” select the following City sub nodes (those that indicate zero Customers) that lie beneath the Alabama City level:

  • Florence
  • Huntsville
  • Mobile
  • Montgomery

35.  Select Other Actions within the PivotDiagram task pane.

36.  Select Merge from the items that appear within the dropdown selector, as shown in Illustration 25.


Illustration 25: Merging the Selected Group of Sub Nodes

The affected Cities are merged into a single sub node, as depicted in Illustration 26.


Illustration 26: The Alabama Cities without Customers Appear in a Merged Sub Node

37.  Perform the same steps for the Georgia City sub nodes that indicate zero Customers. (To select non-contiguous Cities, click on each, while depressing the CTRL key.)

The lowest set of sub nodes (children of the City level) within the current PivotDiagram appear as shown in Illustration 27.


Illustration 27: Merged Sub Nodes alongside Independent Cities with Customers ...

One last level remains in building our diagram. We will next present the Customers that are associated with each City. (Our having merged the Cities with no Customers will, as we shall see, help to present a tidier picture within this context, as well.)

38.  Placing the mouse just to the left and above the Birmingham sub node, click and drag, to capture all City sub nodes within in a “lasso,” as depicted in Illustration 28.

Click for larger image

Illustration 28: “Lasso” Select the City Sub Nodes ...

Let’s say we have been asked to present Customer Addresses, versus Customer Names (the actual Customer member names within the cube). The Customer Address is one of several member properties for each Customer member. Member properties are now exposed as Categories that are available for selection, based upon the fact that we enabled their import from the PivotDiagram Options dialog earlier.

39.  In the PivotDiagram task pane, within the Add Category selection list, once again, click Customer: Address.

The PivotDiagram updates once again, this time reflecting the addition of the Address level.

The Addresses associated with the Cities with Customers are useful to the expressly requested presentation. Those that appear underneath the merged, customer-free Cities simply reflect member properties that exist for all Cities within the respective State’s City level, and are hence not useful within the context of our present objectives. For this reason, we will suppress the sub nodes that have appeared for the merged Cities.

40.  Click the merged Alabama City sub node to select it.

41.  Holding down the CTRL key, click the merged Georgia City sub node to simultaneously select it.

42.  Select Other Actions within the PivotDiagram task pane, once again.

43.  Select Collapse from the items that appear within the dropdown selector, as shown in Illustration 29.


Illustration 29: Merging the Selected Group of Sub Nodes

The affected Cities are collapsed into a single sub node, as depicted in Illustration 30.


Illustration 30: A More Compact, Relevant Presentation, Courtesy of the Collapse Action

Next, we will perform an adjustment to make the diagram easier to understand for, say, other members of the team that do not necessarily grasp the concept of an “All” level – while taking a look at another action we can employ for similar needs, perhaps, in our own environments.

44.  Click the United States node, once again.

45.  Select Other Actions within the PivotDiagram task pane, as we have already done several times.

46.  Select Promote from the items that appear within the dropdown selector, as shown in Illustration 31.


Illustration 31: Promoting United States to the Top Node

The United States (Customer Country member) assumes the top node position. Because our title specifies “SE United States,” readers can assume that the top node represents the Country level (recall that we filtered same to United States earlier). The newly compacted layout appears as depicted in Illustration 32.


Illustration 32: A More Intuitive Top Node Appears ...

We will conclude with a few minor “cleanup” details, to demonstrate more ways to enhance the presentation of our new PivotDiagram. First, we will compact spacing a bit.

47.  Select Shape -> Configure Layout from the main menu.

The Configure Layout dialog opens.

48.  Set the Spacing to 0.3 in.

The Configure Layout dialog appears as shown in Illustration 33.


Illustration 33: Configure Layout Dialog with New Spacing Setting

49.  Click OK to apply modifications and to dismiss the dialog.

Finally, now that design of the PivotDiagram is complete, we can remove the level names (some might prefer to leave them in place), simply as another space conserving measure.

50.  Select the top node.

51.  Select PivotDiagram -> Options..., once again.

52.  Uncheck the setting Show breakdown shapes.

The PivotDiagrams Options dialog appears as depicted in Illustration 34.



Illustration 34: PivotDiagram Options Dialog with New Spacing Setting

53.  Click OK to accept the modification and to dismiss the dialog.

The adjusted layout appears as depicted in Illustration 35.


Illustration 35: Levels (Breakdown Shapes) Removed ...

And so, we see that the new PivotDiagram has much to offer us in a way of design and documentation. I have attempted to cover many of the capabilities in the steps of this practice session. There are many other potential uses for PivotDiagrams, as well as numerous alternate ways to arrange and present the shapes involved and the information that they convey. As I put the PivotDiagram to work, together with other Visio features, I will expose relevant settings and methods in other articles of my series’.

54.  Experiment further, as desired, with other settings among the dialogs we have covered together, as well as with the various actions that are available for use with the PivotDiagram.

55.  Select File -> Save As ..., and navigate to a convenient location to save a copy of recent work, if desired.

56.  Select File -> Exit, when ready, to leave the Visio.

Conclusion

In this article, we introduced and explored the PivotDiagram, which debuts in Microsoft Office Visio 2007, setting our sights upon examining its use within the context of design and documentation from the perspective of the Analysis Services environment. Our focus upon the PivotDiagram included a brief introduction to its possible uses, its components, and the data it can present. We then began 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.

We modified the PivotDiagram to illustrate various setting and layout options. We provided a running discussion, throughout the practice session, surrounding our work with Categories, Levels, and Nodes in the PivotDiagram, mapping each object to its peer object within the Analysis Services environment. We performed steps to add Analysis Services member properties into the new PivotDiagram, exposing them as supplementary Categories therein. Finally, we obtained some practical experience with the application of the Merge, Collapse and Promote Actions to nodes at various levels of the diagram. At relevant junctures within our construction of the PivotDiagram, we provided examples of filtering, together with suppression of objects not useful to a given presentation.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved