Variable not registering in Advanced tabs

kbogies
Posts: 31
Joined: Tue Jun 10, 2008 8:21 pm
OLAP Product: TM1
Version: 9.1sp4 64x
Excel Version: 2003
Location: Hartford, CT

TI: IF & chorequit syntax

Post by kbogies »

brainfart after returning from vaca...
my sql dba and i each have our own autosys job (scheduled via bat files); however i only want to execute the TM1 chore based on the condition that the sql db generates the code "NOTE: Processing Completed" rather than the "completion" of the db's autosys job. therefore if an error occurs in the db, my chore to recreate the TM1 objects will not execute.

what i have done is set up a ti process to check for the "NOTE: Processing Completed", however i get tm1 process error: syntax error on or before:'next_proc'<>"NOTE: .... logical expression

variables tab - name:next_proc / type:string / contents:other
advanced,prolog tab -
IF ('next_proc'<>"NOTE: Processing Completed");
ChoreQuit;
EndIf;

would someone help with my synthax (i'm always open to alternative suggestions as well)?

Thanks!
kbogies :D
User avatar
Alan Kirk
Site Admin
Posts: 6606
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: TI: IF & chorequit syntax

Post by Alan Kirk »

kbogies wrote:brainfart after returning from vaca...
my sql dba and i each have our own autosys job (scheduled via bat files); however i only want to execute the TM1 chore based on the condition that the sql db generates the code "NOTE: Processing Completed" rather than the "completion" of the db's autosys job. therefore if an error occurs in the db, my chore to recreate the TM1 objects will not execute.

what i have done is set up a ti process to check for the "NOTE: Processing Completed", however i get tm1 process error: syntax error on or before:'next_proc'<>"NOTE: .... logical expression

variables tab - name:next_proc / type:string / contents:other
advanced,prolog tab -
IF ('next_proc'<>"NOTE: Processing Completed");
ChoreQuit;
EndIf;

would someone help with my synthax (i'm always open to alternative suggestions as well)?

Thanks!
kbogies :D
I'm not getting the bit about where all these jobs are running and what they're doing, but your syntax problem is simply that you have to use @<>, not just <>, when you're checking against a string value.

Edit: Also, use single quotes, not double quotes to wrap around the strings.
"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.
kbogies
Posts: 31
Joined: Tue Jun 10, 2008 8:21 pm
OLAP Product: TM1
Version: 9.1sp4 64x
Excel Version: 2003
Location: Hartford, CT

Re: TI: IF & chorequit syntax

Post by kbogies »

thanks for the syntax correction alan.

now when i ran a test chore:
#first a process with the syntax:
IF(next_proc@<>'NOTE: Processing Completed');
ChoreQuit;
EndIf;
#secondly a test process

the entire chore seems to abort because of the chorequit command. the process doesn't seem to take into account that the chorequit syntax is nested in the IF statement. note: for this test, next_proc is "NOTE: Processing Completed" in the db. do i need to take extra step to nest the chorequit syntax within the IF syntax?
lotsaram
MVP
Posts: 3647
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TI: IF & chorequit syntax

Post by lotsaram »

You don't need to do anything additional to endure that ChoreQuit is nested in the IF ... ENDIF

Where are you setting next_proc? Have you checked that your next_proc variable really is equal to the string you are testing?

For example:
IF(next_proc@<>'NOTE: Processing Completed');
ASCIIOutput('DebugFile.txt', next_proc);
ChoreQuit;
EndIf;
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: TI: IF & chorequit syntax

Post by Steve Rowe »

Just in case it doesn't like the way you have defined the test try Not equal

If ( ~ next_proc@='NOTE: Processing Completed')
Technical Director
www.infocat.co.uk
kbogies
Posts: 31
Joined: Tue Jun 10, 2008 8:21 pm
OLAP Product: TM1
Version: 9.1sp4 64x
Excel Version: 2003
Location: Hartford, CT

Re: TI: IF & chorequit syntax

Post by kbogies »

I took lotsaram's advice and added the asciioutput command to verify if my variable is being recognized correctly and it is not.

i see "NOTE: Processing Completed" in the datasource's preview window as the next_proc field; in the variables tab i've identified the next_proc field as contents: other and again see "NOTE: Processing Completed" as the sample string value; however with the below syntax run from the prolog tab, the asciioutput file shows an error and does not recognize the variable.

ASCIIOutput('C:\temp\DebugFile.txt', next_proc);

IF(next_proc@<>'NOTE: Processing Completed');
ChoreQuit;
EndIf;

what am i missing/doing incorrectly?
kbogies
Posts: 31
Joined: Tue Jun 10, 2008 8:21 pm
OLAP Product: TM1
Version: 9.1sp4 64x
Excel Version: 2003
Location: Hartford, CT

Re: TI: IF & chorequit syntax

Post by kbogies »

update: just got off the phone with IBM support (Chris from Applix) and through trial & error, we moved the syntax from prolog tab to epilog tab and the variable is now registering and the process works. he seems to think that there is some timing issue with the sql query via odbc conflicts with the advanced tabs... he's looking into this to see why, but at least it works now :D
kbogies
Posts: 31
Joined: Tue Jun 10, 2008 8:21 pm
OLAP Product: TM1
Version: 9.1sp4 64x
Excel Version: 2003
Location: Hartford, CT

Re: TI: IF & chorequit syntax

Post by kbogies »

another day, another result :cry: variable is not registering in the epilog tab now... chris from IBM/applix is looking into this still... anyone have any thoughts relating to ODBC connections to sql db, and variables not registering in the advanced tabs?
kbogies
Posts: 31
Joined: Tue Jun 10, 2008 8:21 pm
OLAP Product: TM1
Version: 9.1sp4 64x
Excel Version: 2003
Location: Hartford, CT

Variable not registering in Advanced tabs

Post by kbogies »

i'm reposting the below topic because the issue is believed to be a clitch with the datasource rather than the TI syntax.

The TI process's datasource is a SQL sub query via ODBC connection. See below for query. The query presents the expected results in the datasource tab's preview window. The sample value is also appearing as expected on the variables tab, where i've identified the variable as a string and identified the contents as other to write advanced syntax.

However, the variable does not register properly on the advanced prolog tab. Initial testing with IBM (applix) moved syntax to the epilog tab and the variable registered correctly, until the following day when the variable did not register on either advanced tab.

Has anyone experienced similar issues with odbc datasources?

http://forums.olapforums.com/viewtopic.php?f=3&t=1060

sql sub query:
select next_proc from dbo.EXEC_RUN_LOG
where ID = (select max(ID) from dbo.EXEC_RUN_LOG)
User avatar
Alan Kirk
Site Admin
Posts: 6606
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: Variable not registering in Advanced tabs

Post by Alan Kirk »

kbogies wrote:i'm reposting the below topic because the issue is believed to be a clitch with the datasource rather than the TI syntax.

The TI process's datasource is a SQL sub query via ODBC connection. See below for query. The query presents the expected results in the datasource tab's preview window. The sample value is also appearing as expected on the variables tab, where i've identified the variable as a string and identified the contents as other to write advanced syntax.

However, the variable does not register properly on the advanced prolog tab. Initial testing with IBM (applix) moved syntax to the epilog tab and the variable registered correctly, until the following day when the variable did not register on either advanced tab.

Has anyone experienced similar issues with odbc datasources?

http://forums.olapforums.com/viewtopic.php?f=3&t=1060

sql sub query:
select next_proc from dbo.EXEC_RUN_LOG
where ID = (select max(ID) from dbo.EXEC_RUN_LOG)
It is unsafe to expect ANY values to be available from a process' data source on either the Prolog or Epilog tabs, particularly the former.

The prolog executes once, before a single row of the data source is read. At that time, any variables will have a value of either 0 or an empty string, as the case may be. As I mentioned in a recent thread, my own view is that the compiler should throw a syntax error if the Variables tab variables are used in the Prolog since it's effectively the same as having an uninitialised code variable. It doesn't, but it should.

The Metadata tab will loop through each row of the data source once, allowing the process to add any new elements and the like.

The Data tab will loop through each row of the data source once, allowing the data to be written to the cubes or otherwise manipulated.

The Epilog tab will execute once, AFTER the last row of data has been processed.

Although I haven't tested it, I would expect any process (Variables tab) variables to retain the values that they had from the last row of data when the Epilog is executed... however if the data source HAD no data then again the variables would be either 0 or an empty string, as the case may be.

(Though even if this supposition is right, you're still using the variable values out of context and IMHO, in an unsafe fashion. The only places that Variables tab variable values have a valid context are in the Metadata or Data tabs.)
"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.
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: TI: IF & chorequit syntax

Post by Steve Rowe »

Well,
Usually variables from a data source are not available in the prolog since the datasource is only opened when the prolog is finished.
The two data tabs everythign should be fine.
The Epilog may not be OK, but I'm not 100%, it's probable that the data variables are not valid on the epilog either I can't recall. I'm not sure I have ever tried to do it. Also if your query returns no records then the epilog will fail too. The last record in a file is often an eof indicator not the last row of data anyway..

Try putting

TestNext_Proc=next_proc;

at the end of the data tab and then in the epilog

IF(TestNext_Proc@<>'NOTE: Processing Completed');
ChoreQuit;
EndIf;

Since you are using your own variable this should work.

HTH


[MOD NOTE : TOPICS MERGED NO NEED TO SPLIT, STOPS PEOPLE ANSWERING THE SAME QUESTION TWICE! :lol: ]
Technical Director
www.infocat.co.uk
User avatar
Michel Zijlema
Site Admin
Posts: 712
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Variable not registering in Advanced tabs

Post by Michel Zijlema »

Like Alan mentioned, the datasource will only be processed on the MetaData and/or Data tab (and only when there is process code on these tabs).
So the content of the variables will not be available on the Prolog tab. On the Prolog tab f.i. you can still change the datasource by setting the DataSourceNameForServer variable or you manipulate the ?dimname? parameters to set the required results of your (drill-through) query.
If the datasource has been processed on the data tabs then the content of the variables on the Epilog tab will be from the last row processed.

What's disturbing me is that you contacted support for this and that they presented this as some kind of glitch and are trying to find a solution on the Epilog tab.
If this is really how they helped you, then they appearently don't have a clue on what they're supporting...

Michel
User avatar
Alan Kirk
Site Admin
Posts: 6606
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: Variable not registering in Advanced tabs

Post by Alan Kirk »

Michel Zijlema wrote: What's disturbing me is that you contacted support for this and that they presented this as some kind of glitch and are trying to find a solution on the Epilog tab.
If this is really how they helped you, then they appearently don't have a clue on what they're supporting...
Yes, that thought crossed my mind as well. Though admittedly it may be that they didn't "get" the question; I didn't get what was trying to be done from the original post, and only did so from the description in the duplicate thread. At that point it became obvious that there was something structurally wrong with the way the task was being performed.
"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.
User avatar
Alan Kirk
Site Admin
Posts: 6606
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: Variable not registering in Advanced tabs

Post by Alan Kirk »

I should have added this in my reply as well; there's nothing to stop you from doing what you're trying to in the Data tab.

We do something similar. The chore that feeds our PNL cube from the general ledger zeroes out all of the data in the cube, then does a fresh load of it. The problem was that when the GL system was down the data would be blown away, but there would be nothing to refresh it from.

The solution was to have a process which reads a single row from the database (much as you're doing) before the zero out process was reached. In the Prolog tab we set a run continuation flag in our chore control cube to "N". The Data tab of the process sets it to "Y", though of course if the GL system is down (meaning that no data is available to process) then that line of code will never be executed. As a consequence the run continuation flag stays as "N" and the subsequent processes know that they should just quit.

There's no reason that you couldn't do something similar in the Data tab of your process, only using ChoreQuit instead of setting a flag.
"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.
kbogies
Posts: 31
Joined: Tue Jun 10, 2008 8:21 pm
OLAP Product: TM1
Version: 9.1sp4 64x
Excel Version: 2003
Location: Hartford, CT

Re: Variable not registering in Advanced tabs

Post by kbogies »

Yes, Alan... that's what i'm trying to do. Thank you to all that contributed to this post! I moved the syntax to the data tab and it is working consistantly now. I've only ever put asciioutput on the prolog tab (as did IBM support from applix). I needed a thorough understanding of how all the advance tabs worked (as does IBM support), as i usually create TI processes with generated statements.

Thanks again... i don't know what i'd do without this forum and your help! much appreciated!
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Variable not registering in Advanced tabs

Post by Steve Vincent »

kbogies wrote:... I needed a thorough understanding of how all the advance tabs worked (as does IBM support), as i usually create TI processes with generated statements.
:lol:
Yes i agree, IBM support is seriously lacking right now, especially if they haven't even got to grips with the tabs yet.

Quick summary of the tabs for anyone that kinds it helpful;

PARAMETERS
allows user interaction by prompting for a value / response to a question such as "which month do you wish to load to?". value can be used in any of the following tabs.

PROLOG
Runs just once and BEFORE any datasource is read. Commonly used to clear a cube before new data is entered. As the datasource is not touched it is not possible to refer to anything from it in this tab. Static, user defined variables are best placed here also and anything in here can be refered to in later tabs.

METADATA
Runs as many times as you have lines of data. Example, an ascii source with 1000 lines will make this code run 1000 times. Most commonly used to manipulate dimensions prior to loading data, such as checking for / adding new elements.

DATA
Same as metadata in that it runs as many times as you have lines of data. Common issues include using a cube view as a datasource that isn't zero suppressed and finding it takes forever to complete. Mainly used to manipulate and load data to a cube.

EPILOG
Same as prolog, runs once but right at the end of the TI. Common usage is to turn logging back on/off or run another TI after its complete.

Hope that all helps :D
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
lotsaram
MVP
Posts: 3647
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Variable not registering in Advanced tabs

Post by lotsaram »

Just to add to Steve's summary of tab function ...

PARAMETERS
- If run as part of a Chore parameters assume their default value
- Parameter values persist for the duration of the process can be reassigned on the Prolog (or any other tab)

PROLOG
- Data source can be dynamically assigned on the Prolog tab. You can also switch data source types (except to a View source from any of the other types, this is because NValue, SValue and value_is_string won't be set)
- Setting data source to NULL on the Prolog means that the Metadata and Data tabs won't execute
- Data changes can be made on the Prolog tab
- Take care when making metadata changes on the Prolog tab, there are some differences in how metadata changes are compiled between versions (i.e. at code execution vs. at the conclusion of the tab)

METADATA
- Metadata changes are saved at the conclusion of the Metadata tab (i.e. after the last source data record is processed)
- DO NOT make data changes on the Metadata tab (CellPutN, CellPutS, AttrPutN, AttrPutS)

DATA
- DO NOT make metadata changes on the Data tab (DimensionElementInsert, DimensionElementDelete, DimensionElementComponentAdd, DimensionElementComponentDelete, DimensionDeleteAllElements)

EPILOG
- Also commonly used to tidy up views and subsets assigned on the Prolog as data sources
- Useful to use to write to cubes to log process activity (number of records, process run time, number of errors, etc.)

Steve pointed this out already but something that is a trap for people learning TM1 especially when the accursed wizard is used to generate code is to remember that all code on the metadata and data tabs executes once for each and every record of the data source. The Metadata and Data tabs execute sequentially, that is having code on both tabs causes the data source to be read twice. If only one or the other tab is needed then there should be NO CODE on the other tab, otherwise the data source will be processed twice unnecessarily. If the data source is large then this can be hugely inefficient.
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: Variable not registering in Advanced tabs

Post by Steve Vincent »

lotsaram wrote:PROLOG
- Data source can be dynamically assigned on the Prolog tab. You can also switch data source types (except to a View source from any of the other types, this is because NValue, SValue and value_is_string won't be set)
Would you mind expanding on that? I recently got a need to have one TI to run after another and they use the same ASCII data source. I'd like the 2nd one to read the path the 1st one is using, is that what you are meaning and if so how?
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Variable not registering in Advanced tabs

Post by Steve Rowe »

Steve,
Not sure if this is what is meant but if you look in the help under Local TI Variables in the TI reference section you'll find a section on the variables that hold the names of the data sources.

Things like DatasourceCubeview, basically all the different options that can be set on the data source page of a TI.

I've only ever written to them but the help implies that you can read from them too, but does not explicitly say so.
Not sure if this is what you are after?
Technical Director
www.infocat.co.uk
User avatar
Alan Kirk
Site Admin
Posts: 6606
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: Variable not registering in Advanced tabs

Post by Alan Kirk »

Steve Rowe wrote:Steve,
Not sure if this is what is meant but if you look in the help under Local TI Variables in the TI reference section you'll find a section on the variables that hold the names of the data sources.

Things like DatasourceCubeview, basically all the different options that can be set on the data source page of a TI.

I've only ever written to them but the help implies that you can read from them too, but does not explicitly say so.
Not sure if this is what you are after?
First Steve,

You read from them just the same way as you do from any variable value; for example

Code: Select all

AsciiOutput( 'C:\Temp\AK.txt', DatasourceNameForServer);
will output the process data source's filepath to the specified file, but unfortunately this doesn't solve the other Steve's problem which is apparently reading one process' data source from another process.
Steve Vincent wrote:
lotsaram wrote:PROLOG
- Data source can be dynamically assigned on the Prolog tab. You can also switch data source types (except to a View source from any of the other types, this is because NValue, SValue and value_is_string won't be set)
Would you mind expanding on that? I recently got a need to have one TI to run after another and they use the same ASCII data source. I'd like the 2nd one to read the path the 1st one is using, is that what you are meaning and if so how?
Oher Steve,

I think Lotsaram is just referring to the fact that you can change a process' data source type (from say ASCII to ODBC, though I don't hold with doing that in practice), or the specifics of the data source (such as the name of the ASCII file based on, say, information which is read from a control cube, which is what we do a lot of in practice).

That would be your only option, I think. You can't read another process' data source directly but what you CAN do is use a CellPutS to send the DatasourceNameForServer out to a chore control cube, then have the next process do a CellGetS to read that value then assign it as its own DatasourceNameForServer value.
"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.
Post Reply