Data-driven Time filters in PerformancePoint M&A

Recently I've been working on methods to display time filters in PerformancePoint M&A that are more user-friendly and efficient for users.  The conventional alternatives--Member Selection, Time Intelligence Post-Back and Time Intelligence Filter--have their strengths, and all of them are user friendly!  However I find that often the underlying data structure doesn't fit with the design of these filter tools.

The fundamental problem I seem to encounter most often is that the data in the average cube isn't "as of" the current system date and/or the [Time] dimension extends well beyond the last date/time used in the data set.  For example, a cube may be processed daily, and the [Last Order Date] is yesterday, but the [Time] dimension extends one year in the future (to support forecasting, perhaps).

So, if the last date of the data is Friday, and today is Monday, how can we make our [Time] dimension filter selection default to Friday?  For Time Intelligence, Day-1 in this scenario is Sunday...that's not what our uses expect!  We could just select the last day in the [Time] dimension using an MDX filter...except that often the [Time] dimension extends out in the future, and our users don't want to default to a future date!

What we really need is a way to fill our [Time] filter with valid members, and pre-select the most logical one.  This way our users will always see valid, sensible data upon loading the dashboard.  Unfortunately, as far as I've been able to determine, none of the built-in Filters will support the scenario outlined above without some intermediate/advanced MDX work. 

What we really need is a dynamic date selector that doesn't use the computer system clock, but rather uses knowledge of the data in our data source.  At our firm we've addressed this for clients in some cases by building in calculated members to our Time hierarchies, or with named sets.  This works OK, but it still hasn't completely solved the problem because it's still tough to control what the default selection is going to be.  So, in this article I'll explore another way I went about this using fairly straightforward MDX. 

Some people aren't that comfortable with MDX.  If you're one of them, don't worry too much, because the template outlined below is pretty simple and you can probably use it without fully undertanding what's happening under the hood.

To use this method, the following should be true:

• The solution is based on a single Analysis Services data source (a cube).  If you have multiple data sources, you need to use the Time Intelligence filters.
• The default measure in the cube is NULL for dates after the filter's desired "as of date"

The basic technique here is to use the MDX filter type in PerformancePoint, and tune the MDX to return exactly the members we want in the list.  Unfortunately there's no way to specify to the PerformancePoint MDX Filter which member should be the default (I don't know why, since the Member Selection filter type allows this..feature request anyone?).  So, we just have to make sure the first thing that's returned by the MDX query is what we want as a default. 

Let's implement two scenarios:

1. A list of months, in descending order, with the last month for which there is data available first
2. The current month, current quarter, and current year--again with "current" defined by the last [Time] periods that have data in the cube 

First, take a look at MDX statements that will return these member sets.  This is just plain MDX you can experiment with in any MDX front-end application, such as SQL Management Studio.

This first query returns a list of all Months in the [Time] hierarchy, and reverses the order so the last month is first. For example: {Dec-08, Nov-08, ..., Jan-08}

SELECT
NONEMPTY (Order([Time].[Time Hierarchy].[Month].AllMembers,
 RANK([Time].[Time Hierarchy].CurrentMember,
   [Time].[Time Hierarchy].[Month].AllMembers),
   BDESC)) ON COLUMNS
FROM [Sales Cube]

Using this query, we'll generate a filter that looks something like this:

This second query returns members from different levels--the last Month, Quarter and Year for which we have data.  For example: {May 08, Q2-08, 2008}

SELECT
{
 TAIL(NONEMPTY ([Time].[Time Hierarchy].[Month].AllMembers)).Item(0),
 TAIL(NONEMPTY ([Time].[Time Hierarchy].[Quarter].AllMembers)).Item(0),
 TAIL(NONEMPTY ([Time].[Time Hierarchy].[Year].AllMembers)).Item(0),
} ON COLUMNS
FROM [Sales Cube]

We'll use this query to generate a filter that looks something like this:

So how do these queries know where the fact data ends? Well, I've cheated actually. These queries work as presented because they will automatically intersect with whatever the default measure is within the cube, and any NULL tuples will be filtered out by the NONEMPTY keyword.  If your default measure doesn't align with what you consider the "data as of" date, you'll have a bit more MDX work to do...you need to add a specific measure to the MDX in that case (see the MDX documentation for a syntax diagram).

You'll need to tweak these queries to work with your own data, but your MDX should be pretty close to these examples.

With the queries tested, simply paste them into a standard PerformancePoint M&A MDX filter definition.  To do so, follow these steps:

1. Create a new query, selecting the MDX Query filter template 
   
   
2. In the Enter MDX Formula edit box, paste the contents of one of the above queries, leaving out the SELECT, ON COLUMNS and FROM clauses.
   
   
3. Save the filter
4. Add the filter to a dashboard, and use it as any other filter

Well, that's it!  The second example is especially flexible, as you can use just about any MDX you want to use to add new items to the list.  Just be certain that any member returned in the query is from the [Time].[Time Hierarchy]...if it's not, your filter targets probably won't know what to do with them and the solution won't work!

While this tutorial deals with a [Time] hierarchy, you can probably see that the same idea can be applied in other ways.  MDX has many powerful functions and methods you can leverage to make filter sets more intelligent--which is a great way to make our users' experience with BI solutions simpler and more intuitive.