Business Intelligence – Oracle

Archive for the ‘APEX’ Category

Oracle BI EE 10.1.3.3/2 – APEX Integration using Web Services – Triggering Ibots

Posted by Venkatakrishnan J on May 7, 2008

As you might probably be aware, APEX (Application Express) has support for web services. One can call web services and can create several sequential processes out of them. There can be lots of use cases for these web service calls. One of them is to have a custom integration of BI EE within APEX environment. If you had worked on APEX and BI Publisher integration (using Web Services) as documented here excellently by Tyler Muth you would have noticed that APEX can identify BI Publisher web services out of the box since APEX web services have only a single binding i.e all the methods can be identified by using a single root service. But in the case of BI EE, it produces a multiple binding WSDL. APEX does not support multiple binding web services currently. So, the question is how do we go about calling BI EE methods from within APEX? Thats where a tool called SOAP UI comes to our rescue. I came across this tool while going through Tyler’s blog entry above and found this free tool to be very useful to identify end points of SOAP requests. Till now, i was using another tool called Mindreef SOAPScope. Though mindreef is also good, the major drawback was that they did not give a free version. They only provide you an evaluation copy. Use the SOAP UI tool and create a project using the BI EE WSDL below.

http://localhost:9704/analytics/saw.dll?WSDL

If you want you can also include the BI Publisher WSDL to know the difference between a single binding and Multi binding web services.

    

Now, just to get us started, lets try to authenticate into BI EE using the Logon Method of the SAWSession service and then use that session to execute an ibot by using the excuteIBotnow method of the IBotService from within Application Express. Since APEX, does not support multi binding web services, we would have to treat each and every service (different bindings) of the BI EE WSDL as a seperate WSDL in itself. So, go to an APEX application and click on Shared Components -> Logic -> Web Service References. Now click on create and do not search for the WSDL in the UDDI. In the next step, instead of entering the WSDL click on create Web Service Reference Manually. Remember, we would have to create one reference for each and every web service. Following are the URLs that you would have to use for each service


XMLViewService - http://localhost:9704/analytics/saw.dll?SoapImpl=xmlViewService
SecurityService - http://localhost:9704/analytics/saw.dll?SoapImpl=securityService
WebCatalogService - http://localhost:9704/analytics/saw.dll?SoapImpl=webCatalogService
SAWSessionService - http://localhost:9704/analytics/saw.dll?SoapImpl=nQSessionService
JobManagementService - http://localhost:9704/analytics/saw.dll?SoapImpl=jobManagementService
ReplicationService - http://localhost:9704/analytics/saw.dll?SoapImpl=replicationService
ReportEditingService - http://localhost:9704/analytics/saw.dll?SoapImpl=reportService
IBotService - http://localhost:9704/analytics/saw.dll?SoapImpl=ibotService
HTMLViewService - http://localhost:9704/analytics/saw.dll?SoapImpl=htmlViewService
MetadataService - http://localhost:9704/analytics/saw.dll?SoapImpl=metadataService

In our case since we want to authenticate and trigger an ibot, we would have to use 2 services. They are SAWSession service and IBotService. While creating a web service reference for the saw session service, use the below XML (you can obtain this from the SOAP UI tool).


<soapenv:Envelope xmlns:soapenv="<a href="http://schemas.xmlsoap.org/soap/envelope/">http://schemas.xmlsoap.org/soap/envelope/</a>" xmlns:v5="com.siebel.analytics.web/soap/v5">
   <soapenv:Header/>
   <soapenv:Body>
      <v5:logon>
         <v5:name>Administrator</v5:name>
         <v5:password>welcome1</v5:password>
      </v5:logon>
   </soapenv:Body>
</soapenv:Envelope>

In the above XML, Administrator and welcome1 are the username/passwords for my BI EE instance. If you want you can replace the actual values with #ITEMNAME# to populate them dynamically based on page/application items.

    

Remember, to enter a collection name for storing the reponse. This is very important since we would have to extract the session id and pass it on to another service. In my case, i had used WSDL_COLLECTION as the name of the collection (Dont worry about creating these collections. These are automatically created. All you need to do is to enter the name. you can get what is stored in the collection by querying the APEX_COLLECTIONS view). Similarly use the below XML for the executeiBotnow method of the IBotService.

 <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v5="com.siebel.analytics.web/soap/v5">
<soapenv:Header/>
<soapenv:Body>
<v5:executeIBotNow>
<v5:path>/users/administrator/_ibots/Test</v5:path>
<v5:sessionID>#F101_SESSIONID#</v5:sessionID>
</v5:executeIBotNow>
</soapenv:Body>
</soapenv:Envelope> 

Here, replace the v5:path tag value with the ibot path that you need to execute. Since session ids are dynamic, we would have to reference an application level item (F101_SESSIONID) which would be populated by the SAWSession service. Again, enter a collection name like WSDL_COLLECTIONSCHED for storing the result. Once these 2 web service references have been created, create a page process to call the Logon Method of the SAWSession service.

    

Make this service to be conditionally called on the submit of a button (Login Button). Whenever each of the service is called from within APEX, the result is stored in a collection. Since this stores the result of a web service response, it would be stored as a CLOB in the CLOB001 column of the APEX_COLLECTIONS view. Typically the response of the 1st service would be like the one shown below.

<?xml version="1.0" encoding="UTF-8" ?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:sawsoap="com.siebel.analytics.web/soap/v5">
<soap:Body>
<sawsoap:logonResult>
<sawsoap:sessionID xsi:type="xsd:string">ihdcqbtmm8q06ii2opb7qhh44npfqh5v0liad5izOr07UFe9W00</sawsoap:sessionID>
</sawsoap:logonResult>
</soap:Body>
</soap:Envelope>

As you see the sessionid would be embedded in the SOAP response. The most common method to extract the session id is to write a simple XPath query with the corresponding namespaces. But in our case, we would not be doing that since the resultant SOAP response is a constant. So, let write a simple query as shown below to extract the session id from the above XML.

SELECT SUBSTR(DBMS_LOB.SUBSTR(CLOB001,4000,1),392,INSTR(DBMS_LOB.SUBSTR(CLOB001,4000,1),'</sawsoap:sessionID>')-392) FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = 'WSDL_COLLECTION'

Use the above query to populate the F101_SESSIONID application level item. Once this is done, create another page process to call the executeIBot method of the IBotservice. Make this process to be executed after the execution of SAWSessionservice page process. Now this would automatically trigger the ibot.

    

This should give you an idea of how to go about calling BI EE web services from APEX. Lets look at more complex scenarios lin future blog entries.

Advertisements

Posted in All Posts, APEX, OBI EE Plus | 3 Comments »

Oracle BI EE 10.1.3.3/2 – Executing iBots from Dashboards/Answers – Sending Emails to Administrators

Posted by Venkatakrishnan J on March 17, 2008

In my previous blog entry here, i had shown you how to go about calling command line utilities(for Purging the Cache) from Dashboards/Answers. Along the same lines, lets look at another possible use case of the procedure that was used there today. Today, we shall see how to go about calling the ibots from Dashboards/Answers. i.e our aim is to execute the ibots directly from Dashboards by clicking a single URL. This would be useful in a lot of situations wherein you would like to send emails to Administrators whenever the database server is down or when there is some error in some of the reports. In order to achieve this, the first step is to create a simple ibot which would basically send out an email to the Administrator. Also, this ibot would contain a report (generally a diagnostics report). In our case, lets include a simple report.

      

      

Once this is done, find out the jobid of this ibot from the Job Manager console.

      

Now, create a batch file to call this ibot on an on-demand basis(on unix you would have to create a shell script). This batch file would basically have this command.

saschinvoke -u Administrator/Administrator -j 1

      

The advantage of the above batch file is that you can also pass parameters (jobids etc) dynamically to it. The next step is to create a simple DBMS_SCHEDULER job to call this utility.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘saschinvoke1’,
job_type => ‘EXECUTABLE’,
job_action => ‘D:\Oracle\OracleBI\server\Bin\saschinvoke.bat’,
repeat_interval => ‘FREQ=YEARLY’,
enabled => TRUE
);
END;

Now, create another procedure to run this job.

CREATE OR REPLACE PROCEDURE PRO_INVOKEIBOTS IS
BEGIN
DBMS_SCHEDULER.RUN_JOB(‘saschinvoke1’);
HTP.PRINT(‘<html>’);
HTP.PRINT(‘<head>’);
HTP.PRINT(‘<meta http-equiv=”Content-Type” content=”text/html”>’);
HTP.PRINT(‘<title>EMAIL Successfully Sent</title>’);
HTP.PRINT(‘</head>’);
HTP.PRINT(‘<body TEXT=”#000000″ BGCOLOR=”#FFFFFF”>’);
HTP.PRINT(‘<h1>Email Successfully Sent</h1>’);
HTP.PRINT(‘</body>’);
HTP.PRINT(‘</html>’);
END;

This is the procedure which we shall expose to be called from a URL using the Pl/SQL gateway. In order to do that, grant execute privilege on the above procedure to ANONYMOUS and add the procedure to the list of accepted and executable packages by the pl/sql gateway.

CREATE OR REPLACE function wwv_flow_epg_include_mod_local(procedure_name in varchar2)
return boolean is
begin
if upper(procedure_name) in (‘HR.PRO_INVOKEIBOTS’) then
return TRUE;
else
return FALSE;
end if;
end wwv_flow_epg_include_mod_local;

GRANT EXECUTE ON PRO_INVOKEIBOTS to ANONYMOUS;

Once this is done, create a URL link in the dashboard to call the below URL

http://localhost:7779/apex/hr.pro_invokeibots

      

Clicking on the above URL should automatically send an email to the Administrator.

      

Posted in All Posts, APEX, OBI EE Plus | 5 Comments »

Oracle BI EE 10.1.3.3/2 – BI Server Cache Purge from Dashboards – Using Pl/sql Gateway and DBMS_SCHEDULER package

Posted by Venkatakrishnan J on March 13, 2008

I came across a good question on forums the other day. The question is how do we purge the BI Server cache from Answers or Dashboards. Basically, the user wanted to have a link clicking on which should enable him to purge the cache. Currently there is no out of the box way to achieve this since cache purging is done through some ODBC calls. It would have been much easier if we had a go URL to automatically purge the cache. But unfortunately there is none (if anyone comes across a GO URL or some other method of doing this feel free to share in the comments section). Lets look at a round about approach today to achieve this. Remember to test this out completely since it involves a lot of dependency across multiple components. Our methodology is based on below list of steps.

1.   Create a batch file which would make a call to nqcmd.exe to purge the cache.
2.   Call this batch file using DBMS_SCHEDULER package of Oracle Database.
3.   Create a package which would call the above dbms_scheduler package and then would do HTP.P prints to a static Html file.
4.   Expose this package to the end users using Pl/SQL gateway
5.   Call the Pl/SQL gateway URL from dashboards.

One of the main reasons of using the above approach is that this gives us a lot of flexibility. We shall be seeing more blog entries next week based on this approach. Lets start first with creating a simple batch file. This batch file would have the following command.

D:\Oracle\OracleBI\server\Bin\nqcmd.exe -d AnalyticsWeb -u Administrator -p Administrator -s D:\CachePurge.txt -o D:\Output.txt

As you see what this batch file does is it calls nqcmd.exe to purge the cache. NQCMD.exe is basically an utility which can make ODBC calls. CachePurge.txt file would contain the below command.

{call SAPurgeAllCache()};

      

      

The next step is to create a DBMS_SCHEDULER job to make a call to this batch file. DBMS_SCHEDULER is a database package which has a variety of features. One of its features is to make a call to command line utilities or shell scripts. In order, to use this package you need to ensure that that Job Scheduler service of the database (not BI EE) to be up and running. For all the database packages and procedures i have used the HR schema.

      

The job that you need to create is as shown below.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘saschinvoke1’,
job_type => ‘EXECUTABLE’,
job_action => ‘D:\Oracle\OracleBI\server\Bin\saschinvoke.bat’,
repeat_interval => ‘FREQ=YEARLY’,
enabled => TRUE
);
END;

      

Now, once this is done, create another pl/sql procedure to run this job on demand. The procedure is shown as below

CREATE OR REPLACE PROCEDURE PRO_INVOKECACHEPURGE IS
BEGIN
DBMS_SCHEDULER.RUN_JOB(‘saschinvoke1’);
HTP.PRINT(‘<html>’);
HTP.PRINT(‘<head>’);
HTP.PRINT(‘<meta http-equiv=”Content-Type” content=”text/html”>’);
HTP.PRINT(‘<title>CACHE SUCCESSFULLY PURGED</title>’);
HTP.PRINT(‘</head>’);
HTP.PRINT(‘<body TEXT=”#000000″ BGCOLOR=”#FFFFFF”>’);
HTP.PRINT(‘<h1>List of Employees</h1>’);
HTP.PRINT(‘</body>’);
HTP.PRINT(‘</html>’);
END;

This is the procedure which we shall execute via the pl/sql gateway. Thats why we are using HTP.P procedure inside the above procedure.

      

Now, the next step is to configure the Pl/SQL gateway. In order to configure Pl/SQL gateway use a 11g database and configure APEX. That will automatically configure the pl/sql gateway. For more details on this check my blog entry here. Once the above procedure has been compiled, lets grant execute on this procedure to anonymous so that everyone can execute this (Remember this has security implications. Try this only on a test instance. To use this on production you need to make this more secure by granting access on this only to relevant users).

GRANT EXECUTE on PRO_INVOKECACHEPURGE to ANONYMOUS;

Once this is done, ensure that you have added this procedure to the list of accepted and accessible procedures by adding it to wwv_flow_epg_include_mod_local function.

CREATE OR REPLACE function wwv_flow_epg_include_mod_local(procedure_name in varchar2)
return boolean is
begin
if upper(procedure_name) in (‘HR.PRO_INVOKECACHEPURGE’) then
return TRUE;
else
return FALSE;
end if;
end wwv_flow_epg_include_mod_local;

Now, test this procedure using the URL as shown below.

http://localhost:7779/apex/hr.pro_invokecachepurge

      

Now, all we need to do is to include this URL in dashboards by using the below in a URL object.

      

This is a very round about way of achieving such a small functionality. One of the main reasons why i have used the above approach is to introduce you to the above method. We shall see much more complex uses of the above approach in future blog entries.

Posted in All Posts, APEX, OBI EE Plus | 8 Comments »

Oracle BI EE 10.1.3.32 – Displaying BLOB images – Using Pl/SQL Gateway

Posted by Venkatakrishnan J on December 10, 2007

I had received a couple of emails last weekend on how to go about displaying images stored inside an Oracle Database as a BLOB field in OBI EE. Well, i have had this question asked before by some customers with whom i had interacted in the past. So, i thought of blogging about it so that others can use the same. Before proceeding further, just a word of notice, BLOB images are not directly supported in OBI EE. i.e. One cannot import these fields as normal fields in the repository and then make them display in BI EE. So, in order to display the BLOB images in OBI EE, we would be using a feature of Oracle Database called as “Pl/SQL gateway”. So, what is a pl/sql gateway? To keep it simple, PlSQL gateway is a feature wherein one can expose Oracle Pl/SQL packages to a web browser. It is similar to a HTTP server but would be using Pl/SQL packages. A very good example of a sample application that uses plsql gateway is the Application Express 3.0. If you had installed 11g, you would have noticed that APEX 3.0 gets installed by default and also the Pl/SQL gateway gets configured by default. One of the main reasons of using PlSQL gateway is to have a mechanism wherein we can display all BLOB images over a web browser. So, basically we would be using the PlSQL gateway a call a procedure which in turn would download a stored BLOB image. In the example to follow, i would be using the 11g plsql gateway and APEX 3.0. If we configure APEX 3.0, the gateway also gets configured. To configure APEX 3.0 in 11g, there are 2 simple steps

1.   Run the @apxconf.sql (under {ORACLE_HOME}/apex) as Sys. It would ask you for a port and password. In my case, i have used 7779 as the port.

      

2.   Unlock the Anonymous Account.

      ALTER USER ANONYMOUS ACCOUNT UNLOCK

Now, you should be able to log into APEX using this URL http://localhost:7779/apex.

      

This would mean that pl/sql gateway is working. If you watch closely, the URL would have been modified to http://localhost:7779/apex/f?p=1232353. So, what this means is that APEX by default calls a procedure named “f” which accepts parameter p. So, lets test this out by compiling a sample procedure (which would print employee details. I have taken this directly from the docs to keep it simple) and by calling this procedure from the gateway. The procedure is given below and i am compiling this from a schema called blob1.

 CREATE OR REPLACE PROCEDURE print_employees IS
CURSOR emp_cursor IS SELECT last_name, first_name FROM hr.employees ORDER BY last_name;
BEGIN
HTP.PRINT(‘<html>’);
HTP.PRINT(‘<head>’);
HTP.PRINT(‘<meta http-equiv=”Content-Type” content=”text/html”>’);
HTP.PRINT(‘<title>List of Employees</title>’);
HTP.PRINT(‘</head>’);
HTP.PRINT(‘<body TEXT=”#000000″ BGCOLOR=”#FFFFFF”>’);
HTP.PRINT(‘<h1>List of Employees</h1>’);
HTP.PRINT(‘<table width=”40%” border=”1″>’);
HTP.PRINT(‘<tr>’);
HTP.PRINT(‘<th align=”left”>Last Name</th>’);
HTP.PRINT(‘<th align=”left”>First Name</th>’);
HTP.PRINT(‘</tr>’); FOR emp_record IN emp_cursor LOOP HTP.PRINT(‘<tr>’);
HTP.PRINT(‘<td>’ || emp_record.last_name || ‘</td>’);
HTP.PRINT(‘<td>’ || emp_record.first_name || ‘</td>’); END LOOP;
HTP.PRINT(‘</table>’); HTP.PRINT(‘</body>’); HTP.PRINT(‘</html>’); END;

      As you see what the above procedure does is, it prints out all the employee details. HTP is a system package to make browser calls. Now lets call this procedure from the URL using the same syntax as above. The URL would look like this

      http://localhost:7779/apex/blob1.print_employees1

      

What happened? Why do we get a Error:404 page not found error. This means that the ANONYMOUS account(ANONYMOUS should have this privilege since we are not giving any login details) does not have a execute privilege on this package. So, lets give the privilege and try that out.

      

      

What happened now? We are geting a Error:403 Page forbidden error. This means that we are able to access the package but pl/sql gateway does not allow us to execute this. In order to make plsql gateway to allow this procedure to execute, we need to add our procedure to the list of allowed procedures in the wwv_flow_epg_include_mod_local function. Use the below function to add out procedure to the list

CREATE OR REPLACE function wwv_flow_epg_include_mod_local(procedure_name in varchar2)
return boolean is
begin
if upper(procedure_name) in (‘BLOB1.PRINT_EMPLOYEES1’) then
return TRUE;
else
return FALSE;
end if;
end wwv_flow_epg_include_mod_local;

Thanks to the APEX blogs here and here for giving the details on the above function. Now lets compile the above function as FLOWS_03000 and see what happens.

      

      

So, this time you would be seeing the list of employees in a web browser. Now that we are sure about the list of steps to configure PlSQL gateway to run desired procedures lets look at how to go about configuring the same for downloading BLOB images. The first step is to create a table containing an id field and corresponding image BLOB field. If you are not sure about the insert syntax for BLOB’s i would recommend using a shareware utility called as OracleLOB Editor. In my case, i have created a table having 3 fields

   ID   FILE_NAME   IMAGE_BLOB

And i have inserted images into my IMAGE_BLOB field. The next step is to write a procedure which can download the images. I will be using the procedure below (taken from the docs here

CREATE OR REPLACE procedure custom_image_display(p_imageid in number) as
v_mime varchar2(255);
v_length number;
v_file_name varchar2(2000);
Lob_loc BLOB;
BEGIN
select ‘image/gif’, image,file_name,dbms_lob.getlength(image)
into v_mime,lob_loc,v_file_name,v_length
from hr.blob_image
where id = p_imageid;

— set up HTTP header

— use an NVL around the mime type and
— if it is a null set it to application/octect
— application/octect may launch a download window from windows
owa_util.mime_header( nvl(v_mime,’application/octet’), FALSE );

— set the size so the browser knows how much to download
htp.p(‘Content-length: ‘ || v_length);
— the filename will be used by the browser if the users does a save as
htp.p(‘Content-Disposition: attachment; filename=”‘ || v_file_name || ‘”‘);
— close the headers
owa_util.http_header_close;
— download the BLOB
wpg_docload.download_file( Lob_loc );
end custom_image_display;

Now compile this procedure and grant execute privilege to the anonymous user. Also, add this procedure to the wwv_flow_epg_include_mod_local function as mentioned above. Now test the procedure using the below URL

   http://localhost:7779/apex/blob1.custom_image_display?p_imageid=1

      

      

In your case, it might display the image or would show a pop up to download the image. If you get either of them then the Procedure and the PlSQL gateway are working fine. Now go to BI EE Answers and we would be using the same technique to display the images as mentioned in my previous blog entry here.

Lets start with creating a sample report. We shall include Region, ImageID (a running number to pass the id to the procedure above) and the image column.

      

In the image column, enter the following formula and change the data format to HTML.

      ‘<IMG SRC=”http://10.177.62.122:7779/apex/blob1.custom_image_display?p_imageid=’||CAST(RCOUNT(GEOGRAPHY.REGION_NAME) AS CHAR)||'” WIDTH=”41″ HEIGHT=”41″ BORDER=”0″ ALT=”BLOB IMAGES”>’

      

Now, if you view the report, you shall see the blob images being rendered directly within BI EE.

      

Posted in All Posts, APEX, OBI EE Plus | 5 Comments »

Oracle BI Publisher 10.1.3.3/2 – 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 »