Covers Oracle Essbase and tools, including Essbase Administration Services, Essbase Integration Services, ASO, BSO, Oracle Visual Explorer, Smart View for Office, and respective administration/development topics.
Problems with member formula and consolidation
Hi, I've got a cube which holds information on a number of funds. The information it hold is at both fund level (ie. the different fund prices, fund status) or at transaction level (ie. premiums and claims transactions). The outline looks a little like this. Account - Premiums + - Claims + - Holding P/L + (UDA: HoldingP/L) Transaction Type - Units ~ - Value ~ - Profit/Loss ~ - Price ~ - Status ~ - Calc Profit/Loss ~ (see formula below) Fund - AAA + - AAB + - AAC + - etc Time - Jan_10 Dynamic Calc -- 05-Jan + -- 06-Jan + Now I've got a kinda complex formula sitting in the Calc Profit/Loss member as follows IF (@ISLEV (Account, 0)) IF (@ISUDA (Account, "HoldingP/L")) IF ("Status"==0) "Profit/Loss"; ELSE Do a more fancy calc based on different prices and units of other account members; ENDIF ELSE some other calc ENDIF ELSE @SUM (@CHILDREN (@CURRMBR (Account))); ENDIF; Now this calc seems to be working for any individual day and gives me the answer I want. However, I'm having a problem when trying to retrieve at the Jan_10 member level. Whilst my other transaction types seem to consolidate fine, the Calc Profit/Loss is coming out vastly over stated. I've identified what I think the issue is but I'm unsure how I could solve it. Basically because I have my days set to consolidate, that means that for Jan_10, it add ups all the individual day's prices together. This this skews the calculation as rather than just adding up the individual days' Calc Profit/Loss together, it is trying to calculate based on the Jan_10 totals. Does this make sense? If not I could whip up a quick table of data showing the problem.
Hi! Have you tried changing the "Price" consolidation to "Never" (^) ? Could this help? kind regards, user637777
Yeah I've already got the 'Price' to set so as not to consolidate (~) within the Transaction Type dimension. I think my problem is that I'm asking the Time dimension to consolidate and therefore it adds everything up. Effectively the month as a whole shouldn't have a price and any calculations that have been done on a daily basis should be added together to make the month, rather than Essbase trying to perform the calculation on the month member. Perhaps I need a calc script rather than a member formula as its a bit complicated.
Yes, it may be one of those times where you need to store the formula, calculate at the lowest level, and let it aggregate from there. Sabrina
From your answer, should I presume you mean I need to perform the calculation in a calc script rather than using the member formula?
Have you tried setting the member to Two-Pass calc? It's pretty complex so it might not help, but worth a shot. Give it a try.
Well, you can have a member formula and still use a calc script to calculate it if the member is stored, but that's pretty much what I meant, yes. But you should try Gary's suggestion of setting it to two pass also - that might fix it.
Hey, This seemed to solve the problem. I set the month Jan_10 member to two pass calc and I'm now getting the expected results. Thanks for your help and to the others for their suggestions.
Ok so now that I've fixed the problem with the month calculation, I've identified another issue. Within my Fund dimension, I have a hierarchy to split between different types of fund (Pension or Assurance). I'm now finding that the Level 1 Members aren't being calculated properly. Seems to be the same sort of issue as I had above with the Month. However if I try to set the Fund level 1 members to Two-Pass calc, I get an error to do with the CalcLockBlock setting or cache size. Any ideas?
I'm thinking you are going to have to do this in a calc script where you can clearly define the calc order. I would expect your Fund dimension is Sparse and dynamic calc on sparse dim is usually not optimal for query performance.
That makes sense. Being able to define the calc order sounds like it would help, now if only I knew how to do that. Must admit that we don't have a great deal of experience with calc scripts. We've only really used them for simple things like applying values from one member across others in a dimension or for clearing certain parts of the cube. Generally though we just run the default script which I think is just 'CALC ALL'. Any tips on how to structure it based on the details above would be appreciated....
The problem could be that the Fund dimension is being calculated during the database consolidation, but the Calc Profit/Loss member (being dynamic calc member formula) is being evaluated at retrieval time (i.e. last), at whatever level of the Fund dimension you are retrieving from. I.e. it is evaluating based on prices & units already consolidated at the higher levels in Funds. You could make Essbase do the Calc Profit/Loss member in a calc script, then finish the script with a Calc Dim("Funds") command. The downside is that Calc Profit/Loss member becomes stored, and the data file size may increase.
It isn't too difficult considering you already have a lot of the calcs already in the member formulas. Essentially you would move the formulas from the members into the script and set the members to Stored. Then it's just a matter of getting things in the right order. Try to stay away from CALC ALL in general, you can usually get better performance with CALC DIM and AGG. an example of a member calc to script would be, let's say you have a member "A" and the formula is "B"+"C"; In the script you would simply say "A" = "B" + "C"; The main thing you have to look at in calc scripts that you don't have in member formulas are FIX statements. FIX statements let you focus the part of the cube you want the script to work on. For example. FIX("Jan"); "A" = "B" + "C"; ENDFIX; This would only calculate "A" for the period "Jan". The tech ref and the DBAG have lots of examples and walk through how a lot of it works. If you or someone you work with really wants to learn this stuff, I would recommend coming to Kaleidoscope this year. There are a number of training sessions and you will likely find it worth the money. Check it out at http://www.odtugkaleidoscope.com/ Hope that helps.