If you look in the configuration guide you will find that the CASE_SENSITIVE_CHARACTER_COMPARISON parameter in the NQConfig file controls the case sensitive search within OBIEE. Be aware that you might be fooled by your database settings. If you are on an Oracle database 10G+ you can use a connection script in the repository to allow case insensitive searches. Go to the connection pool, click on the connection script tab.
Press New:
Enter:
alter session set NLS_SORT=BINARY_CI
alter session set NLS_COMP=LINGUISTIC
Save the repository, reboot the BI-Server
Till Next Time
1 comment:
Hi John, I wish life was simple as it appears. We investigated the case insensitive searches in OBIEE for a while and we couldn't find a workable solution. The CASE_SENSITIVE_CHARACTER_COMPARISON parameter is only meant to describe the BI Server case sensitive setting not your DB. In other words it doesn't affect your DB queries, only data that has been cached by the BI Server is affected by this parameter. As shown in your post you can indeed alter the NLS session settings and this will indeed force case insensitive searches. But the most likely side effect will be that indexes on those columns will not be used which is really a show stopper for a BI application. To avoid not using indexes you can build linguistic indexes. Linguistic indexes bring another big set of issues with them, they use more storage than Binary indexes and so have more associated overheads and costs. They are also ignored for some types of predicate conditions (MIN, MAX and LIKE can not be used). See http://richardfoote.wordpress.com/?s=Linguistic+Indexes for more info. These limitations make linguistic indexes a "no go" for any serious BI application.
We raised this issue Oracle Support and they told us that bug #5537531 "Configuring/querying case insensitive Analytics field in case sensitive db environment" was raised some time ago but currently set to "Suggestion Rejected" with the reason as "This is not feasible for performance reasons".
The only compromise solution we could find was to "cache" the column value we wanted to use in insensitive searches. These were mainly used in "show all choices" while setting prompts. Other than that there isn't much you can do in an Oracle database.
Post a Comment