Monday Jan 09, 2012

BI Publisher January patch for 11.1.1.5.0

The BI Publisher January patch for 11.1.1.5.0 is now available on Metalink; look for patch 13554951. Thanks to Shinji and team, this is one of the easiest posts of the year so far :0) Contents of the readme below, enjoy!


One-off patch for Oracle Business Intelligence Publisher 11.1.1.5.0 READ ME File ==================================================
This patch contains fixes of following bugs for Oracle Business Intelligence Publisher 11.1.1.5.0.

 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
 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
 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
 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
 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
 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
 13529494 - BI REPORT NOT GENERATED WHEN HTML CONTAINS INVALID ATTRIBUTES
----------------------------- INSTALLATION FOR ENTERPRISE -----------------------------
1. Login to BI Domain Weblogic Administration Console.
2. Find the location of the "bipublisher" application.
3. Stop the bipublisher application.
4. Open a terminal and go to the directory.
5. Back up the existing xmlpserver.ear to xmlpserver.ear.org.
6. Copy new xmlpserver.ear from this patch.
7. In Admin Console, select "Lock & Edit".
8. Select the application "bipublisher" and "Update".
9. Leave all options same and proceed to Finish.
10. Select "Activate Changes".
11. Start the bipublisher application.

------------------------- MDX Query Limitation -------------------------
Joins of two MDX queries on a dimension that uses a MDX function in the Select Clause that requires the stipulation of the same dimension, such as Intersect, MemberRange, Union are not supported. Example)
SELECT
  NON EMPTY Hierarchize([Product].Generations(3).Members) ON Axis(0),
  Hierarchize(Union([Market].Generations(3).Members, [Market].Generations(4).Members, ALL)) ON Axis(1)
FROM Demo.Basic
It should be written using a With Set member:
with set [UnionMarket] as '{Union([Market].Generations(3).Members, [Market].Generations(4).Members, ALL)}'
SELECT
  NON EMPTY Hierarchize([Product].Generations(3).Members) ON Axis(0),
  Hierarchize(([UnionMarket]) ON Axis(1)
FROM Demo.Basic 
This MDX set is recreated for the Join clause to:
with set [UnionMarket] as '{Union([Market].Generations(3).Members, [Market].Generations(4).Members, ALL)}'
SELECT
  NON EMPTY Hierarchize([Product].Generations(3).Members) ON Axis(0),
  Hierarchize(Filter([UnionMarket], [Market].CurrentMember.Member_Name = "Boston")) ON Axis(1)
FROM Demo.Basic
----------------------------------------- Query Builder limitation with Microsoft SQL Server -----------------------------------------
Query Builder doesn't support a cross join that does not have a WHERE clause and produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set of such join is the number of rows in the first table multiplied by the number of rows in the second table. A cross join has a very limited use in everyday data retrieval.

----------------------------- HTML text support in XML -----------------------------
This patch supports HTML embedded in XML. The following new layout command is added to retain HTML format from data in the final output. <?html2fo: xpath?> Example: html code in xml data: user should input the html code to xml data file as CDATA. sample, html code is embedded in field RTECODE.
<?xml version="1.0" encoding="UTF-8"?>
<RTECODE>
<![CDATA[
<font style="font-style: italic; font-weight: bold;" size="3"><a
href="http://www.oracle.com">oracle</a></font> <br/>
<font size="6"><a href="www.oracle.com">www.oracle.com</a></font><br/><br/>
]]>
</RTECODE>


Sample usage in rtf template (assume the data.xml is as above).
<?html2fo:RTECODE?>
Supported html formats: Paragraph Font style ( bold, italic, plain, underline, subscript, superscript and strikes-through) Font size Font family Background color Foreground color Paragraph alignment (center, left, right and justify) Paragraph indent URL link List Bullet List Number List Non-Supported formats: Nested List (List with Indent) Some HTML tags/attributes which manually inserted like Table, Image, etc... in stead of HTML editor Sample Report: XML: html_test.xml RTF tempalte: html_test.rtf

Tuesday Dec 06, 2011

11.1.1.5 December 2011 Update Available

Latest and greatest from the boys and girls at the coal face for December. Bug fixes and some interesting enhancements that I hope to dig into to here soon.

Search for patch 13366758: UPDATE FOR BI PUBLISHER ENTERPRISE 11.1.1.5.0 DECEMBER 2011 on Oracle Support. Make sure you check out the readme for patch installation instructions.

 9102877 - QA: QUERY BUILDER GENERATES INCORRECT SQL FOR MANY DBS
 10402890 - SETTINGS FROM "DELIVERY CONFIGURATION" PAGE NOT BEING HONORED WHEN REPORTS RUN
 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
 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
 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
 13421264 - ADDING PARAMETER TO SQL BI SERVER DATA SET CORRUPTS DATA MODEL

 

Enjoy!

Monday Sep 19, 2011

11g September Rollup

The latest rollup patch for BIP 11.1.1.5 is available, 12954375. For your delight and delectation and to bulk out this post, I have reproduced the readme file below.

This patch contains fixes of following bugs for Oracle Business Intelligence Publisher 11.1.1.5.0.

 10402890 - SETTINGS FROM "DELIVERY CONFIGURATION" PAGE NOT BEING HONORED WHEN REPORTS RUN 
 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
 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
 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
 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
 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
 12791282 - WS PROXY WIZARD CANNOT GENERATE PROXY WHEN SERVICE NAME HAS SPECIAL CHARACTERS
 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
 12972852 - DYNAMIC IMAGES BROKEN FOR FO/PDF
 12989467 - NEED TO CONVERT DOCUMENT FORMAT "EXCEL" TO "EXCEL2000"

----------------------------- INSTALLATION FOR ENTERPRISE -----------------------------

1. Login to BI Domain Weblogic Administration Console.

2. Find the location of the "bipublisher".

3. Stop the bipublisher application.

4. Open a terminal and go to the directory.

5. Back up the existing xmlpserver.ear to xmlpserver.ear.org.

6. Copy new xmlpserver.ear from this patch.

7. In Admin Console, select "Lock & Edit".

8. Select the application "bipublisher" and "Update".

9. Leave all options same and proceed to Finish.

10. Select "Activate Changes".

11. Start the bipublisher application.

------------------------- MDX Query Limitation -------------------------

Joins of two MDX queries on a dimension that uses a MDX function in the Select Clause that requires the stipulation of the same dimension, such as Intersect, MemberRange, Union are not supported. Example

SELECT
  NON EMPTY Hierarchize([Product].Generations(3).Members) ON Axis(0),
  Hierarchize(Union([Market].Generations(3).Members, [Market].Generations(4).Members, ALL)) ON Axis(1)
FROM Demo.Basic
 
It should be written using a With Set member:
 
with set [UnionMarket] as '{Union([Market].Generations(3).Members, [Market].Generations(4).Members, ALL)}'
SELECT
  NON EMPTY Hierarchize([Product].Generations(3).Members) ON Axis(0),
  Hierarchize(([UnionMarket]) ON Axis(1)
FROM Demo.Basic
This MDX set is recreated for the Join clause to:
 
with set [UnionMarket] as '{Union([Market].Generations(3).Members, [Market].Generations(4).Members, ALL)}'
SELECT
  NON EMPTY Hierarchize([Product].Generations(3).Members) ON Axis(0),
  Hierarchize(Filter([UnionMarket], [Market].CurrentMember.Member_Name = "Boston")) ON Axis(1)
FROM Demo.Basic
Plus you have the new XHTML formatter included in the patch too, more on that here.

 

Friday Jun 17, 2011

Upgrade Your Existing BI Publisher 11g (11.1.1.3) to 11.1.1.5

It’s already more than a month now since BI Publisher 11.1.1.5 was released at beginning of May. Have you already tried out many of the great new features? If you are already running on the first version of BI Publisher 11g (11.1.1.3) you might wonder how to upgrade the existing BI Publisher to the 11.1.1.5 version.

There are two ways to do this, one is ‘Out-Place’ and another is ‘In-Place’. The ‘Out-Place’ would be quite simple. Basically you will need to install the whole BI or just BI Publisher standalone R11.1.1.5 at a different location then you can switch the catalog to the existing one so that all the reports will be there in the new 11.1.1.5 environment.

But sometimes things are not that simple, you might have some custom applications or configuration on the original environment and you want to keep all of them with the upgraded environment. For such scenarios, there is the ‘In-Place’ upgrade, which overrides on top of the original environment only the parts relevant for BI and BI Publisher, and that’s what I’m going to talk about today.

Here is the basic steps of the ‘In-Place’ upgrade.

  1. Upgrade WebLogic Server to 10.3.5
  2. Upgrade BI System to 11.1.1.5
  3. Upgrade Database Schema
  4. Re-register BI Components
  5. Upgrade FMW (Fusion Middleware) Configuration
  6. Upgrade BI Catalog

There is a section that talks about this upgrade from 11.1.1.3 to 11.1.1.5 as part of the overall upgrade document. But I hope my blog post summarized it and made it simple for you to cover only what’s necessary.

Upgrade Document: http://download.oracle.com/docs/cd/E21764_01/bi.1111/e16452/bi_plan.htm#BABECJJH

Before You Start

Stop BI System and Backup

I can’t emphasize enough, but before you start PLEASE make sure you take a backup of the existing environments first. You want to stop all WebLogic Servers, Node Manager, OPMN, and OPMN-managed system components that are part of your Oracle BI domains. If you’re on Windows you can do this by simply selecting ‘Stop BI Services’ menu. Then backup the whole system.

Upgrade WebLogic Server to 10.3.5

Download WebLogic Server 10.3.5 Upgrade Installer

With BI 11.1.1.3 installation your WebLogic Server (WLS) is 10.3.3 and you need to upgrade this to 10.3.5 before upgrading the BI part. In order to upgrade you will need this 10.3.5 upgrade version of WLS, which you can download from our support web site (https://support.oracle.com)

You can find the detail information about the installation and the patch numbers for the WLS upgrade installer on this document.

Just for your short cut, if you are running on Windows or Linux (x86) here is the patch number for your platform.

  • Windows 32 bit: 12395517:
  • Linux: 12395517

Upgrade WebLogic Server

1. After unzip the downloaded file, launch wls1035_upgrade_win32.exe if you’re on Windows.

image

2. Accept all the default values and keep ‘Next’ till end, and start the upgrade.

Once the upgrade process completes you’ll see the following window.

image

Now let’s move to the BI upgrade.

Upgrade BI Platform to 11.1.1.5 with Software Only Install

Download BI 11.1.1.5

You can download the 11.1.1.5 version from our OTN page for your evaluation or development. For the production use it’s recommended to download from eDelivery.

1. Launch the installer by double click ‘setup.exe’ (for Windows)

2. Select ‘Software Only Install’ option

image

3. Select your original Oracle Home where you installed BI 11.1.1.3.

image

4. Click ‘Install’ button to start the installation.

image

And now the software part of the BI has been upgraded to 11.1.1.5. Now let’s move to the database schema upgrade.

Upgrade Database Schema with Patch Assistant

You need to upgrade the BIPLATFORM and MDS Schemas. You can use the Patch Assistant utility to do this, and here is an example assuming you’ve created the schema with ‘DEV’ prefix, otherwise change it with yours accordingly.

Upgrade BIPLATFORM schema (if you created this schema with DEV_ prev)

psa.bat -dbConnectString localhost:1521:orcl -dbaUserName sys -schemaUserName DEV_BIPLATFORM

Upgrade MDS schema (if you created this schema with DEV_ prev)

psa.bat -dbConnectString localhost:1521:orcl -dbaUserName sys -schemaUserName DEV_MDS

Re-register BI System components

Now you need to re-register your BI system components such as BI Server, BI Presentation Server, etc to the Fusion Middleware system. You can do this by running ‘upgradenonj2eeapp.bat (or .sh)’ command, which can be found at %ORACLE_HOME%/opmn/bin.

Before you run, you need to start the WLS Server and make sure your WLS environment is not locked. If it’s locked then you need to release the system from the Fusion Middleware console before you run the following command.

image

Here is the syntax for the ‘upgradenonj2eeapp.bat (or .sh) command. 

upgradenonj2eeapp.bat
   -oracleInstance Instance_Home_Location
   -adminHost WebLogic_Server_Host_Name
   -adminPort administration_server_port_number
   -adminUsername administration_server_user

And here is an example:

cd %BI_HOME%\opmn\bin


upgradenonj2eeapp.bat -oracleInstance C:\biee11\instances\instance1 -adminHost localhost -adminPort 7001 -adminUsername weblogic

Upgrade Fusion Middleware Configuration

There are a couple things on the Fusion Middleware need to be upgraded for the BI system to work. Here is a list of the components to upgrade.

  • Upgrade Shared Library (JRF)
  • Upgrade Fusion Middleware Security (OPSS)
  • Upgrade Code Grants
  • Upgrade OWSM Policy Repository

Before moving forward, you need to stop the WebLogic Server. Here is an example.

cd %MW_HOME%user_projects\domains\bifoundation_domain\bin

stopWebLogic.cmd

And, let’s start with ‘Upgrade Shared Library (JRF)’.

Upgrade Shared Library (JRF)

You can use updateJRF() WLST command to upgrade the shared libraries in your domain. Before you do this, you need to stop all running instances, Managed Servers, Administration Server, and Node Manager in the domain.

Here is an example of the ‘upgradeJRF()’ command:

cd %MW_HOME%\oracle_common\common\bin

wlst.cmd

upgradeJRF('C:/biee11/user_projects/domains/bifoundation_domain')

Upgrade Fusion Middleware Security (OPSS)

This step is to upgrade the Fusion Middleware security piece. You can use ‘upgradeOpss()’ WLST command. Here is a syntax for the command.

upgradeOpss(jpsConfig="existing_jps_config_file", jaznData="system_jazn_data_file")

The ‘existing jps-config.xml file can be found under %DOMAIN_HOME%/config/fmwconfig/jps-config.xml and the ‘system_jazn_data_file’ can be found under %MW_HOME%/oracle_common/modules/oracle.jps_11.1.1/domain_config/system-jazn-data.xml.

And here is an example:

cd %MW_HOME%\oracle_common\common\bin

wlst.cmd

upgradeOpss(jpsConfig="c:/biee11/user_projects/domains/bifoundation_domain/config/fmwconfig/jps-config.xml", jaznData="c:/biee11/oracle_common/modules/oracle.jps_11.1.1/domain_config/system-jazn-data.xml")

exit()

Upgrade Code Grants for Oracle BI Domain

And this is the last step for the Fusion Middleware platform upgrade task. You need to run this python script ‘bi-upgrade.py‘ script to configure the code grants necessary to ensure that SSL works correctly for Oracle BI. However, even if you don’t use SSL, you still need to run this script. And if you have multiple BI domains (Enterprise deployment) then you need to run this on each domain.

Here is an example:

cd %MW_HOME%\oracle_common\common\bin

wlst c:\biee11\Oracle_BI1\bin\bi-upgrade.py --bioraclehome c:\biee11\Oracle_BI1 --domainhome c:\biee11\user_projects\domains\bifoundation_domain

Upgrade OWSM Policy Repository

This is to upgrade OWSM (Oracle Web Service Manager) policy repository, you can use WLST command ‘upgradeWSMPolicyRepository()’. In order to run this command you need to have your WebLogic Server up-and-running. Here is an example.

cd %MW_HOME%user_projects\domains\bifoundation_domain\bin

stopWebLogic.cmd

cd %MW_HOME%\oracle_common\common\bin

wlst.cmd


connect ('weblogic','welcome1','t3://localhost:7001')
upgradeWSMPolicyRepository()

exit()

Upgrade BI Catalogs

This step is required only when you have your BI Publisher integrated with BIEE. If your BI Publisher is deployed as a standalone then you don’t need to follow this step.

Now finally, you can upgrade the BI catalog. This won’t upgrade your BI Publisher reports themselves, but it just upgrades some attributes information inside the catalog. Before you do this upgrade, make sure the BI system components are not running. You can check the status by the command below.

opmnctl status

You can do the upgrade by updating a configuration file ‘instanceconfig.xml’, which can be found at %BI_HOME%\instances\instance1\config\coreapplication_obips1, and change the value of ‘UpgradeAndExit’ to be ‘true’. Here is an example:

<ps:Catalog xmlns:ps="oracle.bi.presentation.services/config/v1.1">
<ps:UpgradeAndExit>true</ps:UpgradeAndExit>
</ps:Catalog>

After you made the change and save the file, you need to start the BI Presentation Server. This time you want to start only the BI Presentation Server instead of starting all the servers. You can use ‘opmnctl’ to do so, and here is an example.

cd %ORACLE_INSTANCE%\bin
opmnctl startproc ias-component=coreapplication_obips1

This would upgrade your BI Catalog to be 11.1.1.5.


After the catalog is updated, you can stop the BI Presentation Server so that you can modify the instanceconfig.xml file again to revert the upgradeAndExit value back to ‘false’.


Start Explore BI Publisher 11.1.1.5


After all the above steps, you can start all the BI Services, access to the same URL, now you have your BI Publisher and/or BI 11.1.1.5 in your hands. Have fun exploring all the new features of R11.1.1.5!


image

Thursday May 19, 2011

11.1.1.5 New Feature – More Data to Audit and Monitor

image

We believe Auditing and Monitoring capability for Reporting is crucial for the IT management and governance regardless if it’s enterprise level or department level deployment. So we are always committed to improve our BI Publisher Auditing and Monitoring capability since we introduced it for the first time

With the first release of 11g (11.1.1.3) we added this Auditing and Monitoring capability, which enables you to monitor who access to which report at what time.

With this new release of 11.1.1.5, we added more data to audit and monitor especially more BI Publisher reports specific information such as:

  • Report output format type
  • Layout Template name
  • Reporting locale
  • If the reports is run online (on-demand) or through the scheduler
  • Processing time
  • Reporting data size
  • Report error message
  • Scheduled job information

And these information can be all available through the database tables so you can use BI Publisher to generate your Auditing reports or visualize the data to gain more insights about how your users are doing on your reporting platform.

If you are interested in the power of the new BI Publisher Auditing and Monitoring, here is a video.

And, if you want to know how to enable the Auditing with the new data sets I’ll cover that below.

Enable Auditing Data

There are three things you need to do to enable the Auditing data.

Setup basic Auditing configuration

I have already talked about these steps before, take a look at these two posts for the detail.

Run ‘xmlpserver_audit.sql’

There is a SQL script that you can run to create an additional table, which will store more BI Publisher reports specific data to audit and monitor.

Let’s take a look how to run the script step by step.

1. Change the current directory to ‘%BI_HOME%user_projects/domains/bifoundation_domain/config/bipublisher/repository/Admin/Audit’.

This directory should contain a file called ‘xmlpserver_audit.sql’ file.

2. Run this script as SYS or SYSTEM user and alter the session user to the schema who owns the AUDIT tables.

Example:

Let’s say you have created the Audit schema with the RCU with ‘KAN_IAU’, then you can follow something like below.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 2 13:53:49 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter session set CURRENT_SCHEMA = KAN_IAU;

Session altered.

Now you can run the ‘xmlpserver_audti.sql’ script. Note that, when you run you'll be prompted to enter the following three schema names.

  • Basic Auditing Schema name
  • Edit Auditing Schema name
  • View Auditing Schema name

With the above example of using ‘KAN_IAU’ as your basic schema, it would be something like below.

  • KAN_IAU
  • KAN_IAU_APPEND
  • KAN_IAU_VIEWER

Here is the example:

SQL> @xmlpserver_audit

Table created.

Index created.

Enter value for 1: KAN_IAU

old 1: GRANT ALL on xmlpserver to &&1

new 1: GRANT ALL on xmlpserver to KAN_IAU

Grant succeeded.

Enter value for 2: KAN_IAU_APPEND

old 1: GRANT INSERT on xmlpserver to &&2

new 1: GRANT INSERT on xmlpserver to KAN_IAU_APPEND

Grant succeeded.

Enter value for 3: KAN_IAU_VIEWER

old 1: GRANT SELECT on xmlpserver to &&3

new 1: GRANT SELECT on xmlpserver to KAN_IAU_VIEWER

Grant succeeded.

old 1: CREATE OR REPLACE SYNONYM &&3..xmlpserver FOR &&1..xmlpserver

new 1: CREATE OR REPLACE SYNONYM KAN_IAU_VIEWER.xmlpserver FOR KAN_IAU.xmlpserver

Synonym created.

old 1: CREATE OR REPLACE SYNONYM &&2..xmlpserver FOR &&1..xmlpserver

new 1: CREATE OR REPLACE SYNONYM KAN_IAU_APPEND.xmlpserver FOR KAN_IAU.xmlpserver

Synonym created.

After that you need to restart the WebLogic Server to take that in effect. Once that’s done, BI Publisher will start feeding all the auditing and monitoring related information into the auditing tables, and you should be able to start access to the data to create reports and visualize it with BI Publisher.

Create Reports to Visualize

If you have already finished the steps mentioned on the previous blog posts then you should already have a JNDI/JDBC connection created. If you haven’t yet, then take a look at the following post. 

  • Make sure you assign appropriate application roles to this data source connection so that you or any users who need to create reports for the BI Publisher Auditing and Monitoring data will have an access to the data source.

    image

    With this new set of data available, you can now start creating reports to gain more insights about how your users use the reporting platform and prepare for a better reporting experience by responding and adjusting promptly.

    Start with Sample Reports

    We’re planning to release a set of sample Auditing and Monitoring reports as part of our sample reports inside the catalog some time soon but till then you can start creating your own reports against the Auditing tables by using BI Publisher. For those looking for something quick, I’m attaching a sample report here

    You can download the above files and upload them to your BI Publisher 11g (11.1.1.5) environment from the BI Publisher Catalog UI, but make sure the following three points before you run.

    1. Change the Data Model reference inside the Auditing report to point to the Auditing Data Model that you uploaded

    audit_datamodel_reference

    2. Change the Data Source inside the Auditing Data Model to your JNDI connection

    audit_default_datasource

    3. Update the SQL query to point to appropriate schema name.

    audit_sql_query

    Path to Improve Reporting Experience

    Again, the Auditing is not just about collecting the auditing data and record it. It’s about how you as IT manager or administrator understand your users and reports on the reports environment in order to improve the reporting experience. The more you understand them the better service you can provide. We believe that the new release of BI Publisher 11.1.1.5 would provide you a wide range of data to help you understand your reporting environment better and quickly. Hope you’ll enjoy the power of new BI Publisher Auditing and Monitoring capability.

    Also, as always, we’re very interested in your feedback. Please let us know what do you think about the data currently available, the sample reports, any additional requirements or improvement suggestion.

  • Thursday May 12, 2011

    BI Publisher 11g - 11.1.1.5 is here!

    New Oracle BI Publisher 11g Release 11.1.1.5 is released! Here is a list of the new features.

    clip_image002

    Much Faster Interactive Reporting Experience

    Using BI Publisher to interact with your data is snappy. Generating interactive report output uses less memory and is faster than ever. BI Publisher’s highly optimized, three layer caching mechanism provides a highly responsive, interactive reporting experience.




    New Interactive Component - List

    The List component is a nifty way to filter data in an interactive report. When you click values in the list, BI Publisher automatically filters the data in the report. In the Layout Editor you can set the orientation of the List to be horizontal or vertical and over 30 other options to make it visually appealing for your consumer needs. Lists are another great way to interact with your report to gain more insight. See a quick demo

    clip_image004





    clip_image007

    Both Native and Web Apps for iPhone & iPad

    More and more business tasks are performed on mobile devices like iPhone and iPad, so why not browse reports and interact with your data on your mobile device with BI Publisher? Simply access your BI Publisher reports with Safari or other browsers on your iPad or iPhone, or install the Oracle BI Mobile App for iPhone and iPad to enjoy the same rich, fast interactive reporting experience as on your desktop or laptop. See sample pictures.



    Enterprise Reporting Platform for Oracle - E-Business Suite

    BI Publisher now supports Oracle E-Business Suite’s data level security based on user responsibility and allows you to switch responsibilities on the fly. You can use BI Publisher 11.1.1.5 to view, manage, and deliver reports against E-Business Suite data and join and aggregate with data from other data sources. Utilities are available to help you convert from Oracle Reports and upgrade to BI Publisher 11g. Start taking advantage of many of the great BI Publisher 11g new features with your E-Business Suite data today.

    clip_image008




    clip_image010

    New Data Source – Local Excel File

    Often times you need data in Excel to supplement other data or you need to visualize it quickly. BI Publisher 11.1.1.3 added Excel files as a data source so you could create a report against Excel data located on the server. BI Publisher 11.1.1.5 goes one step further. Now you can upload Excel spreadsheets from your local machine directly to a Data Model. Create reports from the uploaded Excel spreadsheet or join it and aggregate it with other data sources. Reporting with local Excel files is a great way to empower your users to visualize and share data easily and still maintain a centralized report environment. See a quick demo





    New Template Type - Excel Template

    Some users just love Excel, so we added Excel as a report layout design option. In addition to the existing template options like BI Publisher Templates, RTF, PDF, Flash, and others, you can now use Excel to design your report the way you want, using custom functions and Excel Macros to split data across multiple sheets, calculate values dynamically, then share with your colleagues.

    clip_image012











    clip_image014

    Auditing and Monitoring

    Auditing is not just about compliance, it’s a way to improve customer service by understanding what your users like to do, when and how they access and view reports. BI Publisher 11.1.1.3 included a framework to help IT Administrators collect data required for auditing. BI Publisher 11.1.1.5 enhances the audit and performance information captured and introduces a method to store the data into a database. Now you can use BI Publisher to visualize, analyze and report on your auditing and performance information. See a quick demo.



    New Output Format Type – Native Excel 2007 Output

    Now you can download your report as Excel 2007 format and open it with the latest Excel on both Windows and Mac with high fidelity formatting and layout. You can use repeating groups in your report to automatically group data in separate worksheets. With this format option the downloaded Excel file is 70% smaller compared to other Excel output types, which makes it easier to share your report in Excel with your colleagues.

    clip_image015



    clip_image017

    Reports Development Life Cycle Management

    Developing reports often involves multiple phases and processes before going live. Reports need to be moved to a testing environment after the development and then to the production environment. You can use the new BI Publisher Catalog Utility to move your reports either one at a time or in batch from one environment to another all without shutting down servers so users can continue to generate the mission critical documents and reports they need.

    Also, we have many more enhancement requests implemented in this release and fixed bugs from the previous release. Try download from here and start BI Publisher 11g today!

    Want to learn more about BI Publisher?

    clip_image019

    Follow us @bipublisher for our latest news, inside scoop, or tweet us your feedback!

    clip_image021

    Connect with other BI Publisher professionals!

    clip_image023

    Find How-to and solutions over 500 articles!

    clip_image025

    Subscribe our channel and check our introduction and How-to videos!

    clip_image027

    Ask any question you have and get answered at our Discussion Forum!

    About

    Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

    Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

    Search

    Archives
    « April 2014
    SunMonTueWedThuFriSat
      
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
       
           
    Today