Search This Blog

Monday 19 March 2012

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 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
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

8 comments:

  1. Very nice and helpful post.

    Can you tell me where is the excel file located -- in the local machine or the server where OBIEE is installed.

    Please Reply soon.

    ReplyDelete
    Replies
    1. Hi Amit,

      I have the Excel sheet where the OBIEE has been installed. Because this example has shown only for small file sheet example or demo purpose.

      Thanks & Regards,
      Bose

      Delete
  2. Hi Thank you for the post.. I am trying to create a ODBC but I cant see Microsoft products under Create New data source .. Could you please help.. Thankyou!!

    ReplyDelete
    Replies
    1. Hi Bose,

      Thank you for reply I used 32 bit odbcad32.exe and I am using 64bit OS. I have created the System data source using microsoft (*.xls) but I am not able to view it in RCU I can only view the DSN available in 64bit odbacad32.exe

      Thankyou!!

      Delete
  3. That was a great post !!! Can u tell me , how to do the same in linux Server . I mean my OBIEE has to read the file from the Linux

    ReplyDelete
  4. Hi,

    Thank you for this post. I was able to create the system DSN for excel but then when I tried importing the data in admin tool using this DSN, I don't see the DSN and driver created by me. Any idea?

    ReplyDelete
  5. Appreciate your time and effort, it was useful

    ReplyDelete

OBIEE 12c to OAS Migration

 Hi All, I have migrated successfully few projects from BI 12c to OAS 6.4. If  anyone wants help then please reach Me! Cheers! Bose

Recent Posts