Consolidations working oddly

[PavoGa]

This is REALLY bizarre and want to know if anyone has seen this. We have a cube that within a particular view, certain accounts of the accounts dimension, but not the majority, are displaying a doubled up value of their component elements within their respective consolidation. Higher level consolidations reflect the incorrect total as well. Now here is the fun part. Change the view by selecting one of the offending accounts and the problem goes away. If we add to the ORG dimension subset the next level ORG consolidation (01.XXX) like:

01.XXX
--01.XXX.001
--01.XXX.002

Then the correct amounts are displayed.

Only certain accounts are affected. The affected accounts are all natural accounts (NNN-NNN-NNN).
There are two rules that use the ConsolidateChildren function, but they are conditional rules (see below) for memo accounts (FTEs, etc) to calculate averages. The conditional looks for the value of an attribute on the account dimensions and none of the affected accounts are set to have the rule applied.

Change any subset in the view on any dimension and the problem disappears. We have been unable to duplicate the problem in our DEV or UAT environments. We are bringing a copy of the PROD instance down to the UAT server to try and replicate the issue there.

Complete rule file on this cube:

Code: Select all

#Region System
SKIPCHECK;
FEEDSTRINGS;
#EndRegion

# Calculates the CY element as the average value of a Memo Account where MemoAccountFlag = 3 for 
['Current - Yr X', {'Plan Summary', 'Final'}, 'CY'] = C: IF(ATTRN('Accounts_Forecast', !Accounts_Forecast, 'MemoAccountFlag') = 3
	, ConsolidateChildren('Fcst - Periods') \ ELCOMPN('Fcst - Periods', !Fcst - Periods)
	, CONTINUE);

# Calculates the Total Variances of Memo accounts Variances from the Previous Plan.
['Current - Yr X', {'Plan Summary', 'Final'}, 'VarianceFromPreviousPlan'] = C: IF(ATTRN('Accounts_Forecast', !Accounts_Forecast, 'MemoAccountFlag') = 3
	, ConsolidateChildren('Fcst - Periods') 
	, CONTINUE);
	
# Retrieves the total adjustments for the account, org, and period from Fcst - Adjustment Details.  Feed from Fcst - Adjustment Details:Adjustment Amount.
['Current - Yr X', 'Adjustment'] = N: DB('Fcst - Adjustment Details',  !Versions, !Fcst - Periods, !Accounts_Forecast, !Organization, 'Total', 'Adjustment Amount');

FEEDERS;

### External Feeders

# Fcst - Adjustment Details.
['Current - Yr X', 'Plan Summary'] => DB('Fcst - Adjustment Details', !Versions, !Fcst - Periods, !Accounts_Forecast, !Organization, '1', 'Baseline Number');

If we can replicate the problem, we will remove the rule file first. Otherwise, has anyone seen this type of behavior before?

[PavoGa]

An update:

We were able to recreate the issue by copying the Prod instance down to one of our other servers. Removing the two ConsolidateChildren rules did initially display the values correctly. However, as soon as the view was refreshed, the values were doubled again.

We have submitted this to IBM.

[lotsaram]

Are you sure that you removed all the rules where ConsolidateChildren might be taking effect? Because as soon as you use ConsolidateChildren or use any C rule to specifically override natural consolidations then I don't think you can really make a claim to "consolidations behaving oddly" unless you have first proved absolutely that whatever results you are seeing are not a result of the rules in effect overriding the consolidation result.

If you are seeing a doubling of the result you expect also of course check
- element weightings in the consolidations
- that members have not been included 2x in the consolidation

[PavoGa]

Thank you for responding, lotsaram. I was about to think this had scared everyone off.

Yes, we did remove all the rules. I included the complete rule file in the original posting and you can see there are only two rules with ConsolidateChildren in them. On top of that, those are conditional rules and we checked that probably a half-dozen times. Additionally, Trace Calculations confirms no rules are being applied to the affected cells. We also confirmed it is only one single account with the problem.

And just about any change to the view, the problem goes away. For example, if we cut the account subset down to just this one account, no problem. Add a consolidation level to a different dimension, no problem. Cut the Organization dimension to a single leaf or consolidation element, the problem disappears.

We checked the weighting of course, but that is fine and would not explain why the problem disappears with changes to the view. I thought about the account being in the consolidation twice or some other dimension consolidation with a problem, but everything looks fine. With regards to other dimensions, again, if that were it, we'd see it on every account.

Like I said, we submitted it to IBM. If we find out anything, we'll update the thread.

Again, thank you for your response.

[PavoGa]

Update:

We demonstrated the problem to IBM. They said, and I quote, "Hmmm."

I think we are going to package up the model and send it to them. It is bothersome what is happening with, at this point, no conceivable explanation.