[OBIEE ] Excel As Data Source In OBIEE

Excel as Data Source in OBIEE

We know that OBIEE is supporting for a wide variety of database to use. Sometimes we are having an requirement How to use Excel as datasource. Here is how to import tables into the physical layer of Administration Tool which we are having in excel file.

First we have to create sample Excel data with ".xls" format. Below is the sample screenshot of three tables which we have created in one excel in three separate tab.

Go to Control Panel -> Administrative Tool -> Data Sources (ODBC) -> Select 'System DSN' Tab. From there click 'Add' Button. We will get 'Create New Data Source' window. From the window Select 'Microsoft Excel Driver(*.xls)'

From the above screen Click 'Finish' Button. We will get 'ODBC Microsoft Excel Setup' screen

Click on 'Select Workbook' button.  Now we are in 'Select Workbook' Screen. Here I have selected the excel file which We have created just before.

we have selected my source excel file which we have created and saved in D drive. The above screen we are showing 'Read Only' option which will not allow the user to change the data when the Oracle BI Server is running with excel source file.

If we want to change the data in this excel while 'Oracle BI Servier' is running, we have to uncheck this 'Read Only' option. 

So once we have selected the source excel file and setup with the option click 'OK' button with above screen. Give the Data source Name with the below screen and give description (optional) if you want and Click 'OK'

Now we can view the DSN 'BISAMPLE' in  System DSN tab in ODBC Data Source Administrator.

Now Open the Administration Tool and go to File -> Import -> Import from Database. Select 'Data Source; Screen will appear. from there leave Connection Type to 'ODBC 3.5' and select  BISAMPLE. No UserName and Password is required. Just Click   'OK'

Once we clicked 'OK' with the above screen we are coming to import screen. From there select 'System tables' Option. It will show all the tables what we have created in the separate tab of an excel.

Click 'Import' with the above screen. Now we can view all the tables in the physical layer of the rpd. Update the row count to check the data.

All other steps are same which we are following for all other Data sources. Now we can make physical diagram all other BMM Layer and Presentation Layer work and can create some report with answers

Points to Remember:

1. For Demo purpose If you need to create some application, we can go for  

   this type of data source.

2. For huge number of tables, data sets this methods is not good enough

3. If the data volume is increasing in Day by Day, or Month Wise then do not 

    follow this method.

4. If you are having static data which will not change for a year or a long

    period then go for this type method