SQL Server Pro Magazine: Datazen Mobile Dashboards with Analysis Services and Drill-through

This post is a teaser for an article I just published to SQL Server Pro Magazine.  This is the third and not the final article about using Datazen to build mobile dashboards and business intelligence solutions.  The entire article is available here.

Figure%202%20-%20%20iPad%20Dashboard%202_0[1]

(excerpt)
A few months ago, shortly after Microsoft announced their acquisition of the Datazen mobile dashboard product, I wrote two articles titled Getting Started with Datazen and Setting Up a Datazen Server.  Datazen is a simple dashboard tool with native apps for all the major mobile device platforms.  I mentioned that the tool had shortcomings with Analysis Services as a data source.  When writing MDX queries, it was necessary to create calculated members for all of the row members and place them on the columns axis.  An update released in August, 2015 improved the MDX query functionality where the work-around is no longer necessary.  In this article, I’ll demonstrate a pair of drill-through dashboards using MDX queries and navigation that pass parameters to an MDX query.  The is an overview rather than a tutorial.  My focus is on queries, parameters and drill-through commands and not so much the dashboard layout.  In the next article, I’ll show you how to go one step further and drill-through to an operational report created with Reporting Services.

A Quick Tour

To set the stage, my dataset is the airline on-time performance database from the Bureau of Transportation Statistics…

http://sqlmag.com/analysis/datazen-mobile-dashboards-analysis-services-and-drill-through

Datazen Visual Control Categories

Choosing the right visualization control for a set of data can sometimes be challenging.  I find some aspects of designing dashboard solutions in Datazen on the surface to be very simple but it’s easy to get stuck in the details and fine points.  The purpose of this guide to help simplify and categorize visual control choices in mobile dashboard design.  Datazen supports five categories of visual controls:

  • Navigators
  • Gauges
  • Charts
  • Maps
  • DataGrids

Navigator Controls

These controls are used to filter the data displayed in other controls

image

  • Time Navigator displays a range of time/date values

Supports years, quarters, months, days, hours
Auto-generates each date/time level value (doesn’t require a date lookup table)
Supports one or multiple metric fields

  • Scorecard Grid combines a selection list with multi-field value KPI scorecard
  • Selection List supports single-select, multi-select and All

Gauge Visuals

image

This group of controls display a single numeric field value rather than multiple values

  • The Number control displays a single value with no target
  • All other controls display a value compared to a target value, which requires that the dataset has two separate metric fields for this purpose.

Chart Visuals

image

  • Time Chart & Comparison Time Chart:
  • Similar to Time Navigator but also supports auto, decade levels
  • These controls segment aggregated metrics by a category field:

Category Chart, Comparison Category Chart, Waterfall Chart, Funnel Chart, Tree Map

These controls use multiple metric fields (doesn’t segment)

Totals Chart, Pie Chart

Map Visuals

image

  • Geographic region names map to embedded shape names in the map definition
  • Shapes use well-known region names but not well documented.  All the shape names are exposed in a JSON structure within the dashboard definition file (to view the definition, rename a copy of the .datazen file with a .zip extension.)
  • Single metric field values:

Map, Bubble Map

Metric & comparison (KPI target) values:

Range Stop Heat Map

DataGrid Visuals

image

  • Simple DataGrid:

Based on one table, displays selected fields

  • Indicator DataGrid:

Based on one table, displays selected fields as either columns or indicators/gauges.

Indicators require 2 fields for metric & target/comparison

  • Chart DataGrid:

Supports features of Indicator DataGrid

Based on two tables with matching key fields

Second table is used for the category chart