Khai báo cấp độ chi tiết của bảng phân tích

Cấp độ chi tiết của bảng phân tích, thường gọi là grain, xác định một dòng đại diện điều gì. Hãy mô tả nó như một quan sát nghiệp vụ trước khi chọn khóa hay chỉ số: một dòng hóa đơn đã chốt, một lần thử thanh toán hoặc tồn cuối ngày của một kho. Chỉ có mã dòng duy nhất chưa trả lời được câu hỏi ấy.

Với người phân tích hoặc kỹ sư đã đọc được phép nối và tổng hợp SQL cơ bản, grain là hợp đồng làm phép cộng diễn giải được. Tiền ở cấp dòng và giảm giá ở cấp hóa đơn có thể đều đúng riêng lẻ, nhưng đặt cùng một dòng có thể khiến tổng sau này sai. Vấn đề bắt đầu từ nghĩa của dòng, trước khi bộ tối ưu truy vấn hay công cụ vẽ biểu đồ nhìn thấy nó.

Bắt đầu từ quan sát rồi chọn danh tính

Xét bản trích xuất hóa đơn đã chốt giả lập. Một hóa đơn có nhiều dòng và cùng SKU có thể xuất hiện trên nhiều dòng. Nguồn bảo đảm (receipt_id, line_number) xác định một dòng trong bản trích xuất cố định này. Nguồn không bảo đảm SKU xác định dòng, cũng không bảo đảm mỗi hóa đơn chỉ có một mặt hàng.

Hướng dẫn mô hình chiều của Microsoft mô tả độ chi tiết của bảng fact là mức chi tiết các dòng biểu diễn. Điều đó cụ thể hơn một danh sách khóa ngoại. Khi quan sát đã rõ, khóa phải giữ danh tính của nó và chỉ số phải mô tả cùng quan sát đó.

SQL tương thích SQLite dưới đây giữ đầu hóa đơn riêng với các dòng hàng được giao. Số tiền dùng số nguyên theo đơn vị tiền nhỏ nhất; ví dụ giả định các đồng tiền có hai chữ số thập phân. Giá và giảm giá đến từ nguồn đã chốt. Ví dụ không quy đổi tiền tệ hay tính thuế.

CREATE TEMP TABLE receipts (
  receipt_id INTEGER PRIMARY KEY,
  currency TEXT NOT NULL,
  discount_minor INTEGER NOT NULL
);
CREATE TEMP TABLE line_import (
  delivery_id INTEGER PRIMARY KEY,
  receipt_id INTEGER NOT NULL,
  line_number INTEGER NOT NULL,
  sku TEXT NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price_minor INTEGER NOT NULL
);
INSERT INTO receipts VALUES (10, 'EUR', 300), (11, 'EUR', 300), (12, 'USD', 0);
INSERT INTO line_import VALUES
  (1, 10, 1, 'A', 1, 1000),
  (2, 10, 2, 'A', 1, 1000),
  (3, 11, 1, 'B', 1, 1000),
  (4, 12, 1, 'C', 1, 2500);

SELECT l.receipt_id,
       SUM(l.quantity * l.unit_price_minor) AS gross_minor,
       SUM(r.discount_minor) AS copied_discount_minor
FROM line_import l
JOIN receipts r ON r.receipt_id = l.receipt_id
GROUP BY l.receipt_id
ORDER BY l.receipt_id;

Hóa đơn 10 có tiền hàng 2.000 và giảm giá bị sao chép thành 600. Giảm giá thật của hóa đơn là 300. Phép nối đã lặp chỉ số ở cấp đầu hóa đơn trên hai dòng hàng. Hóa đơn 11 chỉ có một dòng nên giảm giá sao chép tình cờ vẫn là 300. Bộ thử chỉ có hóa đơn một dòng sẽ che lỗi mô hình.

Mở ảnh kích thước đầy đủ ↗

Đặt chỉ số ở mức được định nghĩa

Giữ tiền hàng từng dòng ở cấp dòng và giảm giá hóa đơn ở cấp hóa đơn. Muốn tính tiền ròng hóa đơn, tổng hợp các dòng thành một dòng mỗi hóa đơn trước, rồi ghép đầu hóa đơn một lần. View tiếp theo còn giữ số dòng rõ ràng để phân biệt thiếu dòng với số tiền không đã biết.

CREATE TEMP VIEW receipt_totals AS
WITH line_totals AS (
  SELECT receipt_id, COUNT(*) AS line_count,
         SUM(quantity * unit_price_minor) AS gross_minor
  FROM line_import
  GROUP BY receipt_id
)
SELECT r.receipt_id, r.currency,
       COALESCE(l.line_count, 0) AS line_count,
       l.gross_minor, r.discount_minor,
       l.gross_minor - r.discount_minor AS net_minor
FROM receipts r
LEFT JOIN line_totals l ON l.receipt_id = r.receipt_id;

SELECT * FROM receipt_totals ORDER BY receipt_id;
SELECT currency, SUM(net_minor) AS net_minor
FROM receipt_totals
GROUP BY currency
ORDER BY currency;

Trong dữ liệu giả lập, tiền ròng của các hóa đơn là 1.700 đơn vị nhỏ EUR, 700 đơn vị nhỏ EUR và 2.500 đơn vị nhỏ USD. Tổng theo đồng tiền là 2.400 EUR và 2.500 USD theo đơn vị nhỏ. Cộng hai số vào tổng không nhãn sẽ trộn đơn vị tiền tệ. Mã tiền của hóa đơn có thể lặp an toàn như thuộc tính mô tả trên các dòng; điều đó không làm giảm giá hóa đơn cộng được theo dòng.

Thiết kế này chưa trả lời doanh thu ròng theo SKU. Giảm giá thuộc hóa đơn và nguồn chưa gán cho sản phẩm riêng. Báo cáo sản phẩm cần phân bổ cấp dòng do nguồn cung cấp hoặc chính sách phân bổ rõ ràng đối soát được về đầu hóa đơn. Sao toàn bộ giảm giá tới mỗi SKU sẽ tự tạo quy tắc quy thuộc đồng thời phá bảo toàn tổng. Giữ quyết định chưa giải quyết nhìn thấy được thay vì sớm đặt tên cột cấp dòng là net_revenue.

Kiểm tra danh tính nghiệp vụ độc lập với khóa lưu trữ

Bảng nhập dùng mã lần giao duy nhất để truy vết. Nó vẫn có thể chứa hai lần giao của cùng dòng hóa đơn. Vì vậy kiểm tra grain phải nhóm theo danh tính nghiệp vụ đã khai báo:

SELECT receipt_id, line_number, COUNT(*) AS delivered_copies
FROM line_import
GROUP BY receipt_id, line_number
HAVING COUNT(*) > 1;

Bộ gốc không trả dòng nào. Thêm (5, 10, 1, 'A', 1, 1000) trên bản mới: khóa chính lưu trữ vẫn nhận, nhưng kiểm tra grain phát hiện hóa đơn 10, dòng một xuất hiện hai lần. Luồng phải giải quyết danh tính lần giao và ngữ nghĩa phiên bản nguồn trước khi công bố bảng fact hứa một dòng cho mỗi dòng hóa đơn đã chốt. Truy vấn phát hiện vi phạm, chưa xác lập nội dung thứ hai là bản lặp hay bản sửa.

Ngược lại, nhóm theo (receipt_id, sku) sẽ gắn cờ hai dòng A ngay trong dữ liệu gốc hợp lệ. Chúng là dòng nguồn riêng dù cùng sản phẩm. Quy tắc loại trùng quá rộng phá chi tiết hợp lệ. Ghi định nghĩa dòng cạnh khóa được kiểm tra để người bảo trì sau không thay hợp đồng nghiệp vụ bằng những cột tình cờ có vẻ duy nhất trong mẫu nhỏ.

Tài liệu kiểm thử dbt mô tả khẳng định như duy nhất và không null. Chúng thực thi được phần của grain đã khai báo, nhưng kiểm tra tính duy nhất trên delivery_id thành công không suy ra quan sát mong muốn. Trước hết người phân tích phải quyết định danh tính nào quan trọng. Kiểm tra quan hệ riêng cần từ chối dòng không có đầu hóa đơn tương ứng; nếu không, báo cáo lấy đầu hóa đơn làm tập gốc có thể bỏ dòng dù khóa grain vẫn duy nhất.

Phân biệt thiếu quan sát với quan sát có giá trị không

Trên bộ gốc mới, thêm hóa đơn 13 theo EUR, giảm giá không nhưng chưa có dòng. Thêm hóa đơn 14 theo EUR, giảm giá không và một dòng có số lượng một, đơn giá không. View báo line_count không và gross/net null cho hóa đơn 13; nó báo line_count một và số tiền không cho hóa đơn 14.

Mở ảnh kích thước đầy đủ ↗

Dòng giá không có thể là hàng miễn phí hợp lệ theo hợp đồng ví dụ. Đầu hóa đơn không có dòng vi phạm kỳ vọng đầy đủ của bản trích xuất này. Đừng đổi cả hai tiền hàng thành không chỉ để biểu đồ hiển thị. Tài liệu hàm tổng hợp PostgreSQL giải thích tổng bỏ qua đầu vào null và trả null khi không có dòng đầu vào. Vì thế tổng theo nhóm có thể che thành phần chưa đầy đủ nếu bước công bố chỉ kiểm tra số tổng.

Với báo cáo này, yêu cầu mỗi hóa đơn đã chốt thuộc phạm vi phải có ít nhất một dòng hợp lệ trước khi công bố tổng theo tiền tệ. Nếu nguồn cho phép hóa đơn thực sự rỗng hoặc đã hủy, hãy mô hình hóa trạng thái và quyết định nó có thuộc tập báo cáo không. Số dòng là bằng chứng cho quyết định, không phải quy tắc toàn cầu rằng mọi hóa đơn thương mại đều phải có sản phẩm được tính tiền.

Bản trích xuất sau có thể sửa một dòng đã chốt trước đó. Nối bản sửa vào bảng công bố sẽ vi phạm cam kết hiện tại về một dòng cho mỗi dòng hóa đơn. Hoặc thay toàn bộ bản chụp đã chọn, hoặc giữ phiên bản trong lịch sử riêng rồi chọn phiên bản mong muốn trước khi tổng hợp. Ghi ranh giới trích xuất giúp phân biệt hai thiết kế ấy; thêm mã lần giao chưa giải quyết được lựa chọn.

Hợp đồng grain hữu ích ghi quan sát, danh tính, phạm vi bản chụp hoặc phiên bản nguồn, đơn vị và chiều nào cho phép cộng từng chỉ số. Nó còn nói rõ thiếu chi tiết nghĩa là gì. Những lựa chọn ấy giúp giải thích vì sao dòng tồn tại, vì sao dòng khác là trùng và vì sao đôi khi hai số đúng riêng lẻ cần ở hai bảng khác nhau.