This QuickStart QS provides an overview and the steps required to recreate three unique use cases that leverage Sigma Input Tables.
Input Tables are Sigma-managed warehouse tables, through which users can add their own data and integrate into their own analysis.
When data isn't in the warehouse, it usually requires a cumbersome technical and people process to ETL data into the warehouse. Now users who need to add data to the warehouse are able to do so directly.
Sigma customers already use Input Tables for:
- Manual data entry of key values
- Analytic Modeling
- scenarios
- forecasts
- territory planning
- sales planning
- supply chain modeling
- categorizations
Target Audience
Anyone who is trying to create QS content using Sigma and wants to augment, adjust, interact and create "what-if" scenarios.
Prerequisites
- A computer with a current browser. It does not matter which browser you want to use.
- Access to your Sigma environment. A Sigma trial environment is acceptable and preferred.
- Some familiarity with Sigma is assumed. Not all steps will be shown as the basics are assumed to be understood.
- Downloadable project files discussed later in this document.
- A Snowflake account with the proper administrative and security admin access.
- Microsoft Excel or Google Sheets (for accessing the provided sample data)
What You'll Learn
We will review a few different ways customers are using Input Tables already and show you how to make them work in your own Sigma environment.
What You'll Build
We will review these three use cases:
- Forecasting
- Rapid Data Prototyping
- Territory Planning
In our first use case, Paula, the VP of EMEA sales, is running her End-of-Year forecast call where her country managers each call out the final numbers for the year.
She enters these into an Input Table which already has the targets for the year.
A map visualizes which countries are way off their targets.
She's also planning out the next year and adds growth numbers for each country.
This gets combined with a parameter that adjusts for inflation to show FY 23 vs 24 on a bar chart.
The final version looks like this:
How to build it:
Open Sigma and create a new Workbook.
Change the name of the Workbook to Input Table Use Cases.
Add a new Page to our Workbook and rename it to Forecast Adjustment.
Open the downloaded file in Excel (or Google Sheets). There should be 3 columns and 13 rows.
Add a new Input Table to the Page and rename it Forecasts from Country Managers.
Click here for information on setting up Write Access.
Copy all rows and columns from the downloaded Excel file and paste them into the new Input Table.
Notice that Sigma automatically identified the data types for you, saving time.
Add a new Visualization to the Page, use the Input Table as it data source, set it to Map - Region and drag the Country column up the Regions.
We want this Map to show different colors for FY23 Targets vs FY23 Forecasts so we need to add a new Column to the data that represents this calculated value.
In the Element Panel / Columns click the + icon and select new column.
Set the formula for this column to:
([FY23 Forecasts] - [FY23 Targets]) / [FY23 Targets]
Your map configuration should look like this now:
The manager in Austria tells you she's cutting her forecast down to $10k because there was a strike at one of the factories.
Update the input table and see the map change. Wait, we are not able to enter new values into the Input Table?
Edit the Workbook.
Now change the FY23 Forecast value for Austria down to $10K and hit Enter.
The Map changes automatically to reflect this revision.
We can also change a value in the Forecasts from Country Managers Input Table / Column Country Growth Adjustment and see similar changes automatically reflected in the Chart.
The Map will not change as it is not looking at FY24 projections.
Drive Input Table Values from Control
We can also drive the Input Table from in-page Controls. For example we can add a Control that allows the user to set a value for a Global Inflation Adjustment and have that update a chart.
Add a new Number Column to the Input Table and rename it to: Country Growth Adjustment.
Set the Column Type to be Percent.
Add a new Control / Text Box to the Page. Rename it to Global Inflation Adjustment.
Adjust it's properties as shown. The Control ID is case sensitive and it's current value will drive in calculations later:
Set a value in the new Control Global Inflation Adjustment = .10 and hit return.
Lets add a bar chart and compare the FY23 and FY24 forecasts (by country).
Add a Visualization and configure it as follows (we will manually add a calculated column for the X-Axis Column shown for F24 Forecast):
Lets add that new column to the X-Axis (click the + icon to the right of the X-Axis in the Element Panel) and select Add new column.
Set the formula bar for this new Column to:
(1 + [P_Global_Inflation] + Coalesce([Country Growth Adjustment], 0)) * [FY23 Forecasts]
Rename the new Column to FY24 Forecasts.
Click on the County column (in the Element Panel / Y-Axis) and set a filter on this map to not show any null values.
The Chart and it's configuration now looks like this:
Publish your Page.
After making any final tweaks you want to make to the layout of the page, it is ready, the VP can make some forecast changes.
Inflation is really bad right now, so increase the Global Inflation Adjustment parameter from 0.10 to 0.15 and hit enter.
The Bar Chart will respond, adjusting for the increase in inflation across the entirety of EMEA.
The manager for the Czech Republic has resolved some supply chain issues and is reporting an anticipated 10% forecast lift.
Enter the new value into the Country Growth Adjustment column for Czech Republic to reflect 10% uplift. Hit Enter and the Map and Chart update automatically to reflect the increase:
In our second use case, Lucy is a merchandiser at a brick and mortar (B&M) electronics retailer.
She's launching their online store which is powered by Shopify.
Although their B&M data is in the warehouse, the Shopify data is too new and hasn't gone through the modeling process, which takes time.
She can't wait 6 months and has to know now how the new online store is doing.
She has the Shopify data in google sheets where she's been playing around with it. But, it only shows part of the picture.
She needs to see the data from B&M against Shopify in real time.
Sigma allows Lucy to copy and paste the Shopify data into an Input Table and then is able to compare online vs B&M sales by category and SKU. This informs her about the strengths and weakness of the online business vs the established B&M, right away.
How to build it:
Open the downloaded file in Excel (or Google Sheets). There should be 5 columns and 82 rows.
We will copy this data into a Sigma Input Table later.
Add a new Page to our Sigma Workbook and rename it to Rapid Data Prototyping.
We will add two tables from the warehouse to the page. One for Product Information and another for Brick and Mortar Sales.
For Product Information we will use the Sigma Sample Database and the D_Product table as shown.
Rename the table to "Product Information".
Create a second Workbook Page and rename it Data. We will use this Page to hold master reference data that will be reused for this use case and the later ones too.
On the new Data page, add a new table as shown:
Rename the table to Brick and Mortar Sales.
At this point your Workbook should look like this:
Return to the Rapid Data Prototyping page.
Add a new Empty Input Table to the Page.
Use the Sigma Sample Database.
Rename the new Input Table Shopify Data.
Copy all rows and columns from the downloaded Excel file and paste them into the new Input Table as we did in the first use case.
The column headers are automatically set by Sigma.
Now that we have the B&M and the online product details, we want to be able to compare them.
In the Product Information table, click the Sku Number column drop and add a Column Lookup.
Configure the Lookup as shown:
Many products have not been sold online yet so right click on the first cell with a null and select Exclude null:
Rename this new column Shopify Reveune and format it as Currency.
Add another new column and use the same workflow.
This time we want to pull in the revenue from the B&M table. However, the column Revenue does not exist in the warehouse table Brick and Mortar Sales so we need to add that first.
Click on the Data page tab and add the new column to the Brick and Mortar Sales table.
select Add a new column and set the formula of that column to (it does not matter the order of the columns in this case):
Sum([Quantity] * [Price])
Rename the new column Revenue and set the format to Currency.
Now we can add a new column to the Product Information table called B&M Revenue using the same workflow for Add a new column via lookup...:
Rename the new column B&M Revenue.
Now that we have the data ready we want to add a new Visualization (Bar Chart in this case) to compare the revenue streams by category for B&M vs. Online.
Add a Child Element / Visualization (from the Product Information Table) and set the X and Y axis as shown:
Doing a tiny bit of additional clean-up, changing the names of the X-Axis columns and removing a grid-line and we have our chart:
What the chart shows is the relative strength of in-store sales vs. online for each category. Now Lucy (our Merchandiser) can begin to see which catagories are not selling well online or where to invest more marketing dollars.
This next use case is a bit more sophisticated, using Linked Input Tables and Data Validation to allow the user to make on-the-fly adjustments to a sales territory.
Mike, is VP of Sales for the US. The company has grown quickly and the regions need to be rebalanced.
Using Salesforce data in Snowflake, he's able to leverage the power of Sigma and see that the West region has brought in the most revenue.
Specifically, California sales are leading the charts.
Using a Linked Input Table, Mike can dynamically get a list of States and Regions based on the latest assignments.
This allows Mike to experiment with the re-balancing, instantly seeing how revenue would change by region based on the new mapping.
The final build will look like this:
In Sigma, create a new Page in our Input Tables Demo Workbook and rename it Territory Planning.
We will add tables to the Page from the Sigma Sample Database / Applications / Salesforce data source.
Add USERS as shown:
Add OPPORTUNITY as shown:
Since the Opportunity table does not contain Region or Territory we we add them using Sigma's Lookup function.
From the Opportunity table and click Add a new column via lookup as shown:
a
...and configure it as follows:
Do the same steps but this time add the USERS column SubRegion.
Your Page should now look like this:
Let's add a chart that shows the current distribution of Sales by Region so that we can evaluate what adjustments to territories may be needed.
Create a Child Element / Visualization from the Opportunity table. Configure it as follows to show the total sales by region:
Our chart is sorted by Sum of Amount to have the largest on top. Rename the chart to Sum of Amount by New Sub Region - BEFORE
We are ready to add a method for users to reassign territories and see the effect in real-time in terms of total regional sales.
Create a new Linked Input Table off the USER table:
In the new Linked Table, click the Sub Region column drop and add a new Text Column:
Now copy the values from the Sub Region column (click the column and use ctl+v or command+c) and then paste into the new column (use ctl+v or command+v).
Rename the new column to New Sub Region.
Now that we have a column where a user can change the value of region, we should add Data Validation
In the new Input Table, click the new column New Sub Region and select Data Validation from the list:
...and configure it as follows:
Now users are limited to selecting valid values from data that exists in Snowflake / USERS table.
Even though we configured the new column's Data Validation to point to the Input Table, the Sub Region column is "linked" to the data coming from the Users table so it will always be up to date.
Next we want to add a New column via lookup to the Opportunities table.
Configure it as follows We should see 100% match (item #4):
Rename the new column from New Sub Region (New Linked Input Table) to New Sub Region.
The Page now looks like this (after some re-sizing and arranging to suit your preference). Use any layout you want.
Lastly, we will add another Visualization (as a child of the Opportunities table) to represent the "after" state of user changes on the Linked Input Table.
Add a new Child Visualization and configure it as follows:
Rename the chart to Sum of Amount by New Sub Region - AFTER.
We are ready to evaluate the Territory and make some adjustments.
First we need to see why the Midwest so much larger than the TOLA in terms of sales. We want to close this gap if we can.
In the Before chart, click the Midwest bar and Drill down into Territory.
We can now see that Missouri is top of the chart. We wonder what if we reassigned it to TOLA?
Locate the Missouri in the New Linked Input Table / Territory column and change it's assignment in New Sub Region.
You can see that the AFTER chart recalcuates and TOLA is now larger with MidWest smaller but still not close enough.
Also change the New Sub Region for Iowa to be in TOLA.
The gap is much better now, Mike is happy.
Many customers are embedding content in their internal and external business applications. Now you can also embed Input Tables and really take it to the next level for your users. With Input Tables your applications can become more than just a way to communicate TO your users. Capturing small amounts of critical data from your customers opens up that "conversation" to be two-way.
This QuickStart assumes familiarity with how to embed in Sigma.
Create a new Workbook and add a new empty Input Table table to the page. You will be prompted to provide a location to save the Input Table date. We will use the Sigma Sample Database.
Now add another column and set it's type to number.
Enter a line of data and your page should now look like this:
Click the Input Tables menu and enable Allow data editing in explore mode.
Publish the Workbook.
Make sure that the Workbook is Shared to the Creator team with Explore or Creator rights:
Using the methods outlined in the Quickstart Embedding 3: Application Embedding, configure this Workbook page into an embed.
We will need to adjust server.js for the Embed Path, User Team and Account Type:
Assuming you started Terminal and ran supervisor server.js without error, browser to http://localhost:3000. You should see your embedded Input Table.
Enter some text and click Save.
Adjust server.js for the User Team and Account Type to lower the access to Viewer:
Refresh the browser and see that the information is as we entered it but there is no Edit functionality for this Viewer user.
If you recall, we enabled Allow data editing in explore mode. Lets test that.
In the Workbook / Sharing change the Viewer to use the Explore role:
Update server.js for account_type and external_user_team and save the file:
Refresh the browser and see that we have the Edit button.
There are times when capturing a small amount of data from a Sigma user can be very valuable to the business. This presents some challenges:
- Will you have to build some new application to capture the data?
- When is the right time to capture the data?
- Will the users accept another tool?
- Once we have the data, how will it relate to existing warehouse data?
- Lots of other questions to be sure!
Since users are already using Sigma (or spreadsheets and should be using Sigma instead!), Input Tables can help solve this problem. Why not just create a Sigma Workbook and augment it with the ability for the user to enter small amounts of data in real time? Sigma will do the heavy lifting of UI and data operations to store the data in your warehouse.
Allowing users to add/supplement warehouse data opens a world of possibilities. We can demonstrate how this can be done we will use a very simple example.
There is a need for accounting to update the status of Invoices, adding comments/notes but don't want to give the data entry clerk access to the accounting system.
We solution this in Sigma using Input Tables without involving developers or database administrators valuable time.
In our Workbook, create a new Page and rename it to Data Collection.
We need to use different data for this use case, joining two tables.
In our use case, the final lineage for the source data table looks like this:
In Edit mode, navigate to the Data Page.
Add a new Table for INVOICE_LINE_ITEMS as shown:
Join the CUSTOMERS table to this INVOICE_LINE_ITEMS as shown:
Configure the Join as follows:
After clicking Preview to look at the result set, click Done:
We don't need every column so let's delete the ones we wont require:
Rename the new Table to Customer Invoice Line Items.
Add a new column using the formula:
[Quantity] * [Unit Price]
Rename this column to Line Item Total.
Now create a Linked Input Table, selecting the columns as shown:
Move the new Input Table to the Page Data Collection:
We want the user to be able to filter the Input Table Table to specific Companies.
Add a new Element / Control Element / List Value element and configure it as follows:
Set the Target as:
We now are ready to add our columns that are enabled for data capture.
Add a new Column to the Input Table called Status.
Add another new Column to the Input Table called Notes.
We did Data Validation in an earlier use case but this time will just limit the Status column to values we will manually provide:
We want the user to "work" the data down so we need another Input List to filter the table, not showing rows that have a Status = Fulfilled,
Set a Filter on the Table to only show rows that have not been Fulfilled:
Now we can filter by Company, adjust the Status and add notes.
Last step before we test is to enable editing in Explorer Mode:
Publish the Workbook and go to the Publish version so that we are looking at this Page as the end-user would see and work with it.
Now we have an Edit Data button. Click that:
Lets test by updating the first four records as shown for each Status:
Click the Save button.
Each time we save, any records set Fulfilled are longer visible. Looking at Column Details for Status we see that there are no rows where Status = Fulfilled.
In this way, the user can just do the updates they need and not be concerned about records that are completed.
In this QuickStart we covered three popular used cases for Sigma Input Tables in great detail.
Additional Resource Links
Be sure to check out all the latest developments at Sigma's First Friday Feature page!
Help Center Home
Sigma Community
Sigma Blog
