Oracle Business Intelligence Enterprise Edition (OBIEE) supports many types of file sources as the data source. Some of the compatible file sources with OBIEE are:
1. Microsoft Excel workbooks.
2. Microsoft Access databases.
3. Delimited text files (.csv and .txt).
4. XML data sources.
We will be discussing Excel data source in more detail in this post.
For this example, I will be using a Microsoft Windows installation of OBIEE 11g. The step by step guide to set up this source are as following:
1. Check if you have Microsoft Excel drivers on your Windows installation. In my case, the Windows Server 2008 R2 edition did not have those drivers pre-loaded. To access those drivers, I installed the “Microsoft Access Database Engine 2010” on the Oracle BI server. The same can be downloaded from the following link:
http://www.microsoft.com/en-us/download/details.aspx?id=13255
2. After installing the above package, you should see the Microsoft Excel data sources in the ODBC connection:
3. Before accessing the excel data in OBIEE, we need to define the ‘Named Ranges’ in the excel. These named ranges behave as physical tables in the RPD. To define the named ranges, open the excel file and select the data range that you want to import as a table. Then open the Formula tab and select ‘Define Name’. Put a name for the range (Sales) and then click OK. Do the same for any other table that you want to import.
4. Place this excel file at a location which is accessible to the Oracle BI server. I am placing this file on the same directory on the BI server on which OBIEE is installed.
5. Create a new ODBC source using Microsoft Excel driver as shown below:
6. Save this new source and open the RPD. Create a new Database in the Physical layer and add a new connection pool. Set Connection type as ODBC 3.5. In the Data Source Name, select the newly created data source. For metadata types, select tables only. It will then show you the tables that we defined earlier:
7. Add these tables as sources to the RPD and click Finish. To check if the ODBC connectivity is working correctly, right click on one of the imported tables and click View Data, it should show the data stored in the excel:
8. Now create the foreign key on the Sales table to join to the Product table and move these tables to the BMM and Presentation layer. Deploy these changes to the BI server and create reports just like we do for all other data sources. Example report is shown below:
This is a very quick and easy setup and usually very useful in preparing client demos and ad hoc data integration with the Data Warehouse data sources.
hi for step 6, after the excel data source has been selected, it asks for username and password. Not sure what I should be entering for this. I have tried my obiee username and password but it says connection failed. Please advise
If you are asking about the username/password in the connection pool properties, then just leave it blank. Also, please make sure that the BI server has access to the excel file.
Hi Sajid,
Even when I leave the username and password blank and just press next it comes up with connection failed error. I launch BI Administrator, create a new repository, then try to select the datasource. When selecting the excel datasource I can browse to the directory that the other repositories are on and select it so I am assuming the BI server has access to the excel file – is there any other setting I need to look at to ensure the BI server has access to the excel file?
Thank you for your help
I have the same setup. But how do you give access to the user to update this excel file. For example, I have weekly scheduled BIP Report which uses Excel Datasource. I have to manually update the data in a column every week and upload it in the datamodel and run the report. Is that possible we just update the excel file and the report generated should include the new updated information without uploading it in the datasource automatically?
Thanks
NP