BI PUBLISHER 12C – EVENT TRIGGERS
Event triggers in BI Publisher allow code to interact with the database at run-time. Typically, they are used to perform any required pre- or post-processing on the data, perhaps checking data is valid, confirming security privileges, setting languages, updating ‘printed’ flags in tables or may be just some logging of report activity.
Note that for this blog I’m only concentrating on Oracle database data sources – much of this won’t work on other data sources
There are five trigger events available to BI Publisher:
- Upon connection to the database
- Upon disconnection from the database
- Before data is extracted for the report
- After data is extracted for the report
- When the report is run via a scheduled report job
The first four of these are based on running a pl/sql function and that function can perform any SQL you want, the only stipulation is that it must return a Boolean value. If a ‘True’ value is returned BI Publisher will continue processing the request, a ‘False’ will halt processing, although the disconnect from database event will still run (assuming that’s not the event that failed).
The scheduled event trigger is a select statement that must return one or more records, a zero-record return will prevent the report from running.
BI Publisher is very strict on the syntax for all of this, so I’m going to cover an example of all five triggers in this blog. For the main connect/disconnect/before/after triggers I’m going to setup a simple logging system, so we get a log entry for each phase, plus an extra log entry on ‘after data’ trigger showing the number of records being extracted.
I’m using the OE (order entry) sample schema that comes with Oracle database. The database is version 12.1.0.2.0 and I’m using BI Publisher version 12.2.1.2.0
Let’s start by creating a table in the OE schema to hold the event log:
CREATE TABLE "PUBLISHER_EVENT_LOG" ( "EVENT_ID" RAW(16), "EVENT_DATE" TIMESTAMP (6), "EVENT_NAME" VARCHAR2(30 BYTE), "EVENT_DB_USER" VARCHAR2(30 BYTE), "EVENT_BIP_USER" VARCHAR2(20 BYTE), "EVENT_TYPE" VARCHAR2(20 BYTE), "EVENT_DETAIL" VARCHAR2(20 BYTE) );
With a trigger to populate the ID, Date and DB User fields automatically, so we don’t need to bother with these in our pl/sql later on:
CREATE OR REPLACE TRIGGER "PUBLISHER_EVENT_TR" before insert on "PUBLISHER_EVENT_LOG" for each row begin if inserting then if :NEW."EVENT_ID" is null then select sys_guid() into :NEW."EVENT_ID" from dual; end if; if :NEW."EVENT_DATE" is null then select sysdate into :NEW."EVENT_DATE" from dual; end if; if :NEW."EVENT_DB_USER" is null then select user into :NEW."EVENT_DB_USER" from dual; end if; end if; end; / ALTER TRIGGER "PUBLISHER_EVENT_TR" ENABLE;
I’m going to start with looking at logging the data connections. You can access the data sources screen from either Administration -> ‘Manage BI Publisher’ -> ‘JDBC Connection’ or using the ‘Manage Private Data Sources’ button in the Data Model editor.
As I’m adding this logging to a BI Publisher report I’d already created, the data source, named ‘OE’ in this case, already exists:
The bit we’re interested in is the Pre and Post Process function fields at the bottom of the screen:
All this requires is the name of a function to call with any specific parameters the function requires. The function can reside within a package, so I’m going to create a simple packaged function that takes the user-id and a string indicating whether it’s a pre or post process call.
Here’s my code:
create or replace PACKAGE BIP_LOGGING AS
FUNCTION BIP_CONNECTION_LOG (bip_user IN VARCHAR2, event_type IN VARCHAR2) RETURN BOOLEAN;
end BIP_LOGGING;
/
create or replace PACKAGE BODY BIP_LOGGING AS
p_orderid varchar2(10);
FUNCTION BIP_CONNECTION_LOG(bip_user IN VARCHAR2, event_type IN VARCHAR2)
RETURN BOOLEAN AS
BEGIN
INSERT INTO PUBLISHER_EVENT_LOG (event_name,event_bip_user,event_type) VALUES('Data Source Connection',bip_user, event_type);
RETURN true;
END BIP_CONNECTION_LOG;
end BIP_LOGGING;
/
So I have a package with a single function which takes two parameters. The most important point is that the function returns a Boolean value and since I’m only writing to a log, not performing any data operations, I can simply hard-code a ‘true’ value to return.
The function only contains a single insert statement to the publisher_event_log table created above.
In the data source screen I can now call this function for either the pre or post process, or in this case, for both. For the pre-process function, I’m going to make the following call:
BIP_LOGGING.BIP_CONNECTION_LOG(:xdo_user_name,Connect to DB)
And for the post process, this:
BIP_LOGGING.BIP_CONNECTION_LOG(:xdo_user_name,Disconnect from DB)
A couple of points here. For the first parameter, I’m passing the BI Publisher internal bind variable holding the user-id used to login to BI Publisher, xdo_user_name. There are several these bind variables – xdo_user_report_locale hold the users reporting locale. See the documentation for others.
For the second parameter, all I need is a simple string describing the event – Connect to DB and Disconnect from DB. Oddly you don’t need quote marks around string parameters in this call – if you do include them, the quote marks will appear in the table as part of the string value. I’ve no idea why…
Anyway just paste in the function calls into the pre and post process function fields and ‘apply’ the changes:
You can now either run the report or use the ‘View Data’ screen in the data model
Once the data appears you should see entries in the log table:
As my report takes just a fraction of a second to run the two entries have the same time but we can see that a user logged into BI Publisher and ran a report using this data source connection at a certain time.
As stated earlier, this is a simple example of basic logging, the function being called could have performed any pl/sql and you could have separate functions for both pre and post processes.
However, this level of logging can only tell us that a BI Publisher report ran using this data source. To see more detail of what actual report was being run need to look at the event triggers within the report, or to be more accurate, within the data model the report uses.
Before looking at the data model event triggers screen, I’m going to expand on my package created earlier and add in two new functions. The first will run before the data is extracted and will log the report being run and the run-time parameter for the report. The second will also log the report name and parameter, but will also record the number of records returned for the report.
So, my new package is:
create or replace PACKAGE BIP_LOGGING AS
p_orderid varchar2(10);
FUNCTION BIP_CONNECTION_LOG (bip_user IN VARCHAR2, event_type IN VARCHAR2) RETURN BOOLEAN;
FUNCTION BIP_BEFORE_EVENT_LOG (event_name in VARCHAR2,bip_user IN VARCHAR2, event_type IN VARCHAR2, p_orderid IN VARCHAR2) RETURN BOOLEAN;
FUNCTION BIP_AFTER_EVENT_LOG (event_name in VARCHAR2,bip_user IN VARCHAR2, event_type IN VARCHAR2, p_orderid IN VARCHAR2) RETURN BOOLEAN;
end BIP_LOGGING;
/
create or replace PACKAGE BODY BIP_LOGGING AS
p_orderid varchar2(10);
FUNCTION BIP_CONNECTION_LOG(bip_user IN VARCHAR2, event_type IN VARCHAR2)
RETURN BOOLEAN AS
BEGIN
INSERT INTO PUBLISHER_EVENT_LOG (event_name,event_bip_user,event_type) VALUES('Data Source Connection',bip_user, event_type);
RETURN true;
END BIP_CONNECTION_LOG;
FUNCTION BIP_BEFORE_EVENT_LOG(event_name in VARCHAR2,bip_user IN VARCHAR2, event_type IN VARCHAR2, p_orderid IN VARCHAR2)
RETURN BOOLEAN AS
BEGIN
INSERT INTO PUBLISHER_EVENT_LOG (event_name,event_bip_user,event_type,event_detail) VALUES(event_name,bip_user, event_type, p_orderid);
RETURN true;
END BIP_BEFORE_EVENT_LOG;
FUNCTION BIP_AFTER_EVENT_LOG(event_name in VARCHAR2,bip_user IN VARCHAR2, event_type IN VARCHAR2, p_orderid IN VARCHAR2)
RETURN BOOLEAN AS
p_recordcount int;
BEGIN
INSERT INTO PUBLISHER_EVENT_LOG (event_name,event_bip_user,event_type,event_detail) VALUES(event_name,bip_user, event_type, p_orderid);
select count(*) into p_recordcount from orders where ORDER_ID = p_orderid;
INSERT INTO PUBLISHER_EVENT_LOG (event_name,event_bip_user,event_type,event_detail) VALUES(event_name,bip_user, 'Record Count', p_recordcount);
RETURN true;
END BIP_AFTER_EVENT_LOG;
end BIP_LOGGING;
/
I now have two additional functions: BIP_BEFORE_EVENT_LOG and BIP_AFTER_EVENT_LOG.
BIP_BEFORE_EVENT_LOG is almost identical to the BIP_CONNECTION_LOG function used earlier, it simply writes a single record to my log table, but this time is picking up additional parameters from BI Publisher: Event Name (Report name), Event Type (‘Before Event’) and the parameter required by the report – in this case the Order ID. It also returns a Boolean value, hardcoded to ‘true’ in this simple example.
BIP_AFTER_EVENT_LOG does the same, but then performs a lookup on the ORDERS table to count the number of records for the given Order ID parameter and writes this as a second log entry.
The number of records for a report should be one (the Orders table contains one record per order), but if a user enters a non-existent order ID I’ll see this logged with a record count of 0. Not the most in-depth bit of logging I’ve done, but demonstrates the point…
There are two important point to note here. First with the run-time parameter supplied by BI Publisher. In my report the parameter is called p_orderid. In order for this to get transferred to the function it must also be defined as a global variable in the package and package body – this is p_orderid varchar2(10); in the above code.
The second is that functions used by BI Publisher event triggers must be packaged together into a single package. You cannot call stand-alone functions, you cannot call functions in different packages. You also cannot call procedures, they must be functions which return a Boolean value.
Now we have the packaged functions, let look at placing them in the data model.
The first step is to go to the Properties screen and enter the name of the pl/sql package in the Oracle DB Default Package field. This is the reason why the functions must be packaged together – only functions in the package specified in this field can be called by the event triggers.
Now we can go the Event Triggers screen:
Initially this will be empty, so click on the icon beneath the Event Triggers title. This will add a trigger and open up the bottom half of the screen displaying the functions within the package and the parameters in the data model. The default type of a new trigger is ‘Before Data’, so we can leave be in this case.
This first trigger will call the Before Event log function. So first give the trigger a better name, then click on the required function and click the > icon to select it.
It’s inserts the function call into the Event Trigger window and then lists all of the functions together with their data type:
BIP_LOGGING.BIP_BEFORE_EVENT_LOG(EVENT_NAME:VARCHAR2 , BIP_USER:VARCHAR2 , P_ORDERID:VARCHAR2 , EVENT_TYPE:VARCHAR2)
Beware! I’ll forgive you for not immediately spotting this (but BI Publisher won’t!) The order of the parameters is not necessarily in the same order as the function! The function created above has the parameters in the order event_name, bip_user, event_type, p_orderid, for some reason BI Publisher has swapped round the last two. This may not always happen, but just be wary of it…you need to ignore the BI Publisher order and switch the parameters into the correct order of the function.
So we now replace each parameter/data type combo with our required parameters:
EVENT_NAME:VARCHAR2 I’m replacing with the string ‘Order Confirmation Report’. Note that unlike the data source triggers, you do need quote marks around strings here.
BIP_USER:VARCHAR2. This will be the BI Publisher user-id, so I’ll use the internal bind variable :xdo_user_name
EVENT_TYPE:VARCHAR2. This is another hardcoded string reference, this time ‘BeforeData’
P_ORDERID:VARCHAR2. This is the report parameter so we can use this as a bind variable :p_orderid
So completed function call is:
BIP_LOGGING.BIP_BEFORE_EVENT_LOG(‘Order Confirmation Report’,:xdo_user_name, ‘BeforeData’ ,:p_orderid)
Next we add a second trigger, call is AfterEvent Trigger and this time set the Type to ‘After Data’. Then select the BIP_AFTER_EVENT_LOG function and set the trigger code as:
BIP_LOGGING.BIP_AFTER_EVENT_LOG(‘Order Confirmation Report’,:xdo_user_name, ‘AfterData’ ,:p_orderid)
This is almost identical to the Before Event function call, but with the event type set to ‘AfterData’
Note, this time when I initially selected the function it put the parameters in the order BIP_USER, EVENT_TYPE, EVENT_NAME, P_ORDERID, so again ignore the order and revert the parameters back to the order of the function.
So now we have our two event triggers setup:
Time to run the report again – or just use the ‘view data’ option in the data model. Then look at the log table:
So we now see five entries for a single run of the report:
- The initial connection to the database
- The BeforeEvent log highlighting the order ID in the event_detail field
- The AfterEvent log also highlighting the order ID
- The second AfterEvent log showing 1 record returned
- The disconnection from the database.
Running the report for a non-existent Order ID gives us a zero on the record count:
Running the report for an invalid Order ID (I entered a varchar value when a number is expected) also gives us:
So we have the basics of a logging system tracking who runs what in our BI Publisher report.
The last type of Event Trigger I said I’ve cover was a Schedule trigger. This is a much simpler type of trigger and can’t be used for logging directly – although the before and after event triggers above will still run when a report is scheduled.
In this case the trigger is used to determine whether the scheduled report should actually be run or not and requires a select statement that returns one or more rows to allow the report to run. If no rows are returned the job is cancelled.
So in the example below I’ve used the select statement:
select ‘RecordsFound’ from orders where order_id = :p_orderid
that returns a single record with the word ‘RecordsFound’ should the where clause, which includes the p_orderid parameter, find a record in the Order table:
As already mentioned the Before and After event triggers will run when the report is scheduled (assuming the schedule trigger is successful) and the pre and post process functions on the data source will also fire. So, Schedule reports are effectively already being logged by my logging functions, the schedule trigger is just there to decide if the report should be run or not.
That’s it! a fairly simple example of how triggers can be used in BI Publisher reports to interact with the database before and after a report is run.
No Comments