Integrating Visio 2007 with SQL Server Analysis Services
Previously discussed was how to integrate SQL Server Analysis Services (SSAS) with Excel 2007 in the article, Accessing Cubes from Excel 2007. Visio 2007 is another tool available in Office 2007 that can be used to connect to a SSAS database. There are two reasons why Visio is not an automatic choice to connect to SSAS:
- Visio is not a popular tool among business users. Unlike, Excel most business users don’t have Vision installed on their computer.
- Another reason that Visio is not used as client tool for SSAS is widespread lack of knowledge about it. This article aims to help resolve this.
To visualise what can be done with SSAS and Visio, the following image shows how information can be presented in Visio based off a SSAS cube:
Configuring SSAS Connection to Visio
Configuring an SSAS connection to an SSAS cube is similar a connection from Excel except for the first screen. After creating a new Visio document, select Insert PivotDiagram from the Data in the Main menu and the screen shown below will be presented:
There is the option to create links to other sources such as Excel, Access and SQL Server. This article will concentrate on SQL Server Analysis Services (SASS).
After this screen, a tool bar and side bar appear as shown in the flowing images:
The next section will examine how these options can be used in detail.
Using SSAS Data
The PivotDiagram has three sections, Category, Total and Actions. Category is mapped to dimensions in SSAS – where all the dimension attributes and hierarchies are found. Total is mapped to the fact table measures. Actions are performed on the Pivot Diagram. In the Actions section, the Other Actions button gives additional options to customise output.
Once the options are understood, an example is used to show how to use them. For example, to analyse the sales amount with the Gender of the Customer.
Select the sales amount from the Add Total section. Next, select Customer:Gendar from the Add category section and the following output will given:
Perform further analysis by adding more categories. For example, to analyse the Female sales amount by occupations; select the F Box under the Gender and select Customer:English Occupation from the Add Category section and the output will be as shown below:
To analyse Gender M with some other category, selecting the other category.
In the Other Action button, the most important option is Edit Data Graphic as the other options are self explanatory.
The data fields can be controlled with the option to add new items, edit existing items, delete existing items and set the default positions.
Note that by default, the amount will have four decimal points and is lacking a comma separator. Reconfigure this by clicking Edit Item.
Configure the Format of the value, Label etc. For example, Value format #,0.00 will display the amount with two decimal points and a thousand separator. There are other parameters like border type and fill type to display, that can be used to format the dispaly.
Rather than adding fields from the side bar, use the New Item option in the dialog. The New item option has four sub-options shown below:
The first, Text, will simply add a column and display it in a text format. The Data bar will display data with some graphical presentation. As shown below, select the graphical representation needed by selecting the Call out option.
Set the minimum and maximum value according to that select indicator represents graphical value. Data can be presented in various formats and the figure below shows the available Data Bar formats.
The Icon Set allows display of values with given icons. For each callout there is five icons set that can define what to display. For each icon, rules can be set using different criterions from the available drop down. That drop down contains options like equals, does not equal, is greater than, is less than, is between and contains etc.
The image below shows available icon sets for selecting an icon set to match the data.
Colour by value gives an opportunity of colouring a Visio diagram. It is possible to set colours for particular values and for ranges of values. The image below shows how to set the colouring method for a range of values:
The issue with these options is that there is no option to set the colouring methods for one level.
Visio is a client tool that can be used to visualize cube data created using SSAS. This provides options for presenting data in an aesthetically pleasing manner.