The myth of OBIEE performance tuning

It is very common for people to think that when reports run for excessive amount of time, there is configurations or performance tunings that need to be done or enhanced witin OBIEE. It is as if OBIEE has the magic key to faster and better performance. At the surface level, the configurations and reports are done in OBIEE, it is natural for most people to think that the problem needs to be solved in OBIEE as well. That is fair.

However, when looking at reports with poor performance, there could be a lot of reasons behind it. OBIEE in essence creates SQL query that executes directly against the database, we can ensure the SQL query to be free of unnecessary clauses and nesting statements by following the best practice in OBIEE design. Yet, this only takes care of some of the the performance issues. It is true that OBIEE has several out of the box functionality for performance related needs, such as caching capability, aggregate tables, changing the log level and so on, but none of these are magic bullets in dealing with performance.

Caching is widely used in OBIEE projects. It offers help in query responses time by storing queries in cache. We can automate the process of refreshing caches by event pulling tables, or we can pre-run a lot of queries for cache seeding by using scheduled IBots. All these can maximize the benefit of caching reports. However, when entering into the OBIEE world, I can safely say that we are entering the Ad-hoc world. So in this case, caching isn't going to help completely.

Aggregate tables allow OBIEE to create separate tables aggregated at defined dimensional level, hence reduces the size of the record set. When certain queries that need to get data at that level, it can have an option to query smaller tables and return results quicker that way. This surely seem a pretty good option that deals with certain requests (but we know not for all). However, creating more physical tables mean spending more resources, and taking up for database spaces. If going down this path in the long run, the business needs to understand their infrastructure and know what they can afford in the long run. Therefore, aggregate table isn't going to help in all situations.

Everything else that can be done within OBIEE configuration is just be ensure the BI server can generate the best query to be executed. If the request runs for ever, we will need to look at the query and understand why it is running slowly. This becomes a question to both the business and technical people.

What is this query going to give you? What value will it add to the business with this report? I have seen requests composed of 45+ columns in one report. They would run for 30 minutes and come back with error, basically the number of records returned was too large. We can certainly increase the default setting of 65K in OBIEE to take care of the error part, but it will never prevent the error for good because the data is likely to increase over time. We can definitely spend lots of effort and time to tune the query or even schedule to cache this report everyday, but it will not help when Adhocing. More critically, if we decide to go down this path, we are leading the business to a wrong direction, and I can ensure that there will be more similar queries to tune in the future, which will never end. Therefore, the first step in making sure the reports are running fast is not to be in a situation where we have to deal with performance killing requests. This is not an easy thing to do because it involves on-going education to the business world about BI concepts and practices. The whole point about business intelligence is to avoid having to data dump millions of records into user's computer for them to slice & dice, it is about slicing & dicing data for them. It is a mindset change that will take time, but can't be done without clearly conveying the purpose and value of BI in the business world. Internet changes the way people obtain information, but the change didn't happen over night, same with BI.

From a technical perspective, when a fairly decent size request is taking a lot of time to return, it can be caused by a lot of things. How is the server machine's CPU usage? Does your database support the type of query you are running? Are you actually performing multiple requests and then union them? What type of table or view this query is running again?

One of the things that I like to do first is bring down the request into smaller ones that only consist of 1 joining operations to see which join is very slow. Chances are, if the query is taking for a long time, it is likely that the query is executing against huge data sets that are joined up. Knowing which join is slow can help us pinpoint the tuning target. For example, I have 1 report that has 12 columns from 4 tables running for 40 minutes in answers. By looking at the Sql statement of this request, I don't see anything particularly wrong in the query. Therefore, I decide to break it down to 3 columns from 1 dim and 1 fact table. The query is running with decent speed. Then I delete the columns from this dim and replace them with those from the second dim table in the original request. The query now is running for 25 minutes. So now I have my focus on investigating why querying these 2 tables take such a long time. Therefore, if i were to create Indexes on the joining columns, I at least when where to create it on. If the table size of these 2 tables are huge, then obviously the join between the 2 will be slow. If worse, these are actually views across more physical tables in the database that i am querying against, then the performance will obviously be slow. Therefore, we need to have detail understanding of the environment in order to determine the best thing to do.

There is no one simple change in the database or OBIEE that will eliminate performance issues one for all. When reports are running slow, we can't just tell the business to change their reports nor can we just throw it to the DBA to deal with it. Instead, we need to all work together and come up with the best solutions for all. Creating Indexes, hints or materialized views are easier to say than done without considering the impacts on the infrastructure side in the long run. In certain cases, capturing certain data may only be done using views to avoid re-writing the ETL process and other jobs. Therefore, the business would need to understand the situation and be willing to compromise certain things if they want better performance. On the other hand, we also have to thoroughly understand the business requirement to determine if there is any workaround available, only then the message can be accurately communicated and the expectation can be effectively managed.

In summary, the topic of performance tuning in OBIEE is a very broad topic that becomes performance tuning in general. There is no one solution or simple configurations that will take care of all the performance issues. It's all about cooperating, understanding, compromising, working around and coming up the best solutions for the project in the long run. It requires deep understand of the nature of one's project environment, data structures, project flows, business in order to come up with the best technical process and business practice in order to be on a good project path to start with. No matter how advance the tool is, if we are dealing with billions of records that outer joins another billions, we are going to have to wait.