OBIEE Caching Configuration

OBIEE Caching Best Practices

One of my recent discussions with a colleague on the caching strategy for OBIEE resulted in the following Best practices, Oracle BI Server is an Intelligent Query Engine that stores database hits in a cache file, This cache file is stored on the BI server.

OBIEE Architectural Best Practice feature is to implement the caching mechanism by using the following methodology where in the configuration tags can be set in optimal fashion as follows:

• Enable: turns caching on/off

• Data_Storage_Paths: defines location to store result files

• Metadata_File: defines location for cache metadata file

• Replace_Algorithm: for discarding entries if cache full

• Buffer_Pool_Size: buffer for caching metadata file

• Max_Rows_Per_Cache_Entry: upper limit on rows in result

• Max_Cache_Entry_Size: upper limit on size (#rows*#bytes/row)

• Max_Cache_Entries: upper limit on #of cached queries

The Following is an in detail Architectural configuration changes that can be implemented for OBIEE Caching

1. Parameter: Enable

• Turns caching on/off

Best Practice

Set to YES if you want caching

1. Parameter: Data_Storage_Paths

• Defines the directory or directories to store cached result files

• Provide location and capacity

  • DATA_STORAGE_PATHS = “d:\OracleBIData\nQSCache” 500 MB

• Least-recently-used cache is purged if full capacity

Best practice

• • use dedicated drive(s): performance and reliability

  • use local disk (not a file share). (Not enforced)

  • capacity should be significantly larger than value of Max_Cache_Entry_Size

Caveats to be kept in mind

• • Disk space must exist (or bad things will happen)

  • Capacity of each location must not exceed 4 GB (2 GB before 7.7)

1. Parameter: Replace_Algorithm

Algorithm used to purge cache entries when the cache is full

“Full” is either:

1. 1. Max_Cache_Entries have been created

   2. Less than Max_Cache_Entry_Size space is available

Removes cache entry that has not been accessed for longest time – not necessarily the oldest “created” cache item

Only choice is LRU (least-recently-used)

1. Parameter: Buffer_Pool_Size

• Defines the amount of memory for caching the cache metadata file.

• Parameter does not affect correctness/behavior of cache – purely a performance setting

Best practice

• Don’t change the default value. No/limited performance gains possible.

1. Parameter: Max_Rows_Per_Cache_Entry

• Defines upper bound on number of rows in a cached result set

• Prevent large or “runaway” queries from consuming too much cache

• Query will run to completion, but if limit exceeded result will not be added to cache – event is not logged

• Set value to 0 if no limit is desired

• Very large cache files are inefficient

  • stored in single file on disk

  • No indexes – full sequential scan to access

Best practice

• • Define a non-zero value (less than 1,000,000 if possible)

  • Max_Cache_Entry_Size is best place to define space limit

1. Parameter: Max_Cache_Entry_Size

• Defines limit on size (#of bytes) of a cache entry

• Used to prevent large cache entries from being created. Query will not be cached if exceeds this limit. No logging of exceeding limit.

• Size: #of rows times #of bytes/row

• #of bytes per row calculation:

  • Unicode expansion (2x or 4x multiplier for char and varchar columns)

  • Column alignment overheads

  • Null value representation overhead

• Cache is purged until Max_Cache_Entry_Size bytes are available

Best practice

• • Set value to at most 10% of cache capacity (of smallest cache directory)

  • More effective limit than Max_Rows_Per_Cache_Entry or Max_Cache_Entries

Caveats

• • Default value (1 MB) is fairly small. Many queries will hit this limit.

ABC’s of Application Integration Architecture

This is an excellent article on oracles next generation Application Integration Architecture it gives us a broad overview of AIA.

http://www.tusc.com/oracle/collateral/ebs/WP-ABCs-of-AIA.pdf

How to Improve Data Quality In a Tight Budget

This is an excellent article linnk by David Loshin discussing on How to improve Data quality in a tight budget scenario to maximize ROI:

http://searchdatamanagement.techtarget.com/generic/0,295582,sid91_gci1363080_mem1,00.html?track=NL-520&ad=722696&Offer=mn_eh082609DTMGUNSC_DQguide&asrc=EM_USC_9063881

How to Secure Oracle BI Adhoc Analytics / Answers

• This Question pops up on the OTN forum on how to secure Oracle BI Adhoc Analytics & Answers functionality, My recent project was an integration warehouse effort and had more of adhoc analytic’s application approach.

• This was my approach on this

• Create an OBI web catalog group named ‘All Functional Area Request Developers’ using ‘Manage Presentation Catalog Groups and Users’. This role will only exist in Presentation Services and be used as a way to consolidate all the individual roles for each functional area.

• This name has spaces and is mixed case so that it is distinguishable from the roles that are synchronized with the warehouse, similar to the Presentation Server Administrators group.

• Create a role web catalog group for each functional area. The role should be named ‘BI_REQUEST_DEVELOPER_XXXXXX_RL’ where XXXXXX should be descriptive of the group using answers. The descriptive part (XXXXXX) may be less than six characters, but not more.

• Each of the functional area roles must now be added to the ‘All Functional Area Request Developers’ web catalog group using ‘Manage Presentation Catalog Groups and Users’.

• Remove privilege from Subject Area for Everyone. Grant read access to each Subject Area in Answers to the appropriate functional area roles. Dashboard viewers do not need access to the Subject Area.

• Grant Answers privilege to the ‘All Functional Area Request Developers’ web catalog group.

• Create a folder:/shared/Functional Area Requests

1. 1. Permissions on this folder:All Functional Area Request Developers – Traverse or Read?, Dashboard Developers – Read, Presentation Server Administrators – Full Control

2. Create sub-folder for each functional area named: XXXXXX Requests

   1. Permissions on this folder: BI_REQUEST_DEVELOPER_XXXXXX_RL – Create/Modify, Dashboard Developers – Read, Presentation Server Administrators – Full Contro

   In addition to this we can also come up with an idea to create a role based connection pool to the database to suffice this Business requirement.

Apt Properties for Oracle BI Dataase

This is an excellent link that gives us the apt properties for the Oracle data base for an OBIEE implementation

http://download.oracle.com/docs/cd/E12104_01/books/AnyInstAdm/AnyInstAdmPreInstall7.html#wp1066442

Modeling Tools Vs Spreadsheets

This  is a great article from TDWI discussing the pros & cons of using a Modeling tool versus a spread sheet and when the business needs to go for the Modeling tool.

http://www.thttp://www.tdwi.org/News/display.aspx?ID=9567dwi.org/News/display.aspx?ID=9567

Sun Microsystem OBIEE Strategy

View more documents Raj Guttikonda.

Articles

http://www.enzeecommunity.com/message/2822

OBIEE Datawarehouse Design Process Iterations

OBIEE Data-warehousing Design Process Inception Phase

Designing the Data Warehouse effort:

The Best practice iterations to design a Data warehouse can be as follows

1. Configuring an Oracle or DB2 or MS Sql Server database for use as a data warehouse

2. Designing the Warehouse if the Warehouse supports more than one LOB the Data Modeler can come up with a Physical warehouse design based on Conformed Bus Architecture with conformed dimensions & Conformed facts which allow the IT to reuse the Data sets across the Business.

3. Managing Schema objects such as Tables, Concurrent programs, Sequences, Schema, Disks, Views, Indexes & Materialized Views

4. Managing & Maintaining the User authentication its a DBA responsibility

5. Developing routines, Streams, ETL Process using an ETL tool or a Push Pull concurrent program code to Leverage operational data resemblance

6. Managing change data capture using tools like CA -7 or Siebel DAC & more

7. Creating Metadata repository, BMM Layer Meta data repository, User Authentication & Authorization Strategy through OBIEE or LDAP or Data base authentication.

8. Backing up the data base & Performing recovery & leveraging fault tolerance

9. Monitoring data-warehouse’s performance using OBIEE tool caching & performance tuning strategies

OBIEE Data-warehousing Design Process Inception Phase

Designing the Data Warehouse effort:

The Best practice iterations to design a Data warehouse can be as follows

1. Configuring an Oracle or DB2 or MS Sql Server database for use as a data warehouse

2. Designing the Warehouse if the Warehouse supports more than one LOB the Data Modeler can come up with a Physical warehouse design based on Conformed Bus Architecture with conformed dimensions & Conformed facts which allow the IT to reuse the Data sets across the Business.

3. Managing Schema objects such as Tables, Concurrent programs, Sequences, Schemas, Disks, Views, Indexes & Materialized Views

4. Managing & Maintaining the User authentication its a DBA responsibility

5. Developing routines, Streams, ETL Process using an ETL tool or a Push Pull concurrent program code to Leverage operational data resemblance

6. Managing change data capture using tools like CA -7 or Siebel DAC & more

7. Creating Metadata repository, BMM Layer Meta data repository, User Authentication & Authorization Strategy through OBIEE or LDAP or Data base authentication.

8. Backing up the data base & Performing recovery & leveraging fault tolerance

9. Monitoring data-warehouse’s performance using OBIEE tool caching & performance tuning strategies

OBIEE Sizing OBIEE Hardware Sizing

BI Hardware Sizing?

One of our recent discussion with a colleague & a client of mine resulted in this we brainstormed on what sizing is & what is the best methodology to achieve apt sizing for an OBIEE Hardware Environment.

I know there are big players like IBM with their Sizing & vending strategies & questionnaires to make it complex but here is a basic understanding.

A sizing is an approximation of the hardware resources required to support a specific software implementation.

It is important to understand that “sizing” the hardware for each customer’s Oracle Business Intelligence Enterprise Edition implementation is an iterative process, which may be refined and repeated a number of times. If you are in the early stages of planning, you will probably have limited information about your planned environment. In this case, we can complete the sizing estimate with general information about your Oracle Business Intelligence Enterprise Edition applications users.

The steps that we need to keep in mind to develop the right sizing is to figure out the right thorough put that may be needed when our System Goes Live for Production to the Max number of users,

Throughput is the maximum number of queries & data load that our BI Presentation Services, BI Server, BI Scheduler, The Database Server Machine can handle for optimal performance there a quite a few tools out there which can be used to determine the thorough put

The thorough put is determined in a Firewall free demilitarized zone.

Thorough put is The amount of data that is being accessed by queries hitting the system at peak time, in conjunction with the acceptable response time. You may be able to use throughput numbers and experience from an existing application to estimate the required throughput.

As a rule of thumb we can use the following formula to determine the CPU sizing, Ram Memory, Disks

• The Number of CPU’s = ( The Max Throughput / 200 ) In MB

• Memory In GB = 2 Times Number of CPU’s

• System Throughput In MB / Individual Disk Controller’s Throughput Gives Us The Number of Disk Controller’s