Please Don't Run on Holidays
By Tim Dexter-Oracle on Feb 18, 2010
This post is for the standalone users out there, I'll explain why in a moment. I was with a customer in sunny, warm Nebraska last week, OK, OK it was sunny but 'warm' is a little of an exaggeration. It was freezing, somewhere around 3F without the windchill. As I drove into Omaha, I was wondering why it seemed deserted; upon opening the car door, it became all too clear, where was my pullover. I should have realized when picking up the car but that was under cover and they had pre-heated the car.
The customer was looking for a way to stop BIP's scheduling engine sending out report outputs on a public holiday, ideally stopping the job altogether and then re-scheduling it for the next non-holiday day. I was with a colleague, Joel from really sunny and really warm Texas. Putting our heads together we thought about using the scheduler in OBIEE, Delivers, to drive the process. For the uninitiated, Delivers, has a really neat feature. You can create a trigger request that gets executed first; if it returns a row then the main request is run. You can not directly run a BIP report from Delivers but there are ways to call the BIP scheduler using a java class and BIP's web services. Our plan was to set up the trigger request to check the current date against a table of excluded dates ie holidays. If there was a match then dont return a row and this would stop the whole process. If there was not a match then force a row to be returned and allow the process to continue. To get the BIP report to run we would employ the Advanced tab on the iBot definition. Here you can define a java class that can call the BIP web services.
When I got home to 32F Colorado (toasty!), I made a start on looking at the issue. I hit a few barriers with the Delivers/Web Services approach which I'll cover in another post. Not insurmountable, but quite a complex solution for the customer to implement. I took another look at the problem from the BIP side. BIP does not currently have a means of stopping a job run based using a trigger , would be a great feature. It also, does not support custom calendars, at least not yet. So, I have no way of stopping the job but I thought of a way to at least stop the delivery of the report.
The bursting engine is normally used to split a set of batch data, then format and deliver the reports. With a little abuse we can use it to stop delivering its payload based on a single report data set rather than a batch of data. Create the report as you would normally, queries, parameters, templates, etc. You'll need to add an element in the top of the tree to act as a KEY column for the bursting query. Something like:
Then add a bursting definition, you'll have to set a split and deliver by column but for this purpose we can just use BURST_KEY element in our data set ie we are not going to split it. Now comes the tricksy piece. Asume you have created a table in the db to hold the holiday dates that you want to avoid . you can then construct a CASE statement in the burtsing select statement to check the current date i.e. the date the report is running against the table of excluded dates.
case when (select count(1) from no_report_date e
where to_char(e.excluded_date,'YYYYMMDD') - to_char(sysdate,'YYYYMMDD') = 0)=0 then 'XXXX' else 'YYYY' end KEY,
'SUBJECT: Enterprise Bursting Test' PARAMETER4,
'BODY: Test Report Body' PARAMETER5,
Rather than trying to match the dates I actually did a diff on them and tested for a zero value. If the 0 came back ie a match then it returns an invalid KEY value, then the busrting engine will not find a KEY match and will not deliver the document. The rownum statement ensures we only get a single row returned.
This approach has a some drawbacks:
- The report will effectively error out in the scheduler. Becasue the delivery will fail the scheduler will report the whole job as failing. There is a way around this, you can move the CASE statement to the delivery column. In my case the email address. If the we get a KEY match we return the correct email, printer, etc; if not, then we get return a dummy value that will not work. the report will report as completing successfully.
- When the user schedules the report they can not enter the delivery options for the report. As soon as the bursting option is selected the template, output and delivery options are hidden. therefore you will need to either hard code the values or retrieve them from the database in the bursting control query. Not so tough but still a drawback.
If the report should not be delivered today then what should happen? For an answer to that, we get in to the gray and murky area of updating the scheduler tables and we are going way off the reservation ... I'll save that for next time.