OBIEE – Blocking Requests in Answers

Somebody asked me recently if we could force the user to filter by a particular column in a subject area; “We most certainly can” I told him and I told him where it was documented.  He came back shortly afterwards unable to get it to work; I must admit that the documentation provided is not great, so thought that I’d document it a little clearer through a guided example.  For those interested, it is documented in Chapter 3 of the Presentation Services Administration Guide under the same heading as this blog.
answerstemplates.xml and customMessages
On my PC there is a folder OracleBI\web\msgdb\messages; if you look in that folder you will see that it has a directory for each installation language.  You will also find a directory messages; which you can see is then replicated as a subdirectory in each language directory, although the exact files in each folder will differ slightly.  You may have picked up the idea that these message folders, which contain only xml files, contain definition files that dictate the behaviour of the obiee application; and that behaviour can be programmed differently between languages.
If you look at the different xml files you can hazard a guess as to the area of the application, or the behaviour, that a particular file defines, such as chartmessages.xml.  And you may also noticed that the contents of chartmessages.xml, for example, is written in the appropriate language within a language folder.
I installed obiee with the English language so I am interested only in the language folder l_en.  At the moment it contains only the expected folder messages.  And in that folder you will find the file answerstemplates.xml which dictates the behaviour of the app when using answers, surprisingly   If you search this file for the WebMessage kuicriteriaBlockingScript then this is the behaviour that we are going to change.  Note that changing this file can be very hazardous so we have an alternative way of making changes.
Within the relevant language folder we will create a folder named customMessages, alongside our current folder, messages.  Obiee will search this folder for xml files; any definitions in these files will override those defined in the messages folder; you can have as many xml files as you like and you can call them anything you like as long as they have an xml extension.  In this example, I have called the file customMessages.xml.  Create the file and paste the script below as its contents.

 {?xml version=”1.0″ encoding=”utf-8″?}
  {WebMessageTables xmlns:sawm=”com.siebel.analytics.web.messageSystem”}
    {WebMessageTable system=”QueryBlocking” table=”Messages”}
      {WebMessage translate=”no”}
        {HTML}
          {script src=”fmap:myblocking.js” /}
        {/HTML}
      {/WebMessage}
    {/WebMessageTable}
  {/WebMessageTables}

Please replace curly braces, { }, with angular brackets, <>.  You will need to repeat this to each relevant language folder; my installation is English only so I needn’t.
If you read the script, you can at least gather that it is going to run a javascript file; and the script will be run for each answer request prior to the request itself.
The JavaScript file
In our custom message file you may have noticed that the reference to the JavaScript file refers to fmap, whatever that is.  A reference to fmap refers to a reserved area in the memory of the OBI Presentation Server; this area is loaded during the initialization of the Server, or on restart.
If we want to get the JavaScript loaded into the fmap area then we need to place it on the Presentation Server, for me this meant in the folder below – and it will be the same for most of you.

OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\b_mozilla

I have spoken to people who have told me they have put the JavaScript file on the BI Server in the location OracleBI\web\app\res\b_mozilla; but this should not work.  This should only work if a new analytics war file is created and re-deployed to the Web Server Container.  If this has worked for you then I am willing to accept that, but it doesn’t work for me.
Anyway, I have called the JavaScript file myblocking.js; create the file and add the contents below.  The script contains a function, validateAnalysisCriteria, specifically recognised by obiee; stick to this naming convention; this goes also for the name of the JavaScript file itself.

// This is a blocking function. It makes sure users pick what I want them to.
function validateAnalysisCriteria(analysisXml)
{
   // Create the helper object
   var tValidator = new CriteriaValidator(analysisXml);
   if (tValidator.getSubjectArea() == “GTS Balances”)
   {
      if (!tValidator.filterExists(“Date”,”Business Date”))
         return “Please filter by a valid Business Date”;
   }
   return true;
}

This script, performs a specific task.  My client had a huge fact table, partitioned by date; by forcing the user to always filter by a date we improved performance hugely.  The script says that for the Subject Area “GTS Balances” produce the error message if there is not a filter on the column “Business Date” in the folder “Date”; the references to SA, folder and column are as they appear in the presentation layer, not their logical names.  Otherwise return true and do nothing.
I have added the below the methods available for this functionality – copied and pasted directly from Oracle’s instructions.

 CriteriaValidator.getSubjectArea()
Returns the name of the subject area referenced by the
request. It generally is used in a switch statement within the
function before doing other validation. If the request is a
set-based criteria, it returns null.
CriteriaValidator.tableExists(sTable)
Returns True if the specified table has been added to the
request by the user, and False if the table was not added.
CriteriaValidator.columnExists(sTable, sColumn)
Returns True if the specified column has been added to the
request by the user, and False if the column was not added.
CriteriaValidator.dependentColumnExists(sCheckTable,sCheckColumn,
sDependentTable,sDependentColumn)
Checks to make sure that the dependentColumn exists if the
checkColumn is present. It returns True if either the
checkColumn is not present, or the checkColumn and the
dependent column are present. If checkColumn and
dependentColumn are null, the tables are validated. If any
column from checkTable is present, a column from
dependentTable must be present.
CriteriaValidator.filterExists(sFilterTable, sFilterColumn)
Returns True if a filter exists on the specified column, and
False if no filter is present.
CriteriaValidator.dependentFilterExists(sCheckTable, sCheckColumn,
sFilterTable, sFilterColumn)
Checks to make sure that the dependentFilter exists if the
checkColumn is present in the projection list. It returns True
if either the checkColumn is not present, or the
checkColumn and the dependent filter are present.
CriteriaValidator.filterCount(sFilterTable, sFilterColumn)
Returns the number of filter values specified for given logical
column. If the filter value is “equals,” “null,” “notNull,” or
“in,” it returns the number of values chosen. If the column
is not used in a filter, it returns zero. If the column is
prompted with no default, it returns -1. For all other filter
operators (such as “greater than,” “begins with,” and so on)
it returns 999, because the number of values cannot be