TI Process doesn't return a correct value

Post Reply
j.calibs
Posts: 6
Joined: Tue Apr 21, 2020 4:13 am
OLAP Product: IBM PAL
Version: IBM PAL 11.0
Excel Version: Version 1908

TI Process doesn't return a correct value

Post by j.calibs » Fri Oct 30, 2020 9:32 am

Hi,

I have an existing TI process which doesn't return the correct value of the cube I'm referencing to. The current process doubles the expected value. ViewZeroOut function is running before the process so I'm kinda stuck why this is happening. I can't seem to find anything wrong in the process, the codes are just generated and I didn't add any since the process will just pull the data from the other cube. Can anyone please enlighten me with this.

Thank you.

Edward Stuart
Community Contributor
Posts: 239
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester
Contact:

Re: TI Process doesn't return a correct value

Post by Edward Stuart » Fri Oct 30, 2020 11:08 am

Sounds like a lot is happening and without seeing your code it will be very hard to give a helpful response.

I would comment out all aspects of the process except for the ViewZeroOut to ensure that is working as expected. Then include an asciioutput to ensure the remainder of your code is aggregating/ exporting/ calculating values as expected and hopefully identify where duplications may or may not be appearing

j.calibs
Posts: 6
Joined: Tue Apr 21, 2020 4:13 am
OLAP Product: IBM PAL
Version: IBM PAL 11.0
Excel Version: Version 1908

Re: TI Process doesn't return a correct value

Post by j.calibs » Tue Nov 03, 2020 10:38 am

Edward Stuart wrote:
Fri Oct 30, 2020 11:08 am
Sounds like a lot is happening and without seeing your code it will be very hard to give a helpful response.

I would comment out all aspects of the process except for the ViewZeroOut to ensure that is working as expected. Then include an asciioutput to ensure the remainder of your code is aggregating/ exporting/ calculating values as expected and hopefully identify where duplications may or may not be appearing
Hi Edward,

I appreciate your comment. I've tried including the asciioutput and it seems that the code is aggregating/exporting correctly. But the file doesn't include the value since it is not an element.

Here is the existing code on the process:
#****Begin: Generated Statements***
vMeasure='Total_Cost';
vVersion=pVersion;
if (VALUE_IS_STRING=1, CellPutS(SVALUE,'Test_Cube',vYear,vPeriod,vVersion,vCurrency,vPlant,vComponentSKU,vMeasure), CellPutN(NVALUE, 'Test_Cube', vYear,vPeriod,vVersion,vCurrency,vPlant,vComponentSKU,vMeasure));
#****End: Generated Statements****

It is just system generated since I will only pull the data from my Source Cube.

Many Thanks,
Jaine

User avatar
PavoGa
Community Contributor
Posts: 465
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: TI Process doesn't return a correct value

Post by PavoGa » Tue Nov 03, 2020 5:49 pm

Add an ASCIIOUTPUT after the generated statements:

Code: Select all

# to use this you'll have to define sDataLogfile with filename and path for the output.
ASCIIOutput(sDataLogfile, EXPAND('%NValue%, %vYear%, %vPeriod%, %vCurrency%, %vPlant%, %vComponentSKU%, %vMeasure%'));

This will tell you what you are reading. Based on what you've said, I'm thinking your datasource is not what you think it is.
Ty
Cleveland, TN

j.calibs
Posts: 6
Joined: Tue Apr 21, 2020 4:13 am
OLAP Product: IBM PAL
Version: IBM PAL 11.0
Excel Version: Version 1908

Re: TI Process doesn't return a correct value

Post by j.calibs » Wed Nov 04, 2020 12:48 am

PavoGa wrote:
Tue Nov 03, 2020 5:49 pm
Add an ASCIIOUTPUT after the generated statements:

Code: Select all

# to use this you'll have to define sDataLogfile with filename and path for the output.
ASCIIOutput(sDataLogfile, EXPAND('%NValue%, %vYear%, %vPeriod%, %vCurrency%, %vPlant%, %vComponentSKU%, %vMeasure%'));

This will tell you what you are reading. Based on what you've said, I'm thinking your datasource is not what you think it is.
I already tried adding this in the process and it seems that it is aggregating as expected.

User avatar
PavoGa
Community Contributor
Posts: 465
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: TI Process doesn't return a correct value

Post by PavoGa » Wed Nov 04, 2020 10:40 am

Where is the ViewZeroOut? In the prolog or in a subprocess?
Ty
Cleveland, TN

declanr
MVP
Posts: 1679
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: TI Process doesn't return a correct value

Post by declanr » Wed Nov 04, 2020 11:20 am

j.calibs wrote:
Wed Nov 04, 2020 12:48 am
I already tried adding this in the process and it seems that it is aggregating as expected.
Focussing on the word "aggregating" here.

If your source has more than 1 record that should go to the same target cell; you need to specify that it should aggregate those values - for example swap your CellPutN for a CellIncrementN statement. Otherwise you will just end up with the last source record's value.
Declan Rodger

declanr
MVP
Posts: 1679
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: TI Process doesn't return a correct value

Post by declanr » Wed Nov 04, 2020 9:08 pm

I have re-read the first post and realised that I somehow missed the main point that its doubling a value. This makes my last reply an unlikely cause, not impossible however if you are trying to aggregate 2 values that just so happen to include 1 being a negative 50% of the other.

Is the end result always exactly double what you want? Or if you run the process 3 times does it end up as triple? And 4 times quadruple etc?
If so, then as others have suggested your zeroout would be the issue - again you can test this by manually clearing the cube once before you run it.

Also have you drilled down to the most granular cell level to see if its doubled there? Or are you just seeing the double value at a high consolidated level? If so it is possible that n-level elements are appearing in your hierarchy twice, or have a weight of 2 where they would usually be weighted as 1.

If everything else fails, you could perhaps provide a small sample of source data and show the result it is creating? Along with providing your full code from every tab, including the zeroout statement etc.
Declan Rodger

User avatar
PavoGa
Community Contributor
Posts: 465
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: TI Process doesn't return a correct value

Post by PavoGa » Mon Nov 09, 2020 10:39 pm

j.calibs wrote:
Wed Nov 04, 2020 12:48 am
PavoGa wrote:
Tue Nov 03, 2020 5:49 pm
Add an ASCIIOUTPUT after the generated statements:

Code: Select all

# to use this you'll have to define sDataLogfile with filename and path for the output.
ASCIIOutput(sDataLogfile, EXPAND('%NValue%, %vYear%, %vPeriod%, %vCurrency%, %vPlant%, %vComponentSKU%, %vMeasure%'));

This will tell you what you are reading. Based on what you've said, I'm thinking your datasource is not what you think it is.
I already tried adding this in the process and it seems that it is aggregating as expected.
As pointed out by Declan and what I was targeting, there is no way the process is aggregating values in a cell with a CELLPUT. Not based on the little bit of code you have shown.
Ty
Cleveland, TN

User avatar
PavoGa
Community Contributor
Posts: 465
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: TI Process doesn't return a correct value

Post by PavoGa » Mon Nov 09, 2020 10:41 pm

j.calibs wrote:
Wed Nov 04, 2020 12:48 am
PavoGa wrote:
Tue Nov 03, 2020 5:49 pm
Add an ASCIIOUTPUT after the generated statements:

Code: Select all

# to use this you'll have to define sDataLogfile with filename and path for the output.
ASCIIOutput(sDataLogfile, EXPAND('%NValue%, %vYear%, %vPeriod%, %vCurrency%, %vPlant%, %vComponentSKU%, %vMeasure%'));

This will tell you what you are reading. Based on what you've said, I'm thinking your datasource is not what you think it is.
I already tried adding this in the process and it seems that it is aggregating as expected.
As pointed out by Declan and what I was targeting, there is no way the process is aggregating values in a cell with a CELLPUT. What you are describing is not possible based on the little bit of code you have shown.

We need to see the code and some sample source data and the cube results.
Ty
Cleveland, TN

Post Reply