Using Excel as a BI Client for OBIEE

For most organizations Excel is still a very important tool. One of the goals of most Business Intelligence projects is to get rid of Excel because of several reasons (one version of the truth, centralized data, etc.) I don’t dig into those reasons, because you can Google them yourselves. Fact is, most organizations still want to use Excel and Oracle BI (OBIEE) has a tool to make this possible; the Oracle BI Office Add in.

In this blogpost I will show you an easy way to install and configure this Add in and create Excel pivot tables on it.

See also:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/biee/r1013/bi_addin/bi_office.htm

Download the Oracle BI Office Add in

When using OBIEE 10g, click on More Products – Download Oracle BI for Microsoft Office

When using OBIEE 11g, download it from the Oracle Business Intelligence Home page

Install the Oracle BI Office Add in

Close Office applications (Excel and Powerpoint) and just run the executable and click Next – Next – Finish.

Configure the Oracle BI Office Add in
–          In your instanceconfig.xml file, notice or add the entry:
<BIforOfficeURL>client/OracleBIOffice.exe</BIforOfficeURL>
–          Start Excel and notice the new menu item called: Oracle BI

– Click Preferences – New and create a new instance

Import data from OBIEE request

Login, navigate to desired request/view and click Insert button

Create Excel Pivot table on OBIEE request

You can create an Excel Pivot table based on an OBIEE request that you import from the catalog. When the OBIEE request is refreshed, you should also refresh the pivot table.
Do’s and Don’ts
Do’s
–          Make sure you have a lot of memory available when you import large requests
Dont’s
–          The addin imports data from requests; avoid using filters in requests (use “Is Prompted” instead)

–          There is no Object Library or documentation available to call the Oracle BI Add in functions with VBA code. This would be handy if one wants to automate the refresh steps.