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 series of drill-through dashboards using MDX queries and navigation to a detail report created with Reporting Services. 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.
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.
Designing the Solution
Two 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.
Filtering the Dashboard
The two selection list navigators get their data from the Airports Med and Large Hub data view. In the Data View page for the Origin Airport selection list control, I indicate that the Perf Origin Dest 2015 data view should be filtered using this selection list. For the Destination Airport list, I’ll filter the same data view on the destination airport column.
The remaining controls are bound to various columns from the Perf Origin Dest 2015 data view. Since the data is filtered on the mobile device using a cached result, interaction with the dashboard controls should be very responsive. I use the Dashboard Settings page to assign the title and file name: Origin and Destination Performance, right-click to publish to the server and then right-click again to save a local copy. I’m pretty paranoid when it comes to saving things and losing work so I always keep two copies. Right-click once again and return to the Home page.
Drill-through Dashboard and Detail Report
The data view for the next dashboard, Performance Origin and Destination Detail, will filter results base on two parameters. There are about six million domestic flights every year and a query that returns every possible combination of origin and destination airports would be extremely slow. Passing these values as parameters to filter results on the database server is far more efficient.
I start with a query that has hard-coded origin and destination members for flights from Boston to Seattle:
-- Perf by Date 2015 Param Origin Dest
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
(
[Date].[Date].[Date].Members,
[Carrier].[CarrierName].[CarrierName].Members,
[Origin].[Airport].&[BOS],
[Destination].[Airport].&[SEA]
) on rows
from [Model]
where
(
[Date].[Year].&[2015]
);
To add parameters to the data view, the Refresh Frequency is set to Real-time. This enables the Define Parameters link. On this page, I add two parameters named @DestinationCode and @OriginCode and assign them default values for testing.
Now to modify the query. I use the parameters in-place of the literal values. There are two components to the expressions you see here in the query. The double set brace notation is how Datazen recognizes a parameter. The expression concatenates the parameter value into the full member reference (e.g. ‘[Origin].[Airport].&[BOS]’). If you’re familiar with MDX you may know that the STRTOMEMBER function is a standard MDX convention that resolves a string expression to a member object. If this is new, just know that this technique is also commonly used with MDX queries in many other reporting tools like Reporting Services. In simple terms, this technique allows the query to run correctly with the parameter values.
-- Perf by Date 2015 Param Origin Dest
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
(
[Date].[Date].[Date].Members,
[Carrier].[CarrierName].[CarrierName].Members,
STRTOMEMBER( "[Origin].[Airport].&[" + '{{ @OriginCode }}' + "]" ),
STRTOMEMBER( "[Destination].[Airport].&[" + '{{ @DestinationCode }}' + "]" )
) on rows
from [Model]
where
(
[Date].[Year].&[2015]
);
Designing the Performance Origin and Destination Detail dashboard with this parameterized data view is no different than any other and this is the only data view used by the dashboard.
Adding Drill-through Navigation
Returning to the Origin and Destination Performance dashboard in edit view, the drill-through navigation could be set on any control. I’ve selected the Avg Departure Delay number gauge and then on the Layout View page, I click the Drill-through Target button. After selecting the Performance Origin and Destination Detail dashboard, the @DestinationCode parameter is set to the SelectedItem property of the Destination Airport selection list control and the @OriginCode parameter is set to the SelectedItem property of the Origin Airport selection list control. This passes the previously selected airport codes when this control is tapped.
In conclusion, here are few of things to keep in mind:
Recent product improvements enable Datazen to work natively with MDX queries. When aggregating measure values in a control, Datazen treats empty values like zeros which is fine when summing values but may produce inaccurate averages. This behavior is likely to change but in the meanwhile, you can make contingencies in query and dashboard design.
When you define a drill-through target, always choose the dashboard published to the server rather than a local copy. This has caught me a couple of times.
Datazen is young and a new member of the Microsoft product family. It’s simple by design and doesn’t have the same flexibility as some more mature tools. Use it if it’s a good fit but realize that it’s not a one-size-fits-all tool. Expect to see changes as the entire Microsoft BI platform matures.
