OBIEE 11g - OBI Server and Presentation Cache Management

Oracle BI Server Cache Management

• BI server could be leveraged by multiple users and reports depending on whether they are accessing similar or subset of the cached data, though from a different reports.
• To purge OBI Server cache the best-practice is to set it as automated process after completing ETL, because if we set the auto-purge at specified time, but for some reason ETL fails, the BI Server cache gets purged and all user queries will have to get executed all over, which will impact the BI Server performance.

1. To complete this task, please follow these steps:
2. Create a text file called: PurgeAllCache.txt and save it in C:\ (easier to remember)
3. Open and type the following: Call SAPurgeAllCache()
4. Save and Close.
5. Create another text file called: PurgeCache.txt (we will later convert this file into batch file .bat) and save it in C:\
6. Go to: \Oracle_BI\bifoundation\server\bin\nqcmd.exe and make sure you see the file DO NOT run it.
7. Copy and paste the nqcmd.exe file directory somewhere temporary
1. This is a ODBC client that runs commands
8. Now, go to: Start > All Programs > Administrative Tools > Data Sources (ODBC)
9. Click on System DSN tab and locate Oracle BI Server 11g_XXXXX > click on Configure…
10.  
11. Copy and paste the Name: with nqcmd.exe file path for now. The ODBC name in this case is:coreapplication_OH905911364
12.  
13. Cancel to close Oracle BI Server and ODBC windows.
14. Open PurgeCache.txt and type the following:
1. Echo off
2. \Oracle_BI\bifoundation\server\bin\nqcmd.exe -d coreapplication_905911364 -u weblogic -p password -s C:\PurgeAllCache.txt
1. Make sure this command is ALL IN ONE LINE
3. Echo 

• Explnation:
• \Oracle_BI\bifoundation\server\bin\nqcmd.exe [nqcmd.exe file path]
• -d coreapplication_905911364 [my DSN ODBC name]
• -u weblogic [-u BI Admin Username]
• -p password [-p BI Admin Password]
• -s C:\PurgeAllCache.txt [-s SQL Input File to execute]

1. Save and Close. 
2. Change PurgeCache.txt into PurgeCache.bat this will convert the file into executable file. 
3. Now run couple of analysis in OBIEE. 
4. Open the RPD in an Online mode:
5. Go to: Manage > Cache - and you'll see recent saved cache.
6. Now run PurgeCache.bat and recheck the RPD Cache, you will see that it is cleaned :).

  

Oracle BI Presentation Cache Management

• When users run analyses, Presentation Services can cache the results of those analyses. Presentation Services determines if subsequent analyses can use cached results. If the cache can be shared, then subsequent analyses are not stored.
• The files for the Presentation Services cache have names such as nQS_xxxx_x_xxxxxx.TMP.
• The files are created by the ODBC driver but generally do correspond to ODBC requests that the Presentation Services cache keeps open. The files are stored in the following directory: 
•  ORACLE_INSTANCE\tmp\OracleBIPresentationServices\coreapplication_obipsn\obis_temp
• The files for the cache are removed whenever Presentation Services shuts down cleanly. If Presentation Services shuts down unexpectedly, then various cache files might be left on disk. You can delete the files when Presentation Services is not running.
• The Presentation Services cache is not the same cache that is accessed by the Oracle BI Server. You can change the defaults for the Presentation Services cache by modifying the instanceconfig.xml file to include the cache entries.
• i.e. if the requirement is to set the cache to expire every 15 minutes; open to edit the instanceconfig.xml for OBI Presentation Services and add the following : 
• Your code should look like this:
   
• 
• Save and Close the file.

Alternative Cache Management Techniques

• Open  BI Administration Tool > Open the RPD
• Go to: Physical table properties
• Check: Checkeable and choose as appropriate
• Cache never expires - self-explanatory
• Cache persistence time - specify how often the table should purge the cache
• Note: this implies only to this table and is not the best-techniques when you are working with 100s of tables.