OBIEE Interview Question- Dimensional Hierarchy

What is dimensional hierarchy? How you define number of elements at each level? Can it be done automatically?

A dimensional hierarchy is created in the BMM layer of a repository to calculate level based measures and to create interactive reports/dashboards with drill down functionality.

A dimension hierarchy can be created manually as well as automatically. Manual process involves right clicking on BMM layer> New Object> Dimension, entering a name; right clickking on this new dimension and create New Level.

Levels are base on the business definition or hierarchy of a dimension, a classic example; year> quarter> Month> week> Day. A hierarchy will have at least two levels, Grand Total Level and Detail Level. For each level we have to define the number of elements. This is always 1 for the Grand Total Level and next levels are the ratio from one level on top to the bottom.

When creating a dimension automatically, we have to right click on the dimension and click Create Dimension. This will automatically create the two levels Grand Total and Detail Level.

We can actually automatically calculate number of elements at each level. The procedure is to open a repository in online mode. Right click on business model or dimension hierarchy and click on estimate levels.


Cheers!
Deepak

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

Just another OBIEE Blog or more?

Hello Everyone!

After noticing a lot of people searching for hours to find answers to their queries on OBIEE, I thought of posting my experiences with OBIEE on this blog. I will cover important topics in OBIEE in my posts. I find newcomers to OBIEE especially concerned about the interviews, so I will try to discuss important interview questions on these areas as well. In the process I hope to meet new people and seek their ideas and comments. 

Thank you!
Deepak