Five Points to Overall OBIEE Performance Improvement

Key Pain Points of OBIEE Users

• Poor analysis performance
• Using analytical tool as reporting tool
• No data quality processes
• Has no reliable source of historical data
• Time consuming ETL process
• Manual, error-prone processes
• No single source of truth for data
• No clear road map for BI implementation
• Ignoring the true end users
• Lack of user adoption due to poor user interface

Performance Improvement Areas

1)Dashboards

• Simplify the dashboard
• Manage screen real estate
• Create processes, not reports
• Avoid overly complex queries those return too much data
• Dashboard should be as interactive as possible:
  • Column selectors,
  • Contextual drill-down,
  • Guided navigation

• Do not overuse dashboard conditions, it has a cost on performance
• Limit the number of metrics in analysis
• Remove unused columns
• Benchmark KPIs
• Allow the audience to pose business questions rather than using technical language to derive their metrics
• Use intuitive naming conventions
• Restrict update
• Mandate documentation
• Consider using a default template for dashboard design

2)Answers

• Queries Design
  • Maintain optimum number of columns required in report definition
  • Continue all aggregations at the report level, and not in views
  • Continue all column formatting at the report level, and not in view

• Filters
  • Do not include hard-coded values (dates, attributes, values) in report filters
  • Consider the need for an is-prompted operator filter on each report
• Views
  • Maintain only the minimum number of views needed for the report

• Use view selectors only& when a report can expose both a set of summary graph views and a detailed table with granular dataUse view selectors onlywhen a report can expose both a set of summary graph views and a detailed table with granular data

3)RPD Development:

• If working in online mode, save your work frequently
• User physical diagrams in the Admin tool to verify the sources and joins
• Decide whether row level data security is more appropriate in database of RPD

Physical Layer:

• Import metadata from source using Admin tool Wizard
• User alias frequently to follow the naming standards and to eliminate extraneous, circular joins between source tables.
• Use an opaque view (a physical layer table that consists of a SELECT statement) only if there is no other solution to your modeling problem
• Restricting the number of rows in physical table using WHERE clause: By limiting the number of rows returned from the data source improves the query performance
• Use of cache: If same query is getting executed by number of users frequently, it is recommended to implement caching mechanism and maintain the cache life cycle with little administrative overhead
• Optimized use of initialization blocks: This restriction will improve the performance because the queries in initialization blocks are executed when Oracle BI Server is started and users log into the server, which uses BI Server bandwidth heavily
• Optimized use of table type SELECT: This will may avoid lengthy inner queries and number of select statements executed by the BI server

BMM Layer:

• Create the business model with one-to any logical joins between logical dimension tables and the fact tables wherever possible
• The business model should ideally resemble a simple star schema in which each fact table is joined directly to its dimensions
• Every logical fact table must join to at least one logical dimension table
• Every logical dimension table should have a dimensional hierarchy associated with it
• When creating level-based measures, make sure that all appropriate fact sources map to the appropriate level in the hierarchy using aggregation content
• Aggregate sources should be created as separate logical table sources
• Each dimension level in a hierarchy must have a unique level key
• To prevent problems with aggregate navigation, ensure that each logical level of a dimension hierarchy contains the correct value in the field named Number of elements at this level

Presentation Layer:

• Because there is no automatic way to synchronize all changes between the BMM layer and the Presentation layer, it is best to wait until the BMM layer is relatively stable before adding customizations in the Presentation layer
• There are many ways to create subject areas, such as dragging and dropping the entire business model, dragging and dropping incremental pieces of the model, or automatically creating subject areas based on logical stars or snowflakes
• It is a best practice to rename objects in the BMM layer rather than the Presentation layer, for better maintainability. Giving user-friendly names to logical objects rather than presentation objects ensures that the names can be reused in multiple subject areas. Also, it ensures that the names persist even when you need to delete and re-create subject areas to incorporate changes to your business model
• Be aware that members in a presentation hierarchy are not visible in the Presentation layer. Instead, you can see hierarchy members in Answers
• You can use the Administration tool to update Presentation layer metadata to give the appearance of& nested folders in Answers
• When setting up data access security for a large number of objects, consider setting object permissions by role rather than setting permissions for individual columns

4)Performance of Queries

• User must request the realistic performance of any query, so the message must be conveyed to end user
• Constant monitoring of end user query habit is mandatory, and based on that appropriate changes can be done in the OBIEE architecture
• Be proactive to detect any OBIEE performance issues before it’s too late
• Data federation comes with query performance cost, maximize the use single of data warehouse store

5)High volume of data

• It is very common to have fact tables with millions of records, so when joined with dimension tables, the performance of the query degrades a lot. If a huge table is divided into multiple smaller tables, naturally the performance improves a lot. The tables can be divided into various functional ways

1)Fact Based Partitioning: – if partitioning is done like data related to fact is stored in different tables

2)Level Based Partitioning: – If the same facts (measures) are stored in different or separate tables at different levels of aggregation. The data inside level based partitioned table is always calculated to a specific level of aggregation

3)Value Based Partitioning: –The values of the data partitioning is possible, and can partition data into separate tables

• Aggregation Persistence Wizard: – OBIEE provides in built wizard in the Admin Client tool that helps to generate scripts those will create aggregate tables and populate them. Most of the dashboards run on the aggregated data, if this data is aggregated on the fly using BI Server bandwidth dashboards will perform slowly.