Wednesday, July 9, 2008

OBIEE Changing XLS (Excel) data on the fly

  • One the big strengths of OBIEE is the possibility to easily integrate Excel sheets into the repository. If you leave everything to the default settings you will find that you can’t edit the sheet “on the fly” without having to restart the OBIEE services.
    Here is a workaround.

    Step 1: Make the XLS workbook “shareable”

In Excel goto Tools > “Share Workbook”


In the “Editing” tab check the “Allow changes……..” box.

When prompted save the workbook!

Step 2: Set the connection pool settings:



Uncheck:
Require fully qualified table names
Shared logon
Enable connection pooling
Use multithreaded connections
Execute queries asynchronously

Check:
Parameters supported

Set:
Parameters supported to Repeatable read

Step 3:

Deselect the cacheable checkbox

Now when you have made a change to your Excel sheet you only have to press the refresh button in your OBIEE report:


Till Next Time

1 comment:

Anonymous said...

Good Post. This works if your BI Server is on the Windows platform. How can I achieve this functionality if the BI server is on a Unix based OS?