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.

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

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

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.

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.

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.

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.

10. Create classes and objects as per your requirement.

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

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.

• 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.