OBIEE 11 - Configuing Web Services for SOA

As part of OBIEE there are 2 types of web services:

• Session Based Web Services
• Web Services for SOA (new in 11g)

A great reference for the entire subject of Action Famework in OBIEE is the lecture "Oracle Business Intelligence 11g Overview of Action Framework" by Antony Heljula. Reading the OBIEE Integrators Guide might help as well.

Most of what you see here is covered very well by Antony, including the topic of security I didn't cover here. Unfortunately I had some problems with the last part when following Antony (since his configuration is 11.1.1.5 style, and starting 11.1.1.6 it was changed), that is why this post exist.

Lets talk about how we can configure the second option, Web Services for SOA.

The access to web services is done by the URL http://YOURSERVER:PORT/biservices/inspection?wsil. In my case it was http://bdahab-lap:9704/biservices/inspection?wsil.

Assuming no one configured it, you will see the Error 500--Internal Server Error screen.

If you want to use the Web Services for SOA, that means you have to do some configuration.

What needs to be done is setting the credential store and updating the ActionFrameworkConfig.xml.

Configuration steps

Enter the EM (my case http://bdahab-lap:7001/em) and Right Click on WebLogic Domain / bioundation_domain and select Security -> Credentials

Select oracle.bi.enterprise and create a new key: wsil.browsing

fill the user name and password. This is the user that will always be used when running the wsil web services. For better security of this option see slides 46-54 in "Oracle Business Intelligence 11g Overview of Action Framework" by Antony Heljula or OBIEE Integrators Guide

Next we will go to MWHOME\user_projects\domains\bifoundation_domain\config\fmwconfig\biinstances\coreapplication and create the file wss_username_token_policy.xml.
with the following content:
 <?xml version="1.0" encoding="UTF-8"?>
<oracle-webservice-clients>
<webservice-client>
<port-info>
<policy-references>
<policy-reference uri="oracle/log_policy" category="management"/>
<policy-reference uri="oracle/wss_username_token_client_policy" category="security"/>
</policy-references>
</port-info>
</webservice-client>
</oracle-webservice-clients>

The last part is configuring ActionFrameworkConfig.xml that you will find in the same folder.
(When you back it up, and for some strange reason decided to save the backup in the same folder, don't save it with XML extension.) Here I had some help from Meirav Malka from Oracle ACS.

 My Original file was:

<?xml version="1.0" encoding="UTF-8"?>
<obi-action-config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="afconfig.xsd">
    <aliases/>
    <registries/>
    <content-types>   
      <content-type>
        <typename>webservices</typename>
        <displayname>Web Services and BPEL Processes</displayname>
        <actionType>WebServiceActionType</actionType>
      </content-type>
      <content-type>
        <typename>misc</typename>
        <displayname>Mixed Services</displayname>
        <actionType>URLActionType</actionType>
      </content-type>
    </content-types>
    <accounts/>
</obi-action-config>



The updated file was:

(Change  bdahab-lap:9704 to whatever is relevant for your system.)

<?xml version="1.0" encoding="UTF-8"?>
<obi-action-config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="afconfig.xsd">
    <aliases/>
<registries>
 <registry>
            <id>reg1b</id>
            <name>BI EE Web Services for SOA</name>
            <content-type>webservices</content-type>
            <provider-class>oracle.bi.action.registry.wsil.WSILRegistry</provider-class>
            <description></description>
            <location>
                <path>http://bdahab-lap:9704/biservices/inspection?wsil</path>
            </location>
           <service-access>
            <account>wsil.browsing</account>
            <policy>wss_username_token_policy</policy>
            <propagateIdentity>false</propagateIdentity>
           </service-access>
        </registry>
</registries>
    <content-types>   
      <content-type>
        <typename>webservices</typename>
        <displayname>Web Services and BPEL Processes</displayname>
        <actionType>WebServiceActionType</actionType>
      </content-type>
      <content-type>
        <typename>misc</typename>
        <displayname>Mixed Services</displayname>
        <actionType>URLActionType</actionType>
      </content-type>
    </content-types>
<accounts>
        <account>
            <name>wsil.browsing</name>
            <description>Account for BI WS for SOA</description>
            <adminonly>false</adminonly>
            <credentialkey>weblogic</credentialkey>
        </account>
    </accounts>
<policies>
    <policy>
        <name>wss_username_token_policy</name>
        <policyfile>wss_username_token_policy.xml</policyfile>
    </policy>
</policies>
</obi-action-config>


In this very naive (security-wise) set up I created an account definition I named weblogic (you can use any name you want). Naturally in production environment we will use better security.
We need to connect this "weblogic" account to a real user and password.

As described in chapter 5.5 of Integrators Guide, enter EM again.
In bifoundation_domain right click and select Security -> Credentials.

Create Map named oracle.bi.actions

In that map create a Key. The key should fit your account (weblogic in my case) and you should enter the username and password of a real BI user.

Restart BI Presentation Services and Weblogic "bi_server1" (or just the entire OBIEE).

Now try the URL again.

Better, open action menu, Invoke a Web Service. Now you see all the objects in catalog, available as web services.

A good question is: OK, what is it good for?
Using Web Services for SOA we can easily run any object in catalog, including agents. To the best of my knowledge, we will not see the result of the activated object, but it will run in the background.
So to my humble opinion, a clear use case is to activate agents from an analysis or dashboard.

I'll be happy to hear additional use cases.

OBIEE - Oracle Business Intelligence Mobile App Designer

Starting with OBIEE 11.1.1.7.1 (with 2 patches) there is a new mobile (HTML5) option in OBIEE. 

It is named Oracle Business Intelligence Mobile App Designer and is part of OBIEE.

Unlike the Oracle BI HD application (that can be downloaded from Apple App store) or the IOS Mobile Security kit (more or less same IOS code that you can use as part of your own application. Described here and some developers documentation is here). This is something you control.
You can create your own HTML5 Application using embedded wizards and publish it to you users. No need for programers and available for all platforms, since it's pure HTML5.

Some links:

Oracle Business Intelligence Mobile App Designer Page at Oracle.com

Solution Brief pdf

Installation Guide doc.

User Guide doc.

Release Notes doc.

YouTube how-to guide

Oracle Training (step by step guide)

Mark Rittman first look at it

Siebel Essentials post about it

As described in the installation Guide, you need to:

1. Be in OBIEE 11.1.1.7.1 (Installation of patch 11.1.1.7.1 described here)

2. Apply OBIEE 11.1.1.7.1 MLR Patch 17004920 from Oracle Support. (Currently available for 64bit Linux and Windows)

3. Apply Oracle BI Mobile App Designer patch 17220944 from Oracle Support. (Generic).

I'm back from short vacation so I don't have pictures of the Mobile App Designer yet, but I can show you few butterflies:

and some moonlight:

OBIEE - Creating dependent - implicitly declared presentation variable

There are times we want to create a presentation variable that should not be seen by end-users and depends on other variables. We want it declared implicitly and not explicitly as most presentation variable.

What is my case?

In the OBIEE demos there are samples of presentation variable containing a column name. For example "Products"."Product Type" or "Offices"."Department". Next that presentation variable can be used for more general cases than column selectors.

My customers didn't like it and preferred  something like 'P Type' and 'Dept' to be selected.

Lets start with a regular presentation  variable p1, that has 2 static text values:

 Next a second Dashboard Prompt is created with a second presentation variable, depending on the first. The SQL Result of Default Selection is:
SELECT case when '@{p1}{P Type}'='P Type' then '"Products"."Product Type"' else '"Offices"."Department"' end saw_0 FROM "Sample Sales Lite"

The second presentation variable, actually return the value I want, but I don't want the user to see it, or be aware of it. So I'll have to hide it.
There are few options to do it.
1. Place the dashboard prompt in a section with condition set to false.
2. Put it as in the dashboard properties variables. That is what we will do:

Edit the dashboard:

go to dashboard properties:

there in 'Filters and Variables' add the second dashboard prompt:

Now, create a new analysis with a column @{p2} (or any function that uses it).

Unfortunately, this is not sufficient. 
The value of the variable p2 is updated implicitly. There is no action on it. The analysis needs to "know" something was updated. So we have to add variable p1, that our p2 based on, to the analysis, so it will be refreshed when p1 value changes.

We will create a new column in the analysis, for example the formula '@{p1}'.
Of course we should hide it. (Column Properties, hide).

Now when I select 'P Type' in the prompt, I see the "Product Type" values:

And the 'Dept' changes the column to "Department"

OBIEE 11.1.1.7 - Formally supports Microsoft SQL Server 2012

If you check the updated OBIEE 11.1.1.7 Certification Matrix HERE (in the certification page), you will see that MS SQL Server 2012 is now supported as data-source both as relational and Analysis services (but not for RCU at the moment), starting 11.1.1.7.0.

So if you were working on 11.1.1.7 with Microsoft SQL Server 2012, now you are certified.

Default OBIEE 11g Compound Layout to Pivot

i had a request, to default OBIEE new analysis to Title and Pivot. 

By default when you create a new analysis the default compound layout can be: Title + Table or Title + Pivot. The Pivot will be default when the analysis contains Hierarchical column.

The solution outline is described in OBIEE System Administrators Guide, Chapter 19.72.3 - Setting Defaults for Analyses in the Compound Layout. (the example there is changing default to narrative + chart).

In MWHOME\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips_Your_Number\msgdb I created folder custommessages .

There I created a file custommessages.xml with the following content:

<?xml version="1.0" encoding="utf-8"?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web.messageSystem">
<WebMessageTable system="Answers" table="ViewDefaults">
<WebMessage name="kuiCriteriaDefaultViewElements" translate="no"><HTML>
<view signature="compoundView" >
<cv signature="titleView" />
<cv signature="pivotTableView" />
</view>
</HTML>
</WebMessage>
</WebMessageTable>
</WebMessageTables>

(the critical part is <cv signature="titleView" />   <cv signature="pivotTableView" />)
It replaces the default definitions in MWHOME\Oracle_BI1\bifoundation\web\msgdb\messages\answerstemplates.xml

Saved it. Restarted presentation services.

When creating new analysis the default is always Pivot:

OBIEE and Time Series Calculations in analysis (Ago‎, Period Rolling‎, To Date‎)

In OBIEE 11 we can use the Time Series Function in analysis and not only in Administration.

2 of the functions require a "time level". So in this post we will learn how to find it and the meaning of each function.
Please note the Time Series Functions are not subject to analysis filters. For example I can create a filter on analysis to show only month X, but if I use the AGO function with last month parameters, it will return data of month X-1, despite the filter. This is the desired behavior of such function.

Time Level:

Unlike the Administration where you can select the time level from the relevant option on the left, in analysis you have to write it yourself. For example, in Sample Sales we have the following Time Dimension:

The Time Level is "Folder Name"."Hierarchy Name"."Level". In our case it's "Time"."Time Hierarchy"."Month".

Time Series Calculations

AGO:

Syntax: AGO(expr, time_level, offset)


Example: AGO("Base Facts"."Revenue","Time"."Time Hierarchy"."Month", 1)

   
A time series aggregation function that calculates the aggregated value from the current time back to a specified time period. In our example AGO provides the Revenue 1 Month Ago from the relevant row data.

For the following example: 

We get this result:

We have the above AGO definition:

AGO("Base Facts"."Revenue","Time"."Time Hierarchy"."Month", 1)

Since the column Month is part of this analysis, we can omit  the time level of the function. The following will give the same result:

AGO("Base Facts"."Revenue", 1)

But it will not work if you delete the Month column from the analysis:

What kind of analysis do I need for Ago without month as a column?

For example when you want the value for this month and last month only as 2 column (so you can compare them). For example:

This criteria (with only 1 month in the filter)

Returns this result:

We need the Time Level here.

Or this example:
I added this column AGO("Base Facts"."Revenue" , "Time"."Time Hierarchy"."Year", 1)
It returns the revenue of a month 1 year ago.

Period Rolling:

Syntax: PERIODROLLING(measure, x [,y])
 Example: PERIODROLLING("Base Facts"."Revenue", -1 ,1 )

This function computes the sum of a measure over the period starting x units of time and ending y units of time from the current time. The unit of time is determined by the measure level of the measures in its first argument and the query level of the query to which the function belongs. In our example the function returns the last, the current and next month aggregated Revenue.

 Few examples:
PERIODROLLING("Base Facts"."Revenue", 0,0) - Returns the current month Revenue (similar to using "base Facts"."Revenue" without the function)
PERIODROLLING("Base Facts"."Revenue", -1,0) - Returns the last month (the -1) and current month Revenue.
PERIODROLLING("Base Facts"."Revenue", -1,1) - Returns the last, the current and next month aggregated Revenue.
PERIODROLLING("Base Facts"."Revenue", -1,2) - Returns the last, the current and next 2 month aggregated Revenue.

TODATE

Syntax: TODATE(expr, time_level)

Example: TODATE("Base Facts"."Revenue", "Time"."Time Hierarchy"."Quarter")

A time series aggregation function that aggregates a measure attribute from the beginning of a specified time period to the current time. In our example we get the total revenue since the beginning of the quarter.

Example:
TODATE("Base Facts"."Revenue", "Time"."Time Hierarchy"."Quarter") returns the revenue from the beginning of the relevant quarter. In our example the result of this column in each first month of quarter is equal to the "regular" Revenue.
TODATE("Base Facts"."Revenue", "Time"."Time Hierarchy"."Year") returns the revenue from the beginning of the relevant Year.

Just as we did with AGO we can run TODATE without Time Dimension columns in the analysis (but we use it in filter). 
For example:

Results in:

You can't use PERIODROLLING this way.

OBIEE - Dual Y axis line and Dual axis Bar charts: the easy, unsupported and not recommended way

I blogged about OBIEE and Dual Y line chart: OBIEE- Narrative and dual Y axis Google Chart

I blogged about OBIEE and Dual Y line chart: OBIEE - dual Y line chart / graph using Javascript

Yes, I blogged about OBIEE and Dual Y line / bar chart: OBIEE - dual Y bar using BI Publisher

It seems there is much easier, but not supported and not recommended way.

Create a Report with dual line bar chart:

In advanced Tab ->Analysis XML

In the line <saw:column measureType="y1" riserType="bar">

Change “bar” to “line”. Press Apply XML.

Everything seems to keep working fine:

  

You prefer dual axis Y bars? It’s on the house. Same solution, just change "line" to "bar".

Everything is fine if you don’t touch the measures in the bar or line part in the chart editor.

You did? You return to the Line / Bar chart.

Advantages: Built in, integrated, Printing works fine.

Disadvantages:  Not supported, it might stop working after an upgrade in the future. To the best of my knowledge; Each time you interfere in the XML, Oracle is not responsible for the results.

OBIEE - PopUp Windows using Browser Scripts

In the Sample App v305 environment (you can download the VM from Oracle Business Intelligence Samples), dashboard 4.2 you can see the following behavior:

  When you click on the value, you open a popup windows that accepts parameter:

Lets check out how its done and then improve it a little.
What do I want to improve? If you are using this code many times, you are left with many popup windows at the background. What I want to add is a functionality that closes the popup window after 60 seconds automatically (you want other timing or other logic... you are welcome to change the code).

What we have here are 2 parts:
1. Some JavaScript code available as "Invoke browser script" Action.
2. Using that code as Action in one analysis and call another, while passing parameters.


Since I'm not a javascript expert, I decided to steal the code from the Oracle Demo. Unlike other stealing cases (like the rude bloggers that copy/paste my posts from time to time) the Oracle Sample App was created for us to steal from it.
Lets start with the javascript.

In your OBIEE installation at the following location:
MWHOME\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\CHANGING_NAME\war\res\b_mozilla\actions (in my case it was D:\or\MWHOME\user_projects\domains\bifoundation_domain\servers\bi_server1\tmp\_WL_user\analytics_11.1.1\7dezjl\war\res\b_mozilla\actions), you will find the file UserScripts.js.
Back it up before you do anything.
Now I copy / pasted from the same file in the Samples VM the following code:

USERSCRIPT.displayPopupWindow = function(aParams){
   var strURL,w,h,scroll;
   for( args in aParams )
   {
      var argName  = args;
      var argValue = aParams[argName];
          if(argName=="url"){
                strURL = aParams[argName];
          } if(argName=="width"){
                w = aParams[argName];
          } if(argName=="height"){
                h = aParams[argName];
          } if(argName=="scroll"){
                scroll = aParams[argName];
          } else {
                argName = "@{" + argName + "}";
                strURL = strURL.replace(argName,argValue);
          }
   }
   
    var LeftPosition = (screen.width) ? (screen.width-w)/2 : 0;
    var TopPosition = (screen.height) ? (screen.height-h)/2 : 0;
    var settings =    'height='+h+',width='+w+',top='+TopPosition+',left='+LeftPosition+',scrollbars='+scroll+',resizable=0,
toolbar=0,menubar=0,location=0,status=0';
    popupWindow = window.open(strURL,'',settings);
    popupWindow.show();
    };

USERSCRIPT.displayPopupWindow.publish =
{
   parameters :
   [
      new USERSCRIPT.parameter( 'url', 'Enter URL', 'www.oracle.com' ),
          new USERSCRIPT.parameter( 'width', 'Width of popup window', '500' ),
          new USERSCRIPT.parameter( 'height', 'Height of popup window', '500' ),
          new USERSCRIPT.parameter( 'scroll', 'Include scroll bars?', 'no' ),
          new USERSCRIPT.parameter( 'p1', 'Enter value for Param 1', '' ),
          new USERSCRIPT.parameter( 'p2', 'Enter value for Param 2', '' )
   ]
};   

Since, as I said, I want the popup window to close automatically after 60 seconds, I added the line:   setTimeout(function(){popupWindow.close();},60000);

So the result is:

USERSCRIPT.displayPopupWindow = function(aParams){
   var strURL,w,h,scroll;
   for( args in aParams )
   {
      var argName  = args;
      var argValue = aParams[argName];
          if(argName=="url"){
                strURL = aParams[argName];
          } if(argName=="width"){
                w = aParams[argName];
          } if(argName=="height"){
                h = aParams[argName];
          } if(argName=="scroll"){
                scroll = aParams[argName];
          } else {
                argName = "@{" + argName + "}";
                strURL = strURL.replace(argName,argValue);
          }
   }
   
    var LeftPosition = (screen.width) ? (screen.width-w)/2 : 0;
    var TopPosition = (screen.height) ? (screen.height-h)/2 : 0;
    var settings =    'height='+h+',width='+w+',top='+TopPosition+',left='+LeftPosition+',scrollbars='+scroll+',resizable=0,
toolbar=0,menubar=0,location=0,status=0';
    popupWindow = window.open(strURL,'',settings);

    setTimeout(function(){popupWindow.close();},60000);

    popupWindow.show();
    };

USERSCRIPT.displayPopupWindow.publish =
{
   parameters :
   [
      new USERSCRIPT.parameter( 'url', 'Enter URL', 'www.oracle.com' ),
          new USERSCRIPT.parameter( 'width', 'Width of popup window', '500' ),
          new USERSCRIPT.parameter( 'height', 'Height of popup window', '500' ),
          new USERSCRIPT.parameter( 'scroll', 'Include scroll bars?', 'no' ),
          new USERSCRIPT.parameter( 'p1', 'Enter value for Param 1', '' ),
          new USERSCRIPT.parameter( 'p2', 'Enter value for Param 2', '' )
   ]
};   

Please be very careful if you copy paste this code from this blog, especially with the " character. It might be better to get it from the UserScripts.js file downloadable from here. In this file I copied additional parts from the VM (be careful with the file as well, I haven't tested it all yet).  

To have the javascript working you need to reset the BI Server and more important, clear the browser cache. Javascipts is exactly the type of thing the browsers tend to cache.

Next you should be able to see the diplayPopupWindow in your "Invoke browser script" Actions.

 

In the second part we will use this action.
On my OBIEE I created 2 analysis. Named Parent and Child.
The child analysis, had 2 relevant columns with "Is Prompted" filters ("Per Name Month" and "Products").
In the Parent analysis I had a Revenue column with the following Interaction.

After I selected the scripts from the browse window, I had the parameters ready:

I marked all parameters Hidden, to avoid interaction with user.
 I filled the first parameter, the URL:
saw.dll?Go&Action=print&path=%2Fusers%2Fweblogic%2Fpoptest%2Fchild&col1="Time"."Per Name Month"&val1="@{p1}"&op1=eq&col2="Products"."Product"&val2="@{p2}"&op2=eq
The rest were easy:
Window height (650 in my case)
Window Length (500)
Do I want scrolling in the window? (no)
2 columns that I pass as parameters in the URL (select column Value a seen bellow and the column you want)

You might ask, what if it's not 2 parameters I want? Then modify the script.

That's it.