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 I am sharing 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
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 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'
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)'
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.
If we want to change the data in this excel while 'Oracle BI Servier' is running, we have to uncheck this 'Read Only' option.
Now we can view the DSN 'BISAMPLE' in System DSN tab in ODBC Data Source Administrator.
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
