OBIEE Interview Question- Performance Tuning / Top 10 Performance Tuning Strategies in OBIEE

One question that I will ask regularly in an interview is regarding Performance Tuning in OBIEE. The question is quite broad and I want to see how, as an OBIEE developer the candidate has worked towards Performance Tuning on various aspects of BI.

I am looking for the following pointers in the answer which will show the overall awareness of performance tuning in a BI project.  You should not only talk about the performance tuning at the OBIEE level but also about databases, the network, and the ETL.

1.  The first and most important and obvious one is to develop a cache management strategy.

I like to hear about how the NQSconfig.ini cache parameters were altered. If you mention about only ENABLE parameter, that’s not enough for me.  I would like to hear about MAX_ROWS_PER_CACHE_ENTRY, MAX_CACHE_ENTRY_SIZE, MAX_CACHE_ENTRIES, POPULATE_AGGREGATE_ROLLUP_HITS, USE_ADVANCED_HIT_DETECTION, MAX_SUBEXPR_SEARCH_DEPTH,  and Cluster- aware cache parameters if you have knowledge/worked on such environment.

I will talk about how the tables are made cacheable. What strategies were implemented to purge and seed caches? In most cases we will use an iBot or a chained iBot or an automated script to build cache for a suite of queries frequently used by business. We also remove the WHERE clauses so that a when filter is created at report level, it still fetch data from cache.

At this time you can also speak about event polling tables and how it was implemented.

2. Create aggregate tables in the database and use aggregate navigation

The aggregate tables are made in the DB for a faster retrieval of data. Sometimes we create materialized views as well. The aggregate navigation and mapping should be done correctly at the BMM layer as well.

3. Implementing partitioning strategies at the DB level.

Usually an OBIEE developer will not be doing this but we must be aware of partitioning, the types of partitioning and how can partition help in BI projects.

4. Creating indexes in the database; this of course is done in consultation with data modelers/DBA.

5. We should also limit the number of initialization blocks for session variables as the initialization blocks are run as soon as a user logs in. In OBIEE 11g, the initialization block run/initialize as and when needed and as such do not take long time to log in.  

6. We turn off logging for all the users as writing log files consume memory and reduces performance.

7. The performance is greatly improved if push calculated measures in database. During ETL we create new calculated columns which will store pre-calculated values instead of calculating at BMM layer or Answers/report level.

8. Using database hints will also improve performance.

9. We also implement usage tracking model in the BI system. This helps us find how the users are using the BI system. We also determine the frequently run queries/dashboards and the queries creating bottlenecks. This helps us in building better strategies for performance tuning.

10. Last but not the least is the log files generated. This also helps us analyzing the actual physical SQL queries generated by the BI Server. Using Explain Plan feature helps us determine if the queries generated are optimal.

I ask readers to please comment and share your views and experience on strategies for performance tuning in OBIEE. 

Cheers!

Deepak