SharePoint: MDX Expressions for PerformancePoint Filters

Dashboard filters is one of the key components of BI Platform that drive reports and KPI’s in various dashboards. MDX expressions to a great extent enables to customize PerformancePoint filters and enhance the end user experience.

First – a quick look at member selection option. Member selection is the easiest way to build dashboard filters which does not require MDX expressions (Screen capture 1). Member selection option allows to select specific members, although this is not usually recommended as the filter can become quite static. A better method is to select Children, Leaves or Descendants with additional option to include or exclude default member. For hierarchies, member selection option also allows selection members from a particular level of hierarchy.

Screen Capture 1 – Member Selection

While member selection option may be sufficient for some business scenarios, using MDX Expressions (Screen Capture 2) enables to customize dashboards without hardcoding the member selection.

Screen Capture 2 – MDX Expression Option for PerformancePoint Filters

Some common and often repeated dashboarding scenarios where MDX expressions for PerformancePoint filters are useful is discussed below

Display only members that have data

It’s not particularly intuitive for business users to select an item from drop down only to find that there is no data for the dashboard to display.

The following example uses FILTER function to select only Product Categories that have a sale.

FILTER([Product].[Category].Children,[Measures].[Internet Order Count] > 0)

Screen Capture 3 – MDX Expression for PerformancePoint Filter

Display only last N time periods

Often business is only keen on trend analysis for the last few years. All time periods from beginning of time need not be displayed in dashboard.

The following MDX expression displays the last 4 fiscal years from current fiscal year. When the fiscal year changes so does the filter data. Note that for this MDX expression to work, the date dimension must have current attributes defined.

(EXISTS([Date].[Fiscal Year].Children,[Date].[Current Fiscal Year].&[Y]).Item(0).LAG(-4)
: EXISTS([Date].[Fiscal Year].Children,[Date].[Current Fiscal Year].&[Y]).Item(0))

Display time periods in reverse chronology – latest dates first

Another common requirement for business users is to see the latest dates first.

Using the ORDER function in the previous MDX expression, the latest fiscal years can be displayed as the first item in the drop down.

ORDER((EXISTS([Date].[Fiscal Year].Children,[Date].[Current Fiscal Year].&[Y]).Item(0).LAG(-4)
: EXISTS([Date].[Fiscal Year].Children,[Date].[Current Fiscal Year].&[Y]).Item(0))
, [Date].[Fiscal Year].CurrentMember.Properties('key'),DESC)

Hide unknown members

It a good to handle unknown dimension members in datamarts which in turn helps to address data quality issues. However bad data need not be displayed in dashboard filters.

The following MDX expression gets all the Sales Reason members except the unknown members.

([Sales Reason].[Sales Reason].Children
-[Sales Reason].[Sales Reason].&[UNAVAILABLE]
-[Sales Reason].[Sales Reason].&[NOT APPLICABLE]
-[Sales Reason].[Sales Reason].&[UNKNOWN])

The above examples demonstrate the ease with which seriously rocking PerformancePoint filters can be created using simple MDX expressions that create business friendly dashboards.

Benny Austin


SSAS: Using MDX to Calculate Snapshot Measures

In a data warehouse, Periodic Snapshot table is a type of fact table that “freezes” the measure value at recurring reporting periods. This enables reporting of measures AS AT a point in time. Periodic Snapshots are usually created from Transaction Fact tables that support reporting FOR the period. Using MDX it is possible to create snapshot values for measures from transaction fact without creating Periodic Snapshot tables. Continue Reading

Dimension Modeling: Creating Business Friendly Dashboards with CURRENT attributes in Date Dimension

Date Dimension is the most commonly used dimension in any BI implementation. Often used as role playing dimension, there is practically no BI Implementation without date dimension. Adding current indicator attributes with Y/N values to date dimension like current year, current quarter, current month, current week and today enhances dashboarding capabilities and provide better navigation for business users. Continue Reading

SSAS: MDX Calculated Measures that Require Date Comparison

Often there is requirement to calculate measures based on two different date dimensions. For e.g. how many orders that were delivered this month was actually ordered last month? How many orders were delivered in the same month as they were ordered? How many orders were carried forward to next month?

Using MDX set functions EXISTS(),FILTER() and Range operator these calculations can be easily achieved provided the Date Dimension conform to same key columns. Continue Reading

SSAS: One Database, many Cubes Vs. One Database per Cube

So you have several cubes to build and perhaps wondering what is the best way to build and deploy your cubes. Should you build all the cubes within the same Analysis Service database or create one database per cube? The following guidelines might help you to choose the best approach to build and deploy your SSAS cubes. Continue Reading

SSAS: Process Incremental – What it is & isn’t

SSAS provides Process Incremental as one of the processing options for cubes, measure groups and partitions. It is important to understand how Process Incremental works because it differs significantly from the seemingly equivalent Process Update for dimensions. Continue Reading

SSAS: Troubleshooting this exception – “An unexpected error occurred (file ‘mdinfo.cpp’, line nnnn, function ‘MDInfo::Init’)”

What’s this exception?

[Analysis Services Execute DDL Task] Error: Internal error: An unexpected error occurred (file ‘mdinfo.cpp’, line nnnn, function ‘MDInfo::Init’).

My guess is that it’s most likely an unhandled exception from the product. I recently encountered this exception while processing index of a particular SSAS Cube using SSIS Analysis Services Processing Task. The cube did get processed in a previous step and only the process index step failed, the cube was still available for browsing minus the indexes.

How to work around this error?

Continue Reading