Incorrect Consolidation Calculation

[ViRa]

Hi all,

I tried looking for solution in this forum but could not find any specific to my issue hence I'm starting a new thread.

The cube I'm working on holds data from ODBC datasource and does not involve any rules or calculations. I've accumulated values in load cube TI process to show the accumulated values wherever cells have more than one data. All this is working fine. However, when I set all the dimensions to the top level (such as total year, total months, total accounts, total services etc..), I noticed that for some records the consolidated values being displayed are incorrect.

I tried to verify the values for the incorrect record by running the query (same as used in the load cube TI process) in db and it fetched two records which I'm able to verify against the cube, which works fine. However, when I set all the dimensions for this record to the top level, the consolidation numbers are incorrect.
Eg, Claim amount for two dates are 144.00 and 135.00 summing to 279. But the cube shows at consolidation 414. Whereas if I verify the data by leaf level, I see the values 144 and 135 correctly.

Could you please advice what could be the reason, since I do not have rules (so no feeders), I'm unable to identify the issue.

Thanks for your time and help.

[EvgenyT]

Hi Vira,

From the top of my head I think your issue: double up of leaf level elements / consolidations in consolidations i.e. one element/consolidation resides in multiple consolidations...

That should get you started

[declanr]

279 + 135 = 414....

Is that element's weighting 2 by any chance? Are those 2 elements definitely the only 2 children? Is it in the consol twice? Have you right clicked on the consolidated element and selected trace calcs?

[ViRa]

Thanks Declan and Evengy for the reply.

Since I'm using the same query in TI to load the cube and also in the DB to verify the results, I'm sure there are just two entries for this record. I checked the weight for all the elements in the measure dimension is set to 1. Yes, I did trace calculation before posting this query and saw the value 414 for the month of Feb. The database also shows only two records for Feb but the total does not match with the trace calcs value.

[declanr]

Thanks Declan and Evengy for the reply.

Since I'm using the same query in TI to load the cube and also in the DB to verify the results, I'm sure there are just two entries for this record. I checked the weight for all the elements in the measure dimension is set to 1. Yes, I did trace calculation before posting this query and saw the value 414 for the month of Feb. The database also shows only two records for Feb but the total does not match with the trace calcs value.

Can you post a screenshot of the consolidated value expanded in the cube viewer and one of the trace calculations result?

[ViRa]

Attached is the screenshot.

[declanr]

In the trace calcs window you can click on the 414 and it will keep drilling down until you see where the numbers are coming from.

It's difficult to tell what it is without the view showing all the dims expanded (from the 414) and being zero supressed.

[rmackenzie]

Your circumstances (odd results at consolidated level; no rules) point to double ups of elements in consolidations in one or more of the dimensions. You need to check every dimension in the cube that has consolidations to make sure there's no instance of an element being included twice, or more. You can use the MDX code snippet below to identify these problems - just paste it into the Expression Window in the Subset Editor and hit 'update' after you've replaced the dimension and consolidation names with those appropriate to your cube:

Code: Select all

{EXCEPT (
  {TM1DRILLDOWNMEMBER( {[YOUR_DIMENSION_NAME].[YOUR_CONSOLIDATION]}, ALL, RECURSIVE )},
  {TM1SUBSETALL( [YOUR_DIMENSION_NAME] )}, 
ALL)}

[ViRa]

In the trace calcs window you can click on the 414 and it will keep drilling down until you see where the numbers are coming from.

It's difficult to tell what it is without the view showing all the dims expanded (from the 414) and being zero supressed.

I did drill down until end and noticed that until the element (just before the leaf), the value keeps showing 414 and clicking that value takes me to leaf level where it breaks down into two elements each holding 135 and 144 respectively.

our circumstances (odd results at consolidated level; no rules) point to double ups of elements in consolidations in one or more of the dimensions. You need to check every dimension in the cube that has consolidations to make sure there's no instance of an element being included twice, or more. You can use the MDX code snippet below to identify these problems - just paste it into the Expression Window in the Subset Editor and hit 'update' after you've replaced the dimension and consolidation names with those appropriate to your cube:

Robin, the dimension structure is such that it contains 6 levels and not all level contains a child element. Eg, as shown below, the element v762 contains children all the way to the leaf level in some cases and in some it does not contain any children. This dimension was built by setting all the levels to consolidation and the final level as element. Hence there are blanks (identified as '-') wherever child is not present. I tried pasting your code but on hitting update all the results disappear.

MDX.JPG (44.7 KiB) Viewed 8475 times

[ViRa]

Please guide me and help me resolve this issue.

[rmackenzie]

I tried pasting your code but on hitting update all the results disappear.

That actually means that non of the elements are doubling up in that particular dimension hierarchy (i.e. v762 in CREATEICDTEST). If you hit Update and got a result, that would be the element that was potentially causing the problem. You should try the code in the other dimensions.

[ViRa]

Yes, there was indeed an element part of two consolidations and hence the incorrect calculation. I made the elements unique and correct results are being displayed now.
Thanks a lot all for your guidance and time.