ChoreQuit Function

fs111

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?

ole2002ole

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 >.

yseen

dutchaussie wrote: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.

• 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.
  
• The Metadata tab code, if any, executes once for each row of data in your data source.
  
• The Data tab code, if any, executes once for each row of your data source.
  
• 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.

zrjz

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.

rampagews3

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;

tianya369

dutchaussie wrote:Hi Alan,

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

You're welcome.

dutchaussie wrote: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: If ( TimVl( now, 'H') > 4 );
    ChoreQuit;
EndIf;

hiba010203

dutchaussie wrote: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: l_DimNm = 1;
l_DimCnt = DimSiz ( 'Plant' );
While (  l_DimNm <= l_DimCnt );

    AsciiOutput ( 'C:TempLoop.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: # 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: # 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.

hnhening

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

kbswangyang

dutchaussie wrote: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.

xdd11

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.