OBIEE Integration with Oracle ADF

This post describes how to Integrate OBIEE (Oracle Business Intelligence Enterprise Edition) with Oracle ADF (Application Development Framework).

Well our goal is to show up the OBIEE reports and dashboards on our ADF jspx pages.

To achieve this first we need BI extension to be downloaded.

  • In Jdeveloper Click on Help -> Check for Updates and download the BI extensions
  • Jdeveloper will restart soon after extensions get downloaded

bi0

Now create a New ADF Application and while creating the project add the BI ADF View Components technology  to the project.

bi1

Expand Application Resources -> Right click on Connections -> New Connection -> BI Presentation Services.

bi2

Enter the OBIEE host and port details

bi3

Give the Username and Password of OBIEE

bi4

Click on Test Connection to check the status of connection

bi5

Jdeveloper fetches the OBIEE catalog content and displays as presentation services which can be used ADF pages.

bi6

Now we can drag and drop the required report or Entire Dashboard to the jspx page.  Here i’m trying to show the dashboard first.

You can give any default values to the Dashboard prompts or you can simply click on OK and continue to make the dashboard to work exactly like in OBIEE.

bi7

You can give the height and width for the dashboard.

bi8

After adding the dashboard to the page the following code gets generated.

bi9

Run the page and you can see the OBIEE Dashboard in the page like shown below

bi10

The Reports will look like shown below

bi11

When you deploy your EAR file to the server the graphs might not shown up which are showing on local server if you not added the required BI libraries to your project.

To add the BI libraries Select your project right click and choose Project Properties -> Libraries and Classpath -> Add library -> Add the BI Extensions

bi12

Generate EAR again and deploy to server.

Issue 1: If you are not able to see the BI presentation Services for a new connection then check your project has required BI ADF view components or not. To add them Simply right click on you project -> Project Properties -> Technology Scope -> Add the  BI ADF view components to the project.

Issue 2: Some times you might not be able to drag and drop the contents to the jspx page. This is mainly because you might be added the BI ADF  View Components after creating the project. This will some times won’t add the required bi related code to the web.xml.

So create a new project and add the BI ADF view Components at the time of creating project.

(or) If you want to continue in the same project then add the below given code to the web.xml


<servlet>
<servlet-name>biregionservlet</servlet-name>
<servlet-class>oracle.bi.presentation.servlet.BIRegionServlet</servlet-class>
</servlet>

<servlet-mapping>
<servlet-name>biregionservlet</servlet-name>
<url-pattern>/BIProxy/*</url-pattern>
</servlet-mapping>

<listener>

<listener-class>oracle.bi.presentation.listener.BIADFServletSessionListener</listener-class>
</listener>

And also add the following code to the jsp root in jspx page.


xmlns:adfbi="http://xmlns.oracle.com/adf/bi/report

Now you’ll be able to drag and drop the BI contents to the jspx page.

Issue 3: Unable to load the resources error when trying to fetch the OBIEE maps. Here you have to understand is that ADF tries to fetch only the data from obiee not the map or graphs. By default it has dvt components so it displays the graphs. But map needs base data which it tries to fetch from adf server not from OBIEE server.

Resolution: So in order to fetch OBIEE maps in adf page you have to take the configured mapviewer.war  file from the OBIEE server and then deploy it to ADF server. Then it’ll fetch the base map data from the adf server and the business data from OBIEE and displays it on the map.

Hope you enjoyed the post!!

venn diagrams in OBIEE using google api

Recently got a requirement to represent  the data in the form of ‘venn diagram’. Unfortunately BI doesn’t have that feature… so tried the alternate ways and found that its possible with using of google api.  Below given graph represents the customers count in each category which is being generated using google api.

Red color, Green color, Blue color circles indicates no.of customers under ‘bad sales’, ‘bad Service’, ‘bad Network’ categories respectively.
The common part of Red&Green circles indicates no.of customers under both ‘bad Sales’ & ‘bad Service’ categories.
The common part of Red&Blue circles indicates no.of customers under both ‘bad Sales’ & ‘bad Network’ categories.
The common part of Blue&Green circles indicates no.of customers under both ‘bad Network’ & ‘bad Service’ categories.
The common part of Red,Green&Blue circles indicates no.of customers under all ‘bad Sales’, ‘bad Service’ & ‘bad Network’ categories

venn1

Procedure to create venn diagram in OBI using google api:

1) Create the table in a report with the required columns.

Note: while dragging the columns in Criteria section, drag the columns in an order. (don’t do move operation between the columns  in Results view)

2) Create a narrative view.

venn2

3) Paste the following code in the Narrative input text box.

<img src=”” id=”venn_chart@9″/>  <!– creates an unique img tag –>
<script type=”text/javascript”>
var chartURL = “http://chart.apis.google.com/chart?cht=v&chs=350×230&chd=t:@1,@2,@3,@4,@5,@6,@7&chtt=@{cb96d660824a87f7a}&chdl=Sales (@1)|Service (@2)|Network (@3) &chdlp=b” ;
/* cht = chart type
   chs = chart size
  chd = chart data
  chtt = chart title
  chdl = chart legend
  chdlp= chart legend position
  more info http://code.google.com/intl/nl/apis/chart/ *//* get the chart */
document.getElementById(‘venn_chart@9’).src = chartURL;
</script>

 
venn3
 
 
-> The size of the graph, data & all the information required to generate the graph is given in the  ‘chartURL’ variable which is given as source to the image tag which will generate the required venn diagram.
 

URL explanation:

chs=350×230 -> represents width & height of the graph. (chart size)

chd=t:@1,@2,@3,@4,@5,@6,@7  -> represents, take the data from the table whose column ids are 1,2,3,4,5,6,7. The order is according to the view in Criteria Section. (here ‘@1’ contains the data present in column 1)
chdl=Sales (@1)|Service (@2)|Network (@3) &chdlp=b -> represents what legend to display for the three colors. Here in this example I’m giving the name to each color circle along with the data which it represents.
 

That’s it !! Now you’ll be able to see the the data in the form of Venn diagram.

 

Issue with Data which is not being updated in Reports

In OBIEE if you change any data in Data base while creating Dashboards/Reports, some times it won’t show you the latest data which is in database because of OBIEE internal Server Cache.  To avoid this problem while creating the Report Click on Advanced tab and follow the steps given below.

1) Check the box Bypass Oracle BI Presentation Services Cache

UpdateData

2) In the Prefix field insert the given below statement and click on Apply SQL button

SET VARIABLE DISABLE_CACHE_HIT=1;

UpdateData1

Then click “Apply SQL” button.

-> This method will fetch the data directly from the database. So now you can see the latest data in your reports.

Note: In some cases this method also won’t work…then you can go for another option clearing the presentation Cache instead of executing queries directly from Database which can be done by using two methods

(1) Clearing Presentation Cache from OBI Server

(2) Clearing Presentation Cache from Command prompt

Method1:

-> Click on Administration link which is located at the top right section of the page.

-> Click on Issue SQL link which is located at the bottom left of the page under Maintenance and Troubleshooting section

->  Enter the following given SQL Statement in the SQL Statement section given and click on Issue SQL button

Command: call SAPurgeAllCache();

cache1

After you will be able to see the Successful message.

cache2