DawaBI Blog | DawaBI

Blog

PASS SQL Saturday Events: December 2014

 

DateEvent
December 6 SQLSaturday #359 – Istanbul 2014
December 6 SQLSaturday #351 – Lima 2014
December 6 SQLSaturday #347 – Washington DC 2014
December 13 SQLSaturday #356 – Slovenia 2014
December 13 SQLSaturday #354 – Charleston 2014
By |November 15th, 2014|PASS, SQLSaturday|

PASS SQL Saturday Events: November 2014

 

DateEvent
November 1 SQLSaturday #339 – Shanghai 2014
November 1 SQLSaturday #337 – Oregon 2014
November 22 SQLSaturday #355 – Parma, Italy 2014
November 22 SQLSaturday #353 – London – BA Edition 2014
November 22 SQLSaturday #350 – Winnipeg 2014

 

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

PASS SQL Saturday Events: October 2014

 

DateEvent
October 4 SQLSaturday #335 – Athens 2014
October 4 SQLSaturday #336 – Holland 2014
October 4  SQLSaturday #344 – Tirana 2014
October 4 SQLSaturday #315 – Pittsburgh 2014
October 4 SQLSaturday #330 – Charlotte – BI Edition 2014
October 11 SQLSaturday #319 – Panama 2014
October 11 SQLSaturday #333 – St. Louis 2014
October 11 SQLSaturday #311 – Bulgaria 2014
October 18 SQLSaturday #345 – Riyadh 2014
October 18 SQLSaturday #341 – Porto 2014
October 18  SQLSaturday #314 – Providence 2014
October 25 SQLSaturday #352 – Sydney 2014
October 25  SQLSaturday #338 – Barcelona 2014
October 25 SQLSaturday #329 – Rio de Janeiro 2014
October 25 SQLSaturday #348 – Ica, Peru 2014
October 25 SQLSaturday #326 – Tampa – BA Edition 2014
October 25 SQLSaturday #332 – Minnesota 2014
October 25 SQLSaturday #349 – Salt Lake City 2014
By |September 15th, 2014|PASS, SQLSaturday|

PASS Virtual Chapter Events: September & October 2014

PASS Virtual Chapter Events: September 2014

Join SQL Server professionals from around the world for free online technical training and networking year round. PASS Virtual Chapters (VC) are interest-based forums that unite the SQL Server community through live webcasts with top SQL Server experts, online forums and resources including an archive of past presentations, and special training events.

DateTimeTitle Link
September 16th 11:00 CST The Roadmap to Better Performance: Reading Query Plans https://attendee.gotowebinar.com/register/3935830875052414466
September 17th 12:00 PST Learn JavaScript Properly Part 2 https://attendee.gotowebinar.com/register/4322399372235297538
September 17th 4:00 EST Absolute Beginners Presentation for Power BI for Office 365https://attendee.gotowebinar.com/register/4757737807317828865
September 18th 12:00 EST Insider's Introduction to Microsoft Azure Machine Learning (AzureML)http://bit.ly/PASSBAVC091814
September 26th 11:00 EST Improving Query Efficiency with Data Access Layers http://oracle.sqlpass.org/
October 8th  12:00 CSTUnderstanding your SQL Server IO Bottlenecks and how Flash can solve them https://attendee.gotowebinar.com/register/6093820357156042754
October 16th 10:00 EST MrExcel's Favorite Excel Tips & Tricks https://attendee.gotowebinar.com/register/8089449354432962050

 

By |September 11th, 2014|PASS, Virtual Chapters|

PASS Virtual Chapter Events: August 2014

 

DateTimeTitle Link
August 15th 12:00 EST Manage Your Shop with CMS and Policy Based Management https://attendee.gotowebinar.com/register/4279519516082609665
August 19th 11:00 CST The Roadmap to Better Performance: Reading Query Plans https://attendee.gotowebinar.com/register/2124351382524970497
August 20th 12:00 PST Learn JavaScript Properly http://bookreaders.sqlpass.org/
August 21st 12:00 EST Looking Through the Windshield: Creating Forward-looking Dashboards and KPIs  https://attendee.gotowebinar.com/register/5282114692357849346
August 21st 1:00 EST SQL Server Security Easy Button https://attendee.gotowebinar.com/register/4752057729799585025
August 27th  12:00 EST The Curious Case of Isolation Levels http://dba.sqlpass.org/

 

By |August 15th, 2014|PASS, Virtual Chapters|

PASS SQL Saturday Events: September 2014

 

DateEvent
September 6 SQLSaturday #301 – Cape Town 2014
September 6 SQLSaturday #320 – Raleigh 2014
September 13 SQLSaturday #323 – Paris 2014
September 13 SQLSaturday #300 – Kansas City 2014
September 20 SQLSaturday #290 – Kiev 2014
September 20 SQLSaturday #310 – Dublin 2014
September 20 SQLSaturday #331 – Denver 2014
September 20 SQLSaturday #340 – San Diego 2014
September 27 SQLSaturday #325 РṢo Paulo 2014
September 27 SQLSaturday #343 – Medellin 2014
September 27 SQLSaturday #334 – Boston – BI Edition 2014
September 27 SQLSaturday #318 – Orlando 2014
September 27 SQLSaturday #342 – Mobile 2014
By |August 14th, 2014|PASS, SQLSaturday|

PASS SQL Saturday Events: August 2014

 

DateEvent
August 2 SQLSaturday #324 – Baton Rouge 2014
August 9 SQLSaturday #304 – Indianapolis 2014
August 23 SQLSaturday #328 – Birmingham 2014
August 23 SQLSaturday #309 – Oklahoma City 2014
August 30 SQLSaturday #327 – Johannesburg 2014
August 30 SQLSaturday #317 – Oslo 2014
By |July 15th, 2014|PASS, SQLSaturday|

Will you be attending Mile High Tech Con?

Technical conferences have always been a great venue for networking and increasing my knowledge base. Mile High Tech Con will be no different. I would like to invite you to be part of the inaugural Mile High Tech Con which will be held July 24 -26 in Denver, Colorado. Mile High Tech Con will highlight innovative topics that will inspire strategic decision making while offering relationship building and networking.MHTC-Twitter

Here are the speakers and topics:

SpeakerTopic
Bill Fellows SSIS package generation via Biml
Carlos Bossy Getting Started with Big Data and Business Intelligence
Jeff Renz Dashboards More Than One Way to Visualize Data
Meagan Longoria Somebody Got BIDS Helper in my Data Tools!
Peter Myers Taking Your Application Design to the Next Level with SQL Server Data Mining
Reeves Smith Master Data Services, How Does it Apply to My Enterprise?
Scot Reagin Strategies for Fixing Broken Information Environments
Tim Mitchell Error Handling Patterns in SSIS
Bill Fellows Getting started with R and SQL Server
Carlos Bossy SQL Server Replication in a Business Intelligence Framework
Dan Lewandowski Securing the cube
Jeff Renz Tabular Models
Meagan Longoria Don't Miss Out on Excel Cube Functions
Peter Myers Big Data Analytics with Excel
Reeves Smith Increase your SSIS productivity with Biml
Scot Reagin Taking the Mystery out of DAX
Tim Mitchell Introduction to Data Quality Services
Carlos Bossy Predictive Analytics in the Enterprise
Peter Myers Superhero Power BI
Tim Mitchell Real World SSIS

There is still time to register and attend. You don’t want to miss it!

By |July 9th, 2014|Networking|

A Simple TSQL Pivot

I’ve always had issues writing a TSQL Pivot statement without going back to books-on-line to figure it out.  Maybe because I haven’t done it enough, but it always seems a bit convoluted.  That meant it was time to write about it and break it down so I could make sense of it.  Let’s set up the demo data first.  Based on the data I was working on pivoting I wrote up some simple demo data.  It has a name, objective and a result for the said objective.  It has one record per objective and the pivot should line up those objectives so there is one row per name instead of one row per objective.

DECLARE @BaseData table (  

Name   varchar(10)  

,Objective  varchar(5)  

,Result   varchar(15)  )

 

INSERT @BaseData

SELECT ‘Bob’,’Obj1′,’Complete’

UNION

SELECT ‘Bob’,’Obj2′,’Pending’

UNION

SELECT ‘Lisa’,’Obj1′,’Pending’

UNION

SELECT ‘Lisa’,’Obj2′,’Pending’

UNION

SELECT ‘Don’,’Obj1′,’Complete’

UNION

SELECT ‘Seth’,’Obj1′,’Complete’

UNION

SELECT ‘Seth’,’Obj2′,’Complete’

UNION

SELECT ‘Megan’,’Obj1′,’Pending’

UNION

SELECT ‘Megan’,’Obj2′,’Complete’

 

 

SELECT * FROM @BaseData

 

 Now to work on understanding the pivot.  Here’s the finished Pivot statement for reference: 

PIVOT(MAX(Result) for Objective in ([OBJ1],[OBJ2])) as ResultPivot

 The [Result] column is the data that needs to be pivoted.  The first stumbling part of the pivot is that the data that is being pivoted has to be wrapped in some kind of accumulation.  So if you are dealing with numerical data it can be a MIN, MAX, AVG, etc.  With string data, which is what I dealt with the easiest is MIN or MAX.  Since there is only one result per objective in this data, the MAX will be that value.

The next part of the pivot is naming the columns that the [Result] data will be pivoted into.  I associated this to telling the PIVOT command what the key was to the data to be pivoted.  In this case there are consistent values in the Objective column of Obj1 and Obj2.  Not all Names in the data have an Obj1 and Obj2 which is fine as you will see.  If you don’t have a consistent value like this I could see using a derived column like a ROWNUMBER() or something similar to give the data values to create columns on.

After naming the columns that the pivoted data will go into the entire statement needs an alias.  It won’t necessarily be used in the SELECT statement but the PIVOT statement will throw errors if it is not aliased.

Here is how the PIVOT is worked into the query. 

SELECT Name, OBJ1 as Objective1, OBJ2 as Objective2                FROM @BaseData

                PIVOT(MAX(Result) for Objective in ([OBJ1],[OBJ2])) as ResultPivot

 Notice in the SELECT statement the columns from the pivot get the names from the PIVOT statement.  I did this because I wanted to alias the column names to be more descriptive.  If you get rid of the column names and just put in SELECT it will work too.  If you put the [RESULT] column in the columns to SELECT the statement will error and throw Invalid Column Name.’  The [RESULT] column basically no longer exists when the data is pivoted.  When the query is run (with the table variable above to create the data) the results will look like this:

NameObjective1Objective2
BobCompletePending
DonCompleteNULL
LisaPendingPending
MeganPendingComplete
SethCompleteComplete

 If you look at Don’s record you will see that Objective 2 is NULL.  That objective does not exist for him.  If there are hundreds or thousands of records that are being pivoted and a flag needs to be created if there are missing objective records it’s simple.  Those objective columns can be used in the query just as normal columns.  In the query below you can see how a CASE statement was added to flag those people with missing records.

SELECT Name, OBJ1 as Objective1, OBJ2 as Objective2                ,CASE

                                WHEN OBJ1 is NULL THEN ‘TRUE’

                                WHEN OBJ2 is NULL THEN ‘TRUE’

                                ELSE ”

                                END as ErrorFlag

                FROM @BaseData

                PIVOT(MAX(Result) for Objective in ([OBJ1],[OBJ2])) as ResultPivot

 The results of this query will have an additional column called ErrorFlag that will have a value of TRUE if there is a NULL in Objective 1 or Objective 2.  This will allow for analysis of the data to easily point out those records.

NameObjective1Objective2ErrorFlag
BobCompletePending 
DonCompleteNULLTRUE
LisaPendingPending 
MeganPendingComplete 
SethCompleteComplete 

 Another way of making the NULL data stand out or if NULL data doesn’t matter at all the OBJ1 and OBJ2 could be wrapped in an ISNULL.  That could be used to put in an empty string or some value to indicate problems with the data.

Hopefully, this helps out.  I’m sure the next time I write a PIVOT I’ll have to dig out this posting and skim through it real quick to remember the details.

By |July 8th, 2014|TSQL|

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.