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.


  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!

Tuesday, May 26, 2009

How to Stay Fresh

After 30 years in the business someone asked me... "How do you stay fresh?".

I had to stop and think about it for a minute and then the answer came to me.

A day or two ago someone pointed me to the samples directory that is included with OBIEE's Paint demo - the basic software that you get from Oracle - to review the entry for a basic time dimension. I opened it up and reviewed it end-to-end. Why? Because I found it to be very interesting and I realized that it is the passion that I have for the details that keeps me going. Maybe you feel the same way? It's that "can't get enough of this" and "I love what I do" that keeps me current.

Working at software companies requires a certain "How are we going to do that?" attitude so it forces you to constantly reach out and learn the tools-of-the-trade that are required to build software - C, C++, Java (Assembler in the old days).... Well for the most part anyway these tools are used to build the software itself.

Applications are built using software so the same passion is required to 'stay fresh' whether you are building software itself or building applications. When building OBIEE Apps you must simply learn to use the tools that Oracle provides and combine that with your business skills.

So now Oracle introduces what I consider to be a third level. First is the software itself. Next are the applications you build using the software. And now third, we have pre-built OBIEE applications that require customization of the application - not the software. This takes the "How to stay fresh" question to a new higher level.

When you look at customizing pre-built applications you must dive into the meta-layer and determine what the application developers where thinking when they built it to spec. Where did the spec come from? How was it established? Does it make sense for my client? Does it need to be customized?

And then there is the GAP (GAAP for financials) Analysis for the loads.... Where does it come from? How does it get there? What adapters so I need? How do I use them? What is the Universal Adapter?

So you have a whole new level of 'customization' consulting that really did not exist up until the combination of the tools that Oracle combined - Informatica, DAC and OBIEE Answers and such.

Staying current or staying fresh in this environment requires a whole new level of passion and experience. You must get into the heads of the application designers and the software developers - both - to stay fresh in this arena.

So let's just say that Oracle has raised the bar on the basic passion and competencies required to "Stay Fresh" in this arena.

If someone walks in and tells you they have "Three Years experience with Oracle's Project Management Analytic App" or "You don't need to use the Informatica piece.." or "The DAC is worthless.." - please kindly ask them to step aside and keep asking questions until you find someone who has the passion to "Stay Fresh !"

Friday, May 22, 2009

New Release of Oracle Business Intelligence 7.9.6

With the new release of Oracle BI Applications, Oracle extends its BI solution offering to include Project Analytics and Loyalty Analytics

Oracle recently announced the introduction of Oracle Business Intelligence (BI) Applications Release 7.9.6. The new capabilities in Oracle BI Applications Release 7.9.6 surrounding Project Analytics and Loyalty Analytics will provide an opportunity to help organizations to manage and execute with greater business insights and agility.

Oracle BI Applications are complete, pre-built BI solutions that deliver intuitive, role-based intelligence for everyone in an organization – from front-line employees to senior management – that enable better decisions, actions, and business processes. Based on best practices, these solutions enable organizations to gain greater insight and value from a range of data sources and applications including the Oracle E-Business Suite, Oracle’s PeopleSoft Enterprise, Oracle’s Siebel CRM, Oracle’s JD Edwards EnterpriseOne, and third party systems such as SAP.