Multiple LDAP servers

OBIEE does allow us to add multiple LDAP server. We needed this functionality as some of the global users were on different domain than the majority of users. Apparently when more than one LDAP servers are configured, BI attempts to get user authenticated with first server in the list, if it fails then it tries using second LDAP server.

This method can also be user for implementing redundant LDAP servers.

Catalog Manager Does not open in offline mode

I don't know when exactly the issue was started, but we had recently upgraded our platform from 10.1.3.3.1 to 10.1.3.4.1. We also installed a patch for date format issue that started coming after upgrade. We installed patch p8599681_101341_WINNT.zip for bug# 8599681.

When I was trying to migrate catalog to new version of BI Apps, I had to use Catalog Manager in offline mode and it did not open, online was working although. I checked the log file for errors:

D:\OracleBI\web\catalogmanager\workspace\.metadata\.log

Following error were logged every time I tried to open webcat in offline mode:

!ENTRY org.eclipse.ui 4 0 2009-12-22 15:11:46.694
!MESSAGE no perfapi in java.library.path
!STACK 0
java.lang.UnsatisfiedLinkError: no perfapi in java.library.path
at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1682)
at java.lang.Runtime.loadLibrary0(Runtime.java:823)
at java.lang.System.loadLibrary(System.java:1030)
at com.siebel.analytics.web.catalogmanager.JNIConnection.init(JNIConnection.java:62)
at com.siebel.analytics.web.catalogmanager.JNIConnection.<init>(JNIConnection.java:22)
at com.siebel.analytics.web.catalogmanager.OfflineConnectionManager.<init>(OfflineConnectionManager.java:17)
at com.siebel.analytics.web.catalogmanager.CatalogmanagerPlugin.setOnline(CatalogmanagerPlugin.java:105)
at com.siebel.analytics.web.catalogmanager.dialogs.OpenCatalogDialog.okPressed(OpenCatalogDialog.java:195)
at org.eclipse.jface.dialogs.Dialog.buttonPressed(Dialog.java:409)
at org.eclipse.jface.dialogs.Dialog$2.widgetSelected(Dialog.java:556)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:90)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:843)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3080)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2713)
at org.eclipse.jface.window.Window.runEventLoop(Window.java:809)
at org.eclipse.jface.window.Window.open(Window.java:787)
at com.siebel.analytics.web.catalogmanager.actions.OpenCatalogAction.doRun(OpenCatalogAction.java:43)
at com.siebel.analytics.web.catalogmanager.actions.CMAction.run(CMAction.java:92)
at org.eclipse.jface.action.Action.runWithEvent(Action.java:996)
at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:538)
at org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:488)
at org.eclipse.jface.action.ActionContributionItem$5.handleEvent(ActionContributionItem.java:400)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:843)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3080)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2713)
at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:1699)
at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:1663)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:367)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:143)
at com.siebel.analytics.web.catalogmanager.CatalogManager.run(CatalogManager.java:170)
at org.eclipse.core.internal.runtime.PlatformActivator$1.run(PlatformActivator.java:226)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:376)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:163)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.eclipse.core.launcher.Main.invokeFramework(Main.java:334)
at org.eclipse.core.launcher.Main.basicRun(Main.java:278)
at org.eclipse.core.launcher.Main.run(Main.java:973)
at org.eclipse.core.launcher.Main.main(Main.java:948)


Next, I tried to uninstall the patch for bug fix. To my surprise catalog manager was working. Another example of shabby bug fix by Oracle. This bug fix has two brand new bugs. First messing the catalog manager offline function, and second, dashboard date default by repository variable throws error in underlying reports (session variable work fine).

Upgrade BUG - Default Date in prompt gives error

After upgrading from 10.1.3.3.1 to 10.1.3.4.1 we got a bug in BI where if a report is defaulted to date using any variables in prompt, the report will error out with error:


View Display Error

A date value was expected (received "2009-12-20 00:00:00.000").
Error Details
Error Codes: QABPH2PO

Oracle did acknowledge this as a bug (BUG 8599681 - DATE FORMAT ISSUE ON THE DASHBOARD PROMPT) and we had them create a patch for this, however even the patch had a bug. After patch installation the dashbaord will work if reposiroty variable is used in prompts to default a date prompt. However if we use session variable to default a prompt, the report throws same error.

Cache Purge Bug

If you want to purge cache for specific tables during the day, oracle has a utility SAPURGECACHEBYTABLE. However apparently it does not work with many versions of OBIEE. Refer to the bug:

The Bug 6906535 - SAPURGECACHEBYDATABASE AND SAPURGECACHEBYTABLE NOT WORKING IN 10.1.3.4.0 [ID 956345.1]

Upgrading OBIEE 7.9.2 to 7.9.6.1

Upgrading BI apps version is a big process. Following steps were taken while upgrading in our environment:

1 . We will need three repositories to begin with, 7.9.2 original rpd , 7.9.2 customized rpd and 7.9.61 rpd. Its important than in 7.9.6.1 all OOTB modules from 7.9.2 should also be installed in addition to new SAs.

2. Equalize repositories - when installing OBI apps, oracle ships with map files that are requried to equialize the rpds. These have two steps:

StepA: Equalize 7961 to 792, command for this is :

equalizerpds -A Administrator -B SADMIN -C OracleBIAnalyticsApps792.rpd -D Administrator -E SADMIN -F OracleBIAnalyticsApps7961.rpd -J rename792796new.map -O oracleBIAnalyticsApps.rpd

StepB: Equalize CustomRPD to 792

equalizerpds -A Administrator -B SADMIN -C OracleBIAnalyticsApps792.rpd -D Administrator -E custompwd -F OracleBIAnalyticsAppscustom.rpd -J rename792796new.map -O customoracleBIAnalyticsApps.rpd

3. Next step is to use three way merge and create a new RPD. AdminTool has inbuilt merge option which will need to be used. Details of merge and other steps are available in "OBI Apps Upgrade.pdf" page 6-37 to page 6-44.

We had multiple errors during merge process. The goal here is to minimize this so that least manual work is required later. Way to do this is notice pattern of error and then change the custom rpd (equalized custom rpd) before merging and making the objects same as in 7.9.6.1 rpd, so that the algorithm treats the object as equal.

4. After rpds are successfull merged, consistency check needs to be performed. Here again there will be patterns of error, some can be fixed by making changed to rpd before merging and then merging again, others will need to be fixed manually.

We faced an unusuall error that read : "[nQSError: 42001] Missing repository object with ID=3003:338714". This error completely halted the process as we could not fix the error without knowing where the error is comming from. To fix this issue we followed three step process:

StepA: export merged rpd to udml using following syntax:

nqudmlgen.exe -u Administrator -p custompwd-r C:\OracleBI\Upgrade\04AfterManualWork\ManualoracleBIAnalyticsApps.rpd -o
C:\OracleBI\Upgrade\04AfterManualWork\merged.udml

StepB: convert udml file back to rpd file using following command:

nQUDMLExec -u Administrator -p customrpd -i C:\OracleBI\Upgrade\04AfterManualWork\merged.udml -o
C:\OracleBI\Upgrade\04AfterManualWork\ManualoracleBIAnalyticsApps1.rpd

This will create the rpd back , except that it will not bring in objects that was causing unusual errors.

StepC: Compare the new rpd with rpd that was give unusual error. This should be able to point all object which need to be fixed in order to proceed.

5. Once all errors and warningd are resolved, rpd is ready for regression testing. Any errors there after will need to be manually fixed.

PDF generation crashes OBIEE server

We faced issue in one of our dashboard where OBIEE presentation server was crashing when trying to export dashboard to PDF. This dashboard had multiple reports embedded in it. We were trying to apply multiple formatting to align various reports on the dashboard. When we removed extra formatting on one of the reports, the server stopped crashing.

Adding database HINTS to OBIEE queries

We had OBIEE queries that could be tuned by applying hints in physical layer tables. We can apply hint to use a specific index which helps in faster query performance. The syntax to apply this is :

index(table_name, index_name)

In oracle database the comma in hint syntax can be omitted however in BI this is required, although the explain plan with comma and without comma is exactly the same.

This approach works when we are tuning specific set of report, however if the subject area is used for multiple report types, the hint index gets applied to all queries and result in performance degradation in other queries. In those cases using a materialized view may be an better option.

Default attribute column in select clause causing incorrect aggregate

We had a subject area where BI was pulling an attribute column (row wids of dimension) in select clause automatically even though we were pulling only measures in Answers. This was making incorrect aggregates in report or in other word aggregates at lower levels of granularity.

One of the measure columns was a calculated column in RPD where we were multiplying a column from fact to a numeric column in dimension. We had included dimension as one of the sources of fact table and then applied the calculation at logical level of fact table.

To fix this issue, we found that the dimension where numeric column was present had that numeric column in one of the levels of dimensional hierarchy. So when we deleted the numeric column from dimension hierarchy, the query was fine. The numeric column was still present in dimension itself but just removed from hierarchy.

Going back 12 months based on selected month

If users need capability to get back rolling 12 months of data based on one month selected in dashboard, the following code will do this:

"Month"."Month" between (CASE WHEN SUBSTRING('@{AsOfMonth}' FROM 8 FOR 2) = '12' THEN SUBSTRING('@{AsOfMonth}' FROM 1 FOR 4) ' / 01' ELSE SUBSTRING((CAST((CAST((SUBSTRING('@{AsOfMonth}' FROM 1 FOR 4) SUBSTRING ('@{AsOfMonth}' FROM 8 FOR 2)) AS INTEGER) - 99 ) AS CHAR(6))) FROM 1 FOR 4) ' / ' SUBSTRING((CAST((CAST((SUBSTRING('@{AsOfMonth}' FROM 1 FOR 4) SUBSTRING ('@{AsOfMonth}' FROM 8 FOR 2)) AS INTEGER) - 99 ) AS CHAR(6))) FROM 5 FOR 2) END) AND '@{AsOfMonth}'

This works if the month in database is in the format 'YYYY / MM'. I had earlier posted a way to do rolling 12 months based on date field in prompts and use TIMESTAMPDIFF function also. However that method has a disagvantage that it does not work with constrained prompts unless the date in database is exactly in the format of 'DD-MMM-YYYY'.

Using Presentation variables in dashboard

We can use presentation variable to assign values in dashboard prompts. To refer these variables in answers following syntax should be used:

'@{PresentationVariable}{Default}'

The benefit of using this is we don't have to make a RPD change. Besides by specifying a default value, the report returns value even when users edit the report in answers. This should be used if business users will edit the reports.

Adding a custom image on dashbaords

save logo as jpg, then copy to \OracleBI\web\app\res\s_oracle10\images\ on server, then you can use it like any other report logo. Code for the same is;

We use following code to embed logo besides the report name and report description:

<table class="TitleTable" cellspacing="0" style="" width="100%"><tr><td class="TitleLogo" style="" rowspan="4"><img border="0" src="res/s_oracle10/images/report_salesVolume.jpg"></td><td class="TitleCell" style="" title="">Sales Summary</td></tr><tr><td class="TitleNameCell" style="" title=""></td></tr><tr><td class="SubtitleCell" style="">Provides a summary of both direct and indirect sales dollars & units for selected customer and time period.</td></tr></table>

OBIEE Sawserver Memory Issue

Many times BI sawserver memory keeps increasing but is not released once reports are not run. To manage force release of unused memory following parameter in instance config file is useful:

<AutoFlushThreshhold>262144</AutoFlushThreshhold>

PDF download Issues

We had an issue with downloading PDF files which were large. By increasing default PDF cache size in instance config file as below we were able to download large PDFs too.

File to be changed is located at:

d:\OracleBIData\web\config\instanceconfig.xml


Lines to be added:

<JavaMinHeapPDF>200000000</JavaMinHeapPDF>
<JavaMaxHeapPDF>200000000</JavaMaxHeapPDF>


The min java heap size can be reduced, but we kept this just to verify if this change is helping PDF to download.

Download Data values do not match report output

OBIEE Version 10.1.3.3.1 had a bug that when user download data (export to CSV) from dashboard, the server actually reruns the report instead of downloading the output. The issue not only leads to delayed response time as query has to be rerun, it lead to incorrect results if the report uses session or presentation variables.

Apparently this issue is fixed in 10.1.3.4 although the release notes does not say so.

Random

Very good way to duplicate test data is using random function. Syntax for the same is as below:

select dbms_random.value from dual

Changing log level for LDAP users

We use LDAP server to authenticate BI users. One of the user was having problem with a report which no one else had. In order to track this issue, we wanted to increase log level for only one user to 5. However we could not do this on a per user bases for LDAP.

Looking at session variables I found a work around for this. Navigate to system session variables, right click and create a 'New LOGLEVEL'. You can give default value of 2, or any other which will get applied to all users.

Next, create a session Initialization block loglevel and select any database as datasource. In the query, you can specify following:

select case when (':USER') = 'user1' then to_number('5',0) else to_number('2',0) end from dual

Select target variable as LOGLEVEL. Doing this will make log level of 5 for user1 but it will remain as 2 for all others.

Also, although valid log level value is upto 7, actual value in current version is only upto 5. Values 6 and 7 is reserved for future use.

First Blog

Thank you for visiting my blog. I will use this space to share new findings and technical issues faced while working on Oracle Business Intelligence. Currently I am working on OBIEE 10.1.3.3.3 version. There are many issues that are not addressed in Administration and User guides for which I find there is a work around. I will post most of those issues with solutions here, and hope to make someone life easier.