OBIEE 11g - OBI Server and Presentation Cache Management

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.

Automate OBIEE 11g Cache Clearing Process

Most of the OBIEE 11g leverages the Cache mechanism to provide the user with faster results . However the downfall is that if the Cache becomes stale, it might show outdated result . Hence it is necessary to clear the Cache at regular Intervals.

Cache Mangement is a huge concept in OBIEE. In this post, i would just deal with a way on clearing the OBIEE 11g Cache using Analysis.

1.) We would need a separate database conenction in the RPD, this would connect us directly to the BI server.Create a new database and choose "ODBC Basic" as the Database type. Make sure you check the direct database request checkbox.

2.) Next, create a new Connection Pool for the above Database and  in the DNS ( data source name) select the default database connection to BI server (ususally it starts as coreapplication... XXX). A user name and password is not required here .

3.) Since you have allowed the Direct database access request, you could setup permissions for it so that the security is not compromised. Click on the "permissions" tab to set it up. This step is not mandatory.

4.) Now login to Analytics ( http://yourServername:9704/analytics) . Go to the Administration Tab and make sure you have the access privileges to send "Direct Database Requests".

5.) Next, Click New - > Create Direct Database Request

6.) Mention the "Database Name"."Connection pool name" that you had created in the earlier steps . in the SQL statement, pass the function SAPurgeAllCache() which would clear the entire cache.The end output would look similar to this:

So, in this manner you could create an analysis , when executed would clear the analysis.

Note: If required we can create an Agent/iBot and call this analysis on fixed schedule which would make this entire process truly automated.


I am ref below blog:
http://obiee-sharat.blogspot.com/2013/06/automate-obiee-11g-cache-clearing.html

Over lapping of views when exported to Excel

One of the major issue what everyone faces is the 'Overlapping of the Chart and table or filter view when downloaded to Excel'


So, here is a small workaround to resolve the issue:

Place a narrative text between the chart and the table/pivot table view. With the following code:
<span style="display:none">[br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/][br/]</span>

Check the HTML and also the number of [br/] tags depends on the requirement.

Save it and export the reports.  You will see the chart and the table view separately.