TI reload process based of trigger table

[MaxwellSG]

I am very new to Cognos TM1. I was trying to Create a TI process that does check a value in a table compare that with a value in a cube.
if value in the table is greater than value in the cube, then it should save the date value to the cube. Else it should throw an error which stops from reloading rest of the batch components in the chore.
Can any body help me in doing this.
Any help is greatly appreciated.

Thank you

Suraj

[lotsaram]

This is quite easy in a tool like Cubeware Importer where you can connect to and query SQL or OLAP sources independently within the same script. It is rather more difficult using only TM1 TurboIntegrator as you can't issue discrete queries to an ODBC source but only in the context of a set of records. But you can do it without too much difficulty but you will just need some additional objects and a 2 step TI process.
1. Create an extra cube to store the value for "the total sum of the table" (this cube will not be multidimensional)
2. Create a TI process with a SQL query that will return just one record with the total sum and write it to the cube created in step 1.
3. Create a 2nd TI process with query that will read all table records. On the prolog of the process use CellGetN to compare the value from the "total table sum cube" with the total of all relevant cells from the actual cube that should be conditionally updated. If the values match then there is no need to update, set DataSourceType='NULL' and exit, if the values don't match then proceed with the update.

[rmackenzie]

Lotsa, I would have thought that it is possible in one TI based on your steps - i.e. why the need for storing the control value to a cube and returning to the calling process?

E.g. if the query in the data source is:

Code: Select all

SELECT ImportantValue FROM ControlTable WHERE SomeOtherControlValue=ValueToLimitSingleRecord

Then the Data tab could read:

Code: Select all

nCheckValue = CellGetN ( 'Cube', 'Important Other Value', 'Number' );
# V1 contains the important value to check against
IF ( V1 > nCheckValue );
  # throw an error
  ProcessError;
  # could do a ChoreQuit etc - lots of options
ENDIF;

# do other processing

I got the impression the OP wants to check a date serial or something to check the 'freshness' of the import. If, though, it was the case they want to check across a bunch of records then the query might be:

Code: Select all

SELECT SUM ( ColumnOfMultipleValues) FROM SomeTable

And then compare the sum to the control value in the cube?

Something like that could work; food for thought anyway. HTH.