OBIEE Popup / Tooltip / Mouse Hover effect


There are some thread/blogs already talks about how to get the OBIEE popup/tooltip effect . As some of the threads are not easy to understand and for some blogs the popup/tooltip is not working for me I want to write my own.

As we know this feature is cool but unfortunately this feature is not available as out of box with OBIEE 11g or 12c.

This leverages a JS called Tipped JS and leveraging the JQuery and Javascript capabilities out there.

So this is the requirement :

We have an Event Main report which shows the aggregated metrics for the number of attendees in that event and we want to mouse hover on Event name and want to see the details as in popup/mouse hover/tooltip report exactly like below:

1-16-2018 2-44-37 PM

So I need to create 2 different reports. One is Main report and other is detail. Idea is every time you hover on Event Name in Main report it is going to run additional query to run Event detail report. So for OBIEE each hover is new query . This is very cool and very powerful to run the detail report on each mouse hover. And at the same time DB cost is associated for that. To get a really nice cool hover effect the DB needs to return the results very fast.

So you need custom JS to give that tooltip effect from here : http://www.tippedjs.com/ and will use a powerful OBIEE feature which is HTML expression wrapped around to insert JS , JQuery , CSS , HTML and OBIEE GO URL code  to format the look and feel.

Pushed that required JS library to OBIEE and restart analyticsRes module from console. So in my case I copied the files which packaged inside tipped JS here and update analyticsRes / Stop and start that deployment module :  [MiddleWare_Home]/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/tipped-3.2.0.1-demo

drwxrwxr-x 5 orabi orabi 4096 Jan 8 2014 js
drwxrwxr-x 3 orabi orabi 4096 Jan 8 2014 example
drwxrwxr-x 3 orabi orabi 4096 Jan 8 2014 css
-rw-r–r– 1 orabi orabi 93068 Aug 5 2016 jquery-1.9.0.min.js

Next, you need to refer those library as static text in your main report and add below:just replace &lt; with < and &gt; with >

&lt ; script type=”text/javascript” src=”http://code.jquery.com/jquery-1.10.2.min.js&#8221; &gt; &lt ; /script &gt;

&lt ;script type=”text/javascript” src=”http://obieenode:9704/analyticsRes/tipped-3.2.0.1-demo/js/tipped/tipped.js”&gt; &lt ; /script &gt;

&lt ;link rel=”stylesheet” type=”text/css” href=”http://obieenode:9704/analyticsRes/tipped-3.2.0.1-demo/css/tipped/tipped.css”/ &gt;

And add below as column expression in the column in your report : In my case I have added it under “Event Name” expression : (ignore the error after hitting ok) . Refer the screenshot below incase text is garbled with wordpress formatting.

‘&lt;div id=”showme’ || TRIM(CAST(“D_EVENTS_DXP”.”EVENT_ID” AS CHAR(10))) || ‘”&gt;’ || D_EVENTS_DXP.EVENT_NAME || ‘&lt;/div&gt;’ || ‘&lt;script type=”text/javascript”&gt; jQuery(document).ready(function($) {Tipped.create(“#showme’ || TRIM(CAST(“D_EVENTS_DXP”.”EVENT_ID” as CHAR(10))) ||'”, “saw.dll?Go&path=%2Fshared%2FPOC%2FEvent_Description&Action=extract&p0=1&p1=eq&p2=%22D_EVENTS_DXP%22.%22EVENT_ID%22&p3=’ || TRIM(CAST(“D_EVENTS_DXP”.”EVENT_ID” AS CHAR(10))) || ‘”, {ajax: true, skin:”blue”, hook: “topright”, border: { size:1}, afterUpdate: function() {} } ); }); &lt;/script&gt;’

1-16-2018 3-31-46 PM

And mark HTML in Data format:

1-16-2018 3-39-00 PM

So here is the explanation of doing this:

‘showme’ is just a div placeholder.

Event ID is the value that I want to pass to GO URL detail report which will show content on hover as popup/tooltip

Event Name is where I want my mouse hover to be placed

I call JQuery ready function to execute actions once the page is completely loaded

I call Event_Description detail report and user parameter called Extract to show the data immediately while passing EVENT_ID as another parameter. A very common mistake here is CASTING and TRIM . I found that your number(EVENT_ID value) doesn’t work well with character (EVENT_NAME value) so I had to user CAST and TRIM . If you don’t do that your popup will NOT work (at least it my case) . This is different than what is other blogger already wrote.

Next is AJAX call for changing the skin (the background color what you want). In my case its blue.

So that’s it .  Sweet and simple and easy . This solution should work in OBIEE 12c as well. Be very careful when you want to implement it as this can cause huge DB performance impact if you hover a lot across table grid.

Feel free to leave me message if I can help.

 

 

 

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 AutoAlert.java 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.

5.png

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

++++++++++++++++++++++++++++++++++++++++++++++++++++

package oracle.bi.webservices;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.HashMap;

import webservices.bi.oracle.v6.Logon;
import webservices.bi.oracle.v6.LogonResult;
import webservices.bi.oracle.v6.SAWSessionServiceSoapPortClient;
import webservices.bi.oracle.v6.IBotServiceSoapPortClient;
import webservices.bi.oracle.v6.ItemInfo;
import webservices.bi.oracle.v6.Subscribe;
import webservices.bi.oracle.v6.SubscribeResult;

public class AutoAlert
{
public AutoAlert() {
super();
}

public static void main(String[] args) {
try
{
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 );
data.put(username,password);

Logon my_logon = new Logon();
LogonResult logResult = new LogonResult();
my_logon.setName(username);
my_logon.setPassword(password);
String log_sessid = new String();
SAWSessionServiceSoapPortClient new_soap_port_client = new SAWSessionServiceSoapPortClient();
new_soap_port_client.setEndpoint(“http://<obiee_host&gt;:9704/analytics/saw.dll?SoapImpl=nQSessionService”);
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();
ibot_soap_access.setEndpoint(“http://<obiee_host&gt;:9704/analytics/saw.dll?SoapImpl=ibotService”);
Subscribe sub=new Subscribe();
SubscribeResult subresult=new SubscribeResult();
sub.setPath(“/shared/Agents/Agency360 Alerts/Pace – Revenue Penetration Rank Decline”);
sub.setSessionID(log_sessid);
sub.setCustomizationXml(“”);
subresult = ibot_soap_access.subscribe(sub);
System.out.println(“Subscribing to Alert: “+sub.getPath());
}
}
catch (java.lang.Exception e) {
e.printStackTrace();
}
}
}

++++++++++++++++++++++++++++++++++++++++++++++++++++

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:

8

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…

D

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 11.1.1.3 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 11.1.1.6 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 11.1.1.3 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: 

https://bi.dev.com/analytics/saw.dll?Go&path=%2Fusers%2Fpdebashi%2FTest-Column_Header_Freeze

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)

https://bi.dev.com/analytics/saw.dll?Go&path=%2Fusers%2Fpdebashi%2FTest-Column_Header_Freeze&Action=Navigate&Viewname=compoundView!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 :

<SeriesItems>
<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”/>
</SeriesItems>

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 : http://www.oracle.com/webapps/dialogue/ns/dlgwelcome.jsp?p_ext=Y&p_dlg_id=11226121&src=7346567&Act=6

And view the Demo here:

BI Mobile sales demo – http://www.youtube.com/watch?v=fdbazqdXBeE
Financial Analytics Mobile demo – http://www.youtube.com/watch?v=qEBLFgwEr9s

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