416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Treemap Sample

Hide navigation
RSS
Modified on Tue, 27 Jul 2010 10:54 AM Categorized as Samples

Building a Treemap in Dundas Dashboard

What is a Treemap?

A treemap is a visualization that can display a large amount of hierarchical data in a pre-defined space using nested rectangles of varying size and color. The total area of a Treemap reflects the sum of all of it parts, with the inner rectangles representing their contribution to the total size.

Treemaps have the ability to display a second measure as the color or shade of each rectangle. You can specify a range of values to associate with individual colors, or just select a start and end color and have Dundas Dashboard calculate the proper shading for each rectangle.

Data Preparation

The data preparation involved in creating a treemap is a little more complex than for regular chart visualizations. When creating many types of visualizations you require only a dimension and a measure, but in a treemap you must represent your hierarchical structure within your data table.

The data table must have a text column from each level of your hierarchy. If you wanted to display sales figures for continent and country you would require a column for each of these levels. An example is shown in the below table.

Region NameCountry NameSalesTarget
AfricaBurundi1,4181,385
AfricaCape Verde1,7451,646
AsiaJapan61,23153,773
AsiaPhilippines706810
AsiaSouth Korea45,63741,087
EuropeAustria736808
EuropeBelgium3,5524,161
North AmericaCanada55,43568,654
North AmericaUnited States64,98482,378


As mentioned above, you can visualize two measures in a treemap, one with size and the other with color or shading. You data table just have to have a column for each of these values. In the above table, we have data that will allow us to visualize total sales as size and the variance from a target value as the color. The order in which these columns appear in your data table will not affect which measure is used for size, and which one for color. This association will be made during the Dashboard design stage.

Included in the sample is a SQL statement that creates a view and a table, called treemap_SalesByCountry and treemap_SalesTargets, that will return a data table in a format that corresponded to the example data table. Open the .sql file of the same name and run it against the sample Sonatica database in SQL Server Management Studios, or a similar database query tool.

Creating a Data Connector and Creating your Virtual Table

Now that the required view is created in the Sonatica sample database, let’s open up Dundas Dashboard, create a Data Connector to the Sonatica sample database and then create a virtual table based off of the view created above. First, let’s create a new project in Dundas Dashboard called Treemaps. Just select the project dropdown, select and then enter the project name.

On the Data Connectors folder, click the context menu and select “New Data Connector” and enter the name Sonatica. On the Data Connector screen, enter the following values:

  • Provider Microsoft SQL Server 2005 or 2008
  • Authentication: Select the method of authentication that Dundas Dashboard uses to connect to its application database.
  • Database name Sonatica2010-SQL-Source

Save this data connector and Check it in.

Expand this Data Connector in the Design Explorer, and then expand the Views folder. Find the view named treemap_SalesByCountry, click the context menu and select “Create Virtual Table”. Next find the table named treemap_SalesTargets, click the context menu and select “Create Virtual Table”.

Check both of these tables in.

Now we need to create a new virtual table that joins the two virtual tables we have just created. From the Virtual Tables folder, create a new Standar Virtual Table named SalesRevenueAndVariance, and select the two virtual tables we have created as the data sources. Add a relationship between the two structures with the following properties:

  • Parent Structure : treemap_SalesByCountry
  • Child structure : treemap_SalesTargets
  • Relationship : Left Join
  • Parent Column : country_id
  • Child Column : country_id

Navigate to the columns tab and Add a Custom Column with the following database expression {DD2.sales_actuals} - {DD2.sales_targets} . Parse this expression and click Finish. Give this column the friendly name “TargetVariance”.

Add another Custom Column with the following database expression CASE WHEN ( {DD2.sales_targets} - {DD2.sales_actuals} ) > 0 THEN 'Under Performing' WHEN ( {DD2.sales_targets} - {DD2.sales_actuals} ) < 0 THEN 'Over Performing' ELSE 'On Target' END Parse this expression and click Finish. Give this column the friendly name “Variance State”.

The above calculated columns are used to calculate the sales variance from a specified target, as well as setting a state for this variance. The state calculated column is going to be used for filtering by state. Preview this virtual table and then Check it in.

Setting up your KPI

Now that we have the necessary virtual tables created, we can move on to creating a KPI that will feed our treemap. Create a new KPI from the KPI folder context menu. Select the salesRevenueAndVariance virtual table as the base data structure. Define the following measures:
  • Sales_targets with a SUM aggregator
  • Sales_actuals with a SUM aggregator

Define the following dimensions:
  • CountryName : Set as an Axis
  • RegionName : Set as an Axis and the Splitter
  • Variance State

Note that when you are selecting your dimensions, you must select both your hierarchy columns as Axes and select the Parent Node as your splitter. In the above example, because Region is the parent column, it must be selected as the Splitter. On the General Tab, select a preferred visualization of Treemap.

Next, we are going to add a calculated measure that calculates the percentage over or under target each regions sales are. To do this, move to the Measures and States tab and click the Add Calculated Value button. Select to add a Dundas Script Expression and enter the following: ( {sales_targets} - {sales_actuals} ) / {sales_actuals}

Next, navigate to the Dimensions tab, where you will set up filters on the following fields:
  • RegionName
    • Public
    • (All) and open ranges allowed for value selection
    • The filter allows single value selection
    • The filter allows multiple value selection
  • Variance State
    • Public
    • (All) and open ranges allowed for value selection
    • The filter allows single value selection
    • The filter allows multiple value selection
      Save this KPI and Check it in.

Configuring a Treemap on a Dashboard

Now we are ready to create our dashboard and treemap. First, create a new Dashboard and open up the design canvas. Drag the SalesRevenueAndVariance KPI to the dashboard canvas. You will now see a treemap with default properties set.

Treemap Specific Properties

The treemap control has some visualization specific properties, notable the Nodes property found under the data grouping in the properties list. Click the list button to bring up the full list of Node properties. These properties allow you to set the parent and child appearance properties, such as border thickness, border color and fon size. The most important of these properties would be the States property, which controls the shading of the child elements.

In our sample we are going to implement two states. The first will be a positive state that shades areas where the sales variance is positive in green, and the second will be a negative state that shades the areas where the sales variance is negative in red. This will allow the end user to quickly discern what areas are greatly above target and what areas are greatly below target, with the areas who are on target taking on a white background.

We’re going to first select a Data Distribution of EqualDistribution and a Data Grouping Count of 5. This means that we are going to have 5 shades between our start and end color and they will be of equal size.

Next, we are going to select a Start Color of White and an End Color of Green and specify a State Name of PositiveVariance. Next we get to specify the range of values that will constitute a positive variance. We are going to specify a Start Value of 0 and an End Value of 1. As our variance is a positive and negative percentage, we are deeming any positive percentage as favourable, and the closer to 2 this value is, the deeper shade of green the rectangle will be shaded.

We are then going to add a second state, by clicking the Add button on the bottom left of the States window. We are going to give this state the following properties:
  • Data Distribution : EqualInterval
  • Data Grouping Count : 5
  • End Color : Red
  • Start Color : White
  • State Name : NegativeVariance
  • Start Value : -2
  • End Value : 0

When we preview this treemap we should now see these our child nodes shaded with colors varying from dark red to white, and white to dark green.

Applying Filters on the treemap dimensions

During the KPI creation process, we did expose certain dimension to be public. Now we are going to add these to the Dashboard to add some end user interactivity, specifically filtering to see a treemap for specific regions and for positive or negative variance states. We’re going to use a Combo Box selection parameter to allow users to filter by multiple continents and a dropdown parameter filter to allow for the filtering of countries with positive or negative variances.

To do this, make sure you are in Design mode of the Dashboard and select the Parameters button, above the Design Explorer button in the left navigation area. Click the ‘Add New Parameter…’ button on the top to bring up the parameter wizard. Give this parameter a name of ‘Continent’ and click Next. Select the treemap visualization from the list of visualization on your dashboard and then click ‘Next’. Select the row that lists RegionName as the filter and the Filter Type of Regular. Select a Dashboard Control type of Check Box and click ‘Next’. Leave the default value as ‘All Values’ click finish. Now drag this parameter onto the design canvas.

Now perform the same steps from above to add a Variance State combo box. The only differences being selecting the VarianceState filter to filter by and selecting a Dashboard Control Type of Combo Box. Drag this to the design canvas. You now have an interactive treemap that will allow an end user to quickly discern problem areas that may constitute additional attention or action.





About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2012 Dundas Data Visualization, Inc.