Note, It funny how things work out. While I’ve not tested it out yet, a patch set update (PSU) 20859535 appeared today after I created this post.
|Defect Number||Defect Fixed|
MDX formulas are not calculating correctly for parent members of the accounts dimension, which are tagged with time balance properties and compression, in an ASO cube where the parent has more than one child.
This is the bug I reported last month.So it appears it might be fixed. I just have to now test it.
I am a creature of habit. I have done the same calculation to put YTD net income into Retained earnings in too many cubes to count. In My ASO cubes, I know that I have to set the solve order higher than normal for the ancestors of my calculated retained earning member to get it to roll up properly. and it has always worked.That is until now. I’m working on 126.96.36.199.502 and have run into an interesting issue.
My retained earnings calculation works if I am at individual periods, but does not work if I’m at total Periods. In addition, it works if I am at the single stored member of my View dimension but not if I expand the View dimension. The stored member value actually changes. In tracing through the issue, It appears the formula for retained earnings is not firing when I’m at Total Year or when I have multiple member of my dynamic View dimension.
. I was able to find a work around. Instead of allowing the parent of my retained earnings calculation be a natural rollup, I forced it to be a calculated formulaic member that adds up its children. That apparently is enough to force the calculation to occur and it properly rolls up to all of the ancestors.
I don’t particularly like this solution as it means that If the users add a new account then the formula has to be changed as opposed to the hierarchy rolling up correctly.
This is also part of a bigger issue. During my testing of formulas in a “View” dimension, I had issues where the formula would not work at a parent account level, but would at the child level. Oracle has confirmed this bug and I was able to get around it by setting the Accounts dimension as a default higher solver order.
Again while this works, IT is different from every other earlier version. My advice is if you upgrade check your calculations very carefully across all of your dynamically calculated dimensions. Don’t assume things will work hunky Dory.