Business Intelligence – Oracle

Archive for October, 2007

Oracle BI EE – New release – New Features

Posted by Venkatakrishnan J on October 22, 2007

Well, i picked this up from Abhinav’s blog here. Looks like the is out for download. One of the things that i do after any Oracle Software release is to go through the list of new features. I see that 3 new features for BI EE and 3 new features for BI Publisher have been added. But keep in mind that this is not a major release. This is only an add-on, if i may put it that way, release to the earlier release. Following are the list of new features in BI EE. I will quote it from the docs here

1. Oracle Business Intelligence Installer Now Deploys BI Components into One OC4J Container – If you had gone through Advanced installation of BI EE earlier, you would have noticed that all the components got deployed into the root container without creating a seperate OC4J container for all of the components. Now, in this release all the components will be deployed into a new OC4J container called as home.

2. DataDirect Connect ODBC Drivers for Linux and UNIX Operating Systems Bundled with Oracle BI EE – Well, now one does not have to go on the look out for ODBC drivers for various databases. Oracle BI EE provides ODBC drivers for Informix, MS SQL Server, and Sybase ASE databases(for linux and UNIX OS).

3. Support for Charts in Oracle BI EE for Arabic, Thai, and Hebrew – This looks to me more like a workaround since BI EE charting engine has a limitation in terms of its rendering capability for Arabic, Thai and Hebrew. Now, in BI EE you would have to use BI Publisher to render these charts. Use BI Answers report as the data source and use the BIP translation to do the rendering for you in Arabic, Thai and Hebrew.

BI Publisher New Features in

1. Time Out Value for Web Service Data Sources – One can now set the time out value for Web Service data sources.

2. Bidirectional Function for eText Data Fields

3. Default Repository – There is now a default repository location that has been added to xmlp-server-config.xml. One can set it to the file path desired. It is also set by default during installation.


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

Oracle BI EE – Presentation Catalog Captions

Posted by Venkatakrishnan J on October 19, 2007

There was one question today in the forums here with regard to the procedure for setting up Presentation Catalog Captions. Even though it is pretty much straight forward, i thought i would blog about it here since there have been some confusion with regard to the folders where Captions had to be copied. Let me first start with a brief introduction of what Presentation Catalog Captions actually mean. As you would know, BI EE suppors a variety of languages. The login screen would contain the list box having the list of languages and one can choose language based on his locale/preference. But when you actually login using the various languages you would find that all the Dashboards, Folders, Reports etc would be in Englidh by default. So what presentation catalog captions does is that it provides a procedure for translating all the presentation catalog data to the desired language. In our case, we will see how to go about converting the presentation catalog captions to french.

1.   Open the Catalog Manager. Open your presentation catalog in offline mode. The presentation catalog, if you had done a default install, would be under this directory {OracleBIData}/web/catalog/paint. Now go to Tools and click on Export Captions. That would prompt you to save the output XML file. Save this in any folder.



In my case i had copied it to D drive. As you see, the export process will basically create different XML files for different catalogs. The XML file of our interest is the PaintDemoCaptions.XML.

2.   Go to {OracleBIData}/web/res and create a folder l_fr(fr denotes the language french. If you are trying to do this for some other language put the corresponding abbr here). Open l_fr folder and under that create another folder called Captions. Now copy the PaintDemoCaptions.XML to this Captions directory.


3.   Open this XML file. As you would see this XML file would contain multiple tags and each of the names of Dashboards, Report, Folders etc would be under the Text Tags. Our aim is to translate these text strings to French. Just for demonstration purposes i would be showing the translation of a Dashboard Page Name and a Report name. I have used Babel Fish web site here to do the translation of 2 strings. The 2 strings i translated are

   Paint Demo   –   Peignez la Démo
   Brand Analysis   –   Analyse De Marque

Change these names in the PaintDemoCaptions.XML file and save the file.



4.   Restart the presentation services. First open the login to the dashboards with English as the language.


Log out and then login with French as the language.


As you see above the report name and the dashboard names have been translated to french.

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

Oracle BI EE – Dashboard Prompts and BI Publisher

Posted by Venkatakrishnan J on October 19, 2007

One of the common questions that generally come up with regard to BI EE and BI Publisher is “how does one pass parameters to BI Publisher reports in Dashboards?”. Well, there are two methods for achieving this.

1.   Create a BI Publisher Report using BI Answers report as the data source wherein the BI Answers report would have one of the column’s filter clause as “is prompted”.

2.   Create a BI Publisher Report using BI Answers/Database as data source. Create a parameter for the BI Publisher report and then match this parameter name while assigning the presentation variable for the dashboard prompt.

Method1 is straight forward. All we need to do is to create a report in BI Answers first and then assign filter to one of the columns using is prompted clause. In our case we would be creating a simple sales report across multiple regions. And Region is the column which would have the filter.


Now go to BI Publisher and use this BI Answers report as a data source. Create a simple template for this report. Check whether this simple report works.


Once this is done lets create a dashboard prompt in answers which would basically refer to the Region Column. In this method, there is no need for setting any presentation variables.


Lets create a new Dashboard page and add the prompt and the BI Publisher report. The BI Publisher report would change based on the Region that you select in the dashboard prompt.



      This method is generally the most used since most of the BI Publisher reports would have database as the data source. In order to test this, lets create a simple BI Publisher report with the data coming in from SH schema of the database. We will be creating a report with the same columns as above to keep it simple. Also create a simple template and check whether the BI Publisher report is working.


Now add a parameter to the report. In our case, we will have Region_Prompt as the parameter name. And test the report.



Then go to the BI Answers and create a dashboard prompt on the same Region column. In this prompt we must set a Presentation Variable, Report_Prompt. Just note the name of the presentation variable. It must exactly match that of BI Publisher parameter name. Include this dashboard prompt and the BI Publisher report into the dashboards. And test it out.



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

Oracle BI EE – Top 10 Common Errors

Posted by Venkatakrishnan J on October 18, 2007

Now that BI EE is gaining traction among a lot of users, i thought i would document a list of most common errors that users might encounter while using OBI EE. I will also be giving in the possible workarounds/solutions possible for these errors. Feel free to chip in with the error that you got and the solution which you used in the comments section.

1.   Failed to load the DLL D:\Oracle\OracleBI\server\Bin\nqsdbgatewayoci10g.dll. Check if ‘Oracle OCI 10G’ database client is installed.

         There can be many variations of the above error. But what this error essentially means is that BI Server is not able to identify the Oracle OCI drivers. So, first thing to check is whether you have installed Oracle client or database on the same machine as the BI Server. If you have these installed already but still getting this error, then if you are on windows check the Path variable. Check my blog entry here to know what your path should have. If you are Linux/Solaris check whether you have ORACLE_HOME, TNS_ADMIN, LD_LIBRARY_PATH properly set. For AIX, check whether ORACLE_HOME, TNS_ADMIN, LIBPATH are properly set. This should get you started and hopefully solve it. Of course there could be many other reasons, but the common reasons are that the environment variables are not properly set.

2.   Unable to start the BI Scheduler Service.

      For this one would have to go through a list of configuration steps. Scheduler is not configured by default since it requires a database to run the schedules on. Check my blog entry here.

3.   Unable to save an ibot. Errors out saying “Authentication Failed”.

      The most probable cause for this is that cryptotools utility was not run in the Scheduler configuration step. Check the 7th and 8th step of my blog entry here

4.   Errors while using Writeback option.

      a.   Template not found.

            This error occurs if the XML template that you have created is not identified by BI Server. Place the Templates in {OracleBI}/web/msgdb/custommessages folder.

      b.   Errors while doing the writeback.

            Check this forum entry here. Try all the options that are mentioned here. Always use OCI drivers for updating/inserting back to the database.

      For detailed step by step instructions, check my earlier blog entry here

5.   Unable to view the charts in BI EE.

      There can be a couple of reasons why this could happen. The display of charts depend on the Java Host service. Try starting that service. If it still does not work, then it could be so that you do not have a flash plugin installed. Check whether the other chart types like SVG, PNG are working. To set up SVG or PNG charts use my blog entry here

6.   Unable to start/stop the BI Server/Presentation Services/ Java host.

      If you are unable to stop/start the services, try starting them from command line. Use the following commands to start/stop the services from command line.

      net start/stop “Oracle BI Server” – For BI Server
      net start/stop “sawsvc” – For presentation Services
      net start/stop “sawjavahostsvc” – For Java Host
      net start/stop “Oracle BI Scheduler” – For scheduler

7.   While setting up external authentication, one might get error messages like init block not initialized.

      One of the possible reasons, if you think everything has been properly setup, is the overlap of users between the repository and the external directory. Remove the overlapping users from the RPD and test it out. For more info on how to setup LDAP/OID authentication check my blog entry here and here

8.   While using Evaluate function, common errors like “Union of non-compatible types. (HY000)”.

      I believe this is a bug in the Evaluate function implementation. One must cast all the arguments to the same data type before passing them as arguments.

9.   Unable to create a new style by following the documentation.

      Again, you have not done anything wrong. I believe there is a documentation bug on this. Check my blog entries here and here

10.   Unable to view/change the images in Answers Title view using FMAP.

      This is again a common problem due to a documentation bug. Try using my blog entry here. One would have to copy the images to 2 folders instead on 1 as given in the documentation.

There could be many other reasons why you might get these errors. But the above list would give you an idea of where to start looking if you are new to OBI EE. Feel free to share your thoughts here.

Posted in All Posts, General, OBI EE Plus | 33 Comments »

Oracle BI EE – Charts – Flash, SVG, PNG, JPEG

Posted by Venkatakrishnan J on October 17, 2007

If you are wondering what are all the various types of charts that OBI EE supports, then the answer is in title of this blog post. Yes, OBI EE supports Flash, SVG, PNG and JPEG charts. But by default one would get the flash charts. If you do not have a flash plugin in your browser then the flash charts would not work. So, if you do not want flash charts but like to have the same interactivity as flash charts then you can try SVG charts. In order to change the chart types to SVG, just follow the following steps

Go to {oracleBIData}/web/config. Open instanceconfig.xml. Add the following entry to the instanceconfig.xml file.


Just remember, these tags should come before the close of ServerInstance and WebConfig tags. Once you have copied this restart your presentation services and you would be getting the graphs in SVG format. But remember, it is always recommended to use Flash Charts since they support huge data sets. SVG might fail in some data sets.



As you see above, we have got SVG chart within OBI EE. If you feel that you dont need any kind of interactivity in your charts, try using the JPEG and PNG options. These would remove the drill downs from your charts and would present the charts as static images of type JPEG or PNG.





This is just to show you the types of charts one can have in OBI EE. But it is recommended that you stick to Flash charts since they have maximum interactivity.

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

Oracle BI EE – Blocking Answers Requests

Posted by Venkatakrishnan J on October 16, 2007

One other least explored/talked about feature of BI EE is the Blocking Requests feature. One can have customized pop up messages prompting users to enter valid data. For example, as administrators we might know that certain columns once pulled into the report will consume huge amounts of memory since a grouping on them is a costly operation. In such a case, we can have a customized pop up message(like “Apply filter on this column”) that would get displayed to those end users who are pulling that particular column into the report. Also, the users would not be able to create the report unless they apply some filter on that particular column. Lets see how to go about creating such kind of blocking requests.

1. Go to {OracleBI}\web\msgdb\messages. Open the file answerstemplates.xml in notepad. Search for the function validateAnalysisCriteria. This is the function which we are going to override using a custom function. To override we would be following the same method about which we saw earlier here.

2. Go to {OracleBI}\web\msgdb\customMessages. Create a file called QueryBlock.xml. The content of the file will be the following

<?xml version=”1.0″ encoding=”utf-8″?>
<WebMessageTables xmlns:sawm=””>
<WebMessageTable system=”QueryBlocking” table=”Messages”>
<WebMessage name=”kuiCriteriaBlockingScript” translate=”no”>
<script language=”javascript” src=”fmap:myblocking.js” mce_src=”fmap:myblocking.js” />

As you see above, the QueryBlock.xml overrides the kuiCriteriaBlockingScript. By default there are no scripts that would be called. Here we are making the function to call “myblocking.js” script.

3. Open a textpad/notepad and enter the following content and save it as myblocking.js in any folder.

function validateAnalysisCriteria(analysisXml)
// Create the helper object
var tValidator = new CriteriaValidator(analysisXml);
// Validation Logic
if (!tValidator.filterExists(“Sales Measures”,”Dollars”))
return “Why don’t you filter on Dollars?”;
return true;

I am using the blocking script available in the documentation. What this script basically does is that, it looks out for a filter on “Sales Measures”.”Dollars”. If it exists only then the function will succeed.

4. Copy the myblocking.js script to 2 folders. {OracleBI}/web/app/res/b_mozilla and {OracleBI}/oc4j_bi/j2ee/home/applications/analytics/analytics/res/b_mozilla

5. Restart BI Server and OC4J.

6. Now lets try creating a sample report without the filter first(include the dollars measure of the paint subject area). Click on display results.


We will get the message which we have put in the myblocking.js since we have not included any filter.

7. Now lets try applying a sample filter on dollars.



For more details refer page 56 of the documentation here.

Posted in All Posts, OBI EE Plus | Leave a Comment »

Oracle BI EE – Variation of YTD function

Posted by Venkatakrishnan J on October 15, 2007

Another interesting question came up in the forums last week. Though the question was pretty straight forward, i thought of blogging about it here since it involves the use of evaluate function. The question was similar to the one below. I have rephrased it for clarity sake.

“We are in the process of creating some YTD reports wherein the users can choose mutiple date ranges of the format ‘yyyymm’. So, if a user chooses ‘200605’ and ‘200708’, then we need to produce an output which would be sum of all the data(measure of the report) from ‘200601’ to ‘200604’ and ‘200701’ and ‘200707’”

So basically, it is a modification of a simple YTD report. But what makes this interesting is that the months that user chooses can vary across years, as shown above in the question. So if a user chooses ‘200605’ and ‘200708’ our output should be a summation of our desired measure across the first 5 months in 2006 and the first 7 months in 2008. Now lets start approaching this problem from a plain sql point of view. Lets create a sample table called Dateentry.


For the sake of simplicity, i have specified the date fields as varchar. Also, note that we only have a month level granularity to keep it simple. To understand this better, i have the value of all the measures in the year 2005 as 100, 2006 as 200 and 2007 as 300. Now lets get the above report using a simple sql. The sql would look like this



As you see above, this query basically involves a set by set comparison of the data and then applying filtering on top of that. Now lets see how to do this in OBI EE. Lets start with building a dashboard prompt. Remember, we cannot have a multi select dashboard prompt here since currently it cannot set presentation variables. We would have to go with a dashboard prompt wherein the users would have to enter the dates in comma seperated format.


In the dashboard prompt, we would set a presentation variable called “datefield”. Now lets create a function in the database which would basically use the above query to generate the sum of “Monthly YTD”. The function would look like this

v_sum number;
exists (select datefield from dateentry where a.datefield < datefield and SUBSTR(DATEFIELD,1,4) = SUBSTR(a.DATEFIELD,1,4) and datefield in ‘||'(‘||FILTER||’)’||’)’ into v_sum;

Now lets create a report which would basically call this function and pass the presentation variable “datefield” to this function. We will be using the EVALUATE function.


Remember, to encapsulate the presentation variable with IFNULL clause. If you do not, then you will receive some socket errors and strangely your BI Server would stop immediately. I am not sure whether this is a bug but the ifnull clause will make it work. Once this done, lets test it out by building a simple dashboard with the dashboard prompt and the report.


I am not sure whether this can be implemented out of the box using BI EE since it involves a set by set comparison. If any one has any other ideas, feel free to let me know.

Posted in All Posts, OBI EE Plus | 1 Comment »

Oracle BI EE and OID – USER and GROUP – Phase 2

Posted by Venkatakrishnan J on October 12, 2007

In the last post, we saw how to establish OID authentication. But that was the simplest part in the sense that all one would have to do is establish the connection to OID by giving in the proper credentials. In this post, we would see how to go about getting GROUPs from OID for the logged in user. This is also straightforward once we know how to get the groups from OID using the user names. In order to the user related groups one would have to use the DBMS_LDAP package. This package has some methods that would return the groups of users. In our case, what we would do is to call this package from a pipelined function so that we can directly use this function in the repository. Following is the function that i used

ldap_host VARCHAR2(256);
ldap_port PLS_INTEGER;
ldap_user VARCHAR2(256);
ldap_passwd VARCHAR2(256);
ldap_base VARCHAR2(256);
my_session DBMS_LDAP.session;
subscriber_handle DBMS_LDAP_UTL.HANDLE;
sub_type PLS_INTEGER;
subscriber_id VARCHAR2(2000);
group_handle DBMS_LDAP_UTL.HANDLE;
group_id VARCHAR2(2000);
group_type PLS_INTEGER;
user_id VARCHAR2(2000);
user_type PLS_INTEGER;
group_string varchar2(32767);
— Please customize the following variables as needed
ldap_host := ‘’ ;
ldap_port := 389;
ldap_user := ‘cn=orcladmin’;
ldap_passwd := ‘welcome1’;
sub_type := DBMS_LDAP_UTL.TYPE_DN;
subscriber_id := ‘o=acme,dc=com’;
user_type := DBMS_LDAP_UTL.TYPE_DN;
user_id := ‘cn=’||Username||’,cn=users,dc=idc,dc=oracle,dc=com’;
group_type := DBMS_LDAP_UTL.TYPE_DN;
group_id := ‘cn=group1,cn=groups,o=acme,dc=com’;
— Choosing exceptions to be raised by DBMS_LDAP library.
— Connect to the LDAP server
— and obtain and ld session.
my_session := DBMS_LDAP.init(ldap_host,ldap_port);
— Bind to the directory

retval := DBMS_LDAP.simple_bind_s(my_session,
— Create User Handle

retval := DBMS_LDAP_UTL.create_user_handle(user_handle,user_type,user_id);
— Handle Errors
DBMS_OUTPUT.PUT_LINE(‘create_user_handle returns : ‘ || to_char(retval));
— Get Group Membership

my_attrs(1) := ‘cn’;
retval := DBMS_LDAP_UTL.get_group_membership ( my_session,
my_pset_coll );
— Handle Errors
DBMS_OUTPUT.PUT_LINE(‘get_group_membership returns : ‘ || to_char(retval));
IF my_pset_coll.count > 0 THEN
group_string := NULL;
FOR i in my_pset_coll.first .. my_pset_coll.last LOOP
retval := DBMS_LDAP_UTL.get_property_names(my_pset_coll(i),
IF my_property_names.count > 0 THEN
FOR j in my_property_names.first .. my_property_names.last LOOP
retval := DBMS_LDAP_UTL.get_property_values(my_pset_coll(i),
IF my_property_values.COUNT > 0 THEN
FOR k in my_property_values.FIRST..my_property_values.LAST LOOP
DBMS_OUTPUT.PUT_LINE( my_property_names(j) || ‘ : ‘ ||
IF my_property_names(j) = ‘cn’ THEN
PIPE ROW(my_property_values(k));
END IF; — IF my_property_names.count > 0
END IF; — If my_pset_coll.count > 0
— Free handle

— unbind from the directory
retval := DBMS_LDAP.unbind_s(my_session);
— Handle Errors
DBMS_OUTPUT.PUT_LINE(‘unbind_s returns : ‘ || to_char(retval));
— Handle Exceptions
DBMS_OUTPUT.PUT_LINE(‘ Error Message : ‘ || SQLERRM);
DBMS_OUTPUT.PUT_LINE(‘ Exception encountered .. exiting’);

Before creating the above function one would have to create a array type using the below code.

create or replace type array as table of varchar2(32767); 

Now lets test out the above function.



As you see above i have 2 users orcladmin and Test in my OID that are associated with certain groups. The above function would return the data that we needed. Once this is done the next step is to create another session initialization block that would basically call this database function. If you do not have a connection pool for connecting to the schema of the database function create one. In my case i used a connection pool called Authentication.


Name the Initialization block as GroupIB and click on Edit Data Source.


Choose database as the data source and enter the above sql. Then go to Edit Data Target and set Row-Wise Initialization. This would convert the above sql in the datasource into a single row statement.

Just to be sure, test the initialization block (after changing :USER to orcladmin in the data source. You should be getting the output similar to the below picture.


Once this is done click on edit preference and click on Edit Execution Precedence and select the first initialization block (OIDIB) that we created in the last post. Also remeber to check the Used for Authentication check box.


Now go to BI Answers login page and login as orcladmin. In order to see whether our groups have been properly assigned lets create a sample report. In the title view of the report, enter the following




As you see above our groups have got populated properly into the GROUP variable.

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

Oracle BI EE – Using LDAP/OID Authentication

Posted by Venkatakrishnan J on October 10, 2007

One of the very good features of OBI EE is its ability to leverage OID/LDAP authentication. I was trying this one out today and thought i would document it. I would split this into 2 articles. In this article we will see how to setup the OID authentication. In the next article we would see how to pass on group credentials to users from OID. Lets go through the steps one by one.

1.   Open the repository in Online Mode using the Administrator. Go to Manage and click on Security. Click on Action–New–LDAP Server


2.   Enter the Oracle Internet Directory details like hostname and the Base DN. And test the connection.



3.   Right click on the LDAP server and click on import. You should be seeing the users that are under OID.


4.   Once this is done, the next step is to create an initialization block that would basically use the OID server created above and set a system session variable called USER. This USER variable would be used during authentication.
Go to Manage->Variables to open up the variable manager. Click on Action->New->Sesion->Initialization Block


Enter any name, say OID, and click on edit data source. Select the OID/LDAP server that we created in the 1st 3 steps. Then click on edit target and click on new variable. Enter USER as the name of the variable and click ok.


Edit the variable and add the uid as the LDAP variable.


Test the initialization block as orcladmin.


You must see orcladmin username set for the USER variable. If you see that then steps that you have done so far are correct. Remember to set the Required for Authentication check box.


Check in the changes and save the repository. Log into Answers as orcladmin. We should be able to see all the public dashboards.


This is the first step in enabling authentication. The next step is to get the group related info from the OID and assign it to the user which we will see in a later article.

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

Oracle Coherence – Installation

Posted by Venkatakrishnan J on October 9, 2007

If you have read my previous post on coherence, i had mentioned that i was able to install the product and it looked as though my OC4J had regained life as it was bit fast. I believe i was wrong in assuming that it was because of coherence. I went through some documentation and after a couple of emails, this is what i found out

1.   Coherence will be very useful in situations where there is need for sharing/managing huge amounts of data across JVMs. This could be very useful in situations wherein we have a clustered environment and would result in huge performance boosts by having distributed caching.

2.   One can have local caching enabled for single node machines. This could help for standalone as well as server applications. But generally, considered a overkill for a smaller application.

3.   Coherence does not configure itself automatically for custom applications. One would have to include it while building the underlying code to leverage the functionality.

4.   Installing Coherence on an application server does not do anything. It just makes the Application Server Coherence Aware. One would have to build applications from scratch to get the value of coherence.

5.   One can have Coherence to cache Static JSPs or htmls. But again this is not done out of the box. One would have to enable custom codesets to achieve this.

Well, now everything looks clear. Since, Coherence did not cache any of my JSPs, i wanted to find out why my local OC4J was reacting very fast. Looks like, during installation my OC4J was installed by a newer OC4J. Now lets look at a step by step approach to install coherence in Oracle AS 10.1.3.

1.   Download Oracle Coherence and extract the zip file. Once this is done, go the lib folder under the extract from command line. Enter the following.

   java -jar coherence.jar


2.   Start another instance by entering the above command in a new command prompt. Now we should be having 2 instances running. You should be seeing the who? prompt in both the consoles.


3.   The next step is to install the Web Session Management for Oracle AS 10.1.3. Go to the lib folder again from command prompt and enter the following command

      jar -xvf webInstaller.jar web-install/coherence-web.jar

This would create a file called webInstaller.jar under a folder called web-install. Copy this webInstaller.jar to one folder up (lib folder).

4.   Open a command prompt and navigate to the lib folder. Enter the following command.

      java -jar webInstaller.jar D:\oracle\OracleBI\oc4j_bi\j2ee\home\applications\analytics -inspect -server:Oracle/10.1.3.x


You should now have Coherence completely installed on your machine. This is pretty simple and does not warrant a seperate step by step article. But i thought i would write an article anyway for starters who would like to know what Coherence does. The install guide is pretty comprehensive and covers almost everything. One can get the install docs here

Posted in All Posts, Coherence | 1 Comment »