How to do Server Caching in OBIEE

 

Why OBIEE Server Caching?

1. Improve Speed of Queries

2. Reduce Network I/O

3. Reduce Database Processing

4. Reduce System Cost (charge back environments)

5. Reduce OBIEE Server Processing

Caching Strategy

1. Volatility of Data

2. Granularity of Data

3. Predictability of Changes

4. Volume of Queries

5. Purging

6. Seed Cache

Volatility of Data in Caching Strategy

• Volatility pertains to the frequency of data change in your environment. Volatile data changes more frequently (relative) than non-volatile data. Typically, the benefits of OBIEE server caching diminish as the data becomes more volatile. In other words, the amount of effort and processes to keep the cache synchronized with the data outweigh the benefits.

• In volatile environments, effort should be concentrated to performance tuning at the database level through a well-designed data model, physical architecture, indexing, and database caching strategy.

Granularity in Caching Strategy 

• Tied directly to volatility is granularity, the level at which the changes occur. Do all of your star schemas (subject areas) change during the same batch window or do some star schemas change during different batch windows or maybe even on different days? What about individual tables that may change independently?

• Less granular change is desired. A system where all star schemas are updated during the same batch window is much easier to maintain and will provide more predictable performance for end users given the ability to center a caching strategy around 1 batch window 

Predictability of Changes in Caching Strategy

• The level of confidence in which you can predict changes to your environment can greatly influence your caching strategy. Change which is very predictable allows the most flexibility when designing your strategy and typically provides the most consistent performance for end users. A more dynamic and often complex strategy must be developed for Data that changes intermittently or on demand.

• For example, a system in which all data is updated always by 2am is less volatile and more predictable than a system that gets updated 3 times a day typically at 8am, 4pm, and 11pm. Even less predictable and more volatile is a system where on-demand updates occur as needed.

Predictable changes are desired. Strive to develop a system where data changes occur at predetermined times.

Volume of Queries in Caching Strategy

• Systems with high query volume are typically great candidates for OBI Server caching especially if the majority of the user community is focused on dashboard reporting rather than ad-hoc Answer analysis.

• The volume of data being retrieved by queries also needs to be considered in your caching strategy. You will need to determine dataset size and row count thresholds in your environment that when breached will not allow the dataset to be cached. By doing so you will prevent flooding the cache with datasets that likely have a narrow audience.

Purging Options in Caching Strategy 

Once you understand how your data changes from a volatility, granularity, and predictability perspective, you are ready to determine how you would like to purge cache from your environment which no longer reflects the underlying database. Listed below are some of the methods for purging server cache.

• Cache Manager (manual)
• Polling Table
• Table Level Configuration
• Repository Variable
• ODBC procedures

Cache Manager

Cache can always be purged manually through the Cache Manager (accessed through the Manage file menu of the Administration Tool). Utilize the Cache Manager to clear cache when ad-hoc updates to data occur as well as updates that occur outside the prescribed change windows. 

Purging data via Cache Manager is a common practice in pre-production environments when testing your caching strategy. However, avoid using Cache Manager as your steady state production strategy for clearing cache (exceptions only!)

Purging –Polling Table

Polling Table

A polling (or event) table is a popular solution for automatically purging data cache based on changes to data tables. Using a predefined polling table structure, rows are inserted (your responsibility) into the table after database changes are made. The BI Server reads the table and purges cache entries corresponding to those entries.

Follow these procedures to set up the polling table:

• Create the table (ddlin Server Admin Guide)
• Grant select, insert, update,delete privileges on the table
• Import the table into the physical layer
• Declare the table as a polling table and set the polling interval
• Can be accessed via Administration Tool…Tools…Utilities…Oracle BI Event Table

A polling table is one of the most accurate and reliable methods to clearing data cache.

Purging –Table Level Configuration

Table Level Configuration

• You can choose to set a purge interval when declaring a table „cacheable‟ from the physical layer. The default is to not expire the cache. Query cache involving multiple tables with varied table purging intervals will be purged using the minimum interval declared for the tables involved in the query.

• Table level purging is based on cache duration not specific purge times and as such usually lacks the precision needed for purging cache in production systems. However, a good use of table level purging are current events tables which are updated frequently and typically contain „FYI‟ data which is not mission critical in nature.

Purging –Repository Variable

Repository Variable

• When OBI refreshes repository variables (via initialization blocks) it will examine the business models to determine if they reference the repository variables. If the business models reference the repository variables, all cache entries for those models will be purged.

Purging cache via repository variables is a very simple but effective „blanket‟ strategy.

Purging –ODBC Procedures

ODBC procedures

• Four ODBC procedures are provided with OBI. These procedures can purge at the query, table, database, or „all‟ levels. Similar to a process for populating a polling table after table changes, the ODBC procedures could be called after ETL jobs to purge data cache. The procedures must be issued via an id with OBI Administrative privileges.

ODBC Procedures:

• SAPurgeCacheByQuery
• SAPurgeCacheByTable
• SAPurgeCacheByDatabase
• SAPurgeAllCache

Caching Strategy –Seed Cache

In systems with low volatility and high predictability it is often desirable to seed server cache to optimize query performance for all users. A common method to seed cache is through the use of iBots. Seed cache with:

1.Common prebuilt queries from dashboards

2.Queries without expressions

3.Queries without „where‟ clauses

4.Other queries which utilize heavy database performance and are likely to be reissued.

1)Each iBot must be sent to at least one user; otherwise, the iBot will not run.

2)Each iBot used for seeding cache must be „Published for Subscription‟.

If you don‟t seed server cache, the first users on the system will typically „take the hit‟ for populating the cache. This can be undesirable for user adoption and can be a source for excess trouble tickets being logged.

Caching Exercise – Step 1

1. Ensure Physical tables have Caching enabled

1) Open the repository in online mode.

2) Enable caching for all physical tables, check in your changes and save the repository.

Caching Exercise – Step 2

Caching Exercise – Step 2

1) Stop all of the OBIEE Services.

2) Make sure caching is enabled in the NQSConfig.ini file.

3) Do not alter any other Cache variables at this time.

4) Start all of the OBIEE services in the proper sequence.

5) Once the services have been restarted open the Cache manager via the Administration Tool.

Caching Exercise – Step 3

1) Ensure there are no Cache entries in the Cache Manager by opening the Administration Tool…Open the online repository…click Manage…Cache

2) Keep the Cache Manager open and create a report using Year, Month, Brand & Units. Run the report once.

3) In the administration tool refresh the Cache Manager screen to view the new cache entry.

4) Now close all cursors by using the OBIEE Presentation Services administration tool. Go to Settings…Administration…Manage Sessions...and click the „Close All Cursors‟ button.

5) Rerun the report you created in step 2.

6) Refresh the Cache Manager. What has changed? Hint: Use count & Last used date stamp

7) Note that „Close All Cursors‟ button does not clear the cache file. These cursors actually mark the place where this query is logged in the query log file, therefore only a small portion of the log file is displayed when the “View Log” link is clicked on any cursor on this page.

8) Save your report under „My Folders‟ in Answers. You will need it for the next step!

Caching Exercise – Step 4

1. Purge all cache entries through the Edit menu in Cache Manager.
2. With the Administration Tool, modify the FACT table under the „Paint DB‟ database in the Physical Layer - Cache Persistence Time to 1 minute.
3. Repeat running report the report you created earlier using the button in Answers. Be careful of false results or rerunning the query due to “Web cache”. Monitor the Cache Manager „Use Count‟ for more than 1 minute. What happens?
4. The Cache Use Count will reset back to 0 after executing a query involving the Fact table where the Cache is greater than 1 minute old.

It is a best practice to NOT use caching in development environments where data volatility and predictability are difficult to control. Save caching for performance testing, UAT, and production environments!

Summary

A system that changes with low volatility at a high level of granularity on a prescheduled basis will facilitate the simplest, most reliable caching strategy.

Listed below are the popular configuration parameters (NQSConfig.ini) which affect server caching.

• ENABLE –enables or disables caching feature
• DATA_STORAGE_PATHS –directory paths to caching files
• MAX_ROWS_PER_CACHE_ENTRY –row threshold over which a result set will not be cached.
• MAX_CACHE_ENTRY_SIZE = size threshold over which a result set will not be cached
• MAX_CACHE_ENTRIES = maximum number of cache entries allowed. LRU strategy enforced.
• POPULATE_AGGREGATE_ROLLUP_HITS = specifies when a cache hit occurs that is aggregated that the aggregated result set is also cached.

Purging the Cache Using a Repository Variable

Repository Variables can be loaded using external tables as a source. They can be used to expire the cache only when the value(s) change.

Why is this important? OBIEE manages cache but doesn‟t know if the data in the cache needs to be refreshed. Using this method forces OBIEE to refresh the cache whenever the data changes –whenever new data is loaded by the ETL!

When the value loaded from the external table –such as “data refresh date” changesfrom the value that is currently stored in the repository variable, the cache will be expired and will be reloaded by the next query.

A connection pool needs to be set up in the admin pool to allow the BI Server to access the table that contains the cache refresh trigger(s).

Typically the table contains Last_Daily_Refresh_Date, Last_Monthly_Refresh_Data, Last_Yearly_Refresh_Date etc.

Logical fields are created in the appropriate tables in the Business Logic Section in the Admin tool. These logical fields acquire their values from the corresponding Repository Variables. When the repository variable value changes, the cache for the table will be expired and not used. A new cache will be created for each affected table.

Caching Exercise 

Double click the shortcut on your desktop to start SQL Developer

Log in to SQL Developer using the ID system and the PASSWORD system

SQL Developer is a tool for creating and updating tables in an Oracle Database. If you are using other databases, you will need to have access to a similar tools for testing purposes.

1. Expand the Oracle XE Database and expand the Tables folder

2. Click on the Repository_Refresh Table

3. Click on the Data tab in the window on the right

SQL Developer is a tool for creating and updating tables in an Oracle Database.

You can view and change the data to test out the repository variables!

Open the Admin Tool and login with ID Administrator and PASSWORD bicg

1. Click Manage

2. Click Variables

Click New => Repository => Initialization Block

1. Click into name and enter “Daily Refresh”

2. Click into Time Interval and change to minutes – (this is for demonstration purposes. You would select Days for daily)

3. Click Edit Source Data

Choose Database as Source type” Click browse to select the connection pool

that connects to the Cache_Refresh database

The Initialization block includes a Scheduler, Source for the data (SQL), Target for the result (Variables) and Execution Precedence. Timing for the initialization block execution should be set to match the data requirements –Daily? Monthly? Etc. This will ensure that the cache is reset only when needed. The Cache_Refreshtable can live in any database that the server can access either through ODBC or Native database connections. A separate connection pool is suggested for performance reasons but it is not mandatory.

1. Choose the Cache_Refresh connection pool.

2. Click the select button.

Note: A separate connection pool has been set up to access the Cache_Refresh table to provide improved performance. Connection pools exist in the Physical

layer.

1. Enter the following SQL 

Select LAST_REFRESH_DATE from REPOSITORY_REFRESH where REFRESH_INTERVAL=„DAILY‟

2. Click the TEST button.

SQL is an acronym. Which of the following is correct ?

A. Structured Query Language

B. Standard Query Language

C. Simple Query Language

Click the EDIT DATA TARGET button – this will allow you to set up the repository variable to receive the data returned by the last step.

Click NEW to set up your Repository variable for the first time

The Initialization block includes a Scheduler, Source for the data (SQL), Target for the result (Variables) and Execution Precedence.

You should develop a standard naming convention for Repository Variables that indicates their purpose.

1. Type Daily_Refresh_Var for the name

2. Enter 01/01/01 as the Default Initializer Click OK

Click the TEST button to execute the SQL. This will return the value to the repository variable.

1. Click the CLOSE button

2. Click OK

3. Save the model

4. The next step is to add a logical column to each table that you would like to have the cache refreshed based on changes to the Refresh_Cache repository variable that we just set up. Right click the Markets table under the business model and choose New Object and Logical Column

1. Enter Cache_Refresh_Daily as the name for the logical column

2. Check the box for Use existing logical columns as source.

3. Click the elipses […] to create the calculation that inserts the repository variable into the table. This will take us to the calculation setup wizard.

1. Scroll down to the folder labeled Repository Variables and click on it

2. Double click on Daily_Refresh_Var. The calculation setup wizard will insert the VALUEOF expression to extract the value of the repository variable.

1. Drag the newly created logical column from the business layer and…

2. Drop it into the Markets folder in the Presentation Layer. Check in and save your changes. 

( This step is only included in this exercise so you can see the value when the cache is refreshed and the repository variable changes )

1. Click Manage and select Cache

2. Click Edit and choose Select All to select all entries in the current cache.. This will allow you to view all entries in the current cache and select them for manual purging.

1. Click Purge to erase all selected entries in the cache. This is how you can manually purge the cache. We are doing this at this point in the exercise to prepare to demonstrate how the automated purge works.

2. Log in to Answers and build this query

Click on the Results tab to run your query. This will cause the cache to load .

The Cache_Refresh_Daily column has been set to be used as a trigger. When this value is updated in the corresponding table in the database and the initialization block executes, the cache will expire and will be reloaded. For each table that includes this column. This helps to avoid inaccurate data in your answers requests.

Go into the Admin tool and click Manage=> Cache and click Refresh. This will reload the Cache Manager with the current information from the cache.

You can see that the cache has been loaded but has never been used.

Go back into Answers and re-run the query

After this query has executed you can see that the cache HAS been used.

Go back in SQL Developer and change the value for the last_refresh_date for the DAILY refresh_interval to 11/01/2222.

Click the commit tool to update the Oracle database with the new values.

Wait for a few minutes and then go back into the Admin Tool. Click on Manage=> Cache and you will see that the cache entry has expired. This is because you set

the Repository Variable to refresh every minute by executing a SQL read of the refresh table. When the value changes, the cache is expired.

Go into Answers and rerun your query. You can see the new value.

It is NOT necessary to include the column used to trigger the cache refresh in your Answers request. This exercise was intentionally set up so you can see the before and after values. OBIEE will detect the change and expire the cache for all tables that include this trigger column.

We also need to prove that the presentation server cache is expired in the same way as the BI server cache. 

Go into Answers and build the following query. Click on the Results tab.

The Presentation Server also maintains a cache. The Presentation server builds a query based on the current state of the RPD. If it is identical to a previous query, it will not trigger a read to the database. When the Repository Variable changes, Answers will generate a different query. This causes the new query to be sent to the BI server signaling a new request.

1. Click on Settings

2. Click on Administration

Click on Manage Sessions to open the console

The Administration console in Answers allows you to Manage the Sessions in the environment. This is the same functionality that you have when using the Admin Tool to manage the BI Server Cache.

Click on View Log to open the current log file.

The log file contains varying information depending on the setting specified for the current Answers logon. The log level for each userid is set in the Admin tool. Higher numbers in the loglevel setting cause additional information to be placed in the log. This has a significant effect on performance. Remember to lower the

log levels when preparing for a production environment.

This log file is telling us that the current query has been saved in a presentation server internal cache and that it is sending the query to the BI server for processing. A request to provide new data.

When you call Oracle support they will request you to change the log level to 5 for maximum detail. There are many log files generate by OBIEE.

Rerun the query

Answers will compare this query to the one you just ran. If they are identical the Presentation Server Cache will be used otherwise the query will be handed over to the BI Server for processing

Go back into Manage Sessions and view the log. We can now see that Answers has determined this to be an identical query and is not requesting new data from

the BI Server.

When the Presentation Server sends SQL to the BI server the BI Server will evaluate the request and then decide to use the cache or issue SQL to the database.

1. Go into SQL Developer and update the Last_Refresh_Date for Daily to 11/02/3333 

2. Click Commit

Go into Answers and rerun your query.

Changing the data will cause BOTH the BI Server cache and the Presentation Server Cache to expire when the Repository variable is reloaded.

Go back into Settings=> Administration=> Manage Sessions and view the log.

Notice how the Presentation server expired the cache and is requesting the BI server to go after new data.

Web/Chart Cache SettingsWeb

SettingsWeb Cache is cache that is specific for an end user. A dashboard/report is cached once an end user accesses it and then when they return to that Dashboard/Report it is pulled from the Web Cache. Minimizes rendering of pages, especially when flipping from dashboard to dashboard or using the back button.InstanceConfig.XML file is where you can change parameters that determine how long and how much web cache will be stored on the web server.

1. CacheMaxExpireMinutes: Parameter determines maximum duration of how long cache for a particular user will be stored on the SAW Server

2. CacheMinExpireMinutes: Parameter determines minimum duration of how long cache for a particular user will be stored on the SAW Server 

3. CacheMaxEntries: Parameter determines how many cache entries for a particular user may be stored on the SAW Server

Note: When an end user logs out of the application the web cache is purged. If the end user does not log out the cache will remain based on the parameters in the InstanceConfig.XML fileCacheMaxEntries