One of fascinating features of Excel 2013 is “App for Office” option which exposes us to some of the great apps from Office store which you can download and install. One of such useful apps which I came across is TreeMap Data Visualization developed by Microsoft Research which allows you to represent hierarchical data of the dimensions in the form of nested rectangles.
If you have used WindirStat to monitor space of your drive, you exactly know whatTreeMap looks like and how useful it is for hierarchical data.
TreeMap allows to plot two Measures simultaneously via Size and Color controls. Such Visualization can be useful in BI World when you want to compare hierarchical dimension members with two measures simultaneously .
In my example below, I am using TreeMap to monitor Table Space Usage for a Database viz Adventureworks2012 so that by looking at the Treemap I can find out the table with large number of rows and which has highest unused free space which can released or reclaimed.
As we know, sp_spaceused in SQL Server helps me find the number of rows and unused space within the table, I use the following Stored Procedure to use sp_spaceused and cursor to query the stats from each table and load it into a separate table called TableStats
USE [AdventureWorks2012] GO /****** Object: Table [dbo].[TableStats] Script Date: 9/1/2013 5:53:58 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TableStats]( [tableName] [varchar](100) NULL, [numberofRows] [varchar](100) NULL, [reservedSize] [varchar](50) NULL, [dataSize] [varchar](50) NULL, [indexSize] [varchar](50) NULL, [unusedSize] [varchar](50) NULL ) ON [PRIMARY] GO CREATE PROCEDURE [dbo].[GetTableSpaceUsage] AS BEGIN DECLARE @TableName VARCHAR(100) DECLARE @SchemaName VARCHAR(100) DECLARE @FullName VARCHAR(100) TRUNCATE TABLE TableStats DECLARE tableCursor CURSOR FOR select [name] from sys.objects where OBJECTPROPERTY(object_id, N'IsUserTable') = 1 FOR READ ONLY DECLARE SchemaCursor CURSOR FOR select SCHEMA_NAME(schema_id) from sys.objects where OBJECTPROPERTY(object_id, N'IsUserTable') = 1 FOR READ ONLY OPEN tableCursor OPEN SchemaCursor FETCH NEXT FROM tableCursor INTO @TableName FETCH NEXT FROM SchemaCursor INTO @SchemaName WHILE (@@Fetch_Status >= 0) BEGIN SET @FullName=@SchemaName+'.'+@TableName INSERT TableStats EXEC sp_spaceused @FullName --Get the next table name FETCH NEXT FROM tableCursor INTO @TableName FETCH NEXT FROM SchemaCursor INTO @SchemaName END CLOSE tableCursor DEALLOCATE tableCursor CLOSE SchemaCursor DEALLOCATE SchemaCursor END
Once you run the above Stored Procedure it executes and loads the Table TableStats. Next Lets go to our Reporting Tool viz Excel 2013. In Excel 2013, Click on the Data and if you already don’t have existing connection to the Sql instance and database, create a new connection as shown below
You can use the following query to fetch the number rows and unused space from Table Stats. The unused space returned from the above stored procedure in string format as it also attaches KB at the end of the number. So we use the following query to get rid of the KB while importing
SELECT tablename, numberofrows, LEFT(unusedspace,LEN(unusedspace)-2) AS unused From TableStats
Once the data is loaded in Excel, you can go to the INSERT tab in Excel 2013 and click App for Office tab and add TreeMap to your Apps, Once you do that you can view the TreeMap view as shown below
In the Name List, you can select the tablename column from the data imported, for the size select the numberofRows column while for the Color select the Unused column.
You can add title to the TreeMap and hide the table and Gridlines following which you will see a great TreeMap as shown below
From the above TreeMap, it becomes very easy to visualize and identify that SalesOrderDetail with 121317 Number of Rows and while Person Table has the highest unused space of 2128 KB which makes it a good candidate to shrink.
There is another great post of TreeMap by Chris Webb who has shown the use of TreeMap and PowerQuery here.
If you would like to download the Excel Workbook, you can download it from here.
Hope this helps !!!
Parikshit Savjani
Premier Field Engineer