TM1 cube different values with dimension (re)arrangment

[kaazimraza]

Hi folks,

We have TM1 10.1.1 server deployed in our environment that users access from Architect (power users and dev), TM1 Web and Excel. Off late we have come across this strange issue when viewing a cube's data in cube viewer in Architect. The cube has 10 dimensions and contains data from an underlying Oracle database, and has no rules to it. All dimensions, except for the measures, have a 2 to 3 level hierarchies with an 'All' master parent in each dimension. Amount is the only measure within the cube.

Dimensions: Version, Years, Months, Company, Cost Centres, Locations, Line of Business, Identifiers, Accounts and Measures.

The issue is that the amount measure gets a new value every time you slice the data depending upon how the dimensions have been arranged, you get a different value. For instance, say, you select All Companies, All Accounts. You keep the Company on title, and place Accounts on rows with Months on columns, and all other dimensions on title. You'll get a value of say, 100,000.

Swap Company with Account so Company is on row and Account on title, (both still at All level) and Months on columns. You get a value of say, 85,000.

If I keep both Company and Account on row, it gives me 100,000 again.

I am looking for ideas to how to go about this cracking this one, including debugging the problem and then resolving it. Given the number of elements in dimensions, I am getting nowhere with debugging itself as I am maxing out memory with a view getting to large to be loaded.

[paulsimon]

Hi Kaz

I can't tell exactly but at a rough guess I would say that your problem is due to one of the All consolidations not including all elements in the dim.

Regards

Paul Simon

[kaazimraza]

Hi Kaz

I can't tell exactly but at a rough guess I would say that your problem is due to one of the All consolidations not including all elements in the dim.

Regards

Paul Simon

Hi Paul,

Thanks for your reply. I did get rid of all alternate hierarchies and made sure that the All consolidations have all elements as it children, but to no avail.

EDIT:
Interestingly, if you right click and do a trace calculation on the consolidation cell with an incorrect value, the calculation trace window does give the correct value. But the cell still shows an incorrect value.

[kaazimraza]

I have been able to resolve this! The problem was a large number at one of the N levels. It was a 20 digit number that was making TM1 going crazy. As soon as I replaced the 20 digit number with 0, all numbers started to behave!

This leads me to ponder what's the biggest number that we can store in TM1?

[gtonkin]

Hi Kaz, short answer is 15 AFAIK.

See
https://www-304.ibm.com/support/docview ... wg21515228

and I would recommend reading this too if you integrate with office
https://support.office.com/en-au/articl ... c00b05040f

[kaazimraza]

Hi Kaz, short answer is 15 AFAIK.

See
https://www-304.ibm.com/support/docview ... wg21515228

and I would recommend reading this too if you integrate with office
https://support.office.com/en-au/articl ... c00b05040f

Thanks.