You might be already familiar with this term Hierarchy-driven Smart Lists (Or Smart List from members). I talked about it in my groovy series and even talked about how it can be replicated on an on-premises Hyperion Planning application here.
When I was doing the Groovy series, many people told me that “It is a great feature”. I’m already in love with this one. The main question I heard was “Well it is great, however now how’ll we use this in Calc scripts?”
The people who asked that question, do have a point. Well, you already know what I’m going to say, if not, please bear with me for a while.
When I create a Smart List and assign it to a member I can use it in the calc in two different ways.
Let’s look at an example.
I’m going to create a Smart List for months
- Use the ID of the Smart List and perform some operation (If(StartMonth == 1)
- Use Smart List.Entry notation (Sl_Month.Jan)
Now the trouble with Hierarchy-driven Smart List is that the ID is auto-generated and it changes after you perform an Essbase artifact import. I still have to go back and check this, but I’m pretty sure it does as this is a database generated number.
So I cannot use IDs, and I don’t want to write a lot of IF conditions? What if you are generating a Smart List from a hierarchy that changes all the time, how can I reference those as members in a calc script?
I’m going to spill the beans about a syntax that you can use.
The example that I’m going to use is a typical Workforce scenario. I got some new hires and I need to spread their annual salary based on their start month.
StartMonth is a Smart List created from members.
- New Hire 1 is going to start in April
- New Hire 2 is going to start in July
That’s it, just 1 line of code!!!!
HSP_ID_ is the key word. All you’ve to do is grab the ID (the one which Essbase stores) and concatenate with HSP_ID_ and use that in the script.
Now “How did I come to know about this?”,
I’m blessed to be a part of a smaller group which exchanges some ideas (read crazy) and this particular one involves (happened in Nov last year)
- My older brother from completely different set of parents, Cameron Lackpour
- The guy from down under, Pete Nitschke
I don’t recollect what our topic was (we just move from one to another 😉 ) and Pete came up with this code and for the life of me, I couldn’t make it work. (I was testing this on an On-Premises version, then on PBCS with a regular Smart List)
I completely forgot about this until yesterday. (I had to create the above-given Workforce calc and I didn’t want to do it the regular way ;)) Also I forgot the part that I was trying with a normal Smart List earlier.
Now you are curious about how this one work, aren’t you? 🙂
Let’s look at the plumbing work. Pete (and me too) had a strong feel that it could be a hidden alias table. How can it be proved now.
I did talk about moving PBCS to an On-premises Essbase application here. I created an application and database and looked at the outline properties.
What do I see there 🙂
That’s our hidden alias table, SLAliases.
Here are the entries from the Smart list.
Set SLAliases as the active alias table and what do we see!!!
Surprisingly it adds an alias for the members that are not involved in the SL creation.
Well, there you go a new way to call Hierarchy-driven smart list members in calc scripts. Something which can be replicated in on-premises along with Hierarchy-driven Smart Lists.
That’s neat. Now this feature is alot more powerful and useful!!
Wouldn’t it be easier if Oracle had given this piece of missing info when they released this feature last year? Sometimes I have a feeling they are putting alot of effort to get new exciting features and functions up and running, but when it comes to docuemntation it is just bad. Until recently (I think they removed it) the working with on-premise functions section of CalcMgr docuementation was poorly written.
Thanks a lot !!!! A thanksfully glow in cloudy PBCS. It was impossible to achieve something reliable with Design time prompts in order to get hierarchies information from smartlists.
Hello Celvin, Thanks for this very useful information. I would like to ask you if you know how to create this hidden SLAliases in PBCS, and what to do to keep it updated when we change the dimension.
Thanks for this very useful information. I need your help in calcscript for workforce. So basically I am trying to change the status of an employee based on hire date and end date. So i am facing issue in creating the script under various senarios.
I would really appriciate if you could help me with my case.
@email : email@example.com
what have you done so far?
I used this concept in PBCS and it has been very successful in creating calc scripts that do not need to maintained.
However an adverse side effect has been the Maintenance Window Snapshot now takes over 12 hours to complete, previously it was 20 mins.
I reversed out the changed an it returned 20 mins.
Does anyone understand and explain why this?
I think I wrote about this. You’ll have to change the hierarchy driven smartlists to a normal one before the maintenance window and change them back to how it was after the maintenance window. Let’s say you have one Smartlist driven by entity dimension, update of entity dimension will be a lot longer.
Does this work if it is not Hierarchy Driven Smarlist. We have Smarlist Loaded from FDMEE and I don’t see the SLAliases in the properties tab.
SLAliases is only created when it is a hierarchy driven smart list.