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:
A repository variable has a single value at any point in time.
Use repository variables instead of literals or constants in the 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:
The value of a static repository variable is initialized in the Variable dialog.
This value persists, and does not 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
where Hour is a logical column, perhaps 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 the Variable tab of the Variable dialog to set up a static repository variable named prime_begin and initialize it to a value of 17, and 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. In addition, you can use Expression Builder to insert a constant as the default initializer, such as Date, Time, and TimeStamp. You cannot use any other value or expression as the default initializer for a static repository variable.
In previous releases, the Administration Tool did not 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.
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 will follow to execute 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. You schedule these queries to be executed by the Oracle BI Server.
Dynamic repository variables are useful for defining the content of logical table sources. For example, suppose you have two sources for information about orders. One source contains recent orders and the other source contains historical data.
You need to describe the content of these sources on the Content tab of the Logical Table Source dialog. 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. Dynamic repository values can be set up to do it automatically.
Another suggested use for dynamic repository values is in WHERE clause filters of logical table sources, defined on the Content tab of the Logical Table Source dialog.
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.
Use these steps to create repository variables.
Use unique names for all variables. The names of system session variables are reserved. You cannot use system session variable names for other types of variables.
If you are creating a dynamic repository variable to override a hierarchy column's selection steps, then you must 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 Creating Initialization Blocks
Static repository variables must have default value defined in the Default initializer field. Static repository variables are constants that do not change values. If you initialize a variable using a character string, enclose the string in single quotes ( ' ).
After creating variables, the variables are available for use in Expression Builder.
Variables should be used as arguments of the function VALUEOF(). This happens automatically when you double-click the variables to paste them into the expression.
You cannot 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