0. Workflow Overview & Goals

Dataset Source: Maven Analytics Pizza Sales Playground.

Business Goals:

  1. Find each pizza type's sales as a % of total sales across all pizzas.
  2. Find each pizza type's sales as a % of total sales within its category.

Quick Example

Imagine we have $100 in Grand Total sales. $80 of that comes from the "Classic" category, and $20 from "Chicken".

If the Hawaiian Pizza (a Classic pizza) sold $40, then:

  • Goal 1: Hawaiian is 40% of the Grand Total ($40 / $100).
  • Goal 2: Hawaiian is 50% of its Category Total ($40 / $80).

We will construct a Master Pizza dataset, summarize sales in three different ways, and combine them back together to calculate the percentages.

1. Inputs

We start by connecting to 4 distinct CSV files.

  • Fact side: order_details.csv (qty, pizza id) and orders.csv (date, time).
  • Dimension side: pizzas.csv (price, size) and pizza_types.csv (name, category).

2. Join #1: Fact Table

Goal: Add date and time to order details.

We use a Join tool to match order_details and orders on order_id.

Checkpoint: Why join these first? Because order_details tells us what was bought, but orders tells us when.

3. Join #2: Dimension Table

Goal: Merge pizza attributes.

Join pizza_types and pizzas on pizza_type_id. This acts as our primary lookup table.

4. Join #3: Master Pizza File

Goal: Combine Fact (sales events) with Dimension (pizza attributes).

Join the outputs of Join 1 and Join 2 together on pizza_id.

5. Formula: Calculate Pizza Sales

Add a Formula tool to calculate revenue line-by-line.

Pizza Sales = [quantity] * [price]

6. Data Format & Preparation (Summarize)

Branch the Master data into three Summarize tools to get different aggregation levels:

  1. Summarize A: Group by name and category. Sum Pizza Sales -> Total Sales per Pizza Type.
  2. Summarize B: Group by category. Sum Pizza Sales -> Total Sales per Category.
  3. Summarize C: NO grouping. Sum Pizza Sales -> Total Sales (produces a 1-row table).

7. Combine Totals (Join & Append)

Bring the aggregations back together.

  • Join: Connect Sum A and Sum B on category. Now each pizza row knows its category total.
  • Append Fields: Connect the Join output (T) and Sum C (S). This attaches the single overall Total Sales value to every single row.
Checkpoint: Why Append Fields instead of Join? Because the grand total has no key field to match on: it must be broadcast to everything via Cartesian join.

8. Final Formula: % Metrics

Use a final Formula tool to calculate the business requirements:

  • % of Total Sales = ([Total Sales per Pizza Type] / [Total Sales]) * 100
  • % of Category Sales = ([Total Sales per Pizza Type] / [Total Sales per Category]) * 100

9. Desired Output

Connect an Output Data tool to write the final polished dataset to pizza_output.xlsx (Sheet1).