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, its 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
a Data Connection, and Kick Start the PivotDiagram, using the Wizard
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.
will start by opening Microsoft Visio 2007, and moving straight into the
Business Diagrams templates that ship with the application.
Click the Start
button on the PC.
Select the Microsoft
Office Program group of the menu.
Office Visio 2007, as shown in Illustration 1.
Illustration 1: Opening Microsoft
Office Visio 2007
Visio opens at the Getting Started
page, by default.
-> New from the main menu.
Select the Business
folder from the cascading menu that appears next.
(US units) from the cascading menu that appears, as depicted in Illustration
2: Select File -> New -> Business -> PivotDiagram (US Units) ...
The first page of the Data
Connection Wizard appears.
radio button to the immediate left of Microsoft SQL Server Analysis Services
to select an Analysis Services data source, as shown in Illustration
Illustration 3: Select Microsoft
SQL Server Analysis Services
We move to the Connect
to Database Server page of the Data Connection Wizard.
Type the appropriate
Analysis Server name (server name / instance, if appropriate) into the Server
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 ...
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: Default Select Database and Table Page
Here we need to select
the appropriate Analysis Services database, via the selector that
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.
the checkbox labeled Connect to a specific cube or table contains a
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: Our Analysis Services Database and Cube Selections
once again, to accept selections and proceed.
We arrive at the Save
Data Connection File and Finish page.
Leaving the File
Name setting at default, type the following into the Description
Adventure Works Sample Cube Data Connection
following into the Friendly Name box:
Adventure Works Sample Cube
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.
The Save Data Connection File and Finish Page, with Additions
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 ...
to accept our selection.
The Data Selector
page appears next, indicating successful importation of data, as shown in Illustration
Illustration 9: The
Data Selector Page Indicates a Successful Data Import ...
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.