Stored Procedure Universe

A stored procedure can be a used in a UNV universe and provides some performance improvement.  The feature itself has many restrictions that should be considered before designing a universe with them.  This is an explanation of this feature and some of the benefits and limits of its use.

Since version XI3.1 it is possible to create a universe on a stored procedure.

A stored procedure is an encapsulated set of sql statements that is stored and run on a database.  It creates the possibility of manipulating the data before it is used in the reporting tool.  The sql statements are not stored or visible from the BusinessObjects application.

Creating a Universe on a Stored Procedure

The universe design tool generates one table per selected stored procedure (or many in a multi-result set) one object per column in the select statement.  The structure of the table is determined by the definition in the stored procedure.

Quick design Wizard, available from the toolbar.

1. Click the Quick Design Wizard toolbar button. The welcome pane appears.

2. Click the check box Click here to choose stored procedures universe at the bottom of the pane.

3. Click Begin. The Define the Universe Parameter panel appears.

4. Type in a universe name in the Enter the universe name field.

5. Choose the database connection from the dropdown list in the Select the database connection list.

6. Click Next.

The Create initial classes and objects panel appears.

7. Click on a stored procedure.

8. Click Add.

The stored procedure is created in the Universe classes and objects pane.

9. Click Next.

10. Click Finish. The Congratulations panel appears.

OR

1. File new universe
2. Check on under the connection the check box for Stored Procedure Universe.  This Adds a parameter to the universe parameter list STORED_PROC_UNIVERSE is set to YES.

Benefits of Using a Stored Procedure

Since stored procedures encapsulate the sql statements the database operation appears only once within the stored procedure.  Comparatively in Web Intelligence the sql may be generated and passed to the database three times.  This makes it easier to maintain a set of code.  Changes in the database are a database administrator task and not BI.  Security is maintained by the database and code can only occur on the database.  Users have no access to view or change sql.  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.  An added benefit is that it can reduce network traffic, which can be the greatest source of bottlenecks.

Restrictions for Using a Stored Procedure

It is important to understand, that while it is efficient to use a stored procedure, they limitations and restrictions in the BusinessObjects Universe.

• A stored procedure can only be called in a universe using the stored procedure universe methodology.  It cannot be called in BEGIN_SQL, Connectinit or a Derived Table
• Can only be created in the Universe Design Tool.  Not in Information Design Tool.
• No joins can be made between a stored procedure and a table in a universe
• No Query Filters can be used
• No predefined conditions
• The procedure itself may contain a variable that will prompt, but it cannot be formatted or converted in any way. A value can be supplied, that is all.
• Input parameters should be of a type the UDT can use without requiring a conversion, e.g. instead of type “bit”, use “integer”.
• They cannot be used in Linked Universes.
• Not All Databases support stored procedures
• These SQL Commands are not ALLOWED:  COMPUTE, PRINT, OUTPUT or STATUS
• Cursors are only supported for Oracle Databases
• The stored procedures do not support OUT or dynamic result sets parameters
• An IF statement cannot be used in the where clause.
• You can only create a new universe based on the stored procedure.  You cannot add it to an existing universe.
• The stored procedure creates all objects in the universe automatically.  If there is a long text object it will not generate an object.
• If a change is made on the database to the stored procedure.  The universe view will not update the schema.  The stored procedure must be re-inserted.  (This causes the object id to change!)
• 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.
• Teradata Cannot use Stored Procedure at all  – Only use Macros

Supported parameters in a Stored Procedure

Without parameters

With parameters (IN)

With multi-result sets (this means it creates more than one virtual table by use of the select statement)

Prompts or Parameterized Query in a Stored Procedure

This is a prompt that is defined within the stored procedure and when the procedure is executed will prompt the user.  The prompt display name is defined in the procedure.  It is possible to associate a list of values to this prompt in the Universe.  A standard database table is inserted in the universe and it can contain only simple values.    In front of each parameter in the stored procedure parameter dialog box there is a button to open an advanced dialog box.

Troubleshooting and current issues

Review the Sql – is the sql supported? Check the Data Access Guide and the UDT Guide.

Did this work in a previous version and stop working after applying a patch or migrating to BI4 from 3.1?

Does the sql contain use of global temp tables?

Check the odbc.sbo file and add this parameter:

Add the following under all libraries needed in the data base sbo file

It is located in the <connectionserver-install-dir>\connectionServer\directory.

<driver>.sbo

This file is specific to each data access driver. The <driver> placeholder stands for the data source to which the configuration file applies. Each SBO file is in a subdirectory of the connectionServer directory, where the subdirectory is named after the database network layer or middleware, for example

  <connectionserver-install-dir>\connectionServer\oracle for Oracle databases.

<Parameter Name=”Force Execute”>Always</Parameter>

Re-launch UDT

To apply in Webi, change server file and restart the SIA