Recently one of my clients asked for a unique requirement for dynamic Answers page. They wanted that when a user develops an Ad-Hoc query in Answers, the criteria is validated or checked before submitting the query to BI Server/Database. This can be a very interesting Interview Question to gauze knowledge in administration of Oracle BI Presentation Services.
There are some procedures in Oracle BI Presentation Services that we can follow to bring such functionality in our deployment.
In this case the client basically wanted to check filters, validate requests and block specific queries. In Oracle BI Answers, we have APIs to fulfill such requirements. We can create our codes to display error message or give some guidance to the users on creating a better ad-hoc report. We can do the following
1. Block a request based on Criteria.
The answerstemplates.xml file in [Installation Dir]\OracleBI\web\msgdb\messages have kuiCriteriaBlockingScript message where we can define or include JavaScript defining a validateAnalysisCriteria function. Oracle BI Answers calls this validateAnalysisCriteria function whenever a user executes an Answers request. The function returns true or false, when the function call return true the report gets executed else a customized message can be shown to user and query is blocked.
For more details click here.
2. Block a request based on a Formula.
We can create JavaScript validation function in Oracle BI Answers which gets called when a user enters or modifies a column formula.
The criteriatemplates.xml file in [Installation Dir]\OracleBI\web\msgdb\messages have kuiFormulaBlockingScript message where we can define validateAnalysisFormula function. When a formula is changed or modified, Oracle BI answers calls this function, when true it executes the query else we can display a customized message to the user and block the request. The user can also decide to ignore the error by clicking OK button.
For more details click here.
Many Validation Helper Functions are available and are defined in queryblocking.js located in [Installation Dir]\OracleBI\web\app\res\b_mozilla\answers
| Validation Helper Function | Description |
| 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 determined. |
Reference: Oracle Business Intelligence Presentation Services Administration Guide, Version 10.1.3.2- December 2006- link
Cheers!
Deepak
