Saturday, 26 July 2014

Capturing Environment Variables in Oracle APEX

 This post focuses on the Environment variables as they are vital in coding conditional statements.

Wednesday, 23 July 2014

String aggregation in OBIEE

In the BI world, we talk about measures and aggregation but we don't talk a lot about string aggregation. String aggregation is about concatenating strings for a certain dimensional value. Let me give an example. Let us say that we have a purchase order (Lets call it PO1) raised for a set of items (Lets say I1,I2 and I3). Lets say that the supplier does not have I1 in stock and wants a larger order quantity for I2 to make the shipping viable and the supplier feels that I3 can be shipped. The store manager might want to see "I1 out of stock, Order quantity of I2 is too less" against PO1. Question is, how do we do it? This blog talks about the process.

Sunday, 20 July 2014

Using DB Link in OBIEE

This blog talks about the process to use DB Links on OBIEE.

Saturday, 19 July 2014

Using BIP parameters in OBIEE dashboards without creating prompts

This is a simple trick that presents the process to have a parametrized BIP report in OBIEE without the need to create new prompts in OBIEE.

Monday, 3 February 2014

Ways to create Matrix report in Oracle APEX

This is another one of those articles where I pick a topic from the sample chapter of my book and share with you. Some of the other discussions in this blog from the sample chapter of my book are on dynamic breadcrumbsdata densification and ReForms. In this post, we will see 3 different ways of creating a Matrix report a.k.a Crosstab report a.k.a Pivoted report.

Friday, 31 January 2014

Consuming OBIEE 11g Web Services using Java

We will talk about consuming OBIEE 11g webservices using Java in this article. My previous articles have been about consuming webservices using JavaScript and consuming webservices using PL/SQL. This is my first on consuming webservices using Java. 
My book also has a number of recipes about consuming and creating web services. Check out a brief description of the book and the table of contents. You may also check out the online application build on top of the discussions in the sample chapter of my book.

Monday, 23 December 2013

Creating ReForms in Oracle APEX

Let me begin by defining a ReForm. A ReForm is an amalgum of Reports and Forms and has the DNA of both. This discussion not only presents a ReForm but also demonstrates the enormous benefits of using JQuery in APEX report development.

Monday, 16 December 2013

Data Densification in Oracle APEX

Data is often stored in sparse form and business often wants to see data in a more organized and explicit fashion. Report developers working across reporting technologies face a requirement to show a record with a '0' in the measures columns if no data exists for a particular combination of dimension values. For such a requirement, we will have to densify the data and this article presents a way to densify and aggregate on the fly.

I wish to state that this article is borrowed from my book on Oracle APEX 4.2 Reporting. You can check the table of contents and a brief description along with screenshots.

Let me explain the use case now

Thursday, 12 December 2013

Creating dynamic breadcrumb chains in Oracle APEX

This article is borrowed from one of the topics of my book ( on Oracle APEX 4.2 reporting. Check the chapter by chapter description and the table of contents to know about the other discussions in my book. I wish to talk about the process to engineering breadcrumbs of varying lengths in this article. Let me explain the motive with an example.

Wednesday, 13 November 2013

Tracking the running reports in OBIEE 11g

I faced a rather strange but relatively simple requirement a few days back. The client had employed a resource to refresh the sessions page and capture the running queries from it and mail it to a team that dealt with performance tuning. This is certainly not the most efficient resource management.

It is strange that OBIEE has an elaborate mechanism of Usage Tracking but this system does not capture the running analysis. It captures the info only after the queries have fetched the desired results and not while they are still running.

This post talks about devising a programmatic way of capturing the info about running analysis/reports in OBIEE 11g.

Thursday, 3 October 2013

Sangam 2013

Just read about event has been cohosted by AIOUG and India OAUG

Such events are indeed rare and few in India. Really hope this to be a trendsetter.

Wednesday, 25 September 2013

ESRI map with Google map and map viewer in APEX

This article is in continuation of my previous work on Oracle Map Viewer/Map Builder and Oracle 12c database.

This article demonstrates the conversion of ESRI maps to datatype (SDO_GEOMETRY) that can then be used in Map Viewer. The article ends with an HTML code that can be put in APEX's HTML region to display the map with ESRI info on it.

Saturday, 14 September 2013

My take on using Mapviewer with Oracle 12c and Google maps API

Map viewer is a kool technology and I wanted to write about it for a long time. 

Let me first chalk the steps that we will follow. We will

1. Download Map Viewer and Map Builder.

2. Create a pluggable database (pdb), create a pdb user and grant the necessary privileges to it.

3. Configure Map viewer.

4. Insert spatial data.

5. Use Map Builder to create styles and themes.

6. Get a key from google and then use it to create a map tile layer using Map Viewer.

7. Write code to create a FOI layer on a base map using Map Viewer's JavaScript API.

Friday, 30 August 2013

Description of my book on Oracle APEX 4.2 reporting

Oracle APEX 4.2 reporting is designed to bring the best of the reporting world for intermediate –advanced readers. The book is available at It is designed to expose APEX developers to other reporting solutions, inform them about the strengths of each of these and to enable them to use these technologies in APEX. The book also assists designers who wish to use APEX with their existing heterogeneous enterprise so that their existing code is reused and effort in building better business intelligence systems is minimized. The table of contents of the book can be found at Let me briefly walk you through the contents of the book.

Thursday, 11 July 2013

My upcoming book - Oracle APEX 4.2 reporting

I have finally finished writing my book on Oracle APEX reporting. The book will be  is now available at

Sunday, 23 September 2012

Creating Download Utility using PLSQL

Let me start this article with an apology for not posting an article in such a long time. I have been involved in writing a book and it is eating most of my time so articles will be sparse in the next few months as well. Let’s however cut the negativity and enjoy the fun of creating a download utility using PLSQL

Friday, 8 June 2012

Coding different page layouts in a single rtf template and the importance of Contexts in BI Publisher

The requirement was to generate an output in statement page size or in legal page size, depending on a parameter value. Since the parameter was passed from Siebel and since I did not know the method to conditionally call different rtf files from Siebel so I had to code everything in 1 single rtf file

Saturday, 2 June 2012

Sorting outer group based on inner group in BI Publisher

I spent almost a day to implement a grouping and sorting requirement and after all that brain storming I found that the requirement can be implemented by an elementary piece of code. I wish to share this with the rest of the world

Wednesday, 23 May 2012

Formatting time in BI Publisher

This article describes a mechanism to format time in BI Publisher.
Let me explain with an example
I have a XML file with the following tag in it.
The requirement is to display this time as 6:00PM

Tuesday, 1 May 2012

OBIEE performance improvement tips

My 1st article in this blog was about OBIEE repository tuning and this article is an extension to it.

This article is intended to be a glossary of the things that I feel are important to tune OBIEE output. This article is only intended to give a pointer and an introduction to the things mentioned in it
Please add your comments if you feel that I have missed something. I will update this article if I discover some new tricks

Enough said, lets roll

Friday, 27 April 2012

Using custom transformations in BI Publisher 10G

Out of all my posts on BI publisher, this one is my personal favorite. The article describes a mechanism to use any custom function in BI publisher reports. This opens a whole box of opportunities. The custom functions have to be written in java which means that you can code anything between the sun and the earth and then use it in your BI Publisher reports.

Friday, 9 March 2012

Creating reports in Excel using OBI server

This article describes a mechanism to create reports in excel using the relations established in Oracle BI Server. We can leverage BI server and the relations established in it to create quick reports in excel. The last paragraph of the article talks about using the iqy file to get data with OBIEE's formatting.

Thursday, 8 March 2012

SQL developer data modeler for OBIEE developers

I haven’t extensively worked on data modeling but I saw this kool feature of sql developer data modeler. It gives you a relational picture of your warehouse and lists all attributes, enforced constraints, joins, keys etc. It became a lot easier for me to work with the physical layer after using this

Wednesday, 29 February 2012

Creating Applications in OBIEE using DAD

OBIEE is a great business intelligence tool but it is a one way highway. Users can use OBIEE to see the intelligence in the data but what if users want to put a few things of their own. I am presenting a mechanism to make OBIEE a 2 way highway

Thursday, 23 February 2012

Folding table in Oracle using unpivot function

In this post, I am unsing unpivot function to change the arity and cardinality in the result. mXn table is used to get pXq result such that product(m,n) = product(p,q)

Saturday, 18 February 2012

Installing SSL enabled APEX 4.1 with development environment and APEX Listener on Weblogic and standalone

This article presents a way to set SSL enabled APEX 4.1 on weblogic using APEX listener. The final part shows the process to configure standalone APEX listener and the use of Grizzly in it.
This post is divided in 4 sections

Tuesday, 14 February 2012

Silenced writeback using webservices - part 2

Happy Valentine's day to all :)

This is the 2nd part of the series in which I am using webservies to writeback. In my previous article, I showed a mechanism to do writeback using a utility of OC4J. That mechanism works well with OC4J and OAS (Oracle Application Server) but for other webservers we need an independent webservice. In this article, I'm creating an independent webservice and deploying it on weblogic

Saturday, 11 February 2012

Silenced writeback using webservices - part 1

This article presents an alternate way to do write back in OBIEE. I call it silenced because this doecs not involve the creation of text boxes to writeback something into the database. The mechanism is helpful if we intend to writeback some info, depending on some action taken by the user and we do not want the user to explicitly write the info in text boxes

Saturday, 14 January 2012

Pivotal performance improvement in OBIEE

This article shows a method to improve the performance of the pivot view of OBIEE. 

Friday, 6 January 2012

Freedom to format in OBIEE

I’m finally back to my blogging business J. This article deals with the usual OBIEE business of formatting. OBIEE gives a lot of formatting options by letting us write javascript/HTML code in a spectrum of places such as narrative, static view, text’s in dashboards, data format tab of columns etc.
I am sure you can list a lot more examples of places where we can write javascript/HTML in OBIEE.

Friday, 9 December 2011

Book review - Oracle BI Publisher 11g: A Practical Guide to Enterprise Reporting

Below is a review of Oracle BI Publisher 11g-A Practical Guide to Enterprise Reporting, written by Daniela Bozdoc and published by Packt Publishing. More information about the book can be found at here

Friday, 21 October 2011

Making OBIEE web service friendly

I know that I am obsessed with web services, but this is my final article in the series dealing with web services.

Monday, 10 October 2011

BPEL tasklist in OBIEE and APEX

We have seen the process of creating a BPEL process with human work flows in my previous article. We have also seen the process of shifting a task from the work list of one user to the other.
Now let’s put this now info to use. 

Saturday, 8 October 2011

BPEL Human Workflows

Let's talk about Human workflows. I have given an introduction to Human workflows in one of my previous posts and I will like to start from where I had left there.

Thursday, 8 September 2011

Bouquet of BI functions

I thought of putting OBIEE, BI Publisher, APEX and BPEL together.

Wednesday, 31 August 2011

Using BPEL in APEX

BPEL is a tool used to define web services. We can use it to invoke web service of a certain application or tool and then transform the output of this service into something that can be used by the web service of another tool. The web service of the 2nd tool can then be invoked to get the desired results.

Friday, 15 July 2011

Query to get a character separated list from a result set

I wouldn’t take the credit of this but I found a very cool way of converting an array to a string using hierarchical queries. I found this mechanism in one of my projects and I think it is good enough to be shared

Friday, 8 July 2011

The performance enhancing drug called Table function

Finally a post on APEX :)

I have seen a number of situations in development of both Oracle APEX (Interactive reports) and in Oracle BI Publisher where the developer has to implement complex functionality but he only has the freedom to write SQL statements and not PL SQL logic. Due to this he ends up writing the complex logic using ‘and clause’ and ‘or clause’.

Saturday, 28 May 2011

Siebel database (OLTP) blocking

A few days back, I was working on a Siebel application which had the issue of a lot of database blockings and the running tasks frequently reached max tasks.
We took the below steps to pin the problem

Siebel architecture high level overview

Siebel has many components and the one which is extensively used is ecommunications_enu.
The following is a good view of the general architecture of Siebel 

Wednesday, 16 March 2011

Multiple BI Publisher on a server and its URL change

Building on the concept of changing files in the war (web archive) and hosting multiple applications, we will now try to host multiple BI Publisher applications on the same server. This process of changing files in war package and deploying it has been discussed in the following post

Tuesday, 15 March 2011

Changing the default URL of OBIEE

We have a limitation that we can only install one OBIEE server and 1 presentation server on a machine

Saturday, 12 March 2011

Installing OBIEE on a Solaris box

There are 2 kinds of possible installations
a.       Basic
b.      Advanced

Friday, 11 March 2011

Oracle Business Intelligence Repository Tuning

I am trying to collect the few steps that might help in repository tuning. The benefits of tuning something up the ladder will be less than the benefits of fixing things at the base of the ladder