OracleBIBlog Search

Wednesday, May 27, 2009

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.

STEP 1: CREATE BI PUBLISHER REPORT

  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)


STEP 2: CREATE TEMPLATE IN WORD AND VIEW RESULTS

  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!

0 comments: