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 Performance Tuning Tip – Turn off Query Logging

 

Though query logging has immeasurable development value, do not use this for regular production users as the runtime logging cost is extremely high. Every log item is flushed to the disk, which in turn hurts query response. Also, note that the query log files are not created on per user or query basis, there is only one query log per OBIEE server and it would have exclusive lock on the log file, which kills concurrent performance.

On the other hand, usage tracking has a very low runtime cost and is preferred to monitor the queries being used.

January 19, 2009 Posted by | OBIEE Performance Tuning | , , , | 3 Comments

OBIEE Data Security – Column Level Security

Oracle BI aka OBIEE offers a wide variety of data security, of which column level security is a flavor. Consider a column that has sensitive information like Social Security Number. This information should not be presented to all in the organization except the select few who need to have this info. You can actually hide the column in the presentation layer from others. You can use this column in reports on dashboards and people with access to this column will see it in report for others the report will not show this column. To achieve this functionality we need to make changes in two places – Metadata and one time change in NQSConfig.ini.

 

Let us first see what needs to be done in Metadata. For this example, let us consider that EmployeeID is a scared piece of information that a select few MegaUsers can see and access.

 

In Metadata on the presentation column, we need to make changes to permission settings. Right click on the column and select “Properties”

 

By default, the columns will have read access to everyone.

 

Choose the groups that should have read access rights on the column. The check box here works like a toggle button. Click on it to get a check mark or a red cross mark. A red cross marks explicitly restricts access. While an unchecked/black check box implicitly restricts access to the column.

Now let us log in as a Mega User (Kumar.Kambam, in this case) and create a report using the EmployeeID Column.

 

In answers, Kumar.Kambam can see the EmployeeID Column. Let us create a simple report using the column with column level security enforced.

 

 

Save it and put it on a dashboard to test OBIEE column level security.

 

 

Now log on as Basic User

In answers check for EmployeeID column. The column is not visible. This is due to the column level security restriction.

 

Go to OBIEE Security Dashboard to see the report, and we get an error message. State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 27005] Unresolved column: “Employees”.”EmployeeID”. (HY000)

The error is expected. The report contains a column on which the current user does not have access to. So for all practical purposes as far as OBIEE is concerned, the column does not even exist. The default setting in NQSConfig.ini file drives this behavior.

In, NQSConfig.ini change the parameter PROJECT_INACCESSIBLE_COLUMN_AS_NULL which is under security section. By default it is set to No. Set it to yes. And restart the services.

 

Now logged in as BasicUser1 and access OBIEE Security Dashboard to test OBIEE column level security

The report is presented without the EmployeeID column on which OBIEE column level security was enforced.

So for the same dashboard report, depending on the data level security access permissions in OBIEE for a user, a column visibility can be controlled using column level security feature.

 

 

 

January 15, 2009 Posted by | OBIEE Security | , , , , , | 20 Comments

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