Skip to Content
Every Business Objects Universe designer faces performance problems in his/her career at least once and in most of the cases he will use “push down strategy” ask DBA to optimize the warehouse .

However it works very well :)  but there are some thing which can also help to optimize the performance of Universe.

Analyze Report SQL for unnecessary Joins

1. First get the list of reports which are performing low and get their SQL.

2. Analyze the report SQL for joins. There could be unnecessary join which is causing query to perform low. make a change in your universe accordingly to generate query with optimized joins.

Analyze report Query for Indexes. ( work with DBA for this )

1. Get the report SQL , check the where clause

2. Check if indexes are user properly in SQL and also they exist in database

3. Also, check if statistics in database is updated, as its its not then DB may not generate optimized query.

Use Aggregates for measures

1. Use Aggregate aware for your measure objects to use summary tables form Database\

2. You can also think of Automatic Query re-write instead of using Aggregate Aware in Universe designer, However this requires careful planning and high involvement from DBA

Use Partitions for high volume fact tables

1. Partitioning the fact table can boost your query performance.

2. Work with DBA to get it done

Array Fetch Size

1. Play with Universe options for setting up optimal value of Array fetch Size parameter.

Universe Analysis for Shortcut joins

1. Analyze your reports and universe for possible use of shortcut joins as they play little but important role in performance.

Index Awareness

1. Try using Index Awareness from Universe side to generate optimized query.

Note: This requires thorough testing of report data and detail understating of data warehouse data

Universe Cleaning.

1. Make sure LOV is disabled measure objects

2. Make sure LOV is disable for un-necessary dimension objects

JOIN_BY_SQL

1. Try evaluating JOIN_BY_SQL universe parameter

I will try posting above Universe Tuning/Optimization practical examples one by one in coming time. Need to do few experiments :) .

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply