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.

4 comments:

  1. is there any work around for implicit fact? i.e. without out selecting the implicit fact check box. is there any work around.

    ReplyDelete
  2. Hi Arjun,

    No, There is no workaround for implicit fact. When BI Server generates a query it needs a join between two tables to retrieve results. If you are querying two dimensions which do not have a join, how can you expect results? Defining an implicit fact make sure a join (relationship) exists.

    Cheers!
    Deepak

    ReplyDelete
  3. Hi, It would be nice if you provide examples along with the text. Nice write up. Simple & easy. Please include screenshots and examples to make it more useful.

    ReplyDelete