OracleBIBlog Search

Monday, March 16, 2009

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...)


OCA-Z1007 said...


I tried doing this.... but got an error after logging in from MS Word. I am able to Login successfully and am able to select the "Answer Request Demo" but when i click on "Open Layout Template" MS Word Throws an error saying "Error Occurred. Please check the settings and try again."

I am Struck at this can you please help me out...

garivaldo said...

Did anyone find a solution to this

Joseph Naujokas said...

Unfortunately with generic errors like that it is hard to diagnose the problem. My best 5-minute advice is:
* Make sure you are executing every step correctly. Seemingly inconsequential mistakes could make a difference.
* Try uninstalling & re-installing the Office plugin.
* Rebooting sometimes works wonders on mystery errors.

Chiedu said...

I get the same error message
"Error Occurred. Please check the settings and try again."
when I try to open & save BI Answer report as BI PUblishers report.

can anybody please solve this