Business Intelligence – Oracle

Archive for the ‘Bi Publisher’ Category

Oracle BI Publisher – Web Services using APEX

Posted by Venkatakrishnan J on October 30, 2007

If you had read my previous blog entry here on BI Publisher SOAP APIs, you would have noticed that i had used a evaluation tool called Mindreef SOAPScope to test out the web services. I was reminded of a much better in house Oracle tool yesterday by Adrian which is none other than the Application Express. Though i have used APEX before to test Web Services, i forgot to mention about it in that blog entry. So, if you want to test out BI Publisher Web Services, APEX would be best tool to start with. I thought i would write about it here today since APEX is a free tool and you can test web services out of the box using this.

1.   Log into your workspace in APEX. Create a new application with some blank pages. Navigate to the shared Components and click on Web Services References. Let us create one for BI Publisher. Ensure that you are not searching the UDDI registry since we have a WSDL already defined.



As you would see, APEX would show all the methods that are exposed. Remember that APEX currently supports WSDL with only one SOAP binding. One cannot use(i am not sure whether one can use manual registration to register multiple SOAP binding WSDL) multiple SOAP binding WSDL like the BI EE WSDL.

2.   Lets test out the simple validate login method. Lets create a simple form based on this Web Service in APEX.


Now lets enter the username and passwords to see the login method actually works. When i click on submit, the Validateloginreturn will get the value of true which basically indicates that this method has successfly got completed.


The above is pretty straightforward. But it is worth a mention since you can use APEX to test out all the methods and of course it would come very handy if you want to build a sample application which would call all these web services.


Posted in All Posts, APEX, Bi Publisher | 3 Comments »

Oracle BI Publisher – Web Services – First Impressions

Posted by Venkatakrishnan J on October 25, 2007

I was going through the new BI Publisher feature of which is the Web Service APIs. Till the earlier release we only had Java APIs. But in the latest release we have the SOAP APIs or the WSDL APIs so that any end user can access the reports remotely using standard web service calls. To try this out, i had installed JDeveloper 11g Tech Preview 2. But somehow i was not able to make the Web Services work from JDeveloper. I constantly got “not a recognized SOAP header error”. So, i downloaded another tool called Mindreef SOAPScope. They provide an evaluation copy for 13 days. Well, i was literally amazed by the power of this tool. It would be good to have similar features in JDeveloper. You can download SOAPScope here.

Coming back to BI Publisher WSDL, there are 7 main methods that are exposed to end users. They are

1.   getFolderContents   –   This method helps in retrieving all the contents within a folder.

2.   getReportDefinition   –   As the name suggests this retrieves the report definition for the report which you give as a parameter.

3.   getReportParameters   –   This retrieves the parameters of the report.

4.   hasReportAccess   –   This checks whether a particular user has access to the report specified.

5.   runReport   –   This is the method which would help in running the report. One can retrieve the contents in the form of PDF, HTML etc.

6.   scheduleReport   –   This method exposes the scheduler functionality of BI Publisher.

7.   validateLogin   –   This checks whether a specific user can log into BI Publisher.

The below picture gives all the methods as seen by SOAPScope.


validateLogin: Now lets test out all the methods starting from validateLogin. As shown below this method accepts two parameters which is the username and the password. The return field of this function is a boolean data type which would give a true or false depending on the user credentials.



getFolderContents: The next method which we shall test is the getFolderContents. This method accepts three parameters. Username, Password and the absolute path of the foldername. For example,

Folder Name: /HR Manager
Username: Administrator
Password: Administrator

The absolute path is actually relative to the URL. I am not sure whether one can access my folders content since the absolute path seem to take all the folders only inside the Shared Folders. The return data set of this function is all the contents of this folder including subfolders and reports(not the recursive reports).



getReportDefinition: This method accepts username, password and the absolute path of the report as its input. The return type is its definition that would include the parameters, templates, data sources etc. The reportAbsolute path should include the xdo path. Eg. /HR Manager/Employee Salary Report/Employee Salary Report.xdo



getReportParameters: This method accepts username, password and the reportRequest as its parameter. reportRequest is a structure that has arguments like
      Parameter Array

In our case we would just include reportAbsolutePath, username and the password. This would return 2 parameters of the Employee Salary Report which is the department and employees.



hasReportAccess: This accepts username, password and the reportabsolute path as the set of parameters. It checks whether the user specified by the username parameter has the privilege to run the report.



runReport: This is the method which would help in running the report. It accepts the same set of parameters like the getReportParameters. The output would contain a Doublebinary byte which would have to be converted to the corresponding output like html, pdf etc.



scheduleReport: This method exposes the entire scheduler functionality. There are a lot of parameters which i would not go in details since most are self explanatory.


I tried creating a sample application. But i am not sure whether the documentation has specified all the jars required in the invoking client as i had to include 3 more jars.

Posted in All Posts, Bi Publisher | 3 Comments »

Oracle BI EE – New release – New Features

Posted by Venkatakrishnan J on October 22, 2007

Well, i picked this up from Abhinav’s blog here. Looks like the is out for download. One of the things that i do after any Oracle Software release is to go through the list of new features. I see that 3 new features for BI EE and 3 new features for BI Publisher have been added. But keep in mind that this is not a major release. This is only an add-on, if i may put it that way, release to the earlier release. Following are the list of new features in BI EE. I will quote it from the docs here

1. Oracle Business Intelligence Installer Now Deploys BI Components into One OC4J Container – If you had gone through Advanced installation of BI EE earlier, you would have noticed that all the components got deployed into the root container without creating a seperate OC4J container for all of the components. Now, in this release all the components will be deployed into a new OC4J container called as home.

2. DataDirect Connect ODBC Drivers for Linux and UNIX Operating Systems Bundled with Oracle BI EE – Well, now one does not have to go on the look out for ODBC drivers for various databases. Oracle BI EE provides ODBC drivers for Informix, MS SQL Server, and Sybase ASE databases(for linux and UNIX OS).

3. Support for Charts in Oracle BI EE for Arabic, Thai, and Hebrew – This looks to me more like a workaround since BI EE charting engine has a limitation in terms of its rendering capability for Arabic, Thai and Hebrew. Now, in BI EE you would have to use BI Publisher to render these charts. Use BI Answers report as the data source and use the BIP translation to do the rendering for you in Arabic, Thai and Hebrew.

BI Publisher New Features in

1. Time Out Value for Web Service Data Sources – One can now set the time out value for Web Service data sources.

2. Bidirectional Function for eText Data Fields

3. Default Repository – There is now a default repository location that has been added to xmlp-server-config.xml. One can set it to the file path desired. It is also set by default during installation.

Posted in All Posts, Bi Publisher, OBI EE Plus | 6 Comments »

Oracle BI EE – Dashboard Prompts and BI Publisher

Posted by Venkatakrishnan J on October 19, 2007

One of the common questions that generally come up with regard to BI EE and BI Publisher is “how does one pass parameters to BI Publisher reports in Dashboards?”. Well, there are two methods for achieving this.

1.   Create a BI Publisher Report using BI Answers report as the data source wherein the BI Answers report would have one of the column’s filter clause as “is prompted”.

2.   Create a BI Publisher Report using BI Answers/Database as data source. Create a parameter for the BI Publisher report and then match this parameter name while assigning the presentation variable for the dashboard prompt.

Method1 is straight forward. All we need to do is to create a report in BI Answers first and then assign filter to one of the columns using is prompted clause. In our case we would be creating a simple sales report across multiple regions. And Region is the column which would have the filter.


Now go to BI Publisher and use this BI Answers report as a data source. Create a simple template for this report. Check whether this simple report works.


Once this is done lets create a dashboard prompt in answers which would basically refer to the Region Column. In this method, there is no need for setting any presentation variables.


Lets create a new Dashboard page and add the prompt and the BI Publisher report. The BI Publisher report would change based on the Region that you select in the dashboard prompt.



      This method is generally the most used since most of the BI Publisher reports would have database as the data source. In order to test this, lets create a simple BI Publisher report with the data coming in from SH schema of the database. We will be creating a report with the same columns as above to keep it simple. Also create a simple template and check whether the BI Publisher report is working.


Now add a parameter to the report. In our case, we will have Region_Prompt as the parameter name. And test the report.



Then go to the BI Answers and create a dashboard prompt on the same Region column. In this prompt we must set a Presentation Variable, Report_Prompt. Just note the name of the presentation variable. It must exactly match that of BI Publisher parameter name. Include this dashboard prompt and the BI Publisher report into the dashboards. And test it out.



Posted in All Posts, Bi Publisher, OBI EE Plus | 4 Comments »

Oracle BI EE – Ref Cursors and Pipelined functions

Posted by Venkatakrishnan J on September 21, 2007

Another interesting question that generally comes up is whether Oracle BI EE supports Ref Cursors. Well, currently only BI Publisher supports the use of ref cursors to pass the data to the output (Remember, BI Publisher uses jdbc connections and jdbc supports ref cursors.). I tried using the following ref cursor function on both BI Publisher and BI EE. Lets see what the results are and of course lets see what is the workaround available for OBI EE. To start with i created a function that basically returns a ref cursor of the dept table in scott schema.

create or replace function refcursor_dept return sys_refcursor as
TYPE v_cursor is REF CURSOR;
p_cursor v_cursor;
open p_cursor for
select * from dept;
return p_cursor;


In BI Publisher this would work like a breeze. All one would have to do is to call the function. Tim Dexter has already blogged about this. One can get more details here.

Now, lets try this in BI EE. I will be testing this using the direct database requests option on BI EE since it provides an easier way of testing things out. If you have not set direct database requests, i have blogged about it here showing you how to set it up. I have created a connection pool that basically points to my scott schema.


Now, lets fire the query using direct database requests using the connection pool that i had created earlier.


As you see, we get an error saying that this data type is not supported (this is the first time i am trying this out. If anyone has found a way of directly calling reference cursors from OBI EE please do let me know. I would be more than happy to share it here). So, for users who want to leverage their existing ref cursor functions, one option is to use pipelined functions. Lets see for the above example, how one can go about creating a pipelined function and of course how one can call them from OBI EE.

The first step is to create an object type that would basically contain all the columns of the dept table.

create or replace type dept_objecttype as object

Once this is done, lets create a table type of the above object.

create or replace type tabletype_dept as table of dept_objecttype;

Once this is done, lets create a pipelined function that would basically take in the ref cursor function that we created earlier as an argument.

create or replace function pipelined_dept
(p_cursor in sys_refcursor)
return tabletype_dept
v_records dept%rowtype;
fetch p_cursor into v_records;
exit when (p_cursor%notfound);
pipe row(
v_records.loc) );
end loop;

Now lets test this function from sqlplus.


Once this is done, lets test it out in BI EE.



For more detailed explanation on pipelined functions, i would recommend Tom Kyte’s article here. And yes of course, if you have other methods of calling ref cursors, feel free to put them in the comments section.

Posted in All Posts, Bi Publisher, OBI EE Plus | 2 Comments »

Oracle Reports to BI Publisher – Conversion Utility

Posted by Venkatakrishnan J on August 22, 2007

Oracle BI Publisher has a very interesting feature. It comes along with a small utility that can convert Oracle Reports to BI Publisher reports. I just thought i would blog about the same, since a lot of Oracle Report users are now migrating towards BI Publisher. This utility would come in handy for a lot of people. In fact getting this utility to work is very simple. Lets see how this utility works in this article. First i started with creating a sample Oracle Report.


The report is based on 3 columns of Geography table. Those 3 are Region Name, Country Name and City Name. This report would primarily consist of grouping by region and then displaying a tabular report of the coutries and cities for that region. The report looks like this


The next step is to export/save the report in the form of XML from within Oracle Reports. Remember that Oracle Reports stores both the layout and data queries in the same file. Once the report is saved update your CLASSPATH to point to the $BIPUB_HOME\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib directory. This directory would contain 4 import jar files. They are

1.   Collections.jar
2.   xmlparserv2-904.jar
3.   xdocore.jar
4.   aolj.jar

The above 4 files are required for the utility to work. The utility accepts the following parameters

                     -source — (required) Source directory for Oracle Reports files. All reports must be in the same format – either RDF or XML.

                     -target — (required) Target directory to create Oracle BI Publisher report objects. This includes the Oracle BI Publisher Report file (.xdo), the layout template file (.rtf), the PL/SQL package, and log file.

                     -oraclehome — (optional) If your reports are in Oracle Reports XML format do not specify this parameter. If your reports are not in Oracle Reports XML format, specify the Oracle home path where Oracle Report Designer (9i or later version) is                         installed. BIPBatchMigration assumes that rwconverter is contained in the bin directory beneath the Oracle Home path.

BIPBatchConversion requires rwconverter from Oracle Reports to convert the report from RDF format to XML format.

                     -debug — (optional) To run the utility in debug mode and write debug statements to the log file.

Command Line Usage:

                     java … BIPBatchConversion [-debug] -source SourceDirectory -target TargetDirectory [-oraclehome OracleHomePath]


Once the command is executed, it will create the layout, sample xml, xdo file and others in a same directory.


The next step is to compile the package that is created once the utility is executed.


Lets open the template to see whether the grouping and others are intact.


Copy the entire folder to the xmlp reports directory. Once the copy is done, refresh the report repository. We would get the report within BIP Enterprise as shown below.


View the report. But ensure that you have data source of the name default that would connect to the source from which you have created the report. Else change the name of the datasource in the date template definition.


 All of the above and more, is available in the documentation here.

Posted in All Posts, Bi Publisher | 15 Comments »

Oracle BI Enterprise Edition

Posted by Venkatakrishnan J on August 20, 2007

As you might know, release of OBI EE is out for download. There have been quite a lot of features that have been added to this release. Especially, in the MS Office integration front and BI Publisher front. There is a new BI office add for office 2003. Also, there is an out of the box integration available between BI Publisher and Discoverer. Like in the earlier release, where in one can source OBI EE reports from BI Publisher, one can source this time from Oracle Discoverer. I won’t be going too much into details about this since we already have a great resource about them in Abhinav’s blog here, here and here. I would rather focus on the BI Publisher features in this article.




There are more download options in this release from within BI Publisher. In the earlier release, if you had noticed, there was no explicit way to install the excel analyzer. The only way was to click on the Analyzer for Excel button (one would have to reduce the excel security level to get this installed). But now, there is an external button that you can leverage to install this directly.



Also, there is an option wherein one can create a template automatically from a data set.

This makes a lot of sense for users who are pretty new with this tool and do not how to start. Atleast this can act as a bridge.




There are more download/viewing options now. Powerpoint, CSV support have been added into this new release.


And ofcourse, there is no need for anyone to remember the URL parameters (basic functionality is exposed. For more advanced, the only way is to get them from the user guide) to get parts of the report. This has been added as links to the report in the BI Publisher Enterprise.


Another major addition to this release of BI EE is that, BI Publisher supports flash templates. One can view flash reports from with BI Publisher and ofcourse can expose them to BI EE. Quite an interesting feature if one needs highly customized flash reports.


The last noticeable feature from within BI Publisher is that, it has option now to store the files within a directory structure (file system) or within XML DB.


And yes, Tim Dexter has earmarked some good new features in BI Publisher here.


Posted in All Posts, Bi Publisher, OBI EE Plus | Leave a Comment »

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

<dataTemplate name=”JobDemo” description=”Job Demo” dataSourceRef=”BIP_VPD” Version=”1.0″>
    <input id=”P_USERNAME” value=”${SYSUSER.username}”/>
     <property name=”showControls” value=”false”/>
     <property name=”online” value=”true”/>
     <property name=”parameterColumns” value=”3″/>
     <property name=”openLinkInNewWindow” value=”true”/>
     <parameter dataType=”character” name=”P_USERNAME”/>
     <sqlStatement name=”Q1″>
 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)
     <group name=”G_EMPLOYEE” source=”Q1″>
      <element name=”EMPLOYEE_ID” value=”EMPLOYEE_ID”/>
      <element name=”JOB_TITLE” value=”JOB_TITLE”/>
      <element name=”FIRST_NAME” value=”FIRST_NAME”/>
      <element name=”EMAIL” value=”EMAIL”/>
      <element name=”HIRE_DATE” value=”HIRE_DATE”/>
      <element name=”SALARY” value=”SALARY”/>

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;
create or replace PACKAGE BODY Context_Package_XML IS
v_ouser VARCHAR2(30);
DBMS_Session.Set_Context(‘HR’,’USER_NAME’, ‘ADMIN’);
DBMS_Session.Set_Context(‘HR’,’USER_NAME’, ‘ADMIN1’);
DBMS_Session.Set_Context(‘HR’,’USER_NAME’, ‘HR’);
END Context_Package_XML;

Create the vpd function

create or replace FUNCTION user_only (
if sys_context(‘HR’,’USER_NAME’) = ‘HR’ then
RETURN ‘1=1’;
return ‘upper(con_user) = ”ADMIN”’;
IF upper(SYS_CONTEXT(‘HR’,’USER_NAME’)) = ‘ADMIN1’ then
return ‘upper(con_user) = ”ADMIN1”’;
return ‘1=0’;
end if;
end if;
end if;

Add a vpd policy using dbms_rls.add_policy procedure

(object_schema => ‘HR’,
object_name => ‘COUNTRIES’,
policy_name => ‘COUNTRY_SEL_POL’,
function_schema => ‘HR’,
policy_function => ‘USER_ONLY’,
statement_types => ‘SELECT’);

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.

<dataTemplate name=”JobDemo” description=”Job Demo” dataSourceRef=”BIP_VPD1″ defaultPackage=”Context_Package_XML” Version=”1.0″>
                                <input id=”P_USERNAME” value=”${SYSUSER.username}”/>
                                        <parameter dataType=”character” name=”P_USERNAME”/>
     <sqlStatement name=”Q1″>
                                <dataTrigger name=”beforeReport” source=”Context_Package_XML.BeforeReportTrigger”/>
     <group name=”G_COUNTRIES” source=”Q1″>
      <element name=”COUNTRY_ID” value=”COUNTRY_ID”/>
      <element name=”COUNTRY_NAME” value=”COUNTRY_NAME”/>
      <element name=”REGION_ID” value=”REGION_ID”/>
                                                <element name=”CON_USER” value=”CON_USER”/>

What this basically does is that it calls a before report trigger(a Pl/SQL package wherein we pass the username of the user logged in using the ${SYSUSER.username} variable. The Pl/SQL package basically sets the context thus enabling the report to be executed as the user based on the context.

Posted in Bi Publisher | 1 Comment »

BI Publisher and VPD

Posted by Venkatakrishnan J on July 23, 2007

I was going through an interesting post by Jonathan Lewis on Proxy Authentication that is available for end users via Sql Plus in 10g R2. This post made me think about the availability of the same in BI Publisher wherein one can leverage the proxy authentication(for database connections that are on 10g) to do VPD or row level security. This article would demostrate how to use Proxy Authentication to do row level security within BI Publisher. Of course, this applies only if you are version of BIP and 10g of database. For users who are on earlier releases, I would follow this one up with another article for showing how to go about doing the same.Assume that there are end users Analyst and Executive within BI Publisher(Analyst and Executive are also users within the 10g R2 database). There is one table under Analyst that has all the Employee details. Assume that the Analyst is working on the Employee table but would like Executive end user to see only those data wherein the salary is greater than 7000.

Create a Proxy user bipubapp_user that would be used to proxy authenticate both Analyst and Executive. The following commands are performed as a user Sys(any admin user)

Create user bipubapp_user identified by welcome1;

grant create session to bipubapp_user;

create user Analyst identified by welcome1;

grant create session, create table to Analyst;

alter user Analyst grant connect through bipubapp_user;

create table Analyst.Employees as select * from HR.Employees;

create user Executive identified by welcome1;

grant create session to Executive;

alter user Executive grant connect through bipubapp_user;

The next step is to set up the VPD policy within the database. I have a user called VPD_ADMIN to source the function and the VPD policy.

1. Create the following VPD function.

create or replace FUNCTION Executive_Apply 
                       RETURN VARCHAR2
if USER = ‘ANALYST’ then
   RETURN ‘1=1’;
   return ‘salary > 7000’;
return ‘1=0’;
end if;
end if;

2. Add a VPD policy with the above function

         (object_schema => ‘ANALYST’,
          object_name => ‘EMPLOYEES’,
          policy_name => ‘EMPLOYEE_SEL_POL’,
          function_schema => ‘VPD_ADMIN’,
          policy_function => ‘EXECUTIVE_APPLY’,
          statement_types => ‘SELECT’);

3. Check whether VPD is working

SQL> conn bipubapp_user[executive]/welcome1
SQL> show user
SQL> select count(*) from analyst.employees;


SQL> conn bipubapp_user[analyst]/welcome1
SQL> show user
SQL> select count(*) from analyst.employees;


The next step is to go to the BI Publisher admin console and create 2 users Analyst and Executive.


Create a datasource that would use the bipubbapp_user as the login username. Make sure to enable proxy authentication while creating the datasource.


Give the necessary privileges to Analyst and Executive. Log out and log in as Analyst and create a report which would extract the data from the Employees table.


View the report. I have not created any templates here. This is just to show you how Proxy Authentication works in BIP.

Log out and log in as Executive. View the same report.


Ofcourse, this works only with databases that are on 10g and the BIP version has to be or above. For the earlier releases, i would follow up with another article that would show you how to set application contexts in order to use VPD. On the same lines, Mark Rittman has an interesting article on setting up VPD in OBI EE. Interesting read.

I have not gone into details of Proxy Authentication. One can use secure application roles to have one more layer of authentication. For more information on Proxy Authentication, would request you to go through the docs here.

Posted in Bi Publisher | 5 Comments »