How to create Universe using Stored Procedures?

I would like to share my experience on creating universes based on Stored Procedures to all of you and there would be some limitations when using stored procedures as source. All the examples and codes used in the post are from BO XI 3.1 & Oracle 11g DB.

A stored procedure universe is a special universe that enables Web Intelligence users to access stored procedures residing in the database. This is the only way that Web Intelligence users can access stored procedures. Web Intelligence users can use the stored procedures universes to create reports that are based on stored procedures in the database.

A stored procedure is a compiled SQL program, consisting of one or more SQL statements, which resides and runs on the target database.

Web Intelligence cannot open reports created by Desktop Intelligence when the reports are based on stored procedures. This means Web Intelligence users must use specific stored procedures universes to access the stored procedures and create reports.

Stored Procedures Benefits:
• In stored Procedures, database operation appears only once throughout the application source and it will improves debugging & maintainability.
• Source codes in the stored procedures will get affected in only one place when changes happened to the database schema and schema change will be administrator task rather than BO Admin / developer task.
• You can set tighter security restrictions for stored procedures, since it resides in server.
• Because stored procedures are compiled and stored outside the client application, they can use more sensitive variables within the SQL syntax, such as passwords or personal data.
• Stored procedures reduce database network traffic.

Create universe based on Stored Procedure:
1. Provide the universe name and Stored Procedure Universe connection in the Universe Parameter definition.
2. Since the universe uses the Stored Procedure as a source, you can check “Click here to Choose Stored Procedure Universe” option and you will have only Stored Procedures in the list.

Stored procedure1

3. Double Click in universe structure or Insert – Stored Procedures.

Stored procedure2

4. Select the required Stored Procedures from browser and click insert.

Stored procedure3

5. Once you click insert the Stored procedures, If stored procedure has parameters, it will now ask you to enter information for the Parameter, Prompt Label and what it should do on ‘Next execution’.

6. If you don’t want to change your prompt values for next execution then you have to select “Use this Value” as highlighted below.

Stored procedure4

7. If you want to enter different values for each execution then select “Prompt me for a Value” option as highlighted below and enter your prompt label.

Stored procedure5

8. If you want customized LOV (the list of values must come from a standard table not from stored procedure) for your prompt, you have to add standalone table into the universe structure and create objects related to that.

Stored procedure6

9. Now the Stored Procedure inserted into Universe structure. Designer generates one table per selected stored procedure (or many tables if multiple result sets) and one object per column returned by a stored procedure.

Stored procedure7

10. Create classes and objects as per your requirement.

Stored procedure8

11. You cannot change Object property like other table based universe objects and all stored Procedure universe objects are greyed out.

Stored procedure9

12. In order to avoid parsing errors on stored procedures columns, it is recommended that you alias result columns based on complex SQL, for example using the aggregate functions – sum, count. The creation of aliased objects cannot be constrained.

Note: In Universe parameters, check the parameter STORED_PROC_UNIVERSE is set to YES. This indicates that the current universe is based on a stored procedure.

What can be done in Stored Procedure Universe?

• You can change the data type of an object
• You can set object properties of an object (Dimension, Measure & Detail)
• 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.
• You can multiple stored procedures in a single universe and all are act as a standalone.

What can’t be done in Stored Procedure Universe?

• Query Panel in universe Designer Tools option is grey out for Stored Procedures.

Stored procedure10

• The stored procedures do not support OUT or dynamic result sets parameters.
• 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.
• You cannot create Pre-defined Conditional Objects in universe
• You cannot use stored Procedure objects into Report filter panel.
• You cannot create additional prompts or add additional criteria in query panel.
• You cannot hide any of the Class & objects which are created from Stored Procedure.
• You cannot change / show Class & objects status which are not created from stored procedure (example, standalone table object).

Report Creation using Stored Procedure Universe:
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 can refresh and view the returned data from the database.

Please pass on your comments.

Advertisements

12 thoughts on “How to create Universe using Stored Procedures?

  1. Very good article on stored procedure universe,but please let me know the requirement that made you to create universe on stored procedure,give me an example.

  2. What is the purpose for a company to build all of their reporting structure on stored procedures, and not build a relational database? At some point would it be better to have a DBA build a database then hve several hundred, or even thousands of stored Procedures to be drawn on for Universe reporting? Just trying to understand the logic. Also, are there any particular issues when building on the BI 4.x platform, especially with the IDT?

  3. Good article. Is it fair to say the Database person is more of a SQL Code Guru, and less of an ETL practitioner? What drives a company to build a data source using Stored Procedures instead of Tables, just looking for a logical reason?
    When dealing with BI 4.1, are we still restricted to building with the UDT, or are stored procedures allowed for in building IDT Universes?

    • Hi James,

      Mostly stored procedures are created to avoid ETL processing and you need to have proper setup to do ETL and cost effective.

      You can create universes using stored procedures. Data access > stored procedure > select connection > select SP (your specified Stored Procedures).

      Thanks
      MadhesDWBI

    • Hi James,

      Mostly stored procedures are created to avoid ETL processing and you need to have proper setup to do ETL and cost effective and also time saving when you use Stored procedures.

      Thanks
      MadhesDWBI

      • Hi May I know your number please , I badly required your help in one of my project

        My no 8939858503 please provide ur no ..

  4. Hi All,

    We are recently upgraded to BO 4.2 SP2 Patch 1 and I need all of your help in creating a Webi report with Oracle stored procedure.

    I am able to call a simple stored procedure with below command but I am unable call main procedure from Oracle which is created with parameters and Out parameter as Ref cursor, as we need to pass some (In) parameters to get the data.

    begin
    GetEmp_Details (:R);
    end;

    GetEmp_Details is stored procedure and R is Ref cursor (Out parameter)

    Please can you help me with syntax, how to call a complex procedure from Oracle.

  5. Hello,
    If there are multiple Stored Procedures in my universe, then what will be the process for crating report.What about Joins, Cardinalities, Loops and etc.

  6. Hi Madhes,
    It pretty nice blog, my question is not to related with this article, I was wondering if you could possibly help me out to upgrade BI 4.2 in to cloud. I am almost done requirement gathering from customer and I have some exists current state documents. The client want to move in to cloud, I don’t know using AWS or Azure.

    Please advise.

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