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.
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.
