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.
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:
– 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
– Make sure you have a lot of memory available when you import large requests
– 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.