Use MDX in PPS KPIs before it is too late

We don’t like MDX… well I don’t. It is complicated, long and there are many ways to go around it to bother to learn it. But every now and then MDX is useful and with PerformancePoint Server I’ve found where our old friend can help:

Maybe it happened to you that you created a scorecard with KPIs defined in PPS in the UAT environment and later you tried to move/copy that scorecard to the Production environment. Once you have succeed in moving/copying the PPS items to Production and load them into a new Workspace you may find, with dismay, that none of the items actually worked. KPIs maintain their configuration, but for some reason they refuse to work (and yes I tried to open them, edit something, save them back to refresh PPS metadata, etc. Nothing seemed to work). Then, the only solution that you have is to recreate the KPIs manually… which is quite frustrating boring, etc… But! It just happens that this is not the case if you use MDX to declare your KPIs… 🙂 So it is time to click that “Use MDX tuple formula” check box and learn a bit of MDX… It is not that difficult… After a while you even will like it as I do 😉

KPI with MDX

Here is an example to display a KPI’s measure called “Aggregated Actual”, for the Attrition KPI (including hierarchy path) and filtered manually by year 2013 and dynamically by the current month:

([Measures].[Aggregated Actual], [Dim KPI].[CS2013 KPIs].[ORYX GTL].[Valued People].[Attrition], [Dim Year].[Year].&[2013], strtomember("[Dim Period].[Period].&["+ format(now(), "MM")  +"]"))
Posted in PPS

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 24 other followers

%d bloggers like this: