Define Global Variables

This topic describes what you need to know to understand and define global variables for use in data filters and expressions.

Topics:

• About Global Variables

• Create a Global Variable

About Global Variables

Use a global variable when you need a variable's value to be the same for all users.

To define a global variable, you create or use an existing initialization block to contain one or more global variables. The initialization block contains an initialization string and connection pool to access the data source and return results to populate the global variables that you define. The global variables are then available for you to add to data filters or expressions.

For global variables, you can scheduled the initialization block to refresh variable values as needed.

A common use of global variables is to set filters in logical table sources. See About Global Variables and Logical Table Source Fragmentation.

Create a Global Variable

Create a global variable when you need the variable's value to be the same for all users, for example, current period or current year.

After you define and save global variables, they're available for you to add to the semantic model's data filters or expressions.

In a semantic model, you create and define a global variable within an initialization block. You can't create and define a standalone global variable and then later associate it with an initialization block.

Consider the following information when creating a global variable:

• If you add more than one variable to the initialization block, then the variables must match the column order in the initialization query. This is so each variable receives the proper value when the initialization query runs.

• The initialization query used to populate the variables must reference the data source tables needed for the variable values. You don't have to include the data source tables that supply the variable values in the semantic model's physical layer.

• If you're creating a variable to override selection steps in a hierarchy column, then use JSON syntax to write the initialization query. See Initialization Queries Used in Variables to Override Selection Steps.

• Because object permissions don't apply to variables, the values in variables aren’t secure and anyone who knows or can guess the name of the variable can use it in an expression. Because of this, Oracle recommends that you don’t put sensitive data like passwords in variables.

Follow these steps to create a global variable:

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Variables.
4. Click Create and then click Create Initialization Block.
5. In Create Initialization Block, go to the Name field and enter an initialization block name.
6. Go to the Type field and select Global. Click Add.
7. Confirm that the Variables tab is displayed, and then go to the Select Statement: DEFAULT field and enter the initialization query.
8. Go to Connection Pool and click Select to browse for and select a connection pool specifically for use in initialization blocks.
9. Click Add Variable and enter a unique name.
10. Use one of the following options to specify a default value.
    • Go to the Value field and enter a default value.
    • Leave the Value blank if you want null as the default value.
    • Click Detail View and click Open Expression Editor to create an expression that determines the default value.
11. Optional: Click Add Variable to add another variable, and use the Move Up and Move Down to position the variables in the correct order.
12. Optional: Click Test Query to review the variable values returned by the initialization query and to confirm that the variables are receiving the correct values.
13. Click Save.