Wednesday, September 29, 2010

Interview Question: OBIEE Project Life Cycle

People will usually ask an experienced OBIEE resource if she has full life cycle implementation experience in OBIEE and to tell about the phases in the BI project.

As an OBIEE Developer, not everyone work during the whole life cycle of the project. This is especially true for contractors. Considering the RUP there are four life cycle phases of a project- inception, elaboration, construction, and transition. As an OBIEE developer you will be mostly working during the designing, developing, testing, and implementing phase. Sometimes there is involvement in requirements gathering phase but more chances are to work in the refining the requirements.

After explaining this you can tell about the project life cycle as below:

1.  The initiation/inception phase: Creating business case, Project planning and feasibility study.
2.  The elaboration/planning phase: Resource Planning, Requirements gathering and analysis
3. The execution/development phase: Design and development. This is where as an OBIEE developer I worked the most.
4.  The transition/closure phase: Deployment, operations and maintenance.


Thursday, September 23, 2010

Interview Question: OBIEE Architecture

One question that is very interesting to know in an interview is about the OBIEE Architecture.

The following is a very interesting and informative link to understand the OBIEE Architecture.

You can talk about the OBIEE 10g as well, below are some informative links.

When talking about the OBIEE Architecture, I am not only looking for a description about all the components present in the OBIEE but also how they communicate with each other.

Pointers: talk about the OS you used, Application Servers, Semantic Layers in OBIEE, Database, Authentication (Active Directory etc.) etc. and communication protocol.

An in depth knowledge about the architecture assures me that the candidate is well versed with not only the tool but have an overall picture of the BI at the higher/enterprise level.


Thursday, September 9, 2010

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.