Currently reading "Information dashboard design" of Stephen Few. A book i can highly recommend to you when you are interested in hands on advice, best practices about building dashboards. We all know the dashboardhype: everybody wants a dashboard. Dashboards are sexy. Building a good dashboard is about giving the users a unique and powerful means to presenting information, but they rarely fullfill their promisses. Too bad, because building a dashboard according to the usability rules can greatly enhance insights. During my study i became interested in building applications according to the usability laws that were researched by Don Norman, for instance. One book of him, that is very easy to read and give you great insights of the (bad) design of everyday things, is his famous book : The Design of Everyday Things. In this book he explains some bad examples and some good examples of design of normal things that surrounds us. Great book and when your designing dashboards it could give you some background information.
In this post i'll describe building a piece of a dashboard. In the coming weeks, months i will regularly post some blogs about building pieces of dashboards according to best practices or some great ideas that i've seen in the book of Stephen Few. While reading the book of Stephen Few i got interested in building a sober dashboard and i would like rebuild them in Reporting Services. One example i find particularly interesting. You can find this on page 201, left under. Below you i will show you the end result that i've build with Reporting Services.
First
At first we need to set up a environment in which we can build a dashboard. For this purpose i've created the following SQL Script :
USE [dashboard]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FactSales]') AND type in (N'U'))
DROP TABLE [dbo].[FactSales]
GO
CREATE TABLE [dbo].[FactSales](
[FactSalesID] [int] IDENTITY(1,1) NOT NULL,
[FK_Date_Key] [int] NOT NULL,
[FK_Department_Key] [int] NOT NULL,
[FK_Product_Key] [int] NOT NULL,
[FactSalesCount] [int] NOT NULL,
[SalesAmount] [money] 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].[DimProduct]') AND type in (N'U'))
DROP TABLE [dbo].[DimProduct]
GO
CREATE TABLE [dbo].[DimProduct](
[Product_Dim_key] [int] NOT NULL,
[Product_Code] [int] NOT NULL,
[Product_Description] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FactPurchaseOrder]') AND type in (N'U'))
DROP TABLE [dbo].[FactPurchaseOrder]
GO
CREATE TABLE [dbo].[FactPurchaseOrder](
[FactPurchaseOrderID] [int] IDENTITY(1,1) NOT NULL,
[FK_Date_Key] [int] NOT NULL,
[FK_Department_Key] [int] NOT NULL,
[FK_Product_Key] [int] NOT NULL,
[FactPurchaseOrderCount] [int] NOT NULL,
[FactPurchaseAmount] [money] 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
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.DimProduct (Product_Dim_key, Product_Code, Product_Description)
VALUES
(1, 1, 'Product 1'),
(2, 2, 'Product 2'),
(3, 3, 'Product 3'),
(4, 4, 'Product 4'),
(5, 5, 'Product 5')
INSERT INTO dbo.DimDate
VALUES (0,2000),
(1, 2001),
(2, 2002),
(3, 2003),
(4, 2004),
(5, 2005),
(6, 2006),
(7, 2007),
(8, 2008),
(9, 2009),
(10, 2010),
(11, 2011),
(12, 2012),
(13, 2013),
(14, 2014),
(15, 2015)
INSERT INTO [dbo].[FactSales]([FK_Date_Key], [FK_Department_Key], [FK_Product_Key], [FactSalesCount], [SalesAmount] )
VALUES
(8, 5, 2, 1, 6.25),
(8, 2, 3, 1, 5.96),
(8, 3, 1, 1, 2.95),
(8, 1, 1, 1, 4.98),
(8, 3, 5, 1, 20.00),
(8, 5, 5, 1, 100.00),
(9, 3, 1, 1, 12.70),
(9, 2, 1, 1, 22.67),
(9, 4, 4, 1, 56.16),
(9, 5, 2, 1, 23.16),
(9, 2, 3, 1, 22.13),
(9, 1, 1, 1, 100.12),
(10, 1, 3, 1, 50.0),
(10, 2, 1, 1, 39.95),
(10, 3, 2, 1, 66.05),
(10, 5, 1, 1, 40.95),
(10, 3, 4, 1, 6.05),
(10, 5, 5, 1, 1.95),
(11, 1, 3, 1, 50.0),
(11, 3, 1, 1, 39.95),
(11, 3, 5, 1, 66.05),
(11, 1, 2, 1, 23.0),
(11, 3, 4, 1, 55.95),
(11, 3, 2, 1, 78.05)
INSERT INTO [dbo].[FactPurchaseOrder]([FK_Date_Key], [FK_Department_Key], [FK_Product_Key], [FactPurchaseOrderCount], [FactPurchaseAmount] )
VALUES
(8, 3, 5, 1, 66.67),
(8, 5, 1, 1, 25.00),
(9, 1, 2, 1, 21.95),
(9, 2, 5, 1, 80.45),
(10, 3, 2, 1, 51.02),
(10, 5, 1, 1, 30.05),
(10, 1, 3, 1, 21.00),
(10, 2, 1, 1, 19.95),
(10, 3, 2, 1, 21.05),
(10, 5, 1, 1, 15.95),
(10, 1, 3, 1, 60.0),
(11, 3, 1, 1, 30.95),
(11, 3, 5, 1, 31.05)---
This script will create some tables and will insert some data into the appropriate tables.
Building the cube
The next thing we need to do is building a cube based on this datamodel. Below you can see the structure of this cube.
An image can you tell more than the SQL script above. So i'll explain the case with aid of this diagram. There are two facts Salesorder and purchase order. There are a couple of conformed dimensions :Department, Date and Product. The idea behind this that you need to purchase a product before you can sell it. I don't think i have the purchases in line with the sales. It is just an example. Another dimension POStatus is used for analyzing the status of a purchase order. Below the dimension usage tab:
Building the report
Okay, now it's time to build the report. The first thing is dragging the fields needed in the query designer into the query window.
Just plain and simple.
In the reportinfg services i've dragged a tablix on the report. In the dashboard example i've 7 columns and below you can see how i've created them in the tablix.
1) Product description (Desc)
The product descritption field in the table is simple. It's the product description of the product. This product description is used for grouping the products.
2) Last five years sparkline
I've created a sparkline graph based on the sales amount and a year column.
3) Number (# 2011)
In order to determine the current year i'm using the Globals!ExecutionTime as a rundate. The # column is calculated by summing the counts in tha fact table
=SUM(IIF(Fields!DateYear.Value = YEAR(Globals!ExecutionTime), Fields!Fact_Sales_Count.Value, 0))
4) Amount ($ 2011)
As described at point 3, The salesAmounts are summarized by grouping the products.
=SUM(IIF(Fields!DateYear.Value = YEAR(Globals!ExecutionTime), Fields!Sales_Amount.Value,0))
5) Percentage of total (% $/total (2011))
Calculating the perccentage betwee the productsales and the total product sales is done by the following expression
=SUM(IIF(Fields!DateYear.Value = YEAR(Globals!ExecutionTime), Fields!Sales_Amount.Value,0), "grpProductDescription")/
SUM(IIF(Fields!DateYear.Value = YEAR(Globals!ExecutionTime), Fields!Sales_Amount.Value,0), "tblxSalesPerProduct")
6) Since last year
Calculating the percentage of growth or decline is a bit more difficult. Below you can see the expression i've created. Most of the expression is build around the demand that a - (minus) and + (plus) should be shown in case of a decline or a growth.
=IIF(
SUM(IIF(Fields!DateYear.Value = YEAR(Globals!ExecutionTime), Fields!Sales_Amount.Value,0)) > SUM(IIF(Fields!DateYear.Value = YEAR(DATEADD("yyyy", -1, Globals!ExecutionTime)), Fields!Sales_Amount.Value,0)),
"+" + FORMATPERCENT(CSTR(SUM(IIF(Fields!DateYear.Value = YEAR(Globals!ExecutionTime), Fields!Sales_Amount.Value,0))/
SUM(IIF(Fields!DateYear.Value = YEAR(DATEADD("yyyy", -1, Globals!ExecutionTime)), Fields!Sales_Amount.Value,0))),0),
"-" + FORMATPERCENT(CSTR(SUM(IIF(Fields!DateYear.Value = YEAR(Globals!ExecutionTime), Fields!Sales_Amount.Value,0))/
SUM(IIF(Fields!DateYear.Value = YEAR(DATEADD("yyyy", -1, Globals!ExecutionTime)), Fields!Sales_Amount.Value,0))),0)
)
7) AVG $ (2011)
The last column that i've created is the avg $ (average sales). That is done by aggregating (sum) and dividing the amounts and the counts.
Other Tablix adjustments
There are two more important adjustments to the report: sorting and filtering. The sorting is about sorting the products according their sales effort (regarding to the whole sales). The filtering is about the top 3 bestseller products.
Sorting
Sorting the products is possible by clikcing on the grouping of the products (and not tablix level).
Click on sorting.
And use the following expression for sorting the products. I only implemented the amount. That's enough.
Filter top 3
The last last change in the report is filtering the top 3 products. The filter option that is used is also on the group level.
Below you can see the expression that i've used for filtering
And the expression that i've entered in value field is (don't forget the =) :
=3
And end result.....
Conclusion
The example in the book of Stephen Few has a sparkline as the first column. I haven't read the complete book of Stephen and i do not know why he decided to put the sparkline in the first column but from usability view point i do think that product description should be the first colum. Humans read from top left to bottom right and they are interested in productdescriptions, first.
Geen opmerkingen:
Een reactie posten