IBM Planning Analytics Tips: Make Default Cube Views Dynamic with MDX
IBM Planning Analytics Tips: Make Default Cube Views Dynamic with MDX

This is a guest post by Revelwood’s Jay Apwah. 

Did you know you can easily update a cube value to control the period elements that will display in a subset used in views? In this blog post, I detail a scenario why you may need to do this and then how to do it.

The Challenge

It’s the end of a quarter and the VP of Sales wants to look at the projected financials for her department. You’ve already built a view that she can open to see the financials, but you just remembered that you forgot to change the period subset to display this quarter’s results. You’ve also just received an email notification from an annoyed VP of Sales. Despite the quarterly reminders that you’ve set for yourself, you forgot to update the default views in various cubes to reflect the current quarter’s plan.

This blog will show you a solution that will allow you to simply update a cube value to control the period elements that will display in a subset used in views.

Example of Solution

Before:

GlobalControls cube value “Current Quarter” is set to “Q4 2017”:

Making default cube views dynamic with MDX for IBM Planning Analytics and TM1

Default Finance cube view displays Q4 2017 and the Q4 2017 months:

Learn how to make default cube views dynamic with MDX for IBM Planning Analytics and TM1

After:

GlobalControls cube value “Current Quarter” is changed to “Q1 2018”:

use mdx to make default cube views dynamic

Default Finance cube view displays Q1 2018 and the Q1 2018 months:

mdx can enable you to make default cube views dynamic

How to Implement

Step 1: Create or update a system controls cube

-If you already have a system controls cube, add a new element that will be used to store the current time value. In this example it the new element is called “Current Quarter”.

-If you don’t already have a system controls cube, create a two dimensional cube where both dimensions contain string elements so that you can enter text to define which periods to display in the subset. The control cube should look something like this:

With mdx you can make default cube views dynamic

Step 2: Create a dynamic subset in your time dimension

The MDX expression is key here.

Mdx lets you change the default cube view in IBM Planning Analytics to a dynamic view

MDX Expression:

{DESCENDANTS({FILTER({TM1SUBSETALL([Period])}, [Period].CurrentMember.Name = [GlobalControls].([GlobalControl_Items].[Current Quarter],[GlobalControl_Msr].[Value]))})}

Before moving on, let’s get familiar with the cube, dimension and element names to make it easier to understand this expression:

A) GlobalControls: Name of the control cube that stores the current quarter value

B) GlobalControl_Items: Name of the dimension that contains the “Current Quarter” element

C) GlobalControl_Msr: Name of the dimension that contains the “Value” element

D) Period: Name of the time dimension

E) Current Quarter: An element in the GlobalControl_Items dimension

F) Value: An element in the GlobalControl_Msr dimension

After placing letters A – F described above into the MDX expression, it would look like this:

{DESCENDANTS({FILTER({TM1SUBSETALL([D])}, [D].CurrentMember.Name = [A].([B].[E],[C].[F]))})}

Step 3: Save the dynamic subset

Save the expression in your time dimension and give a name to the public   Then use that public subset in every default view. In this example, the name of the subset is “CurrentQuarterPeriods”.

Change default cube views in IBM Planning Analytics to dynamic views with MDX

Step 4: Relax

Never worry again about having to update every default cube view as time passes. Simply update the value in your control cube!

This concept can be applied in many different ways. Get creative and give it a try!

IBM Planning Analytics is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

 

Read more blog posts with tips & tricks for IBM Planning Analytics and TM1:

IBM Planning Analytics Tips & Tricks Video Demo: MDX in TM1

IBM Planning Analytics Tips & Tricks: Using MDX to Compare Dimension Hierarchies

IBM Planning Analytics Tips & Tricks: Using the Full Power of MDX


Never miss another post.

Sign Up for Our Newsletter.