Using Index Awareness in Business Objects Universe for Performance Optimization

in Articles


What is Index Awareness?

Making universe index aware means telling universe on which columns indexes are created. This helps Business Objects to generate efficient query which uses indexes instead of actual column values which help to speed up the data retrieval.

e.g. If we have customer name in the query its useful to end user of report however we can use customer id to retrieve the data by making the use of index awareness which would help to fetch data faster.

You can define two types of index awareness in universe.

Primary Key: Using primary key index awareness universe can use index value instead of actual value of column. The query will thus use the key value. This helps database to fetch data faster.

Foreign Key: Using foreign key index awareness on object universe can filter the data without the need of join in query. Suppose you need to build a report which has filter on dimensions table. In absence of index awareness designer will use actual dimension values which requires join between fact table and dimension table. However if we had foreign key index awareness applied. Designer can apply filter directly in fact table using foreign key index. So this avoids join between dimension table and fact table. However foreign key index awareness requires dimension column values to be unique if same value is represented by different key. Then this may return unwanted result.

How it works?

Suppose you are building a report on Island Universe “Service wide Sale revenue.”, After you drag drop the object, You will see following query generated.

clip_image002

Now if you see in the query, designer has joined Outlet_Lookup table to put restrictions on the states and using actual values from service table to filter the data.

Now Suppose we apply index awareness on service object defining primary keys and foreign keys.

The query will look as below.

clip_image004

If you could see in the query, Dimension values have been replaced with actual foreign keys filtering data on fact table. Which is an efficient way to filter data? So query will run faster compared to earlier way.

However remember, index awareness might return wrong result if you have dimension value which have two different keys in dimension table.

e.g.

If we have data as below in service dimension

Service_id

Service

212

Activities

213

Activities

Now if you have index awareness applied on service object and using service as a filter in report. Since ‘Activities’ have two keys, Business Objects does not know this might put any of the PK as filter returning wrong data in report

While defining the index awareness for particular column you can also define the data restriction for the object using WHARE clause in index awareness. It’s very useful to restrict the data in index awareness.

e.g. for service object I can define service price WHERE clause as below.

clip_image006

How to define index awareness.

To define index awareness

1. Right click on Objects to open its properties.

2. Open the Keys Tab

3. Define the primary keys and foreign key to be used in index awareness.

clip_image008

Index awareness when used with proper data analysis can give you significant performance gains.


Previous post:

Next post: