William Andrus, Author at DawaBI

williamandrus

About William Andrus

This author has not yet filled in any details.
So far William Andrus has created 4 entries.

Designing Dashboard Layouts 15 Tips & Tricks

Here is a helpful guide to go through when designing dashboards (before and after). These tip should help your dashboard design by improving the layout, functionality, and usability.

1) Avoid scrolling and/or multiple pages for a single dashboard. Anything more than 1 page is considered a report. The graphs and numbers should always be together, allowing the user to do quick analysis.

2) Choose the correct visualization. Take advantage of visualizations and graphs that allow the user to quickly associate patterns.

a. Example 1: Avoid most gauges, what might work for a car’s dashboard won’t necessary work in a business. They take up space and say very little.

b. Example 2: Pie charts with more than 4-6 items can make it hard to compare sizes of the slices.

c. Example 3: Using text instead of a line chart; can give a better understanding of where the data is going.

DashboardLayout1

d. Example 4: Using a bar chart to try and draw comparisons when numbers would be more forward.

DashboardLayout2

3) Consider flow and transitions. Incorporate links to details and/or filtering, and try to let the eyes rest on the data and graph without unnecessary jumping around.

a. Example: Placing values in the legend but not on the graph, this would cause the user to switch their views constantly when trying to do comparisons.

4) Avoid textual overload, try to keep it visual. Dashboards are meant to be fast and easy to read.

5) Don’t do complex patterns; simple patterns allow the brain to recognize values more quickly. Sort the data to give direction.

Example:

DashboardLayout3

6) Group similar characteristics, making them perceived as a group.

Example:

DashboardLayout4

7) Use the correct grouping. Differing grouping options can convey different messages.

a. Example: If the objective is to view how each region is comparing to each other. Try grouping on the region instead of the quarter.

DashboardLayout5

DashboardLayout6

8) Covert complex data into simple logical stories. Arrange items to give a flow of communication with data laid out logically. Are the items in the data distinguishable or can they be removed or combined. Is the macro point being made clearly? Use spacing and subtle borders to create distinctions between groups and add comparative measures, like percentages to communicate differences.

9) Communicate and drive action, don’t try to use every available feature or show off your skills if it is not necessary.

10) Show focus, if the dashboard was designed for a specific reason, then try highlighting that item.

DashboardLayout7

11) Show insight, don’t show metrics by themselves; this will end up leaving the interpretation to the user. Give insight by showing metrics with benchmarks, goals, and prior performance to give context. Give hits & misses, root causes, reference lines, goals, etc.

12) Don’t clutter, always give enough whitespace, and never add background logos, stock photographs, large company logos, or too many links to the dashboard.

13) Make it interactive. Users have their own questions and area of expertise. Allowing the user to filter the view, drill down, and examine underlying data will give them confidence and focus in the area they need most.

14) Stick with the basic everyday graphs, and avoid the cute uncommonly seen graphs. Visualizations like bar graphs, line graphs, heat maps, and scatterplots are popular, because they are easy to read.

15) Keep the write-ups short, sweet and simple avoid dryness. Try to avoid technical jargon, if it makes it less understandable.

 

WRITING QUERIES WITH MULTIPLE GROUPING SETS

Using the basic group-by in a T-SQL query is very common when dealing with grouping, but there is one other option that often gets overlooked. In SQL-Server, you can also do multiple grouping sets, by grouping on different sets of fields. The benefits, a more efficient and all-in-one query; versus doing separate individual, group-by, queries put together via a union. Here is a basic example of using grouping sets on the Adventure Works database.

SELECT
[CustomerID]
,TerritoryID
,SUM([SubTotal]) AS TotalSpent
FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]
GROUP BY GROUPING SETS
(
(CustomerID),
(TerritoryID),
()
)
ORDER BY TotalSpent desc;

In this query, we are doing three types of grouping, on CustmerID, TerritoryID, and empty group. The results of the query:

GroupingSets1

Each of the groups are distinguishable by null values, used as placeholders in the columns that are not used in the set.

 1) CustomerID Gives the total spent per customer, with null value in the TerritoryID column. Results found in row 12 and up.

 2) TerritoryID Gives the total spent in each territory, with null value in the CustomerID column. Results found in row 2 11.

 3) An empty grouping set, giving a grand total for all rows, with null values in both TerritoryID and CustomerID columns. Results found in row 1.

With the grouping set, there are also two short-cut abbreviations (CUBE, ROLLUP) that can be applied instead of writing out every possibility of those sets.

CUBE

Cube, is a way to define all possibilities of a set-group down to the empty set. A query written without the short-cut:

SELECT
[CustomerID]
,TerritoryID
,SUM([SubTotal]) AS TotalSpent
FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]
GROUP BY GROUPING SETS
(
(CustomerID,TerritoryID),
(CustomerID),
(TerritoryID),
()
)
ORDER BY TotalSpent desc;

Can be shortened to:

SELECT
[CustomerID]
,TerritoryID
,SUM([SubTotal]) AS TotalSpent
FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]
GROUP BY CUBE (CustomerID,TerritoryID)
ORDER BY TotalSpent desc;

ROLLUP

Rollup is a short-cut used when dealing with hierarchical fields. For this example, the hierarchy of the fields, from broad to narrow (Group, CountryRegionCode, Name, TerritoryID). For the long version of the query:

SELECT
st.[Group]
,st.CountryRegionCode
,st.[Name]
,st.TerritoryID
,SUM([SubTotal]) AS TotalSpent
FROM [AdventureWorks2012].[Sales].[SalesOrderHeader] soh
INNER JOIN [AdventureWorks2012].[Sales].[SalesTerritory] st on soh.TerritoryID = st.TerritoryID
GROUP BY GROUPING SETS
(
(st.[Group],st.CountryRegionCode,st.[Name],st.TerritoryID),
(st.[Group],st.CountryRegionCode,st.[Name]),
(st.[Group],st.CountryRegionCode),
(st.[Group]),
()
)
ORDER BY TotalSpent desc;

Can be replaced with:

SELECT
st.[Group]
,st.CountryRegionCode
,st.[Name]
,st.TerritoryID
,SUM([SubTotal]) AS TotalSpent
FROM [AdventureWorks2012].[Sales].[SalesOrderHeader] soh
INNER JOIN [AdventureWorks2012].[Sales].[SalesTerritory] st on soh.TerritoryID = st.TerritoryID
GROUP BY ROLLUP (st.[Group],st.CountryRegionCode,st.[Name],st.TerritoryID)
ORDER BY TotalSpent desc;

The table results:

GroupingSets2

COMBINING ROLLUP & CUBE

When combining grouping sets,

SELECT
st.[Group]
,st.CountryRegionCode
,st.[Name]
,st.TerritoryID
,[CustomerID]
,SUM([SubTotal]) AS TotalSpent
FROM [AdventureWorks2012].[Sales].[SalesOrderHeader] soh
INNER JOIN [AdventureWorks2012].[Sales].[SalesTerritory] st on soh.TerritoryID = st.TerritoryID
GROUP BY ROLLUP (st.[Group],st.CountryRegionCode,st.[Name],st.TerritoryID), CUBE([CustomerID],st.TerritoryID)
ORDER BY TotalSpent desc;

The total resulting set ends up as the multiplication of each set.

CUBE (CustomerID,TerritoryID) will give 4 sets
(
CustomerID + TerritoryID,
CustomerID,
TerritoryID,
{}
)

ROLLUP (st.[Group],st.CountryRegionCode,st.[Name],st.TerritoryID) will give 5 sets
(
Group + Region + Name + TerritoryID,
Group + Region + Name,
Group + Region,
Group,
{}
)

The resulting set total will end up being 20 sets:

DEALING WITH NULL

Using null-values to distinguish sets, works as long as there are no null values coming through the query. If we used a query, where SalesPersonID and TerritoryID could be null:

SELECT
SUM(SubTotal) as CalcSubTotal,
SalesPersonID,
TerritoryID
FROM
[Sales].[SalesOrderHeader] soh
GROUP BY CUBE (SalesPersonID, TerritoryID)
ORDER BY CalcSubTotal desc;

GroupingSets3

The results can end up being confusing. Is row 1 or row 2 the empty set total? To get around this problem, we have two functions that can be used, Grouping, and Grouping_Id.

Grouping

SELECT
SUM(SubTotal) as CalcSubTotal,
SalesPersonID,
GROUPING(SalesPersonID) AS SalesPersonGrouping,
TerritoryID,
GROUPING(TerritoryID) AS TerritoryGrouping
FROM
[Sales].[SalesOrderHeader] soh
GROUP BY CUBE (SalesPersonID, TerritoryID)
ORDER BY SalesPersonGrouping desc, TerritoryGrouping desc, CalcSubTotal desc;

The results from the above query, a little backwards, where 0 represents the grouping column(s), and 1 represents the column(s) not used in the grouping. From the table below, row 1 represents the empty set, and row 2 represents the total with no or unknown sales person.

GroupingSets4

Grouping_ID

 

SELECT
SUM(SubTotal) as CalcSubTotal,
SalesPersonID,
TerritoryID,
GROUPING_ID(SalesPersonID, TerritoryID) as GrpingID
FROM
[Sales].[SalesOrderHeader] soh
GROUP BY CUBE (SalesPersonID, TerritoryID)
ORDER BY CalcSubTotal desc;

The grouping_id functions accepts a list of grouping columns to be used. The results, giving an integer value, is actually the conversion from a binary value (e.g. 00 = 0, 01 = 1, 10 = 2, 11 = 3). Just like the grouping function, the concept of ones and zero are reversed; where, 0 represents the grouping and 1 is where the column was not used.

GroupingSets5

12 Avoidances in the initial dashboard design phase

There are many milestones through the lifecycle of a dashboards. The initial stages are the most important, in preventing headaches or failure. This list will hopefully give some guidance to start the ball rolling on a correct path to success.

1)      Avoid waiting for technology waiting for traditional BI projects to begin could take months or even years with delays. Find what technology you have available and start working on some implementations. Once you have started some ground work, the design can easily be applied to other BI technologies with little adjustments needed.

2)      Avoid underestimating time and resources – Though a dashboard is designed to be typically compact, it does not mean it will be quick and easy to create and maintain. Expect it to be an on-going, frequently changing process.

3)      Avoid static/stale data Having data automatically and frequently updateable is very important to the user and designer.  Static or stale data will lead to a false sense of confidence.

4)      Avoid limited access Making dashboards easily accessible is ideal. Making it easy for the user to access the dashboard will allow for more frequent uses and feedback; helping keep the dashboard current and useful. For example, distributing dashboards on the web can help keep data current and still adhere to IT protocols and security standards.

5)      Avoid choosing the wrong metrics Find the metrics that will end up answering the underlying questions.

  1. How does each metric contribute to the objectives?
  2. Is there data that can shed light on the objective?
  3. Is there a meaningful metric that measures those contributions?
  4. Are the metrics, chosen, necessary to contribute to the objective?
  5. Is the metric a continuous organized measurement?

6)      Avoid not knowing the goals and objectives of the dashboard. Some examples of the most common goals found in companies can be generalize as:

  1. Make better-informed business decisions.
  2. Improve customer interaction and satisfaction.
  3. Gain an overall competitive advantage.

The goal(s) usually can be seen having these characteristics at its core:

  1. Objective-focused
  2. Visual
  3. Relevant
  4. Current
  5. Accessible to its audience

7)      Avoid meaningless variety When starting, keep it simple and aim at 3-5 metrics. Never go above 10 metrics. Too many metrics will likely cause added charts and graphs that add no value to the dashboard.

8)      Avoid ignoring the target audience give the audience what they need (e.g. timely summaries, links to supporting details, what actions are applicable, etc..) The usual types of audiences can basically be broken down into 3 categories.

  1. Strategic (Executives) these users want a summary of the overall health, long term progress and goals, historical data.
  2. Operational (Department Heads) these users need a snapshot of the current operation, clear meaning, real time data, interaction and link to information.
  3. Analytic (Engineers, Programmers, Researchers, Lawyers, Paralegals, etc¦) these users need ways to do comparisons, review historical data, evaluate performance, drill into causes, needs feedback, established goals to course-correct.

9)      Avoid using metrics no one understands it is wise to avoid any unnecessary teaching of some new metric. Stick to the common and familiar metrics that the audience will know of and understand.

10)   Avoid using compound metrics Compound metrics are usually developed by using a formula comprised of a variety of measurements to come up with a single value. (Example: FICO, Klout, etc¦) The problem is that compound metrics masks insight and understanding making it hard to compare and contrast the real underlying numbers.

11)   Avoid blindly following easy measurements Easy measurements are great, but can cause users to lose sight of the bigger, more important picture. For example, SAT scores may be a great measurement of what a student’s potential will be at a college, it can also cause a blind spot on the whole life of the student.

12)   Avoid complexity Keep it simple. Focus on trends rather than point-in-time comparisons. Keep focus on the handful of metrics and design principles throughout the dashboard(s). Reduce, minimize, or eliminate non-data elements. Try reducing emphasis by changing the size, color, and borders; meanwhile, removing data that does not add insight. Draw attention and maximize the important data element.

Some helpful resources:

 

By |January 28th, 2014|Visualizations|

A Quick Intro into SQLCLR User Defined Functions

 Initial Setup

For a quick start guide into SQLCLR, the easiest first step will mostly like be creating a user defined function.

To get started, I’m using the following installations:

  • SQL Server Data Tools for Visual Studio 2012 (http://msdn.microsoft.com/en-us/jj650015)
  • Microsoft SQL Server Data Tools Business Intelligence (http://www.microsoft.com/en-us/download/details.aspx?id=36843)
  • SQL Server 2012
  • A database to deploy to, I’ll be using Adventure Works 2012 (http://msftdbprodsamples.codeplex.com/releases/view/93587)

Creating the Project

To get started, open up SQL Server Data Tools for Visual Studio 2012. Select new project and go to the Other Languages in the templates section. Name the project (SQLCLRDemo):

CLRIntro1

Once in the project, in the Solution Explorer, right click on the project and select Add then New Item:

CLRIntro2

There are 5 basic types of SQL CLR objects that can be developed in C# for SQL Server:

  • Aggregate
  • Stored Procedure
  • Trigger
  • User Defined Function
  • User Defined Type

 Creating a User Defined Function

Let’s start with the easiest and most used SQLCLR type, User Defined Function. For this example I’ll create a simple function called IsLeapYear which will accept a year as an integer value and return Boolean:

CLRIntro3

I’m not going to do any special coding for this example, since C#’s DateTime library has a nice slew of usable functions. Here is the user defined function template with the added code:

//------------------------------------------------------------------
// <copyright file="CSSQLFunction.cs" company="Microsoft">
//    Copyright (c) Microsoft Corporation.  All rights reserved.
// </copyright>
//------------------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
     [Microsoft.SqlServer.Server.SqlFunction]
     public static SqlBoolean IsLeapYear(SqlInt32 year)
     {
        // Put your code here
        return DateTime.IsLeapYear((int)year);
      }
}

As you may have noticed the data types used in SQL Server and C# are different, where SQL Server’s data types are given the prefix of SQL. When using built-in C# libraries, you will need to cast to a C# type.  The return data type, for the function, will need to be a SQL data type (i.e. SqlBoolean).

So build the project, to verify that code is correct. Before deploying, you may want to check and verify the project’s properties. (PROJECT > SQLCLRDemo Properties¦) Here you will be able to set the target SQLServer platform, default schema type, database settings, permission level, etc¦

CLRIntro4

Deploying to SQL Server

To publish right-click the project and select Publish¦.

CLRIntro5

Select the target database and then click publish. Verify in the Data tools Operations pane that everything goes over smoothly:

CLRIntro6

You should then see the new scalar-valued function in the database (you may have to refresh the list):

CLRIntro7

Testing the SQLCLR function

So far so good, let’s test the function:

USE [AdventureWorks2012]
GO

;WITH cteYears AS
(
	SELECT 2013 as iYear
	UNION ALL
	SELECT 2012 as iYear
	UNION ALL
	SELECT 2011 as iYear
	UNION ALL
	SELECT 2010 as iYear
)

SELECT 
iYear,
dbo.IsLeapYear(iYear)
FROM cteYears
GO

Uh, oh. We get an error:

CLRIntro8

Simple enough, just need to enable CLR Integration (http://technet.microsoft.com/en-us/library/ms131048.aspx) just run this script on the targeted database:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Re-run the test script and finally get our results:

CLRIntro9

Conclusion

Creating some SQLCLR functions could make SQL development easier and provide a faster technique versus regular SQL-Server functions.

The other SQL CLR types can get more complicated with higher security needs, potential un-safe code, serializations, and importing dlls to SQL-Server, as some examples. I will try to do some more in-depth SQLCLR tutorials in the future that will run into some of these situations.

 

By |January 21st, 2014|Uncategorized|