Sunday, June 15, 2008

OBIEE Excel Import, prepping the data

One of our customers has an application which generates an Excell with combined data.

They wanted this to be imported into OBIEE, but couldn’t get OBIEE to see the data. They got the connection oke, but didn’t "see" the tables. This problem was caused by the fact that the generating application didn’t prep the data. It didn’t set the named ranges for the three tables.

Here is how you do it by hand:
Select a range:

Enter a range name and press enter!

Do the same for the two other tables:

Here is how you do it by VBA code:
Columns("A:B").Select ActiveWorkbook.Names.Add Name:="MY_TABLE_RANGE",
RefersToR1C1:= _ "=Sheet1!C1:C2"

When you now try to import the data into OBIEE you will see the three tables:

Till Next Time
Note: don't forget to disable the count distinct feature:


Anonymous said...

Do you have to place the excel file in a certain location on the BI Server for this to work?

John Minkjan said...

you have to place the file in the location where your ODBC connection points to.

Anonymous said...

I have Excel 2003 file. I can import file into rpd and see data; however, I get an error ODBC Excel Driver] '(unknown)' is not a valid path
in Answer when I try to build a query against the same table.

Mindaugas said...

If you dont see any tables from excel in OBIE administration tool when importing, try to move excel file to the root of disc (c:\) out of user space like desktop, my documents. If you have excel in user's space under C:\Documents And Settings, then excel file in OBIE administrator will be empty.

Ricky Barry said...

Make sure the name of the spreadsheet is short enough as I had the same problem with a long named file with spaces in it.

Soumya said...

Hi John,

Could you please let me know how you could setup a DSN for Excell source to iimport data in to OBIEE on AIX?

Soumya said...

I can get it wokring on Windows
however i am not sure how i could create a DSN equivalent in AIX