DawaBI Blog | DawaBI - Page 2 of 10

Blog

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

PASS SQLSaturday Events: July 2014

 

Date Event
July 12 SQLSaturday #312 – Sacramento 2014
July 26 SQLSaturday #302 – Albany 2014
July 26 SQLSaturday #322 – Guatemala 2014

PASS Events

PASS Summit

PASS Summit is the world’s largest and most intensive technical training conference for Microsoft SQL Server and BI professionals. But more than that, it’s your conference planned and presented by the SQL Server community for the SQL Server community.

Since 1999, PASS Summit has delivered the most technical sessions, the largest number of attendees, the best networking, and the highest-rated sessions and speakers of any SQL Server event in the world. Join us for top-notch training, technical tips and tricks, and the connections you need to take your SQL Server skills to the next level.

PASS Summit 2014
Washington State Convention Center
Seattle, WA
November 4-7, 2014

$1,595 until June 27, 2014 – register now!

24 Hours of PASS

24 Hours of PASS delivers free SQL Server and BI/BA training directly to your computer in live webcasts by some of the most knowledgeable and top-rated speakers in the world. Delivered either in 24 back-to-back hours or across two 12-hour days, the 24HOP program also has Portuguese, Russian, and Spanish editions.

Interested in putting on your own language-specific 24HOP event? Contact us. And follow @pass24hop for information about upcoming 24HOP events.

 

By |June 10th, 2014|PASS|

PASS Virtual Chapter Events: June 2014

 

Date Time Title Link
June 11 12:00 MST PASS DBA Virtual Chapter: A Comedy of Errors sponsored by Dell Software https://attendee.gotowebinar.com/register/1711991640373835777
June 11 10:00 MST Whats New in Hyper-V 2012 R2 https://attendee.gotowebinar.com/register/308303418870126593
June 13 12:00 CST SQL 2014 Clustered Updatable Columnstore Indexes https://attendee.gotowebinar.com/register/7946480954532285698
June 17 11:00 UTC Rolling Upgrades, The Easy Way! https://attendee.gotowebinar.com/register/3511824004444615426
June 19 12:00 EST The Accidental Report Designer: Data Visualization Best Practices in SSRS https://attendee.gotowebinar.com/register/5392867398311459330
June 26  2:00 EST Workload Tuning with Distributed Replay https://attendee.gotowebinar.com/register/1254440871617041921
By |June 10th, 2014|Virtual Chapters|

Colorado SQL Events for June 2014

All ColoradoSQL user group meetings start at 5:30 p.m. and provide food and refreshments. There is no cost to attend so bring a co-worker¦or two. These events provide for great learning opportunities as well as networking with other local SQL Server professionals.

BoulderSQL

June 18th

Confio Software in Boulder
4772 Walnut Street, Suite 100
Boulder, CO 80301

Title: 

The Life Cycle of a Query

Have you ever wondered what really happens behind the scenes when you execute a query? In this unique demonstration, Todd will use a video game engine (Unreal Development Kit) and will dramatically show The Life Cycle of a Query in 3D. We will follow a query from beginning to end and “run through” the various SQL Server components all queries must use including the Protocol Layer, Relational Engine, Storage Engine, Buffer Pool and then back out to the client. A volunteer at the end will then don an Oculus Rift Virtual Reality Headset for 3D Gaming and then race through the sections in under a minute to win a prize. Participants will leave with a better understanding of the major components and with a conceptual framework to explore the components more in-depth.

Presenter

Todd Kleinhans  has been working with SQL Server since 1999. He has worked as a dba, developer, data architect, manager, and consultant. Todd has served in the past on the board of the Denver SQL Server User Group and is the current VP of Marketing. He enjoys presenting at user groups, virtual groups, and SQL Saturday events

SpringsSQL

Third Wednesday

Hyatt Place Garden of the Gods
503 West Garden of the Gods Road
Colorado Springs, CO 80919

 

DenverSQL

Third Thursday

Microsoft DTC Office
7579 Technology Way, Suite 400
Denver, CO 80237

Topic:

SQL 2014 In-Memory OLTP What, Why, and How

In-Memory OLTP is one of the most significant new features in the SQL Server 2014 release. In this session we will learn about the benefits of this new feature and how it works inside the SQL engine. We will touch on when it makes sense to use it, and most importantly learn how you can implement this new feature should you see the need in your environment.

Presenter: Andy Vold, Architect RevGen Partners

Andy Vold, a SQL Server MCSA, MCTS and MCITP, is an Architect with RevGen Partners working primarily in business intelligence, SSIS/ETL, and database development. He enjoys helping clients by building database and business intelligence solutions to solve their complex business needs. Andy is a past board member of the Denver SQL Server Users Group.

Northern Colorado

UNC Loveland Center at Centerra
2915 Rocky Mountain Ave.
Loveland, CO. 80538
Breckenridge Conference Room 2nd floor

PASS SQLSaturday Events: June 2014

 

Date Event
June 7, 2014 SQLSaturday #307 – Iowa City 2014
June 7, 2014  SQLSaturday #294 – Philadelphia 2014
June 14, 2014 SQLSaturday #281 – Edinburgh – BI Edition 2014
June 14, 2014 SQLSaturday #316 – Christchurch 2014
June 14, 2014 SQLSaturday #288 – South Florida 2014
June 14, 2014 SQLSaturday #299 – Columbus 2014
June 17, 2014 SQLSaturday #321 – Cairo 2014
June 21, 2014 SQLSaturday #303 – Rochester 2014
June 21, 2014 SQLSaturday #286 – Louisville 2014
June 28, 2014 SQLSaturday #313 – Rheinland 2014
June 28, 2014 SQLSaturday #293 – Maine 2014
By |May 15th, 2014|PASS, SQLSaturday|

The Importance of Networking

In a survey conducted by Business Network SW, the organization found that 87% of businesses partake in some form of networking. The reasons why these individuals felt networking was important varies, but the top three reasons building relationships, looking for new opportunities and business development. Whatever the reason behind choosing to network with other business owners, being around like-minded individuals can help bring about new ideas, offer different ways to implement processes and even result in a few collaborations or new business.

 Where to Network

Finding the time and opportunity to network with other businesses can be a challenge. Conferences are one place where you can come together with people learning about a topic you’re all interested in. For example, at local user group meetings or a conference such as the Mile High Tech Con which will run July 24-26. This is a great opportunity not only to learn more about BI, but to connect with other business professionals.networking

Social media is another good place to connect with others. Participate in blogs, join a group on Facebook or follow complimentary businesses on Twitter.

 How to Network

The business world is made up of people with all different types of personalities. While some are extroverts and find it easy to strike up a conversation with someone new, others are introverts and may find it more difficult to exchange information. One thing that can help both types of personalities is having a prepared couple of sentences that explains who you are and what your company does. While you will want to give it your own personal spin, here is a simple formula you can use.

I am (fill in your name here), the (your role in the company) from (your company). We make/produce/offer (product or service). + Something truly unique.

Having this statement in mind ahead of time will help you avoid a lot of awkwardness. Hopefully the conversation will flow naturally from there. If not, there are always the throw back topics.

  • Where are you from?
  • What are your hobbies?
  • Do you have a family?
  • Do you like to travel? Have you been anywhere recently?

The key is to listen to what the other person is saying and make a connection on a personal level. They will remember you far longer than if you spend the entire time talking about yourself.

Other Tips to Make Your Networking Successful

Once you’ve made a connection, be sure to exchange business cards. Take a minute to jot down a few notes on the back of the card about the conversation you just had with this person. Does he have a Yorkie that he adores? Do you know the dog’s name? Does he like to travel and just returned from Italy? This is important, because when you get home, you’re going to contact each of the people you made a connection with. You’ll want to send them a personal email or letter.

The letter should read something like this:

Dear Jack,

I enjoyed meeting you at the Pass Summit last week. Your Yorkie Samantha sounds like quite a character. I hope she enjoyed the dog toy you brought her home from Italy.

I was thinking about our two companies and how we are similar and realized we might be able to work together on ________.

I would love to set up a time for a conference call so we can discuss this idea further.

Again, it was good to meet you.

Kind regards,

Jane Smith

With this type of connection, you’ll be more likely to gain a trusted business ally who will work with you for years to come.

Why Network?

Networking gives you an opportunity to meet people who don’t normally run in your social or business circles. This in turn brings in fresh ideas and additional contacts you might not otherwise have had. For example, Jack and Jane may decide to endorse one another’s products to their current clients as they are not in competition with one another. Networking has a way of snowballing. You meet one new person, who introduces you to another and so on. It is an excellent way to grow your business and build your professional reputation.

Resources

I had the pleasure and opportunity to meet Don Gabor several years back at a Pass Summit in Seattle. Don is a master at the art of starting conversations and networking. Visit his site at http://www.dongabor.com/ to learn more about him and several of his resources.

By |May 8th, 2014|Networking|

Colorado SQL Events for May 2014

SQL Meetings are training events for SQL Server professionals and those wanting to learn about SQL Server.

Date Group Info
Tuesday, May 6, 2014 Denver SQL Lunch
Wednesday, May 21, 2014 Boulder Policy-Based Management by Tom Norman
Tuesday, May 20, 2014 Northern Colorado Evening Meeting
Wednesday, May 21, 2014 Colorado Springs Evening Meeting

PASS SQLSaturday Events: May 2014

 

Date Event
May 3, 2014 SQLSaturday #285 - Atlanta 2014
May 3, 2014 SQLSaturday #305 - Dallas - BA Edition 2014
May 10, 2014 SQLSaturday #308 - Houston 2014
May 10, 2014 SQLSaturday #298 - Jacksonville 2014
May 17, 2014 SQLSaturday #292 - Detroit 2014
May 31, 2014 SQLSaturday #306 - Redmond 2014

View future events on the SQLSaturday website.

By |April 15th, 2014|PASS, SQLSaturday|