Calculations: EVALUATE, FILTER, and Level-Based Measures

Explain how EVALUATE, FILTER, and level-based (context-changing) formulas let analysts write calculations OBIEE's standard aggregation model can't express directly.

Most calculations are ordinary. Three mechanisms handle the ones the standard aggregation model cannot express.

FILTER is like asking "what would my grade be if only my math scores counted?" without retaking the whole semester. EVALUATE is like handing a translator a note in the destination language directly, bypassing your own broken grammar entirely. Level-based measures are like posting the country's total population next to every city's row in a table, even though the table itself is sorted city-by-city.

Key Concepts

FILTER computes a measure under a different filter context.

    FILTER("Sales"."Revenue" USING "Geography"."Country" = 'US')

FILTER

In an analysis filtered to Europe, that column still returns US revenue. It ignores the surrounding filter for that column only.

The use is comparison: this region against the total, this product against its category, actuals against a fixed baseline — all in one analysis rather than two.

Level-based measures change the grain rather than the filter.

    revenue                   at the row's grain -- by product
    revenue at Category level aggregated to the category

    % of category = Revenue / Revenue-at-Category-Level

That ratio is impossible in a plain analysis, because the denominator is at a different grain than the row. A level-based measure is defined in the RPD by setting the measure's aggregation level in a hierarchy, and it is the correct answer to "percentage of parent".

EVALUATE passes a function through to the database.

    EVALUATE('REGEXP_SUBSTR(%1, ''[0-9]+'')' AS CHARACTER, "Customer"."Code")

EVALUATE

The literal string is sent to the database, which runs its own function. It is the escape hatch for anything OBIEE's function library lacks — regular expressions, analytic functions, database-specific features.

And it is the one to use sparingly.

    it ties the model to one database's SQL dialect
    the BI Server cannot reason about it -- so aggregate
      navigation and caching behave less well
    it is disabled by default for security, since it injects
      SQL into the generated statement

EVALUATE_SUPPORT_LEVEL in the configuration controls whether it is allowed at all, which tells you how Oracle views it.

EVALUATE_SUPPORT_LEVEL

The decision worth stating. A calculation used by one report belongs in the analysis. A business definition belongs in the RPD, once. EVALUATE is for something the tool genuinely cannot express, and if it appears in twenty analyses the real answer is a derived column in the warehouse.

EVALUATE

What the interviewer is probing.1. "What does EVALUATE do and what is the caution?" Probing: the power and the risk. Stalls: "It calculates." Moves up: passes a database function through to the source — powerful, but it ties the analysis to that database and must be enabled in configuration.

2. "What is the FILTER function for?" Probing: the capability. Stalls: "Filtering." *Moves up:* a measure filtered independently of the analysis filter, which is how you show this year beside last year in one row.

3. "What is a level-based measure?" Probing: the modelling answer. Stalls: "A total." *Moves up:* a measure fixed at a hierarchy level regardless of the query grain, which is how percent-of-total is computed correctly.

4. "Where should a reusable calculation live?" Probing: the governance. Stalls: "In the analysis." Moves up: in the RPD business model, so every analysis gets the same definition rather than ten slightly different ones.