Ross Goodman

Business Objects – Best Practice – Universe Design

The purpose of this document is to provide a description of what I consider to be best practice when designing a Business Objects Universe. We will also describe the reasoning behind the suggestion.

1 Connectivity

This section will describe any items around the connection between Business Objects and the underlying database that holds the data.

Native Connectivity

Always use the vendor’s native connectivity where possible. Avoid using generic drivers, e.g. ODBC for Oracle. You always get better performance and more parameters when using the vendors own drivers.

Avoid ODBC

When accessing Microsoft SQL Server databases use an OLDDB connection rather than an ODBC connection. The connection is faster. OLEDB connections store their parameters in the connection definition it’s self whereas an ODBC connection refers to a predefined ODBC connection name which must be defined on every PC and server.

Keep Connections Open

It is possible to define if the database connection is kept open permanently, closed after a specified period of inactivity or closed after each query. The process of creating a database connection is quite “expensive” time wise so it is better to keep connections open for longer. Open connections consume resources on the database server so it is better to keep the number of connections to a minimum.
Generally, connections to the Business Objects repository database should be kept open permanently. Connections to data source databases should generally time out but this is dependant on the typical users usage profile.

Tuning Network Connections

It is possible to fine tune the way that Business Objects uses the network by changing network packet sizes etc. This can make a large difference to problem reports that return a large volume of data.

2 Physical Structure

This section will describe any practices that should be adhered to when implementing the physical data structures into the Business Objects universe.

Alias All Tables

When importing the database tables, immediately create an aliased version. The aliased versions should be the ones that are joined and are used to build objects from. This is done purely to make the generated SQL and the universe it’s self easier to read.

Join All Tables

Every table that will be being used to supply information should be joined to at least one other table. It should not be possible to create a Cartesian product

Index Joins

Ensure that all joins that are implemented in the universe have a corresponding index on the physical table. Queries that join tables together will almost always run faster when the joining fields are indexed.

Count Rows

There is the facility to get designer to count the number of rows in each table. This should always be done and the results displayed in the universe. There are two reasons for this: if data is missing from a particular table it is easier to spot, if you are using Designers built in validation tools it needs the row counts to check the cardinality of the joins.

3 Business Structure

This section will describe any practices that should be adhered to when implementing the logical classes and objectsinto the Business Objects universe.

Classes

Each class should be uniquely named and have an appropriate business description.

Objects

Every object should be uniquely named and have an appropriate business description. If you find you have two versions of the “same” object, for example “Year” you should find out when you would use each and then name them appropriately.

Conditions

You should implement as many condition objects as you can. It should be the exception that users have to manually build a condition. Conditions should come in two flavours. Interactive filters will use the prompt feature to as the user to enter information, for example “Which years?”. Static filters will apply a restriction without any visible interaction, for example “This Year Only”.

Automation

If you intend to schedule reports then you should ensure that any conditions you do use are not interactive. For example; if you were to add the condition “Year=2007” or “Which Years?”, the value that is hard coded or the value that is supplied at schedule time would be used for every subsequent run, even when the calendar year does change.

Core Definitions

If you find you need to define the same object in multiple places or you have an object that extends the definition of an existing object, you should not redefine the logic of the core object every time it is used. For example MyCustomers = case ownerID when 1234 then customerID end. If I then wanted a MyGoodCustomers object I should not redefine “My Customers” but reuse the definition already in place. This is done via the @Select syntax. MyGoodCustomers=@Select(MyClass,MyCustomers) and > 1000000. This means that if the definition of MyCustomers ever changes it only needs to be changed in a single place.
In reality, I would store the value of 1000000 used above in an object again so that there is a single place where this is defined.

Hidden Objects

If you are using core definition objects, you may end up with objects that simply hold definitions but should not be used in their own right. These objects can be hidden. Hidden objects are still accessible to @Select calls but do not appear in the object list for users to select them.

Where Clauses

Every object has the ability to have a where clause added to it. This should never be used. If you add where clauses to objects then when you select multiple measures, these where clauses are combined. For example Sales2006 = select sum(sales) from sales where year=2006. Sales2007 = select sum(sales) from sales where year=2007. If I were to use both measures in the same query then the result would be: select sum(sales), sum(sales) from sales where year=2006 and year=2007. It is not possible for a single row to belong to both years so no data would be returned.
The “correct” way to implement this object would be Sales2007=select sum(case year when 2007 then sales else null) from sales.

4 Linked Universes

Linked universes are a great way to maintain different “versions” of the “same” universe. For example you may wish to have an exact copy of your live universe but point it at your training database. You will not want to maintain your training universe every time your production universe changes. Using the live universe as a kernel of the training universe and only changing the universe connection will allow this to happen automatically.

Parameters

Universe parameters are not automatically replicated between linked universes.

Contexts

When you create a linked universe the contexts contained in the core universe are not propagated into the linked universe. If you wish to have an “exact” copy then you must re-implement the contexts and also maintain them on an ongoing basis.

5 Parameters

This section will describe any parameters that can be applied to the universe that will have an affect.
This is an example where you may wish to have multiple versions of a universe, one for scheduling with no limits in place and one for novice users with very tight limits.

Limit Size Of Result Set

This option allows you to restrict the number of rows that a single query that a user runs will return. This is one of the features that will prevent ill thought out queries from saturating the network. The value used will be dependant on the number of rows that a “typical” report returns.

Limit Execution Time

This option allows you to set the maximum time in seconds that a users query will run for. This is one of the options that will prevent long running queries from consuming resources on both your database server and your Business intelligence server. The value of this will be dependant on the “typical” report execution time.

Warn If Cost Estimate Exceeds

When a user submits a query and this option is enabled and the underlying database supports the feature then you can warn users if the expected duration of their query exceeds a particular value. The value of this will be dependant on the “typical” report execution time.

Limit Size Of Long Objects

Databases have the option of holding large volumes of data in a single field. Each “long” field in each row can contain a maximum of 4GB of data. If all of this data was returned then this could easily saturate the network and the memory of the local PC. We would recommend that this value is kept quite low to limit the volume of data returned by this field. We would also question as to why long fields are included in the universe in the first place.

Allow Use Of Sub Queries

Sub queries are a way of restricting queries based on the results of others. This is not something that novice users would be using but it is an easy way to implement complex selection criteria.

Allow Use Of Union, Intersect and Minus Queries

These set based operators are easy ways to compare different result sets. We would recommend that this is enabled as other ways to answer the same questions are very complex.

Allow Complex Operands in Query Panel

Selecting this option gives you access to the full range of operands in the query panel. This should normally always be selected unless the universe is targeted at novice users.

Multiple SQL Statements for each Context

If you write a query that requires to access multiple contexts, this parameter specifies that a separate SQL statement should be used for each context. This should normally be enabled if your universe contains contexts.

Multiple SQL Statements for each Measure

When using a relational data source certain structural conditions can exist; chasm and fan traps. One way to resolve these is to tell Business Objects to write a separate SQL statement for each table that is providing a measure. This should normally be selected when using relational data sources.

Allow Selection of Multiple Contexts

Contexts are one way to prevent loops, chasm traps and fan traps. If this is the reason that you have created a context then this option should not be selected.

Cartesian Products (Warn / Prevent)

A Cartesian Product is when no join has been defined between two tables. In this circumstance every row of the first table will be joined to every row of the second table. The setting of this parameter is not relevant. Every table that is defined in the universe should be joined to at least one other.

Core Order Priority

If you are using linked universes then this should be set to YES in the derived universes. This forces the order of the classes and objects to match the order as they are defined in the kernel universe.

6 Security

Depending on the nature of the data you may require to restrict access to the information held in the universe. There are two main methods of implementing this.

Database Security

This is the most secure method to implement security. Each Business Objects user requires a corresponding database login with matching usernames and passwords. The universe connection is then defined so that it uses the users credentials. The definition of the scope of the data that the user has access to is defined in the source database.
This is the preferred solution as it is a more generic solution. This means that even if the user chooses to use a differing query tool, the security cannot be bypassed.

Universe Security

This method involves using a generic logon to the database. The definition of the security roles are stored wither in the universe it’s self via object definitions or in database tables, which are included in the universe. This solution is good as it means that control of the security definition is either data driven or in the control of the Business objects universe designer. Using the previous method, the control of the security is the remit of the DBA. The disadvantage of this method is that if any other query tool is used then the data becomes unsecured. There si also the risk that the user will have the ability to rewrite the SQL that Business Objects produces prior to running the query, allowing them to remove or change the security implementation.

7 Summary

These are what I would consider to be the current best practices when designing business intelligence universes. As is always the case there are times when many of these can and should be ignored. This list should not be used dogmatically but pragmatically. If you are having any particular problems then a member of our Service Delivery team would be happy to work with you to resolve them.