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.

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

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

Datawarehouse Design, Best Practices & Pragmatic approach

Datawarehouse Design, A Tool Independent Pragmatic Approach

I always asked myself during my consulting career what would be the best steps to Design a  Datawarehouse  & after musing for a while I came up with this couple a years ago which I had in my personal diary which am going to digitize today.

I always ask myself what can BA’s & Power users gain from a data warehouse ?

• Firstly it can enhance business productivity as we can see the organization’s information quickly & efficiently using a unified repository tool.
• It may provide us information to win over competitors by adjusting our prices & improving our performance.
• The data warehouse improves the CRM because it consistently provides business information across all business group hierarchies, departments & domains to make informed decisions possible.

Implementing a decision support system is like building a magnificent skyscraper which requires strategies of an Architect, Owner & Implementer / contractor.

Anonymous

To build an efficient data warehouse following 4 strategic perception should be combined together

• Datawarehouse Strategy It’s a process of identifying the fact tables, Dimension tables & ETL push-pull strategy, It represents the aggregates to be calculated during the ETL, database indexing strategy, database partition, DW refresh strategy & more.
• The Power User Strategy This is a view from the eyes of the BA, Power users & Customers on what information should be included in the warehouse for the current & future business requirements
• Sourcing the Data This is a strategic view of the Data Modeler he decides on how to gather & model te silo’s of data using specific data modeling techniques & tools like OBIEE, Cognos & Microstrategy
• The End User Perspective is to view & relate the data to business query

So the Datawarehouse design can be attained by a top down approach a bottom up approach or a combo,].

In the Top down approach the technology or DW tool is mature in the market and this approach starts with an over – all  Inception analysis & planning phase where problem definition is apt.

In the Bottom up approach starts out with POC’s & experiments

In the Combo approach we can use the strategic planning of the top down approach with the rapid implementation technique of bottom up to increase ROI.

Datawarehouse Design Process:

• The first decision is to choose a Subject Area or a business process to model, This Subject area is a functionally related Dimensions & Facts together examples are Orders, Invoices, Shipments, Inventory, LOB Performance & more.
• The second decision is to choose a Fact Table Granularity because te DW front end testing is done in at the lowest granular level for example transactional details of a Customer on a monthly snpshot or a quarterly snapshot.
• The third step is to Choose functionally related Dimensions that would eventually become a part of the  Fact.
• Te fourth Step is to Choose the Meters & Metrics, A meter can be defined as a collection of functionally dependent metrics.

Implementation Steps for a DW / DSS:

• Inception Analysis & Problem Definition.
• Requirements Study.
• DSS / Warehouse Design which comprises of Data Modeling, Database Modeling, Meta Data Repository Modeling, ETL Strategy & more.
• Integrating the Data & Regression Testing.
• Deployment, Training & Maintenance.

http://www.google.com/#hl=en&q=obiee+best+practices&aq=0&oq=obiee+best+&aqi=g3&fp=KxYPMM6r3XA

Oracle Business Intelligence Application Development Lifecycle

Business Intelligence Software Development Lifecycle