Oracle OBIEE: Use Excel files as datasource

This post explains how to use Excel files as datasources for Oracle Business Intelligence repository.
As you know OBIEE supports a wide range of datasources to be imported in repositories and then processed for analytics. These comprise (well, obviously) Oracle Database, Essbase, SQL, and last but not least Excel.

To use Excel files as datasource a little modification is required to allow BI Administration Tool to properly recognize datasource tables and columns.

Let's pretend this is our source file.

As you may notice there are 2 tables with 3 columns each.

ID_TBL1, FIELD1_TBL1, FIELD2_TBL1 for TABLE1
ID_TBL2, FIELD1_TBL2, FIELD2_TBL2 for TABLE2

To allow OBIEE to correctly recognize these tables and rows we need to define the sheet area dedicated to each one by dragging cell delimiter to the area of our interest, right clicking a cell within this area and select Define Name. Credits for this go to John Minkjan and his blog post.

We then assign a name to the table. In this example I called it TABLE1.

Repeat the same for TABLE2.

Then save the file and transfer it over to your BI server.

Now login to OBIEE server and install Microsoft Access Database Engine 2010 Redistributable. This component is needed in order to allow the creation of system DSN based on Excel sheets (actually a few Office files are supported, not just Excel).

This guide assumes you are running OBIEE instance on a Windows Server machine. Although this is also feasible under Linux I cannot show you how to import Excel datasource into the repository since AFAIK BI Administration Tool is available only in Windows BI version.

Once you are done copying Excel files over to OBIEE server let's now define our datasource by clicking Start -> Administrative Tools -> Data Sources (ODBC) -> System DSN tab

Click Add -> Microsoft Excel Driver -> Finish

Choose a name for your datasource (DATA_FROM_EXCEL in this example) then click Select Workbook, choose the previously created Excel file and click OK twice.

Now Excel file is identified by the system as a proper datasource, so let's use it in OBIEE repository.

Open BI Administration Tool, load your existing repository if needed or simply create a new one, then click File -> Import Metadata

Select datasource you created in the previous step (DATA_FROM_EXCEL in this example) and press Next.
Check only Tables type and press Next.

Your tables will be correctly recognized and ready to be imported. Select what you need then press Finish.

Tables, rows and data will be imported in your Physical Layer ready to be used for your analytics.

That's all!!