Friday, January 21, 2011

Implicit Fact Column in OBIEE

What is Implicit Fact Column and how it is defined?

In case we have multiple Fact tables, then it is a best practice to set an implicit fact column. Setting an implicit fact has two benefits.
1.     An implicit fact column help BI Server choose the join path when multiple join paths are available between facts and dimensions.
2.     When a report is generated only from dimension tables, it will result in an error as an Analyses should always consist of at least one fact or measure. To overcome this error we define an implicit fact column. This ensures that a fact is always included in the SQL generated by BI Server.

To set an implicit fact column:
Ø  In presentation layer, double click the presentation catalog/ go to properties of presentation catalog.
Ø  In the general tab, find the Implicit Fact Column section.
Ø  Click on Set… button, this will open the browse window to select a fact column from the fact tables available in the presentation catalog.
Ø  Select the column.
Ø  Click OK.
Ø  Ensure that the selected Implicit Fact Column is displayed in the Implicit Fact Column section in the general properties of Presentation Catalog.
Ø  Click OK.
Ø  Save your changes in the repository.

Tuesday, January 18, 2011

Administration: Oracle BI Presentation Services

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