“We use Oracle Business Suite as our primary ERP. We have acquired BICG product through Oracle, with plug in adapters, and thought we could do it on our own and after 3 years were unsuccessful. We contacted BICG … within a week we had working instance of the BI Aps. We decided we had to work with BICG. It has been 3years now, we have had BICG do training, had consultants onsite. It has been a great relationship. We are a reference often for Oracle, and when we are asked I tell them that they need to do business with a company like BICG. They have done wonderful work for us. They are the first ones that I would recommend to do the work.”

Ware Hartwell - Los Alamos National Laboratory

“We recently implemented Financial Analytics Module, we are now working on integrated some retail water billing data form a CIS system. The system has been well adopted by the users. It has become a fundamental enterprise system to the company.”

Keith Niesner - LCRA
Functional Analyst for Data Warehouse and OBIEE platform

Search the Blog

OracleBI Blog

Entries in BI Publisher 10g (9)


Oracle BI Publisher 10g - New patch released

A quick note to just let know our followers that there is a new BI Publisher 10g patch released just today through the Oracle Support site: http://metalink.oracle.com


We will look into it and see if there are useful things which were corrected.

Stay tuned BI Publisher is coming soon!


BI Publisher - Webservices update to10g

BI Publisher 10g provides an update to its webservices from previous releases with the patch 9546699. All you have to do is (with OC4J and BI Publisher 10g running) to open the following URL:


Where: mybipserver is the machine’s name where OC4J is installed and where BI Publisher has been deployed. Save this file locally.


Once you get the WSDL file you can immediately test the services using SOAP UI (a webservices tester).

Start loading BI Publisher’s WSDL into SoapUI and create a new project:

So for example, we can find out which Security mode is being used in a BIP installation. Simply invoke the service “getSecurityModel”. This service does not require any parameter, therefore just click on the green button “play” .

This returns the security model employed in BI Publisher’s environment: “BI_SERVER”, which means the security model used is based and controlled by BIEE Server.

The next example will employ BI Publisher’s web services which require parameter values: “login” and “getFolderContentsInSession”. Invoke “login” and enter “Administrator” as user and password:

This returns the session string, known as BIP’s session token:


Next, invoke the “getFolderContentsInSession” service and enter the string just obtained in previous step plus the folder path to find out its content: “/HR Manager”.

Finally, click on Run (the green arrow button on the upper left hand side of the window) to obtain a list for reports and folders:

With this you now get a flavor of all the things you can do using BI Publisher’s webservices. Of course you can start developing your web application using JDeveloper and BIP WSDL, but that will be the topic for new post soon.

Note: Based on BI Publisher’s PM comments I have updated our post’s original title.




BI Publisher (build 1145)- latest patch - 9546699 (April 1st 2010)


More than a week ago Oracle BI Publisher PM team released a new patch 9546699 (on March 31st, but published in Metalink April 1st), which brings many bug fixes and product enhancements.


There is a long list of updates you can read in the readme file that comes in the patch. But for those of you interested in getting this patch applied in your environment I have compiled a step by step screenshot sequence (for Windows environment) which I hope can help you in this process. Here is how it goes:

I) Upgrade of BI Publisher Server


1. First, take a backup copy of $ORACLE_HOME/oc4j_bi/j2ee/home/applications/xmlpserver/xmlpserver/WEB-INF/xmlp-server-config.xml , where as usual $ORACLE_HOME refers to the location of your base directory for BI Publisher server installation.

2. Second, take a back up of BI Publisher’s repository. Default location usually is /xmlp/XMLP:

3. In case BI Publisher’s Scheduling feature has been enabled and configured ==> Also take a back up of such schema in the database used. Although as the upgrade will be performed on the application server side then there is no really need to perform this step, it is just as precaution.

4. You can now use Enterprise Manager for the rest of the steps : uninstall of the current version and the installation of the new version. Notice the port number is 9704 since the original installation of BI Publisher was part of BIEE installation and that is the default port number. Thus, invoke Enterprise Manager as follows : http://server_name:9704/em




Normally, the password is “oc4jadmin” but it could be something different. This password is defined during the first install of BI Publisher, so you need to get this value from whoever performed the first installation. Otherwise, if the password is not known then use google to obtain a process on how to reset the OC4J administrator’s password. Here is a link from Mike Lehmann’s : Reset OC4J admin password

4.1 Shut down BI Publisher.
4.2 Undeploy the BI Publisher application (“xmlpserver”) from your J2EE application server. Here a screenshot from Enterprise Manager for OC4J:
4.3 Deploy the (patch 9546699 ) xmlpserver.ear or xmlpserver.war to your application server. If you are using OC4J then here is the sequence of screenshots:


- Click on “Deploy”
- Select the EAR or WAR file to deploy:
-Enter the deploy application attributes:

In the third step simply accept defaults and Apply and Deploy. You get finally this:

4.4 Stop BI Publisher application (repeat as in step 4.1). This step is required as you will restore the backup files taken in step 1. and 2.

5. Copy the saved backup copy of the xmlp-server-config.xml file from step 1 to the newly created BI Publisher: $ORACLE_HOME/oc4j_bi/j2ee/home/applications/xmlpserver/xmlpserver/WEB-INF/ directory


6. Copy the repository (the complete XMLP directory and subdirectories from the back up folder, see step 2.) into $ORACLE_HOME/xmlp/

7. If your environment is (03-April-2009, Patch 8398277) or earlier then perform the following two steps, Otherwise continue with step 8.

  • Copy this file: ssodefaults.xml and place it in the following directory: $ORACLE_HOME/xmlp/XMLP/Admin/Security. Default values for properties can be updated depending on your configuration if SSO is implemented.
  • Copy database-config.xml to the following directory: \XMLP\Admin\Scheduler. This file stores information about the database which will be used to store BI Publisher Scheduler’s metadata tables.
8. Restart xmlpserver application or Application Server



9. Finally you can test your new patch by going to: http://server_name:9704/xmlpserver

Notice, down on the right hand side the BI Publisher’s version (build 1145). To see this version your application must be in DEBUG mode though. Alternatively, to see the build number from the login page you can view the page source (IE View —> Source) and Notepad will come up. You will see this line:



<meta name=“Generator” content=“Oracle BI Publisher (build# 1145 on Fri Apr 09 16:26:02 GMT-05:00 2010)”/>

This indicates that the patch has been applied and now BI Publisher’s version is build 1145.


II) Upgrade of BI Publisher Desktop

Finally, there is one more upgrade to make. The BI Publisher’s desktop should also be upgraded with the new xdocore.zip which, is shipped with the new patch. To apply it follow the next steps:

1. Using the just deployed new patch, get a copy of this file:


2. Take a backup of the the following existing xdocore.jar files:

a)C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\TemplateViewer\lib\xdocore.jar
b)C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\jlib\xdocore.jar

3. Paste the xdocore.jar file copied in step 1., into the following directories:

a)C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\TemplateViewer\lib\
b)C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\jlib\

After this, BI Publisher Desktop has been upgraded and is in sync with the patch 9546699 just applied.



BI Publisher - Layout Builder preview in 10g

If you are a BI Publisher user of release (Patch 9229122) then you would like to know that this release includes a preview of the Layout Builder (WSYWYG Editor) part of the official release 11g.
Only thing you would have to do is set one particular parameter in xmlp-server-config.xml located in the repository directory xmlp\XMLP\Admin\Configuration as follows:
After restarting OC4J this change will take effect. When you create or modify a report then you will notice that under the “Layout” section there is a new button “Layout Builder” available.

The template prepared by the Layout Builder will have extension .xpt and will be an XML file which contents will only be rendered properly in the Layout Builder tool. In addition to this, the tool will not read any other file type than .xpt.

I guess that if you want to edit the .xpt file outside the Layout Builder you could use any XML text editor and assign a schema or DTD to it, but that is the topic for another post in this blog :).

At this point I would like to make a disclaimer: the parameter is an undocumented feature in release and thus is not supported by Oracle in this release.



BIEE and BI Publisher's integration (Best Practice tip)

BI Publisher can gather data from different data sources, but with regards of the integration with BIEE two in particular are of our interest:

  1. SQL Type
  2. OBIEE Answers, termed Oracle BI Answers

The first type, allows us to connect to any database using a JDBC connection. The second type, allows us to connect to BIEE server by means of BI Answers queries.


What is the difference? Let’s see. If we take the route with OBIEE Answers then in BI Publisher we get immediate results without any additional work:

For the above data set we get the following XML output:

Problem with Long Tag Names in RTF Templates

But one of the drawbacks of this approach is that we get long tag names, like for example: <_sales_facts_._amount_sold_>. These long tag names are difficult to handle in BI Publisher for RTF template development. For example, if the formula used to create the OBIEE Answer is a long CASE statement, the tag contains this long statement. This drawback is a problem because of Microsoft Word’s properties limitation. Any situation of form field is limited to 276 chars. That is 138 chars max per tab: Status Bar and Help Key (F1) ) as confirmed below:

Workaround for the Problem with Long Tag Names in RTF Templates
But using the following workaround, we still can access BI Server and leverage all its content. Simply go to same BI Answers query ==> Advanced tab and take the “logical sql”:

Next, paste this logical sql into the data model in BI Publisher and voila, we get the same results but with the labels associated: saw_0, saw_1, … saw_5.

At this point some work is required to mapping the saw_X columns with the corresponding ones in the BI Answers query to make the column names meaningful in our report. Renaming the columns does not present any limitations to our goal of pixel perfect reporting.

Now, if we execute the report just defined then we can see that the XML elements of the output file are shorter and can be accommodated to our taste.


BI Publisher's Timestamp and Date

Let us take the following case: When we run a report, how do we get the correct local’s (server’s) timestamp printed?

In the process of getting the final output, every rtf template goes into different temporary files:


RTF —> XSL-FO —> FO —> HTML, PDF, XLS, RTF, etc.

When the template RTF file is converted into XSL-FO BI Publisher’s parsers include several default parameters at run time and one of them is :



<xsl:param name=”_XDOTIMEZONE”>GMT</xsl:param>



By default, this parameter will determine the timestamp based on Greenwich Median Time.


One way we can adjust this value in our RTF template is in the following way:


<?format-date:xdoxslt:sysdate_as_xsdformat();’Dy DD-Mon-YYYY hh24:mi:ss’;’PST?>

sysdate_as_xsdformat() : This function will generate the server’s timestamp
format-date:xdoxslt: This function will format the time & date value using the mask and the Timezone provided.

The output obtained is similar to: 



Thu 19-Nov-2009 21:47:50

When the server is located in New York (EST) then its timestamp is like: Fri 20 Nov 2009, 00:47:50.



As indicated above if the timezone is omitted (i.e PST, EST, etc) then GMT will be used and the output would have been:



Fri 20-Nov-2009 05:47:50

You can find a good example on formatting date values in this link:



And the corresponding documentation on date formatting here:



BI Publisher and OBIEE: Integration with Dashboards & Prompts

In this post I will demonstrate another way to “integrate” OBIEE with BI Publisher (“BIP”): adding a pre-built BIP report to a Dashboard and configuring it to interact with a Dashboard Prompt.

For this example I’ve already logged into BIP as “Administrator” and created a report named “BI Publisher Subject Area” including some simple dimensions and facts from the “Paint Exec” demo Subject Area. I’ve uploaded a simple table template aptly named “SubjectAreaTable” using Word to this report. The resulting report viewed in HTML looks like this:


Step 1: Add a BIP Report to Dashboard

From a blank Dashboard page, drag & drop “BI Publisher Report” object into the blank Column. Click “Properties” then “Browse” to the location of the existing BIP report “BI Publisher Request.” (Note what happens when you “hover” over the report name - that’s the value of the report’s Description field.)

Click on the report name and then OK. Click OK again in the Properties window (accept the default parameters for now). Save your changes then view the new Dashboard. Note that the standard BIP controls are available - you can select a Template & format and even choose to deliver the output using the standard BIP options (which are a bit different from OBIEE’s options).

Step 2: Configure the BIP Report to respond to a Parameter for “Region”

Now switch to BI Publisher (More Products > BI Publisher). Navigate to “My Folders,” then click the “Edit” link under the “BI Publisher Request” report.


Click the “Parameters” option in the left-hand nav, then click “New” to create a new parameters.

Fill in the values as follows:

Identifier: “REGION_NAME”

This value is fairly arbitrary, but as you will see below we will be referencing it in Answers so it doesn’t hurt to consider some sort of naming convention. I am also deliberately making the identifier different than the name of the Presentation Column in Answers to clarify the distinction between the two.

Data type: “String”

I’ve had spotty results using anything but “String” — Date in particular gave me trouble.

Parameter type: “Text”

There are some interesting uses for other types, but we’ll save that discussion for later.

Display Label: “Region Name”

This value can also be arbitrary

Text Field Size: [leave blank]
Options: [leave blank]

Both of these are also out of scope for this discussion.

Now we click into the Data Set (within the Data Model “folder”) and add the line “WHERE Markets.Region = :REGION_NAME” to the SQL Query input box. Note we are using the “Identifier” of the Parameter we just created.


Save your changes (click the disk icon in the upper LH corner) and view the results - Note the results by default will be blank because we haven’t entered a value. Enter “CENTRAL REGION” in the parameter box and click “View.”

Step 3: Add a Prompt to your dashboard

Go back to Answers and create a new Dashboard Prompt based on the “Region” Presentation Column. Here’s the important part: Configure the prompt to set a Presentation Variable named - you guessed it - “REGION_NAME” and save your new prompt.

OK last step: Still in Answers, go back to modify your Dashboard and add the new prompt. Might as well put it on top of the existing BI Publisher report.

Save your changes and view the Dashboard. Again, because the default value for Region is blank, you won’t get any results unless you pick one from your new prompt.

But, uh oh, how can I select ALL regions? The “All Choices” selection doesn’t seem to work the same way with BI Publisher as it does with native OBIEE reports…

Hmm. How about I give a banana to whomever can chime in with the solution to THAT problem?

Remember that reports in BI Publisher can be created against other data sources besides OBIEE… but the Dashboard integration is essentially the same. I’ll save that topic for another post.



BI Publisher and OBIEE, Part 2: Integration with Subject Areas

In Part 1 we explored the basic concepts of the integration between MS Word, BI Publisher and OBIEE. We saw that BI Publisher can be used as a sort of “interface” between MS Office (Word or Excel) and OBIEE, whereby one can leverage the best-of-breed functionality of both tools: the layout functionality of MS Office and the data consolidation & warehousing functionality of OBIEE.

In particular we demonstrated how Word could be used to design a custom layout (in RTF format) for an existing Answers Request.

In this example we will use Word to design another simple RTF layout, but this time we will apply it directly against the OBIEE Subject Area itself. This approach eliminates the need to rely on an Answers Request — instead the data set will be defined within BI Publisher using the Subject Area as a type of SQL data source.



  1. Create report and data model

    Login to BI Publisher


    click My Folders > Create new report >
    Enter Report Name = “BI Publisher Subject Area” > Create >

    click My Folders > BI Publisher Subject Area > Edit >

    click Report > Data Model > New >

    Name = “Sample Sales: Basic facts”
    Type = SQL Query
    Data Source: Oracle BI EE

    click Query Builder >


  2. Use Query Builder to define data model


    Catalog: Sample Sales …
    Note that BI Publisher has truncated the subject area name “Simple Sales Reduced”

    In left-hand nav window:
    click Facts Revenue >
    click Products >
    click Time >

    In Model window:
    select Facts Revenue > Revenue >
    select Products > Brand >
    select Time > Year >

    click Save > (for Query Builder)

    click Save > (for the Report itself)




  1. Open MS Word and create simple layout using data fields from OBIEE


    click Add-ins > Oracle BI Pulisher > Log on [same user/pw as login to BI Publisher]

    click Add-ins > Oracle BI Pulisher > Open

    click My folders > Reports > BI Publisher Subject Area > Layout Templates > Open Layout Template

    click Add-ins > Oracle BI Pulisher > Insert > All fields

    Note the insertion of the data field tags “Revenue”, “Brand”, “Year”, but also note the “F” and “E” tags before and after the data tags - these are “For/Each” tags that allow iteration through the entire dataset which we will see demonstrated in a bit

  2. Save template as RTF and upload to OBIEE


    click Add-ins > Oracle BI Pulisher > Upload Template As…

    [accept RTF prompt and save as RTF template]

    Template Name = “All Fields”

  3. View results


    [Return to BI Publisher]

    My Folders > BI Publisher Subject Area > View

    Note the Template used for this view is the same we just uploaded: “All Fields”

That’s it!


With these simple exercises we covered the essential features of the BI Publisher / OBIEE integration and actually touched on quite a few features of BI Publisher and the MS Word add-in.

To explore further, try experimenting with other features - For example:

  • Use BI Publisher’s Query Builder to interface with more complicated Subject Areas and build more complicated queries
  • Play with the Word Add-in’s “Insert” feature combined with Word’s full native formatting functionality to build charts, prospectus-quality layouts, and form letters based on OBIEE data
  • “View” and “Export” the BI Publisher reports in different formats (PDF, Excel, PowerPoint etc)
  • Use the BI Publisher “Send” feature to FTP a PDF version to a document repository site (or to email the doc every morning at 9:00 am - this feature will require advanced configuration of the Scheduler component)
  • Explore the functionality of the Excel add-in

Have fun!




BI Publisher and OBIEE, Part 1

BI Publisher originated as a Java application called “XML Publisher” (hence the “xmlp” naming convention) embedded within Oracle’s E-Business Suite. XML Publisher has been an integral component within E-Business Suite as a means to deliver highly refined PDF-formatted output of raw transactional data, primarily for hard-copy printing.

In its incarnation as Business Intelligence Publisher (hereinafter “BIP”), it is essentially the same application at heart but with several powerful new features. Like XML Publisher, BIP is a XML transformation engine that applies XSLT templates (created on the fly from user-generated WYSIWYG templates in RTF format) against raw data to produce reports with highly customized visual layouts in a variety of output formats.

XMLP differs from BIP in two significant ways:

First, BIP has much greater flexibility using a variety of template sources, output formats, delivery methods and scheduling options.

Second, and more importantly, BIP is not restricted to E-Business Suite as a data source - in fact it has been expanded to interface with a wide variety of applications, including JDBC-complient databases, Webservices, Hyperion… and OBIEE.

From a high-level pespective, BI Publisher…

  • transforms raw data from various sources (JDBC, Webservices, OBIEE, etc)
  • according to a layout designed using native functionality of best-of-breed layout tools (Word, Excel, Acrobat, etc)
  • into various file formats (HTML, PDF, Flash, CSV, XML, XLS, etc)
  • and delivers them via various mechanisms (Email, FTP, HTTP, WebDAV, Fax, etc)
  • either on demand or according to a user-defined schedule

Any organization making the investment into OBIEE should understand and take advantage of the extensive data formatting & delivery functionality provided by BI Publisher.


This discussion is divided into two parts illustrating BIP’s basic functionality within the context of its two native OBIEE interfaces:

  1. with BI Presentation Services as a specialized integration source, wherein Answers Requests are treated as a unique data type (Part 1)
  2. with BI Server as a JDBC-compliant SQL database, wherein Presentation Tables are treated as simple database tables (Part 2)

Assumptions for this discussion:

  • BIP was correctly installed and configured onto a demo system during a full OBIEE installation process (full installation & configuration of BIP can be tricky, but is out of scope for this discussion - see Oracle’s documentation for more info)
  • OBIEE on this demo system is serving the demo “Sample Sales” repository
  • MS Office is installed on the client system (screenshots herein represent MS Office 2007 but should adequately demonstrate functionality for previous versions)

PRE-REQ: Install “BI Publisher Desktop” (interfaces desktop client apps Excel and Word with BI Publisher)

  1. Log in to OBIEE as Administrator
  2. Click to “More Products - BI Publisher”
  3. Click to “Business Intelligence”
  4. Click on “Template Builder” (in Developer Tools) - Save BIPublisherDesktop.exe then run


PART 1: Integration with OBIEE Presentation Services (via Answers Requests)

BIP can interface with the OBIEE Presentation Services as a data source. BIP “sees” existing Answers requests and treats them as reports ready for formatting & delivery. In this exercise we will create a simple Answers request, then connect to it from BIP and build a simple template using MS Word, then observe how we can use native functionality in MS Word to modify the resulting output.


  1. Click to Answers
  2. Open the “Sample Sales Reduced” Subject Area
  3. Create & save new Request titled “BI Publisher Request” in your personal folder with the following dimensions & facts:
    Products . Brand
    Time . Year
    Facts Revenue . Revenue



  1. Open BI Publisher (“More Products” - “BI Publisher”)
  2. Click to “Shared Folders” - “Business Intelligence”
  3. Click to “Create a New Report”
  4. Enter Name = “Answers Request Demo”
  5. Click “Create”
  6. Click “Answers Request Demo” - “Edit”
  7. Click “Data Model” = “New”
  8. Enter Name = “BI Publisher Request”
  9. Select Data source = “Oracle BI Answers”
  10. Click Flashlight icon
    NOTE: Directory structure matches Web Catalog structure - because BIP here is communicating with Presentation Services
  11. Click “users” then [your username] then “BI Publisher Request”
  12. Click “Save” (green disk icon in upper LH corner of screen)



  1. Open MS Word
  2. Click to “Add-Ins”
  3. Click “Oracle BI Publisher”
  4. Click “Log on” - Pop up window will appear
    NOTE: Value for Report Server should be the local OBIEE install


  5. Click “Login”
  6. “Open Template” window will open
  7. In “Workspace” drop-down, select “BI Publisher”
  8. Expand “Shared Folders” then click “Business Intelligence”
  9. In the “Reports” area, click on the “Answers Request Demo” (which you created in BIP above)
    NOTE: Directory structure matches BIP structure - because Word here is communicating with BI Publisher
  10. In the “Layout Templates” area, click on “New”
  11. Click on “Open Layout Template”


  12. Click to “Add-Ins”
  13. Select “Insert” - “Table wizard”


  14. Select report format = “Table” and click “Next”
  15. Click “Next” (Data Set = “ROWSET/ROW”)
  16. Click >> (selects all available fields), then “Finish”
  17. Select “Preview” - “PDF”


  18. Obey prompt to save document in RTF format
  19. PDF viewer will render output


  20. Use “Add-ins” - “Insert” to create new objects, then experiment with native Word formatting functionality to create your desired report layout
  21. When ready to proceed, select “Add-ins” - “Oracle BI Publisher” - “Upload Template As…”


  22. Enter Template Name = “Answers Request Demo Template”


  1. Return to BI Publisher
  2. Click to “Shared Folders” - “Business Intelligence”
  3. Click “Answers Request Demo” - “Edit”
  4. Click “Report” - “Layouts” - “Answers Request Demo Template”
    NOTE: This is the RTF template which you created in MS Word above
  5. Click “View” (link in upper RH corner)


  6. Voila!

PART 2 Integration with OBIEE Server (via Presentation Tables)


(To be continued…)


Bookmark and Share