PerformancePoint 2010 - connecting Filter to Analytic Chart from different Data Source

I am working on a PerformancePoint 2010 Dashboard which contains filters and Analytic Charts from different Data Sources. I wanted to share one of the challenges and my solution to it, and if possible to discuss any other alternative solutions.

My challenge was to connect an Analytic Chart from one Data Source (OLAP Database A) to a "Period" filter from another (OLAP Database B). I could not use Time Intelligence Filter to solve this, because the calendar control and Time Intelligence functions (Current Month, Previous Month) were not giving us what was needed - we needed just to display a list of Period names in the filter.

To implement this funtionality, Period dimension members had to be translated into Month hierarchy members of a Time dimension when connecting to a Chart by using string manipulation in the MDX query of the Analytic Chart.

Here are the steps to make it work:

1. Drop Period filter and Analytic Chart to the Dashboard

2. In Analytic Chart, create Parameter <<Period>>

3. Create Connection between Filter and Chart with Member Unique Name as a Source Value:





4. Specify <<UniqueName>> in Connection Formula



5. In Analytic Chart MDX Query create a Calculated Member FilterDate

WITH MEMBER FilterDate
AS "[Time].[Date].[Month]." + vba!Right("<<Period>>", vba!Len("<<Period>>") - vba!InStr("<<Period>>",".&["))

this will remove the dimension and hierarchy name sent from Period filter and append the rest to the dimension and hierarchy name of the Time dimension of the cube that is used in the chart

5. in the WHERE condition, put WHERE StrToMember(FilterDate)

This solution will work only for single-selection filter because it replaces the dimension name only once. I only wish REPLACE funciton was available in MDX, then we would be able to implement multiple-selection solution as well.

There is another condition in the cube design to make this work - Dimension members of Periods and Month hierarchies must have same keys, ex: March 2010 has a key "201003", and it must mean the same thing in both dimensions - Period and Time.

My special thanks to Chris Webb for the link to VBA functions in MDX - http://cwebbbi.wordpress.com/2007/03/03/vba-functions-in-mdx

.