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.