Create Variables to Use in Expressions

In Data Modeler, you can define variables that dynamically calculate and store values so that you can use those values in column expressions or data filters.

Topics:

• About Variables

• Define Variables

About Variables

Variables dynamically calculate and store values so that you can use those values in expressions. You can use variables in column expressions, or in data filters.

For example, suppose User1 belongs to Department1 and User2 belongs to Department2. Each user must access only the data that is specific to his department. You can use the DEPARTMENT_NUMBER variable to store the appropriate values for User1 and User2. You can use this variable in a data filter in which the data is filtered by Department2 for User1 and Department2 for User2. In other words, variables dynamically modify metadata content to adjust to a changing data environment.

Values in variables aren’t secure, because object permissions don’t apply to variables. Anybody who knows or can guess the name of the variable can use it in an expression. Because of this, don’t put sensitive data like passwords in variables.

You can’t use a variable in an expression that defines another variable.

Define Variables

You can create a variable for use in column expressions and data filters. For example, a variable called SalesRegion might use a SQL query to retrieve the name of the sales region of the user.

Tip:

Only reference source database objects in the SQL query for a variable. Don’t include names of semantic model objects in the query.

1. In Data Modeler, lock the model for editing.
2. In the Variables menu in the left pane, click the Plus icon.
3. Enter a SQL query to populate the value of the variable:
   1. Specify whether the variable returns A single value or Multiple values.
   2. Enter a SQL query to populate the value or values of the variable. For example:
      — Return a single value with the query like: SELECT prod-name FROM products
      — Return multiple values with a query like: SELECT 'MyVariable', prod-name FROM products

      For multiple values, always use the format: SELECT ‘VariableName’, VariableValue FROM Table

   3. Provide a default starting value if needed.
   4. Click Test to validate that the query returns an appropriate value
   
   Description of the illustration data_mod16.gif
4. To create a variable that refreshes its value at the start of each user session, select On sign in for Update Value.
5. To create a variable that refreshes its value on a schedule that you set, select On a schedule for Update Value.

   In the Run SQL Query area, select the frequency and start date for refreshing the variable.

6. To create a variable with a static value that never changes, select Never for Update Value and provide a value for the variable in the Value field.
7. Click Done to return to the semantic model.

   Tip:

   To edit an existing variable, right-click it in the Variables list and select Inspect. To delete a variable, right-click it and select Delete.

After you have defined a variable, you can use it in a data filter or in a column expression.