Wednesday 29 February 2012

Creating Applications in OBIEE using DAD

OBIEE is a great business intelligence tool but it is a one way highway. Users can use OBIEE to see the intelligence in the data but what if users want to put a few things of their own. I am presenting a mechanism to make OBIEE a 2 way highway
 
Let me present an example.
 
Consider the following few parameters to measure business
 
  1. Sales
  2. Unused inventory
  3. Increase in customer base
  4. Profit margin
  5. Revenue compared to previous quarter
 
Let’s say the user wants to create thresholds which tell him that something has gone bad in the system at some point in time. One example of such a threshold could be a combination of
 
  1. unused inventory > $5000
  2. sales < $6000.
The user might want to create many more similar thresholds using any of the above 5 parameters and might want to edit them whenever required.
 
This can obviously be done using writeback of OBIEE but it gives us free text boxes and we normally have a requirement where we are often requested to use drop downs or shuttles.
 
To achieve this, we can use DAD. Dads are Database access descriptors and are the backbone of the architecture of Oracle APEX
 
We can use webservices for this, presented here and here, but I love the DAD approach because it doesnot involve the gyration of using JDeveloper and involves lesser javascript.
 
The drawback of using DAD is that the page reloads once. The advantage is lesser effort J
 
Ok, now let’s start digging
 
We can use DAD in 2 possible ways
 
  1. Express edition of Oracle 10G and Oracle 11G comes with an inbuilt APEX. This APEX uses the XML HTTP server to cater web requests. By default, XML HTTP Server works on port 8080.
    APEX gives us a freedom to call PL SQL functions from an HTTP request.
  2. We can use DBMS_EPG package of the PL/SQL Web Toolkit to create DADs of our own
 
Let’s start with the 1st approach
 
1.    Using inbuilt APEX
 
a.       Make sure that your ANONYMOUS schema status is OPEN and your XML DB HTTP server is running on port 8080
 
BEGIN
   DBMS_XDB.sethttpport (8080);
 END;
 
The following query will give you the current status
 
SELECT account_status
  FROM dba_users
 WHERE username = 'ANONYMOUS'
 
ANONYMOUS user name is configured as an attribute of APEX DAD. So ANONYMOUS should be open. Run the following code to get a confirmation of this
 
SET serveroutput on
BEGIN
   DBMS_OUTPUT.put_line (DBMS_EPG.get_dad_attribute ('APEX',
                                                     'database-username'
                                                    )
                        );
END;
 
      After unlocking ANONYMOUS, you should be able to open the following link
 
 
b.      Let’s now create a table and a procedure which will feed info to the database and redirect us to OBIEE’s dashboard
 
CREATE OR REPLACE PROCEDURE app_schema.info_writer (
   param                       VARCHAR2,
   redirect_url_var   IN OUT   VARCHAR2
)
IS
BEGIN
   INSERT INTO info_writer_table
               (info_writer_value
               )
        VALUES (param
               );
 
   COMMIT;
 
/**** OBIEE's url uses '&' to separate the arguments of variables like PortalPath and page.
APEX also uses '&' to separate the arguments of the function called using DAD.
In order to avoid conflict between the 2, we are using the translate function.
We replace all & in the url to which the redirection has to happen with '~'. This is done in step 1g and step 2c
We then convert all ‘~’ back to ‘&’ with calling the redirect_url from PL SQL function
Since '~' in never used in urls so the we are good ****/
   redirect_url_var := TRANSLATE (redirect_url_var, '~', '&');
   OWA_UTIL.redirect_url (redirect_url_var);
END info_writer;
 
c.       Grant execute on this procedure to ANONYMOUS. This is done because APEX DAD has ANONYMOUS as its attribute and is authorized to work under the privileges of ANONYMOUS schema.
 
      GRANT EXECUTE ON app_schema.info_writer TO anonymous
 
d.      This approach works by just doing a simple configuration in a function (wwv_flow_epg_include_mod_local) in the APEX schema
Every function which has been mentioned as an exception in WWV_FLOW_EPG_INCLUDE_MOD_LOCAL function can be called from an http request
The schema which holds 'WWV_FLOW_EPG_INCLUDE_MOD_LOCAL' function depends on the version of APEX bundled with the database. Execute the following statement to know the schema owner
 
SELECT owner
           FROM all_objects
 WHERE object_name = 'WWV_FLOW_EPG_INCLUDE_MOD_LOCAL'
 
My schema name is  APEX_030200 which basically means that the version of my bundled APEX is 3.2. I am working with Oracle 11G. I believe the version of bundled APEX with 10G is 2.1
 
e.      Open this function and make sure that it looks like the following. Note that APP_SCHEMA.INFO_WRITER has been mentioned in the IN clause of the IF statement
 
CREATE OR REPLACE function APEX_030200.wwv_flow_epg_include_mod_local(
    procedure_name in varchar2)
return boolean
is
begin
    --return false; -- remove this statement when you modify this function
    --
    -- Administrator note: the procedure_name input parameter may be in the format:
    --
    --    procedure
    --    schema.procedure
    --    package.procedure
    --    schema.package.procedure
    --
    -- If the expected input parameter is a procedure name only, the IN list code shown below
    -- can be modified to itemize the expected procedure names. Otherwise you must parse the
    -- procedure_name parameter and replace the simple code below with code that will evaluate
    -- all of the cases listed above.
    --
    if upper(procedure_name) in (
          'APP_SCHEMA.INFO_WRITER') then
        return TRUE;
    else
        return FALSE;
    end if;
end wwv_flow_epg_include_mod_local;
/
‘return false’ statement at the top of the package is uncommented by default. This statement camouflages in the bunch of comments under it. I spent approx an hour because of this problemJ.
 
f.        Try putting the following URL in your browser and it should insert a row in info_writer_table and redirect you to Google’s home page
 
http://<hostname>:8080/apex/APP_SCHEMA.INFO_WRITER?param=Test string&redirect_url_var=http://www.google.com
 
Here APP_SCHEMA is the schema that holds info_writer procedure.  Param and redirect_url are the parameters of info_writer procedure.
 
g.       The last step is to fire the URL using javascript. Below is the code for it. The string inside window.open will have to be changed to suit your dashboard
Note that I have replaced ‘&PortalPath’ with ‘~PortalPath’ and ‘&page’ with ‘~page’. This has been done to avoid conflict, as explained here.
<script language="Javascript">
function load ()
{
window.open(' http://localhost:8080/apex/APP_SCHEMA.INFO_WRITER?param=Test string&redirect_url_var=http://vipatha-in.in.oracle.com:9704/analytics/saw.dll?Dashboard~PortalPath=%2Fusers%2Fweblogic%2F_portal~page=APEX%20DAD','_self');
}
</script>
<html>
  <head>
    <title>DAD</title>
  </head>
  <form>
    <table>
      <tr>
        <td>
          <input value="Call APEX DAD" type="button"
                 onclick="Javascript:load();"></input>
        </td>
      </tr>
    </table>
  </form>
</html>
clip_image002
clip_image002[5]
 
2.    Using DBMS_EPG package
 
This process also uses the XML DB HTTP server but unlike the previous process, this does not need APEX. So we will see how we can get the same end result without the use of WWV_FLOW_EPG_INCLUDE_MOD_LOCAL
In this process, we will create a DAD, authorize it to use the privileges of certain db schema and then call a procedure in that schema.
In this example, we are going to set APP_SCHEMA as the value for database-username attribute of the DAD. This is done by DBMS_EPG.set_dad_attribute procedure.  The value of this attribute for APEX DAD is set to ANONYMOUS (default setting). We do not have to grant execute to ANONYMOUS if we are using this approach
 
Let’s start
a.       Create a DAD and authorize it to work under the privileges of the APP_SCHEMA
 
/****Execute these statements after logging in SYS****/
BEGIN
   DBMS_EPG.create_dad (dad_name => 'obiee_dad', PATH => '/obiee_dad/*');
END;
 
--If there is some problem in your configuration then this will help in displaying that on the browser.
BEGIN
   DBMS_EPG.set_dad_attribute ('obiee_dad', 'error-style', 'DebugStyle');
END;
 
--These are to give access to all the functions defined in APP_SCHEMA to obiee_dad
BEGIN
   DBMS_EPG.authorize_dad (dad_name => 'obiee_dad', user => 'APP_SCHEMA');
END;
BEGIN
DBMS_EPG.set_dad_attribute ('obiee_dad', 'database-username', 'APP_SCHEMA');
END;
 
b.      That’s it. Job is done. We can now call any procedure, defined in APP_SCHEMA, from a URL. Let’s call the info_writer proc using this DAD
http://<hostname>:8080/obiee_dad/info_writer?param=Test string&redirect_url_var=http://www.google.com
 
c.       Modify the code in step 1.g to call the above URL from OBIEE’s dashboard. You will have to change the string in window.open to suit your dashboard.
Note that I have replaced ‘&PortalPath’ with ‘~PortalPath’ and ‘&page’ with ‘~page’. This has been done to avoid conflict, as explained here.
 
<script language="Javascript">
function load ()
{
window.open('http://localhost:8080/obiee_dad/info_writer?param=Test string&redirect_url_var=http://vipatha-in:9704/analytics/saw.dll?Dashboard~PortalPath=%2Fusers%2Fweblogic%2F_portal~Page=Custom%20DAD','_self');
}
</script>
<html>
  <head>
    <title>DAD</title>
  </head>
  <form>
    <table>
      <tr>
        <td>
          <input value="Call custom DAD" type="button"
                 onclick="Javascript:load();"></input>
        </td>
      </tr>
    </table>
  </form>
</html>

No comments: