OBIEE: How to import data from Excel on Windows x64

I experienced some troubles importing data from Excel into OBIEE repository installed on a 64bit Windows machine.

This is not Oracle related, instead it's a Microsoft issue concerning ODBC data sources, this is beacause Win 2008 R2 doesen't comes with the capability of transfer Office data files to other sources (like databases).


Back on my old Windows 2003 adding data from Excel files was an "out-of-the-box" feature, just go to ODBC Data Sources and add your Excel file. 

If you install a 64bit Windows (in my case Windows 2008 Server R2) you will have two different ODBC Data Sources:

- the first is the default one and can be accessed under "Start"-> "Administrative Tools" -> "Data sources ODBC"
- the second is the corresponding 32bit version and is located at: "C:\Windows\System32\odbcad32.exe"

OBIEE uses as default the first one and, unlike Win2003, Excel datasource import is not an "out-of-the-box" feature.

After some searches I found out that it's enough install a Microsoft component that can be downloaded here:

Microsoft Access Database Engine 2010 Redistributable

After this is installed just go to ODBC Data Sources and add your Excel file: "System DNS" -> "Add"

Choose "Microsoft Excel Driver":

 

Choose a name for your data source, select Excel file from which data will be imported and then continue:

 

Next step is to open BI Administration Tool, open repository then go to "File"-> "Import Metadata" and choose the previously created Excel datasource.

 

 

Check all items then proceed:

You will see all sheets and all columns of your Excel file, select the ones you need  and then import them.

You have just imported data from an Excel file and now this data can be used and linked to other data using BI Administration Tool and subsequntly using OBIEE.

That's all!!

OBIEE: Data is not being updated on dashboards

Hello!!
It's a bit since last update and today I would like to share something discovered about OBIEE.

I stepped into this problem some days ago while creating OBIEE reports. These reports are based on data from an Oracle database that is being updated by another software.

In detail the problem was: despite the data in database is being updated, these changes are not reflected on OBIEE dashboards, which still present old data.

After a brief search I discovered that OBIEE uses an internal server cache. When dashboard is created OBIEE performs the query against datasource to retrieve and store data. Every subsequent access to these dashboards returns cached data preventing dashboard to redo the query on db at every connection.

To bypass OBIEE server cache and perform the query on db every time you just need to insert a parameter on "Advanced" tab while creating a dashboard.

Click "Advanced" tab, then CHECH "Bypass Oracle BI Presentation Services Cache"
Under "Prefix" insert:

SET VARIABLE DISABLE_CACHE_HIT=1;
Then click "Apply SQL" button.







That's all!!