Example - Create and Use a Multi-Source Session Variable

This topic provides an example of how to create and use the MVCOUNTRY multi-source session variable.

Topics:

Create a Multi-Source Session Variable

This topic explains how to create the MVCOUNTRY multi-source session variable. When you add the MVCOUNTRY variable to an expression or data filter, it returns data from the Oracle and Snowflake data sources.

After you create a multi-source session variable, the variables definitions are listed in the initialization block's definition (for example, MVCOUNTRY____ORCL and MVCOUNTRY____SNFL). But in the Expression Editor, the multi-source session variable name is displayed (for example, MVCOUNTRY).

See About Multi-Source Session Variables.

  1. On your home page, click Navigator Navigator icon and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Create the first variable.
    1. Click Variables Variables icon.
    2. Click Create Create icon and then click Create Initialization Block.
    3. In Create Initialization Block, go to the Name field and enter mvcountry_orcl_init.
    4. Go to the Type field and select Session. Click OK.
    5. Confirm that the Variables tab is displayed, and then go to the Query Returns field and select Variable names and values.
    6. Go to the Select Statement: DEFAULT field and enter the following initialization query, using four underscores between the variable name (MVCOUNTRY) and the source (ORCL):
      select distinct 'MVCOUNTRY____ORCL', country from oracle_table
    7. Go to Connection Pool and click Select to browse for and select a connection pool.
    8. Click Add Variable Add variable icon and enter the name MVCOUNTRY____ORCL. You need to add four underscores between the variable name and the source name.
    9. Click Save.
  4. Create the second variable.
    1. Click Variables Variables icon.
    2. Click Create Create icon and then click Create Initialization Block.
    3. In Create Initialization Block, go to the Name field and enter mvcountry_snfl_init.
    4. Go to the Type field and select Session. Click Add.
    5. Confirm that the Variables tab is displayed, and then go to the Query Returns field and select Variable names and values.
    6. Go to the Select Statement: DEFAULT field and enter the following initialization query, using four underscores between the variable name (MVCOUNTRY) and the source (SNFL):
      select distinct 'MVCOUNTRY____SNFL', country from snowflake_table
    7. Go to Connection Pool and click Select to browse for and select a connection pool.
    8. Click Add Variable and enter the name MVCOUNTRY____SNFL. You need to add four underscores between the variable name and the source name.
    9. Click Save.

Use a Multi-Source Session Variable in an Expression

After you create the MVCOUNTRY multi-source session variable, you can use it in an expression.

For information about the MVCOUNTRY session variable used in this example, see Create a Multi-Source Session Variable.

This is an example of how to use the multi-source session variable in an expression: 

select lastName, firstName, country from employee 
where country=VALUEOF(NQ_SESSION.MVCOUNTRY)

Use a Multi-Source Session Variable in a Data Filter

After you create the MVCOUNTRY multi-source session variable, you can use it in a data filter.

The MVCOUNTRY multi-source session variable is displayed in the Expression Editor's Variables tab. For information about the MVCOUNTRY session variable used in this example, see Create a Multi-Source Session Variable.

  1. On your home page, click Navigator Navigator icon and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Browse for and open a logical or presentation table.
  4. Click the Data Filters tab.
  5. Go to the Add field, enter the application role that you want to set the data filter for, click Search by Role Name, and from the list select the application role.
  6. In the Role Name list, click the role you added to highlight it, and then click Open Expression Editor.
  7. Enter this expression:
    Country=VALUEOF(NQ_SESSION.MVCOUNTRY)
  8. Optional: Click Validate.
  9. Click Save to save the expression.
  10. Click Save to save the semantic model.