​Datazen Mobile Dashboards with Analysis Services and Drill-through

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.  If you’ve flown within the US in the past five years, your flight is in this database along with about 35 million other flight records for all US origins and destinations.  This isn’t a sample database you can easily download and use as a tutorial.  I like to use it as an example of real, production-quality data that everyone understands.  You should be able to adapt these examples using another data set.

I begin with a data source for my SSAS database called AirDelaysTabular which references a tabular SSAS instance on my SQL Server 2014 development server.  On my iPad, the first dashboard lets me see flight delay statistics for any origin and destination airports I select.  This year there were 8,983 flights from JFK to LAX with an average departure delay of nine minutes.  According to the dashboard, Delta had the longest average departure delay for this route and JetBlue had the shortest, with improvement over the previous year.

The number gauge titled Avg Departure Delay has a drill-through icon in the upper-right corner which indicates that tapping this will navigate to another dashboard to show more detail.  When I use this to navigate to the second dashboard, I see specific delay categories like the time it took to taxi out, and weather and security delays.  I can use the time navigator to narrow the scope of my data.  The dashboard response is immediate and intuitive.esigning the SolutionTwo data view queries will feed data to my first dashboard.  These are created in the Datazen Dashboard that were introduced in my earlier article.  I’m using a separate query to list all medium and large hub airports in the correct order.  The other data view query supplies data to the main body of the dashboard and will be filtered by the two airport selection lists.

I write my queries in SQL Server Management Studio and save them in a script file.  Each query begins with a commented name which is reasonably short but descriptive.  In the Datazen Control Panel, for each new view, I paste the query with the commented name into the Data View Query box and then copy and paste the name into the Data View Name box on the Data View designer page.

-- Med & Large Hub Airports

with member measures.AirportCode as [Origin].[Airport].CurrentMember.Name

select

       {measures.AirportCode} on columns,

       (

             [Origin].[Airport].[Airport].Members,

             [Origin].[CityName].[CityName].Members,

             [Origin].[State].[State].Members

       )

       on rows

from [Model]

where

       {[Origin].[Hub Size].&[Medium], [Origin].[Hub Size].&[Large]}

 

-- Perf Origin Dest 2015

select

       {

             [Measures].[Avg Dep Delay],

             [Measures].[Avg Dep Delay Previous Year],

             [Measures].[Late Aircraft Delay],

             [Measures].[NAS Delay],

             [Measures].[Security Delay],

             [Measures].[Taxi Out Time],

             [Measures].[Weather Delay],

             [Measures].[Flight Count]

       } on columns,

       non empty

       (

             [Origin].[Airport].[Airport].Members,

             [Destination].[Airport].[Airport].Members,

             [Carrier].[CarrierName].[CarrierName].Members

       ) on rows

from [Model]

where

       [Date].[Year].&[2015];

 

In the Datazen Publisher app on my Windows 10 machine, I create a new dashboard and import both of these queries.  Since neither query includes parameters, I’ll rely on Datazen’s caching feature to refresh the data at regular intervals.

By the way, the publisher app, which is used to design dashboards, is a resizable window in Windows 10.  In Windows 8 and 8.1, it always runs full screen.  The Windows 7 compatible designer has been in preview for a while and should be released very soon.