Archive for July, - practice document

Customizing OBI EE – SOAP API

Posted by Venkatakrishnan J on July 31, 2007

As I was writing my blog yesterday on using URL parameters as a customizing option, it made me think about the availability of open API’s for OBI EE. As luck would have it, I had some

time yesterday to work on the API’s and this blog is a result of that. In this article, we would be seeing how to use SOAP API of OBI EE from JDeveloper. One of the major advantages of OBI EE is that it opens itself for developers in the form of standard API’s. Almost all the functionality is exposed and it would enable customers to use these API’s within their

application code sets and thereby enabling complete integration. Here, we would basically be seeing how to go about using the SawSession service and the HtmlViewService to view a simple report. The SawSession service helps is maintaining sessions & authentication. The HtmlViewService helps in actually extracting the layout of a report. The SOAP API works on the WSDL implementation within OBI EE. If you navigate to

http://localhost:port/analytics/saw.dll?WSDL one can obtain the WSDL schema information. One can use the Mindreef SoapSCOPE evaluation copy to go through the WSDL.

One can test each of the invoking methods using this. But the only major problem with this evaluation copy is that you cannot maintain session state. Hence it would be difficult to test all the methods available. Now lets look at the various services that are available.

1. Security Service

2. Ibot Service

3. Web Catalog Service

4. Replication Service 5. Metadata Service 6. ReportEditing Service 7. HTMLView Service 8. XMLView Service 9. SAWSession Service

Security Service: This service helps in identifying the user priveleges. One can assign, revoke privileges using Security Services.

Ibot Service: As the name suggests, this is a service for invoking Ibots. Web Catalog Service: This service is for managing the web catalog.

Replication Service: This service is used for replication. Export/Import of catalogs can be done using this.

Metadata Service: This is for managing the BI Server metadata.

ReportEditing Service: This service is used to merge arguments and Oracle BI Web Services data to create and return the results.

HTMLView Service: This service is used to embed Oracle BI HTML results in third-party dynamic Web pages, such as Active Server Pages (ASP) or JavaServer Pages (JSP), and portal frameworks. The embed process merges Oracle BI Web Services content with the content of third-party Web pages.

XMLView Service: This is used for retrieving the data from Oracle BI EE Server in the form of XML.

SAWSession Service: This is the service that would enable users to login, logout and maintain sessions. Now lets see how we can go about using these services from within Jdeveloper to create a sample custom report.

1. Open Jdeveloper and create a new application. (With no templates)

2. Create a new Package within the Applcation.

Use Web Service Proxy technology to create it.

Invoke all the methods that you need. In our example, we will be including the SAWSession service, the HTMLView Service and the XMLView Service. 3. The next step is to create an object for SAWSession Service and invoke the logon method.

For example, soaptest.SAWSessionServiceSoapClient myPort = new soaptest.SAWSessionServiceSoapClient();

sessionID = myPort.logon(“Administrator”,”Administrator”);

This would create a session. 4. Once a session is created, the next step is to create a pageID for that session. A page is nothing but an entire html page that might contain multiple reports. This uses the HTMLView Service.

For example, soaptest.HtmlViewServiceClient htmlClient = new soaptest.HtmlViewServiceClient();

pageID = htmlClient.startPage(newPage,sessionID); 5. After creating the pageID, create multiple report Ids that you would like to associate with a pageID.

For example,

htmlClient.addReportToPage(pageID, reportID, newreportRef, null, null, null, sessionID);

Here, newreportRef uses a Reference structure like this

ReportRef newreportRef = new ReportRef();

newreportRef.reportXml = reportXML;

For specifying a report one would have to specify 2 values. One is the report path and the other is the report definition. Both can be obtained from the report definition properties.

5. Once this done the next step is to generate html output for the report. For example,

htmlOutput = htmlClient.getHtmlForReport(pageID, reportID, sessionID); For future reference, I have uploaded the entire code here. One can use this for

reference. This just shows the extensibility of OBI EE. Also, one can call BPEL processes from within BI EE to make educated business decisions. Mark Rittman has already blogged about that here. Interesting post considering the fact it is gaining more and more traction in the customer environments. I just had a customer who had this query and i was more than happy to direct them to Mark’s blog just to give them an idea and of course the documentation. The power of community is so good in this regard and it has helped me out a lot of times. If you want to know more about the open API’s they are available here. Just in case you want to do the same with BI Publisher they are available here. You might want to check out this blog by Vlamis and his team for giving out excellent articles on OLAP and DW in general. Also, if you are passionate about discoverer check out Michael Armstrong-Smith‘s blog.

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

Customizing OBI EE – GO URL Parameters

Posted by Venkatakrishnan J on July 30, 2007

While I was working on the data mining pieces, I got a request from a customer who basically wanted to completely customize OBI EE. Well, it got me thinking about the various customization options that we have with OBI EE. In my customer’s case, they wanted to integrate OBI EE into their web application. Let’s see what options we have with OBI EE customizations.

Using OBI EE GO URL parameters

Almost most of the functionality that a customer would need or expect out of OBI EE is available for them via URL parameters. One can leverage these URL parameters into their application. What this would do is that rather than having OBI EE as a separate reporting tool, it would enable an end customer to call various reports via iframes into their application.

A sample URL with the parameters for OBI EE would look like this

http://hostname:port/analytics/saw.dll?

GO&NQUser=Administrator&NQPassword=Administrator&

Path=/Users/administrator/GEC_DW/Regional+Sales/Profit+per+Category+Pie+Chart&Options= md

When one needs to use the URL parameters the fundamental part of the URL should include

http://hostname:port/analytics/saw.dll?GO and following that options are to be entered. Lets look at the various URL parameters for OBI EE

1. &NQUser – Username for logging into Answers

2. &NQPassword – Password for login. One must realize that entering password directly into the URL is not very secure. Its recommended to use the post method wherein the password is not explicitly shown. (People who know discoverer, entering password, as a parameter is no more

available. Only post method is supported)

3. &Options=mdfr – Each of the letters in mdfr have a specific function. m - To include the modify report link under the report

d - To include the download link under the report f - To include the printer friendly link under the report r - To include the refresh report link under the report

All the above can be interchangeably used. For example &Options=md for including modify and download links alone.

4. &Action – This allows the developers to request specific formats for the reports. For example, &Action=print (can also have excel etc)

5. &ViewName – This argument allows one to specify a view. For example, &ViewName=Chart1

6. &Style – Specify a style sheet For example, &Style=Lime 7. &Format – Specify the format of the output, html or xml 8. &Path – Specify the path for the report. For example, &Path=/Shared/administrator/testReport

9. &SQL – Logical Sql – One can even issue logical sql via the URL parameter. For example, &SQL=select+region+from+Sales. Here Sales is the subject area.

The above are some of the parameters that you might find useful. But for more detailed and some

parameters refer the Documentation here.

The above picture is actually a simple application that calls out 2 different reports based on the data entered. This kind of integration is very strong and infact the entire OBI EE is integrated into the Oracle Siebel CRM using this methodology. One can use Post methods; execute scripts etc using this type of methodology.

Following are some examples for simple HTML form elements using post method. Access to Answers:

</form>

To open a report in Answers (for edit), include the Path parameter:

To open BI Publisher use “AdvancedReports” as the Cmd value

</form>

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

Oracle Data Mining and Business Intelligence

Posted by Venkatakrishnan J on July 25, 2007

I believe Oracle Data Mining is one of the most under utilized tools/database options in the BI space. There are so many Oracle Data Warehouse implementations out there but hardly few use Oracle Data Mining (but yes the market share is growing recently). Is there any reason for such a low adoption rate? Is it due to the lack of capabilities of Oracle Data Miner? Or is it due to the perceived notion that Data Mining is for statisticians and the like? Or is it because the management does not believe in the value of Data Mining?

I think it’s primarily due to the perceived notion rather than the lack of features because ODM indeed has a very rich set of functionality (a lot!!!). Of course statisticians can much appreciate the value of a data-mining tool. But again, as a person who has been involved in some DW

implementations, I believe we understand the data better than anyone else. To use data mining Statistical knowledge would be an absolute plus but not mandatory. I am planning to put together a series of articles that would elucidate the various models that are used in Oracle Data Miner with examples. Its more of an education for me too since I myself would be getting my hands dirty for the first time with this tool. It is a bit too ambitious but let me see how far I can go. I will try to put together some relevant day-to-day examples as and when I get time (I will try as much as possible to deviate from the Beer bottle and Baby diaper example J). For more detailed analysis and inputs

we always have the documentation and the ODM PM Marcos Campos’s blog. Actually the ODM

PM is Charlie Berger. Thanks for correcting me Charlie. But some usual caveats here. Data

Mining is a huge and a vast topic. I plan to just cover the models that might make sense for all those who use Business Intelligence. If you have any feedback do let me know since I think I am being too opinionated here!!!

Before we get into the details, lets go through an overview of datamining types. There are 2 types of datamining.

1. Supervised Data Mining 2. Un-Supervised Data Mining

Supervised is also called as Predictive data mining wherein the process is used to predict some outcomes. For example, a credit card company can use this to predict whether a customer is going to default on his payments.

Un-Supervised Datamining, also known as descriptive datamining involves finding out intrinsic patterns within the data. Again, how the pattern is identified depends on the model.

Following are some of the models supported by Oracle Data Miner. Supervised Data Mining:

1. Classification 2. Regression

3. Attribute Importance 4. Anomaly Detection Un-Supervised Data Mining: 1. Clustering

2. Association 3. Feature Extraction

With that I would like to close this with some interesting articles on datamining

1. Crime Detection

2. Credit Card Fraud Detection

Posted in All Posts, Data Mining | 2 Comments »

BI Publisher 5.6.2 and VPD

Posted by Venkatakrishnan J on July 24, 2007

In my last article, i had written about leveraging Proxy Authentication for VPD in BIP 10.1.3.2.

But in earlier releases of BIP especially for people who are still on 5.6.2 version, there was no proxy authentication. Also, if a customer wants to leverage VPD within an Oracle database that is of earlier releases like 9i/8i then Proxy Authentication will not work. So in this article i would be focussing on 2 methods of using VPD within BIP 5.6.2. I will talk about 2 cases here. To be honest, the first one does not leverage VPD within the database. But i have included it here just for the sake of completeness.

1. If the idea is just to pass the username & get the data based on the user, this can be done directly from the BIP itself without going back to the database (without setting up VPD in the database). Consider there are 2 enterprise users Executive & Analyst. We have to create a report from a table that has a column called userlogged. The idea is when Analyst logs in one must see the Analyst user’s (userlogged = ‘ANALYST’ in the sqlquery) data. Similarly, for the executive user one must see the Executive User’s data.

To implement this we would be creating an XMLP report with DataTemplate as the data source.

For more on Templates refer Tim Dexter’s blog and of course the BIP 5.6.2 User Guide

Use the following script for Data Template.

select EMPLOYEE_ID, JOB_TITLE, FIRST_NAME, EMAIL, HIRE_DATE, SALARY from employees a, jobs b where a.job_id = b.job_id and UPPER(ADMIN_USER) =

upper(:P_USERNAME) </sqlStatement>

</dataQuery> <dataStructure>

</group> </dataStructure> </dataTemplate>

Here, the ${SYSUSER.username} variable would substitute the actual usernames into the query & hence would return different results for each logged in user. For complex queries one can use nested case statements to use this username variable & implement the logic accordingly. It is important to note the positioning of the parameter variable tags since placing anywhere else would not return the valid data. The other option would be is to have the where clause statement itself returned back to the bip by using lexical references. This way one need not implement vpd at the database level.

2. If the idea is to have some complex queries based on the username passed then VPD at the database level can be used.

2.1.In the following example, the 2 users analyst & executive would be mapped within the database as admin & admin1 respectively. These users would be accessing the data under the HR schema which would be the VPD owner

2.2. The VPD policy would then be applied on a table called hr.countries over which the column

COL_USER would be the column that maintains the user details.

2.3. Before creating the policy one would have to create application context package using the

following script. create or replace context HR using HR.Context_Package_XML;create or replace PACKAGE Context_Package_XML AS

FUNCTION BeforeReportTrigger return boolean; P_USERNAME VARCHAR2(100);

END;create or replace PACKAGE BODY Context_Package_XML IS FUNCTION BeforeReportTrigger RETURN BOOLEAN IS

v_ouser VARCHAR2(30); BEGIN

DBMS_Session.Set_Context(‘HR’,'SETUP’,'TRUE’);

v_ouser := SYS_CONTEXT(‘USERENV’,'SESSION_USER’); IF upper(P_USERNAME) = ‘ANALYST’ THEN

BEGIN

DBMS_Session.Set_Context(‘HR’,'USER_NAME’, ‘ADMIN’); END;

IF UPPER(P_USERNAME) = ‘EXECUTIVE’ THEN BEGIN DBMS_Session.Set_Context(‘HR’,'USER_NAME’, ‘ADMIN1′); END; ELSE DBMS_Session.Set_Context(‘HR’,'USER_NAME’, ‘HR’); END IF; END IF; DBMS_Session.Set_Context(‘HR’,'SETUP’,'FALSE’); RETURN TRUE; END; END Context_Package_XML; Create the vpd function

create or replace FUNCTION user_only ( p_schema IN VARCHAR2 DEFAULT NULL, p_object IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 AS BEGIN if sys_context(‘HR’,'USER_NAME’) = ‘HR’ then RETURN ’1=1′; else

IF upper(SYS_CONTEXT(‘HR’,'USER_NAME’)) = ‘ADMIN’ THEN return ‘upper(con_user) = ”ADMIN”’;

ELSE

IF upper(SYS_CONTEXT(‘HR’,'USER_NAME’)) = ‘ADMIN1′ then return ‘upper(con_user) = ”ADMIN1”’;

else return ’1=0′; end if; end if; end if; END;

Add a vpd policy using dbms_rls.add_policy procedure BEGIN DBMS_RLS.add_policy (object_schema => ‘HR’, object_name => ‘COUNTRIES’, policy_name => ‘COUNTRY_SEL_POL’, function_schema => ‘HR’, policy_function => ‘USER_ONLY’, statement_types => ‘SELECT’); END;

This application context package would set the context depending on the username (Analyst or Executive). This package would be run by using a before report trigger that’s available in BIP. Once this is done, the next step is to create a report using Data Templates. One can use the following Data Template code.

select COUNTRY_ID, COUNTRY_NAME, REGION_ID,CON_USER from countries </sqlStatement>

</dataQuery>

In document practice document (Page 30-47)