Sigma Data App

|

Sigma Data App |

Build a Stagging App with Sigma Input Tables and Warehouse Views

Data App Design

Platform: Sigma, Snowflake

Forward-looking statement: The solution being showcased is suited to forecasting, scenario planning, and other non-critical use cases that do not concern the compliance of a transactional database and the durability of critical data storage

Use Case Definition:

  • The Big Picture: Imagine you are tasked to help the retail manager at Plugs Electronics Store, who produces the monthly Discounts List that determines which products are eligible for discounts.

  • The retail manager needs a user-friendly interface to create a “Discount Plan” that consists of

    • A list of SKUs that are discount-eligible for each monthly period

    • A discount matrix that maps out the discount percentage for different product categories

Why Date Scaffold?

  • Users are used to looking at calendar views that are continuous, but most tables are not built that way.

    • For example, in the original table from the Sigma WOW challenge, the flattened project management table only records the start date and end date of a particular project. The dates before the start, after the end, and during the task are not recorded.

    • Even in a transactional data set, dates without any active activities would not exist in the table.

    • If a date is not recorded in the source table, it is not presented in the visualization.

  • Date Scaffold can fill in the “missing” date records by transforming the shape of the data table.

    • This allows us to create calendar-style visualizations that users are used to. Even if there isn’t any activity taking place on a specific date, the date record is presented in the visualization.

How to Create a Date Scaffold?

  • The basic structure of the scaffold is cross joining every date throughout the entire time frame to every existing record.

    • There are multiple ways to create the scaffold, depending on what tech stack you have access to. If you only have Excel, it is doable in Excel. (We will demo it in Sigma)

  • We want to create a cross join between the flat table to a date helper table that simply records every single date throughout the time frame as a single row.

  • We will add a column to the joined table to indicate what is happening at every single date for each row in the original flat table.

  • (Please refer to the demo video to see how this is done step by step in Sigma)

Finally, visualization - Gantt Chart

  • We need to hack the system a little bit, since there is no default “Gantt Chart” type.

    • Line Chart and Scatter Plot are both capable of creating visually appealing Gantt Charts, but Line Chart offers the possibility of creating on-click actions for drill-downs.

  • Setting up the Line Chart Visual:

    • X axis: “All Date” field

    • Y axis: “Project Name” field (We are not using the Task Name field because it wouldn’t allow us to add separate reference bands later)

    • Trellis: “Task Name” field (This is the workaround to allow reference band per Task Name, more details in the demo video)

    • Label: Create a custom calc to return Task Name only if the period = “Project Ends” (This ensures the Task Name is only displayed once per task)

    • Reference band: Set it up to be “per chart”

      • Band start: If([Period_Start_End] = "Start", [Day of Date])

      • Band end: If([Period_Start_End] = "End", [Day of Date])

    • Color: Set the color of the line to match the color of the reference band