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 2

By William Pearson

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.)


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.

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