Irfan's World

November 17, 2012

What you should know about Stored Procedure Universe in BI 4.0 ?

As we all know there are many advantages of using stored procedures, they are always in my problem resolving kit, as a last resort I have to use it to create one of the complex report that needs to be delivered  as Web Intelligence report. Apart from providing many advantages like transaction control, speed, complex logic support, security and data access, they are powerful enough to deliver complex reports.  My all time favorite tool is to use Universe, I have to use stored procedure to develop a report that takes data from multiple excel files including few database tables,  one other thing I would like to mention is the data from excel file changes everyday.  Luckily Business Objects supports stored procedure universes and I have used it to deliver this requirement.

I would like to bring my experience to all my readers who are interested in learning what stored procedure universe can offer to a developer or an end-user. I would like to point out what is possible and what is not possible with Stored Procedure universe and I hope it should give a clear understanding when one can utilize it.  I am using BI 4.0 with Oracle 11g database.

What is a Stored Procedure?: Sets of SQL statements that are saved as executable files in a database fully parsed.
What is a REF Cursor?: A pointer to a work area in which a result set is stored.

What are the other requirements needed at first place ?

  • Create package that defines the REF CURSOR. This REF CURSOR must be strongly bound to a static pre-defined record structure.
  • The procedure must have a parameter that is a REF CURSOR type.
  • The REF CURSOR parameter must be defined as IN OUT (read/write mode).
  • The stored procedure can only return one ResultSet
  • Test and run the stored procedure in an SQL Tool like TOAD or SQL*PLUS

Once all of the above requirements have been met, verify that the database driver that you are using works with that version of Oracle.

I am using SCOTT schema from Oracle.  Following is a package emp_package declaration for a strongly typed cursor in Oracle using EMP table.  REF CURSOR emp_row_type returns a result set of type emp%rowtype

image

Following is a structure of the stored procedure, it shows how to use or refer emp_row_type REF CURSOR data type declared in the emp_package.

image

The name of the stored procedure is getEmployeesByDepartment, It accepts a parameter value for a department column and returns the data from the EMP table. We will use this stored procedure to build a universe in Business Object. First create a secure connection that connects to the database and also make sure you have selected the checkbox ‘Click here to choose stored procedure universe’ as shown in the screenshot below. This will indicate that it is a stored procedure universe

image

If stored procedure has parameters, it will now ask to enter information for the Parameter, Prompt Label and what it should do on ‘Next execution’ ?. If you see the screenshot below it shows value column, prompt label and Next execution drop down box. Click OK to save and close this window.

image

Once all the information is provided, it will display a table in the universe pane as shown below, this table contains all the columns that will be returned by the stored procedure. In this example, I have created a class named Employee with two measure objects like Sal(Salary) and Comm(Commission).  Similar to an ad hoc universe one can use the stored procedure without an issue.  There are no joins, no contexts in the universe, all the business layer logic is stored in the stored procedure itself. 

image

You can save the universe and export it to the repository.  Now, you can use this stored procedure universe to develop Web Intelligence reports.  You can drag and drop the objects similar to any other Universe. There are few things that are not possible in query panel.  You cannot create additional prompts or add additional criteria in query panel. You can refresh and view the returned data from the database.

image

Now use the Universe to create a Web Intelligence report.  I have selected all the columns that were returned by the stored procedure.

image

What is possible in a Stored Procedure Universe ?

    You can change the data type of an object
    You can set object properties of an object
   
You can do the check integrity of stored procedure universe
    You can use stored procedure objects in result pane while creating report.
   
You can apply sort on an object if required.
   
You can apply access restriction for a user or group.
   
You can set a prompt value if the stored procedure accepts a prompt.

What is not possible in a Stored Procedure Universe ?

     You cannot use a stored procedure object in the criteria pane in query panel of Web Intelligence report.
     You cannot change the definition of a Universe object, it is disabled or grayed out by default.
     You cannot create a pre-defined filter using a stored procedure Universe object, it is disabled or grayed out in the Universe.
    You cannot INSERT another stored procedure in a stored procedure universe.
    You cannot INSERT a table in the stored procedure universe.
    You cannot call a stored procedure from a Derived Table in Universe.
    You cannot apply INDEX AWARENESS on stored procedure Universe objects.
    You cannot create custom or new objects that were created for certain purpose based on stored procedure objects.

In conclusion I would say that there are limitations of using Stored procedure universe, lot of things not possible at the Universe Level and the universe becomes like a read only Universe. There is a little control on the Universe side which is changing a connection such as ODBC to Native.  Would use this feature only as last resort.  If you have any comments please share it through the comments section below.

Advertisements

8 Comments »

  1. Irfan, very interesting to read this article. I have no history of using stored procedures and can understand from the example why you might. With BOXI4 however the Information design tool allows you to federate XLS files with database tables. Doesn’t this ability mean that stored procedures are not the only or next method for your example?

    Comment by keith — November 18, 2012 @ 1:42 am

    • Keith,

      Thanks for stopping by, I knew this question will arise, but my requirements was quite different, I was dealing with data like NCLOB,CLOB kind, that needs to processed first and eliminate the unwanted data, It also needs some ranking on certain groups based on some marketing goals data. It was also involved with row level security as well which was making it more complex at IDT level while creating security profiles. Hence I pushed it to the database and stored procedure worked well.

      Irfan

      Comment by Irfan — November 18, 2012 @ 12:32 pm

  2. Hi Irfan, have you worked with BEGIN_SQL in the designer? if yes, can u help to elaborate more on this? i need to call a package to get user ID from oracle before generating my WEBI reports. I hope you can help

    Comment by Fara — December 12, 2012 @ 8:56 pm

    • Fara,

      You can send a value to a stored procedure parameter by calling it in BEGIN_SQL. But you cannot retrieve any value back using this universe parameter. (For example, you can set SET_CONTEXT using DBMS_SESSION in oracle for row level security using VPD’s.). This parameter is not supported by all databases.

      Irfan

      Comment by Irfan — January 29, 2013 @ 2:46 pm

  3. Hi Irfan… I found this to be quite helpful, but one question…. Can a WebIntelligence report pass a run-time variable (for example, a user name) into a stored procedure? I am thinking of using this approach for data security. I want to capture the currentuser() at run-time and pass this value into a a stored procedure to perform a membership lookup, then return a result set.

    Comment by Paul Gundersen — January 29, 2013 @ 1:46 pm

    • Hi Paul,
      Which database are you using ? You can send system variable BOUSER. Create a pre-defined filter in Universe Designer using @Variable.

      Good Luck,
      Irfan

      Comment by Irfan — January 29, 2013 @ 2:02 pm

  4. Hi Irfan,

    I am working on a stored proc universe and would like to implement data level security. How do I achieve this. My db is SQL server 2012, how do I use BOUSER variable to conrol security. Please let me know if this is doable.

    Thanks

    Comment by BOadmin — March 3, 2015 @ 11:39 pm

  5. Is it possible to write a SP to insert data in db?

    Comment by ram — July 21, 2015 @ 4:31 am


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: