Tag Archives: obiee solutions

Few 11g questions I found in the support log

A few useful things I found about 11G.

1.

Goal

In OBIEE 11g there is no link on the homepage for “Oracle Answers” like there was in the 10g version. Where is it or how to get it to appear?

Solution

BIEE 11g has been re-engineered in 11g, especially with the inclusion of WebLogic in the mid tier. A new look and feel is displayed from the 10g version. Now when connecting in 11g BIEE, you basically see Answers.

On the main 11g page, some of the latest Dashboards and Reports that have been created are displayed, plus there are still links to create a new report or open another dashboard.

So it is more a look and feel (cosmetic) change than anything else as the same functionality exists in both versions.

2.

Symptoms
In OBIEE Presentation Server log, you may see the following warning messages:

11g xml data incompatibility: object [some object type] at [the object path] requires upgrade. Admin needs to rerun upgrade, or remove the object, or fix the object’s xml manually. If problem persists, please contact technical support
Cause
========
Reason
========

Customers did not run catalog upgrade from the previous 11g version to the current version. Therefore, we have to upgrade it at run time in memory without saving it (if the catalog is upgraded through upgrade process, it will be upgraded and saved permanently in the catalog.) We give this warning message to warn customers that it is recommended to perform the catalog upgrade otherwise the application will need to perform the upgrade in memory.

Solution
The resolution for the issue has been documented. It is suggested to use the following:

Please refer to section: 3.8.10.2 Updating Oracle Business Intelligence Catalogs in
Oracle® Fusion Middleware Patching Guide 11g Release 1 (11.1.1.5.0). Here is the link to the documentation.

Oracle® Fusion Middleware Patching Guide 11g Release 1 (11.1.1.5.0)

The following bug has been logged to address the issue

Bug 12911878 – ERROR “11G XML DATA INCOMPATIBILITY” WHEN MOVING FROM 11.1.1.3 TO 11.1.1.5

Applies to:
Business Intelligence Server Enterprise Edition – Version: 10.1.3.4.1 [1900] to 11.1.1.5.0 [1308] – Release: 10g to 11g

3.

Symptoms
You are upgrading OBIEE 10g to OBIEE 11g using the Upgrade Assistant.  You have upgraded the repository successfully, but when you upgrade the web catalog, you get the following error:

[2011-03-09T13:38:25.565-05:00] [BIEE] [ERROR] [] [upgrade.BIEE] [tid: 13] [ecid: 0000IuRtnAxFw000jzwkno1DTwWL000004,0] javax.management.RuntimeMBeanException: Cannot refresh – Currently locked[[
at oracle.as.jmx.framework.standardmbeans.spi.OracleStandardEmitterMBean.doInvoke(OracleStandardEmitterMBean.java:897)

Cause
There is a lock on the web catalog in Enterprise Manager.

Solution
1. Search through Enterprise Manager and release any locks.
2. Stop and re-start Weblogic and the managed servers.
3. Start Upgrade Assistant and try upgrading the web catalog again.

OBIEE – state of the market – rates

[ad#banner]

Good afternoon all. In this post, I wanted to give my analysis of the current state of OBIEE market in the US. There’s a regular disclaimer that these opinions of mine – and not of my employers’, clients’, or other 3d party.

First observation: Dice. I’m trying to be cautiously optimistic, but it seems as Business Intelligence market in general is picking up. I use my own Dice job index, where, I enter keyword (OBIEE) – and track it through out time. The results aren’t statistically correct, since it’s been my experience that there’re many similar positions advertised on Dice by different consulting companies – which means that in reality it’s the same position. During fall of 2007, there were very few positions (150+) . In 2008, there were stale (150-200). 2009 starting to grow (on average 250-290).  Now, I see 350+ on a regular basis (again remember -that doesn’t mean there’re actually 350 open positions, as frequently various vendors compete for the same position). Other trend I’ve noticed, most companies demand US Citizens/Green-Card holders, and many companies specifically exclude H1Bs (even through 3d parties).

Second observation:  OBIEE blogging has cooled down. That might indicate that first, people are busy on their current assignments; second, there’s plenty of business to handle, so not much incentive to be involved in self-promotion. Third, warm weather could be attributing to general blogging cool-down.

Third:  it seems as OBIEE has penetrated federal and state organizations throughout the U.S. It’s literally golden time for OBIEE consultants who are US  Citizens and who are able to obtain security clearance (usually that means  no criminal history of any kind, decent credit report/score,  references/education check). I’ve seen full-time salaries on federal projects offered at 150-180k  range + benefits, and I’m sure there’s potential for more. Hourly rates for independent consultants could also be above average. Unfortunately, for H1B consultants, it’s very difficult (if not impossible) to find a federal project which would allow foreigners. Some agencies (such as USCIS, DOD) will not even consider Green Card holders employees/consultants. Some agencies (such DOE) would allow consulting companies to place GC holders, provided they can obtain clearence. I’ve also seen a strong trend in state agencies to utilize OBIEE for reporting purposes. As US government is becoming more and more keen on disclosing various data gathered from federal agencies, OBIEE will be there to stay (foot-in-the-door-principle).

Fourth: even though the amount of positions/jobs have bounced back, the rates haven’t fully bounced back to 2005-2007 levels. However, with proper negotiation skills and market research, one can live a comfortable living. Without getting too much into details, I suggest ignoring ads that advertise their willingness to pay $50 Corp-to-corp to an OBIEE senior architect.  On the other hand, people make mistakes while creating ads, so buy beware.

Fifth: There’re a lot more full-time OBIEE jobs than ever before, mostly in three types of companies: a) large consulting companies growing their OBIEE practice b) companies that have invested heavily in OBIEE and would like to make the best use of their investment c) small consulting vendors bidding on pieces of larger projects with their small-disadvantaged-minority-owned status

I invite you to participate in the discussion. I avoided discussing hourly rates for the reason that there’re many key factors that influence rates, mainly: immigration status, location, form of contract, consulting company’s cut, etc. etc.  So that makes it difficult to weight-in. Do you think that an anonymous rate survey would be in order?

OBIEE issues – you asked I answer (find answer)

[ad#banner]
Hello,

I’m back after being busy with some really interesting issues at work.  I found a minute and I’d like to post few items.

First of all – the question “In OBI, How to move System Wide default settings from one environment to another?” comes up a lot – here’s an official response that I found on the knowledge base:

System wide default settings cannot be moved from one environment to another by design. The recommended way to move any other changes is to perform an Archive and then an unarchive. But, this method does not work for System wide default settings

The following Bug has been logged to make this feature available in the future releases:
BUG 8271294 – ARCHIVING AND UNARCHIVING DOES NOT STORE SYSTEM WIDE DEFAULTS

So now you know what to tell your project manager.

Second item I found interesting is:

“How to reduce the space between multiple pivot tables placed side by side?”

1. Provided that s_oracle10 is in use; backup the following files before editing them
– C:\OracleBI\web\app\res\s_oracle10\b_mozilla_4\views.css
– C:\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\s_oracle10\b_mozilla_4\views.css

2. Set the following section in both files above:
.PTSectsTable

{
border:
none;
border-spacing:0px;
padding:0px;
margin:0px;
}

3. Restart the OC4j and the Presentation server
Third item,
“When defining multiple connection pools for a database in the physical layer of the rpd, is it possible to make the queries use the 2nd connection pool because current behavior seen is that the 1st connection pool is used by default for all queries?”

There is currently no way to make a query use a specific connection pool. The following bug has been raised to address this requirement- As you are already aware, current workaround is to have separate database defined in physical layer :

Bug 9392303 – FIRST CONNECTION POOL CHOSEN BY DEFAULT FOR ALL QUERIES

Customers requirement :

They have a new application that need connect to mutiple database, so under
physical layer root have two connection pool.
So under Database in physical layer 1st connection pool points to an oracle
instance and second connection pool points to a different oracle instance.
When we run report in answers, the connection information is always taken
from the 1st connection pool by default – hence we get table cannot be found
ora error. Reason being the table exists in the database instance referenced
in the 2nd connection pool.
When we do view data in RPD , the results return fine – this is because while
clicking on view data, we explicitly have to select the connection pool.
While creating the logical table/column, we only specify the database name
and the schema name and the table name- no place to provide connection pool
info – hence the server chooses the 1st one by default.
Need a way to fix this – current workaround suggested is to have multiple
database created in physical layer.

Thank you wonderful Oracle support representatives. You’re doing great job – often overlooked, but appreciated nevertheless.
Have a safe OBIEE day

OBIEE 10.1.3.4 is Not Supporting Excel 2007

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

How to remove “My Dashboard” and keep My Folders

Someone wanted to take away from all users ‘My Dashboard’. They want that the users only have the choice between the ‘common Dashboards’.

But the users (the report creators) need My Folders.
So Manage Privileges – Catalog – Personal Storage (My Folders and My Dashboard) does not fit exactly.

Someone has created default shared dashboards that they want the users to see/read by default, but they want to restrict user access to the Shared Dashboard folders.
They want users to be able to access their My Folders directory, but not have access to their My Dashboard link or My Dashboard folders. This is not possible with out of box functionality.

The following enhancement request has been created to benefit the users.

The only way to deny access to ‘My Dashboard’ is via the “Personal Storage (My Folders and My Dashboard)” Catalog privilege. Denying this privilege to a group or user will take away access to both ‘My Dashboard’ and ‘My Folders’ – currently, you can’t separate the two.

Jay Honnavalli suggested the following solution:
“Alternatively, here is a suggestion of using current functionality would be:

1. Create a web catalog group and dashboard via “Manage Presentation Catalog Groups and Users” to be used as the default user dashboard.
2. Grant the ‘Everyone’ web group the appropriate privilege to this catalog group via “Manage Presentation Catalog” – e.g. ‘Read’.
3. In the admin tool, create a session init block to set the ‘PORTALPATH’ session variable to the webcatalog path of the default dashboard page.
3.a. You can find the path by looking at the properties of the folder under ‘_portal’ in the catalog group folder the default dashboard was created in – e.g. “/shared/user_default/_portal/Home”.
3.b. The init string could either select the literal path (e.g. select ‘/shared/user_default/_portal/Home’ from dual) so all users would be routed to the same dashboard. Alternatively, the path could be stored externally, e.g. in a table, and selected from it per userid. That way, it would be manageable outside of the admin tool.

This would force all users to the default dashboard specified in the path used to populate PORTALPATH for all users.
One issue that may occur is with existing users that have set their ‘Default Dashboard’ to anything but ‘Default’ in the ‘My Account’ link – they may be required to set it to ‘Default’, then log-off/log-on for the default dashboard setting to take affect.
I know of no way to get around this potential issue for this scenario short of each user making the change OR deleting users from the webcat such that a subsequent logon will ‘re-initialize’ their account (which will then use the init block set PORTALPATH value). Both of these options seem severe, and the second may prove problematic as long as the ‘My Account’ link is still active. ”