9.4 Parameters not being properly passed to TI Process

[rfielden]

We have a spreadsheet where cells are linked to TI parameters. When the TI process is run, the parameters are not being successfully passed to the process. The spreadsheet and screen shots of the process are attached.

[rfielden]

Thought I might try one more time to get a 'bite' on this one. Been with Applix (or I should say IBM) support for three weeks and going around in circles. We have other processes that pass parameters fine to select statements but this is our first attempts passing to a procedure. Hopefully get a few nibbles this time around as forum has never let us down!

[Steve Rowe]

rfieldon,
It might be worth putting the content of the doc into the body of your post. That's just putting a barrier up for people looking at the problem. On a couple of posts the word doc has been on Word 2007 which would probably require alot of to patch our older versions of word and it all becomes a bit of a hassle....
Not help for your problem but it might help attract a friendly souls attention.

Cheers,

[rfielden]

We have attempted the following using ODBC vs Oracle procedure:
This works (when we pass hard coded values):
#****Begin: Generated Statements***
#****End: Generated Statements****
ODBCOpen('RDWPRD');
ODBCOutput('RDWPRD', 'TRUNCATE TABLE RF_RFIEL.LSM_PARAMETERS;');
ODBCOutput('RDWPRD','INSERT INTO RF_RFIEL.LSM_PARAMETERS (SLNO,DEPT,
CLASS)
VALUES (1,1046,13);');
ODBCClose('RDWPRD');
This doesn't work (when we pass parameters):
#****Begin: Generated Statements***
#****End: Generated Statements****
ODBCOpen('RDWPRD');
ODBCOutput('RDWPRD', 'TRUNCATE TABLE RF_RFIEL.LSM_PARAMETERS;');
ODBCOutput('RDWPRD','INSERT INTO RF_RFIEL.LSM_PARAMETERS (SLNO,DEPT,
CLASS)
VALUES (1,pDept1,pClass1);');
ODBCClose('RDWPRD');

[paulsimon]

Hi

I think that part of the problem with the example in your Word Doc, is that you are running a SQL Procedure to insert values in the Data Source tab. However, that tab is intended to be a select query to return values to use in the Variables, MetaData, and Data tabs.

The approach I would take would be to have a Data Source of None, then specify the parameters to the TI process in the Parameters Tab, then validate the parameters in the Prolog, and then to do an ODBCOpen and ODBCOutput and ODBCClose, all within the Prolog. With the ODBCOutput see the Help for use of the Expand command as a way of getting TI Variables in to a SQL String.

Regards

Paul Simon

[rfielden]

Paul,

Thanks for the reply. Being a still somewhat newbie, how do I validate parameters in the Prolog and also could not find anything related to "Expand command as a way of getting TI Variables in to a SQL String" in either the developers or reference guide.

Applix also still working this issue with no resolvement to date.

Thanks again,

Rick

[paulsimon]

Hi

By validate parameters I mean something along the lines of

IF( DIMIX('MyDim',pMyParam)= 0 ) ;
ItemReject('Invalid Param') ;
ENDIF ;

Assuming that the parameter value should exist in a Dim on your cube. By the way ItemReject works fine on the Prolog. It writes a message to the TM1ProcessError log file and stops the MetaData and Data from running. I generally validate as pParam then assigned to vParam once it is all checked, but others may prefer different conventions.

Expand is listed under TI Functions in the Miscellaneous section, or just do a search on Expand. The example shows an INSERT which is probably just what you want.

Regards


Paul Simon

[Alan Kirk]

By validate parameters I mean something along the lines of

IF( DIMIX('MyDim',pMyParam)= 0 ) ;
ItemReject('Invalid Param') ;
ENDIF ;

Assuming that the parameter value should exist in a Dim on your cube. By the way ItemReject works fine on the Prolog.

It does... it's always irritated the bejebbers out of me that it does since there obviously is no "item" in the Prolog, but it does notwithstanding what is, IMHO, bad coding on the part of TI's authors.

It writes a message to the TM1ProcessError log file

True, buuuut...

and stops the MetaData and Data from running.

Not so true.

What itemReject will do in either of those tabs is to stop the code in the remainder of that tab from running. An ItemReject in the Metadata tab will have no effect when the equivalent row is processed in the Data tab. An ItemReject in the Prolog tab will have bupkis effect on either the Metadata or the Data tab.

Try the following code (tested in 9.0, but I expect it'll be the same in other versions). This uses a 2 column .cma file, the first column is string, the second numeric:

Prolog

Code: Select all

ItemReject('How stoopid is it to allow a function which has no context to compile?');

Metadata

Code: Select all

AsciiOutput ('C:\Temp\MetaDataOutput.txt', V1, NumberToString (V2) );

Data

Code: Select all

AsciiOutput ('C:\Temp\DataOutput.txt', V1, NumberToString (V2) );

The prolog code will indeed spit an error into the error log, but the two output files are generated from the Metadata and Data tabs, indicating that each row of the data source is processed. (It may not process properly if the parameter is invalid and the code is dependent on it, but the code does run.) Which of course has the potential to cause problems, depending on what the code is. What will stop the Metadata/Data code from running is if the parameter is needed to modify the data source, and as a result of the failure the data source becomes invalid and there's no data to process. However the ItemReject alone isn't the cause of that, and I'd be reluctant to rely on it.

If the parameter is invalid, I'd prefer to do an AsciiOutput (though the ItemReject has the same effect) followed by either a ProcessQuit or ChoreQuit.

[rfielden]

I used the format for Expand as referenced on pg. 260 of reference guide. I create an Oracle table for both all numbers and another all string columns to see if that was the issue. On one error log I can see the correct parameters in the insert statement but still failed. Continue to get the following err msg:

Error: Prolog procedure line (12) for the insert command.

Applix support has been 'useless' the past three weeks on this issue. Yesterday they gave me the following code which was the worse yet:

I just heard back and the engineer is asking you to try:

ODBCOpen('RDWPRD','RDWTST30','cato');
ODBCOutput('RDWPRD', 'TRUNCATE TABLE RF_RFIEL.LSM_PARAMETERS;');
ODBCOutput('RDWPRD','INSERT INTO RF_RFIEL.LSM_PARAMETERS (SLNO,DEPT,
CLASS)
VALUES (1,StringToNumber(pDept1),StringToNumber(pClass1));');
ODBCClose('RDWPRD');

as I had already tried this, to_char, to_number, with and without quotes, etc. which were all posted to the support request.

I am at lost here and now trying to write VB macro and then a second button to fire off remaining TM1 processes which use the table being built as a backup and/or workaround.

[rfielden]

For those interested, here is what I had to do to make it work (this is only a partial portion of prolog):

IF( DIMIX('Years',pYear12) = 0 ) ;
pYear12 = 'NULL';
ENDIF ;

IF( DIMIX('Periods',pPeriod12) = 0 ) ;
pPeriod12 = ' ';
ENDIF ;

vSingle = '''';

vSql = 'INSERT INTO LSM_PARAMETERS3 (SLNO,DEPT,CLASS,SUBCLASS,MONTH,YEAR,CUT_OFF_DATE)VALUES (1,' | pDept1 | ',' | pClass1 | ',' | pSubclass1
| ',' | vSing
le | pPeriod1 | vSingle | ', '| pYear1 | ',' | vSingle | pDate | vSingle | ');';
ODBCOpen('Server','User','Password');
ODBCOutput('Server', 'TRUNCATE TABLE LSM_PARAMETERS3;');
ODBCOutput('Server',vSql);
ODBCClose('Server');

vSql = 'INSERT INTO RF_RFIEL.LSM_PARAMETERS3 (SLNO,DEPT,CLASS,SUBCLASS,MONTH,YEAR,CUT_OFF_DATE)VALUES (2,' | pDept2 | ',' | pClass2 | ',' | pSubclass2
| ',' | vSing
le | pPeriod2 | vSingle | ', '| pYear2 | ',' | vSingle | pDate | vSingle | ');';
ODBCOpen('Server','User','Password');
ODBCOutput('Server',vSql);
ODBCClose('Server');