Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Report Bursting using Delivers – Phase1 Using Pipelined Functions

Posted by Venkatakrishnan J on December 28, 2007

Note: Though i have shown email addresses below those are actually users. You can burst to multiple users who are part of the repository and not email addresses directly. But you can have multiple email addresses as part of a user. 

One of the very good features of BI EE is its ability to burst and send the reports to multiple users simultaneously. Lets look at how to go about achieving bursting and also at the same time look at some additional examples of how to send out the reports to multiple users whose email ids are not stored a database column. First lets look at how to burst reports out using Delivers. In order to do bursting, one would have to have a column in the database that would have all the email ids of the end users to whom you would like to do the bursting to. So, the first step in enabling bursting is to create a report containing the email id column. This report can contain all the desired filters that you want to apply on this report. For example, in my case i have 2 of my own email ids in the report.

      

Once this is done the next step is to create an ibot. While creating the ibot follow these steps below

1.   In the conditional request tab enter the report that we just created above. This is what will be used for bursting.

      

2.   Go to the Recipients tab and enable the checkbox wherein you can determine the users from a Conditional Request. Choose the column that contains the email ids.

      

3.   Now go to the Delivery Content Tab and choose the report which you would like to send.

      

4.   Also choose the kind of scheduling that you need along with the desired Destinations (Email, Pager etc).

This would give you the basic bursting capabitlites that you are looking for. But in some cases it so happens that we would like to send the reports to users whom we know but those that are not stored inside a database column. So, in order to configure delivers for this case we shall see 2 approaches

1.   Use of Pipelined Functions and Direct Database Requests

2.   Use of Writebacks

In this blog entry i shall be covering the first approach. This is not recommended for production machines. This is just for illustration purposes since Direct Database Requests are not recommended (they bypass all of your BI EE security). In order to achieve this lets start with creating a simple Pipelined function as shown below

CREATE OR REPLACE TYPE VAR_ARRAY AS TABLE OF VARCHAR2(100);

CREATE OR REPLACE FUNCTION PIPE_EMAIL( p_csv IN VARCHAR2)
RETURN VAR_ARRAY
PIPELINED
AS
v_inCSV varchar2(50);
v_noCSV number;
v_inSTR number;
v_enSTR number;
BEGIN
SELECT LENGTH(p_csv) – LENGTH(REPLACE(p_CSV,’,’,”)) INTO v_noCSV FROM DUAL;
v_inSTR := 1;
SELECT DECODE(INSTR(p_CSV, ‘,’, 1,1),0,LENGTH(p_CSV) + 1,INSTR(p_CSV, ‘,’, 1,1)) INTO v_enSTR FROM DUAL;
FOR I in 1..v_noCSV + 1 LOOP
SELECT SUBSTR(p_CSV,v_inSTR,v_enSTR – v_inSTR) INTO v_inCSV FROM DUAL;
PIPE ROW(v_inCSV);
v_inSTR := v_enSTR + 1;
SELECT DECODE(INSTR(p_CSV, ‘,’, v_enSTR + 2,1),0,LENGTH(p_CSV) + 1,INSTR(p_CSV, ‘,’, v_enSTR + 2,1)) INTO v_enSTR FROM DUAL;
END LOOP;
RETURN;
END PIPE_EMAIL;

So what this basically does it accepts comma seperated email ids as its arguments and will convert them into rows of data. For example if you give ‘krisvenky83@gmail.com,abcd@abcd.com’ as its argument then it will print like this

krisvenky83@gmail.com
abcd@abcd.com

        

So, all you need to do is to create a new report using Direct Database Requests and typein the following

select * from table(pipe_email(‘krisvenky83@gmail.com,abcd@abcd.com’))

In your case, instead of the email ids specified above you can put as many comma seperated email ids as you want. Now save this report.

      

      

Now the procedure to burst is same as the above. But now we do not have to have the email ids stored in a database column. In a later blog entry we shall see another approach to the same problem that would use Writebacks.

Advertisements

10 Responses to “Oracle BI EE 10.1.3.3/2 – Report Bursting using Delivers – Phase1 Using Pipelined Functions”

  1. Tina said

    Hi,Venkata
    Great done!
    Always can I learn a lot from your blog.I cannot express how I appreciate your effort on this.
    Thank you.
    Just one question,Any official documents about the items in the *.xml files in the msgdb directory?You just test and document?Wow!
    I have tried for some,but not all.

    Thanks to you and this wonderful blog.
    Regards,
    Tina

  2. Venkatakrishnan J said

    You are right. I should have made it clearer. The EMAIL column was a bit misleading. Those were my users. You can only burst it to users and not to email addresses directly. I will update the blog entry accordingly. But each user can have multiple email addresses as part of their profile.

  3. Doru said

    Hi,

    DocumentBurster – http://www.pdfburst.com is a good report bursting solution which can send bursted reports to
    email and/or ftp destinations. It can be used with any report generation tool and it works on Windows and UNIX. It is free for using.

    Doru

  4. Tim said

    Hi,

    Has anyone successfully used BI Publisher to burst BIEE content (either answers, or from subject areas)?
    I am encountering errors when attempting to do so.
    Bursting non BIEE content works fine.

  5. Marija said

    Hi,
    this method is really interesting but how and where do you specify which report you want to burst since here the conditinal request is the one with the email addresses?

    Thanks
    Marija

  6. sudipta pathak said

    hi ,

    my requirement is to send monthly bill to all the employee.i have a obi report where we have to select the empid and phone number 2 see d bill.
    now i want to send individual bill 2 each employee .so i need 2 create some ibot which can brust d report for each empid and then generate report for each emp id and deliver to the corresponding employee.is it possible in obi ee deliver?
    please get back to me

    regards
    sudipta

  7. Jagdeep said

    Hi,

    I get this error:

    +++ ThreadID: 37 : 2009-04-15 10:50:49.040
    [nQSError: 77006] Oracle BI Presentation Server Error: A fatal error occurred while processing the request. The server responded with: Path not found (/users/arikaths/_ibots/Srikanth/iBot – Sample)

    Error Codes: U9KP7Q94

    Error Codes: AGEGTYVF

    Can you please help?

    Regards,

    Jagdeep

  8. Goud said

    Hi,
    I need to send report to Non-OBI users, can someone suggest whats the best way.
    Regards,
    Goud

  9. Kaushik said

    Hi,

    I have special requirement for bursting the reports.

    In my report, I have data State wise.
    and in my location dimension, each state will have the respective State_Head_Email.

    Now can I burst the same report and send it to each of the State Heads only their corresponding data?

    Thanks
    Kaushik

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: