This is to describe how a stored procedure universe is created at the semantic layer and how Web Intelligence will consume this type of universe. What are the benefits and the limitations of a stored procedure universe.
A stored procedure can be created as a UNV universe created in the Universe Design Tool. With the release of BI 4.2 SP6 it can now be created as a UNX universe with the Information Design Tool. While a Stored Procedure provides some performance improvement; the feature has restrictions that should be considered before designing the universe. This is an explanation of a stored procedure universe and some of the benefits and limits of its use.
What is a Stored Procedure?
BI version XI 3.1 introduced the option 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. A universe is a read only object and a database stored procedure provides the possibility of manipulating the data before it is consumed by the universe or reporting tool. This allows the sql statements such as CREATE, DROP, INSERT, UPDATE statements to be used, where it is not permitted in the universe structure. These sql statements will not be visible to the universe or reporting tool.
Benefits of Using a Stored Procedure
Since stored procedures are encapsulated and precompiled the sql statements will appear only once. Comparatively in Web Intelligence sql may be generated and passed to the database up to three times. The stored procedure makes it easier to maintain code. Changes in the database are a task for the database administrator and not the BI application. 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. Another added benefit is that it can reduce network traffic, which help eliminate bottlenecks.
There are a limits to available options and these should be reviewed and understood.
Supported Databases for Stored Procedures
Stored procedures are supported in the BI platform for the following databases and network layers:
- DB2 UDB and iSeries through CLI driver
- Sybase Adaptive Server through CTLIB
- Oracle through OCI
- MS SQL Server through OLE DB
- Through JDBC:
- DB2 UDB
- MS SQL Server
- MySQL 5
- SAP HANA
- Through ODBC:
- DB2 iSeries
- MS SQL Server
- SAP HANA
- Sybase ASIQ
- Sybase SQL Anywhere
- Teradata (only macros)
Restrictions for Using a Stored Procedure
It is important to understand, that while it is efficient to use a stored procedure, there are some limitations and restrictions.
- 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
- It can be created in the Universe Design Tool. It is available in Information Design Tool with BI 4.2 SP6 and it is possible to convert a unv universe stored procedure to a unx universe in IDT.
- 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 Universe 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 unv universe view will not update the schema. The stored procedure must be re-inserted. (This causes the object id to change!)
- In IDT if a change is made on the database to the stored procedure, the unx universe permits "Regresh Structure" to update the universe.
- If a change is made on the database to the stored procedure using IDT, then a refresh structure may be performed and the data foundation is updated. (After 4.2 SP7)
- 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 - Only use Macros. (Because the Teradata SP does not perform the last step generating a SELECT statement)
- The procedure should end with the SELECT statements to generate the Virtual Table in the Universe.
The connections Use connection for Stored Procedure security right is enforced at universe authoring time (4.2 sp7.)
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
In Information Design Tool
The IDT 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.
- Create a connection and shortcut to the LocalProject
2. Create a new single source Data Foundation in the Local Project
3. In the Data Foundation Select Insert Stored Procedure from the Graphic Panel.
5. Create new Business Layer and continue with creating and publishing the universe.
The Universe Design Tool
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.
- File new universe
- 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.
Local Temp Tables
Troubleshooting and current issues
- ConnectionServer supports stored procedures that return result sets.
- Review the Sql – is the sql supported? Check the Data Access Guide and the IDT or UDT Guide.
- Did this work in a previous version and stop working after applying a patch or migrating from an earlier version?
- Does the sql contain use of global temp tables?
- Check the KBA's listed below
- Add the following under all libraries needed in the data base sbo file
It is located in the <connectionserver-install-dir>\connectionServer\directory.
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 Oracle Database path: <connectionserver-install-dir>\connectionServer\oracle
<Parameter Name="Transactional Available">No</Parameter>
<Parameter name="Transaction Mode">AutoCommit</Parameter>
<Parameter Name="Force Execute">Always</Parameter> Re-launch UDT
To apply in Webi, change server file and restart the SIA
Sybase: for issues with Sybase tables add the following parameter to the Sybase.prm
To apply in Webi, change server file and restart the SIA