Friday 18 July 2014

Excel & OBIEE 11g on Windows - ODBC drivers Problem

In OBIEE, you might face this issue while creating a DSN to access MS-Excel as a source database. Even if you have installed OBIEE setup, MS-Office setup successfully, etc.., you will not be able to create a DSN pointing to an Excel source on your system.

This is because, you have installed a 32 bit version of MS-Office on your 64 bit system.

A 64-bit version of the Microsoft Windows operating system includes the following versions of the Microsoft Open Database Connectivity (ODBC) Data Source Administrator tool (Odbcad32.exe):
  • The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
  • The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.
You might be wondering.. What's this stuff?? :-) Let me try to clear you these things. 

With the Odbcad32.exe file from %systemdrive%\Windows\System32, you are not able to create a DSN for any Excel file. There will be no any drivers for Excel data source.

Unlike this, with the Odbcad32.exe file from %systemdrive%\Windows\SysWoW64, you will be able to create a DSN for Excel pointing to your source excel file. 

But you will not be able to access this DSN through BI Admin Tool.

So, to overcome with this issue, we need to install 64-bit Microsoft Access components?
You can download this component from this link, (Download 64 bit)

Install this file,


But still, didn't went well..!! Don't worry.. :-)

Use the /passive parameter to force install of the 64-bit ODBC drivers:

Open your command prompt, & go to the downloaded folder, and type the following command.

Now open Odbcad32.exe file from %systemdrive%\Windows\System32 location, you are ready to create DSN for excel source file.

Create one sample DSN,

Make sure, this DSN is available in Admin Tool also to import data from source file mentioned in that DSN.

Sucess..!! :-)

Adding Logo's to the Title of the OBIEE reports.

What if you would like to set some standard logo for that report?

Yes, there is an option in OBIEE where we can set the logo for an individual report. It is possible to add images into title of the view. This can make title views look more graphically appealing and can also help the user to understand what the title view is conveying.

You can find all the images that comes with the OBIEE installation at the following path,

C:\Middleware\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\s_blafp\images
  • Create any sample report.
  • Go to the results tab & edit the Title view of the report..
  • Set the title for the report. (By default, this title is same as report name). 
  • Set other fields in you are interested in further formatting.
  • In the logo, you give the path for that logo/image. It can be,
    • Relative path - The logo will accessible only on the localhost.
    • URL - A complete URL to that image.
  • Relative path - 
Note: When running in a secured environment, only resources that are located on the Oracle BI Presentation Server may be used. These resources are referenced using a relative path prefixed with "fmap:".

  • URL - 

  • For further exploration, lets fill the Subtitle & Help URL fields. 
  • Set Started Time to "Display Date and Time". This will print the latest date and time when this report is executed against the database.

You can see the question mark (?) button in the title of the report, where you can give a link to any other BI content, website, etc.

Happy blogging.

Tuesday 15 July 2014

OBIEE 11g - Double Column support for dashboard level prompt.

Now it is possible to filter on the IDs when an end user chooses its description.

This is one feature that I am pretty sure everyone expects by default in a reporting tool i.e. the ability to pass IDs when a description is chosen in a prompt. But the way it has been implemented in BI EE actually makes it useful for a lot of applications than just description/id switch. This can be put to use for multi-language applications where the descriptions can be any language but the id remains the same.

This feature has 2 fold advantages,
  1. In OBIEE 10g, there was no automated way of filtering on ID’s when end users chose the description values in the prompts. The Double column feature provides this ability in 11g.
  2. In many implementations where data is captured in multiple languages, the descriptions might be stored in different languages. But the filtering of data will be on ID’s (which will be the same across languages). Double column feature provides that ability now.
Let's try this feature by taking one example,
  • The following is the screenshot for the table on which we will be applying this double column support.

  • To achieve this double column support, in BMM layer of repository, we need to assign the descriptor ID column for the column on which we are going to apply this feature. Here, Country_Name.

  • Save the repository and upload it on server.
  • Now go to BI Analysis page and create one dashboard level prompt. When we include this Country_Name column in the prompt, you will notice that the prompt will automatically show the Included ID column as well.

  • Notice that, we now have the ability to display the Descriptor ID as well.
    Lets enable that option as well so that users who are more familiar with the ID’s will have the ability to toggle between the Country_Name and the Country ISO Code.

  • Now create a sample report and apply a filter on the column having description. Here, Country Name.

  • Take that report and the prompt you have just created on a dashboard, end users will now have the ability to filter on the description as well as the ID.

  • If you enable the Select by ID check box, you will notice that the drop down will now have the ID and the description concatenated for easy selection.

Reference: http://www.rittmanmead.com/

Happy blogging. :-)