Strange posting title I know.
I'm working on a very big modelling system. Within it there are several areas where (for instance) we take a reserve and run it down to nil, using subtraction consolidations plus some fiddly rules to get the prior 'period' value. I am finding that TM1 is leaving very small numbers behind when it does the calculations, eg 2e-20. In materiality terms, these are irrelevant - but the values are taking up RAM, which is a scarce resource. I have done all sorts of lively stuff with feeder management, but these minute numbers hang around, showing as 0 or (0) in a zero suppressed view. I have tried using roundp, but that isn't eliminating the values. I'm beginning to suspect 9.4+'s handling of subtraction consolidations is flawed.
Suggestions very welcome.
Core platform is Cognos Express = TM1 9.4, but I've ported the app back and forward between that and TM1 9.5.1 - no difference in behaviour.
Sludge issues
- Alan Kirk
- Site Admin
- Posts: 6608
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Sludge issues
Random thought off the top of my head... but would rounding the input numbers to (say) two decimal places upon upload help? Easy if it's done by TI, harder if it's done manually... but it might be an interesting idea to (in your dev environment) create a view showing all of the non-calculated entries and round them back to a couple of decimal places and see whether the problem persists. I know that when we have uploads from DBRWs/DBSs from Excel, values which are uploaded sometimes go up with some ridiculous decimal values from having been calculated in Excel.David Usherwood wrote:Strange posting title I know.
I'm working on a very big modelling system. Within it there are several areas where (for instance) we take a reserve and run it down to nil, using subtraction consolidations plus some fiddly rules to get the prior 'period' value. I am finding that TM1 is leaving very small numbers behind when it does the calculations, eg 2e-20. In materiality terms, these are irrelevant - but the values are taking up RAM, which is a scarce resource. I have done all sorts of lively stuff with feeder management, but these minute numbers hang around, showing as 0 or (0) in a zero suppressed view. I have tried using roundp, but that isn't eliminating the values. I'm beginning to suspect 9.4+'s handling of subtraction consolidations is flawed.
Suggestions very welcome.
Core platform is Cognos Express = TM1 9.4, but I've ported the app back and forward between that and TM1 9.5.1 - no difference in behaviour.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Sludge issues
I have experienced the same type of issue and frankly, I don't know any way around it except for eliminating the rule and/or consolidation and doing it in TI and piping the rounded results to the cube. The bottom line seems to be in TM1 a value that "mathematically" calculates to zero, through either a consolidation or through a rule (excluding an IF statement condition that sets a value to 0), results many times in an extremely small number, like .00000000000001, that is effectively zero, but is not an empty cell as defined by TM1.David Usherwood wrote:Strange posting title I know.
I'm working on a very big modelling system. Within it there are several areas where (for instance) we take a reserve and run it down to nil, using subtraction consolidations plus some fiddly rules to get the prior 'period' value. I am finding that TM1 is leaving very small numbers behind when it does the calculations, eg 2e-20. In materiality terms, these are irrelevant - but the values are taking up RAM, which is a scarce resource. I have done all sorts of lively stuff with feeder management, but these minute numbers hang around, showing as 0 or (0) in a zero suppressed view. I have tried using roundp, but that isn't eliminating the values. I'm beginning to suspect 9.4+'s handling of subtraction consolidations is flawed.
Suggestions very welcome.
Core platform is Cognos Express = TM1 9.4, but I've ported the app back and forward between that and TM1 9.5.1 - no difference in behaviour.
- Martin Ryan
- Site Admin
- Posts: 1988
- Joined: Sat May 10, 2008 9:08 am
- OLAP Product: TM1
- Version: 10.1
- Excel Version: 2010
- Location: Wellington, New Zealand
- Contact:
Re: Sludge issues
I think this might be a bits and bytes issue. I think TM1 is (only) accurate to 15 decimal places which sometimes means things don't quite get to where you need them to for things like time to repay calculations.
I had a similar issue in a model I was building for a finance company. I ended up having to include an if statement (if abs(lastPeriod)<0.1, 0, lastPeriod) which will of course slow it down, but at least it removed the zero suppression and feeder issue.
Martin
I had a similar issue in a model I was building for a finance company. I ended up having to include an if statement (if abs(lastPeriod)<0.1, 0, lastPeriod) which will of course slow it down, but at least it removed the zero suppression and feeder issue.
Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
Jodi Ryan Family Lawyer
- stephen waters
- MVP
- Posts: 324
- Joined: Mon Jun 30, 2008 12:59 pm
- OLAP Product: TM1
- Version: 10_2_2
- Excel Version: Excel 2010
Re: Sludge issues
I recall something very similar with an older OLAP product. Numbers that were calculated and appeared to be zero were actually stored as 0.0000000000000000012345 or such like. The support team told us it was a bits and bytes issues (something to do with floating point arithmetic?).tomok wrote:
I have experienced the same type of issue and frankly, I don't know any way around it except for eliminating the rule and/or consolidation and doing it in TI and piping the rounded results to the cube. The bottom line seems to be in TM1 a value that "mathematically" calculates to zero, through either a consolidation or through a rule (excluding an IF statement condition that sets a value to 0), results many times in an extremely small number, like .00000000000001, that is effectively zero, but is not an empty cell as defined by TM1.
With that product we could set these values to NA since it differentiated between zero and null cells. Not much help here though.