Most of the superhero aliases out there do not really have much of a deeper meaning than what they appear. Their aliases all sound cool and represent iconic characters, but all of these aliases are intended to simply mask their real names and identities. Just a couple of examples…
- Bruce Wayne’s alias, Batman, to the fans just represents some rich guy dressed up as a bat.
- Clark Kent’s alias, Superman, who ironically is not even a man, is an all-powerful flying alien that shoots laser beams out of his eyes.
- Peter Parker’s alias, Spiderman, is a radioactive-spider bitten teenager who, you guessed it, runs around like a spider shooting webs from his wrists.
If you changed any of these heroes’ aliases, their powers would all remain the same and their story would continue without skipping a beat. But what if, instead of these heroes’ powers remaining static, changing their alias could directly affected their powers?
Fortunately, in the PBCS/Essbase world, we do not need to speculate because aliases do have these power altering abilities. Just like in the superhero world of Gotham, you have the ability to mask member names with an alias table, which gives users a clean and straightforward name to the members for reference. Unlike superheroes, who typically have a single alias, you can add several others if you choose and give the same members multiple aliases…yes hardcore fans, Batman is a poor example as he is also known as the Dark Knight and/or Caped Crusader! By adding new and custom alias tables and combining them with intelligent calculating scripting, we can leverage the powerful ability to transform the way we calculate elements of our applications using these aliases.
When writing calculations that involve conditions or require other members to calculate, developers have the option of using a function to retrieve the appropriate members, or hard-code the member they want to use in the calculation.
In the above calculation, we created static calculations for our KPIs by hard coding the members used in the formulas. For simple calculations like the above, the novice administrator could make minor updates to the script and be confident that they will calculate appropriately in all places. However, as business needs change and calculations become more difficult to manage, these manual updates become unrealistic to maintain and risky to overall performance. This is when we can use the power of aliases to save us.
To make things easier on the administrator and remove the burden of business rule updates, we will create 3 alias tables: ‘CalcAccount1’, ‘CalcAccount2’, and ‘CalcAccount3’. In addition to these three alias tables, we will also create a UDA, called ‘AliasUDA’. The UDA is not required, but can help prevent from any issues due to initial setup errors and mistakes. Next, for our 4 KPI accounts, we will go ahead and update the newly created alias tables and assign the UDA. With the Smart View Planning extension, updating metadata is a breeze for administrators.
You will notice the naming convention for our newly created alias tables goes, MemberName_CalcMemeberName. The reason for including the member name of the account is to provide uniqueness to the aliases, as some of the Calc Accounts are the same for the different members. Additionally, you will notice that Account5_KPI does not have any alias or UDA setup.
Now that the metadata has been setup, we can write our calculations. In the below screenshot you will notice how I switched the period dimension with the account dimensions in the FIX. In other words, I am fixing on the accounts I want to calculate and using the periods as the member to calculate. Luckily, there are only 12 months, so we only need to have 12 lines of formulas that will calculate any of our accounts that have been tagged with our UDA, ‘AliasUDA’. Now, this calculation might look like a scary villain, but once we walk through it, you will realize this calculations power is much like All Might’s one for all.
You can see in the calculation where the new alias tables come into play. We are using a few different functions here to make it dynamic. Specifically, since we FIX on the accounts, we use @CURRMBR, to have ESSBASE dynamically use the current account being calculated in our formula. Next, we use @NAME to convert the member name into a string. After converting the current member name into a string, we use @ALIAS, to bring back the alias value that we setup previously. Once ESSBASE determines the alias value for the current member we are calculating, we use @NAME again to convert the alias value into a string. Once we have the alias as a string, we use the @SUBSTRING function to cut off the accounts member name that we used as a prefix for uniqueness, leaving just the account we want to use in our KPI calculation. Disclaimer: Using the substring function will only work as long as there is a consistent number of characters in all member names – i.e. MAKE SURE YOUR CHART OF ACCOUNTS IS SOLID!!! The @SUBSTRING function is flexible, as it allows you to specify the starting character (0 is the starting position for the first character) and it will go to the end of the string unless you specify an ending character number. Lastly, we need to use @MEMBER to convert the string back into a member name for ESSBASE to use in order to return the data values. Since we now have this formula calculating on each period, we are using cross-dimensional operators to point to the accounts in the formula.
Now, our KPI calculations are dynamic and an administrator will never need to go into calculation manger for a newly created Account_KPI (assuming the same formula: KPI = (Account + Account) / Account). They will simply need to create the KPI account, update the alias tables accordingly and tag the UDA. Additionally, if you ever need to change the inputs for an existing KPI, administrators simply need to update the corresponding alias table’s value and the calculation automatically uses the updated alias to calculate based on this new value. Lastly, we have even implemented controls, and can stop (or start) a particular KPI from calculating by removing the UDA (or adding), which will prevent us from FIXing on the account in the first place.
While this example of using an alias to change our calculations is extremely simple, you can really get complex and creative when designing these types of calculations. For an example, introducing driver-based calculations, with Smart Lists, Planners now have additional inputs for ESSBASE to determine values. Those examples will require a completely different blog to explain, but I hope you have gotten a taste of the dynamic and GREAT powers of the ESSBASE aliases (Unlike the static and powerless Manbat, ManSpider, and ManSuper… or whatever alias they have)!
[sch_pullbox] Need some help with building a successful PBCS Application? Connect with our team today. [/sch_pullbox]
