Seeding the Cache IN OBIEE11G

Seeding the Cache

I runs an OBIEE11G dashboard, and the results are added to the cache so that when Bill runs the same dashboard Bill gets a great response rate because his dashboard runs straight from cache. Kinda sucks for Bob though, because his query ran slow as it wasn’t in the cache yet. What’d be nice would be that for the first user on a dashboard the results were already in cache. There are several options for seeding the cache. These all assume you’ve figured out the queries that you want to run in order to load the results into cache.

1. Run the analysis manually, which will return the analysis data to you and insert it into the BI Server Cache too.
2. Create an Agent to run the analysis with destination set to Oracle BI Server Cache (For seeding cache), and then either:
   1. Schedule the analysis to run from an Agent on a schedule
   2. Trigger it from a Web Service in order to couple it to your ETL data load / cache purge batch steps.
3. Use the BI Server Procedure SASeedQuery (which is what the Agent does in the background) to load the given query into cache without returning the data to the client. This is useful for doing over JDBC/ODBC/Web Service (as discussed for purging above). You could just run the Logical SQL itself, but you probably don’t want to pull the actual data back to the client, hence using the procedure call instead.

4. SET VARIABLE SAW_SRC_PATH='/users/weblogic/Cache Test 01',  
   DISABLE\_CACHE\_HIT=1:SELECT  
      0 s_0,  
      "A - Sample Sales"."Time"."T02 Per Name Month" s_1,  
      "A - Sample Sales"."Base Facts"."1- Revenue" s_2  
   FROM "A - Sample Sales"  
   ORDER BY 1, 2 ASC NULLS LAST  
   FETCH FIRST 5000001 ROWS ONLY  

Checking the RPD for Cacheable Tables

The RPD Query Tool is great for finding objects matching certain criteria. However, it seems to invert results when looking for Cacheable Physical tables - if you add a filter of Cacheable = false you get physical tables where Cacheable is enabled! And the same in reverse (Cacheable = true -> shows Physical tables where Cacheable is disabled)

Cache Location

The BI Server cache is held on disk, so it goes without saying that storing it on fast (eg SSD) disk is a Good Idea. There's no harm in giving it its own filesystem on *nix to isolate it from other work (in terms of filesystems filling up) and to make monitoring it super easy.

Use the DATASTORAGEPATHS configuration element in NQSConfig.ini to change the location of the BI Server cache