Essbase Tuning When There Are Share Members

Using share member is a quite powerful capability in ESSBASE. It allows you to create unlimited alternative roll ups, and it takes zero storage. Whenever the base member changes (data, name, etc), the share member which it points to, changes too. You have freedom to use different alias for share members, but you can not change the member name. 

One other reason developers like to use share members is that it is SAFE to delete.
When the share members are deleted, it has no impact to the base members where the data are stored.

More often than not, dynamic calc will be used for alternative rollups, with the intention that the overall aggregation time will not be increased by the addition of multiple alternative rollups. However, if we are not careful on writing the calc scripts, we could easily miss out the opportunity of improvement on calc time. Following is an example -

In Department hierarchy, there are multiple alternative hierarchies, all are with dynamic calc

In Calc script CurnTr10, the first FIX statement is like this

Here, member function @RELATIVE("Department",0) brings all level0 member from Department.
But what will be in the list?

Let's use SmartView and drill to bottom level, which is the same using the above function list.

You will notice that the same department appears multiple times, from main roll up and alternative roll ups.

Now we see that in FIX statement, same department appears multiple times which are returned by the member function from Department.

After I changed member function to only focus on the main roll up.

 
I ran both, and noticed that the calc time was improved by ~30%.