This content has been marked as final. Show 12 replies
I agree with Glenn, I prefer the second script.
You might want to consider creating a Substitution Variable such as CurMonth.
Creating a variable allows you to set it in one place without having to maintain the calc script, with the bonus that you can reuse the same variable in other scripts without having to maintain those scripts either.
Thank You GlennS_3 & tyoung358
i dont know why that didnt work either i have tried these approches i did create 3 Substitution Variable for month year and scenario and the script just runs for like 20 hrs and cleans nothing.
on top of the head how long do you think it should take to clear the data of one month with calcparallel set to 3.
If the previous suggestions won't work, try another approach (using Glenn's excellent suggestion about turning off intelligent calc):
SET UPDATECALC OFF;
"Month" = #Missing;
Or something along these lines:
SET UPDATECALC OFF;
"Year" = #Missing;
Glenn can point out any logical errors I may have made. Also as he mentioned, it is impossible to tell how long it will take to run a given Essbase calc.
I'm inclined to agree with you Glenn. What gave me pause was that Month is dense, which (1) means everyblock would have to be tested by the Fix statement and (2) it may explain why the calc won't run properly.
My thought process behind suggesting the second approach goes back to the old "Use IF statements on dense members" theory - that the calc may run faster by ignoring blocks where the IF member doesn't have data.
Is that theory no longer valid? I know that Essbase theory has changed over the years.
I'll see you in San Antonio next month.
While I have no hard and fast evidence, If you fix on a combination of sparse and dense dimensions, it seems the sparse dimensions limit the number of blocks that are selected and the dense limits further to only the cells in the block that meet the dense criteria. As I said in an earlier post, using IF can slow down calculations as it has to evaluate every time and will go through all the cells in the block rejecting a lot of them. . Having it in the fix removes that evaluation. On a big database I tested on (A long while ago) putting the dense dimension in the fix saved about 20% calculation time over using an IF statement.