Oracle Business Intelligence OBIEE 101: CRITERIA

Friday, September 30, 2011

OBIEE11g Blocking a formula

In http://obiee101.blogspot.com/2011/09/obiee11g-blocking-analyses-based-on.html I showed you the possibilities to block an analyses based on criteria system wide. In this article I want show how to block an analyses based on the editing of a formula.

A large part of the criteria editor is controlled by the criteriatemplate.xml

the kuiColumnFormulaEditorHead generates a web message reference to kuiFormulaBlockingScript

In order to use this reference you will have to create a new web message in on of your custom xml files:

<WebMessage name="kuiFormulaBlockingScript" translate="no">
    <HTML>
        <script type="text/javascript" src="fmap:myformulablocking.js" />
    </HTML>
</WebMessage>

This effectively creates a “fork out” to a javascript (.JS) file. You can place this java script file in the ORACLE_INSTANCE\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obipsn\analyticsRes directory.

Let’s start with a simple example:

// http://obiee101.blogspot.com
// This is a formula blocking function.
// It makes sure the user does not enter an unacceptable formula.
function validateAnalysisFormula(sFormula, sAggRule)
{
alert(sFormula);
alert(sAggRule);
return true;
}
//

It basically returns the formula you enter:

and the Aggregation rule you selected.

Based on this info you can block for instance the usage from EVALUATE functions: (based on example for 10g found here:http://prolynxuk.com/blog/?p=413) (note: EVALUATE can be a security risk if the connection pool user have certain database roles…..)

// http://obiee101.blogspot.com
// This is a formula blocking function.
// It makes sure the user does not enter an unacceptable formula.
function validateAnalysisFormula(sFormula, sAggRule)
{
// alert(sFormula);
// alert(sAggRule);
// Donot allow EVALUATE function
var evaluateRe = "EVALUATE";
var nEvaluate = sFormula.search(evaluateRe);
if (nEvaluate >= 0)
        {
        alert("You used Evaluate function and is not allowed.");
        return false;
        }
    return true;
}

gives:

Till Next Time

Thursday, June 19, 2008

OBIEE Blocking request based on Criteria

The basis can be found in Oracle Business Intelligence Presentation Services Administration Guide (B31766.pdf)

“ When a user attempts to execute a request that your code blocks, you can display an error message, and the request will not be executed. The answerstemplates.xml file includes a message named kuiCriteriaBlockingScript that can be overridden to either define or include JavaScript that defines a validateAnalysisCriteria function. By default, this message contains a function that always returns True.
Answers calls your validateAnalysisCriteria function when the user tries to execute the request. The function can return True if the request is not blocked, or False or a message if the request is blocked. If a message or a value other than False is returned, the message is displayed in a popup window. In either case, the query is blocked.”

Since the documentation on this subject is guiding you in the wrong direction, I toke upon myself to make a step by step instruction.

Step 1: in .. \OracleBI\web\msgdb create a customMessages folder if it not already there:

Step 2: in \OracleBI\web\msgdb\customMessages create an XML file named : qbCriteria.xml (The name is arbitrary, the content isn’t !!!)

Step 3:
Enter the following XML into the file

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

Replace the { and }
Save the file!

In …OracleBI\web\app\res\b_mozilla (this is wrong in the OBIEE documentation) make a new text file called myblocking.js

Enter the following Script:

// 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);
// Validation Logic
if (tValidator.getSubjectArea() != "Paint")
return "Why don't you try Paint?";
if (!tValidator.dependentColumnExists("Markets","Region","Markets","District"))
{
// If validation script notifies user, then return false
alert("Region and District go so well together, don't you think?");
return false;
}
if (!tValidator.dependentColumnExists("Sales Measures","","Periods","Year"))
return "You picked a measure so pick Year!";
if (!tValidator.filterExists("Sales Measures","Dollars"))
return "Why don't you filter on Dollars?";
if (!tValidator.dependentFilterExists("Markets","Market","Markets"))
return "Since you're showing specific Markets, please filter the markets.";
var n = tValidator.filterCount("Markets","Region");
if ((n <= 0) (n > 3))
return "Please select 3 or fewer specific Regions";
return true;
}

save the file:

the name myblocking.js is seemingly hard coded is OBIEE somewhere, I wasn’t able to change it……..

Restart the whole BI-server and play around in the Paint and Paint Exec subject area.

In Paint Exec:

In Paint:

Till Next Time