All topics
8 / 55
OBIEEintermediate

Session & Repository Variables

Distinguish repository (static) variables from session (dynamic/system/non-system) variables and how each is used in security, personalization, and dynamic filtering.

Variables are how one RPD and one set of dashboards behave differently per user and per day, without duplicating anything.

A repository variable is like the date printed on today's newspaper — same for every reader. A session variable is like the name printed on your hotel room key card — unique to you, generated the moment you check in.

Key Concepts

1
Repository variables are shared by everyone.
    static      a fixed value, set in the RPD
    dynamic     refreshed on a schedule by an initialisation block
2
    CURRENT_FISCAL_YEAR   refreshed nightly:
                          SELECT fiscal_year FROM d_time
                           WHERE current_flag = 'Y'
3
Every report needing "this year" references the variable. Without it, the year is hardcoded in dozens of filters and January is a deployment.
4
Session variables are per user, established at login.
    system      reserved names the server uses:
                USER, ROLES, LOGLEVEL, DISABLE_CACHE_HIT
    non-system  yours: USER_REGION, EMPLOYEE_ID, COST_CENTRE
5
A session initialisation block populates them:
6
    SELECT region_code FROM d_user_security
     WHERE username = ':USER'
7
:USER is substituted with the authenticated user, so each session gets its own value.
:USER
8
The scope confusion is the common bug. A repository variable holds one value for the whole server. Use one where you meant a session variable and every user sees whatever the last refresh set — including somebody else's region.
9
    repository variable  one value, all users, refreshed on a schedule
    session variable     one value per user, set at login
10
Which matters most for security. Row-level filters are written against session variables:
11
    Sales.Region = VALUEOF(NQ_SESSION.USER_REGION)
12
And the failure mode to design for. If the initialisation query returns no row for a user, the variable is unset.
13
    filter becomes  Region = (nothing)
    -> depending on configuration: no rows, or an error, or the
       filter is dropped entirely
14
The third is the dangerous one, and it is why the initialisation block should be set to fail the login when it returns nothing, rather than allowing a session with no security context.
15
Testing this means testing with a user who is not in the security table, which is the case people do not think to try.
16
A practical note on caching. Session variables form part of the cache key, so users with different variable values correctly get different cache entries. That is what stops one user's cached rows being served to another.
17
What the interviewer is probing.1. "What is the difference between the two?" Probing: the fundamentals. Stalls: "Nothing." Moves up: repository variables are set by scheduled initialisation blocks and are the same for everyone; session variables are per user at login.
18
2. "What is the classic use of each?" Probing: the practical angle. Stalls: "Filters." Moves up: a repository variable for the current reporting period; a session variable for the user's region or employee id driving row security.
19
3. "What is an initialisation block?" Probing: the mechanism. Stalls: "A setting." *Moves up:* a query run against a source to populate variables, with a defined refresh for repository ones and execution at login for session ones.
20
4. "What is the performance risk?" Probing: the practical warning. Stalls: "None." *Moves up:* a slow session initialisation block runs for every login, so it delays every user — including during a morning login spike.
Use cases:variablesrow-level-securitypersonalization