Scheduling jobs based on trigger events
March 9, 2015 by: David LaiBeing able to schedule a job based on a trigger event is a very important feature in SAP BusinessObjects.
For example, let’s say we have a report that is refreshed daily based on data updated by a daily ETL process. We only want the report to be refreshed if the ETL process completes successfully.
There are 2 ways of creating a trigger event. You can either create a file trigger, where the ETL process drops a file into a network directory and the event trigger will sense that a file has been dropped. Or you can create a report trigger report.
I like using the latter method as you do not need to worry about setting any extra network directory settings. For those of you working in large institutions, I’m sure you will agree on the amount of effort and time it takes to get any sort of extra network access approved.
Outlined below is the steps on creating a report based trigger that will check if an ETL process has completed successfully, then give the dependent scheduled job the go ahead to refresh the report.
Create trigger report
The trigger report is a scheduled report that will succeed only if the daily ETL process executed successfully.
Step 1
Create a table in the database that contains an ETL check flag. This flag will have a value of 1 if the ETL process was successful and 0 if the ETL process failed.
Step 2
Create an ETL check object in the universe
The object will contain a case statement that will either pass or fail the trigger report. The trick here is that the fail case will contain a 1/0 path which causes an error on the query execution, thus failing the report when the report is refreshed.
Make sure you do not press the Validate button as it will give you an error since the 1/0 is technically invalid.
Step 3
Create a trigger report using Web Intelligence that will be based on the ETL check object
The result will look like this
Step 4
Now test to make the report trigger works. You will need to schedule the report 2 times. Once when the ETL flag is good and once when it is bad.
When the ETL flag is bad we will see a fail on the schedule history, otherwise we will see success.
Create a trigger event in CMC
The trigger event is used to tell the scheduled report that it is okay to execute.
Step 1
In the Central Management Console (CMC), go to Events
Step 2
In System Events or a sub folder of System Events, create a new Event
Step 3
Select Schedule as the Type, and give it the appropriate name and description. Set the Result as Success because we only want the event to trigger when the trigger report is successful. You can either leave the Alerting Enabled checked or unchecked, depending if you want to be notified each time the trigger report was executed successfully.
Note that you’ll need to subscribe to the Alert in order to see the alert each day.
The new trigger event should look like the following
Create a scheduled job that will trigger the event
We’ll need to create a scheduled job on the report that will trigger the event when the data threshold has been met.
It is very important that the trigger executes after the dependent report. The reason being that the dependent report will scan for the trigger event and will not execute until it sees the trigger event. The dependent report does not take into account triggers that happen before the dependent report job starts.
For example if we schedule the dependent report job to run at 9am. Any trigger events that happen before 9am will be ignored. It only sees event triggers after 9am.
Step 1
Create a scheduled job for the report trigger. If the dependent report job runs daily at 6am, then we would set the recurrence to daily at any time later than 6am, but something close to 6am like 6:05am.
If we have multiple jobs that depend on the trigger, we can set the report trigger to run at different times of the day (ie: every 2 hours).
In our example we will only be using 1 dependent report, so we will schedule the report trigger once a day at 6:05am.
Step 2
On the Events section, set the Report Scheduler Trigger to execute after completion of the report trigger report. If the report fails (because ETL process failed), nothing will happen because the trigger only executes on success. If the report succeeds (because of ETL process passed), the trigger will execute.
Schedule the trigger dependent report
We can now schedule the trigger dependent report, which will only execute when it sees the trigger.
Step 1
Create a new scheduled job for the trigger dependent report
Step 2
Set the recurrence to whenever you want it run. In our example we are scheduling our job to run every day at 6am.
Step 3
On the Events section, we need to set the Events to wait for to the Report Scheduler Trigger. This means that the report will not refresh until it sees the trigger.
Step 4
Test to make sure everything works!
Okay so let’s say it’s 6am and the scheduled job starts. It will remain at the status Pending until it sees the trigger.
Now let’s say the report trigger failed, then the job will not execute and remain at status Pending.
Now let’s say the report trigger can execute again 1 hour later and is successful, then the trigger will execute and the trigger dependent report will execute.
Hi David,as for the ETL flag table on database, is this one as a standalone table only store one column called ETL flag, the value 1 or 0 is filled based whether the overall ETL job fails or success?
Hi Hong,
Sure that would work
Thank you very much for the post. I was looking for schedule-based event solutions and your post comes out in time.
Wondering if you could let me know the CASE statement used in the report. The image is too small to catch.
Thank you Dave in advance,
Wendy
Hi Wendy,
If you click on the image it will zoom out and you will be able to see the pseudocode for the case statement.
Hope that helps!
David,
Question: Is it necessary to create flag object on DB side?
My scenario: I do not have access to ETL side or Write permission on DB side
So I created a universe object based on a field “Load Date” from the table that usually have Today date.
Then i create another object to find the difference between a Load Date and today date.
Case when Today-Load Date = 0 then 1 else 1/0
Even though the case statement should be returning 1, and not 1/0 but my report fails to execute.
I can not even create a report.
As soon as i run the query in webi (to create report) its just erroring out “division by zero not possible”. Remember Right now it should give me value “1”.
Hi Danish,
I use the 1/0 case statement to force a failure report when required.
It seems like your case statement is going to the “else” section. Please check your query again. Instead of doing a 1/0 you can try 0 to begin with. I’m sure that’s the issue.
Thanks
David
I resolved the issue for now, and test the trigger report by scheduling it twice.
What i did:
I created two(2) Objects in the Universe.
Object 1-Difference : CASE WHEN DayOfMonth(Max(curDate()))
–
DayOfMonth(Max(“db_load_date”)) = 1 THEN 1 ELSE 0 END
Object 2- Trigger:
Case When @Select(MAKI\Report Scedule Trigger\Difference) = 1 then 1 else 2 /@Select(MAKI\Report Scedule Trigger\Difference) end
in essence, i did not use 1/0 but 2/Objectresult(difference)
Thanks
Hi Danish,
I would recommend the case statement because it is easier to maintain. Again please check your query first by using 0 on the “else” clause.
Although your method works, it seems more complicated than a simple case statement that goes to 1/0 when you want a failure case.
Hope that helps.
Thanks
David
Hi David,
I tried create a variable and has the if else statement, but the report will still run successfully even with 1/0, the report jus shows #DIV/0.
Please advise.
Thanks
Yang
You have to do it at the database level.
If you create a SQL statement with 1/0 it will throw an error
Hope that helps!
Hi David,
When universe is created on bex query Process chain status, will this flag work?
The case statement is not working when tried with status = G then 1 else 1/0.
any help?
Thanks
For now, the publication is working as required. Got busy with other stuff ( object level security- Find reports that are based on specific object)
Will definitely look back into this, since the customer is looking to burst the report to various group.
Thanks for your help though.
Your blog was a relief. Keeping writing the good stuff.
Is it possible to get this done without using Web Intelligence, and only through CMC? We use Crystal Reports, but do not have license for Web Intelligence.
Hi JD,
I’m a little confused, I thought all SAP BusinessObjects licenses should include Web Intelligence. If you only have a Crystal Reports license then you won’t be able to enjoy all the features SAP BusinessObjects offers.
Hi David,
You seem very knowledgeable and I was wondering if you might be familiar with an issue we are having. We currently have something similar to what you have described set up — once our daily ETL data load completes, it triggers a webi publication to run. This part works fine. What is not working is the backup process we have set up in the case of a publication failure (which we get periodically seemingly due to random glitches).
We have two instances of the publication created. The first is as I described — it is recurring and gets triggered when our data load completes every day. If this triggered instance fails, it is set to trigger a rerun event. The second publication instance is a duplicate of the first – recurrence timing etc is all the same – EXCEPT that it runs only when the rerun event is triggered by a failure of the first instance. This process does not seem to be working for us and we are having a difficult time trouble shooting it.
In test, it seemed to work fine though we were failing our first instance every day. We were hypothesizing that since the second instance of the publication only triggers if there is a failure, maybe there is a problem with it resetting itself for the next day if it does not get triggered. We are unable to see it sitting in Pending status so it’s hard to be sure.
Hopefully this all makes sense — your insight is appreciated!
Hi Karin,
Have you tried using the option retry on failure after X amount of time.
It’s one of the scheduling options. If that works it would save you the hassle of the backup publication.
Hi,
Scenario – Report “Daily Sales Report” scheduled via CMC with retries , success notification and failure notification.
I don’t have access to CMC and i’m required to change the instance name. If i change the instance name by using “Reschedule –> Replace existing Schedule” option in Infoview – will the retries and notification setting be still present for this report instance ?
Any guidance here will be highly helpful.
Thanks in Advance
Hi Ajid,
Yes it will keep the existing number of retries and notification settings when you reschedule an existing schedule.
Hope that helps!
David
You will need to create 29 events to have them all scheduled sequentially one by one.
Hope that helps!
Hi David,
I have the below case statement when the job runs successfully it puts 0 on the log table then if the job failed the schedule log table will be blank.
My below statement keeps running even if the the etl job failed and the log table is empty
select
(
case
when SQL_ERR_CODE_END = 0 then 1
else 1/0
end
) AS etl_output
from dataware.sched_log
where group_name = ‘VOC’
and job_name = ‘VOC_CRE_EXPECTATION_SUMMARY’
and (end_time >= trunc(sysdate) and end_time < sysdate)
Great job.
Does anyone know if there is a way to see what events a scheduled report will trigger?
If I select Instance Details for a scheduled report, it will show me Waiting for event(s), but I cannot fins anywhere that lists event(s) triggered by the report. I use Instance Details to confirm that my scheduled reports are configured properly, but cannot see any information concerning events that will be triggered/fired by a report.
An easy way to do that is to go to the recurring instance on the history, then select reschedule.
From there you can see if it is triggering any events
Hi David,
Can i use the above created event based on webi repot to schedule a deski report
Yes you can.
But why is your organization still using DESKI?
DESKI has been phased out a long time ago.
Hello David,
My case statement return 1 when I replace 1/0 by 0. But if I am using 1/0 in else part then it does not work at all.
CASE WHEN “FLAG – 1 Million”.”FLAG” = 1 THEN 1 ELSE 1/0 END —- Does not work. Throws error.
But
CASE WHEN “FLAG – 1 Million”.”FLAG” = 1 THEN 1 ELSE 0 END — Does work and gives 1 as output.
Concern is I wan to use 1/0 so that trigger should not initiate but I am not able to use 1/0 the way you explained above.
Hi,
This is a SQL syntax issue. Please google on the correct SQL syntax to solve your issue.
Regards
David
Hello David,
Is it possible to do the scheduling if I create the flag object Inside the report as report variable based on loading date of process chain with today date?
We have lots of reports to Schedule for different domain.
We have one summary report from where ,get the information about the loading completion date.
So if we create the report variable on that summary report then how we can link the reports for eachj domain.
Waiting to get response from you to resolve it.
Thanks in advance.
Regards,
Anu
Hi Anu,
Sorry that is not doable
Hi David,
In my IDT layer, even when the condition is not met, the report runs with no error.
I’m running the same case statement as you provided. What reason could be causing the failure to not occur?
Kind Regards,
Johnathan
Hi Johnathan,
Your case statement needs to cause the SQL query to generate an error.
As long as that happens the report will fail, thus either meeting/not meeting the trigger criteria