Hyperion Essbase 9.3.1 and BI Publisher Connectivity - Using BI EE as a data Bridge

As i was testing the BI EE and Hyperion Essbase connectivity yesterday, i thought i would test out the connectivity of BI Publisher and Hyperion Essbase through BI EE metadata and Answers. I started out under the impression that the performance would either be slow or there would be some hiccups on the way. But to my surprise, the connectivity was so seamless that it gave me the impression that BIP itself is using XMLA to connect to Essbase( i am not sure whether there are plans for supporting XMLA directly from BIP). I used the same cube that i had built yesterday here which contained a ragged hierarchy on the products dimension. As you would probably know, there are 2 ways of connectivity from BI Publisher to BI EE. One is by firing a query on the BI Server and the other is by using an existing Answers report. Lets try both of them today to find out how the connectivity actually works. Lets start first with the BI Server connectivity. So, create a simple BI Publisher report and in the data source use “Oracle BI EE”. And in the query use “Sql Query”.

    

Click on the Query builder and choose your Subject Area ( in our case the SA containing the Hyperion objects). Choose the 3rd level and the measure

    

If you notice, you would get a query like this

 select  PRODUCTS.Gen3,PRODUCTS as Gen3,PRODUCTS,
  Shblog.AMOUNT_SOLD as AMOUNT_SOLD
 from  SH_BSO1.Shblog Shblog,
  SH_BSO1.PRODUCTS PRODUCTS

Unfortunately, BI Publisher is not aware of Hyperion Essbase modelling in BI Server and hence does not encapsulate all the columns within double quotes. The above query would only give an error. So, change the above query to the one below

 select  "PRODUCTS"."Gen3,PRODUCTS" as "Gen3,PRODUCTS",
  Shblog.AMOUNT_SOLD as AMOUNT_SOLD
 from  SH_BSO1.Shblog Shblog,
  SH_BSO1.PRODUCTS PRODUCTS
 where "PRODUCTS"."Gen4,PRODUCTS" is not null

Now, try viewing the data. So far so good.

    

Now all we need to do is to add templates to this report and it works like a breeze.

    

    

Lets try the other means of connectivity which is by using an Answers request as the data source.

    

Add a template to this data source and there you go. A simple BIP report using Essbase as the data source.

    

Very nice!!!

Oracle BI EE 10.1.3.3/2 - Sending Reports to Non-OBI Users - Delivery Manager API of BI Publisher

Another common question that generally comes from end users is “How do we make OBI EE Delivers to email the reports to users who are not part of a Delivery profile?”. Well, there are a variety of ways to achieve this. Let us first list those down

1.   Create a dummy user and assign as many email ids as possible to this dummy user’s delivery profile. While using Delivers, send the reports out to all these users.
2.   Create SA system subject area in BI Administrator and let the subject area be populated from database using Init Blocks. In this case, all the email ids would come from the database.
3.   Use BI Publisher Delivery Manager API to accept multiple email ids as its parameter and send the report to various users.

Today, we shall be looking at the 3rd approach which is easier to implement. Also, this provides us with much more flexibility in delivering the reports to the end users. In order to use this approach, you need to go through the basics of calling Java Classes from Delivers in my blog entry here. For the Delivery Manager API of BI Publisher to work, we need to have the following jars.

a.   Activation.jar - You can get this jar from {OracleBI}\oc4j_bi\j2ee\home\lib
b.   Mail.jar - You can get this jar from {OracleBI}\oc4j_bi\j2ee\home\lib
c.   xdocore.jar - You can get this jar from {OracleBI}\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib
d.   versioninfo.jar - You can get this jar from {OracleBI}\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib
e.   schedulerrpccalls.jar - You can get this jar from {OracleBI}\web\javahost\lib\scheduler

Now open JDeveloper and include the above jars in your classpath. Also, create a Java Class to enter the below code.

package bieesoap;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import oracle.apps.xdo.delivery.DeliveryException;
import oracle.apps.xdo.delivery.DeliveryManager;
import oracle.apps.xdo.delivery.DeliveryPropertyDefinitions;
import oracle.apps.xdo.delivery.DeliveryRequest;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJavaExtension;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobException;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobInfo;

import java.io.File;

import javax.activation.CommandMap;
import javax.activation.MailcapCommandMap;

import oracle.apps.xdo.delivery.local.LocalPropertyDefinitions;

public class burstEmail implements SchedulerJavaExtension{
public burstEmail() {
}

public void run(SchedulerJobInfo jobInfo) throws SchedulerJobException {
try
{
FileInputStream fis = new FileInputStream(jobInfo.getResultSetFile());
DeliveryManager dm = new DeliveryManager();
DeliveryRequest req = dm.createRequest(DeliveryManager.TYPE_LOCAL);
req.addProperty(LocalPropertyDefinitions.LOCAL_DESTINATION, “D:\\Output.pdf”);
req.setDocument(fis);
req.submit();

if (req.getStatus() == DeliveryRequest.STATUS_SUCCESSFUL)
{
MailcapCommandMap mc = (MailcapCommandMap)CommandMap.getDefaultCommandMap();
mc.addMailcap(”text/html;; x-java-content-handler=com.sun.mail.handlers.text_html”);
mc.addMailcap(”text/xml;; x-java-content-handler=com.sun.mail.handlers.text_xml”);
mc.addMailcap(”text/plain;; x-java-content-handler=com.sun.mail.handlers.text_plain”);
mc.addMailcap(”multipart/*;; x-java-content-handler=com.sun.mail.handlers.multipart_mixed”);
mc.addMailcap(”message/rfc822;; x-java-content-handler=com.sun.mail.handlers.message_rfc822″);
CommandMap.setDefaultCommandMap(mc);
req = dm.createRequest(DeliveryManager.TYPE_SMTP_EMAIL);
req.addProperty(DeliveryPropertyDefinitions.SMTP_SUBJECT, “Mail from BI Delivers”);
req.addProperty(DeliveryPropertyDefinitions.SMTP_HOST, jobInfo.parameter(1));
req.addProperty(DeliveryPropertyDefinitions.SMTP_FROM, “BIEEDelivers-Admin@oracle.com”);
req.addProperty(DeliveryPropertyDefinitions.SMTP_TO_RECIPIENTS, jobInfo.parameter(0) );
req.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_TYPE,”application/pdf”);
req.addProperty(DeliveryPropertyDefinitions.SMTP_CONTENT_FILENAME,”Output.pdf”);
req.setDocument(”D:\\Output.pdf”);
req.submit();
req.close();
}
}
catch(Exception ex)
{
throw new SchedulerJobException(1, 1, ex.getMessage());
}

}

public void cancel() {
}

}

      

As you see, what the above code basically does is, it first stores the report in a local destination and then sends that report out to all the users. jobInfo.parameter(0) can accept comma seperated email ids as parameter. Compile the above code and bundle it into a jar file (including all the above 4 jars). Now, put this deployed jar file in {OracleBI}\web\javahost\lib. This will make the jar file to be accessible by delivers. Once this is done, create a simple ibot with PDF as the attachment

      

Now, in the advanced tab of this ibot, call the above class as shown below. This will accept 2 parameters

   1. Comma Seperated Email ids to which you want to send the report to
   2. Your SMTP mail Server

      

Now, you should be able to send emails out to multiple users. You can use it or customize the above code to your convenience. Let me know if anyone needs the jar file, i will post it here.

Oracle BI EE 10.1.3.3/2 - Combining Multiple Excel Report Outputs(Workbooks) to a Single Excel Report - Using BI Delivers, BI Publisher APIs and VB Scripts

I received this question a couple of days back. The question was “Is there any way to combine say 2 or more number of Report Outputs into a single Excel File automatically, instead of manual exports and copy paste?”. This question keeps coming a lot many times but there is no out of the box solution for this. Lets look at one possible approach today. Remember that this is not an easy way and would involve a call to a lot of components. I am blogging about it just to demonstrate the extensibility of BI EE. In order to achieve this we shall be using 2 reports. Our aim is to use BI Delivers and BI Publisher APIs to export both the reports in Excel 2000 format to a folder. And then use another ibot to call a VBScript to combine all the exported reports to a single excel file. All the ibots will be called one after the other.

1.   So, lets start with the Java Code to export the reports in the form of Excel 2000. This Java Code has to be called from an ibot. For more details on how to go about calling Java Classes from ibots check my blog entry here. The code is given below

package bieesoap;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import oracle.apps.xdo.delivery.DeliveryException;
import oracle.apps.xdo.delivery.DeliveryManager;
import oracle.apps.xdo.delivery.DeliveryPropertyDefinitions;
import oracle.apps.xdo.delivery.DeliveryRequest;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJavaExtension;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobException;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobInfo;

import java.io.File;

import oracle.apps.xdo.delivery.local.LocalPropertyDefinitions;

public class ExcelOutput implements SchedulerJavaExtension{
public ExcelOutput() {
}

public void run(SchedulerJobInfo jobInfo) throws SchedulerJobException {
try
{
String OutputFileName;
OutputFileName = “D:\\Work\\Report” + jobInfo.jobID() + jobInfo.instanceID() + “.xls”;
FileInputStream fis = new FileInputStream(jobInfo.getResultSetFile());
DeliveryManager dm = new DeliveryManager();
DeliveryRequest req = dm.createRequest(DeliveryManager.TYPE_LOCAL);
req.addProperty(LocalPropertyDefinitions.LOCAL_DESTINATION, OutputFileName);
req.setDocument(fis);
req.submit();
req.close();
}
catch(Exception ex)
{
throw new SchedulerJobException(1, 1, ex.getMessage());
}

}

public void cancel() {
}

}

So what this basically does is, it accepts the output of an ibot and stores it in D:\Work with a dynamic file name based on the Job ID and the InstanceID (Something like this Report240.xls etc).

2.   Now the next step is to create 2 ibots and each of them should be made to export 2 different files in Excel 2000 format. Ensure that both the ibots call the above java code using a bundled Jar. I have 2 different reports as shown below

      

      

3.   Just save the above 2 ibots and make each of them to call the above Java Code. Once this is done, lets move away from BI Delivers and look at the VB Scripting part which would achieve combining of multiple excel files into one excel file. In order to do this, we shall start with a simple VBA or macro script given below.

Sub MergeExcel()
Dim finalOutput As Workbook
Dim sourceWbk As Workbook
Dim sourceWsh As Worksheet
Dim sourcePath As String
Dim sourceFile As String

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
sourcePath = “D:\work” ‘ change to suit
Set finalOutput = Workbooks.Add(xlWBATWorksheet)
sourceFile = Dir(sourcePath & “\*.xls”, vbNormal)

If Len(sourceFile) = 0 Then Exit Sub

Do Until sourceFile = “”

Set sourceWbk = Workbooks.Open(Filename:=sourcePath & “\” & sourceFile)

Set sourceWsh = sourceWbk.Worksheets(1)

sourceWsh.Copy After:=finalOutput.Worksheets(finalOutput.Worksheets.Count)

sourceWbk.Close False

sourceFile = Dir()

Loop
finalOutput.Worksheets(1).Delete
finalOutput.SaveAs (”D:\work\Test.xls”)
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

So, what this basically does is it checks the directory D:\Work and combines all the excel files it finds in that directory into a single file Test.xls.

4.   Remember the above is a VBA Script. BI Delivers can only call VB Scripts. Now, in order to call VBA Script, what we shall do is to create a dummy excel file called Final.xls in D:\(ensure that this excel file is not in the same directory as the above export files) and we shall create a macro using the above code in this Excel file. Our aim is to make the macro to run as soon as the Excel file is opened.

      

      

      

Now, in order to make this macro to run as soon as the Excel is opened, right on the Excel icon just above file menu and click on view code. Go to the Workbook and add the below code.

      

      

Private Sub Workbook_Open()
Run “MergeExcel”
End Sub

Then just save this file. Also, ensure that you have the security of this Excel file set to Low. Now open a text editor and add the below VB Script which will open the Excel file.

Dim app
Set app = createobject(”Excel.Application”)
app.visible = false
app.Workbooks.Open(”D:\Final.xls”)
app.Quit
Set app = Nothing

Save this file in {OracleBI}\server\Scripts\common folder.

5. Now create a third ibot which would just call the above VBScript.

      

6.   Now schedule all the 3 ibots to execute one after the other (you can either use ibot chains or just schedule them to execute one after the other). Its better to use ibot chains since they are synchronous in nature. Now you should see 3 excel reports in the D:\Work directory, 2 being the actual reports and the third being the Test.xls which would be a combination of the above 2.

      

      

But just remember the above would work only in windows since VBScript would not work in a non-windows environment

Oracle BI EE 10.1.3.3/2 - Adding Watermarks to Delivered PDF Documents - Using PDF Merger API of BI Publisher

In the previous blog entry we have seen how to go about delivering documents to a local file system using the Delivery Manager API of BI Publisher. Along the same lines, lets look at another very interesting feature which is the PDF WaterMarking feature. Many a time you might want to watermark your documents (especially PDFs) when you send out to multiple users via iBots. Watermarking is not available out of the box for BI EE. This is where BI Publisher can come to our rescue ( i always wonder how both BI EE and BI Publisher complement each other some times). In order to do watermarking, we need to use the PDF Merge API of BI Publisher and then call it from ibots. Lets look at the list of steps below. In the example below, i shall be adding an Oracle Logo as the watermark to a delivered BI EE PDF document.

1.   The first thing that you need is the following sample java code which combines your PDF output with a local image. This has to be included as part of the scheduler’s run method since the remote procedure call will only call this run method.

package bieesoap;

import java.io.FileInputStream;
import oracle.apps.xdo.common.pdf.util.PDFDocMerger;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJavaExtension;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobException;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobInfo;

import java.io.File;

import java.io.FileOutputStream;

import oracle.apps.xdo.delivery.DeliveryManager;
import oracle.apps.xdo.delivery.DeliveryRequest;
import oracle.apps.xdo.delivery.local.LocalPropertyDefinitions;

public class PDFMerge implements SchedulerJavaExtension{
public PDFMerge() {
}

public void run(SchedulerJobInfo jobInfo) throws SchedulerJobException {
try
{
FileInputStream[] inputStreams = new FileInputStream[1];
inputStreams[0] = new FileInputStream(jobInfo.getResultSetFile());
FileOutputStream outputStream = new FileOutputStream(”D:\\BIPAPI\\Output4.pdf”);

PDFDocMerger docMerger = new PDFDocMerger(inputStreams,outputStream);
FileInputStream imagePath = new FileInputStream(”D:\\BIPAPI\\oraclelogo_small.gif”);
float[] rct = {300f, 500f, -1f, -1f};
docMerger.setImageWatermark(imagePath, rct);
docMerger.mergePDFDocs();
docMerger = null;

imagePath.close();
outputStream.close();
}
catch(Exception ex)
{
throw new SchedulerJobException(1, 1, ex.getMessage());
}

}

public void cancel() {
}

}

As you see above, what this does is, it calls the PDFDocMerger class. This class accepts 2 inputs, a stream of pdf inputs and a pdf output. It also accepts the setImageWatermark property which will set the watermark to the pointed image.

2.   Compile this class and bundle this as a jar along with the dependent jars (schedulerrpccalls.jar, versioninfo.jar and xdocore.jar). For more details on how to do this refer my blog entry here and here

3.   Now go to delivers and create an ibot. This ibot should contain a report exported in the PDF format. This PDF of the report will be watermarked with Oracle Logo.

      

      

4.   Once this is done, now go to your Advanced tab and call the class that we created above.

      

5.   Now save this ibot. As soon as this is done, you would notice that a new file called output4.pdf under D:\\BIPAPI would have been created and this would contain both the report and the watermark image.

      

Just add delivery manager API code to the above class and now you would be in a position to send the watermarked PDF to other users.This feature can be extended to a lot of other features like combining multiple reports into a single PDF etc.

Oracle BI EE 10.1.3.3/2 - Calling BI Publisher Java APIs from iBots - Storing reports in File System Using Delivers and BI Publisher Scheduler

If you had gone through my previous blog entry here i would have talked about the means of storing BI EE reports on a periodic basis using Java Scripts. But one of the main problems with this is that it would not work in an Unix environment. So, now lets look at an approach to store the BI EE report outputs using Java APIs of BI Publisher and Delivers. But as a pre-requisite you need to go through the above mentioned blog entry to understand how Java Classes are called from BI Delivers. Now in this example, we shall be using BI Publisher Delivery Manager Java APIs and will be calling these APIs from iBots. Lets look at the steps one by one.

1.   The first step to achieve this is to create the Java Class containing the relevant code. I have used JDeveloper 10.1.3.2. In order for the below code to work you need to have 3 jar files included. They are xdocore.jar, versioninfo.jar and schedulerrpccalls.jar. You can find xdocore.jar and versioninfo.jar in {OracleBI}\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib. And you can find schedulerrpccalls.jar in {OracleBI}\web\javahost\lib\scheduler. Now compile the below given code and bundle it as a jar file. To know how to bundle it as a jar check the blog entry mentioned above. Remember, while deploying it as a jar you need to include the above mentioned 3 dependency jars too.

package bieesoap;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import oracle.apps.xdo.delivery.DeliveryException;
import oracle.apps.xdo.delivery.DeliveryManager;
import oracle.apps.xdo.delivery.DeliveryPropertyDefinitions;
import oracle.apps.xdo.delivery.DeliveryRequest;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJavaExtension;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobException;
import com.siebel.analytics.scheduler.javahostrpccalls.SchedulerJobInfo;

import java.io.File;

import oracle.apps.xdo.delivery.local.LocalPropertyDefinitions;

public class LocalCopy implements SchedulerJavaExtension{
public LocalCopy() {
}

public void run(SchedulerJobInfo jobInfo) throws SchedulerJobException {
try
{
FileInputStream fileInputStr = new FileInputStream(jobInfo.getResultSetFile());
DeliveryManager delMan = new DeliveryManager();
DeliveryRequest delReq = delMan.createRequest(DeliveryManager.TYPE_LOCAL);
delReq.addProperty(LocalPropertyDefinitions.LOCAL_DESTINATION, “D:\\BIPAPI\\Output.pdf”);
delReq.setDocument(fileInputStr);
delReq.submit();
delReq.close();
}
catch(Exception ex)
{
throw new SchedulerJobException(1, 1, ex.getMessage());
}

}

public void cancel() {
}

}

This is how the above code works. When we include a Java Class from an ibot, the ibot uses the Java Host service to make a remote procedure call to the Run method of the class specified. jobInfo object would get the output of the ibot (say a PDF file) and will pass it on to the Delivery manager API of BI Publisher. The delivery manager API then makes a call to the file system to store the PDF file to a local directory.

      

      

2.   Copy the deployed jar file to your java host lib path(check the above blog entry to know where to specify this path).

3.   Now create an ibot and include any report content. Ensure that you are making the output type to be of PDF since we are making the above output file name to be output.pdf. Go to the Advanced properties and add a Java Class as shown below.

      

      

      

      

      

This can be very useful for advanced integration scenarios. You can even call the BI Publisher FOProcessor API to actually create the BIP reports and can use Delivers to deliver them. There are lots of different functionalities where this can be very helpful.

Oracle BI Publisher 10.1.3.3.1 - Creating Applications using JDeveloper 11g and Web Service SOAP APIs

I had some time today to see how easy it is to create a sample application that would basically call a BI Publisher web service and then run a report. Well if you had seen my previous blog entry on this here, i would have mentioned that i was not able to get the sample code provided in the documentation to work. Well, so far still i could not figure out the additional jars required. So we would be using a different approach here to test out these web services. Before going further, let me first list out the components and their versions that i would be using for this

1.    JDeveloper Tech Preview 3 - Yes this is a new year gift(though released by the end of December). JDev has never been any better. So many changes in UI and features and many more are coming in the 11g release.
2.    BI Publisher 10.1.3.3.1 (without the latest patchset).

Now, the first thing that we need to do for testing this to see whether we are able to access the WSDL of the BIP web services. To check this just open a browser and enter the below URL.

      http://localhost:9704/xmlpserver/services/PublicReportService?WSDL

      

Once this is done open up the JDev Tech Preview 3 Console and start with creating a new application (without any template). In my case the application and all the project names would be bipsoap.

      

Now create a new web service proxy. Here we would be entering all the details regarding the BIP WSDL.

      

Use JAX-RPC as the Web Service Client type and enter the WSDL URL that we used above. Do not make any changes to the port end points (every other step will be having the default values. No changes required).

      

Now this would automatically create a java file containing the relevant code. Now go to the main method of this java file and in the Try block enter the below piece of code

// @GeneratedBlockBegin (value={”oracle.j2ee.ws”} );
samplesoap.proxy.PublicReportServiceClient myPort = new samplesoap.proxy.PublicReportServiceClient();
// @GeneratedBlockEnd (value={”oracle.j2ee.ws”} );
String userName = “Administrator”;
String passWord = “Administrator”;

System.out.println(”calling ” + myPort.getEndpoint());
System.out.println(myPort.validateLogin(userName,passWord));

ReportRequest repReq = new ReportRequest();
ReportResponse repRes = new ReportResponse();

repReq.setAttributeFormat(”pdf”);
repReq.setAttributeLocale(”en-US”);
repReq.setAttributeTemplate(”World Sales”);
repReq.setReportAbsolutePath(”/Sales Manager/World Sales/World Sales.xdo”);

repRes = myPort.runReport(repReq,userName,passWord);
System.out.println(repRes.getReportContentType());

byte[] binaryBytes = repRes.getReportBytes();
OutputStream out = new FileOutputStream(”D:\\out.pdf”);
out.write(binaryBytes);
out.close();
System.out.println(”Success for Run Report”);

What this code does it runs the World Sales report (present under Sales Manager -> World Sales) and exports it into PDF format in the local D drive. Lets try to understand the code above.

   samplesoap.proxy.PublicReportServiceClient myPort = new samplesoap.proxy.PublicReportServiceClient();

The above instantiation opens up myPort which would your service handle. So, in JDeveloper if you type myPort. and press Ctrl+Space you can find all the methods that have been exposed in the BIP webservice.

      

Now compile this report and run it. If you get Success for Run Report in the output window, then go to your D drive. You would find out.pdf which would contain the report in a PDF format.

      

      

Very simple but again can be very useful when you are doing custom integration. You dont have to have any more of xdo related jar files in your code (Java APIs). All you need is the service handle!!!

Oracle BI Publisher and BI EE - Invisible Admin Tab!!!

I had a couple of users today who had sent me an email informing me about a strange issue while installing BI EE. Call it coincidence, we had the same question pop up in the forums today as well. I do not have a complete solution to this problem yet. But i thought this is worthwhile to blog about since there are chances that other users might come across a similar issue. The problem was, after the default install of 10.1.3.3.1/0(using OC4J), the users are unable to perform any kind of administration on BI Publisher. i.e if they log into BI Publisher as Administrator, they do not see the “Admin” tab!!!. There can be multiple reasons why this can happen. But following should give you a general set of guidelines on where to look at to see where the source of the problem is.

When you install BI EE, the BI Publisher that also gets installed would be configured to use BI Server security. So, in order to access BI Publisher you need to ensure that BI Server and the Presentation Services(if you want reports from Answers as data source) are up and running. There are 3 different files that are worth a mention here.

1.   {OracleBI}\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml

2.   {OracleBI}\xmlp\XMLP\Admin\Security\principals.xml

3.   {oc4j_bi}\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\xmlp-server-config.xml

Now, each of these files store certain parameters that would be used in the security and also in identifying the reports. So, if you have an install where your BI Publisher does not show you the Admin tab, try to check the following.

1.   Open the file {OracleBI}\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml using a text editor. Go to {OracleBI}\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml. Check whether you have SUPERUSER_PASSWORD and SUPERUSER_USERNAME set. Also check whether SECURITY_MODEL property is properly set.

      

Edit the property SECURITY_MODEL and change it to XDO. i.e. for example <property name=”SECURITY_MODEL” value=”XDO”/>. This would basically change the security model from BI Server security to BI Publisher Security.

2.   Restart OC4J and see whether you are able to log in as Administrator and see the Admin tab. If you are able to see the Admin tab then there is some problem with BI Publisher not being able to connect to BI Presentation Services in order to leverage the security. By default, when we install BI EE, BI Publisher would use BI Server security. By changing it to XDO we are making it to leverage local BI Publisher security instead of BI EE security. In the {OracleBI}\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml, try placing the ip instead of the hostname. For example, <property name=”BI_SERVER_SECURITY_URL” value=”jdbc:oraclebi://vejanaki-pc:9704/”/>. Default install will take the hostname and in some cases BI Publisher does not seem to recognize the hostname.

3.   Go to {OracleBI}\xmlp\XMLP\Admin\Security\principals.xml. Check whether you the Administrator Role mapping is enabled there.

The above are some checks that should get you started. If others have any other comments feel free to post them here. In the meantime, i am off for a mini vacation to celebrate the festival of lights!!!. Should be back next week with more posts.

Oracle BI Publisher 10.1.3.3/2 - Web Services using APEX

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.

Oracle BI Publisher 10.1.3.3.1 - Web Services - First Impressions

I was going through the new BI Publisher feature of 10.1.3.3.1 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
      attributeFormat
      attributeLocale
      attributeTemplate
      Parameter Array
      reportAbsolutePath

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.

Oracle BI EE 10.1.3.3.1 - New release - New Features

Well, i picked this up from Abhinav’s blog here. Looks like the 10.1.3.3.1 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 10.1.3.3 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 10.1.3.3 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 10.1.3.3.1 - Well, now one does not have to go on the look out for ODBC drivers for various databases. Oracle BI EE 10.1.3.3.1 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 10.1.3.3.1 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 10.1.3.3.1

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.