Shared BI dataset and KPI checks

Use these examples with the BI guides. This dataset is intentionally small enough to audit by hand and contains a repeated buyer, a cancelled order and two refunds on one order.

Create the dataset

Run the following as an administrator in a fresh, disposable database. bi_tutorial must not already exist; creation fails rather than overwriting existing data. Run the initialization once. Keep the BI connection separate from this administrator session.

CREATE DATABASE bi_tutorial;
USE bi_tutorial;

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  region VARCHAR(20)
);
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  ordered_at DATETIME,
  category VARCHAR(20),
  status VARCHAR(20),
  quantity INT,
  gross_amount DECIMAL(24,6),
  cost_amount DECIMAL(24,6)
);
CREATE TABLE refunds (
  refund_id INT PRIMARY KEY,
  order_id INT,
  refund_amount DECIMAL(24,6)
);

INSERT INTO customers VALUES (1,'East'),(2,'West'),(3,'East');
INSERT INTO orders VALUES
  (1,1,'2026-01-01 10:00:00','A','paid',2,100.000000,60.000000),
  (2,1,'2026-01-02 11:00:00','B','paid',4,200.000000,120.000000),
  (3,2,'2026-01-02 12:00:00','A','paid',1,50.000000,30.000000),
  (4,3,'2026-01-03 09:00:00','B','paid',2,80.000000,40.000000),
  (5,2,'2026-01-03 13:00:00','A','cancelled',1,999.000000,0.000000);
INSERT INTO refunds VALUES (1,1,10.000000),(2,1,5.000000),(3,3,5.000000);

CREATE TABLE order_metrics AS
SELECT o.order_id, o.customer_id, o.ordered_at, o.category, c.region,
       o.quantity, o.gross_amount, o.cost_amount,
       CAST(COALESCE(r.refund_amount,0) AS DECIMAL(24,6)) AS refund_amount,
       CAST(o.gross_amount-COALESCE(r.refund_amount,0)
            AS DECIMAL(24,6)) AS net_amount
FROM orders o
JOIN customers c ON c.customer_id=o.customer_id
LEFT JOIN (
  SELECT order_id, SUM(refund_amount) AS refund_amount
  FROM refunds GROUP BY order_id
) r ON r.order_id=o.order_id
WHERE o.status='paid';

order_metrics is a physical snapshot with one row per paid order, not a live view. Refresh/rebuild it when source data changes. Refunds are summed to one row per order before the join, which avoids multiplying order amounts. The tutorial subtracts all recorded refunds from their original order, regardless of refund date; it is not a refund-date cash-flow metric.

Create a read-only connection user

Replace the password before executing these statements in the same MatrixOne account as the dataset. The example role/user must be new.

CREATE ROLE bi_tutorial_reader;
GRANT SELECT ON TABLE bi_tutorial.* TO bi_tutorial_reader;
CREATE USER bi_reader IDENTIFIED BY 'REPLACE_WITH_A_UNIQUE_PASSWORD'
  DEFAULT ROLE bi_tutorial_reader;

Reconnect as bi_reader, select database bi_tutorial, and run the queries below. MatrixOne privileges are granted to roles and inherited by users; do not substitute MySQL’s user@host grant syntax. See CREATE USER and GRANT. Add explicit VIEW privileges if you later replace the physical table with views; discovery privileges may also be required by a particular tool.

Overall metrics

SELECT COUNT(*) AS paid_orders,
       COUNT(DISTINCT customer_id) AS buyers,
       SUM(gross_amount) AS gross_amount,
       SUM(refund_amount) AS refunds,
       SUM(net_amount) AS net_amount,
       SUM(net_amount-cost_amount) AS margin,
       SUM(quantity) AS units
FROM bi_tutorial.order_metrics;

paid_orders

buyers

gross_amount

refunds

net_amount

margin

units

4

3

430.000000

20.000000

410.000000

160.000000

9

Grouped metrics

SELECT category, COUNT(*) AS paid_orders,
       COUNT(DISTINCT customer_id) AS buyers,
       SUM(net_amount) AS net_amount, SUM(quantity) AS units
FROM bi_tutorial.order_metrics
GROUP BY category ORDER BY category;

category

paid_orders

buyers

net_amount

units

A

2

2

130.000000

3

B

2

2

280.000000

6

Summing category buyers gives 4, while overall distinct buyers is 3. Customer 1 purchased in both categories: distinct counts are not additive across overlapping groups.

Daily trend and running total

SELECT DATE(ordered_at) AS day, SUM(net_amount) AS net_amount
FROM bi_tutorial.order_metrics
GROUP BY DATE(ordered_at) ORDER BY day;
WITH daily AS (
  SELECT DATE(ordered_at) AS day, SUM(net_amount) AS net_amount
  FROM bi_tutorial.order_metrics GROUP BY DATE(ordered_at)
)
SELECT day, net_amount,
       SUM(net_amount) OVER (
         ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative_net
FROM daily ORDER BY day;

day

net_amount

cumulative_net

2026-01-01

85.000000

85.000000

2026-01-02

245.000000

330.000000

2026-01-03

80.000000

410.000000

Weighted ratios

SELECT SUM(gross_amount)/NULLIF(SUM(quantity),0) AS unit_price,
       SUM(net_amount-cost_amount)/NULLIF(SUM(net_amount),0) AS margin_rate
FROM bi_tutorial.order_metrics;

Unit price is 430/9 (approximately 47.777778); margin rate is 160/410 (approximately 0.390244, or 39.0244%). Calculate ratios from summed numerators and denominators. Averaging individual order ratios answers a different question. NULLIF makes an empty/zero denominator produce null rather than an error.

Encoding and exact numeric transport

SELECT '健康中文😀' AS label,
       CAST(1.123456 AS DECIMAL(24,6)) AS small_decimal,
       CAST(9007199254740993.123456 AS DECIMAL(24,6)) AS large_decimal;

Verify the Unicode label and both decimals after the tool returns/serializes the values. 9007199254740993.123456 exceeds JavaScript’s exact-integer range and has fractional digits. Converting it to a binary floating-point number loses information. Displaying a SQL-cast string may preserve text but does not validate numeric chart aggregation; changing to integer minor units also needs a range check.

Inspect full row counts before comparing previews with complete result sets. Notebook table formats can also misinterpret embedded tabs/newlines without any database value being wrong.