williamandrus

About William Andrus

This author has not yet filled in any details.
So far William Andrus has created 4 blog 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.

 

By |July 1st, 2014|Categories: Visualizations|Tags: |

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.TerritoryI