The patch is available at Oracle Support:  13742915

Bug fixes in this patch listed on the Oracle Site include:

13567594:SDE_FUSION_NATURALACCOUNTDIMENSION MAPPING FAILS IN RUP2 ETL
13355378:PROGRESS BAR HANGS FOR THE 'ADMINTOOL/COMMAND' UTILITY (PATCH COMMAND)
13360114:RPDPATCH DECISION FILE NEEDS TO FLAG DELETES THAT CONFLICT
13596792:PATCHRPD UNABLE TO HANDLE DELETED COMPLEX JOIN
13583718:SERVER CRASH DURING INTERNAL FULL OUTER STITCH JOIN ON COLUMNS WITH NUMERIC TYPE
13583449:SETTING WINDOW SIZE AND START RECORD RESULTS IN NO RECORDS (EXISTING PRODUCT)
13578840:FUSIONBI BI SERVER INITBLOCKS WITH UNIONS DONT BYPASS DIRECT DB ACCESS
13560202:EXTRACTPROJECT.EXE CRASHES DURING THE PATCHING PROCESS
13393226:INDEXCOL NOT TAKING MINUS CALCULATION (WORKS IN 10G BUT NOT IN 11G)
13568567:PROJECT EXTRACT FAILS TO EXTRACT CERTAIN PROJECTS DURING PATCHING
13596457:PATCHRPD IS NOT CORRECTLY UPDATING PROJECTS WHEN SUBSET PATCHING IS BEING USED
13581658:BIAPPS RUP2 FORCE WEBCAT UPGRADE
13628171:CONSISTENCY CHECK DOESN'T FIX MISSING LTS FOLDERS
13526632:DTE ESSBASE CANNOT CONNECT TO BI
13636501:MATS TESTWEBCATALOG DUMMYCATALOG.DIF
13568305:OBJECT PROPERTY DIALOG AND UDML CODE SHOW DIFFERENT NAMES IN COMPARE
13383217:QDR ISCUSTOMGROUPUNIQUEID DOES UNNECESSARY STRING CONCATENATION
13582384:RUP2 HCM CREDENTIALACCESSPERMISSION IN BI STARTUP LOGS AFTER PATCHING
13640792:CONSISTENCY CHECK DOESN'T FIX UNKNOWN LOGICAL COLUMN TYPE
13640427:RUP2-AIX BI CONVERSION FROM XML TO RPD FAILS WITH PASSWORDS DIFFERENT
13625435:RUP2-ST-CDRM FORMMATING AND ACTION LINK ISSUE AFTER PS5 UPGRADE
13458667:hidden option not available for date columns in table layout
13477267:pm rename view not working in current build.
13401386:auto preview are not prevented except pivot view
13586650:qa psbh1  crash display selection steps view exception condition dble col qdr
13616593:auto preview are not prevented for graph
13517767:positional calc can cause xmlwriter empty document error
13564003:SAVING AN IBOT(AGENT) GIVES ORACLE BI SCHEDULER ERROR [NQSERROR 76016]
13110980:THE SECTION PROPERTIES OF PIVOT TABLE IS NOT EFFECTIVE OR NOT CORRECT
13563800:INCLUCDE SELECTED MEMBER NOT WORKING FOR LEAVES OF IN VALUE HIERARCHY
13592629:CANNOT RECREATE THE SAME CURRENT RPD FROM BISERVERXMLEXEC.EXE
13634568:WIN64  WEBCAT APPLY PATCH FAILED  CATMAN INVALID CHARACTERS IN PATH
13502703:ATTR SELECTIONS ON HIERARCHY COLS SHOULD ALLOW PROMPTING ON COMPARE COL.
13553428:DELIVER TO CORP OID LDAP USERS FAILED WITH IMPERSONATOR DOES'NT EXIST
13627864:BACKPORT OF BUG 13597266 TO 11.1.1.6.1
13566162:otbi cascading b/w two prompt sets is not happening in ps5rc2(11.1.1.6.0)
13626098:RUP2-ST-CDRM  DRILL DOWN REPORT NOT RETURNING OUTPUT
13692889:RPD VALIDATION SCRIPT NEEDS TO BE INSTALLED INTO THE ORACLE HOME
13573564:audit  wrong subject area when webcat is large
13375536:audit  catman report not displaying columns
13573558:audit  case sensitive check against rpd
13578848:audit  unable to report on sql in non-analysis
13642995:BISERVERXMLEXEC CRASHES WHILE REGENERATING PS1 RPD
13561559:RUP2-ST2-CDRM  SECTION FORMATTING IS SKIPPED WHEN APPLY PROMPT OR CLICK REFRESH
13574731:QM CRMQA_RRF - DASHBOARD CONTENT
12978196:column selector does not pass values from summary report to detailed report
13680310:ERROR DISPLAYING VIEW WITH NESTED AND SAVED CALC ITEMS
13707164:npe in catalogtaghandler.removejunkfromxml
13332593:PATCHING FAILED WITH ERROR DURING THE XML EXECUTION
13623096:AFTER APPLYING PATCH:13428026, THE SEGMENTATION FAULT IS HAPPENED
13403816:after applied mlr 13110245 still having the hierarchical prompt issue
13535937:NEGATIVE VALUE IN CACHE MANAGER
13710970:BISERVERXMLEXEC CRASHES DUE TO BROKEN NQSTRING'S C_STR() METHOD
12992697:PAGE BREAK WITH COLUMN BREAK DOES NOT WORK WHEN USE PRINTABLE HTML
13249783:AFTER APPLY PATCH 129725592 OBIEE 11G THROWS BAD XML INSTANCE
13098323:AN ARITHMETIC OPERATION IS BEING CARRIED OUT ON A NON-NUMERIC TYPE
13043745:PIVOT TABLES LAYOUT COLLAPSES WHEN HIDDEN ROW AND TOTALS BEFORE IS USED
13719583:38028 CONSISTENCY CHECK ERROR SHOULD BE MADE WARNING
13350175:ADDING FILTER TO DIFFERENT LEVELS OF A HIERARCHY GETS IGNORED IN MDX
13083074:PROMPTS CREATING UNSPECIFIED ERROR WHEN SELECTING MULTIPLE CONSTRAINED CRITERIA
13546009:SELECT PIVOT TABLE IN VIEW SELECTOR CAUSES COLUMN TO BE EXCLUDED WHEN DRILLING
13733427:ADD AND UPDATE VERSION STRING TO DIFF FILE
13071711:CIRCULAR JOIN ERROR WHEN RUNNING ANALYSIS USING UPGRADED REPOSITORY
13649502:rup2-st1cdrm  drill down report not returning output - staying busy
13522060:RPD SESSION VARIABLE UNABLE TO IDENTIFY THE DYNAMIC SCHEMA NAMES FOR OLAP
13626491:REPORTS ARE FAILING AFTER APPLYING PATCH13470147 WARNINGS IN CONSISTENCY CHECK
10647655:COLUMN DATA FORMAT IS IGNORED ON WRITEBACK
13724814:ADD ABILITY TO RETRIEVE QUERY METADATA ON ISSUE RAW SQL SCREEN
13405110:opening report crashes bi presentation services
13393602:rsum not calculating correctly when there are excluded columns
13567100:case when used in measure in pivot is not working as in 10g
12612904:total picks value in first row and gives not a number when averaging null
13647309:obiee grand total functionality incorrect and inconsistent with olap cubes
13400140:multi-step promt missing the dashboard prompt which limit values by is set
13523548:error out when using multiple double columns in saved filters
13444157:error when use is in top operator in filter with presentation variable
13533404:SECTIONS HEADERS ARE CAUSING DASHBOARD SPACING ISSUES
13501903:PATCH FOR BUG 11837656 DOES NOT FIX THE ISSUE WITH DOUBLE QUOTES
13724995:CONSISTENCY CHECK CRASHES ON PS1 RPD
13418590:export to excel still not working properly after patch 12394949 (linux 64)
13682411:CALC ITEM NOT DISPLAYED IN A PIVOT TABLE AFTER PATCH 13544352
13562847:PG/PROMPTS DROPDOWN LISTS ONLY GROUP MEMBERS AFTER PICK GRP FROM DROPDOWN
13739082:ASSERTION ERROR IF VALUE HIER WITH CALC ITEM MOVE TO PAGE/PROMPTS EDGE
12981896:mapviewer hover tooltips show wrong values

• Changes in the report creation process, a new and better process flow for designing reports.
• New option to display parameters as check boxes and radio buttons.
• Parameters can now be displayed in a vertical region or on a separate full page. Report users can also show or hide parameters.
• Search option is now available in parameter menus.
• Report developers can now hide the “Apply” button in parameter region. If the button is removed, selecting a new parameter value will rerun the report.
• HTML formatting is now supported in reports. Some HTML features such as Nest lists, inserted tables and images are still not supported.
• New output types PDF/A and PDF/X. These are advanced variations of PDF standard.

For more details refer to the oracle documentation at: http://www.oracle.com/technetwork/middleware/bi-publisher/documentation/index.html

The Software download is now available on edelivery.oracle.com: https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=12854069

More on new features to follow soon….

Firefox OBIEE Error.

The upgraded firefox version is 10.0.1:

Firefox Upgrade 10.0.1

I’ve had no issue using BI Publisher. It seems like the upgrade only impacts Analytics.

Oracle Support states, “If you need a fix now, please contact Oracle Support who can request a one-off patch suitable for your environment.” Firefox 10 is scheduled to be supported in the next release and 11.1.1.5 patch bundle.

I found a patch on the Oracle Support site but this patch is only for Windows 32 Bit Platform. Patch 12877995.

Search for: Patch 13683731: UPDATE FOR BI PUBLISHER ENTERPRISE 11.1.1.5.0 FEBRUARY 2012

The bug fixes listed on the Oracle Site include:

9102877 – QA: QUERY BUILDER GENERATES INCORRECT SQL FOR MANY DBS
10071370 – IF ‘USE SECURE FTP’ IS CHECKD ON ADMIN PAGE, ITS SERVER IS NOT LISTD WHEN SCHED
10402890 – SETTINGS FROM “DELIVERY CONFIGURATION” PAGE NOT BEING HONORED WHEN REPORTS RUN
11678983 – QA:LDAP CONFIG ASSUMES THAT ADMIN IS DEFINED UNDER USERS (DISTING NAME FOR USERS
11725468 – ER: RTE HTML SUPPORT IN DATA XML
11828672 – QA: INCORRECT INACTIVE WARNING MESSAGE
11835845 – 11G SQL DATA SET CREATION ORA-01756 ERROR BECAUSE COLON IS READ AS BIND VARIABLE
11850902 – SHARE REPORT LINK “NO PARAMETERS” AND “DOCUMENT ONLY” RENDER INCORRECTLY
11855658 – SEARCH PARAMETER THROWING ERROR IN XML VIEW UI
11870449 – BI PUBLISHER XDOXSLT:SDIV() FUNCTION RESULTS NOT CORRECT
12312539 – QA: REPORT VIEWER SCREEN FREEZES FOR A FLEX REPORT WHEN SESSION EXPIRES
12376386 – REMOVETEMPLATEFORREPORT API DOESN’T REMOVE TEMPLATE
12405904 – CANNOT DEPLOY BIP WHEN DEPLOYMENT PATH INCLUDES SPACE CHARACTERS
12406034 – CANNOT USE JAVA DB – DERBY – FOR SCHEDULER DATABASE
12538753 – OUTPUT FILE INCONSISTENCY USING EMAIL WHEN FORMAT IS EXCEL2007 IN BIP SCHEDULER
12570248 – PROJECTS : -GENERATE REV/INV PROCESS ERRORING OUT
12588342 – DELETEOBJECT RANDOM FAILURE
12595931 – WHEN PRINTING CHARTS TO PDF, NUMBERS BECOME ARABIC NUMBERS
12615821 – MISSING MEMBERS ON THE XML GENERATED BY BIP
12634030 – RE-PACKAGING MLR#11893189 TO XDOPARSER11G.JAR
12646493 – ORA-22902 CURSOR EXPRESSION NOT ALLOWED
12666066 – ER: EXCEL 2007 SUPPORT IN REST API
12705244 – JOINED MDX DATA SETS DO NOT PRODUCE CORRECT DATA – DATA MISSING
12726163 – NUMBER OF SESSIONS INCREASES AFTER RUNNING SCHEDULED JOBS
12737670 – GROUP BY FOR MDX DATA SET MISSING IN 11.1.1.5 – WAS PRESENT IN 11.1.1.3
12741858 – BI ASNWERS ANALYSIS IMPLEMENTING CASE WHEN STATEMENTS CAN NOT BE CONSUMED IN BIP
12750192 – ER: GENERATE NATIVE COMBINATION DUAL Y GRAPH IN PPTX OUTPUT FROM RTF AND XPT
12766932 – VERTICAL DUAL Y COMBO CHART MISSING SETTINGS TO ASSIGN VALUES TO AXIS
12775400 – DBFW:GENERATING IN XLXS OUTPUT IS THROWING AN ERROR
12822514 – SQL WITH CLAUSE – DATASET DOESN’T WORK (AND DIAGNOSTIC ISN’T HELPFUL)
12828845 – ISSUE WITH XDO_USER_NAME PARAMETER ON BIP 11.1.1.5.0
12835431 – INTRANSIT VALUATION REPORT QUERY GIVING ERROR IN EDITOR
12847306 – BI PUBLISHER DATA SET – WON’T SAVE
12872831 – EXCEL TEMPLATES GENERATING INCORRECT RESULTS FROM NESTED GROUPS
12879801 – ALLOW SAVING DATA MODEL WITH SPECIAL CHARACTER
12945367 – NOT ABLE TO EDIT DATA MODEL WHEN LINKS EXIT AMONG DATA SETS
12945475 – IE: NOT ABLE TO EDIT DATA MODEL WITH MANY DATA SET AND LINKS
12956901 – IN SQL SERVER CLOB IN DATA MODEL UI MARKED AS STRING DATA TYPE
12956915 – INCORRECT DATA WHEN PROCESSING OF CLOB COLUMNS FOR SQLSERVER
12956934 – DATA MODEL SHOULD RESPECT CASE SENSITIVITY FOR COLUMN AND TABLE NAMES
12972559 – XML DATA FILE TRUNCATED
12972852 – DYNAMIC IMAGES BROKEN FOR FO/PDF
12989467 – NEED TO CONVERT DOCUMENT FORMAT “EXCEL” TO “EXCEL2000″
13005659 – PSR:PERF:BIP:POOR PERF ACCESSING CATALOG FOLDER IN BIP 11G WITH SECURITY ENABLED
13007341 – DISPLAY ISSUES IN PARAMETER TAB OF OBI PUBLISHER 11G
13095050 – STRESS: OUTOFMEMORY ERRORS WITH VIEW REPORT STRESS
13100021 – BI PUBLISHER: QUERY BUILDER RESULTS RETURN COLUMN TYPE (APPEARS) INSTEAD VALUE
13100220 – HOW TO USE 11.1.1.5 WITH THE IDENTITY ASSERTER
13250009 – FOR METADATA LOGICAL SQL BIND WITH DUMMY VALUES INSTEAD OF NULL
13321449 – RESOURCELIMITEXCEPTION DURING ANY OPS THAT USE JNDI DATA SOURCE
13346737 – MULTIPLE VALUES IN A DASHBOARD PROMPT DOESN’T WORK WITH BI PUBLISHER INTEGRATED
13351305 – QUERY BUILDER DOES NOT BUILD CORRECT SYNTAX FOR SQL SERVER
13391941 – FROM BI 11G QUERY BUILDER:THE COLUMNS THAT HAVE SPACES ARE NOT DISPLAYED AT ALL
13393658 – PRINTER DELIVERY [0] STATUS: FAILED PRINTING FROM WEB SERVICE CALL FROM EXTERNAL
13421264 – ADDING PARAMETER TO SQL BI SERVER DATA SET CORRUPTS DATA MODEL
13459907 – NONSTRESS:FA:PRC PS5 BASED FAINTEG: PDF GEN FAILS, XDOEXCEPTION
13481659 – CAN’T SAVE ON BI DEV ENV RC2
13501838 – ORA-00923 ISSUE WHEN CREATING A BI PUBLISHER DATASET
13521951 – BIP UPGRADE FROM 10G TO 11.1.1.5.0 IS NOT SUCCESSFULL FOR TIAA-CREF
13562801 – XML TAG DISPLAY SHOULD DEFAULT TO ‘FOLLOW THE DATA STRUCTURE’
13529494 – BI REPORT NOT GENERATED WHEN HTML CONTAINS INVALID ATTRIBUTES
13568043 – BIP QUERY FAILING VALIDATION DUE TO ‘COALESCE’ KEYWORD
13592901 – THE REPORT IS THROWING AN SQL ERROR THAT REFERENCES CHECKING FOR NULL VALUES
13611515 – CAN’T SEE PERMISSIONS FOR JAPANESE FOLDER, REPORTS.
13684789 – UNABLE TO SELECT COMPANY PROMPT. PROMPT VALUES ARE HIDDEN BELOW THE PDF

To calculate difference between two dates, OBIEE provides a TIMESTAMPDIFF function. The syntax for using the function is:

TIMESTAMPDIFF(interval, first_date, second_date)

First_date and second_date have to be valid values in the TIMESTAMP type. Interval is a specified value.  Valid values for INTERVAL are:

• SQL_TSI_SECOND
• SQL_TSI_MINUTE
• SQL_TSI_HOUR
• SQL_TSI_DAY
• SQL_TSI_WEEK
• SQL_TSI_MONTH
• SQL_TSI_QUARTER
• SQL_TSI_YEAR

If the dates are not in a valid TIMESTAMP format, use CAST function to convert values into TIMESTAMP type. See example below:

TIMESTAMPDIFF(SQL_TSI_DAY, CAST(VALUEOF("MY_DATE_COLUMN") as TIMESTAMP), CURRENT_DATE)

Date Difference Formula

In the example, we are subtracting MY_DATE_COLUMN date from CURRENT_DATE. Note how we apply CAST to MY_DATE_COLUMN. CURRENT_DATE holds the current date (it’s like SYSDATE in SQL) and has the TIMESTAMP type by default. The difference returned will be in number of days as we have used SQL_TSI_DAY interval.

You can also use TIMESTAMPADD function to add an interval to a date. Example:

TIMESTAMPADD(SQL_TSI_DAY, 7, TIMESTAMP '2011-11-01 12:00:00')

A search on the Oracle support site, let us to a solution (ID 1359064.1).

The issue occurs because some roles are missing for SOAP. The roles can be updated in the “SOAP Section” under Manage Privileges.

1. Log into OBIEE 11g with Administrator privileges -> Click on Administration -> Manage Privileges.
2. Go to the “SOAP Section” and assign following roles:

Assign BI Consumer and BI System roles to the Access to SOAP privilege

Assign BI Administrator and BI System roles to Impersonate as System User privilege

After making the changes, refresh the BI Publisher page. The page will show up correctly. I didn’t have to restart any services after assigning the privileges.

BI Publisher Home Page works after assigning privileges

When I started the template builder in MS Word and tried loading sample data (BI Publisher -> Sample XML), I got an error “Compile error in hidden module: Module_starter”. Here is a screen shot of the error:

BI Publisher Desktop Error

After some investigating and looking up Oracle BI Publisher forum, I found two fixes for the error:

1) Set the Template Builder for Word Language. After installing BI Publisher Desktop, go to BI Publisher Desktop program menu and set the UI language. Close any running Microsoft Office programs. Steps: All programs -> Oracle BI Publisher Desktop -> Template Builder for Word Language. Select your language and click OK.

Template Builder for Word Language

2) The BI publisher add-in uses Visual Basic software components. When I try to run the add-in, the old versions of VBA components are found to be incompatible with the newer add-in, and it may cause the error. The second fix is to delete older versions of the software components, specifically the EXD files. The steps to delete these files are documented here: http://www.treeplan.com/object-library-invalid-error.htm

Go to your user folder using a DOS command prompt and execute: DEL /S /A:H /A:-H *.EXD

After setting the Word Language and deleting the EXD files, I was able to use Template Builder in Word. The compile error in hidden module was resolved.

It seemed to me the invalid password was being stored/cached somewhere on the system and weblogic startup services were not letting me enter the correct password. So after running some searches, I found a file that stored the weblogic admin username and password.

The file is called “boot.properties” and it can be found under {OBIEE_HOME}\user_projects\domains\bifoundation_domain\servers\AdminServer\security on windows.

I renamed the file to boot.properties.old and restarted BI Services. This time, the start up script prompted me for admin username and password again. I entered the correct credentials and our BI services were restored.

For detailed instructions or new features in 11g release 2, refer to the Oracle Spatial User’s Guide and Reference. The scripts executed in the following instructions are listed in the oracle database home directory:

C:\app\product\11.2.0\dbhome_2\md\doc\readme.txt

Step 1: Connect to the database where you want to install Oracle Spatial using SYSDBA privileges (use the SYS or SYSTEM accounts). For my install I used SQL*Plus to connect to the database.

Step 2: Verify prerequisites. To install Oracle Spatial, we need to have JAVA virtual machine, Oracle intermedia and Oracle XML database products already installed. Verify if the products are installed by executing the following SQL:

SQL> SELECT comp_id, version, status FROM dba_registry
2  WHERE comp_id IN ('JAVAVM','ORDIM','XDB')
3  /

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ -----------
ORDIM                          11.2.0.1.0                     VALID
XDB                            11.2.0.1.0                     VALID
JAVAVM                         11.2.0.1.0                     VALID

Step 3: The install scripts also require a MDSYS user account. Ensure MDSYS user exists or create one before installing Oracle Spatial:

SQL> select username from dba_users
2  where username like 'MD%'
3  /

USERNAME
------------------------------
MDSYS
MDDATA

-- If the user does not exist then create the user

SQL> CREATE USER mdsys IDENTIFIED BY <password> DEFAULT TABLESPACE sysaux ACCOUNT LOCK
2  /

Step 4: Execute scripts to install Oracle Spatial:

SQL> spool c:\spatial_install.txt
SQL> @C:\app\product\11.2.0\dbhome_2\md\admin\mdprivs.sql
SQL> @C:\app\product\11.2.0\dbhome_2\md\admin\mdinst.sql
SQL> spool off

Step 5: Verify Oracle Spatial is installed correctly. There should not be any invalid objects in MDSYS account:

SQL> SELECT comp_id, control, schema, version, status, comp_name
2  FROM   dba_registry
3  WHERE  comp_id = 'SDO'
4  /

COMP_ID                        CONTROL
------------------------------ ------------------------------
SCHEMA                         VERSION                        STATUS
------------------------------ ------------------------------ -----------
COMP_NAME
--------------------------------------------------------------------------
SDO                            SYS
MDSYS                          11.2.0.1.0                     VALID
Spatial

1 row selected.

SQL> SELECT object_name, object_type, status FROM dba_objects
2  WHERE  owner = 'MDSYS'
3  AND    status <> 'VALID'
4  ORDER BY 1
5  /

no rows selected