I am a big believer in not trying to over-engineer a solution and I strive to find the balance between creating a good user experience and maintaining a simple solution.
Selection Widgets – The Issue
One issue I kept coming up against in Planning Analytics Workspace was that my cubes were designed to hold data and perform calculations in the optimal way; this would mean that I almost always had users needing to work with data in multiple cubes. Cubes with different but LINKED dimensionsality e.g. a Store Dimension in 1 cube and a Country Dimension in another. The user wants to enter data in both cubes for the same country, but at different granularity. The way that dimension Synchronization currently works; does not allow for me to synchronize those 2 dimensions to 1 selection widget.
And its not just about different dimensions; I could have a version dimension – my user selects that they want to plan data in the Q1-2020 plan. On 1 screen I just want them to see Q1-2020 but on the next screen I want them to see Q1-2020’s plan with the Budget plan next to it for comparison. Again the way that synchronization currently works – does not allow me to do this.
The Solution
I could choose to make my users select what is essentially the same thing multiple times for each input cube OR I could go back to the way that I used to set up applications in TM1 Web and Perspectives. And that is exactly what I did, and it works incredibly well in Planning Analytics Workspace.
Instead of using Selection widgets; I have users make their selections in a cube. Picklists present them with the options that they are allowed to select. Next I reference their selections throughout various MDX based subsets; so my whole book is synchronized with an immediate refresh when they make their selection. The only real differences to using Selection widgets is that the input looks different and I have far more flexibility in what I do with them.
MDX Examples from the Video
}Clients Dimension – Return UserName
A standard MDX statement that every developer should have in their arsenal. This will filter to the client element for the person executing the query.
Country Dimension – Return Selected Country Element (or GB if blank) – StrToMember
This MDX statement takes the cell value for “Location” held against the name of the executing user. And it converts it into an element in the Country dimension, using StrToMember.
The added IIF statement makes sure that the statement will always return a valid result.
Store Dimension – Return Selected Country’s Store Elements (or GB’s Stores if blank) – StrToSet
This MDX statement takes the cell value for “Location” held against the name of the executing user. And it converts it into a set of the Country element’s children (stores) in the Store dimension, using StrToSet and .Children.
The added IIF statement makes sure that the statement will always return a valid result.
Whether or not you intend to use MDX for this sort of requirement; it is always worth a developer brushing up on their MDX skills. Between MDX subsets and MDX views, this often overlooked BUILT-IN tool can add so much to any application.
The example here where I have replaced Selection Widgets, it was a small amount of extra work for the developer (me) at the start of the process but will be saving the users time, effort and energy every single time they use the application.
Find other Videos and Articles from the Quick Tips series here.
