Adding a Excel spreadsheet as a datasource in OBIEE

OBIEE can combine Excel spreadsheets as sources of data into your BI solution.

Here's a quick run-through of the stepsyou should take to include the spreadsheet.



1. Open the excel file to the sheet in the workbook you want to use as a datasource.

2. Ensure you have no filters set.

3. Highlight the range of rows and columns you wish to use, including the column headings. The column headings will become the column names. Right click and give your range selection a name or you can go to Formulas-->Define Name and give the scope and range.

4. Each spreadsheet can have multiple named ranges. Each named range will become a "table".

5. Now you need to create a System DSN for the Excel data source. Open the Data Sources (ODBC) from the Control Panel or Administrative Tools menu.  Select the Microsoft Excel Driver from the list of drivers.

6. Give the DSN a name and browse to the spreadsheet with the named range and click on Ok.

7. Open the OBIEE Administraton Tool. For OBIEE 10g go to Administration tools and click on File>Import from Database. Select the DSN which you created for Excel. For OBIEE 11g select File>Import Metadata - the DSN should be in the list of DSN data sources available, select the DSN and continue to import as normal through the wizard.

8. When you click on Import, it will import all the tables present in that excel file.

9. Once you get the tables in physical layer, you can play around with those tables to create subject area.