Easy method to create BIP 11g reports on top of OBIEE dashboards

This blog talks about a quick way to create BIP reports on top of OBIEE dashboards.

Active directory configuration in OBIEE 11.1.1.7

We will learn about configuring Active Directory (AD) for OBIEE 11.1.1.7 today.

Non Standard SQL in BIP 11g - Dynamic query implementation

SQL query is often used as a data source in BIP and often we have a requirement in which we have to use one table when the user selects a certain value in a parameter and select a totally different table when the user selects another value in the parameter. While we have discussed a method to do this using pipelined functions in http://obiee-oracledb.blogspot.com.au/2011/07/performance-enhancing-drug-called-table.html, this blog post talks about a BIP 11g method of achieving the same result.

Increasing width of Multiselect in OBIEE 10g

I was recently involved in a project implementation on OBIEE 10g. The dashboard had a multiselect and the values in the prompt were huge. The standard width of the multiselect was making the user uncomfortable since a large part of the value was hidden due to the small size of the multiselect. This blog talks about a method to fix this problem.

Privilege to see hidden objects in OBIEE 11g

My client wanted to hide some of the reports from the users and release them at a later date. The however wanted a set of users to have the privilege to see hidden objects. Let us see the process to achieve this objective.

Smart Tables in OBIEE 11g

This article introduces the concept of Smart Tables in OBIEE. I call them 'Smart' because these tables have the ability to produce different data on the fly. These can morph themselves according to the user requirement. Smart Tables also make the data model dynamic in nature as a Smart Table can change characteristics on the fly. Let me now explain the procedure of implementing Smart Tables.

Skipping schedule in BI Publisher 11g

This is an awesome feature in BI Publisher 11g but I do not hear a lot of noise about it so let me create some.
I'm talking about Event triggers in BI Publisher 11g. Using event triggers, we can conditionally stop the delivery of a report. We will see the process of skipping schedules in this article.

Drill-able hierarchical charts in OBIEE 11g

This is a discovery more than a solution. OBIEE 11g lets us use Hierarchical column as dimensional attributes for charts.

Formatting levels of a hierarchical column in OBIEE 11g

I had a requirement to indent the levels in a hierarchical column. The solution isn't really innovative but I thought of sharing this because finding the option can become a treasure hunt.

Capturing Environment Variables in Oracle APEX

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

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.

Using DB Link in OBIEE

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

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.

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 breadcrumbs, data 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.

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.

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.

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

Creating dynamic breadcrumb chains in Oracle APEX

This article is borrowed from one of the topics of my book (http://www.packtpub.com/oracle-apex-4-2-reporting/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.

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.

Sangam 2013

Just read about http://www.sangam13.info/.The 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.

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.

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.

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 http://www.packtpub.com/oracle-apex-4-2-reporting/book. 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 http://obiee-oracledb.blogspot.in/2013/07/my-upcoming-book-oracle-apex-42.html. Let me briefly walk you through the contents of the book.

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 www.packtpub.com/oracle-apex-4-2-reporting/book

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

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

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

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.

          <SsStartTime>18:00:00</SsStartTime>

The requirement is to display this time as 6:00PM

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

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.

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.

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

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

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)

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

1. Installing APEX schema and related DB components

2. Installing APEX listener

3. Time to configure the listener

4. APEX Listener in standalone mode

Silenced writeback using webservices - part 2

Happy Valentine's day to all :)

This is the 2^nd 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

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

Pivotal performance improvement in OBIEE

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

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.

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

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.

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. 

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.

Bouquet of BI functions

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

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 2^nd tool can then be invoked to get the desired results.

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

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’.

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 

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

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

Installing OBIEE on a Solaris box

There are 2 kinds of possible installations

a.       Basic

b.      Advanced

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