Background
The recent release of our Input Tables beta has prompted numerous inquiries on the different use cases for the exciting new feature. Truthfully, Input Tables weren’t intended to address any one specific use case— while there are already many useful and creative ways we’ve learned of our customers leveraging Input Tables, the goal from the onset was to enable Sigma users to easily write their own data back to the warehouse for any use case they see fit. One of the most common ways we’ve seen Input Tables used is Scenario Modeling, so in this weeks How-To, we’ll show you the step-by-step process of speeding up your SQL modeling with Input Tables.
More Context
- Sigma offers the flexibility of injecting Control Element values into your Custom SQL. This enables us to create a dynamic UI element that can alter the underlying query behind a Data Element, based upon end user input.
- Control Elements can source their values from columns in Input Tables. This means that we can compile lists of various scenarios to explore, and make them dynamically adjustable to end users.
- The ease of data entry with Input Tables will allow you to write one dynamic SQL query, and hot swap various clauses and permutations without having to write, edit or alter the table’s SQL query.
- A user with Create or Explore access can interact with Input Tables to update the values supplied to the SQL Query. This means an Input Table can be used to manipulate a query to the fullest extent while in edit mode, and give creators strict control over what data is accessible to end users.
Process
- First, let’s set up our Input Table. Add a new Input Table to your workbook, and rename the default Text column that appears to ‘Where Clause’. This will be a source to inject different conditions to our SQL Query. Then, let’s populate the column with some default values.
- This is a dataset of retail transactions across several US states, so some possible conditions we may be interested in exploring are:
- Products that were purchased more than 2 at a time
- Orders containing at least one item of the ‘Music’ product type
- Orders containing products within the Music, Photography, or Electronics categories
- Orders where the price of a product therein is greater than $500
- All Rows
- Because the syntax of the query can be difficult to parse for end users, let’s add another text column to the input table called ‘Definition’, and add a brief description of the logic behind the corresponding where clause.
- Before we can create a SQL table, we must publish the workbook with a default parameter value. Select the ‘Show All Rows’ option from your new dropdown in order to show all rows by default, then click ‘Publish’ before navigating back into ‘Edit’ mode.
-
Create a new table from Custom SQL, injecting your dynamic Where clause from the Input Table, like so:
SELECT *
FROM plugs_electronics.hands_on_lab_data
WHERE {{#raw prm_Where_Clause}}
If there are any visualizations based off of the SQL table, they will dynamically update when new selections are made.