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
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
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 . Business Objects Universe Optimization is not a one night task it requires careful planning and effort.
BUSINESS OBJECTS UNIVERSE OPTIMIZATION
Subscribe to Email to receive all the FREE updates and Business Objects Interview Questions and Answers FREE!!
Related posts:
{ 4 comments… read them below or add one }
Nice post Kuldeep.. Could you please provide practical example also.
can you post a example of using index Awareness and its benefits!!!
If there are people who count on my work (which I hope there are), then there should be some “scary” in wanting to watch that I’ve done excellent work for them. If there are people who will be disrupted if my work does not arrive soon enough, or right enough, or react fast enough, then I guess it is correct to be worried, actively attempting to assure their needs are met. Perhaps the phrases “scary” and “nervous” don’t express enough of the “business dignity” linked with programming and testing, but they for certain do resonate with the feelings I’ve had in the trenches of programming and testing.
its very nice post for learners………
{ 1 trackback }