OBIEE 11.1.1.7.1 – What is difference between FIRST & FIRST_PERIOD Function

There are two repository functions available to select first returned value of the expression argument, FIRST AND FIRST_PERIOD. This is blog is an attempt to explain the difference between these functions and elaborate on when and where to use these functions.

FIRST

This function selects the first non-null returned value of the expression argument. It uses primary level key not based on the chronological key.

The FIRST function is limited to defining dimension-specific aggregation rules in a repository. It cannot be used in SQL statements. The FIRST function operates at the most detailed level specified in your explicitly defined dimension. For example, if you have a time dimension defined with hierarchy levels day, month, and year, the FIRST function returns the first day in each level.

One should not use the FIRST function as the first dimension-specific aggregate rule. It might cause queries to bring back large numbers of rows for processing in the Oracle BI Server, causing poor performance. In addition there is one more limitation that one cannot nest PERIODROLLING, FIRST, FIRST_PERIOD, LAST, and LAST_PERIOD functions.

Syntax

FIRST (expr):- expr is any expression that references at least one measure column

Example

A measure is aggregated as FIRST based on HO Time Dimension, Data is dense is not selected.

FIRST("01 - Sample App"."F4 Headcount Base Measures"."9- Full Time Empl  (Beg Period)"

FIRST_PERIOD

This function selects the first returned value of the expression argument. For example, the FIRST_PERIOD function can calculate the value of the first day of the year.

Use the FIRST_PERIOD function instead of the FIRST function whenever you want to compute the first value based on the chronological key rather than the primary level key. Also, FIRST returns the first non-null value, whereas FIRST_PERIOD returns the first value, regardless of whether it is null.

The FIRST_PERIOD function operates at the most detailed level specified in your explicitly defined dimension. For example, if you have a time dimension defined with hierarchy levels day, month, and year, the FIRST_PERIOD function returns the first day in each level.

One should not use the FIRST function as the first dimension-specific aggregate rule. It might cause queries to bring back large numbers of rows for processing in the Oracle BI Server, causing poor performance. In addition there is one more limitation that one cannot nest PERIODROLLING, FIRST, FIRST_PERIOD, LAST, and LAST_PERIOD functions.

Syntax

FIRST_PERIOD (expr):- expr is any expression that references at least one measure column

Example

FIRST_PERIOD("01 - Sample App"."F4 Headcount Base Measures"."9- Full Time Empl  (Beg Period)")

Inference

In short FIRST returns the first non-null value, whereas FIRST_PERIOD returns the first value. FIRST_PERIOD requires "data is dense" flag to be checked in the Logical Column Aggregation tab in Admin Tool. If that flag is not check, these function just become First.

 I hope you find this information useful.