Business Intelligence – Oracle

Archive for September, 2007

Oracle BI EE – Changing logos in the Answers Title View

Posted by Venkatakrishnan J on September 27, 2007

We had a question in one of our forums today, wherein the question was “How do we add a custom picture as a logo to a reports Title View?”. Though it is very much documented, i am not sure whether it works the same way as documented. Lets see how to go adding a custom oracle logo to the Answers Title View. The first step that one would have to do is, copy the image to 2 folders.

1.   {OracleBI}\web\res\app\s_oracle10\images

2.   {OracleBI}\oc4j_bi\j2ee\home\applications\analytics\analytics\res\s_oracle10\images

I am assuming that you are using the default style of s_oracle10. Restart your presentation services and the OC4J.Then open a report where you would like to add the image to and click on the edit title view.


In order, to use the images in the title view, one would have to enter the relative path of the image that we just copied. It would look like this


fmap is a keyword and is case sensitive. Then we are good to go. Let me know if anyone has got their title views working by following the documentation.


Posted in All Posts, OBI EE Plus | 13 Comments »

Oracle BI EE and mapviewer – Step by Step Integration Phase2 / Phase3

Posted by Venkatakrishnan J on September 26, 2007

We saw yesterday here on how to go about implementing the phase 1 of integrating OBI EE and Mapviewer. Now lets discuss the next 2 phases. I would rather focus on the phase 3 which shows how to about passing parameters from Mapviewer to OBI EE.

Phase 2 is primarily on the mapviewer front wherein one can have multiple basemaps and change the basemaps when we click on any foi region dynamically in order to simulate a drill down. We would be using the same example as we used in phase1. But our aim is to showcase the way of passing parameters from mapviewer to OBI EE. So, lets begin with the simple map that we created yesterday. Instead of passing parameters to the map, we would change the map in such a way that it would show the customers in 2 cities i.e OAKLAND and HAYWARD. The idea is to show the sales of a city while clicking on any customer within that city. For example, if we click on any customer within OAKLAND our OBI EE sales report should change and show the sales of OAKLAND. The same should happen for HAYWARD city.

1. Lets create a BI EE report that would basically show the sales of all the cities. Create a filter on city that would be entered via prompts (i.e prompted).



2. Once this is done, the next step is to create a narrative BI EE report that would basically call the above report using iframes. Ensure that iframe is given a name and also check the GO URL parameter for the BI EE report that we created earlier. It should look like this


The above url will pass OAKLAND as a parameter to the BI EE report which in turn would be displayed in a narrative iframe element named BI EE.


3. The next step is to create a map that would basically show the customers within the 2 cities OAKLAND and HAYWARD. We shall use the same map that we created yesterday. Save the same map as a new map and modify it to suit our needs.


Lets discuss the structure of the map html today. I have commented out the part wherein we had passed Region as a parameter to the map yesterday. Also, i have hardcoded 2 regions OAKLAND and HAYWARD using the below parameter variable.

var parameters = “\”” + “OAKLAND” + “\”,” + “\”” + “HAYWARD” + “\””;

Once this is done, we need to add a listener in order to make the map listen for mouse clicks on the customers. This is done by this part of the code.

var marrayPara = new ArrayParameter(parameters,’sarray’,’city_list’);
var themebasedfoi=mapview.getThemeBasedFOI(“themebasedfoi1”);
if (themebasedfoi==null)
themebasedfoi = new MVThemeBasedFOI(‘themebasedfoi1′,’mvdemo.customer_by_cities’);
themebasedfoi.refresh() ;

themebasedfoi.addEventListener(“mouse_click”, foiClick);

After adding the mouse click event, we need to define the foiClick function that would basically obtain the city name (stored in the foi) and pass it on to OBI EE.

function foiClick(point, foi)

var drillURL =



objWin =, “biee”,”height=480,width=240,scrollbars=yes,resizeable=yes”);


The foi.attrs[1] would have the city name and this is passed to OBI EE in the form of GO URL parameter. OBI EE report would change dynamically since points to the biee (name we gave earlier to the iframe) iframe.


When one clicks on the OAKLAND customers, the OBI EE report would give the sales of OAKLAND city. Same is the case for HAYWARD.



Posted in All Posts, Mapviewer, OBI EE Plus | 9 Comments »

Oracle BI EE and Mapviewer – Step by Step integration – Phase1

Posted by Venkatakrishnan J on September 25, 2007

Update: Phase2 and Phase3 are available here

I had mentioned in my previous blog entry here that i was working on documenting a step by step approach for the integration between Mapviewer and BI EE. Well, the integration is not pretty much out of the box and involves quite a bit of understanding primarily from the Mapviewer front. So, just be aware that the integration is not out of the box and would require some amount of coding. The entire integration process involves understanding 3 major pieces.

1. Understanding how to pass parameters between Mapviewer and a Dashboard Prompt (Maps must change as we change the prompt).

2. Understanding how we can simulate drill down within a map (This is the harder part that does not involve BI EE).

3. Understanding how one can change the prompts of BI EE as we navigate in a Map.

We will see the first part today as part of a 3-phase series wherein i would try to document this in a step by step fashion. But before i go there, one would have to have these pre-requisites done.

1. Deploy Mapviewer in the OC4J that holds BI EE.

2. Get all the demo setup of Mapviewer up and running. One must be able to view the demo maps that comes along with mapviewer.(This needs a schema called mvdemo that would basically contain all the spatial data in an Oracle Database).

3. A little background of javascript is desired.

If you have all of these pre-requisites then you can try the following. We would be seeing the phase 1 of the integration process. The idea of this integration is to create a dashboard prompt that would basically contain all the cities and based on the city that we select, we must get a map which would show all the customers within that city.

This involves the following steps.

1. Import the table called customers(without the location column) into OBI EE Administration Console. Do the corresponding mapping in BM and the Physical layer.

2. Once this is done. We would have to create a dashboard prompt that would basically list down all the cities and also set a presentation variable called Region based on what we have selected.

As you see above, i have created a dashboard prompt that would basically set a presentation variable called Region.

3. Now lets go to the mapviewer and open up the default demo map called “Array Parameter and Templated Theme Based FOI”.

The map should look like this.

Our idea is to build upon an existing map. The above map basically varies the map based on input city names.

Now lets view the source of the above map and change the source based on our requirements. I have used the following code wherein i have removed unnecessary parts. Remember we would be passing the presentation variable as the URL parameter to the mapviewer. I have added a function called getURLParameter which would basically store the value of the URL parameter in a variable called Region.

Test out whether the URL parameter is getting passed properly to mapviewer. The URL to pass into the mapviewer would look like this http://localhost:9704/mapviewer/fsmc/tutorial/samples/DynarrayThemeBasedFOI.html?Region=OAKLAND

4. The next step is to pass down this Region variable to the mapviewer from the dashboard prompt. We can do that using iframes in a narrative view report. Create a new narrative view report and enter the data as shown below. The iframe URL would be like this{Region} where @{Region} is the presentation variable that we had created in step 2.

5. Once this is done, we would have to call the both the reports from a dashboard i.e the Dashboard Prompt report and Narrative report.

I will follow this one up with phase-2 and phase-3 later. But the above should get you started.

Posted in All Posts, Mapviewer, OBI EE Plus | 24 Comments »

Power of Data – Importance of BI

Posted by Venkatakrishnan J on September 22, 2007

I was going through the following presentation by Hans Rosling, a renowned professor of World Health. An amazing presentation which makes anyone understand the value of data that is hidden behind the behemoths called databases. It would make even more sense if you can compare this to the importance of Business Intelligence or the Intelligence derived from various Reporting toolsets. Amazing video. I would recommend this to anyone who wants to know the power of data.

What i like in this video is the 2 main aspects

1. The way it was presented.

2. The analysis that Hans has applied on top of the data.

What BI tools can do is, exactly this. Give a new perspective of the data that we already have in our databases.

Posted in General | Leave a Comment »

Oracle BI EE – Ref Cursors and Pipelined functions

Posted by Venkatakrishnan J on September 21, 2007

Another interesting question that generally comes up is whether Oracle BI EE supports Ref Cursors. Well, currently only BI Publisher supports the use of ref cursors to pass the data to the output (Remember, BI Publisher uses jdbc connections and jdbc supports ref cursors.). I tried using the following ref cursor function on both BI Publisher and BI EE. Lets see what the results are and of course lets see what is the workaround available for OBI EE. To start with i created a function that basically returns a ref cursor of the dept table in scott schema.

create or replace function refcursor_dept return sys_refcursor as
TYPE v_cursor is REF CURSOR;
p_cursor v_cursor;
open p_cursor for
select * from dept;
return p_cursor;


In BI Publisher this would work like a breeze. All one would have to do is to call the function. Tim Dexter has already blogged about this. One can get more details here.

Now, lets try this in BI EE. I will be testing this using the direct database requests option on BI EE since it provides an easier way of testing things out. If you have not set direct database requests, i have blogged about it here showing you how to set it up. I have created a connection pool that basically points to my scott schema.


Now, lets fire the query using direct database requests using the connection pool that i had created earlier.


As you see, we get an error saying that this data type is not supported (this is the first time i am trying this out. If anyone has found a way of directly calling reference cursors from OBI EE please do let me know. I would be more than happy to share it here). So, for users who want to leverage their existing ref cursor functions, one option is to use pipelined functions. Lets see for the above example, how one can go about creating a pipelined function and of course how one can call them from OBI EE.

The first step is to create an object type that would basically contain all the columns of the dept table.

create or replace type dept_objecttype as object

Once this is done, lets create a table type of the above object.

create or replace type tabletype_dept as table of dept_objecttype;

Once this is done, lets create a pipelined function that would basically take in the ref cursor function that we created earlier as an argument.

create or replace function pipelined_dept
(p_cursor in sys_refcursor)
return tabletype_dept
v_records dept%rowtype;
fetch p_cursor into v_records;
exit when (p_cursor%notfound);
pipe row(
v_records.loc) );
end loop;

Now lets test this function from sqlplus.


Once this is done, lets test it out in BI EE.



For more detailed explanation on pipelined functions, i would recommend Tom Kyte’s article here. And yes of course, if you have other methods of calling ref cursors, feel free to put them in the comments section.

Posted in All Posts, Bi Publisher, OBI EE Plus | 2 Comments »

It’s Time – Blogroll Update

Posted by Venkatakrishnan J on September 20, 2007

It is high time that i updated my blogroll. I am a regular reader of many wonderful Oracle related blogs out there and its time that i put them all here and of course add them to my blogroll.
New blogs added in no particular order are,

1.   Frank Buytendijk – 
2.   Eddie Awad’s Blog – 
3.   Justin Kestelyn –
4.   Pawel Barut –
5.   Peter Scott –
6.   David Aldridge –
7.   Doug Burns –
8.   Tim Hall –
9.   Borkur Steingrimsson –
10.   Adrian Ward –
11.   Dimitri Gielis –
12.   Pete Finnigan –
13.  Laurent Schneider –

Posted in All Posts, General | 1 Comment »

Oracle BI EE – Write Back Option – Budgeting/Planning

Posted by Venkatakrishnan J on September 20, 2007

OBI EE has another interesting feature that would allow end users to update or insert data back into the database. For example, one can have a column of data that can be manually entered by end users, which in turn can be updated back to the database. This would make a lot of sense for users who want to do planning or budgeting on a mini scale. They can use OBI EE both as a reporting tool and also partly for entering sales quotas or budgets etc depending on how the business is performing currently. Let us see how to go about setting up this option in OBI EE. The entire process of setting this up involves changes right from the connection pool to the presentation layer. The example that i am going to follow here is based on the data from SH schema. I have added a new column called Sales_Quota to the sales table which is what i would like to update and insert. The idea is to allow end users to enter data for sales_quota and insert new records into the database.


1.   The first step is to enter in the relevant details in the connection pool writeback properties section. If you are on Oracle, leave them as default.


2.   The second step is to make the sales table not cacheable. This would ensure that we would get the results as and when we update or insert.


3.   Once this done, log into OBI EE Answer and give in the writeback privilege to those users who would need them. In my case, i would be giving them to Administrator since it is not enabled by default.


4.   Next is to create a sample report that would include the sales_quota column. I have a report with all the 5 dimension keys and sales_quota.


5.   Once this is done, go to the column properties of sales_quota and enable writeback.


6.   After enabling the write back, save the report. The next step is to specify the query that we would like the BI Server to execute while writing back to the database. The queries (insert and update) are specified in a XML Template.The XML Templates are generally placed in {ORACLEBI}/web/customMessages folder. The structure of my XML template file is

The filename of the XML file can be anything. But the WebMessage name is the actual XML template name i.e SHNEW is the XML Template name. One can have inserts as well as updates. @{cn-1} specifies the actual coulmns that i have pulled into the report.

7.   Go to the table properties of the report and click on the write back option. Enter in the template details.



8.   Once this is done, enter in some data and test out the write back.



More details can be obtained from the BI Server and Presentation Services guides.

Posted in All Posts, OBI EE Plus | 65 Comments »

Oracle BI EE – Execute Direct Database Requests

Posted by Venkatakrishnan J on September 18, 2007

This article is just to show the one of the features of OBI EE. It is not recommended to use this feature since it bypasses the metadata layer and executes direct database requests. If it has to be used in your environment, do it with utmost care and caution taking all the security into consideration.

OBI EE can execute user provided SQL on a database and can get the results back to the end user. By default this feature is turned off. In order to enable it, there are 2 steps that one would have to follow.

The first step is to enable the database in your physical layer to allow for direct database requests.



Once this is done, we would have to login to the presentation services and give the necessary “execute direct database requests” permission to the user. This is turned off by default even for the Administrator. Lets turn it on for the administrator using the manage privileges.

Go to Answers->Settings->Administration->Manage Privileges


Then enable direct database requests privilege for the user group that you need. In my case, just for demonstration, i have given that permission to everyone.


Once this is done, go to Answers and click on Execute Direct Database Requests and then enter the query along with the connection pool name.



As you would see here, one can enter any query that the database would support. For more details on securing this feature refer BI Server Guide and Presentation Services Guide

Posted in All Posts, OBI EE Plus | 8 Comments »

Oracle 11g – BLOB and BFile support for SQL Plus

Posted by Venkatakrishnan J on September 17, 2007

I happened to come across one other new feature in Oracle 11g with respect to SQL Plus. SQL Plus now supports the use of blobs and bfiles in your queries. This is what the documentation has got to say about this “Queries in SQL*Plus now support BLOB columns.This feature allows you to verify the contents of BLOB columns.”. Pretty interesting feature.

This is what i get in 10g.    


And this is what i get in 11g.     


Posted in All Posts, Oracle 11g | 4 Comments »

Oracle BI EE – Configuring Delivers – iBots

Posted by Venkatakrishnan J on September 13, 2007

I had one OBI EE user today who had specifically asked about configuring iBots. Though it is pretty straight forward, the customer was looking out for a screenshot by screenshot tutorial on how to go about setting delivers. Well, i had some time to put this one together. Hopefully, you would find it of some use.

1.   The first step is to configure a schema that would hold all our scheduler tables. If you do not have a schema create or use an existing one. In my case i have a schema called S_NQ_SCHED wherein i would create all my scheduler tables.

2.   Navigate to your {ORACLEBI}/Server/Schema folder and run the scheduler scripts. If you are on an Oracle Database, you would have to run SAJOBS.Oracle.sql. This would basically create the scheduler tables that Oracle Delivers would use later.

3.   The next step is to open your Job Manager. Go to File – > Configuration Options and enter the schema and connection details for the scheduler schema. In my case it would be S_NQ_SCHED schema.


4.   Go to the General tab. If you had followed the default install, then leave everything the same. Just enter in the Administrator usernames and passwords.


5.   Go to the Mail tab and enter in your mail server details.


6.   Open your instanceconfig.xml and add in the scheduler details. This step basically lets the presentation server know about the machine and port details of the scheduler. By default the scheduler port is 9705.


7.   The next step is to run a cryptotools utility that would basically store the username and password of the scheduler into an XML file called credentialstore.xml.

8.   Go to {OracleBI}/web/bin from command prompt. Then enter in the following command.

cryptotools credstore -add -infile OracleBIData_HOME/web/config/credentialstore.xml


Ensure that you have the alias as “Admin”.

9.   The next step is to add the details of this credentialstore.xml file into the instanceconfig.xml.


Once this is done you should be able to schedule and create new ibots. The best and quick way to check whether the configuration has been done properly is to save a sample ibot. If you are able to properly save one then your configuration has been properly set. For more details refer the docs here.

Posted in All Posts, OBI EE Plus | 111 Comments »