Oracle Cloud Infrastructure Documentation

Create Variable

Create custom session variables that you can use in your semantic model.

The SQL query that you define is executed by user OAX$OAC. If you're using another schema in the query, then you must mention the schema name as prefix. You must ensure to grant user OAX$OAC access to all the database objects used in the query.
  1. Sign in to your service.
  2. In Oracle Fusion Data Intelligence Console, click Semantic Model Extensions under Application Administration.
  3. On the Semantic Model Extensions page, click User Extensions.
  4. On the Semantic Model Extensions page, under Customizations Sandbox, hover over an applicable sandbox to view Actions, and then click View Sandbox.
  5. On the selected sandbox Details page, click Perform Action, select Manage Variables, and then select Create Variable.

    Create Variable option

    You see the wizard sequence to add the session variables and a list of existing session variables.

  6. In step 1 of the wizard, check if any of the existing session variables serve your purpose. If yes, then you can exit the wizard and use the applicable existing session variables in your analyses. If no, then continue with the next steps to create the session variables that you require.
  7. In Initialization Block Name, enter a name such as Add a Session variable using Invoice Received Date, add a brief description, and select a preceding initialization block in Preceding Block.
  8. In SQL Query, enter the SQL query that would be executed in the autonomous data warehouse and return a value that you can use in the reports and click Next. For example, if you want to get the Exchange Rate Type that's defined in the system into a session variable, then you can use the following SQL script:
    Copy
         SELECT PARAMETER_VALUE FROM DW_CONTENT_PARAM_CONFIG WHERE
         PARAMETER_CODE='PARAM_GLOBAL_EXCHANGE_RATE_TYPE'
  9. In step 2 of the wizard, create the session variables using the output of the initialization block created in step 1 of the wizard. Select Row-wise Initialization to reset variable value for each row and Use caching check boxes to improve performance.
  10. Click Finish.