OBIEE Customization Series – How to impress managers


When it comes to BI tools comparison one of the points in discussion is the Data Visualization or also referred as Data Discovery Capabilities.

Why data visualization matters? The human brain is not able to extrapolate data and map it correctly on its context. We are not capable to look at a set of data and at first sight understand the trending and its criticality.

With the use of visual aid like graphs, charts, maps we provide to the audience the capability to understand the data, its trend, the critical parts, and to have a clear picture of the overall situation.

The dashboard designer has to think about how visual aids can help the user to consume the dashboard effectively. The goal here is that the user must be provided with a clear idea of a particular business situation at a glance. Giving clear understanding and keeping things simple will be one of our great assets for user adoption as users will see the benefits from the first time.

You are starting to understand the importance of visualizations on the BI projects and then it is time to know how to implement it, and probably time to add a talented designer into your BI team structure.

On this new visualization series we will cover from the very basic graph creation to the principals of dashboard design and how to present it on a professional fashion, in a nutshell:

Tableau helps people see and understand their data


Tableau’s very short and sweet mission statement captures the very essence of why its highly visual BI software has taken the market by storm.  With year-on-year sales growth still close to 100% - even a year after IPO and with 17,000 customers - it’s no wonder that Tableau is the product all the other vendors in a crowded BI market are running scared of and the analysts are ranking ever higher in their “Leaders” charts.

If you see and hear the infectious enthusiasm and passion of Tableau users, or experience the buzz of a Tableau Conference, you will start to realise that this product is pretty special.

The best way to appreciate why is to see Tableau in action – most business users who see Tableau, want it!

The last few years has seen the emergence and rapid growth of a new strand of BI technology known as “discovery-based analytics”.

Traditional BI generally enables the exploration of structured data through pre-built hierarchies and relationships: start with some high-level KPI’s, identify the areas of greatest interest (e.g. the ones performing below target), then drill and pivot down and across the various layers and groupings to home in on the underlying causes.  In the hands of an experienced analyst, traditional BI enables very powerful insight and can identify actions to add significantly to the business bottom line.

Unfortunately, in many enterprise BI implementations, this tends to get dumbed-down and passed out to consumers as pre-built dashboards and reports that are glanced at but not explored, absorbed or understood.  Maybe the graphs aren’t clear enough, the tables are too boring, the tools are not intuitive enough, or the canned reports just don’t answer the questions that you need to ask today.

The discovery-based model starts more from the premise that you don’t know what you don’t know, and the tools make it easy for you to have a conversation with your data without the preliminary round of wrestling the data into submission in Excel.   And you can ask it the questions that you as a business user want to ask today, rather than the questions that the BI Development Team thought you might want to ask 12 months ago.

In a large organisation there is usually room and validity for both traditional and discovery approaches.  In a smaller organisation, or for a specific business need, the discovery approach – typically requiring less up-front effort and cost to start getting value – can be very compelling.

Tableau is in our view the best of the discovery-based BI tools, enabling beautiful visualisation so that you can present data in a way that the human brain can interpret easily.  But it’s not just about a tool being able to draw graphs – every BI tool can do that - it’s about creating the right type of presentation to convey the meaning.  If you have played with any graphing tool you will know that it is can be very easy to create nonsensical charts, with pointless axes, too many bars/data points, inappropriate scaling and so on.  And it isn’t always obvious how to create what you really mean! 

Tableau, as well as enabling a huge set of highly visually-appealing chart types, won’t let you make a bad choice – based on the data you have selected, it will suggest only the representations that make sense. If you have too many data points, you can group all the small values together on-the-fly into a “Total Others” bar – just with a click and drag on-screen, no disappearing off to the admin console to create a new dimension value.  Similarly, if a scatter-plot has a lot of outliers, you can just eliminate them from your analysis and focus your concentration on the area of interest.

What impresses me most about Tableau is how astonishingly easily it does things that I always felt other tools were struggling with, or just couldn’t do.  It is crammed with so many useful analytical features that to describe Tableau as “a visualisation tool” misses much of the point!


Tableau's spider chart from Howard Dresner's BI study.

Tableau gives the BI specialist all the analytical power and creativity that he craves; however, it isn’t aimed at just at the specialist - anyone who is comfortable with Excel, or indeed any information worker, can create their own analysis with very little training.

For dealing with the complexities of the real business world, Tableau has intelligent native connectivity to a wide and growing array of common data sources, and the product developers are rapidly adding more and more “enterprise” features at every release.

Of course, the discovery-based products – Tableau, Qlikview, Endeca, Spotfire and so on – are somewhat over-hyped in their marketing.  The notion persists that you just take the tool, point it at any data, and off you go, with no technical knowledge or understanding of data structures.  No need for months of building complex plumbing to make it all happen.

It’s true that you can connect to fairly straightforward sources (spreadsheets, flat files, non-complex databases) very easily, and indeed one of the beauties of Tableau is the capability to blend two or more sources together quickly and discover what results the combinations can throw up.  But let’s take a quick reality check: most organisations have a complex collection of systems that probably aren’t integrated quite as well as they would like, and there remains no substitute or long-term shortcut for good data management.

The important thing is to strike a balance and understand the difference between rapid-fire data discovery – with possible inherent risks about the “food chain” of data provenance – and robust, consistent corporate information.  Each has its place and value, and with a little structure, discipline and process, Tableau can front both of these approaches.  The key is “just enough structure”.

Tableau enables you to deliver a very pragmatic and agile solution, especially if your data has some level of organisation to it, e.g. a data warehouse or a small number of datamarts, maybe enhanced by some properly-managed spreadsheet-based data (e.g. Targets or external benchmark data).  Its capabilities can be extended by combining with an agile open-source ETL tool, plus maybe an extreme performance layer such as HP Vertica, in order to  offer a very compelling , cost-effective and very fast solution even for Big Data analysis.

ClearPeaks helps you to get started with Tableau.  We can help you to acquire the right software (we are a Tableau Partner reseller) – the entry cost can be very low, because there is no minimum number of users: you can buy just one user license to begin with if you wish!

We provide training through our excellent Academy (>92% customer satisfaction rating) and offer packages of startup services to help you get your project to first base quickly and build momentum from the success of a quick win:

  • Installation, Planning, Training and Best Practices package
  • Phase 1 Guided Implementation: Assist and Assure package
  • Business As Usual package: 1 year of Support and mini-enhancements

Above all, we bring our many years of experience in implementing robust and successful BI solutions with major enterprise BI platforms such as OBIEE and Business Objects, to ensure that your Tableau implementation is enterprise-ready and doesn’t risk becoming an unmanaged cottage industry.

Are you curious about Tableau? Leave us a comment on what is your experience with Tableau or contact us if you would like to see a Demo.

Rich Visualizations in OBIEE with JavaScript



Are you suffering the consequences of Oracle BI’s limited, visually old fashioned out-of-the-box charts? Are the end users of your BI platform in need of extended functionalities?

Today’s article is the first one of our Oracle BI Customization series - How to impress managers blog article series.

To set out on this journey, I will show you how to integrate JavaScript into Oracle BI dashboards. JavaScript is a cross-platform programming language that provides you with the opportunity to meet your customer visual and usage requirements, thus enabling end user satisfaction.

A real case

For this article I want to start with a real scenario where the customer was very worried about the lack of user adoption in some of the corporate dashboards.

In these dashboards, a standard Gauge view was used to display aggregated information. The Oracle BI 11g standard Gauge view, despite having improved if compared to the 10g version, still has limited visual options and therefore has a lack of grip for the end user. This was not unnoticed by the dashboard users, who very soon started complaining about the “poor look & feel” of the dashboard.

As soon as this was identified, multiple alternatives where assessed in order to increase user adoption and therefore make sure the project was a big success. Among all options, the alternative of choice was to customize the analysis using JavaScript snippets. Here’s how it was done.


Create new gauges integrating JavaScript with Oracle BI. The goal is to have rich, clean, interactive and touch-enabled charts that will catch the attention of our users.

Implementation plan

Two simple steps are needed to get the integration done:

1. Deploy Java libraries on the WLS Server. We will get the Java libraries that help us to fulfill the requirements and we will make them available to the WebLogic server.

2. Create your custom charts in Oracle BI. We will create an analysis with a Narrative view that will include the code to display the customized charts.

1. Deploy Java libraries on the WLS Server

Usually, most of third party visualization tools require referencing some libraries in the format of JavaScript minimized files. Most of the times, the files are available directly from the Internet CDNs (like jQuery) so there’s no actual need to download or deploy any software component.

However, most of the Oracle BI implementations have their servers isolated from the Internet and protected in internal LAN segments. In this cases, referencing these resources from Oracle BI, means having to first deploy them in the WebLogic Server as static resources.

There are several ways to achieve this. Our recommendation is to create a virtual directory application and deploy it in your WebLogic Server. Once this is done, just copy the required JavaScript files to that folder using an FTP program, for instance, and the files will be automatically available to be used in Oracle BI.

Issues creating virtual directories? Please leave a comment and we will assist you on doing that.

2. Create your custom charts in Oracle BI

2.1 Create an Analysis

From the Home Page in Oracle BI, create a new Analysis that retrieves the set of data that you want to display in your charts.

For instance, to represent the total amount of budget consumed, we retrieve one single record with two columns: “Actual Budget” and “Approved Budget”.

However, if we want to represent the data of multiple countries we will have several records. Now, you have to bear in mind whether the number of countries that will be in your data set will remain the same or it may change over time. This is important to avoid any crush when displaying the chart; see below how to prevent it.

For this scenario we create a report, which returns one single row.


2.2 Add a new Narrative view

With your set of data ready to cook, we can start writing the script that will generate our chart. Add a new "Narrative view" from the drop down list of available views.


Do not forget to check the box: Contains HTML Markup


2.3 Reference the java library

On the Prefix section of your view, reference the Java library for your development, we call JustGage libraries as follows:

<script type="text/javascript" src="{resources}/raphael.2.1.0.min.js"></script>
<script type="text/javascript" src="{resources}/justgage.js"></script>

2.4 Define the style in a class

In order to apply a same style along your development, optionally you can define a CSS class to adjust the parameters, see below an example we use to set up the chart size:

<style type="text/css">
    .JGsize {height: 100px; width: 100px;}

We strongly advice to create a single file with all CSS code and place it in the virtual directory; this will avoid any type of inconsistency when rendering the charts.

2.5 Write the JavaScript code

Define and call the function to render the chart with the appropriate parameters. The basic definition of JustGage function is ‘JustGage({id, value})’ – refer to the documentation of each JavaScript library for more details.

<script type="text/javascript">
 function RCVG() {
  var RCVG = new JustGage({
    id: "RCVG",
    value: @n,
    valueFontColor: ["#666666"],
    min: 0,
    max: 100,
    formatNumber: true

Last, create a div container to render the chart.

<div id = "RCVG" class = "JGsize"></div>

Once you write the code keep in mind the structure of the Narrative view, where the Narrative section is the one that allows reference to the Answers columns @n and the Prefix and Postfix rendered before and after the Narrative as a simple block.
Said that, the final result in our scenario looks as below:


2.6 Displaying the results

Click on done and see how amazing your chart looks in a Dashboard page, you can repeat this steps for multiple data sets and display them all in a dashboard to cross information.


If you are planning to implement it take into consideration which type of browser are you using, in case of JustGage was tested compatible with Chrome 20, Firefox 12, IE6, 7, 8, 9, Opera 12, Safari 5.1.2, Android 4.0.

2.7 Advance example with Kendo UI

Other open source java libraries that are available is KendoUI, that brings you different features and look and feel, see below an example:


On that scenario the challenge is that we will have multiple records coming from our Answer and the number of records will vary. Then, if the number of records changes that will drive the chart rendering into madness.
What we did is to use a dynamic array. For this we have to create and ID on each of the rows that will be used as a record reference on our array. See below the results of the Cost by Product Analysis:


Now on our narrative view, we will create a variable defined as an array. For this scenario was enough to create an array of maximum 5 records. A for loop is created to read records and to create the array

var dim = new Array(5)

for (i=0; i <5; i++)
    dim[i] = new Array(2);
    var max_i;
    dim[@1][0] = "@2";
    dim[@1][1] = @3;
    max_i = @1;

Then when we create the function to create the chart we have to define the series of data will be displayed:


Looking at the results we see a consistent chart that will handle a set of data whatever the number of records:


An interesting Kendo UI feature is its interactive legend. This allows the user to simplify data on charts by clicking on the legend and compare specific KPIs, see the example below with line charts where we compare Actual versus Planing:

Kendo Legend Interaction


3. Conclusion

Customizing Oracle BI with JavaScript opens up a huge world of visual opportunities that may help you to meet the user requirements. Just make sure you are using them wisely and don’t fall into the trap of using them in every single Analysis.

By enriching the information with visual aids and displaying the data in a proper manner we can add a huge value to the user, as highlighting key data and placing it on the correct context will drive your solution to success.


More on Customization right here - the links will be updated as soon as we publish the articles:

  • How to create a Pop-Up effect
  • How to create a schematic effect
  • How to print a dashboard with JavaScript enhancements
  • Key principals on dashboard design


Integration of Linux/UNIX OBI installations with MS Access



Although most of the organizations that use OBI use some kind of ERP software, there are still divisions or departments where some decentralized, separate data is in use. Quite often those small solutions use MS Access as a database, as it’s cheap and doesn’t require a lot of technical knowledge. If there is a need to make this data available in the data warehouse, it usually requires an ETL process. Anyway, sometimes there may be a need to connect directly to MS Access and retrieve the data directly from such a source.

Unfortunately, there is no ODBC driver that would allow creating the connection from Linux server (where OBI is usually installed) to an MS Access database; hence you may think that creating this connection is impossible. Well, in this article I will solve this problem, describing one of the possible workarounds.


Before we get to the details, let me explain you briefly what we are going to implement. The whole concept is simple: we will install Oracle Database Gateway – which will ‘translate’ MS Access tables to be seen as Oracle tables. Then, using DB link we will connect DWH to the Oracle Gateway and we will create synonyms on the ‘db-linked’ tables. Finally, those synonyms will be imported to the RPD.


But what is Oracle Gateway actually?

Oracle Database Gateway is a product that translates the heterogeneous data source – which may be not only relational database (MS SQL Server, Teradata, Sybase – see documentation for full list), but any data source for which ODBC driver is available (MS Access, MS Excel, also any of previously mentioned RMDBSes) – to be seen as Oracle Database. The license for Gateway for ODBC is included if you already own Oracle DB, Gateways for other products have to be purchased separately. Oracle Database Gateway is not a standalone product, it requires Oracle Database to work. For the official Oracle’s summary on the Gateway, please check the following document: Oracle Database Gateway


Before we start, we need to download some software that will be necessary to configure and run the connection between OBI and MS Access. We also have to make sure that a few more components are available:

  • OBI environment: up, running & rock stable –your local virtual machine you use to play with RPD is fine, even if it freezes once in a while.
  • MS Windows machine, where you should have administration rights and which can be reached (through network) from the DWH that OBI uses. If you are short of Windows licenses, your local PC may also be used, if only it satisfies the previously mentioned networking condition. At that machine Oracle Database Gateway will be installed & Access database will also be located there.
  • Client machine, with Oracle client, Oracle SQL/development software & Oracle BI Administration Tool. Exactly like the PC you use each day in the office.
  • Oracle Database Gateways 11gR2 for Windows is necessary, it can be downloaded here: Oracle Database Gateway for Windows and now choose file (32bit version)  or here: Oracle Database Gateway for Windows  and download file (64bit version). OTN account is required to download any of them.
  • Package including ODBC Driver for MS Access: ODBC Driver download – if you don’t have the required ODBC Access driver installed in your system yet.

After making sure you have all components mentioned above, let’s roll with this! This tutorial will have the following parts:

  • #1: Configuring ODBC connection to MS Access database
  • #2: Installing and configuring Oracle Database Gateway
  • #3: Creating the required database objects in the DWH
  • #4: Importing MS Access tables to OBI


Step 1: Configuring ODBC connection to the MS Access database

This step is very simple. Copy the MS Access file containing the database you would like to integrate with OBI to the machine where you will install Oracle Gateway. This is mandatory, as Gateway cannot use a database located on network share, it has to be a local one. Now you would have to configure the ODBC connection for this database. If  you are a BI developer, you probably already did this a few hundred times, so let me go through that briefly:

’Control Panel’->‘Administrative Tools’->‘Data Sources (ODBC)’->‘System DSN’->‘Add’->’Microsoft Access Driver’. Now chose the file with the database, set credentials (if necessary), data source name & description, and that’s it!

If you don’t have the ODBC driver for MS Access installed, or if you just have the version supporting mdb file format (up to MS Access 2003), but you want to use accdb file, please download and install the ODBC driver for MS Access mentioned in the previous paragraph.

Step 2: Installing and configuring Oracle Database Gateway

If you have already downloaded the Gateway, you may start its installation. Extract the content of the downloaded zip file and then run file ‘setup.exe’. In the following steps set installation path, choose which gateway drivers you would like to install (for the need of this exercise only the Oracle Database Gateway for ODBC is required, which is, by the way, the only driver that is free of charge) and also configure listener of the Gateway, setting its name, network protocol and specifying the port number (assuming you chose TCP to be used, which in 99,99% you did). If you don’t use listener’s default name, please note & save it somewhere as it will be necessary in the following steps.

After the installation is finished, there are still a few files to be edited.

  • #1: GATEWAY_INSTALLATION_PATH/hs/admin/init[SID].ora

This file is used to connect Oracle Database Gateway to the previously created ODBC connection. You choose whatever you want to use as a SID – for this exercise let’s use ‘msacc’ as a SID, hence, the file should be named ‘initmsacc.ora’. As such a file doesn’t exist in the file system, to create it make a copy of ‘initdg4odbc.ora’ file that is created during the installation of Gateway. Then change its name and edit the content to be following:


# Environment variables required for the non-Oracle system


#set <envvar>=<value>#

# HS init parameters




Of course string [NAME_OF_ODBC_CONNECTION_CREATED_IN_STEP1] shall be replaced by the required value. After editing the file, save, close and proceed to the next one:

  • #2: GATEWAY_INSTALLATION_PATH/network/admin/listener.ora

Here the content should be as following:

















For those of you who wonder what line:


means: it just tells gateway to use ODBC Gateway. ‘PROGRAM’ parameter would have different value if you install and use other Gateway

Below there is an example, with values & paths set:




 (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))




ADR_BASE_LISTENER_GTW = C:\product\11.2.0\tg_1









Attention! If you want to integrate/import more than one MS Access database into OBI, repeat the bold part for every database you want to import, of course giving each of them a different name.

Save, exit and reboot Windows.

After rebooting Windows, you may open ‘services.msc’ console (Menu Start->Run->Type ‘services.msc’->hit Enter key), and there, you shall find a service named ‘OracleOraGtw11g_home1TNS[listener_name]’ and start it if it hasn’t started yet. The configuration of Gateway is now finished and it shall be ready to use! To verify it, run command


at the machine where the Oracle Gateway is installed and see listener’s status.

Step3: Creating the required database objects at the DWH

To make MS Access tables available for the import in OBI RPD, you need to do 3 things:

  • #1: Create TNS entry at OBI’s Data Warehouse pointing to listener’s SID created in the previous step:

The following entry should be added to ‘tnsnames.ora’ file used by your DWH:







So, in case of our exercise, the values should be as follows:

msacc_connection  =






(of course if you configured more than one SID in listener.ora file in a previous paragraph, then you should create a tns entry for each of them!)

To check if the created tns entry works correctly, you can execute the ‘tnsping’ command at the server where your DWH is installed.

#2: Create a database link at the Data Warehouse pointing to the MS Access database behind Oracle GatewayTo do that, run the following command:



So, in our case this would be:

CREATE DATABASE LINK ms_access_db USING msacc_connection ;
  • #3: Create synonyms for all tables of the MS Access database that you want to import to OBI

Now, for each table you want to import to OBI RPD, you have to execute:


Remember about quotation marks! They are obligatory!

If all your steps were successful, you should now be able to access data from MS Access in your Oracle DWH just by issuing:


Please also remember, that if you want to access only certain columns of such a synonym, you also need to put their names between quotation marks, e.g.:

SELECT “some_column_name” FROM TAB_SYNONYM;

Otherwise, it doesn’t work.

Now your objects should be ready to be imported to the RPD.

Step 4: Importing MS Access tables to OBI

Now the last step – import of the metadata. When we import Oracle tables to the OBI we can do that automatically, just ‘ticking’ the required table on the list of tables and then moving it to existing physical containers. Here the story is different, as the metadata of db-linked tables is not present in Oracle system dictionaries, hence it cannot be imported automatically (as, in the end, OBI’s automatic metadata import is a SQL query against ‘all_tables’ and ‘all_tab_cols ‘ dictionaries). So to import the metadata, we need to create the physical table manually (giving it the name of the synonym), and then, one by one, also create the physical columns manually. Bear in mind to put column names in quotation marks, like it was suggested while querying the columns in the SQL tool. You also need to remember that for some MS Access data types, the data type that should be used in OBIEE is not that obvious. For example ‘YES/NO’ type in MS Access translates to INTEGER in OBIEE. To check if metadata was imported correctly, please save the repository, then right click on the physical table name and choose ‘View Data’ from the menu.

And that’s it! Now you have your MS Access tables available in OBI RPD and you can add them to existing stars, create new hierarchies and do whatever you want!


MS Access databases are still in use in many places and it may be sometime useful or even necessary to add them to an existing BI system.  As you could find out, this operation is not that complex, and, once done correctly, may be easily repeated in the future, as just part of the steps needs to be repeated for any new database that has to be imported.

If you have any issues with the configuration described in the article, please let me know in the comments, maybe I will be able to help you.

privacy policy - Copyright © 2000-2010 ClearPeaks