Kumar Kambam’s OBIEE Blog

Intelligence on Oracle Business Intelligence

OBIEE Cache is enabled, but why is the query not cached?

Repeatedly customers pose the question – OBIEE cache is enabled, but why is the query not cached? The reason why the queries are not cached can be of many reasons. Some of the reasons are:

Non-cacheable SQL function: If a request contains certain SQL functions, OBIEE will not cache the query. The functions are CURRENT_TIMESTAMP, CURRENT_DATE, CURRENT_TIME, RAND, POPULATE. OBIEE will also not cache queries that contain parameter markers.

Non-cacheable Table: Physical tables in the OBIEE repository can be marked ‘non-cacheable’. If a query makes a reference to a table that has been marked as non-cacheable, then the results are not cached even if all other tables are marked as cacheable.

 

Query got a cache hit: In general, if the query gets a cache hit on a previously cached query, then the results of the current query are not added to the cache. Note: The only exception is the query hits that are aggregate “roll-up” hits, will be added to the cache if the nqsconfig.ini parameter POPULATE_AGGREGATE_ROLLUP_HITS has been set to Yes.

Caching is not configured: Caching is not enabled in NQSConfig.ini file.

 

Result set too big: The query result set may have too many rows, or may consume too many bytes. The row-count limitation is controlled by the MAX_ROWS_PER_CACHE_ENTRY nqsconfig.ini parameter. The default is 100,000 rows. The query result set max-bytes is controlled by the MAX_CACHE_ENTRY_SIZE nqsconfig.ini parameter. The default value is 1 MB. Note: the 1MB default is fairly small. Data typically becomes “bigger” when it enters OBIEE. This is primarily due to Unicode expansion of strings (a 2x or 4x multiplier). In addition to Unicode expansion, rows also get wider due to : (1) column alignment (typically double-word alignment), (2) nullable column representation, and (3) pad bytes.

 

Bad cache configuration: This should be rare, but if the MAX_CACHE_ENTRY_SIZE parameter is bigger than the DATA_STORAGE_PATHS specified capacity, then nothing can possibly be added to the cache.

 

Query execution is cancelled: If the query is cancelled from the presentation server or if a timeout has occurred, cache is not created.

OBIEE Server is clustered: Only the queries that fall under “Cache Seeding” family are propagated throughout the cluster. Other queries are stored locally. If a query is generated using OBIEE Server node 1, the cache is created on OBIEE Server node 1 and is not propagated to OBIEE Server node 2

Advertisements

January 19, 2009 Posted by | OBIEE Install and Config | , , , , , | 1 Comment

OBIEE on Windows 7 beta 32 bits

I was reading the initial reviews of Windows 7 and how good it is and so on when it said somewhere that Microsoft is going to release the beta version for general public to try on Friday. I immediately wanted to get my hands on it for not to see how it works, but out of curiosity to see if, Windows 7 can tango with OBIEE 10.1.3.4 (the latest and greatest). Well, after all someone had to do it!

After the usual Microsoft fiasco, I was finally able to download both the 32 bits and 64 bits of Windows 7.

Upon successful installation of Windows 7 32 bits, I could not wait to install the Java SDK and launch the setup.exe. Soon after, I was hit with a disappointment – Oracle Business Intelligence is not supported on this Windows version. After all, Windows 7 is a leaner and meaner Vista….

 

Yes, after all Windows 7 is a leaner and meaner Vista, so I tried to install it in compatibility mode.

 

Right click on “setup.exe”. In the setup properties, click on “Change settings for all users” the in the new window, check the check box “Run the program in compatibility mode for” and choose Windows Vista in the drop down.

Click ok and start setup.exe after the usual selections and wait at 100% forever, I was presented with the following screens.

 

 

 

Now to see if the components have started….

Oc4j seems to start…

 

Services seem to start…

 

The connection to presentation services was successful

 

The log on was successful and the dashboards and answers were working just fine.

 

OBIEE seems to work just fine on Windows 7 Beta build 7000 32 bits. I would not really expect a true supported version of OBIEE until after the official release of Windows 7.

 

I will install Windows 7 64 bits later to “test” OBIEE on it and will let you know its outcome.

 

January 12, 2009 Posted by | OBIEE Install and Config | , , , | 13 Comments

JAVA SDK for Installing OBIEE 10.1.3.4 on AIX 6.1 64 bits

Recently (week of October 19 2008 to be precise), I was installing OBIEE 10.1.3.4 on AIX 6.1 64 bit and found out that the Java SDK Version requirement is very hard to fulfill. The minimum version requirement according to the documentation is 1.5.x, so I installed one and ran the UnixChk.sh and it gave thumbs up. After which I have begun the installation process. The GUI pops up; I do all the steps until where it verifies the components it is going to install. I click next it briefly (<1sec) shows the status bar and then the status bar in the installer window disappears but the installer window will remain open (I wish I made some screenshots). I let it be for more than an hour and had to kill it. I looked at the OracleBI and OracleBIData directories and they are empty –this is the case before and after I killed the process.

 

I then started the installation in console mode. Ran UnixChk.sh everything is fine as shown in the screenshot bellow

 

Then I started the installation process…

 

… and the wait is forever.

I then tried other versions of Java SDK and it did not make any difference.

After a while, found out on the metalink that this is a known bug (Bug 7389678: OBIEE 10.1.3.4 INSTALL HANGS ON AIX USING IBM JDK 1.5 – JAVA COMPATIBILITY ISSUE). Then started other set of problems…. First off there was no link to download the version of SDK that the engineering provided to the other client. Then while trying to raise a SR, neither the AIX 6 nor OBIEE 10.1.3.4 are shown under the respective drop downs. So was forced to choose AIX5.1 and 10.1.3.3, and this confused the support. Therefore, after a few calls and a couple of days later, we finally got THE SDK to successfully complete the installation. You can download the 64 bit Java 5 SDK for OBIEE here (Megaupload) or here (Rapidshare).

 

December 30, 2008 Posted by | OBIEE Install and Config | , , , , , , , | 3 Comments