ChoreQuit Function

[dutchaussie]

Hi,

I am trying to put some kind of 'safety' code into one of our overnight chores as it tends to hang and this affects other chores that are scheduled to run after that.
I am looking into as to why this particular chores does this but until then I want to kill the chorse if it hasn't finished running by 04.00 AM.

Now I am sure you will find this easy but somehow I can't get it to work. I haven't used the time/date function yet in TI and it just won't do what I want.

Idea is simple, in the prolog (I assume the code goes in there) I added this:

if(TIME>'04:00');
ChoreQuit;
ENDIF;

But apparently the if statement is not correct. What am I doing wrong?

[tomok]

The TIME function returns a string. You are doing a numerical compare aainst a string which is not allowed. You need to put an @ before the >.

[Alan Kirk]

Hi,

I am trying to put some kind of 'safety' code into one of our overnight chores as it tends to hang and this affects other chores that are scheduled to run after that.
I am looking into as to why this particular chores does this but until then I want to kill the chorse if it hasn't finished running by 04.00 AM.

Now I am sure you will find this easy but somehow I can't get it to work. I haven't used the time/date function yet in TI and it just won't do what I want.

Idea is simple, in the prolog (I assume the code goes in there) I added this:

if(TIME>'04:00');
ChoreQuit;
ENDIF;

But apparently the if statement is not correct. What am I doing wrong?

Leaving aside the fact that you're trying to use a greater than comparison operator against a string (on the subject of time formats and time values, see this thread), think about how a process is executed.

1. The prolog tab code executes once, when the process first triggers. Therefore the test that you're doing will occur just the once, in the first moment that the process runs. Even leaving aside the issue of trying to do a datetime comparison against a string, the process would have to start after 4am for the test to be true, not be running after 4am having started before that time.
2. The Metadata tab code, if any, executes once for each row of data in your data source.
3. The Data tab code, if any, executes once for each row of your data source.
4. The Epilog code executes once, after the last row of data has been dealt with, immediately prior to the end of the process.

It therefore follows that if you want the chore to quit part way through if it's still running at a specific time, you would need to do the test in either the Metadata or Data tab or both (as the case may be), and even that presupposes that the "hang" that you refer to is not the result of an infinite loop somewhere inside one of those tabs (or possibly some failure at the data source end which prevents the feed of the next record) which also prevents the test from ever being executed.

[dutchaussie]

Hi Alan,

Great feedback, thanks! And great thread about Time/Date as well.

Did I mention I am new to TI processes.....

I obviously am and just trying to get my head around it. Have to ignore what it is in Excel and read the Reference Guide better!

OK...I understand the prolog vs. metadata/data tab issue and also that when it hangs without going through the code it doesnt actually read the ChoreQuit bit so nothing happens. Only 1 way to find out and that is to run it overnight.

So if I understand the Time/Date thread correctly I need to do something like this (if 0.25 is 3AM I want 0.15):

If(TIME > 0.15);
ChoreQuit;
ENDIF;

It is not date driven, just based on time.

Does this make sense to you?

Appreciate your help on this.

[dutchaussie]

Alan,

I think you are right, I do have an infinite loop in there.

This is what I have:

# Plant
sPlant = NumberToString(vPlant_No);

# skip missing Plants
IF(DIMIX('Plant,sPlant)=0);
#ItemSkip;
ENDIF;

It seems I forgot the 'then' part of my if-statement as the itemskip is commented out?!

Does this look more like it?

# skip missing Plants
IF(DIMIX('Plant,sPlant)=0);
ItemReject('Plant not found: ' |sPlant);
ENDIF;

[Alan Kirk]

Hi Alan,

Great feedback, thanks! And great thread about Time/Date as well.

You're welcome.

Did I mention I am new to TI processes.....

I obviously am and just trying to get my head around it. Have to ignore what it is in Excel and read the Reference Guide better!

OK...I understand the prolog vs. metadata/data tab issue and also that when it hangs without going through the code it doesnt actually read the ChoreQuit bit so nothing happens. Only 1 way to find out and that is to run it overnight.

So if I understand the Time/Date thread correctly I need to do something like this (if 0.25 is 3AM I want 0.15):

If(TIME > 0.15);
ChoreQuit;
ENDIF;

It is not date driven, just based on time.

No, the Time function still returns a string, and you're still comparing it to a numeric value. (Also 4am is actually 0.1666666666666667, not 0.15 which is 03:36 am. 4am would be 4/24 since 24 (hours) represents a value of 1 day. Each hour is therefore 1/24th of a full day and 4am is therefore 4* (1/24) .)

You can in fact get away with doing a string comparison (up to a point) if you use the correct comparison operator, which Tomok mentioned earlier. But since dates and times are still intrinsically values, not text, I regard it as an undesirable practice. My approach would be to use the TimVl function like so:

Code: Select all

If ( TimVl( now, 'H') > 4 );
    ChoreQuit;
EndIf;

[Alan Kirk]

Alan,

I think you are right, I do have an infinite loop in there.

This is what I have:

# Plant
sPlant = NumberToString(vPlant_No);

# skip missing Plants
IF(DIMIX('Plant,sPlant)=0);
#ItemSkip;
ENDIF;

It seems I forgot the 'then' part of my if-statement as the itemskip is commented out?!

Does this look more like it?

# skip missing Plants
IF(DIMIX('Plant,sPlant)=0);
ItemReject('Plant not found: ' |sPlant);
ENDIF;

That's not a loop, it's a conditional block. The While function is used in a loop. This would be an infinite loop:

Code: Select all

l_DimNm = 1;
l_DimCnt = DimSiz ( 'Plant' );
While (  l_DimNm <= l_DimCnt );

    AsciiOutput ( 'C:\Temp\Loop.txt', DimNm ( 'Plant',  l_DimNm ) );

End

That's because l_DimNm is not incremented inside the loop, and will therefore equal 1 each and every time. It will simply write the name of the first element again, and again, and again, and again until you have to use TM1 Top to kill the process because it's hanging your server.

A conditional block does something quite different; it executes a block of code inside itself, but it does that only once. (If the block is inside your Metadata or Data tabs it will do it once for each record in your data source, but the data source is the loop, not the code.)

All that would happen there is that as each record is processed the block of code will test the DimIx function to tell whether the element is in the dimension. The first one:

Code: Select all

# skip missing Plants
IF(DIMIX('Plant,sPlant)=0);
   #ItemSkip;
ENDIF;

Will do absolutely nothing because there's no live code inside the If block. It will do the test for each record in your data source, then just continue on with whatever code follows it.

If you uncomment the ItemSkip statement, it will do the test, and if the element isn't in the dimension it will skip any remaining code on the Data or Metadata tab for that data source record only, then go on to the next record from the data source and start the code again from the beginning with that one.

The second one:

Code: Select all

# skip missing Plants
IF(DIMIX('Plant,sPlant)=0);
    ItemReject('Plant not found: ' |sPlant);
ENDIF;

is essentially the same as the ItemSkip, except that in addition to skipping all of the code on the tab and moving on to the next record in your data source it will also write an error message out to the process' error log.

[dutchaussie]

Hi Alan,

Thanks again for your words of wisdom!

The ChoreQuit does work now, so thanks. And I understand what you mean with itemreject and itemskip ( I guess I was just hoping this would be the issue....)

However, I don't think it is going to help me much as it seems there is something fundamentally wrong with the process.
When I run the process for one plant, it works fine. When I run it for two plants (based on 'OR' code SQL) it works fine.

But when I take the 'WHERE' condition off and try to run it for all plants, it #$%@& itself.
The TM1 server becomes unresponsive and we have to reboot which is quite annoying for all users.

It might be something with the SQL statement, however I can't see anything wrong with it. And the fact it does work for one and two plants tells me the SQL is fine.
I have run massive load processes before (couple of financial years of sales by product!) and I haven't had an issues with an unresponsive server before, it just takes a long time to load the cube.

This may be a new entirely new topic but maybe you can shine some light on this?

Cheers

[Alan Kirk]

However, I don't think it is going to help me much as it seems there is something fundamentally wrong with the process.
When I run the process for one plant, it works fine. When I run it for two plants (based on 'OR' code SQL) it works fine.

But when I take the 'WHERE' condition off and try to run it for all plants, it #$%@& itself.
The TM1 server becomes unresponsive and we have to reboot which is quite annoying for all users.

It might be something with the SQL statement, however I can't see anything wrong with it. And the fact it does work for one and two plants tells me the SQL is fine.

The syntax of the SQL may be fine, but it really does sound like what's happening is that without a Where clause your database server is getting overwhelmed by the query, not serving up any data, and that's why your TM1 server is hanging; not because TM1 has a problem with the query, just because it's waiting forever.

I'd suggest trying the SQL out in the database's native browser; Management Studio / Query Analyzer if it's SQL Server... can't remember what Oracle's one is since I used to use TOAD anyway... scroll of papyrus if it's DB2... anyway, run the query in the relevant environment and see how long it takes to return any records. If you can do that from the server box that TM1 is running on so that you're seeing things as the TM1 server should see them, so much the better. You may need to look to see whether perhaps there's some kind of outer join in the query or the view that it's based on (if it is based on a view) which is capable of handling it if there's a limit of one or two plant rows but not if it's every row in the table.

[dutchaussie]

Hi Alan,

Thanks again for your quick response, I really appreciate you taking the time to try to help me (and possibly others)!

I thought of that and I did chuck the SQL in SQL server.
The result: returning lines after 9 seconds and completely run after 30 seconds. Now I am not a SQL expert by all means (the contrary really, I have been using it for 2 months now) but to me sounds reasonable.

This is the SQL:

select RECEIPT_LINES.plant,
RECEIPT_LINES.product,
RECEIPT_LINES.qty,
RECEIPT_LINES.rrp,
RECEIPT_HDR.receipt_date,
RECEIPT_LINES.discount,
RECEIPT_LINES.po_number
FROM RECEIPT_LINES
INNER JOIN RECEIPT_HDR ON RECEIPT_HDR.receipt_no =
RECEIPT_LINES.receipt_no

WHERE RECEIPT_HDR.receipt_date between 'start date' And 'end date'
Not very fancy coding I know but it does the job!
Well kinda....not so much in TM1 at the moment unfortunately.
What I might do is instead of joining the header and lines table to get receipt_date, I'll try item_receipt_date as opposed to order_receipt date.
This means I don't need a join which might help.
It wont give me exactly what I am after because of the possible difference in dates but hey, its better than not having anything in TM1.

I will let you know how that goes!

Thanks again, love your work.

[tomok]

I thought of that and I did chuck the SQL in SQL server.
The result: returning lines after 9 seconds and completely run after 30 seconds. Now I am not a SQL expert by all means (the contrary really, I have been using it for 2 months now) but to me sounds reasonable.

If you are using Oracle as the source for your TI process you may need to modify some settings on your TM1 server. Check out this link http://forums.olapforums.com/viewtopic.php?f=3&t=3557, especially the post about the SQLFetchScroll setting.

[dutchaussie]

Hi Tomok,

Thank you for your reply.
As mentioned in the post before we use SQL server as far as I understand that topic you were referring to is all about Oracle?

[tomok]

As mentioned in the post before we use SQL server as far as I understand that topic you were referring to is all about Oracle?

That setting isn't just for Oracle, it;s just that Oracle is the only back-end database I've ever had to use it for. If you are having a problem with an SQL query timing out on your server that doesn't time out when you run it with another client front-end then it is probably worth a try. If it doesn't work you can always just revert back to the original setting.

[Steve Rowe]

Not sure if you had any luck figuring out what is killing your TI routine.

Some things you can try.
Look in the message log and see if it's complaining in there. (right click on your server name in the server explorer). Post anything you think might be related.
The ODBC connection has a logging facility, try turning that on and see if you get anything useful back.
Put some ASCIIOutput statements in your TI script so you can see how far the TI get's before it freezes.
Check the message logging on the SQL side, you'll probably need the SQL DBA for this.
Check that there's no activty on the SQL side that could block or put a lock on the table your reading from.
More of a tip this one, make sure your ODBC connection is using a dedicated log in. I've had problems in the past with a shared account where other applications or users are using the same log-in as my TIs, if they put the password in wrong a few times, that locks the account which breaks your ODBC, cue much head scartching.

Might be worth posting all of your TI script maybe someone will spot an issue.

That's all I have I think, HTH

Cheers,

[dutchaussie]

Hi Steve,

Thanks for your suggestions.
I found out what is causing it, well almost. I def know it is something with the SQL. As far as I am concerned it is a very basic SQL statement with one left join. It works fine in SQL server but TM1 doesnt like it at all!

Now the hard part is why TM1 doesnt like this particular SQL statement and that will take a bit more investigation from my end I guess. I have a workaround for now but it seems odd to me TM1 cannot handle this kind of queries.

I will take your suggestions on board and see what I can do.

Thanks again.

Cheers Niels

[tomok]

If you can, remove the JOIN from your SQL by building a view that does all that lifting for you. Then all you have to do is SELECT ALL FROM YourView. I have had the same issue happen to me before so I just baked it into a view. Problem solved.

[dutchaussie]

Hi Tomok,

Thanks for that. I did get the same advise from someone else.
Another option I have heard is to index the actual column that joins the two tables.

Will try both and see which one works best.

Cheers NIels