OBIEE Agents/Alert Subscription using Java Webservice

After long time I am publishing a interesting post. I could do that long time back but time was not permitting to do so.

So let me first explain the requirement : We had 10g for couple of years before we transitioned to OBIEE 11g. During migration process we lost Subscribers who had subscription to individual Agents/iBOTs. There are several reason why we had lost this, because one of our BI product Catalog is so huge with millions of tiny files (because it has not been cleaned for long time plus it had past 5 years of junk users/folders which never deleted or cleaned) moving those files with archive/un-archive and then OBIEE 10g to 11g upgrade was never successful. So we had to plan for either manually do Subscription for those Alerts or Automatically do that.

Manual was not an option as there were thousand of Alert subscribers who subscribed to individual agents. So that is why the solution come into play. I was never a Java coder but tried my best to code it in below phases:

  1. Install JDeveloper 11g software which will be our UI and java code compiler/execution interface
  2. Create a Application and then Create a Project.
  3. Under one Project add New -) Web Services -) Web Service Proxy
  4. Always select JAX-RPC Style coding
  5. Then Add your WSDL web link which is OBIEE WSDL URL (For e.g : if your OBIEE URL is : http://(host):9704/analytics/saw.dll?Dashboard – your WSDL URL will be http://(host):9704/analytics/saw.dll?wsdl
  6. Then Select the corresponding webservice. In this case we need “SAWSessionService” and “IBotService”

The step by step are in the Slideshow:

This slideshow requires JavaScript.

So finally you see how two Webservice has been added. Now you need the code which is and what it does step by step is :

  1. We had a lookup table with all username and password under csv file which is A4.csv here. This csv is has list of users for which we want the Alert subscription automatically for one specific Agents
  2. The code with parse the csv ,take user/password from each row
  3. It will create new OBIEE session for each row and then subscribe that user with specific agent in that OBIEE session itself
  4. Finally once you edit that Agent in OBIEE you will all users subscribed automatically.

For this java file you need to create it under Project as New -) Java -) Java Class and named it as AutoAlert.


Then paste the code as below: Also replace the OBIEE URL with your env’s URL.



import java.util.HashMap;


public class AutoAlert
public AutoAlert() {

public static void main(String[] args) {
BufferedReader br = new BufferedReader(new FileReader(“D:\\A4.csv”));
String sCurrentLine;
String first = “Username is: “;
String second = “Password is: “;
HashMap data = new HashMap() ;
while ((sCurrentLine = br.readLine()) != null) {
String[] information = sCurrentLine.split(“,”);
String username = information[0];
String password = information[1];
System.out.println(“Username: ” +username + ” / ” + “Password: ” + password );

Logon my_logon = new Logon();
LogonResult logResult = new LogonResult();
String log_sessid = new String();
SAWSessionServiceSoapPortClient new_soap_port_client = new SAWSessionServiceSoapPortClient();
logResult = new_soap_port_client.logon(my_logon);
log_sessid = logResult.getSessionID();
System.out.println(“Logon Session ID: “+log_sessid);

IBotServiceSoapPortClient ibot_soap_access = new IBotServiceSoapPortClient();
Subscribe sub=new Subscribe();
SubscribeResult subresult=new SubscribeResult();
sub.setPath(“/shared/Agents/Agency360 Alerts/Pace – Revenue Penetration Rank Decline”);
subresult = ibot_soap_access.subscribe(sub);
System.out.println(“Subscribing to Alert: “+sub.getPath());
catch (java.lang.Exception e) {


Before you execute this 2 changes required to be done in OBIEE end:

  1. Login as Admin user and go to “Manage Catalog Groups” -) Under Presentation Admin add “Authenticated User” . (This is required because I am not using Impersonate method for my Java webservice call)
  2. Also under “Manage Privilege” add “Presentation Admin” under SOAP  option.

3. Now once you run the project you will see below : with each login each session has been initiated and subscribe to Agents: The failure below because user password was expired

WebService error 2

4. Also if step 1 and 2 is not configured proper way you will see “Insufficient Privilege” issue.

WebService error 3.jpg

WebService error 4

5. Now when this started executing just monitor the Session here and you will see Webservice call :

Manage Sessions while running Alerts Sunscription

6. Finally when everything is done. You will see users are subscribed to Agent as below:


7. If you want to run this Subscription for multiple Agents you just need to code such a way and change that Alert path in below method call :  sub.setPath(“/shared/Agents/Agency360 Alerts/Pace – Revenue Penetration Rank Decline”);

8. So you are all set and enjoy writing Java codes to do OBIEE stuff 😉 😀

Fixing Pivoted Graph Conditional Formatting – OBIEE 11g

OBIEE 11g upgrade has a lot of issues and among couple of them common is on Conditional Formatting . This emphasis that even if the Catalog upgrade is successful there are some of the problem still persists in XML conversion engine of the Upgrade Assistant(UA) leading though several catalog defects due to malformed XML . This is another example as my upgraded 11g charts failing to show its conditional formatting which is was working perfectly in 10g ….

Lets deep dive on whats the issue …

Below is the conditional format definition in 11g which is aligned with 10g:

So ideally it should show Chart bars (which is generated from Combined request Pivoted Graph) and see the below comparison:

To understand the reason of why 11g chart not showing the conditional format I went through the full XML from Advanced tab of 11g and find the below discrepancies:

This is malformed XML generated by UA during the upgrade of 10g code .If we look into 10g Advanced XML this is actually decimal but for some reason UA failed to put correct literal against it .

So the fix is replace all the instances of string “untypedLiteral” with “decimal’ like below:

Now after applying the XML the 11g chart output like :

And voila …. the problem is fixed … There could be another workaround to copy your 10g entire XML to 11g and yes it should work     🙂 🙂

Enjoy ..till next time…


Fixing the BAD XML Instance Issue – OBIEE 11g

“The current xml is invalid with the following errors: Bad xml instance!” is very unusual error but when this has been encountered it is become frustrating as it sounds the entire report can’t be saved !

I remember last time I had faced the similar issue in version while I was building a new report from scratch . The error enforced me not to even edit the reports so I neither I can move to criteria nor I can save this . That was weird !  The only option left was to edit the XML using Catalog manager but until and unless you know where the problem lies you can’t fix it right ?

Now recently I got similar kind of issue in one of our upgraded report which is a Combined request report and when I tried to edit the Criteria Analysis properties Interaction tab to remove Sort column of un-check any other options it throws me below error during the save:

Okay … so the error was at the very last line of the displayed XML as below:

Line:814, Col:61, Element ‘criteria’ is not valid for content model: ‘((criteria,interactionOptions,views,prompts,listOptions),segmentOptions)’

so the bug is around the green circled tags !! Reason is whenever you un-check some options under “Analysis-Properties” it will throw errors !! Probably this is some problem with the up-graded code in 11g from 10g as I tried to replicate the same creating a new reports from scratch which was pretty cool and removing any interaction works perfectly okay in the dashboard report .

After spending some times the reason of the issue found with the fix . I have customized the location for the below tag from Advanced XML :

<saw:interactionOptions drill=”true” movecolumns=”true” sortcolumns=”false” addremovevalues=”true” groupoperations=”true” calcitemoperations=”true” showhidesubtotal=”true” showhiderunningsum=”true” inclexclcolumns=”true”/>

Edited the XML and moved it from beforesection to the end of all. As it is combined request hence several would be present but placed it after all of them.

 After that apply the changes in Advanced XML and voila …. your problem fixed !  Note that this could be the one of incident with BAD XML error and probably you can found it in several other places with no relation to my above fix note . This those cases you need to dig down into more details of the Advanced XML generated and fix those issues as applicable for your case .

Bottom line is same . Dig into the XML details to find out the crappy code and the fix it ! …

So that’s it …. till next time… Happy weekend 🙂


OBIEE 11g/10g Column Header Freeze

I thought to post this thread long time back during version but just waited for 11g new release .5 and upcoming edition assuming Oracle might include this demanding features soon and still surprised there is no OOB feature included while we are in .6 now.

I know the couple of fellow bloggers already tried that and everybody could have different approach . So lets put what I had experimented in last year in .3 release which is still valid in .6 . And this approach and implementation still valid in 10g.

Its simple to have column header freeze adding some scripting with embedded content . I took a simple dimension and simple fact to create one Tabular report and one Pivot report in two different tab where I have applied same logic but just want to show that the feature still feasible in both view.

So lets see how it will look like after Tabular column freeze (I would rather say it is kind of floating column header attributes)

Now I have scrolled at the right pane and watch the year attributes.  Now you can see that the column header retains.

Now see the implementation:

I have added Text and Embedded content in the dashboard page:

The text properties(HTML Markup checked): I can’t put it like text for your copy paste as the tag is restricted to use in wordpress and sorry for that 😦 Hence click on the image to get the detail view.

Embedded Content URL:

So I have created a simple tabular report with the above columns and saved it under my folder and referred that in the URL .

That’s it and you are done …

Now if you try to implement the same, you will see some alignment issue and in that case you need to adjust the Width and height and other parameters of HTML content to get the cropped column list  . Note that in above implementation I just remove my title view from report .

Though the above design has a lot of other restrictions (Downloading , Column sort ) but there could be possible workaround for that. At-least you can get something which is better than nothing. 😀

Similar way I did it in Pivot view :

In this case my Embedded URL is different: (note the Viewname param at the end which only pull the compoundView2 which is my pivot table)!2

Refer this same URL  under the HTML content defined in above image as Text HTML Markup properties.

Let the soul of the above approach rest in peace once Oracle implements this as OOB functionality in upcoming releases !!!  🙂 

And we expect this to be sooner than later …. Catch you again ..till then …. 

OBIEE 11g Graphical Color Scheme like 10g

Salut à tous…. Joyeux Noël…. Wish you Merry X-Mas and good time with your near and dear ones … So I am back here with some new geeks … 🙂 Excuse me if somebody already know it !… and please let other to learn …. !

OBIEE 11g Graph colors are quite jazzy comparing to 10g… this is one of the recent feedback we received from clients in our post up-gradation review of codes .So one of the obvious requirement came into picture is that, how do we retain the 1og Graphical color schemes in 11g. This could be easily done by playing across the XML codes for color definition .

Lets do it …

As you might already be aware of that 10g Color contents (hex-codes) definition are stored in the palette.cxml file. So what you need to do is only copying the color Hexcodes from 10g files and put those under the file located : “..\Middleware\Oracle_BI1\bifoundation\web\msgdb\s_blafp\viewui\chart\dvt-graph-skin.xml” having the below sample syntax  (do the same for other style folders like s_FusionFX  if you need it)

So below content copied to the above xml and put this between the first and last tag of  SeriesSlice :

<Series id=”0″ color=”#A4D5F9″ borderColor=”#A4D5F9″/>
<Series id=”1″ color=”#5592D0″ borderColor=”#5592D0″/>
<Series id=”2″ color=”#EFD084″ borderColor=”#EFD084″/>
<Series id=”3″ color=”#687FA1″ borderColor=”#687FA1″/>
<Series id=”4″ color=”#CFC4E5″ borderColor=”#CFC4E5″/>
<Series id=”5″ color=”#8D9EB7″ borderColor=”#8D9EB7″/>
<Series id=”6″ color=”#5898AA” borderColor=”#5898AA”/>
<Series id=”7″ color=”#CEE4EA” borderColor=”#CEE4EA”/>
<Series id=”8″ color=”#90B5D1″ borderColor=”#90B5D1″/>
<Series id=”9″ color=”#F6E795″ borderColor=”#F6E795″/>
<Series id=”10″ color=”#C7C1C1″ borderColor=”#C7C1C1″/>
<Series id=”11″ color=”#85A8EE” borderColor=”#85A8EE”/>

Make sure all the services restarted before the change to be effective.


See below difference before applying the XML(11g Original) and after applying (aligned with 10g color).

N.B:- It has been observed that though the 10g color scheme applied for 11g but it is not at per with existing 10g colors and there are slight deviations in terms of colors which is not eye-catching but somehow not matching exactly due to the difference of the rendering engine of 10g and 11g.

A worth to note is that, in our implementation we have seen that in several charts the color change is not propagated and hence it seems that the color scheme is not applied properly as per the defined XML. However if you edit the chart and remove any measure and then drag the same measure again we see that the color starts changing as per XML . The behavior seems to be unpredictable for us. Assumption is that ,it might happened due to some internal cache specially for the chart engine here.  But the problem is not observed for any new reports created in answers in 11g and it starts reflecting the new colors scheme from very beginning.

Oracle BI Mobile App for iOS devices

Welcome back after long days … Just tied up with couple of  OBIEE 10g to 11g up-gradation projects nowadays and manage to have any time to Blog considering lot of other extra-curricular activities 🙂

Anyway today my experiment was with Oracle BI Mobile iPhone/iPad App. I have downloaded the app from AppStore couple of days ago but unfortunately have not receive any server details to see how the App looks like in my iPhone 4 .However recently Oracle launched this Demo Application and you need top register there to get the PDF having the details of the servers (Thanks to Mr. Barry for sending me the details).

Register Here and then download the App :

And view the Demo here:

BI Mobile sales demo –
Financial Analytics Mobile demo –

I believe underline iPhone App is based on OBIEE 11g as I can see the 11g interface only while connecting from my PC.

Here are the couple of screenshots first, taken in my iPhone, to get the flavor how it looks like. Later I will explain my comparative findings between 11g Desktop based and 11g Mobile BI App.

The text colored in “Green” is short briefing against each screen-shots:

+++++++++++++++++++++++++++ My findings +++++++++++++++++++++++++

1) Due to the Display limitation of iPhone I can see most of the Horizontal reports/Charts came in vertical view.But always you can switch to “Original Layout” from the actual default “Mobile Layout” to see the change report components layout across dashboard pages. I don’t know how it looks like in iPad by default… haven’t tried yet 😦

2) The Zoom-in and out features by playing with two fingers sometime doesn’t fit the display and causing some display out of the screen which obviously require to scroll by fingers both Vertically and Horizontally.

3) Online App via PC Browsers having “Graph Slider” while in iPhone it is drop-down. Not sure if iOS having different compilation process

4) No download to excel/pdf/ppt features yet.

5) Spatial Graph display and drill down works great .

6) All kind of Navigation and Drill-down feature is aligned. However Master-Detail event tracking has not been seen so far as per this application . Not sure whether this works or not !

That’s all ….

Narrative view issue with OBIEE 11g

Currently I was looking into Narrative view syntax for OBIEE 11g and it seems the older HTML tag based syntax is obsolete and not work properly as expected .Therefore ,  below syntax is not functional anymore with OBIEE 11g however it works perfectly fine with OBIEE release :

<table width=”100%” cellpadding=”3″>
<td width=”60%” align=”left” valign=”top”><b><font size=”2″>Prescription Sales</font></b></td>
<td width=”40%”align=”left” valign=”top”><b><font size=”2″ color=”green”><u>@2</u></font></b></td>

However following syntax works fine:

[b]Hello World[/b] = [b]@2[/b]

In order to get the above HTML tag based example working fine we need to do two things:

1) Set HardenXSS to false in the instanceconfig.xml file and restart the service. Here is a snippet of a working instanceconfig.xml with this tag:

<!–This Configuration setting is managed by Oracle Business Intelligence
Enterprise Manager–>
<!–HardenXSS is not managed by Enterprise manager–>

2) Restart the Presentation server after making changes to instanceconfig.xml.

3) Check the “Contains HTML markup” checkbox in the Narative view.