Work with Repository Variables

Learn about repository variables and how to create repository variables in these topics.

This section provides information about working with repository variables, and contains the following topics:

• About Repository Variables

• Create Repository Variables

About Repository Variables

A repository variable has a single value at any point in time.

Use repository variables instead of literals or constants in the Model Administration Tool Expression Builder. The Oracle BI Server substitutes the value of the repository variable with the variable in the metadata.

This section contains the following topics:

• About Static Repository Variables

• About Dynamic Repository Variables

About Static Repository Variables

The value of a static repository variable is initialized in the Variable dialog.

The value of the static repository variable persists and doesn't change until an administrator decides to change it. For example, suppose you want to create an expression to group times of day into different day segments. If Prime Time were one of those segments and corresponded to the hours between 5:00 PM and 10:00 PM, you could create a CASE statement like the following:

CASE WHEN "Hour" >= 17 AND "Hour" < 23 THEN 'Prime Time' WHEN... ELSE...END

Hour is a logical column, mapped to a timestamp physical column using the date-and-time Hour(<<timeExpr>>) function.

Rather than entering the numbers 17 and 23 into this expression as constants, you could use a static repository variable named prime_begin, initialize the variable to a value of 17, and then create another variable named prime_end and initialize it to a value of 23.

Static repository variables must have default initializers that are either numeric or character values. You can use the Expression Builder to insert a constant as the default initializer, such as Date, Time, and TimeStamp. You can't use any other value or expression as the default initializer for a static repository variable.

In previous releases, the Model Administration Tool didn't limit the values of default initializers for static repository variables. Because of this, if your repository has been upgraded from a previous release, you may see warnings in the Consistency Checker similar to the following:

The variable, 'Current Month' does not have a constant default initializer.

If you see warnings similar to this, update the relevant static repository variables so that the default initializers have constant values.

About Dynamic Repository Variables

You initialize dynamic repository variables in the same way as static variables, but the values are refreshed by data returned from queries.

When defining a dynamic repository variable, you create an initialization block or use a preexisting one that contains a SQL query. You also set up a schedule that the Oracle BI Server uses to run the query and periodically refresh the value of the variable. When the value of a dynamic repository variable changes, all cache entries associated with a business model that reference the value of that variable are purged automatically.

Each query can refresh several variables, one variable for each column in the query. In Oracle BI Server, create a schedule to run the queries.

Dynamic repository variables are useful for defining the content of logical table sources. If, for example, you've two sources for information about orders, one source contains recent orders and the other source contains historical data, you need update the repository to use the recent orders and move the historical order data to a different view.

You describe the content of the sources in the Logical Table Source. Without using dynamic repository variables, you would describe the content of the source containing recent data with an expression such as:

Orders.OrderDates."Order Date" >= TIMESTAMP '2001-06-02 00:00:00'

This content statement becomes invalid as new data is added to the recent source and older data is moved to the historical source. To accurately reflect the new content of the recent source, you would have to modify the fragmentation content description manually. You can set up dynamic repository values to do automatically modify the content.

Another suggested use for dynamic repository values is in WHERE clause filters of logical table sources.

A common use of these variables is to set filters for use in Oracle BI Server. For example, to filter a column on the value of the dynamic repository variable CurrentMonth, set the filter to the variable CurrentMonth.

Create Repository Variables

Use these steps to create repository variables.

Use unique names for all variables. The names of system session variables are reserved, so you can't use system session variable names for other types of variables.

When you create a dynamic repository variable to override selection steps in a hierarchy column, choose an initialization block with its initialization string written in JSON syntax. See Initialization Strings Used in Variables to Override Selection Steps. To create a new initialization block, click New; see Create Initialization Blocks.

Static repository variables must have a default value defined in the Default initializer field. Static repository variables are constants that don't change values. If you initialize a variable using a character string, enclose the string in single quotes ( ' ).

1. In the Model Administration Tool, select Manage, then select Variables.
2. In the Variable Manager, From Action, select New , then select Repository , and select Variable.
3. In the Repository Variable dialog, in Name, type a name for the variable.
4. From Type, select one of the following:
   • Static
   • Dynamic
5. If you selected Dynamic, from the Initialization Block list select an existing initialization block to refresh the value on a continuing basis.
6. In Default initializer, type the value for the repository variable, or click the Expression Builder button to define an expression to use as the variable value.
7. Click OK.

Use Repository Variables in Expression Builder

After creating variables, you can use them in the Expression Builder.

Use variables as arguments of the function VALUEOF(). This happens automatically when you double-click the variables to paste them into the expression.

You can't use variables to represent columns or other repository objects.

For example, the following CASE statement is identical to the one explained in the preceding example, except that variables have been substituted for the constants:

CASE WHEN "Hour" >= VALUEOF("prime_begin")AND "Hour" < VALUEOF("prime_end") THEN 'Prime Time' WHEN ... ELSE...END

• In Expression Builder, click the Repository Variables folder in the left pane to display all repository variables (both static and dynamic) in the middle pane by name.
• To use a repository variable in an expression, select it and double-click. Expression Builder pastes it into the expression at the active cursor insertion point.