Create actions that call stored procedures (Beta)

🚩

This documentation describes a public beta feature and is under construction. This documentation should not be considered part of our published documentation until this notice, and the corresponding Beta flag on the feature in the Sigma service, are removed. As with any beta feature, the feature discussed below is subject to quick, iterative changes. The latest experience in the Sigma service might differ from the contents of this document.

Beta features are subject to the Beta features disclaimer.

Workbooks support actions that can call a stored procedure and allow you to use the output of the stored procedure as a variable.

For example, if you have an existing stored procedure in your data platform that you use to perform a complex calculation, rather than recreating the logic in a Sigma custom function or formula, you can call the stored procedure and use the output in Sigma.

This document explains how to create actions to call a stored procedure. For more information about actions in Sigma, see Intro to actions.

For specific end-to-end examples, see:

• Example: Run a stored procedure with column data
• Example: Run a stored procedure based on user input

Limitations

• Only Snowflake, Redshift, and BigQuery are supported.

• If you use audit logs, stored procedure activity is not yet included in audit logs.

• The stored procedure must be contained in a schema that is indexed by Sigma (available through the connection). Do not place any stored procedures in the same schema used for write back.

• Variant argument types are not supported.

• To work with the return value or output of a stored procedure, the stored procedure must return a scalar value.

  • If a stored procedure returns tabular data, you cannot work with the output as a variable and instead must locate the created table in the data catalog for the connection to work with it in Sigma.
  • If the stored procedure returns an array, modify the stored procedure to convert the array to text (serialize) and then deserialize the data in the stored procedure.

User and system requirements

📘

The following requirements apply to users who configure actions. Users who access and interact with a workbook can typically trigger all existing actions within it. Any restrictions are noted in this document.

The ability to configure actions requires the following:

• You must be assigned an account type with the Full explore or Create, edit, and publish workbooks permission enabled.

• You must be the workbook owner or be granted Can explore¹ or Can edit workbook permission.

1 If you’re granted Can explore workbook permission, you can configure actions but cannot save changes to the workbook’s published version.

You cannot define stored procedures in Sigma. Before you can call a stored procedure in Sigma, you must create it in your data warehouse or data platform.

To call a stored procedure from an action, the user and role associated with the connected data platform must have permission to use and execute the stored procedure in the data platform.

• For a BigQuery connection, if the service account is granted the BigQuery Data Viewer role, no additional permissions are required. See Connect to BigQuery.

• For a Redshift connection, the service account must be granted the following permissions:

  • USAGE on the schema(s) that contain the stored procedure
  • EXECUTE on all procedures, or specific stored procedures in the schema.

  See Connect to Redshift.

• For a Snowflake connection, the user's default role or the service account role must be granted the following privileges:

  • USAGE on the schema(s) that contain the stored procedure
  • EXECUTE on all procedures, or specific stored procedures in the schema.

  See Connect to Snowflake.

📘

If a user with Can Explore or Can Edit access to the workbook does not have access to use or execute stored procedures, they cannot see or modify the stored procedure. The stored procedure does not appear when browsing available connections, and an already configured action appears blank. Users that can use but cannot execute a stored procedure can see the stored procedure, but attempts to call the stored procedure with the action fail.

Call a stored procedure from an action

Create an action that calls a stored procedure in your data platform.

1. Open a workbook in Explore or Edit mode.
2. Select the trigger element (the element users must interact with to initiate the action), then click Actions in the side navigation.
3. In the Actions panel, click Add action in an existing sequence, or click Add action sequence to create a new one.
4. In the Action modal, configure the required fields to define the response:

• When selecting cells in	Select a column from the trigger element to initiate the action when clicked. This field only applies when the trigger element is a table, pivot table, or input table.
  Condition	[optional] Turn on the switch to configure a condition that should be met for the action to take effect.
  Action	In Advanced, select Call stored procedure.
  Select a stored procedure	Search or browse to the stored procedure that you want to call, then select the stored procedure.

• Configure the stored procedure setup. These steps might be different depending on the arguments and signatures expected by the stored procedure:

• Select signature	If the stored procedure has multiple signatures, in the list of signatures, select the desired signature for the stored procedure.
  Set value for: <argument>	Assign values to the stored procedure argument using one of the following options: - Static values: Enter a value for the argument. - Column: If the action is triggered from a data element, choose a column in the data element to which the action applies to provide the argument value. - Control: Choose a control element in the workbook to provide the argument value. - Formula: Enter a formula to use to provide the argument value. The provided argument value must match the data type expected by the stored procedure.
  Outputs	If the stored procedure provides an output, review the variable name and data type with which the output is available. For example, for a stored procedure named "echo_args", the action variable is echo_args.

5. If the trigger element is a table, pivot table, or input table, configure additional settings that determine when and how user interaction triggers the action sequence:
   • To trigger the action sequence only when a user selects a cell in a specific column, click the dropdown following the On select heading and select the column. To trigger the action sequence when a user selects a cell in any column, select Any column.
   • [optional] To control whether keyboard navigation within the element can trigger action sequences on the element, click More in the Actions panel, then select Allow keyboard to trigger actions. When the option displays a checkmark, keyboard navigation and pointer events (e.g., mouse clicks) can trigger the action sequences. When the option doesn't display a checkmark (default), only pointer events can trigger them.

     💡

     Keyboard navigation as a trigger interaction can disrupt the user experience. For example, if the element's action sequences include actions that open links or other workbooks, a user can be unintentionally navigated away from their current task. This can be particularly disruptive if the action sequence can be triggered by selecting a cell in any column.

     Consider allowing keyboard navigation to trigger actions only when it facilitates the configured action sequences and is unlikely to interfere with usability.

6. If the trigger element is a plugin, select the name of the plugin configuration object under Custom plugin. In your code editor, refresh your plugin, then test the action in the workbook. For more information, see Configure plugins to use as trigger elements.
7. [optional] To execute the action sequence only when a specific condition is met, click More in the action sequence, then select Add condition and configure the criteria. For more information about conditions, see Define an action condition.

📘

When using a stored procedure action in an action sequence, Sigma waits for the stored procedure to finish running before triggering the next action in the sequence. If the stored procedure fails with an error, the action sequence stops running and any following actions in the sequence do not run.

For more details about calling a stored procedure with an action, see the following examples.

Example: Run a stored procedure with column data

As an end-to-end example, run a stored procedure based on user selection in a data element.

For example, if you have a stored procedure in BigQuery called GET_CAMPAIGN_STATUS that compiles and calculates the latest clickthrough data for an email campaign performed in a third-party tool. The stored procedure takes one argument, the name of the email campaign, and outputs text with the plain text body of the email.

1. Open your workbook for editing.

2. Add relevant workbook elements:

   1. Add a data element, for example a table with a row for each email campaign sent by your marketing department, with columns like Campaign Name and Date Sent.
   2. Add a text area control next to the data element and rename the control Campaign details.

3. On the data element, define an action sequence that runs On select from the Campaign Name column:

   1. Add the Call stored procedure action and configure it:
      1. Navigate to the BigQuery connection and search for the GET_CAMPAIGN_STATUS stored procedure.
      2. For Set value for arg1: name, choose Column, then choose the Campaign Name column in the data element that contains the name of the email campaign.
      3. Note the action variable used for the response, get_campaign_status.
   2. Add the Set control value action and set the value to the Action variable: get_campaign_status.
   

4. Test the workflow. In this example, a modal appears with the text of the email campaign:

   

5. Publish your workbook.

💡

Add the output control to a modal to add interactivity and reduce visual clutter. Set the action sequence for the primary button for the modal to clear the control and close the modal.

Example: Run a stored procedure based on user input

As another end-to-end example, run a stored procedure based on user input.

For example, recalculate the project budget requirements based on several inputted values. In this example, assume that you have access to a stored procedure in Redshift called ESTIMATE_BUDGET that calculates the estimated budget for a project. The stored procedure takes 3 number arguments, one for estimated materials cost, one for estimated number of employees, and one for number of weeks, and outputs a string with a budget estimate.

1. Open your workbook for editing.

2. Add relevant workbook elements:

   1. Add three number input control elements to use as the input for the calculation, named:

      • Materials cost
      • Headcount
      • Weeks

      💡

      Use a slider control instead of a static value to set a number, which allows you to constrain the input values.

   2. Add a text input control element to display the output of the stored procedure, named Estimated budget.

   3. Add a button element. Update the button title to Estimate the budget!.

   

3. On the button element, define an action sequence with the following actions:

   1. Add the Set control value action and target the Estimated budget control element. Set the value to "Calculating…"
   2. Add the Call stored procedure action.
      1. Navigate to the Redshift connection and search for the ESTIMATE_BUDGET stored procedure.
      2. For Set value for arg1: cost, choose Control, then choose the Materials cost control.
      3. For Set value for arg2: headcount, choose Control, then choose the Headcount control.
      4. For Set value for arg3: weeks, choose Control, then choose the Weeks control.
      5. Note the action variable used for the response, estimate_budget.
   3. Add the Set control value action and target the Estimated budget control. Set the value to an Action variable, then choose the estimate_budget variable to display the output of the stored procedure.
   

4. Test the workflow:

   1. Add numbers to each input control, then click the button.
   2. Review the output control, Estimated budget.
   

5. Publish the workbook.

💡

Add another button with an action sequence that inserts a row to an input table with the values of all 4 control elements to keep a historic record of the various budget estimates, then clears all the controls on the page to reset the configuration.