Translate

Monday, 11 March 2013

Shahed

OBIEE 11g Blocking Analysis (Enforcing mandatory fields to be picked and enforcing filters to be applied on Subject Area)

Enforcing mandatory fields to be picked and enforcing filters to be applied within Subject Area


Tested on OBIEE 11.1.1.6.8 working OK
Krishna M & Shahed M


Make sure the custom analytics res folder is deployed. ( Please see our post on backgrounds or watermarks)


1.Ensure customMessages folder exists below analyticsRes folder

$MW_HOME/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/customMessages


2. Looks for the file answerstemplates.xml in the path

$MW_HOME/Oracle_BI1/bifoundation/web/msgdb/messages/

3. Copy it to the following directory
$MW_HOME/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/customMessages

4. Look for the pattern <WebMessage name="kuiCriteriaBlockingScript" within the answerstemplates.xml script and ammend as below


 
Before Change

<WebMessage name="kuiCriteriaBlockingScript" translate="no">
<HTML>
<script type="text/javascript">function validateAnalysisCriteria(analysisXml)
{
return true; // by default, no checking is done
}</script>
</HTML>
</WebMessage>


Replace the section with the following

After Change

<WebMessage name="kuiCriteriaBlockingScript" translate="no">
    <HTML>
        <script type="text/javascript" src="/analyticsRes/mycriteriablocking.js" />
    </HTML>
</WebMessage>


5. Create a file named mycriteriablocking.js and place it in the path
$MW_HOME/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes


Example content mycriteriablocking.js as (Just create in notepad or something and save as all files with extension .js)


 
// This is a function to block query combinations in subject area and apply mandatory filters etc. Shahed M
function validateAnalysisCriteria(analysisXml)
{

   // Create the helper object
   var tValidator = new CriteriaValidator(analysisXml);

   // This logic will only apply to the subject area specified
     if (tValidator.getSubjectArea() == "My Subject Area")
   {

   // If Date is picked from Calendar Folder then Active filter is mandatory from the same Folder

if (    !tValidator.dependentFilterExists("Calendar","Date","Calendar","Active"))
        return "Please apply Filter on Active";

   // If any field from Fact Folder is picked then Date from Calendar Folder is mandatory

if (    !tValidator.dependentFilterExists("My Fact","Amount","Calendar","Date"))
        return "Please apply Date Filter from Calendar Folder";

        }
                   // If everything is ok then

   return true;

}

6. Bounce all Services for OBIEE incuding Weblogic.


You can keep ammending the mycriteriablocking.js without bouncing the box once the first bounce is complete

7. in OBIEE refresh Metadata within the Administration tab

8. Test solution and keep ammending the mycriteriablocking.js as needed.
 
Other Notes (David)

Change the admin console
Deployments > analytics (11.1.1) > analytics > Resource Reload Check (in seconds)" = 0 for the js code to be refreshed on the server.
And testing on the client web browser requires force refresh (Ctrl+F5).


Oracle’s Example

// This is a blocking function. It ensures that users select what
// the designer wants them to.
function validateAnalysisCriteria(analysisXml)
{
   // Create the helper object
   var tValidator = new CriteriaValidator(analysisXml);
   // Validation Logic
   if (tValidator.getSubjectArea() != "Sample Sales")
      return "Please try Sample Sales?";
   if (!tValidator.dependentColumnExists("Markets","Region","Markets","District"))
   {
      // If validation script notifies user, then return false
      alert("Region and District are well suited, do you think?");
      return false;
   }
   if (!tValidator.dependentColumnExists("Sales   Measures","","Periods","Year"))
   return "You selected a measure so pick Year!";

   if (!tValidator.filterExists("Sales Measures","Dollars"))
   return "Maybe filter on Dollars?";

   if (!tValidator.dependentFilterExists("Markets","Market","Markets"))
   return "Since you are showing specific Markets, filter the markets.";

   var n = tValidator.filterCount("Markets","Region");
   if ((n <= 0) || (n > 3))
      return "Select 3 or fewer specific Regions";
   return true;
}





List of Validator functions

Validation Helper Function
Description
CriteriaValidator.getSubjectArea()
Returns the name of the subject area referenced by the analysis. It generally is used in a switch statement within the function before doing other validation. If the analysis is a set-based criteria, then it returns null.
CriteriaValidator.tableExists(sTable)
Returns True if the specified folder (table) has been added to the analysis by the content designer, and False if the folder was not added.
CriteriaValidator.columnExists(sTable, sColumn)
Returns True if the specified column has been added to the analysis by the content designer, and False if the column was not added.
CriteriaValidator.dependentColumnExists(sCheckTable, sCheckColumn, sDependentTable, sDependentColumn)
Checks to ensure 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, then the folders are validated. If any column from checkTable is present, then 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 ensure 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 that are specified for the given logical column. If the filter value is "equals," "null," "notNull," or "in," then it returns the number of values chosen. If the column is not used in a filter, then it returns zero. If the column is prompted with no default, then 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 determined.


About Authors

Shahed Munir

Krishna Udathu

Shahed and Krishna are Oracle / Big Data Experts