Apache Superset for Operational Dashboards in Retail

Table of Contents

1. Why Apache Superset for Retail Operations
2. Data Modelling for Retail Operational Dashboards
3. Dashboard Design Principles for Retail
4. Building Your First Operational Dashboard
5. Performance Optimisation and Caching Strategies
6. Rollout Patterns and Change Management
7. Security and Access Control
8. Real-World Retail Use Cases
9. Common Pitfalls and How to Avoid Them
10. Next Steps: Moving from Pilot to Scale

---

Why Apache Superset for Retail Operations

Retail operations move fast. Your store managers need to know in-shift whether they’re tracking to sales targets, whether inventory is flowing correctly, and whether staffing levels match demand. By 2 PM, yesterday’s data is already stale. By tomorrow morning, you’ve missed the window to act on yesterday’s exceptions.

Most retail organisations start with Excel, move to Tableau or Looker, and then hit a wall: per-seat licensing costs spiral at $100–$200 per user per month, and you need 50+ seats just to get store managers, category leads, and regional directors visibility. A single retailer running 200 stores pays $120,000+ annually just for BI software—before engineering time, data pipeline costs, or the operational burden of managing user access.

Apache Superset changes that equation. It’s an open-source, self-hosted business intelligence platform built for speed and scale. Unlike traditional BI tools, Superset runs on your infrastructure, costs nothing per user, and integrates directly with your data warehouse or operational database. For retail, that means:

• No per-seat licensing: Unlimited users, unlimited dashboards, one infrastructure cost.
• Sub-second query latency: Real-time operational visibility across stores, categories, and regions.
• Embedded analytics: Dashboards live inside your retail management system, not in a separate tool.
• Flexible data sources: Connect to PostgreSQL, MySQL, Snowflake, BigQuery, ClickHouse, or any JDBC-compatible database.

Retail organisations we’ve worked with at PADISO have replaced expensive per-seat BI tools with Superset and cut dashboard delivery time from 4 weeks to 4 days. Store managers get real-time visibility without learning a new tool. Regional teams can drill into exceptions without waiting for the BI team to build a report.

But Superset isn’t magic. It requires careful data modelling, thoughtful dashboard design, and a disciplined rollout pattern. Get those three things right, and you’ll have a lean, fast, scalable operational intelligence platform. Get them wrong, and you’ll have slow dashboards, confused users, and technical debt that stalls future projects.

This guide walks you through all three.

---

Data Modelling for Retail Operational Dashboards

Your data model is the foundation. If your model is wrong, your dashboards will be slow, your metrics will be inconsistent, and your users will stop trusting the numbers.

Retail operational dashboards typically answer these questions:

• Sales: Are we tracking to plan? Which categories, stores, or regions are underperforming?
• Inventory: Do we have stock where we need it? Are we overstocked or understocked?
• Labour: Are we staffed for demand? What’s our labour cost per transaction?
• Shrinkage: What’s our actual loss rate? Where are the hotspots?
• Customer: Traffic, conversion, basket size, loyalty engagement.

Each of these domains requires a separate fact table, designed for the questions you’re actually asking.

Fact Tables and Dimensions

A fact table in retail is typically one of these:

Sales Fact Table (fact_sales)

• One row per transaction (or per line item, depending on your query patterns).
• Columns: transaction_id, store_id, category_id, product_id, date_key, time_key, quantity, net_sales, discount, margin, tender_type.
• Granularity: transaction-level or daily-per-store, depending on your refresh cadence and query volume.

Inventory Fact Table (fact_inventory)

• One row per location per day (or per shift, if you track intraday).
• Columns: store_id, location_id, product_id, date_key, on_hand, on_order, damaged, reserved.
• Granularity: daily snapshot or real-time if you have a connected POS.

Labour Fact Table (fact_labour)

• One row per employee per shift (or per day, aggregated).
• Columns: store_id, employee_id, date_key, shift_start, shift_end, hours_worked, labour_cost, department.
• Granularity: shift-level or daily, depending on your payroll system’s refresh cycle.

Shrinkage Fact Table (fact_shrinkage)

• One row per cycle per store (e.g., weekly or monthly inventory reconciliation).
• Columns: store_id, cycle_date_key, category_id, expected_inventory, actual_inventory, variance_units, variance_percent, variance_cost.
• Granularity: cycle-level (typically weekly or monthly).

Dimension tables are the lookup tables that add context:

Store Dimension (dim_store)

• Columns: store_id, store_name, region_id, district_id, format (supermarket, convenience, etc.), opening_date, square_footage, manager_id.
• Use slowly changing dimensions (SCD Type 2) if store attributes change: track valid_from and valid_to dates so historical queries remain accurate.

Product Dimension (dim_product)

• Columns: product_id, sku, product_name, category_id, subcategory_id, supplier_id, cost, list_price, is_active.
• Again, use SCD Type 2 if prices or categories change.

Date Dimension (dim_date)

• Columns: date_key, calendar_date, day_of_week, week_of_year, month, quarter, fiscal_year, is_weekend, is_holiday.
• This is critical for retail: you need to flag holidays, trading periods, and promotional windows so you can compare like-for-like.

Time Dimension (dim_time)

• Columns: time_key, hour, minute, period_of_day (e.g., ‘morning’, ‘afternoon’, ‘evening’).
• Use this if you track intraday traffic or labour patterns.

Aggregation Tables and Pre-Aggregates

Raw transaction-level fact tables can be huge. A single store with 500 transactions per day generates 182,500 rows per year. A 200-store chain generates 36.5 million rows per year. If your Superset dashboard queries the raw fact table every time, you’ll hit performance walls fast.

Instead, build aggregation tables (also called “aggregate fact tables” or “pre-aggregates”):

Daily Sales by Store and Category (agg_sales_daily_store_category)

• Columns: store_id, category_id, date_key, transactions, quantity_sold, net_sales, discount_total, margin_total.
• One row per store per category per day.
• Refresh daily via your ETL pipeline.
• Queries against this table are 10–100x faster than raw fact tables.

Hourly Traffic by Store (agg_traffic_hourly_store)

• Columns: store_id, date_key, hour, customer_count, transactions.
• One row per store per hour.
• Useful for labour scheduling and staffing optimisation dashboards.

Weekly Inventory by Store and Category (agg_inventory_weekly_store_category)

• Columns: store_id, category_id, week_key, avg_on_hand, min_on_hand, max_on_hand, stockouts.
• One row per store per category per week.
• Helps identify chronic understocking or overstocking patterns.

The key is to build aggregates at the grain your dashboards actually query. If your store managers look at daily data by store and category, build a daily store-category aggregate. If your regional directors look at weekly data by region and department, build a weekly region-department aggregate. Don’t over-aggregate (you’ll lose detail) and don’t under-aggregate (you’ll stay slow).

Conformed Dimensions and Consistency

If your sales fact table uses one store_id encoding and your labour fact table uses a different one, your dashboards will break. If your date dimension uses one fiscal calendar and your inventory system uses another, your year-to-date numbers won’t reconcile.

Build a single conformed dimension layer:

• One dim_store that all fact tables reference.
• One dim_date with a single fiscal calendar.
• One dim_product that maps SKU to category to supplier.
• One dim_employee that maps employee ID to department to manager.

If your source systems use different encodings, create mapping tables in your ETL layer and join them before loading your fact tables. This is tedious but essential. Inconsistent dimensions are a silent killer of trust in your dashboards.

Handling Time Zones and Store-Local Time

If you operate across multiple time zones (or even multiple regions within Australia), be explicit about time. A sale at 11:59 PM Sydney time is tomorrow’s sales in Perth, but today’s sales in Melbourne.

Store all timestamps in UTC in your fact tables. Create a dim_time_zone dimension that maps store_id to time zone offset. In your dashboards, allow users to toggle between UTC and store-local time, and always label which you’re showing.

For daily reporting, use the store’s local date, not UTC date. A store manager in Brisbane cares about Brisbane sales for the Brisbane day, not UTC day.

---

Dashboard Design Principles for Retail

Once your data model is solid, dashboard design is where you win or lose user adoption.

Most retail dashboards fail because they try to show everything: 20 KPIs, 15 charts, drill-down on every dimension. Users open the dashboard, get overwhelmed, and go back to Excel.

According to Atlassian’s guide to operational dashboards, the best operational dashboards answer one clear question per screen and support one primary action.

The Three-Dashboard Pattern

Build three types of dashboards, each with a specific purpose and audience:

1. Executive Dashboard (1 dashboard, 5–7 KPIs)

• Audience: Store manager, regional director, CEO.
• Refresh: Daily (or real-time if your POS integrates).
• KPIs: Sales vs. plan, traffic, conversion, basket size, labour cost %, shrinkage %.
• Design: Large, scannable numbers. One colour for target vs. actual. Drill-down to store or category level.
• Example: A single screen showing yesterday’s sales by region, with a red flag if any region is >5% below plan.

2. Operational Dashboard (3–5 dashboards, 8–12 KPIs per dashboard)

• Audience: Store staff, category leads, inventory managers.
• Refresh: Hourly (or real-time).
• KPIs: Vary by role (e.g., inventory managers see stock levels and reorder alerts; category leads see sales by subcategory and margin trend).
• Design: Detailed, actionable. Show exceptions first (out-of-stock items, overstaffed shifts). Include drill-down and filtering.
• Example: An inventory dashboard showing low-stock items by store, with a “reorder” button that creates a purchase order.

3. Analytical Dashboard (2–3 dashboards, 15+ KPIs, open-ended exploration)

• Audience: Category managers, planners, merchandisers.
• Refresh: Daily or weekly (not real-time).
• KPIs: Everything else—elasticity, promotional lift, cannibalization, SKU performance, seasonal trends.
• Design: Flexible, exploratory. Heavy use of filters and drill-down. Expect users to spend 20+ minutes here.
• Example: A product performance dashboard where users can filter by region, time period, and promotion type, and compare elasticity across categories.

Layout and Visual Hierarchy

IBM’s dashboard design guide emphasises clarity and visual hierarchy. For retail operational dashboards, follow these rules:

Rule 1: KPIs First, Charts Second At the top of every operational dashboard, show the headline KPI in large text. For a store manager, it’s “Sales YTD: $2.3M (98% of plan)” in 48-point font. Below that, show the supporting charts (sales trend, sales by category, etc.).

Rule 2: Use Colour Sparingly Red for alert (e.g., out of stock, >10% below plan). Green for on-track. Grey for neutral. Don’t use colour to decorate; use it to signal action. A dashboard with 10 colours is noise.

Rule 3: Arrange by Importance, Left to Right, Top to Bottom Western readers scan top-left to bottom-right. Put your most important chart (e.g., sales vs. plan) in the top-left. Put supporting detail (e.g., sales by category) below or to the right.

Rule 4: One Insight per Chart If a chart requires more than 10 seconds to understand, it’s too complex. A line chart showing sales trend over 13 weeks is clear. A line chart showing sales trend by 50 categories is noise. Use filters instead: “Show me sales trend for [category].” Let users choose what to see.

Rule 5: Label Axes, Add Context Every axis should be labelled. Every number should have a unit (dollars, units, %). Every chart should have a title that answers a question: “Sales vs. Plan (Last 13 Weeks)” not “Sales Trend.”

Designing for Mobile and Embedded

Retail staff check dashboards on their phones. A 1600-pixel-wide dashboard breaks on a 375-pixel screen.

If you’re embedding Superset dashboards in your retail management system (e.g., showing inventory levels in your POS), design for mobile-first:

• Vertical layout: Stack charts vertically, not side-by-side.
• Large touch targets: Buttons and filters should be at least 44×44 pixels.
• Responsive filters: Use dropdown filters, not multi-select checkboxes. One tap to filter, not five.
• Readable text: Minimum 14-point font for labels, 18-point for KPI numbers.

Test your dashboards on a phone before you roll out. If your regional director can’t read the sales number on their iPhone, they won’t use it.

Filtering and Drill-Down

Give users control without overwhelming them. For a store manager dashboard:

• Always-visible filters: Store (default: their store), date range (default: last 13 weeks), category (default: all).
• Drill-down: Click a bar in the sales-by-category chart to see subcategory detail. Click a subcategory to see SKU detail.
• Breadcrumb navigation: Show “Sales > Category > Subcategory > SKU” so users know where they are and can step back.

Don’t give users 20 filter options. Every filter adds cognitive load. Stick to 3–5 core filters per dashboard.

---

Building Your First Operational Dashboard

Let’s walk through a concrete example: a store manager’s daily sales dashboard.

Step 1: Define the Question

What does a store manager need to know every morning? “Did we hit our sales target yesterday? Where did we win, and where did we miss?”

That’s it. One question. Everything else is supporting detail.

Step 2: Identify the Data

You need:

• agg_sales_daily_store_category (or raw fact_sales if you only have a few stores).
• dim_store (to filter by store).
• dim_category (to group by category).
• dim_date (to filter by date).
• A sales_plan table (to compare actual vs. plan).

Step 3: Write the Core Query

SELECT
  s.calendar_date,
  st.store_name,
  c.category_name,
  SUM(a.net_sales) AS actual_sales,
  SUM(a.transactions) AS transaction_count,
  p.planned_sales,
  ROUND(100.0 * SUM(a.net_sales) / p.planned_sales, 1) AS percent_of_plan
FROM agg_sales_daily_store_category a
JOIN dim_store st ON a.store_id = st.store_id
JOIN dim_category c ON a.category_id = c.category_id
JOIN dim_date s ON a.date_key = s.date_key
LEFT JOIN sales_plan p ON a.store_id = p.store_id
  AND a.category_id = p.category_id
  AND a.date_key = p.date_key
WHERE s.calendar_date = CURRENT_DATE - 1  -- Yesterday
  AND st.store_id = ${store_id}  -- Parameterised by store
GROUP BY 1, 2, 3, 5
ORDER BY actual_sales DESC;

This query gives you yesterday’s sales by category for a single store, with plan comparison.

Step 4: Build Charts in Superset

Chart 1: Headline KPI (Big Number)

• Query: SELECT SUM(net_sales) FROM agg_sales_daily_store_category WHERE store_id = ${store_id} AND date_key = YESTERDAY.
• Display: “Sales Yesterday: $45,200” in 60-point font.
• Colour: Green if ≥95% of plan, red if <95%.

Chart 2: Sales vs. Plan by Category (Bar Chart)

• Query: The core query above.
• X-axis: Category name.
• Y-axis: Actual sales (blue bar), planned sales (grey line or stacked bar).
• Hover: Show transaction count and % of plan.

Chart 3: Sales Trend (Line Chart)

• Query: Last 13 weeks of daily sales (or last 4 weeks if you want finer detail).
• X-axis: Date.
• Y-axis: Sales.
• Reference line: Average sales (or plan).
• Colour: Blue for actual, grey for plan.

Chart 4: Transactions by Hour (Bar Chart)

• Query: SELECT hour, COUNT(*) FROM fact_sales WHERE store_id = ${store_id} AND date_key = YESTERDAY GROUP BY hour.
• X-axis: Hour of day (8 AM, 9 AM, etc.).
• Y-axis: Transaction count.
• Use this to identify slow periods and staffing mismatches.

Step 5: Assemble the Dashboard

Layout:

┌─────────────────────────────────┐
│ Sales Yesterday: $45,200 (98%)  │  <- Headline KPI
├─────────────────────────────────┤
│ Sales vs. Plan by Category      │  Sales Trend (13 weeks)
│ [Bar chart]                     │  [Line chart]
├─────────────────────────────────┤
│ Transactions by Hour            │  Store Filter: [Dropdown]
│ [Bar chart]                     │  Date Range: [Picker]
└─────────────────────────────────┘

Add filters at the top:

• Store: Dropdown, default to user’s home store (you’ll set this in access control).
• Date Range: Date picker, default to “Last 13 weeks.”
• Category: Multi-select, default to “All.”

Step 6: Test and Validate

Before you roll out:

1. Validate numbers: Run the query directly against your database. Do the numbers match your POS? If not, debug your ETL.
2. Test filters: Change store, date range, category. Do the charts update correctly?
3. Check performance: Does the dashboard load in <3 seconds on a typical internet connection? If not, optimise (see next section).
4. User test: Show a store manager the dashboard. Can they understand it in 30 seconds? Does it answer their question? If not, redesign.

---

Performance Optimisation and Caching Strategies

A slow dashboard is a dead dashboard. If a chart takes 10 seconds to load, users won’t wait. They’ll go back to Excel.

According to Preset’s guide to fast Superset dashboards, the difference between a 1-second dashboard and a 10-second dashboard is the difference between adoption and abandonment.

Query Optimisation

1. Use Aggregation Tables Never query raw fact tables if you can avoid it. A query against agg_sales_daily_store_category (millions of rows) is 10x faster than a query against fact_sales (hundreds of millions of rows).

If you don’t have aggregation tables yet, build them. This is your highest-ROI investment.

2. Add Indexes Index the columns you filter and join on:

CREATE INDEX idx_agg_sales_store_date ON agg_sales_daily_store_category(store_id, date_key);
CREATE INDEX idx_dim_store_id ON dim_store(store_id);
CREATE INDEX idx_dim_date_key ON dim_date(date_key);

Ask your database administrator (or your cloud data warehouse) to add indexes. This is a 5-minute job that can make queries 10x faster.

3. Denormalise Where It Matters If you’re always joining agg_sales_daily_store_category to dim_store to get store_name, add store_name as a column in the aggregation table. Yes, this duplicates data. But it eliminates a join and makes queries faster.

Denormalise for speed; normalise for consistency. Strike a balance.

4. Limit Data Scans If your dashboard filters by date, make sure that filter is applied before aggregation:

-- SLOW: Aggregates all data, then filters
SELECT * FROM (
  SELECT store_id, SUM(sales) FROM fact_sales GROUP BY store_id
) WHERE date >= '2024-01-01';

-- FAST: Filters first, then aggregates
SELECT store_id, SUM(sales) FROM fact_sales
WHERE date >= '2024-01-01'
GROUP BY store_id;

In Superset, this means using native filters (filters applied at query time) rather than post-query filters (filters applied to results).

Caching Strategies

Even with optimised queries, some dashboards will be slow if they’re queried 100 times per day by 50 store managers.

Superset supports three levels of caching:

1. Query Result Caching Superset caches query results in Redis (or your configured cache backend). If two users run the same query within the cache TTL, the second user gets the cached result.

Configure this in superset_config.py:

CACHE_CONFIG = {
  'CACHE_TYPE': 'redis',
  'CACHE_REDIS_URL': 'redis://localhost:6379/0',
  'CACHE_DEFAULT_TIMEOUT': 3600,  # 1 hour
}

DATASET_CACHE_TIMEOUT = 3600  # Cache dataset queries for 1 hour

For a store manager dashboard that refreshes daily, 1 hour is reasonable. For an operational dashboard that needs real-time data, use 5 minutes.

2. Database-Level Caching (Materialized Views) If your database supports materialized views (PostgreSQL, Snowflake, BigQuery do), create a materialized view for your aggregation tables and refresh it on a schedule:

CREATE MATERIALIZED VIEW agg_sales_daily_store_category_mv AS
SELECT
  store_id,
  category_id,
  date_key,
  COUNT(*) AS transactions,
  SUM(quantity) AS quantity_sold,
  SUM(net_sales) AS net_sales,
  SUM(discount) AS discount_total,
  SUM(margin) AS margin_total
FROM fact_sales
GROUP BY store_id, category_id, date_key;

-- Refresh every hour
REFRESH MATERIALIZED VIEW agg_sales_daily_store_category_mv;

Queries against materialized views are instant because the aggregation is pre-computed.

3. Data Warehouse-Level Caching (ClickHouse, Druid) If you use ClickHouse or Druid, you get built-in columnar compression and caching. A query that scans billions of rows runs in milliseconds because only the relevant columns are read and cached.

For retail organisations with high-volume transaction data, ClickHouse is a game-changer. PADISO’s platform development teams in Sydney, Melbourne, and Brisbane often recommend ClickHouse + Superset for retail analytics because it eliminates the aggregation layer entirely: you query raw transactions and get sub-second results.

Superset-Specific Performance Tuning

CelerData’s guide to optimising Superset dashboards recommends:

1. Use Native Filters: Filters applied at query time are faster than post-query filters.
2. Limit Chart Types: Avoid complex visualisations (scatter plots, maps with thousands of points). Use simple bar, line, and number charts.
3. Pre-Filter Large Datasets: If a chart displays 1,000+ data points, add a default filter (e.g., “Top 20 categories”) to reduce rendering time.
4. Increase Timeout: Set SUPERSET_TIMEOUT to 60 seconds for complex queries (default is 30).
5. Use Async Queries: Enable async query execution so slow queries don’t block the UI.

---

Rollout Patterns and Change Management

You’ve built a beautiful dashboard. Now you need to roll it out to 200 stores without breaking anything.

Rollout patterns matter because a bad rollout kills adoption. Store managers see the dashboard, don’t understand it, and go back to their old process. Six months later, you’re maintaining two systems.

The Pilot-Wave-Scale Pattern

Phase 1: Pilot (Weeks 1–2)

• Deploy to 5–10 stores (ideally early adopters who are excited about new tools).
• Provide daily support: answer questions, fix bugs, iterate on design.
• Collect feedback: what’s confusing? What’s missing? What’s working?
• Success metric: 80% of users in pilot stores use the dashboard daily.

Phase 2: Wave 1 (Weeks 3–4)

• Deploy to 50 stores (one region or district).
• Provide weekly support (not daily).
• Run a 1-hour training session for store managers and assistant managers.
• Success metric: 70% daily usage; <5 support tickets per day.

Phase 3: Wave 2 (Weeks 5–6)

• Deploy to another 75 stores.
• Provide on-demand support (email, Slack).
• Create a 5-minute video tutorial and FAQ.
• Success metric: 60% daily usage; <3 support tickets per day.

Phase 4: Scale (Week 7+)

• Deploy to remaining stores.
• Support is self-service (FAQ, video, Slack).
• Monthly office hours for advanced users.

Training and Documentation

Don’t assume users will figure it out. Create three levels of documentation:

1. 30-Second Quick Start

• One image showing the dashboard with callouts: “Your store’s sales are here. Click this button to see last week.”
• Printed and posted in store offices.

2. 5-Minute Video

• Screen recording: “How to Check Yesterday’s Sales and Find Your Top Categories.”
• Posted on your intranet or YouTube.
• Closed captions for accessibility.

3. FAQ Document

• “Why does my store show $0 sales?” → “The dashboard updates at 6 AM. Check back after 6 AM.”
• “How do I see last month’s data?” → “Click ‘Date Range’ at the top and select ‘Last 30 Days’.”
• “Can I export this to Excel?” → “Yes, click the download icon in the top-right corner.”

Managing Change and Resistance

Some store managers will resist. They’ve been using Excel for 10 years and don’t see why they should change.

Address this head-on:

1. Show the time saving: “This dashboard takes 30 seconds. Your Excel report takes 10 minutes. That’s 45 hours per year you get back.”
2. Show the accuracy: “This data updates every hour. Your Excel report is 2 days old. You’re making decisions on stale data.”
3. Show the peer adoption: “80% of stores in the Western region are using this. Your stores are the only ones still on Excel.”
4. Make it easy to switch: Embed the dashboard in your existing retail management system so users don’t have to log into a separate tool.

Measuring Adoption

Track these metrics:

• Daily Active Users: % of store managers who access the dashboard at least once per day.
• Feature Adoption: % of users who use filters, drill-down, or export features.
• Support Tickets: Number of “how do I…” questions per week (should decrease over time).
• Business Impact: Do stores with high dashboard adoption perform better? (Causation is hard to prove, but correlation is useful.)

If daily active users are below 50% after 4 weeks, something’s wrong. Go back to pilot stores and ask why people aren’t using it. Redesign accordingly.

---

Security and Access Control

Your operational dashboards contain sensitive data: sales by store, labour costs, shrinkage rates. A store manager shouldn’t see another store’s data. A regional director should see all stores in their region, but not other regions.

Superset’s role-based access control (RBAC) handles this, but you need to set it up correctly.

Users and Roles

Create roles that map to your organisational structure:

Role: Store Manager

• Can view dashboards for their own store only.
• Can export data.
• Cannot edit dashboards or create new ones.

Role: Regional Director

• Can view dashboards for all stores in their region.
• Can create ad-hoc reports.
• Cannot edit core dashboards.

Role: Category Manager

• Can view dashboards for their category across all stores.
• Can filter by store, region, time period.
• Cannot see labour or shrinkage data (not their domain).

Role: BI Analyst

• Can view all dashboards.
• Can edit dashboards and create new ones.
• Can access the SQL editor.

Row-Level Security (RLS)

RBAC controls which dashboards a user can see. Row-level security controls which rows of data they can see.

In Superset, you implement RLS by adding a WHERE clause to every query based on the logged-in user:

SELECT * FROM agg_sales_daily_store_category
WHERE store_id IN (
  SELECT store_id FROM user_store_mapping WHERE user_id = ${current_user_id}
)

Set up a user_store_mapping table that maps each user to the stores they’re allowed to see:

user_id | store_id
--------|----------
101     | 1001
101     | 1002
102     | 1001
102     | 1003
102     | 1004

When user 101 logs in and views a dashboard, all queries automatically filter to stores 1001 and 1002. User 101 can never see store 1003’s data, even if they try to hack the query.

Audit Logging

Enable audit logging in Superset so you can track who viewed what:

LOGGING_CONFIG = {
  'version': 1,
  'formatters': {
    'standard': {
      'format': '[%(asctime)s] %(levelname)s [%(filename)s:%(lineno)d] %(message)s'
    },
  },
  'handlers': {
    'audit': {
      'level': 'INFO',
      'class': 'logging.handlers.RotatingFileHandler',
      'filename': '/var/log/superset/audit.log',
      'maxBytes': 10485760,  # 10 MB
      'backupCount': 10,
      'formatter': 'standard',
    },
  },
  'loggers': {
    'superset.models': {
      'handlers': ['audit'],
      'level': 'INFO',
    },
  },
}

Review audit logs monthly to spot unusual access patterns (e.g., a store manager viewing data from 50 different stores).

---

Real-World Retail Use Cases

Let’s walk through three concrete examples of operational dashboards that drive real business impact.

Use Case 1: Daily Store Performance Dashboard

Business Question: “Did we hit our sales target yesterday? Where did we win and lose?”

Users: Store managers, assistant managers.

Refresh: Daily at 6 AM (after overnight POS close).

Dashboards:

1. Headline: Sales vs. plan (big number, red/green).
2. Sales by Category: Bar chart showing actual vs. plan.
3. Sales Trend: 13-week line chart.
4. Transactions by Hour: Identifies slow periods for labour scheduling.
5. Margin by Category: Ensures discounting isn’t eroding profit.

Business Impact:

• Store managers identify underperforming categories within hours, not days.
• Regional directors spot regional trends (e.g., all stores in a region underperforming on a category) and can investigate root cause.
• Merchandising team can respond to slow categories with promotions or resets before the week ends.

Use Case 2: Inventory Optimisation Dashboard

Business Question: “Do we have the right stock in the right places?”

Users: Inventory managers, category leads, merchandisers.

Refresh: Daily (or real-time if you have a connected inventory system).

Dashboards:

1. Out-of-Stock Items: List of items that are out of stock in >3 stores, with reorder quantity.
2. Overstock Alert: Items with >60 days of inventory, flagged for markdown or donation.
3. Inventory by Store and Category: Heatmap showing stock levels (red = low, green = adequate).
4. Inventory Turnover: Days of inventory on hand by category, trended over 13 weeks.
5. Shrinkage by Category: Variance between expected and actual inventory.

Business Impact:

• Inventory managers reorder automatically based on out-of-stock alerts, reducing manual processes.
• Merchandisers identify overstocked items and plan markdowns before inventory becomes obsolete.
• Category managers spot seasonal patterns and adjust reorder points accordingly.
• Shrinkage dashboard identifies high-loss categories for investigation (e.g., theft, waste, process issues).

Use Case 3: Labour Efficiency Dashboard

Business Question: “Are we staffed appropriately for demand?”

Users: Store managers, labour schedulers, regional directors.

Refresh: Daily (or real-time if you have a connected scheduling system).

Dashboards:

1. Labour Cost %: Actual labour cost as % of sales (target: 12–14%, varies by format).
2. Staffing vs. Traffic: Scheduled hours vs. actual traffic, by hour of day.
3. Transactions per Labour Hour: Productivity metric (higher is better, but watch for burnout).
4. Overtime Hours: Identifies stores consistently over-scheduling.
5. Absence Rate: Tracks callouts and unplanned absences by store and department.

Business Impact:

• Store managers adjust schedules based on traffic forecasts, reducing labour cost without sacrificing service.
• Labour schedulers identify chronic over-staffing (e.g., a store consistently has 15% excess labour) and adjust baseline schedules.
• Regional directors spot stores with high absence rates and investigate root causes (e.g., low morale, retention issues).
• Finance team validates labour cost vs. budget and investigates variances.

---

Common Pitfalls and How to Avoid Them

Pitfall 1: Inconsistent Metrics

Problem: Your sales dashboard shows $100K in sales, but your finance team’s monthly report shows $95K. Store managers don’t trust either number.

Root Cause: Different definitions of “sales.” Dashboard includes gift card sales; finance excludes them. Dashboard uses transaction time; finance uses settlement time.

Solution: Document every metric definition and store it in a central “metrics dictionary.” For “sales,” document: “Net sales excluding gift cards, taxes, and returns, using transaction time (not settlement time).” Reference this definition in every dashboard.

Pitfall 2: Slow Dashboards

Problem: Your inventory dashboard takes 15 seconds to load. Store managers give up and go back to Excel.

Root Cause: You’re querying raw fact tables with billions of rows. You have no indexes. You’re not caching.

Solution: Build aggregation tables, add indexes, and enable caching (see Performance Optimisation section).

Pitfall 3: Too Many Filters

Problem: Your dashboard has 15 filters (store, region, category, subcategory, date, time period, format, manager, etc.). Users get confused and don’t know which filters to use.

Root Cause: You tried to make one dashboard for everyone. Store managers don’t need to filter by region; regional directors don’t need to filter by store manager.

Solution: Build separate dashboards for different roles. Store manager dashboard has store filter (default: their store). Regional director dashboard has region filter (default: their region).

Pitfall 4: Stale Data

Problem: A store manager checks the dashboard at 3 PM and sees sales data from 2 days ago. They make a decision based on stale data.

Root Cause: Your ETL pipeline runs once per day at midnight. Your data warehouse is not connected to your POS.

Solution: Determine your refresh requirement. For operational dashboards, aim for hourly refresh (or real-time if you can). For analytical dashboards, daily refresh is fine. Clearly label every dashboard with the refresh time: “Data last updated: 2024-01-15 at 2 PM.” If data is >4 hours old, add a warning banner.

Pitfall 5: No Context or Targets

Problem: Your dashboard shows sales of $45,200. Is that good or bad? The user has no idea.

Root Cause: You showed the metric in isolation, without plan, target, or historical context.

Solution: Always show context:

• vs. Plan: “$45,200 vs. plan of $46,000 (98%).”
• vs. Last Year: “$45,200 vs. $42,000 last year (+7.6%).”
• vs. Trend: “$45,200 vs. 13-week average of $44,000 (+2.7%).”

Use colour (red, yellow, green) to signal whether the metric is on track.

Pitfall 6: No Drill-Down or Exploration

Problem: A regional director sees that their region is 10% below plan. They want to know which stores are dragging down the region, but the dashboard doesn’t let them drill down.

Root Cause: You built static dashboards with no interactivity.

Solution: Build dashboards with drill-down and filtering. Click a region to see stores. Click a store to see categories. Click a category to see SKUs. Let users explore.

---

Next Steps: Moving from Pilot to Scale

You’ve built your first dashboard, piloted it with 10 stores, and it’s working. Now what?

Step 1: Formalise Your Data Model

Don’t build dashboards ad-hoc. Formalise your data model:

1. Document your fact and dimension tables.
2. Create a data dictionary (metric definitions, table schemas, refresh schedules).
3. Set up automated tests: every morning, run a query to check that yesterday’s data loaded correctly.
4. Assign ownership: who maintains the data model? Who adds new metrics?

If you don’t formalise now, you’ll have 20 dashboards built on 20 different interpretations of “sales,” and you’ll spend all your time reconciling numbers.

Step 2: Build a Dashboard Template Library

As you build more dashboards, you’ll notice patterns. Create templates:

• Daily Performance Dashboard: Headline KPI, trend, category breakdown, hourly detail.
• Weekly Trend Dashboard: 13-week trend, week-over-week change, category trend.
• Inventory Dashboard: Out of stock, overstock, turnover, shrinkage.

When a new team asks for a dashboard, give them a template and ask them to fill in the metrics. This speeds up delivery from 4 weeks to 4 days.

Step 3: Invest in Data Infrastructure

Superset is only as good as your data. Invest in:

1. ETL Pipeline: A reliable data pipeline that loads transaction data from your POS to your data warehouse hourly (or real-time if you can).
2. Data Warehouse: A scalable warehouse (Snowflake, BigQuery, ClickHouse) that can handle your transaction volume and support fast queries.
3. Data Governance: A process for adding new data sources, defining metrics, and managing access.

If your data pipeline breaks, your dashboards are useless. Invest accordingly.

Step 4: Embed Dashboards in Your Applications

Store managers shouldn’t have to log into Superset separately. Embed dashboards in your retail management system:

1. Single Sign-On (SSO): Integrate Superset with your identity provider (Okta, Azure AD) so users log in once.
2. Embedded Dashboards: Use Superset’s guest token API to embed dashboards in your app without requiring a Superset login.
3. Mobile: Build a mobile app that displays dashboards optimised for small screens.

If you’re building a retail platform from scratch, consider PADISO’s platform development services in Sydney, Melbourne, and other Australian cities. We specialise in embedding analytics (Superset + ClickHouse) into retail management systems so your users never leave the app.

Step 5: Build a Community of Users

Your dashboards will only be adopted if users feel ownership. Build a community:

1. Monthly Office Hours: A 1-hour video call where users can ask questions and request new dashboards.
2. Slack Channel: A dedicated Slack channel for dashboard questions and feedback.
3. User Advisory Board: A quarterly meeting with 5–10 power users to discuss roadmap and priorities.
4. Recognition: Celebrate wins. “Store 42 identified a $50K opportunity using the inventory dashboard. Great work!”

Step 6: Measure Business Impact

Finally, measure whether your dashboards are actually driving business results:

1. Adoption: % of users accessing dashboards daily.
2. Time Saved: How many hours per week do users save by using dashboards instead of Excel?
3. Decision Speed: How much faster do decisions happen? (E.g., “We used to take 2 weeks to reorder overstocked items. Now it’s 2 days.”)
4. Business Outcome: What’s the financial impact? (E.g., “Inventory dashboard identified $2M in obsolete stock, which we marked down for $500K loss instead of $2M loss.”)

If you can’t measure impact, you won’t get budget for the next phase.

---

Conclusion

Apache Superset is a powerful tool for retail operational dashboards. But power without discipline leads to slow dashboards, inconsistent metrics, and user frustration.

Get three things right:

1. Data Model: Build aggregation tables, add indexes, use conformed dimensions. Your data model is the foundation.
2. Dashboard Design: Answer one question per dashboard. Use colour to signal action. Make it mobile-friendly. Test with real users.
3. Rollout: Pilot with 10 stores, wave out to 50, then scale. Provide training and support. Measure adoption.

If you get these right, you’ll have a lean, fast, scalable operational intelligence platform that your store managers actually use.

If you’re building a retail platform and need help designing and deploying Superset dashboards, PADISO offers platform development services across Australia and the United States. We’ve embedded Superset + ClickHouse into retail management systems for companies with 50–500 stores, delivering sub-second query latency and 95%+ user adoption. Reach out for a conversation about your specific use case.

For teams in specific cities, we offer specialised expertise: platform development in Sydney for financial services and retail, platform development in Melbourne for insurance and health, and platform development in Brisbane for logistics and resources. We also operate across the United States (New York, Chicago, Austin, Dallas, Seattle) and Canada (Toronto).

Your operational dashboards should drive action, not create busywork. Start with a clear question, build a solid data foundation, and iterate with real users. The rest follows.