SATYA RANKI REDDY OBIEE SOLUTIONS

BI Publisher Bursting Configuration on OBIEE 11g 


This article gives a complete example of BI Publisher Bursting using Oracle BI Publisher which comes bundled with OBIEE 11g (11.1.1.5.0).
Create a schema/user called bischeduler on the database.



CREATE USER bischeduler IDENTIFIED BY bischeduler DEFAULT TABLESPACE USERS;
GRANT CONNECT, RESOURCE, CREATE DATABASE LINK, CREATE MATERIALIZED VIEW, CREATE PROCEDURE,
CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM,



CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, CREATE SESSION To bischeduler;

Scheduler Configuration

After you login to the BI publisher, go to Administration (right-hand top). Under System Maintenance, click Scheduler Configuration.

Give connection details of the database and user you just created and do “Test Connection” and if it succeeds, do “Install Schema”. This will create the necessary database object in the schema needed by Quartz Scheduler used by BI Publisher engine.

Data Source Configuration

Make sure “demo” database is correctly configured under Administration->Data Sources-> JDBC Connection.

Point to any Oracle database which comes with HR and OE schemas. Most of the reports which come pre-packaged with BI Publisher fetch data from OE schema. Click on “demo” hyperlink shown below

And set it properties as shown below

Report which we are going to use for Bursting is going to be fetching data from HR schema.

Will start with the report creation.
Click on ‘Catalog’ to see all the files and folder. Click on ‘New’ icon and select ‘Folder’.

Next click on ‘New’ icon (Next to Catalog link) and select ‘Data Model’.

You will get a page as below.

You can give the Description for your Data Model and the Default Data Source. In XML Output Options, make sure to check Include Parameter Tags and the other two are unchecked. Select ‘Data Sets’ tab, click the ‘New’ icon and select ‘SQL Query’ as shown in the image below.

You will get a screen similar as shown below.

Here you can directly enter the SQL statement or can generate the SQL statement using Query Builder. Make sure that you select the right Data Source.
select EMPLOYEES.EMPLOYEE_ID as EMPLOYEE_ID,
EMPLOYEES.FIRST_NAME as FIRST_NAME,
EMPLOYEES.LAST_NAME as LAST_NAME,
EMPLOYEES.EMAIL as EMAIL,
EMPLOYEES.PHONE_NUMBER as PHONE_NUMBER,
EMPLOYEES.HIRE_DATE as HIRE_DATE,
EMPLOYEES.JOB_ID as JOB_ID,
EMPLOYEES.SALARY as SALARY,
EMPLOYEES.COMMISSION_PCT as COMMISSION_PCT,

EMPLOYEES.MANAGER_ID as MANAGER_ID,
EMPLOYEES.DEPARTMENT_ID as DEPARTMENT_ID,
JOBS.JOB_TITLE as JOB_TITLE,
DEPARTMENTS.DEPARTMENT_NAME as DEPARTMENT_NAME
from HR.JOBS JOBS,
HR.DEPARTMENTS DEPARTMENTS,
HR.EMPLOYEES EMPLOYEES
where DEPARTMENTS.DEPARTMENT_ID=EMPLOYEES.DEPARTMENT_ID
and EMPLOYEES.JOB_ID=JOBS.JOB_ID








order by DEPARTMENTS.DEPARTMENT_ID
Click on ‘XML’ icon on right-hand top.

Select ‘Number of rows to return’ to ‘All’ and select Run. On the click of Run you will see an XML output as shown below.

Besides ‘Return’ button on the right-hand top, there is an image. On the click of this image, select the option of ‘Save As Sample Data’.

After this step, in ‘Data Model’ you can see ‘sample.xml’ across ‘Sample Data’.

Save the work by clicking on ‘Save’ icon (right-hand top).
Creation of Report
Create a new layout called “Employee Template 1” and upload the rtf template file for the report. Figure below shows contents of layout file. This was created in Microsoft Word.
<?call:iWareCompanyHeader?> <?xdoxslt:set_variable($_XDOCTX,’RTotalSalary’,0)?>

Emp ID	First Name	Phone Number	Department	Salary
<?for-each: ROW?><?EMPLOYEE_ID?>	<?FIRST_NAME?>	<?PHONE_NUMBER?>	<?DEPARTMENT_NAME?><?end for-each?>	<?SALARY?> <?xdoxslt:set_variable($_XDOCTX, ’RTotalSalary’, xdoxslt:get_variable($_XDOCTX, ’RTotalSalary’) +SALARY)?><?xdoxslt:get_variable ($_XDOCTX, ’RTotalSalary’)?>

<?template:iWareCompanyHeader?>
iWareLogic
iWareLogic Technologies Pvt. Ltd.
Aditi Samruddhi,
Baner, Pune 411045
Email: info@iwarelogic.com
<?end template?>

Next click on ‘New’ icon (Next to Catalog link) and select ‘Report’.

You will see a page similar as shown below. You have to select the Data Model you have created in the previous step.

Select ‘Upload’ from region ‘Upload or Generate Layout’.

Give the path of the template in ‘Template File’ and the other details. Once the template is uploaded, save the work and you can now view the report. Click on ‘View Report’ icon.

You will see an output as shown below.

The report generated list of employees and “sum of salary” as the last row.
Bursting
Before we start with bursting configuration, let us say that we want this employee report to be generated on per department basis, i.e. there should be as many reports as there are department and each report should show list of employees only in that department along with sum of their salary. For Bursting to work, scheduler has to be correctly configured which we already covered in section “Scheduler Configuration”.
Now select the Data Model file and click on ‘Bursting’ tab. Suppose this option is disabled, make sure you have checked the option ‘Enable Bursting’. It comes under Properties on edit of the report created.

“Split by” is set as “/DATA_DS/G_1/DEPARTMENT_ID”. This is XPath expression for choosing DEPARTMENT_ID from the XML. In Order to see how this XML data, which is fed to Layout Template, just go click on ‘XML’ icon.
“Deliver by” is also set to “/DATA_DS/G_1/DEPARTMENT_ID”. Value chosen by this XPath expression acts as a co-relation KEY between the report data and the SQL Query which we are going to be using for specifying bursting delivery details.
SELECT distinct(department_id) KEY,
‘Employee Template 1′ TEMPLATE,
‘RTF’ TEMPLATE_FORMAT,
‘en-US’ LOCALE,
‘PDF’ OUTPUT_FORMAT,
‘FILE’ DEL_CHANNEL,
‘C:\temp\yogesh\publisher’ PARAMETER1,
(select department_name from hr.departments d
where d.department_id = e.department_id) || ‘.pdf’ PARAMETER2

FROM hr.employees e








where e.department_id is not null

Make sure you specify the right path for PARAMETER1.

If you run this query, you will see output like.

This query specifies all the parameters necessary for bursting the report. Column names of this query have to be “KEY”, “TEMPLATE”, “DEL_CHANNEL” etc. DEL_CHANNEL stands for Delivery Channel. Following table lists all possible delivery channels and additional parameters which you have to specify depending upon the delivery channel you choose. Example above is using “FILE” delivery channel and it needs Parameter1 to be Directory where reports will be saved and Parameter2 will be name of the report.

Channel	Parameter1	Parameter2	Parameter3	Parameter4	Parameter5
Email	Email address	CC	From	Subject	Message body
Printer	Printer Group	Printer	Number of copies	Sides	Tray
Fax	Fax Server Name	Fax Server Number
WEBDAV	Server Name	Username	Password	Remote Directory	Remote File Name
File	Directory	File Name
FTP	Server Name	Username	Password	Remote Directory	Remote File Name

Parameter6 and Parameter7 are applicable only when delivery channel is EMAIL and their values mean “Attachment true/false(For PDF, always set this parameter to true)” and “Reply-To” respectively.
Once Bursting parameters are set, click on ‘Save’ to save your work.
To schedule a job to generate the reports, select ‘Catalog’ and navigate to the report you have created. Below the report name and details, there is a link ‘Schedule’.

In ‘Output’ tab, make sure to select ‘Use Bursting Definition to Determine Output & Delivery Destination’. In ‘Schedule’ tab, select ‘Run now’ option. Click on Submit.
Important options are “Run Immediately” and “Burst Report”, click on Submit. Once you submit this schedule, you will contents of folder c:\temp\yogesh\publisher. In my case, I saw 11 reports generated as there were 11 distinct departments. Please note, if you don’t put where e.department_id is not null in the SQL Query given for bursting, scheduler will not run and you won’t get the reports. In the HR.EMPLOYEES table there is one employee with NULL DEPARTMENT_ID and bursting SQL query does not like NULL to be present in the KEY column.

And see below contents of Accounting.pdf. As you can see this report only contains employees belonging to accounting department.

Thanks,

Satya Ranki Reddy