A repository variable has a single value at any point in time. Repository variables can be used instead of literals or constants in expression builders in the Administration Tool. The Oracle BI Server will substitute the value of the repository variable for the variable itself in the metadata.
Static Repository Variables
The value of a static repository value is initialized in the Variable dialog box. This value persists, and does not change until an Oracle BI Administrator decides to change it.
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:
where Hour is a logical column, perhaps mapped to a timestamp physical column using the date-and-time Hour(<>) function.
Rather than entering the numbers 17 and 23 into this expression as constants, you could use the Variable tab of the Variable dialog box 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.
Using Variables in Expression Builders
After created, variables are available for use in expression builders. In an expression builder, click on 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. The expression builder will paste it into the expression at the active cursor insertion point.
Variables should be used as arguments of the function VALUEOF( ). This will happen automatically when you double-click on the variables to paste them into the expression.
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
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 will create an initialization block or use a pre-existing one that contains a SQL query. You will also set up a schedule that the Oracle BI Server will follow to execute the query and periodically refresh the value of the variable.
NOTE: When the value of a dynamic repository variable changes, all cache entries associated with a business model that reference the value of that variable will be 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.
Example
Dynamic repository variables are very 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 box. Without using dynamic repository variables, you would describe the content of the source containing recent data with an expression such as:
This content statement will become 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, that are defined on the Content tab of the Logical Table Source dialog box.
The values of dynamic repository variables are set by queries defined in Variable Initialization blocks. When defining a dynamic repository variable, you create an initialization block or use a preexisting block that contains a 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.
A common use of these variables is to set filters for use in Oracle BI Presentation Services. For example, to filter a column on the value of the dynamic repository variable CurrentMonth, set the filter to the Variable CurrentMonth.
Creating Repository Variables
Use the this task to create a repository variable. For more information, see Understanding and Creating Repository Variables.
- From the Administration Tool menu bar, choose Manage > Variables.
- In the Variable Manager dialog box, from the menu bar, choose Action > New > Repository > Variable.
- In the Variable dialog box, type a Variable name.
- In the Variables dialog box, select the type of variable: Static or Dynamic.
- (Dynamic repository variables) Use the Initialization Block drop-down list to select an existing initialization block that will be used to refresh the value on a continuing basis. To create a new initialization block, click New. For more information, refer to Process of Creating Initialization Blocks.
- (Dynamic or static variables) To add a Default initializer value, perform one of the following steps:
- To use the Expression Builder, click the ellipsis button to the right of the Default initializer work space. For more information about creating the value, refer to SQL Logical Operators.
- Type the value into the Default initializer text box.
- Click OK.
No comments:
Post a Comment