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 1^st 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

 

http://<hostname>:8080/apex/f?p=4550

 

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>

 

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>