Replace Trump for Biden
- Nov. 13, 2020
Introduction
At the moment of writing, Joe Biden seems to have won the US presidential elections - yet his predecessor Donald Trump is doing everything to keep Joe Biden from becoming the 46th president of the United States. This is the topic of this article. Imagine we have all 46 presidents (in the past there was 1 president who could serve two terms but not consecutively). Oh yes, you are now looking at screenshots of IBM Planning Analytics, rather than good old TM1 Perspectives/Architect.
The information was loaded in Excel first with the use of Power Query. Just point to Data > From Web and import the table from Wikipedia. After a few easy manipulations you will soon get the result in a table (ListObject). Finally, copy the whole thing to a TM1 dimension with a few attributes. The attribute on the age buckets was promoted into a hierarchy, such that we can have an exercise on Planning Analytics alternative hierarchies too !
So I have Joe Biden at spot 46. This dimension is used in a cube and has data stored on its elements. It could be a cube containing the total money spent on getting elected, the total count of all presidents that were murdered, the yearly salary received, the money received by lobbying firms, the number of times the Democrats won or the Republicans, etc. The idea is that the element '46 - Joseph Robinette Biden' would need to be swapped for the element '46 - Donald John Trump' - in case the latter would somehow remain president.
As you will know, renaming elements isn't an easy task in TM1, unfortunately. Data in regular cubes, attributes cubes, security settings, picklists, ... they can all be linked to element names. For consolidated elements this is less of an issue: we could delete the element, recreate it with the correct name, and add the children to the new element. But what if we need to do many replacements ? What if the element is at leaf level ? What if multiple hierarchies are involved within the dimension ? I decided to write a process for this task, and it uses the undocumented SwapAliasWithPrincipalName function: give it a dimension and an alias name and the function will happily swap them. Alias values become element principal names, element principal names become the alias values. For empty alias values nothing changes. So this is ideal ! Just create an alias, fill it in for the element of Joe Biden, swap them and we are done.
As always I strive for generic processes, so I took a broader perspective with 4 'pMode' parameter values:
- pMode = 0: call a subprocess to do the swap. We don't supply old element name and new element name (loosely speaking, Biden and Trump, resp., in our exercise)
- pMode = 1: we supply old element name and new element name (Biden and Trump), and we simply store Trump value in an alias. Biden remains the element principal name.
- pMode = 2: we supply old element name and new element name (Biden and Trump), and we swap Biden for Trump. Biden goes to the alias, Trump becomes the element principal name after executing a subprocess.
- pMode = 3: same as mode 2 but we delete the alias. Therefore, Biden will no longer be part of the dimension.
Let's look at these cases one at a turn. For Mode = 0, it's a simple swap:
becomes:
For Mode = 1, it's even more simple: I only make sure the alias exists and store the new name (Trump) for the element principal name (Biden). This is often done in TM1: add the new name as an alias, and use that value in reports / input sheets / TI processes / rules / ...
becomes:
For Mode = 2, we definitely want to replace Biden for Trump. But let's keep the value of Biden in the alias:
becomes:
For Mode = 3, we want to replace Biden for Trump and also get rid of Biden in the alias (the alias is removed/does not exist after the dimension update):
becomes:
From a Turbo Integrator point of view, 3 processes are used:
- Process 'President_rename element'
- Process 'WG_DIM_rename_an_element'
- Process 'TECH_swap element name and alias'
Process 1 is the one the user will execute. That process calls process 2, and process 2 calls process 3 (unless it's not needed). In the whole chain of processes, we make sure to pass the correct parameters.
Alternative hierarchies
We can also rename elements in alternative hierarchies. We could always do that, by for example creating the new element and moving the child components of the old element to that new element, then delete the old element. However, using the function to swap principal names and alias values seems to be much easier. However, in the case of alternative hierarchies, there is a caveat ! It turns out that all hierarchies' elements can have their element principal names swapped with the alias value, provided that the alias value is not empty. Even if we only want to execute the swap in 1 alternative hierarchy (because there happens to be only 1 element in that hierarchy that needs a renaming/swap), still all hierarchies CAN be affected. So the idea it that the elements that need to be swapped, are the only ones to contain a value in the alias that assists in the swap. That way, we are sure that other elements are not affected.
For example, let's create an alternative hierarchy for the age buckets at the start of the presidential term:
I am not so much fond of the generated names 'All Age bucket at start', '75 - 80', '70 - 74', ... I prefer to have them renamed (they only exist in this new hierarchy): [obviously, we could/should use a proper TI process and suitable hierarchy functions to set up and maintain hierarchies, but that's an aside here]
becomes:
Even though I indicated in the process execution call that I want the hierarchy 'Age bucket at start' from the dimension 'President', the swap will be done on all hierarchies, including the main hierarchy of the dimension:
becomes:
We clearly see that also elements in the main hierarchy see their principal names swapped ! If we leave these values blank, they will not be affected:
becomes:
To wrap up, we can also treat the case where the elements are not level 0 but consolidated. In that case, different alternative hierarchies can have different alias values ! For example, the consolidation called 'Total' in the main hierarchy of the dimension could have alias "Description" equal to 'All', whereas in the same dimension a different hierarchy could contain an element called 'Total' with different children/weights, and a different alias value for the same alias ("Description") - the alias value could be 'All 2' (sorry, lack of imagination here).
Final remark: as element names and alias values change, be sure to check whether the other attribute values still match. While preparing this article, I was bitten by this too. I swapped Trump for Biden but the age of Biden was still there, instead of the one for Trump. So in general, have a look at all attribute values in that dimension.
Rules-calculated alias values
Alias values that are calculated by using a calculation rule, can be swapped too. The values of the alias will become the element principal names, while the rules will calculate again. It's certainly possible that the alias values, rules-calculated, after the swap do not correspond to the element principal names before the swap. Use it with caution but it also opens up great opportunities, as it is often very quick to add a rule, and swap its values.
Renaming an attribute
Yes, it's possible ! With the same techniques I showed you. Beware, for the dimension 'President', you need to execute the swap in the dimension '}ElementAttributes_President'. We need an alias for that, so we end up with the dimension '}ElementAttributes_}ElementAttributes_President' !
I'm not kidding:
If the alias value is rules-calculated, be sure to either adjust the rule(s), either still leave the old name of the attribute in the '}ElementAttributes_}ElementAttributes_President' dimension. I prefer option 1 !
Finally
All in all, it's not been very easy, but it proved to be a very good learning path towards working with Turbo Integrator when it comes to the interface of Planning Analytics, and working with alternative hierarchies as well as attributes. I can recommend it to all of you ! Don't forget to add a number of sanity checks to warn the user in case of wrong input and abort the process where needed.
