giovedì 18 ottobre 2012

OBIEE: How to import data from Excel on Windows x64

I experienced some troubles importing data from Excel into OBIEE repository installed on a 64bit Windows machine.

This is not Oracle related, instead it's a Microsoft issue concerning ODBC data sources, this is beacause Win 2008 R2 doesen't comes with the capability of transfer Office data files to other sources (like databases).

Back on my old Windows 2003 adding data from Excel files was an "out-of-the-box" feature, just go to ODBC Data Sources and add your Excel file. 

If you install a 64bit Windows (in my case Windows 2008 Server R2) you will have two different ODBC Data Sources:

- the first is the default one and can be accessed under "Start"-> "Administrative Tools" -> "Data sources ODBC"
- the second is the corresponding 32bit version and is located at: "C:\Windows\System32\odbcad32.exe"

OBIEE uses as default the first one and, unlike Win2003, Excel datasource import is not an "out-of-the-box" feature.

After some searches I found out that it's enough install a Microsoft component that can be downloaded here:

Microsoft Access Database Engine 2010 Redistributable

After this is installed just go to ODBC Data Sources and add your Excel file: "System DNS" -> "Add"

Choose "Microsoft Excel Driver":


Choose a name for your data source, select Excel file from which data will be imported and then continue:


Next step is to open BI Administration Tool, open repository then go to "File"-> "Import Metadata" and choose the previously created Excel datasource.

Check all items then proceed:

You will see all sheets and all columns of your Excel file, select the ones you need  and then import them.

You have just imported data from an Excel file and now this data can be used and linked to other data using BI Administration Tool and subsequntly using OBIEE.

That's all!!

Nessun commento:

Posta un commento