SQL Denali Connect Item Dashboard

Dashboards are for everyone, but it is important to provide the correct type of dashboard for the various sets of people who are consuming the data. The dashboards are both designed differently, and function differently. There are three main types of dashboard:

Dashboard Type
Faceted Analytical
Tracking Analysis
Data Mining
Measure Execution, Forecasting
Senior Management
Strategy maps
Initiative Management
There is a definite difference between screens that monitor the state of things and screens that are used for data analysis.  In an article imaginatively entitled Three Blind Men and an Elephant, Stephen Few discusses the concept of a Faceted Analytical Display (FAD), where the data navigator actively consumes the data. One of my favourite quotes from Stephen Few’s Now You See It book is ‘Comparison is the beating heart of analysis’. Translated into the concept of a Faceted Analytical Display, the dashboard has multiple concurrent views of a common data set so that comparisons can be made more easily. 
Normally, when we are creating a dashboard, we need to understand two key items:
        What does the data consumer need to know?
        What does the data consumer need to act upon?
Normally, a dashboard should be underpinned by key strategies and goals, in order to align the organization towards a common series of goals. The dashboard then displays the measurement of the performance of the organization towards the goals.
What matters is that you have the means to expand your analytical reach by viewing the same data in different ways.  In a Faceted Analytical Display, the same dataset is common to all charts, so the viewer is looking at the same dataset in different ways. In order to clarify what a Faceted Analytical Display actually is, this dashboard example presents information from the Microsoft Connect database. If you click on the image, it will take you through to Tableau Public, where the dashboard is hosted:

Connect Items Dashboard

In case you are not familiar, the Microsoft Connect program provides a forum for users to provide feedback to Microsoft in order to improve the quality of Microsoft products. By giving users a say, it allows users to impact the direction of Microsoft products. At the time of writing, Microsoft products are currently accepting bugs in 65 different products, and accepting suggestions in 50 products. The range includes a diversity of Products including Microsoft Office, Bing Map App SDK, and Project Trident. Before we proceed, I’d like to thank the following people for their help in obtaining the data:  Aaron Nelson (Twitter), Nic Cain (Twitter) and Rob Farley (Twitter).
The purpose of the dashboard was to allow people to examine some of the Connect information in more detail, and to expose any patterns in the data.  The dashboard is constituted of four different parts, and a sample can be seen below:
To summarise, the four dashboards cover four different areas:
Number of Denali Connect Items – this answers the question ‘How many Denali Connect Items were opened by year?’
Breakdown of Denali Connect Item Types by Product – this answers the question ‘How many Items could be classified by Product?’
Closed Connect Calls – This answers the question ‘Why were the Connect Calls Closed?’
Resolved Connect Calls – This answers the question ‘Why were the Connect Calls Resolved?’

Each chart in the dashboard is based on the same dataset. Overall, the dashboard was designed in order to facilitate interaction.
It is possible to analyse the data in terms of the item status dimension i.e. active, closed or resolved items, or grouped together by the ‘All’ status. This is done by selecting the appropriate item from the top right hand side box called ‘Split by Item Status’.
The top two graphs are line charts; normally we might expect this categorical data to be a column chart. However, I felt that the identification of patterns using the additional radio button feature might be enhanced by using a line chart since the simplicity might be more revealing. I will be interested to see what the user feedback is!
I have added an overall title for the dashboard, as well as adding in individual titles and captions for each of the four charts. The additional text has been added in order to clarify the ‘business question’ for each chart. Whilst text is not usually necessary, I felt that this dashboard would be consumed by people whom I would never be likely to meet, who wouldn’t be familiar with the data source, and to whom I wouldn’t be able to explain anything verbally. Therefore, I felt that the additional title and caption would be extremely useful.
The dashboard is made up of four individual graphs, which are discussed below:
Number of Denali Connect Items
This chart aims to show a trend of the number of SQL Server Denali connect items that have been opened, over time. An example of the chart is given below:

Number of Connect Items Count

It is clear that the number of ‘Active’ Calls is much higher than the Closed and Resolved calls. Thus, the actual data point shows the number of new calls. The current status of those Connect items is denoted by the colour of the line. So, for example, so far in 2011, there are 566 calls opened which are currently noted as ‘Active’.
Breakdown of Denali Connect Item Types by Product
This chart shows a very rough classification of the Denali Connect items into product areas e.g. SSRS, SSAS and so on.

Breakdown by Product

In terms of classifying the Denali Connect Items, a simple keyword search serves to reveal patterns of organising the Connect Items into subject area e.g. SSRS, SSAS etc. The dashboard shows that SSRS is the most ‘popular’ area for recording new Connect items. As someone with a background in Artificial Intelligence who has delivered projects in natural language processing, very simple keyword searches are considered to be very rudimentary.  Due to time constraints, unfortunately I didn’t have time to create anything more complex than a simple analysis.
The main point is that the chart shows that the SSRS vocabulary is the most prevalent in the Connect items discussion. This is closely followed by SSMS.
Normally you might expect to see a bar chart or similar for this type of data, because it is categorical. I have used a line chart because it emphasised the patterns in the data. For example, SSMS is shown to have a high number of Active connect items, with the higher number of Closed and Resolved status items. I will be interested in the comments!
Closed and Resolved Status Items
The ‘Closed Connect Calls’ chart shows the breakdown of the Closed Connect calls where it’s possible to obtain some additional information about the closure itself. For example, if a Connect item is closed as ‘Non Reproducible’, then this is added here. Connect Items are not included here if there is no closure statement.

Closed Connect Items

The ‘Resolved Connect Calls’ chart is very similar to the ‘Closed Connect Calls’ chart, but shows the reason for the Connect items being given the ‘Resolved’ status. Here is an example:

Resolved Connect Items

The colour scheme remains consistent throughout e.g. if something is ‘Fixed’ for closed, then the same colour is ‘Fixed’ for Resolved too.
Further work will involve keeping the dashboard up-to-date. Unfortunately I don’t have a ‘closed’ date so I can’t work out the duration of the Connect item with certainty. Due to this, I have left this part of the analysis. I would have liked to add this in, and perhaps we will have access to the ‘Closed’ date in the future.

To summarise, the creation of a dashboard needs to start at a point where the purpose and function of the dashboard are identified so that the correct type of dashboard can be utilised. Its design should be in line with the cognitive and perceptual evidence on data visualisation. Finally, it’s good to expose data to allow users to analyse and work with their data. 

Leave a Reply