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”:

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

After:

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

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

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:

Step 2: Create a dynamic subset in your time dimension

The MDX expression is key here.

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”.

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