OBIEE Blog

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).”

I’ve found the following items to be very interesting as I’m very much interested in running OBIEE in a cloud.
Question someone asked on OS:
Q:How to cluster OBIEE that is installed on two virtual machines?
And their response was:
A:It is not currently possible to use Virtual machine names for OBIEE within a Cluster. The following Enhancement Request exists:
BUG#7576055
VIRTUAL MACHINE NAME FOR CLUSTERED OBI SERVER

This one is interesting, because it’s not just applicable to headers, but to some other elements that may contain HTML:
Q:Is it possible to add HTML code into a Column Header in OBIEE?

A:In order to render HTML in OBIEE (i.e. Answers, Dashboards, etc) please ensure you have set the following parameter “HardenXSS” to FALSE in the instanceconfig.xml file.

Example:


[ServerInstance]
[HardenXSS]false[/HardenXSS]
[/ServerInstance]

Last one is related to using external methods to get in to OBIEE.

Goal
1) Customer has a JSP and java application running on web sphere application server which uses OAM for single-sign-on.
2) In the same JSP application they have embeded a report which gets the data from OBIEE webservices. The actual report is deployed on OBIEE presentation server and uses OAM for authentication. When the user logs in to java application and clicks on the page which invokes a report from OBIEE customer doesn’t want to prompt for authentication again.
3) Customer does not see any OBIEE login webservices which takes as input parameter the authentication token or cookie generated in JSP application.
A:
There is currently no mechanism within the OBI Web Services to use Single Sign-on (SSO).

There are some methods that may assist further with customer requirement (i.e. impersonate() Method and impersonateex() Method). These methods should allow the users to logon and impersonate another user when customer only have the Administrators Login and Password.

However, these methods are not SSO as customer would still need to provide a username and password for the SOAP client (i.e. Administrator/Administrator) from within their J2EE application.

In order to overcome the fact the OBI Web Services does not use SSO, customer may want to install a new Presentation Server. The dedicated Presentation Server can then have the SSO disabled and can be used solely for the WEB Services application and nothing else.

I wonder what are security implications of such arrangement.

Have a good day!

When trying to install Oracle Business Intelligence Suite Enterprise Edition, v. 10.1.3.4.0 for Microsoft Windows in Windows XP and getting the following error message.

“Oracle Business Intelligence is not supported on this Windows version. Oracle Business Intelligence is only supported on Windows XP x86, Windows 2003 x86, Windows 2003 AMD64, Windows 2003 EM64T, Windows Vista x86, Windows Vista AMD64, Windows Vista EM64T, and Windows 2000 x86.”

Installer uses systeminfo.exe command to get information on the architecture of the machine. If this command will cause any error, then there is a need to check and fix that error first.

Run the following command and see if any errors. In our case we had the following error.

The command systeminfo.exe is run from command line.

***
C:>\windows\system32\systeminfo.exe

Error:-

Back with error for Network Card
Information???????..Error:Provider Load failures.

***********

Since installer use the same command to get information on the architecture of the machine. In this case because of the above error installer was not able to drive the machine detail and as a result installation failed.

The errors from the above command systeminfo.exe (if any) needs to be fixed first.  In our case after we fixed the Network Card issue, installation went fine.

Happy New Year! It’s been a busy time, however, I’m back with some new posts. I wish the readers the best of the best of the best I wish that your repository always stays consistent, that your physical layer is a true star, and your users never have problems with BI Answers.  I’ve digged up a few things that might be interesting:

Issue 1

Duplicate records are being eliminated from the Answers request results, when the desired results would be to include all rows, including duplicates.

The duplicate rows can have both the same case or different case.  Setting the case sensitivity parameter has no impact.

Example:

One row may be:
Dinner

Another row may be:
dinner or Dinner

The second row is not included in the Answers results.  Running the same SQL directly on the server does return all rows, including duplicates.  Analytic server is removing the duplicates.

Cause

Analytic Server issues the distinct clause.

Solution

Include the table key column in the request.  Hide the column so it is not actually seen in the request results.  This will make each row unique and thus each row, including what would be considered duplicate, is included in the results.

=======

Item 2

How to configure External Db Authentication that supports OBIS Impersonation (i.e. Delivers, BI Publisher)

Solution

To implement external DB authentication (i.e., authentication via a user/pw record in a database), you should NOT use AUTHENTICATION_TYPE = DATABASE. Instead, define it exactly like the Siebel Integrated setup in SiebelAnalytics.rpd’s/EnterpriseBusinessAnalytics.rpd’s Authentication init block. That is:
Use the default NQS Authentication type in NQSConfig.ini by commenting out all authentication types.
Define a separate physical database in the rpd which has a connection pool configured to hit the external database used which has the defined OBI users.
Enable Shared logon for this connection pool; set User name to :USER and Password to ASSWORD.
Only the connection pool configuration is required for this physical database – no table/column objects are required.
Define an Authentication Session initialization block to set the session system security variable ‘USER’ to be used for OBIS External DB Authentication.
Point the initialization block to the connection pool defined in step 2.
Use an initialization string like “Select ‘:USER’ from external_table WHERE USERID=’:USER’.
Define the ‘Edit Data Target’ to use system security variable ‘USER’ – this will make this initialization block the defacto OBIS authentication init block.
Define all connection pools used for backend data access to use ‘Shared logon’ with an explicit database user/password with at least read access to all backend tables.
This configuration supports OBIS Impersonation (i.e. Delivers). Database Authentication will be done via the connection pool defined in the Authentication init block. The user / password entered in an OBIPS client browser session will be passed to OBIS via the :USER / ASSWORD system session variables. These values will then be passed to the database for user validation – if a database session is established using this user/password combo, the user will be considered authenticated to OBI.

Also, it does not require all OBI users to be defined in the rpd. In fact, remove all the OBI users from the rpd that will be authenticated via a database user entry. If you require OBIS Group Security assignments for users, you should use a separate external table (either existing or new) that stores the user-to-OBIS group associations, and make the associations via a session initialization block. Again, similar to the SiebelAnalytics.rpd’s/EnterpriseBusinessAnalytics.rpd’s Autherization initialization block.

I was just about to file an SR – and quick search shows that someone else has already had this problem.

OBIEE 10.1.3.4 is Not Supporting Excel 2007 – File you are Trying to Open is in a Different Format than Specified by the File Extension – Contain Features not Compatible [ID 980627.1]

Comments
——–
OBI APPS 7.8.2.13 [19250]
OBIEE 10.1.3.4
Microsoft Windows x64 (64-bit)

- Oracle OBIEE 10.1.3.4 is not supporting MS Office 2007.

- We are trying to download a report from answers to Excel 2007 but its downloading as Excel 2003.
- Why is it?

- After clicking Download -> Download to Excel, in the request, saving the file and then trying to open it in 2003, the following message appears:

”
Microsoft Office Excel

The file you are trying to open, ‘Test.xls’, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?
[Yes][No][Help]
”

If I click on Yes button, I am able to open the report.

If I update some values and try to save then I get a warning message:

”
Microsoft Office Excel

test.xls may contain features that are not compatible with Single File Web Page. Do you want to keep the workbook in this format?

- To keep this format, which leaves out any incompatible features, click Yes.
- To preserve the features, click No. Then save a copy in the latest Excel format.
- To see what might be lost, click Help.

[Yes][No][Help]
”

I have to click on Yes button then it can be saved.

I should update and save this 2003 excel type on excel 2007 without any warning message.

- In Citrix, If I use Download -> Download to Excel and click on open button to open the file, I get this popup:

”
Windows Installer

Preparing to install…

[Cancel]
”

and that ends up with error:

”
D:\TEMP\…\Temporary_Internet_Files\Content.IE5\STMP6X8Z\Test.xls

Only Administrators have permission to add, remove, or configure server software during a Terminal services remote session. If you want to install or configure software on the server, contact your Network Administrator.

[Ok]
”

Solution

Comments
——–
1. Excel 2003 documents can be opened in Excel 2007.

2. From Release Notes OBIEE 10.1.3.4.1:
(“http://download.oracle.com/docs/cd/E10415_01/doc/bi.1013/e10404/toc.htm)
”
4.1.18 Limitation on the Number of Rows in MS Excel 2007
This issue applies to Versions 10.1.3.3, 10.1.3.3.1, 10.1.3.3.2, and 10.1.3.3.3. It is fixed in version 10.1.3.4.

In Microsoft Excel 2007, users cannot insert views that have more than 65536 rows. This is because Oracle BI Office uses an Excel 2003 based template, which has a limit of 65536 rows per sheet.
”

So, template used is for Microsoft Windows Excel 2003.

3. There is an Enhancement Request already filed asking to use an Excel 2007 template.
It is Document Bug 6970162, “Abstract: DOWNLOAD TO EXCEL REQUIRES TRANSLATION WHEN OFFICE 2007 INSTALLED”.

It is set to be reviewed for future versions. It has not yet been approved to be included in a particular version.

4. About the error you get when you try to open the downloaded file, in Excel 2007:

”
Microsoft Office Excel

The file you are trying to open, ‘Test.xls’, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?
[Yes][No][Help]
”

It is an Excel 2007 issue.

ORACLE cannot  be responsible for giving you solutions for Microsoft Excel Issues, as it is not our area.
You could google the error.
Nevertheless, we are giving you an example of one of the documents available in Google for that error. Please, be aware it is being given as a guideline, if you want to try it.

There is a descdription and some workarounds provided at:

http://www.itexperience.net/2008/03/17/excel-2007-error-different-format-than-specified-by-the-file-extension/

where they say:
”
This errors particularly occurs when you’re trying to open an XLS file (Excel 2000-2003) with Excel 2007. In most cases, the problem can be solved by defining MIME types and associating them with extensions.
When you’re using a Document Management System (like Sharepoint or Livelink), you can try adding these:
.xls = application/msexcel
.xlsx = application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

However, if you don’t want to look for a solution, but just want to solve the problem, insert this key in your registry to suppress the notification:

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security]
“ExtensionHardening”=dword:00000000

You can accomplish the above by doing the following:

Open your Registry (Start -> Run -> regedit.exe)
Navigate to HKEY_CURRENT_USER\SOFTWARE\MICROSOFT\OFFICE\12.0\EXCEL\SECURITY
Right click in the right window and choose New -> DWORD
Type “ExtensionHardening” as the name (without the quotes)
Verify that the data has the value “0?
”

You could talk to your Microsoft Excel Support to verify the workarounds given above, or to provide another solution.

5. About the error you get once you modify the file in Excel 2007 and try to save it:

”
Microsoft Office Excel

test.xls may contain features that are not compatible with Single File Web Page. Do you want to keep the workbook in this format?

- To keep this format, which leaves out any incompatible features, click Yes.
- To preserve the features, click No. Then save a copy in the latest Excel format.
- To see what might be lost, click Help.

[Yes][No][Help]
”

It seems to be due to OBIEE using mhtml when exporting to Excel.

There is an Enhancement Request already filed, Document Bug 5661454, “ABILITY TO GET EXCEL VIEWER WORKING WITH ‘DOWNLOAD TO EXCEL’”, requiring changing Excel download from MHTML to native Excel format.
It has been approved to be included in OBIEE 11.1.1.2.0.

Document Note 749436.1, “Download to Excel from reports downloading as HTML/MHTML rather than ‘real’ Excel”, explains more about that.

Having said that, the following document provides some information on the error when trying to save files with some formats in Excel 2007 (again, it is not an ORACLE article, so we are giving it only as a guide):

http://office.microsoft.com/en-us/help/hp100141051033.aspx

They say:
”
The Microsoft Office Excel 2007 Workbook format (.xlsx) preserves all worksheet and chart data, formatting, and other features available in Office Excel 2007, and the Macro-Enabled Workbook format (.xlsm) preserves macros and macro sheets in addition to those features.

If you frequently share workbook data with people who use an earlier version of Excel, you can work in Compatibility Mode to prevent the loss of data and fidelity when the workbook is opened in the earlier version of Excel, or you can use converters that help you transition the data. For more information, see Use Office Excel 2007 with earlier versions of Excel(http://office.microsoft.com/en-us/help/HA100775611033.aspx).
…
…
…
Web Page and Single File Web Page
These Web Page file formats (.htm, .html), Single File Web Page file formats (.mht, .mhtml) can be used for exporting Excel data. In Office Excel 2007, worksheet features (such as formulas, charts, PivotTables, and Visual Basic for Application (VBA) projects) are no longer supported in these file formats, and they will be lost when you open a file in this file format again in Excel.
”

6. About the other errors you get on Citrix:

”
Windows Installer

Preparing to install…

[Cancel]
”

and

”
D:\TEMP\…\Temporary_Internet_Files\Content.IE5\STMP6X8Z\Test.xls

Only Administrators have permission to add, remove, or configure server software during a Terminal services remote session. If you want to install or configure software on the server, contact your Network Administrator.

[Ok]
”

You would need to verify them with your Citrix Support, as they cannot be reproduced outside Citrix. The error text seems to say you do not have permissions to write in the TEMP directory for IE to create a temporary Excel file. It is a Windows issue also, apparently.

References
BUG:5661454 – ABILITY TO GET EXCEL VIEWER WORKING WITH ‘DOWNLOAD TO EXCEL’
BUG:6970162 – DOWNLOAD TO EXCEL REQUIRES TRANSLATION WHEN OFFICE 2007 INSTALLED
NOTE:749436.1 – Download to Excel from reports downloading as HTML/MHTML rather than ‘real’ Excel