Saturday, 16 August 2014

OBIEE Performance Monitoring with Usage Tracking

We provide the reports below and the OBIEE repository implementation deployed completely free of charge! Find the details for obtaining them at the end of this article.

Oracle BI application operating and users often ask for a simple solution to monitor overall OBIEE analysis and report performance. Question often comes up when discussing service level agreements (SLAs) for Oracle BI. SLA discussions for BI applications can become very complex, because there are many factors which impact reporting performance like database/application server hardware, data growth, number of concurrent users and complexity of user initiated queries ... just to list a few of them. When initiating BI projects normally information on these topics isn't very accurate. Therefore sizing is often a best guess on imprecise preconditions. Furthermore you shouldn't declare "Any report must return results within 10 seconds!" in your SLAs. That's easy to break.

Well, BI and of course OBIEE solutions generally evolve. You will have to monitor and optimise performance regularly. To optimise you need to know what reports users run actually, to be able to decide if you need restructuring (physical or logical). Very often it's sufficient to help users build enhanced analyses. Therefore SLAs are nice, but they don't help you to get a long running optimal Oracle BI solution which causes lowest possible costs.

To get an OBIEE report performance overview we created an Oracle BI performance monitoring solution based on Usage Tracking. Usage Tracking is an internal OBIEE tracking component. Once activated it tracks all data on report performance. For analysing purposes no additional implementation is needed, you just need to retrieve the relevant information. You can obtain the 4 reports within a dashboard and corresponding OBIEE repository for free (see below).

Performance per Bucket
Bucket report returns performance information based on categories. The chart tells you immediately that 87% of queries succeed within 30 seconds and 96% of queries succeed within 2 minutes.

OBIEE Performance per Bucket

Detailed table provides different figures for each runtime category:
Query #: number of queries
Cache Hit #: number of queries having at least one cache hit
DB Query #: number of queries executing only database requests
RT Avg: runtime average (seconds)
RT Cache Avg: runtime average for queries having at least one cache hit
RT DB Avg: runtime average for queries executing only database requests
Rows Avg: average number of selecting data rows per query
Cache Hits per Query: average number of cache hits per query
DB Queries per Query: average number of database requests per query

Performance per Hour
Hour report shows intraday system workload and runtime average.

OBIEE Performance per Hour

Performance per Subject Area
Usage of subject areas and corresponding runtime.

OBIEE Performance per Subject Area

Performance per User
Most active users and how OBIEE system serves their requests.

OBIEE Performance per User

Release Notes v1.0
The reports are integrated in a dashboard to be able to select specific time periods. Oracle BI repository and reports are based on OBIEE 11.1.1.7.1. Reports are additionally provided in OBIEE XML structure. For installation or downgrading support just contact us.

As being the first release for OBIEE performance monitoring we are aware that there are many questions not covered yet, especially for detail reporting. Please feel free to send us your specific analysis requests for Oracle BI report performance monitoring. We will try to integrate them in the next release.

Release Notes v1.1
Added additional filters for Date, Subject Area and User Id to the dashboard prompt allowing you to query for detailed data within each analysis. This should provide good means when users complain about performance.

Obtain OBIEE Performance Monitoring
As already mentioned we provide all relevant configuration files completely for free. Just contact us or send an e-mail to info@ga-itbs.de. Please provide your company contact data. We don't use the data for marketing purposes. We just want to know who is using our OBIEE performance monitoring solution.

Please add us to your network: TwitterLinkedInFacebookGoogle+
We will share additional information, updates, etc. via our social sites.

We allow you to use and modify our solution fully or partially. You can provide it for free to your clients. In any case you agree to our terms of use as follows and you will integrate terms and conditions into each of your OBIEE monitoring analysis based on ideas or code from our solution (just copy it from the Static Text view of one of our reports):

© OBIEE performance monitoring v1.1 by G.A. itbs GmbH - OBIEE expert training and consulting. Terms of use: Free of charge if this declaration, copyright and links above are not removed. Reselling prohibited. We disclaim all implied warranties with regard to this monitoring model or any analyses based on this subject area. You agree to indemnify, defend and hold us harmless from any liability, loss, claim and expense (including reasonable legal fees) related to the use of this software. You can change the implementation and/or provide it to your clients if these terms and conditions are observed and agreed by all parties involved. Furthermore terms will be integrated into each OBIEE monitoring analysis based on ideas or code from this solution. Documentation and updates are provided in our blog.

1 comment:

  1. Hi,
    Nice article.. but I would also try to include Impact analysis subject area in this. Impact analysis would be a data lineage document. If a RPD column is deleted, what all reports will be impacted. This was one of the solution which I have provided to my client and got a good response from client. Let me know if you need more details.
    Thanks
    Sai Kumar

    ReplyDelete