OBIEE Blog

Someone asked Oracle’s support  the following question: “Why some of the prompt case sensitive, some of them are case insensitive”

You noticed that some prompts are case sensitive and some are not. User selects a multi-select prompt, with the ”BEGIN WITH” matching functionality, ie, User enters “We” (without quotes) and presses the Go button, the user receives “No Choices Available.” message. User then enters “We%” the users receives results.

On other dashboards the steps describes above return results by entering “We” OR “we” into the “BEGIN WITH” matching functionality.

By default, Oracle database is case sensitive, this can be easily verified by run a query to test it, for example:

SELEECT PROD_NAME FROM PRODUCTS WHERE PROD_NAME LIKE ‘p%’
ORDER BY PRODNAME;

This query should not return any result as the first character for product name stored in database is upper case.

However, when using BI Answers, you may find some of the prompt with condition IS EQUAL TO/LIKE is case insensitive. This happens when

1. CASE_SENSITIVE_CHARACTER_COMPARISON = OFF is set (BIEE default setting) for Oracle Database in NQConfig.ini file.

2. The table is set cachable in BIEE Physical model.

So the data is cached in BI server is case insensitive, consequently the prompts retriving data from cacheable table is Case Insesitive, and the prompts retriveing from Non-cacheable table is Case Sesitive.

You also can turn off the cache in BIEE Administrator for the table in question, this can be done by double click on the table in Physical Model, click on General tab, uncheck Cacheable option for the table in question, save it. Restart BI Server. You then will find it is case sensitive, but for those tables set cacheable, they are still case insensitive.

It is recommended to set CASE SENSITIVE_CHARACTER_COMPARISON according to database searching behavior, so SENSITIVE_CHARACTER_COMPARISON=ON is set for Oracle Database in NQConfig.ini file to enforce consistent Case Sensitive searching behavior.
REF: [ID 862731.1]

I also had this problem (Thank you very much people at Oracle’s support):

When using the function @{fmap:images/my_image.jpg} no image is being returned on the PDF version of the report.

Image is properly shown in the dashboard screen.

It only happens with one Presentation Service and Web Server. The other one works fine.

No custom skins/styles used.

War file has already been redeployed.

Already have tested many things: paths, permissions, fmap syntax, war/ear webserver redeployment, invoking image url, copying image file from one server to the other, testing with different image files, smaller and bigger ones, testing with less data, using the fmap in different areas of the Dashboard (footer, narrative, title), verifying there is no custom style in Dashboard properties, etc.

Results are always the same: the image is shown in the dashboard but not in PDF print.

No error message reported in sawlog0.log.

Cannot see images when printing to pdf only. The image can be seen in the dashboard, and its direct URL shows the image fine too.

Is there a particular setting to get the images in PDF? A particular path PDF format uses that is different to the one used to show the image in the dashboard? Any additional path where the image needs to be copied? Something particular to Websphere?

Could you point us to where to look or how to get better detailed information on what is happening when printing to pdf when looking for the instance of the image? We have tried with different image files, very small ones, and copying the image that works from one server to the other. Customer also redeployed his war file after adding the image.

Could you give us any pointer to where to look or how to get better log info or how the printing to pdf looks for the image?

1. OBIEE Development provided the following answer for you:
”
Make sure the path to the file used by fmap is fully qualified (i.e. a complete path). The problem is likely because the PDF generation process which is being generated in the Javahost process, doesn’t have access to the same path.

The path to the file must be the same for both the Presentation Server, and also available to the Javahost process. If they’re on different servers then this will need to be a path that is common to both systems.

A fully qualified path would typically satisfy this requirement, but this really is dependent on the customers environment, and the permissions granted to each process. A webserver doesn’t necessarily have access to a fully qualified path, and the Javahost process may well have the same problem.

So the easy solution may be to duplicate the image file on the javahost server, and try to figure out a path where both the Presentation Server and Javahost server see the “same” file.
”

Please, review and follow it.

They also suggested to verify this image be downloaded to other file types. That is, it is shown when printing to html, or downloading to other file types.

Also, make sure adobe version to download to pdf is the same in both the environments. the one that works and the one that does not.

2. Additionally, you could, access the working environment via a web browser in Answers, open the HTML source code (like right-click and View Page Source (or View Image Info) in FireFox or View Source in MS IE) and search for the image.
you will see the HTML source like:
<img border=0 src=”analyticsRes/s_customSkin/mydirectory/my_image.jpg”> or
<img border=”0″ src=”res/s_oracle10/images/my.gif”>

Compare the values in both footers, for your working and not working environment.
The source of the image in the working environment should give a clue of the relative directory there. You could try to place the image in the same location on the non-working environment (see point 1 first).

3. Additionally, as a test, you could place the image in an accessible virtual directory via websphere (i.e. http://server/domain:port/virtualdir/../../mi_image.gif) and then in Answers, test with a URL mapping instead of the fmap. That is, try pointing the image as a URL instead of an fmap.

4. You could also look in the javahost log to search for errors thrown when the issue is reproduced.
They are all the log files in the following directory:

<install_drive>\OracleBIData\web\log\javahost

They are multiple and OBIEE rotates then.

5. Given that one environment is working and another is not, please, review your webserver/oc4j  mappings of both environments (in this case they were using Websphere). Moreover in relation to point 1. Websphere mappings and access given to process, fall outside of OBIEE scope.

This one I found to be interesting:

The goal is to set session variables using url variables, but can you also do this for the user and password ?
url variable (&Upwd) is not passed to session variable USER_PWD.
The variable USER is correctly passed, the variable USER_PWD is not

The steps to set an OBI session variable via a URL call utilizing the

instanceconfig.xml tag should be as follows

1. Create a session init block that will act as a ‘placeholder’ for the
session variable to be set via the url call – the variable can be set to
anything.

2. Set the ‘Enable any user to set the value’ option for the variable.

3. Add the following tag block to the instanceconfig.xml file anywhere
between the <ServerInstance></ServerInstance> tags:

<Auth>
<UserIdPassword enabled=”true”>
<ParamList>
<Param name=”NQ_SESSION.TEST_VAR”
source=”url”
nameInSource=”SETVAR”/>
</ParamList>
</UserIdPassword>
</Auth>
“TEST_VAR” should match the session variable name (case sensitive).

4. The following option will need to be appended to the OBI url passed -
&SETVAR=’variable value to pass. So a full example would be:

http://localhost:9704/analytics/saw.dll?Dashboard&nqUser=USER001&nqPassword=US

ER001&SETVAR=SomeValue

However, note that you cannot set the value of any System Security Session variable (specifically USER, PROXY, GROUP and WEBGROUPS) using any source method (e.g.: url, cookie, httpHeader) by design. Having this ability would open possible security breaches.

If you attempt to set the USER variable with the following instanceconfig.xml setting:

<Param name=”NQ_SESSION.USER” source=”url” nameInSource=”nquser” />

You will get the following error when using the url: http://localhost:9704/analytics/saw.dll?Dashboard&nquser=user1&nqpassword=public

nQSError: 10018: Access for the requested connection is refused

This one is useful if you’re trying to set-up BI to work with LDAP

Oracle BI allows for integration with LDAP servers for authentication and security
out-of-the-box. This document articulates the solution for retrieving Security Groups
defined within LDAP and reuses them within the context of Oracle BI repository
seamlessly. This document assumes that the users are using an Oracle Database and can
leverage the DBMS_LDAP package built into the Oracle Database for this
purpose.

Typically Organizations use LDAP servers as a central infrastructure for storing the
Users security credentials and use these servers to authenticate and authorize users
access to various applications within the organization. Tapping into this security
infrastructure helps the organization maintain its security in a central infrastructure.

Currently, OBI EE can connect to an LDAP server and authenticate a user with
user and password credentials, but it is limited in its ability to extract the groups
defined within the LDAP server and to leverage these groups in the repository.

Scope and Application

The work around suggested in this paper would allow the admin to reuse the
groups in the LDAP server using the DBMS_LDAP package available within the
Oracle Database.

Accessing Groups in LDAP for use in Oracle Business Intelligence

The goal is to allow access to the Users and Groups defined within LDAP Server,
without having to redefine these in a database. This allows the enterprise to
leverage a single common security infrastructure and allows OBI EE to plug into
this infrastructure.
The following are the high level steps to access the Groups defined within the
LDAP server.
1. Using the DBMS_LDAP package provided within the Oracle Database,
write a stored function to connect to the LDAP Server and expose the
Groups as a virtual table.
This PL/SQL package creates a virtual table within the database, which acts as
a gateway to LDAP server. It is now possible to write queries in standard SQL
form to this virtual table that would in turn be translated to the LDAP server.
2. Provide parameters needed to connect to LDAP for authentication. In
order to do this, open the Administration Tool used for managing the
OBI EE repository. From Manage -> Security -> LDAP Servers menu,
provide the necessary parameters needed to connect to the LDAP Server.
(for additional details follow the steps detailed in the Server Admin Guide
for OBI EE).

The above picture is a sample of properties required for connecting to a LDAP
server.
3. The next step is to create a Session Initialization Block within the OBI EE
Admin tool and wire the LDAP server property to this initialization block.
The user id defined in the LDAP server should be associated with the “USER”
session variable. USER is a system session variable within the Oracle BI stack
and is used to store the USER information entered during login from the
presentation server.

4. Next, create another initialization block within the OBI EE Admin tool to
store the Group information. The group information will be queried from
the Virtual Table (defined as part of stored procedure/function defined in
step 1) and to get the group information using row-wise initialization. This
Initialization block should be executed after the Initialization block
defined in the previous step.

The screen shot above shows an example of the SQL query being passed to
the Oracle DB where the PL/SQL stored procedure (from step 1) was created
and extracting the Group information stored in LDAP using row-wise
initialization.

This is probably helpful for someone who migrated from OBIEE 10G to 11G and need to retain the same settings. I’m still wondering who needs to see reports with 500k rows, but still.

In OBIEE 10g the ResultRowLimit was working with a value of 5,000,000 (65,000 is the default value).

We have tried to do the same in 11g through EM > BI > Core application > Capacity Management >

Performance > Maximal number of rows in a tabular view set from 65,000 to 5,000,000.

The Servers have been restarted and the report is still not running.

The SQL query fetches less than 200,000 rows. i.e. one of our reports is 172,310 rows.

Impact on Business:
This problem impacts a lot of reports through the National Healthcare agency.
These reports used to work in 10g.

View Display Error

saw.rpc.server.handleConnection, saw.rpc.server.dispatch, saw.threadpool, saw.threadpool, saw.threads
SQL Issued: 858~k1vsg0egoo7i60qhn13qv8lj52


Moreover, the same matter occurs in a report with 49220 lines fetched from Database.

Could it be linked to the fact that these reports use a pivot table to perform a second aggregation phase?

The final Pivot view is about 10 columns by 10 lines.
Cause
The Number of Rows setting was verified within OBIEE 11.1.1.5.

Capacity Management > Performance

Maximum Number of Rows Processed when Rendering a Table View

This setting limits how much data is retrieved from the BI Server and processed. The default value is 65000.

Reducing the maximum number of rows processed can significantly improve performance by reducing the system resources that can be consumed by a given user session.

Number of Rows 65000

It was found that the cause was the Pivot Table (which generally have more performance overheads when compared to a standard Table).
Solution
Within the instanceconfig.xml file in 10g you would have had to adjust the limits in your file for the parameters below.

In OBIEE 11g, these values below are discussed in the following Guides.

Oracle Fusion Middleware System Administrator’s Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1) E10541-02

18.3.1 Manually Configuring for Data in Views

If changes are made to the instanceconfig.xml file please carefully refer to the section stated below:
(Section 3.4, “Using a Text Editor to Update Configuration Settings.”)

When the following changes were made to the instanceconfig.xml file, this fixed the issue.





1000000
5000000





Oracle suggests that “These values were specific to this environment and the reports being run by that organisation. Other readers should note that increasing these values to too high a level may have an impact on performance (which is hardware dependent).”