Stored Proceudre in Universe Designer

Stored Procedure Universe Best Practice Guide

Purpose

This is to describe how a stored procedure universe is created at the semantic layer and how web intelligence will use this type of universe. Why use a stored procedure, and what are the limitations of using this type of universe.

Overview

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.

What is a Stored Procedure?

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.

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.

• 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 manipulated.
• They cannot be used in Linked Universes.
• Not All Databases support stored procedures
• These SQL Commands are not ALLOWED: COMPUTE, PRINT, OUTPUT or STATUS
• 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)

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.

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: Always

Posted in: