Oracle BI EE 10.1.3.3/2 – Integration into External Applications/Portals

This question generally comes up in almost all the customer calls that i go into. So, i thought i would blog about all the possible options for integrating BI EE into any external application. Though i have talked about the individual options before i thought it would make more sense if one has a complete list of all the possible options(let me know if you find more). The various possible options are listed below

1.   Integration using GO URL and URL Parameters.

2.   Integration using SOAP APIs(BI EE Web Services).

3.   Integration by deploying BI EE JSR-168 compliant portlets to a WSRP standard Portal

4.   Integration by calling BI Server metadata using ODBC from other tool sets.

Now lets discuss the advantages and disadvantages of using all the above 4 options.

1.   GO URL is the easiest and best approach for integrating BI EE into any kind of a web environment. Currently, BI EE is integrated into Siebel CRM using this methodology. One of the major advantages of using this approach is that it gives the end user a lot of flexibility. One does not lose any kind of in-built features of BI EE and at the same time one call the reports and the dashboards from an application using a single URL. Parameters can be passed back and forth between the application and BI EE since both have HTML based UI. So, if you are one of those users who want to integrate some BI EE reports into one of your own custom application i would recommend you to evaluate this approach first before going into any of the other 3 approaches. For the list of all known and unknown parameters check my blog entries here and here.

2.   SOAP APIs or the BI EE Web Services also provide very good integration capabilities. But just understand that these are APIs that expose the nuts and bolts of OBI EE. In most scenarios, these might not be useful at all. But this can be useful in situations wherein you would like to leverage some of the BI EE reports and would like to do something in your application based on the output of a report. For example, assume that you have a web application that creates and manages workflows. Now, assume that an end user creates a workflow request asking for 5 days of vacation. This workflow request would be sent to the manager if he has more than 5 days of vacation balance left for approval else it will go one more level up for approval. Whether the end user has enough vacation is determined from a BI EE report that can accept parameters. In such a case, WSDL SOAP APIs can be very useful. Also, it can be useful in situations wherein you would like to synchronise user authentication in your custom application and BI EE. For more details check my blog entry here.

3.   JSR-168 portlets – This method of integration is useful in those situations if you have a standard portal that is WSRP compliant. In such a case you can deploy these portlets directly into a WSRP container of the portal and can view BI EE reports directly from your portal (for example Oracle Portal). But i would recommend going through GO URL approach first and see whether it is applicable in your situation. If it is not only then should this approach be used. For more details on how to deploy these portlets check my blog entry here

4.   Using BI Server ODBC – This is not an integration methodology per se. But again i have seen users using this approach for leveraging BI Server Metadata. So, if you have any other reporting tool that can create reports using a ODBC connection then and if you want to leverage BI Server metadata then this option is for you.

Oracle BI EE 10.1.3.3/2 – Integration into External Applications/Portals

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

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 http://10.176.246.99:9704/mapviewer/fsmc/tutorial/samples/mapParameter.html?Region=@{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.

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

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

http://localhost:9704/analytics/saw.dll?GO&NQUser=Administrator&NQPassword=Administrator&Path=/shared/Paint+Demo/Mapviewer/City+Sales&Options=md&Action=Navigate&P0=1&P1=eq&P2=”Dimension%20-%20Customer”.CITY&P3=”OAKLAND”

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.setQueryParameters(marrayPara);
themebasedfoi.setBringToTopOnMouseOver(true);
mapview.addThemeBasedFOI(themebasedfoi);
}
else
{
themebasedfoi.setQueryParameters(marrayPara);
themebasedfoi.setBringToTopOnMouseOver(true);
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 =
“http://vejanaki-pc:9704/analytics/saw.dll?

GO&NQUser=Administrator&NQPassword=Administrator&Path=/shared/Paint+Demo/Mapviewer/City+Sales&Options=md&Action=Navigate&P0=1&P1=eq&P2=\”Dimension%20-%

20Customer\”.CITY&P3=”+foi.attrs[1];

objWin = window.open(drillURL, “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 window.open 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.

      

      

Integrating Oracle OBIEE Content Using GO URL Syntax

This blog discusses various techniques for integrating Oracle OBIEE content into external web content using the GO URL syntax available in OBIEE. Note that OBIEE should be in SSO with the external app otherwise authentication info. is also required as part of this URL syntax. Most of this is also documented in OBIEE Web administrator guide, just some additional examples and few additional syntax are provided here and also putting them all at one place

Integrating Analytics Reports using Go URL syntax
Analytics supports a versatile “Go URL” syntax to incorporate Analytics content into external portals.

• Go URL with parameters can be posted as a Form or
• Issue it as a URL with parameters.

If you are issuing parameters as part of a URL, they need to be escaped properly.

Calling GO URL

When calling from within Analytics Dashboard or HTML view simply use the URL

saw.dll?GO

When calling from another screen from the same Web server then use the syntax

/analytics/saw.dll?Go

When calling from a different Web server the use full syntax

http://server_name_or_ip_address/Analytics/saw.dll?Go

Authentication with the GO URL

It is assumed that Analytics Web site has Single Sign On (SSO) enabled within corporate web-sites.Single Sign On authentication information is not required as a part of the “Go URL”. If Single Sign On is not enabled then additional authentication parameters must be supplied with the Go URL

http://server_name_or_ip_address/Analytics/saw.dll?Go&NQuser=xxx&NQPassword=xxx

Structure of the Go URL

Basic structure of the Go URL is as follows:

saw.dll?Go&Path=/Shared/Training/ParameterReport

Here, the path is the catalog path for the Analytics report ParameterReport. This basic URL syntax displays the default result view for the report in standard style.

There are number of optional arguments to this URL syntax

Displaying Report Results in different format

Display ‘Modify’, Download’ ‘Print’, ‘Refresh’ options along with the report

saw.dll?Go&Path=/Shared/Training/ParameterReport&Options=mdr

Print report in HTML format
saw.dll?Go&Path=/Shared/Training/ParameterReport&Action=print

Print in xx format, where xx=PDF,Excel, mht or xml
saw.dll?Go&Path=/Shared/Training/ParameterReport&Action=print&format=pdf
saw.dll?Go&Path=/Shared/Training/ParameterReport&Action=print&format=excel
saw.dll?Go&Path=/Shared/Training/ParameterReport&Action=print&format=mht
saw.dll?Go&Path=/Shared/Training/ParameterReport&Action=print&format=xml

Download report directly in the xx Format where xx= Excel, csv or mht
saw.dll?Go&Path=/Shared/Training/ParameterReport&Action=download&format=csv
saw.dll?Go&Path=/Shared/Training/ParameterReport&Action=download&format=excel
saw.dll?Go&Path=/Shared/Training/ParameterReport&Action=download&format=mht

Note that download does not support filters to be passed from the GO URL

Showing specific view of the report
saw.dll?Go& Path=/Shared/Training/ParameterReport &ViewName=pivot

Displaying all the records in a table view
saw.dll?Go&Path=/Shared/Training/ParameterReport &Action=Scroll&P5=-1&ViewID=go~Table

Displaying Dashboard or Dashboard Pages

Syntax for displaying entire Dashboard or specific Dashboard pages URL syntax is
saw.dll?Dashboard&PortalPath=/shared/Training/_Portal/Training

Displaying specific Dashboard page
saw.dll?Dashboard&PortalPath=/shared/Training/_Portal/Training&Page=Sales%20by%20Region

Displaying specific Dashboard page in PDF format
saw.dll?Dashboard&PortalPath=/shared/Training/_Portal/Training&Page=Sales%20by%20Region&Action=print&format=pdf

Passing Parameters to the Report using Go URL optional argument

The Go URL can also be used to pass context such as filters to a destination request. This is done by adding additional parameters to the call. You need to make sure that any columns you are passing are set up in the destination with Is Prompted filters, or specific default filters. Up to 6 parameters can be passed to the target report

New syntax for GO URL in addition passing the parameters only supported in OBIEE 10.1.3.2 or above

New syntax for Go URL in Analytics. You can now pass as many parameters and values as you want
note the syntaxt &col1=&val1=

http://webserver/analytics/saw.dll?Go&Path=%2Fshared%2FSupplier%2FRegionDollars&Action=Navigate&col1=Periods.%22Year%22&val1=%221999%22&op1=eq&nquser=Administrator&nqpassword=Administrator

Also an operator can be included in the URL, default is ‘eq’ if op1 etc. are not included
http://webserver /analytics/saw.dll?Go&Path=%2Fshared%2FSupplier%2FRegionDollars&Action=Navigate&col1=Periods.%22Year%22&val1=%221998%22&op1=gt&nquser=Administrator&nqpassword=Administrator

The syntax is pretty flexible and can include as many parameters and operators there is no limit

http://webserver/analytics/saw.dll?Dashboard&PortalPath=%2Fusers%2Fadministrator%2F_portal&Page=TestPrompts&Action=Navigate&col1=Markets.Region&val1=%22EASTERN%20REGION%22&col2=Markets.District&val2=%22PHILADELPHIA%20DISTRICT%22&col3=Markets.Market&val3=%22PHILADELPHIA%22&col4=Periods.%22Year%22&val4=%222001%22&col5=Periods.%22Month%22&val5=%222001-03-01%22&col6=Periods.Week&val6=%22WEEK%20ENDING%2003%2F10%2F01%22&col7=Products.Type&val7=%22COATINGS%22&col8=Products.Brand&val8=%22Enterprise%22&col9=Products.UPC&val9=%22Enterprise%20Steel%20Gloss%22&nquser=Administrator&nqpassword=Administrator

http://webserver/analytics/saw.dll?Go&Path=%2Fusers%2Fadministrator%2FAllPromptTestReport&Action=Navigate&col1=Markets.Region&val1=%22EASTERN%20REGION%22&col2=Markets.District&val2=%22PHILADELPHIA%20DISTRICT%22&col3=Markets.Market&val3=%22PHILADELPHIA%22&col4=Periods.%22Year%22&val4=%222001%22&col5=Periods.%22Month%22&val5=%222001-03-01%22&col6=Periods.Week&val6=%22WEEK%20ENDING%2003%2F10%2F01%22&col7=Products.Type&val7=%22COATINGS%22&col8=Products.Brand&val8=%22Enterprise%22&col9=Products.UPC&val9=%22Enterprise%20Steel%20Gloss%22&nquser=Administrator&nqpassword=Administrator

Old syntax for passing parameters works both in OBIEE and previous releases e.g. 7.7, 7.8, 10.1.3.1 etc.

Pass one parameter
saw.dll?Go&Path=/Shared/Training/ParameterReport&Action=Navigate&P0=1&P1=eq&P2=Periods.Year&P3=1+1999
Pass 2 parameters
saw.dll?Go&Path=/Shared/Training/ParameterReport&Action=Navigate&P0=2&P1=eq&P2=Periods.Year&P3=1+1999&P4=eq&P5=Customers.State&P6=1+CA

Here the operator P1=eq can be of different type as documented in the Siebel Analytics Web Admin guide e.g. like, lt, gt etc.
saw.dll?Go&Path=/Shared/Training/ParameterReport&options=md&Action=Navigate&P0=2&P1=eq&P2=Customers.State&P3=1+CA&P4=like&P5=Products.Type&P6=1+”B%”

Include the numeric parameter value in “ e.g. &P2=Periods.Year&P3=”1998”
Also position Like operator at the end of the parameter list.

Passing parameter to a Dashboard prompt

If a Dashboard is using prompts then values can be passed to it similar to above syntax as follows

http://webt43/analytics/saw.dll?Dashboard&PortalPath=/shared/Training/_Portal/Training&Page=Filters&Action=Navigate&P0=1&P1=eq&P2=Products.Type&P3=Beef

Issuing Direct SQL to Siebel Analytics using URL syntax

Following URL syntax can also be used to directly issue a SQL against Analytics
saw.dll?Go&SQL=select+Region,Dollars+from+SupplierSales
following URL will bypass web cache and issued directly to the Analytics server
saw.dll?Go&IssueRawSQL=select+Region,Dollars+from+SupplierSales

Calling BI Publisher reports directly similar to GO URL

URL to directly call a BI Publisher report.
It will prompt the login screen if BI publisher is not single sign-on with the calling app

http://webserver:9704/xmlpserver/Business+Intelligence/Paint+Demo/Paint+Demo.xdo

Calling BI publisher report from OBIEE URL similar to Go URL syntax
Use ExecuteReportObject syntax

http://webserver/analytics/saw.dll?ExecuteReportObject&Path=/Business%20Intelligence/Paint%20Demo/Paint%20Demo.xdo

New syntax for Go URL in Analytics. You can now pass as many parameters and values as you want
note the syntaxt &col1=&val1=

http://webserver/analytics/saw.dll?Go&Path=%2Fshared%2FSupplier%2FRegionDollars&Action=Navigate&col1=Periods.%22Year%22&val1=%221999%22&nquser=Administrator&nqpassword=Administrator

Calling a Dashboard page works the same way.
If Page has some prompts then those can also be filtered using new syntax

http://webserver/analytics/saw.dll?Dashboard&PortalPath=%2Fusers%2Fadministrator%2F_portal&Page=page2&Action=Navigate&col1=Periods.%22Year%22&val1=%221999%22

If a page has only a BI publisher report and also has a prompt then
You can pass filters to the BI publisher report using Go URL syntax as below

http://webserver/analytics/saw.dll?Dashboard&PortalPath=%2Fusers%2Fadministrator%2F_portal&Page=page2&Action=Navigate&col1=Period