SSAS/SSRS : Building different gauges with MDX.

Introduction
For one of my customers i've to build a dashboard. This customer wants to improve the quality of service. There are discussions about how do you measure quality, what is good service and what is bad service. The problem is that there are many departments and how do you compare them. Is it recommended to say below 50 is ok and above is not ok? So what is good and what is wrong? Just showing the number is also a bad practice (without any reference).

Another approach i've read in the book of Stephen Few : "Information dashboard design" and he showed an example of a gauge with an organization average and an industry best practice (benchmarking). Below you can see a diagram about combining the department average, organization average and an industry best practice. An other idea pops in my mind about comparing departments peer2peer with same departments of  other organizations.

So in theory there are four measures possible for comparing values.

• Department average.
• Organization average.
• Industry best practice.
• Peer2peer department average (for later).

Red, orange and green imply a judgement of the figures. So i decided not use these colors. At this moment i'm using more neutral colors like purple and darkblue.

Prerequisites
Okay, let's setup the case. First setup the datamodel and insert some test data. Below i've drawn a star datamodel with two facts a department level fact and a benchmark fact (for showing the industry best practice average). The department level fact is a fact with data from the own organization and the benchmark fact is information that is gathered, stored, processed and distributed by an exernal benchmark organization. For simplicity i'll assume that the one measure of department level fact will be compared with the measure of the benchmark fact. In the real world there will be multiple organizations star schemas and they need to be coupled with the benchmark fact.

So there four dimensions : OrgDepartment, Date, organization and P2Pdepartment (for later blogposts). So i've created a script for  this purpose.


USE [Gauge]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeptLevelFact]') AND type in (N'U'))
DROP TABLE [dbo].[DeptLevelFact]
GO

CREATE TABLE [dbo].[DeptLevelFact](
[DeptLevelFactID] [int] IDENTITY(1,1) NOT NULL,
[FK_Date_Key] [int] NOT NULL,
[FK_Department_Key] [int] NOT NULL,
[DeptLevelFactCount] [int] NOT NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDepartment]') AND type in (N'U'))
DROP TABLE [dbo].[DimDepartment]
GO

CREATE TABLE [dbo].[DimDepartment](
[Department_Dim_key] [int] NOT NULL,
[Department_Code] [int] NOT NULL,
[Department_Description] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BenchMarkFact]') AND type in (N'U'))
DROP TABLE [dbo].[BenchMarkFact]
GO

CREATE TABLE [dbo].[BenchMarkFact](
[BenchMarkFactID] [int] IDENTITY(1,1) NOT NULL,
[FK_Date_Key] [int] NOT NULL,
[FK_Organisation_Key] [int] NOT NULL,
[BenchMarkFactCount] [int] NOT NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimDate]') AND type in (N'U'))
DROP TABLE [dbo].[DimDate]
GO

CREATE TABLE [dbo].[DimDate](
[Date_dim_key] [int] NULL,
[Date_Year] [int] NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimOrganisation]') AND type in (N'U'))
DROP TABLE [dbo].[DimOrganisation]
GO

CREATE TABLE [dbo].[DimOrganisation](
[Organisation_Dim_key] [int] NOT NULL,
[Organisation_Code] [int] NOT NULL,
[Organisation_Description] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
---

INSERT INTO dbo.DimDepartment (Department_Dim_key, Department_Code, Department_Description)
VALUES
(1, 1, 'Department A'),
(2, 2, 'Department B'),
(3, 3, 'Department C'),
(4, 4, 'Department D'),
(5, 5, 'Department E')



INSERT INTO dbo.DimDate
VALUES (1,2000),
(2, 2001),
(3, 2002),
(4, 2003),
(5, 2004),
(6, 2005),
(7, 2006),
(8, 2007),
(9, 2008),
(10, 2009),
(11, 2010),
(12, 2011),
(13, 2012),
(14, 2013),
(15, 2014),
(16, 2015)

INSERT INTO dbo.DimOrganisation (Organisation_Dim_key, Organisation_Code,Organisation_Description)
VALUES
(1, 1, 'Organisation X'),
(2, 2, 'Organisation Y'),
(3, 3, 'Organisation Z')

INSERT INTO [dbo].[DeptLevelFact]([FK_Date_Key],[FK_Department_Key],[DeptLevelFactCount])
VALUES
(10, 1, 3),
(10, 2, 5),
(10, 3, 8),
(10, 5, 1)


INSERT INTO [dbo].[BenchMarkFact]([FK_Date_Key],[FK_Organisation_Key],[BenchMarkFactCount])
VALUES
(10, 1, 3),
(10, 2, 5),
(10, 3, 7)


Building the cube
Based on the script above i've created a cube. This structure of the cube can be seen in the diagram below:

I've deployed  the project and the next step is building the report.

Building the report
Now let's start another session of visual studio (BIDS) and to create a new report. I've added the following query to the dataset.

WITH
MEMBER [Measures].[avg measure by all departments] AS
    AVG({[DimDepartment].[DepartmentDescription].[DepartmentDescription]},
    [Measures].[DeptLevelFactCount])
MEMBER [Measures].[avg measure by department C] AS
    AVG({[DimDepartment].[DepartmentDescription].&[Department C]},
    [Measures].[DeptLevelFactCount])
MEMBER [Measures].[avg measure by all Organisations] AS
    AVG({[DimOrganisation].[OrganisationDescription].[OrganisationDescription]},
    [Measures].[BenchMarkFactCount])
SELECT
{
[Measures].[avg measure by all departments],
[Measures].[avg measure by department C],
[Measures].[avg measure by all Organisations]
}
ON COLUMNS
  
FROM ( SELECT ( { [DimDate].[DateYear].&[2009] } ) ON COLUMNS
    FROM [Gauge])
WHERE ( [DimDate].[DateYear].&[2009] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS


And i've dragged some gauges on the dashboard.
.

The radial gauge (1) is the one i initially preferred but while reading more about the usability of this diagram the less i prefer (see below for the usability tips). The second is a linear gauge and is like a thermometer. The third is also a linear gauge but with ranges. This would imply that a indicator is judged somehow in right or wrong. The fourth lineair gauge is a gauge with precentages.  I didn't manage to change the percentages into absolute numbers. And the last gauge (5) is the same as 2 but horizontal positioned.

Usability tips
From studying (old study) books, internet,  etc i can give you some handy tips and best practices when you're developing gauges and dashboards:

• The first tip is don't use radial gauges (1) ;-) in your dashboard because they use a lot of space and the added value is low. If do want to use gauge in your dashboard, use the lineair gauge (2, 4 or 5). This diagram uses less space on your dashboard and gives you the same information.
• When you do use the radial gauge don't punt the numbers on the inside of the scale. Put them on the outside of the scale. This way the pointer won't overlap the numbers. This will enhance faster readability of the gauge.
• If you're using the radial gauge put the markers on the inside.There are three options available and this the least worst option. The other options are outside and cross. The outside option could cover the numbers with the marker and cross will do the same but centers between the inside and the outside option. So i would recommend using the inside option.
• In the example i used multiple indicators for an average of all departments and an average of all other organisations. I used different color for these indicators. But one thing i did not manage was adding a label (avg departments or avg organisations) to the indicators. For beter readability i would have liked that. Again this would have enhanced readability. Perhaps i'll find out in the future.
• The use of colors is also an important choice. In number 2 and 5 i've used red as a color and red indicates as there is a danger, problem, etc. In this example is the color red not a very good choice.

Conclusion
There is a lot possible with these gauges and you can play for hours with these things. I think that dashboard design is not about putting all kind of gauges, diagrams, graphs on a window but you have to think about how to present the information on the screen for the best usability possible. That's the challenge when you're developing a dashboard.

Greetz,
Hennie