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 - Page 3

By William Pearson

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:


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.

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