Business Objects: Universe Creation by Stored Procedure:

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.

Benefits of this process -

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

Steps to be followed - 

• Open the universe via using universe design tool and then open the universe parameter box.
• In the definition tab provide the name to the universe and set the connection.
• After that select the check box “click here to choose stored procedure universe”.
• Then the universe get open up and then go to Insert – stored procedure.
• After that select the required Stored Procedures from browser and click insert.
• 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’.
• If you don’t want to change your prompt values for next execution then you have to select “Use this Value” as highlighted below.
• 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.
• 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.
• 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.
• Create classes and objects as per your requirement.
• You cannot change Object property like other table based universe objects and all stored Procedure universe objects are greyed out.
• 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.

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