This content has been marked as final. Show 9 replies
Assuming that you have more than two sparse dimensions, you are calculating this for all members in all sparse dimensions other than Year. Do you really want to do that?
Why don't you FIX on level zero of all sparse members (and level zero of Account as well assuming you have made it dynamic for upper level rollups) and then do the calc? When finished, roll up the database. You will find Essbase generally much better at agging than assigning a value at each and every block.
Some of your code comments, btw, don't make sense (or at least suggest that someone thought of the above before) and maybe you are doing some or all of the above.
On my current script all are sparse dimension members, I have my fiscal period and account dimension members are dense dimension members. I am using this script first time. My formula include all sparse dim members. I think calc script is not creation blocks? Is there anything I need to add on script of just add @relative (accounts,0) on fix statement with &CurYr.
I have following Dimensions:
Account --- Dense
FY Periods ---- Dense
Cost Center---- Sparse
All members in formula is belongs to Scenario Dimension Members....
We have level 0 data....
Do I need to add any Command, I guess block is not creating because of sparse dimension members?
Edited by: dhooan on Feb 11, 2013 12:57 PM
You need to FIX on all of the level zero members in Entity, Cost Center, and Channel. Something like this:
FIX(@LEVMBRS("Entity", 0), @LEVMBRS("Cost Center", 0), @LEVMBRS("Channel", 0), &CurYr)
"Feb Est"="FAB" + "TSA" + "Standalone" + "Day1" + "AcqInt";
That will make everything happen at the lowest level then you can FIX on Feb Est and &CurYr and AGG Entity, Cost Center, and Channel. At least that would be what I would do. As for block creation, that is only an issue if you can't get any values into Feb Est. You stated (and the title of this thread) is that you have slow performance and I think that's because you are not constraining the scope of the member formula.
There is no point of using CLEABLOCK command with a fix on dense member as it will ignore clearing the blocks and just clear the data, using CLEARDATA might improve some time.
I am also not sure about why you can't use the scenario member on the left side of your equation and use block creation set command; and move month to fix. This will address the block creation issues as well.
If block creation command taking extra time, it is faster to copy the data to all possible intersections and then run your formula.
I see, I was having an impression that Feb Est. is a period and thus dense member. Anyways as CL suggested it usually is faster to calc at lvl0 and agg to parent levels. I am just trying to put something together to begin with..
CLEARDATA "Feb Est";
SET CREATEBLOCKONEQ ON; /* If this is taking more time; you can use copydata from some other scenario, but make sure that you are copying data to all valid intersections that the equation would create data values */
SET CREATEBLOCKONEQ OFF;
AGG(Entity,"Cost Center","Channel") /* Assuming no member formula on these 3 sparse dimension members */