BI mã nguồn mở 3 — Metabase nâng cao

Khi query builder chạm trần

Bài Metabase căn bản đã cho thấy query builder giải quyết ~80% câu hỏi thường ngày mà không cần một dòng SQL. Nhưng 20% còn lại — những câu hỏi khiến người ta thực sự thuê analyst — luôn vượt khỏi bốn cú chuột: "tỷ trọng từng loại giao dịch trong tổng của mỗi tháng", "top 10 khách theo tổng số dư kèm xếp hạng", "số dư luỹ kế cộng dồn theo thời gian". Đây là địa hạt của window function, CTE nhiều tầng, subquery tương quan — những thứ query builder no-code cố tình không phơi ra để giữ giao diện đơn giản.

Bài này bàn về nửa còn lại của Metabase: native SQL question để viết truy vấn tuỳ ý, SQL variables để biến câu SQL cứng thành báo cáo có tham số, models làm lớp ngữ nghĩa sạch, dashboard nâng cao, cảnh báo, caching, phân quyền chi tiết tới cấp dòng, và embedding để nhúng dashboard vào ứng dụng nội bộ. Đây là ranh giới nơi Metabase thôi là "đồ chơi self-service" và trở thành một nền BI vận hành được cho ngân hàng.

Native SQL question

Khi tạo một Question mới, thay vì chọn bảng bạn chọn Native query và gõ thẳng SQL. Metabase gửi nguyên câu đó tới database (qua tài khoản read-only đã cấu hình) và trả bảng kết quả vào cùng bộ máy visualize/save/dashboard như question thường. Ba tình huống bắt buộc phải xuống native:

• JOIN nhiều bảng không đi qua Foreign Key đơn giản, hoặc cần điều kiện join phức tạp.
• Window function (OVER (PARTITION BY ...)): xếp hạng, luỹ kế, tỷ trọng theo nhóm, so với kỳ trước.
• Logic nhiều tầng: CTE lồng nhau, HAVING trên biểu thức tính toán, UNION.

Giao dịch theo tháng với date_trunc

Câu hỏi kinh điển cho báo cáo huy động/giao dịch: "Mỗi tháng có bao nhiêu giao dịch và tổng giá trị bao nhiêu?". Query builder làm được phần group-by-tháng, nhưng khi cần kiểm soát chính xác định dạng và sắp xếp thời gian, native rõ ràng hơn:

-- ▶ Chạy được
SELECT date_trunc('month', created_at) AS thang,
       COUNT(*)                        AS so_giao_dich,
       SUM(amount)                     AS tong_gia_tri,
       ROUND(AVG(amount)::numeric, 2)  AS gia_tri_tb
FROM transactions
GROUP BY date_trunc('month', created_at)
ORDER BY thang;

Lưu ý AVG(amount) phải ép ::numeric trước khi ROUND(..., 2) vì PostgreSQL không có round(double precision, int). Kết quả này lên biểu đồ line cho ra ngay xu hướng lưu lượng giao dịch theo tháng.

Top khách hàng theo tổng số dư (JOIN ba bảng)

Đây là ví dụ query builder gần như không dựng nổi ở dạng mặc định: gộp số dư của mọi tài khoản về từng khách hàng, rồi lấy tên khách. Vì accounts không có cột tên, phải JOIN sang customers:

-- ▶ Chạy được
SELECT c.id,
       c.full_name,
       c.city,
       COUNT(a.id)    AS so_tai_khoan,
       SUM(a.balance) AS tong_so_du
FROM customers c
JOIN accounts a ON a.customer_id = c.id
GROUP BY c.id, c.full_name, c.city
ORDER BY tong_so_du DESC
LIMIT 10;

Lưu thành model "Số dư khách hàng" (mục dưới) là bạn có sẵn nền tảng cho hàng loạt câu hỏi phân khúc VIP về sau.

Tỷ trọng theo kind bằng window function

Câu hỏi "mỗi loại giao dịch chiếm bao nhiêu phần trăm tổng giá trị?" cần chia mỗi nhóm cho tổng toàn cục — chính là window function SUM() OVER ():

-- ▶ Chạy được
WITH theo_kind AS (
    SELECT kind, SUM(amount) AS tong
    FROM transactions
    GROUP BY kind
)
SELECT kind,
       tong,
       ROUND((100.0 * tong / SUM(tong) OVER ())::numeric, 2) AS ty_trong_pct
FROM theo_kind
ORDER BY tong DESC;

SUM(tong) OVER () tính tổng của toàn bảng kết quả (không PARTITION BY), cho phép tính tỷ trọng mà không cần subquery lặp lại. Đây đúng là loại truy vấn khiến người ta phải rời query builder.

SQL variables & filter widgets

Một native question viết cứng ngày tháng, chi nhánh là báo cáo dùng một lần. Sức mạnh thật sự đến khi tham số hoá bằng SQL variables — cú pháp {{ten_bien}} trong câu SQL. Metabase quét câu SQL, thấy {{...}}, tự dựng filter widget trên đầu question cho người dùng nhập giá trị.

Có bốn kiểu biến, khác nhau ở mức độ "thông minh":

Field Filter là kiểu đáng dùng nhất. Bạn không viết = {{x}} mà chỉ đặt {{bo_loc}} rồi ánh xạ nó tới, ví dụ, cột transactions.kind. Metabase biết cột đó là Category nên tự dựng dropdown liệt kê các giá trị kind có thật, và tự sinh cả mệnh đề WHERE (kể cả IN (...) khi chọn nhiều). Với cột thời gian, nó dựng bộ chọn khoảng ngày phong phú ("tháng này", "30 ngày qua").

Optional clause [[...]] giải bài toán "filter không bắt buộc": nếu người dùng để trống chi nhánh, cả mệnh đề biến mất và câu trả về toàn bộ. Mẫu điển hình cho dashboard huy động có tham số chi nhánh + thời gian:

SELECT date_trunc('month', created_at) AS thang, SUM(amount) AS tong
FROM transactions
WHERE created_at BETWEEN {{tu_ngay}} AND {{den_ngay}}
  [[AND kind = {{loai_gd}}]]
GROUP BY 1 ORDER BY 1;

(Block trên không đánh dấu chạy được vì chứa cú pháp biến {{...}} của Metabase, không phải SQL Postgres thuần.) Khi để trống loai_gd, báo cáo hiển thị mọi loại; chọn "chuyển khoản" thì lọc lại — tất cả trên cùng một question.

Models: lớp ngữ nghĩa curated

Bài căn bản đã giới thiệu model như cách giấu JOIN. Ở mức nâng cao, model là lớp dữ liệu ngữ nghĩa (semantic layer) của Metabase — nơi bạn định nghĩa "bảng sạch, đã curated" mà toàn tổ chức dùng lại thay vì đụng bảng thô.

Một model được dựng từ query builder hoặc từ native SQL (VD dùng chính câu "top khách theo số dư" ở trên làm nguồn). Sau đó bạn tinh chỉnh metadata của model:

• Đổi tên & mô tả từng cột thân thiện ("Tổng số dư" thay vì tong_so_du), ẩn cột kỹ thuật.
• Gán lại semantic type: đánh dấu cột số dư là Currency, cột thành phố là City, cột id là Entity Key — để drill và định dạng hoạt động như trên bảng thật.
• Kết nối cột model tới bảng khác qua Foreign Key để tiếp tục drill.

Giá trị cốt lõi: một định nghĩa dữ liệu, dùng lại khắp nơi. Khi nghiệp vụ "Ask a question" trên model "Số dư khách hàng", họ có sẵn tên khách, thành phố, tổng số dư đã tính đúng — không ai join sai, không ai quên lọc tài khoản đóng. Đây là nền tự phục vụ sạch: người dùng tự do khám phá nhưng trong khuôn khổ dữ liệu đã được kiểm soát. Model là bước Metabase tiến gần ý tưởng semantic layer được đào sâu ở bài semantic & metrics và nguyên tắc chuẩn hoá metric & KPI.

Dashboard nâng cao

Ngoài filter dùng chung và drill cơ bản đã nêu ở bài trước, dashboard nâng cao có vài công cụ đáng khai thác:

• Parameter & linked filter: các dashboard filter có thể liên kết (linked) — chọn Chi nhánh thì dropdown Nhân viên chỉ còn nhân viên thuộc chi nhánh đó. Giá trị của một filter thu hẹp lựa chọn của filter phụ thuộc, tránh tổ hợp vô nghĩa.
• Click behavior / drill-through: với mỗi cột trên card bạn cấu hình hành vi khi nhấp — "đi tới dashboard khác" (truyền chi nhánh vừa nhấp làm tham số), "mở URL" (VD trang chi tiết khách trên core banking), hoặc "cập nhật filter dashboard". Đây là cách dựng luồng phân tích phân cấp: dashboard tổng quan chi nhánh → nhấp một chi nhánh → dashboard chi tiết chi nhánh đó.
• Conditional formatting: tô màu ô/hàng theo ngưỡng — số dư giảm so kỳ trước thành đỏ, đạt chỉ tiêu thành xanh; hoặc thang màu (heatmap) cho bảng số. Giúp mắt bắt bất thường tức thì.
• Tabs: chia một dashboard lớn thành nhiều tab (Tổng quan / Huy động / Giao dịch) thay vì cuộn dài.

Alerts & subscriptions

Bài căn bản đã nói subscription — gửi định kỳ dashboard/question qua email hoặc Slack theo lịch. Nâng cao hơn là Alert đặt trên một question: gửi khi đạt điều kiện, không theo lịch cứng.

• Goal line alert: khi một chỉ số vượt/xuống dưới ngưỡng bạn đặt (VD tổng giao dịch bất thường trong ngày vượt mốc cảnh báo rủi ro).
• Results alert: khi câu hỏi có kết quả trả về (VD question "tài khoản có giao dịch > 500 triệu chưa duyệt" mà ra dòng nào là gửi ngay).
• Tần suất kiểm tra: mỗi giờ / ngày / tuần; gửi một lần rồi thôi hoặc lặp mỗi lần còn thoả điều kiện.

Kết hợp: subscription cho báo cáo đều đặn (7h sáng gửi số dư hôm qua), alert cho ngoại lệ cần phản ứng (phát hiện giao dịch đáng ngờ). Alert tôn trọng phân quyền dữ liệu của người tạo.

Caching kết quả

Native query nặng (JOIN nhiều bảng, quét lịch sử giao dịch dài) có thể chạy vài giây tới vài chục giây — chấp nhận được cho một người, nhưng một dashboard 8 card mở đồng loạt bởi 50 người sáng đầu tuần sẽ đè database. Caching giải quyết: Metabase lưu kết quả question trong một khoảng thời gian (TTL) và phục vụ lại từ cache thay vì chạy lại SQL.

• Cấu hình theo cấp: mặc định toàn hệ thống, ghi đè theo database, hoặc theo từng question.
• Chiến lược TTL cố định (VD 60 phút) hoặc theo tần suất thay đổi dữ liệu (adaptive): dữ liệu ngày hôm qua đã đóng sổ thì cache dài, dữ liệu real-time thì cache ngắn hoặc không cache.
• Đánh đổi kinh điển: cache dài giảm tải database nhưng số liệu có thể "cũ" vài chục phút — với báo cáo huy động cuối ngày thì hoàn toàn ổn, với màn hình giám sát giao dịch real-time thì không.

Phân quyền theo group & data sandbox

Phân quyền cơ bản (Data + Collection theo Group) đã trình bày ở bài trước. Bản Pro/Enterprise bổ sung Data Sandbox — cơ chế row-level security (RLS) cực quan trọng cho ngân hàng đa chi nhánh (multi-tenant).

Ý tưởng: thay vì mỗi Group thấy toàn bộ bảng, bạn định nghĩa một "phiên bản bị lọc" (sandbox) của bảng cho từng Group, dựa trên thuộc tính người dùng (user attribute). Ví dụ mỗi tài khoản Metabase gán attribute ma_chi_nhanh; sandbox trên bảng accounts tự chèn WHERE branch_id = {{ma_chi_nhanh}} theo attribute của người đang đăng nhập. Kết quả: cùng một dashboard, cán bộ chi nhánh Hà Nội chỉ thấy số của Hà Nội, chi nhánh Đà Nẵng chỉ thấy Đà Nẵng — một dashboard, nhiều "khung nhìn".

Có hai kiểu sandbox: basic (lọc theo một cột khớp user attribute) và advanced (thay bảng bằng một model/SQL tuỳ biến nhận tham số từ attribute). Đây là nền tảng để mở BI ra hàng trăm cán bộ mà vẫn bảo đảm mỗi người chỉ chạm dữ liệu được phép — nguyên tắc kiểm soát truy cập bàn kỹ ở governance & security và access control.

Embedding: nhúng vào app nội bộ

Không phải người dùng nào cũng vào giao diện Metabase. Embedding cho phép nhúng dashboard/question vào portal, app nghiệp vụ, hay trang intranet. Ba mức, khác nhau về bảo mật và tương tác:

Với ngân hàng, public link gần như không dùng cho dữ liệu thật. Static embedding phù hợp khi cần nhúng một dashboard chi nhánh vào portal: server nội bộ ký JWT chứa { "chi_nhanh": "HN01" }, tham số bị khoá nên người xem không thể đổi sang chi nhánh khác. Interactive embedding mạnh nhất: nhúng nguyên trải nghiệm Metabase vào app nội bộ qua SSO, tôn trọng cả data sandbox — người dùng đăng nhập app một lần, tự khám phá nhưng chỉ trong phạm vi dữ liệu chi nhánh mình.

Use case thực tế

Bối cảnh. Ban Giám sát Giao dịch NCB cần một dashboard theo dõi giao dịch toàn hàng, có tham số chi nhánh và khoảng thời gian, kèm cảnh báo tự động cho giao dịch bất thường. Yêu cầu: mỗi chi nhánh chỉ được thấy số của mình; hội sở thấy toàn bộ; nhúng dashboard vào portal điều hành nội bộ.

Triển khai.

1. Native question dựng ba báo cáo lõi bằng SQL: giao dịch theo tháng (date_trunc), top khách theo tổng số dư (JOIN customers), tỷ trọng theo kind (window SUM() OVER ()) — đúng ba block SQL ở trên.
2. SQL variables: thêm Field Filter khoảng thời gian và optional clause [[AND kind = {{loai}}]] để lọc linh hoạt không bắt buộc.
3. Model "Số dư khách hàng": đóng gói JOIN + gán semantic type (Currency, City), làm nguồn sạch cho câu hỏi phân khúc.
4. Dashboard với linked filter (chọn chi nhánh → thu hẹp danh sách phòng), conditional formatting tô đỏ số dư giảm, click-through sang dashboard chi tiết chi nhánh.
5. Data sandbox: gán user attribute ma_chi_nhanh, sandbox bảng giao dịch lọc theo attribute — 200 cán bộ chi nhánh dùng chung một dashboard, mỗi người chỉ thấy phần mình.
6. Alert results-based: question "giao dịch > 500 triệu chưa duyệt trong ngày" ra dòng nào là gửi Slack ngay cho tổ giám sát; subscription email 7h sáng gửi tổng hợp hôm qua.
7. Caching TTL 30 phút cho card nặng; interactive embedding qua SSO nhúng dashboard vào portal điều hành.

Kết quả (minh hoạ). Một dashboard phục vụ toàn mạng lưới thay cho hàng chục bản Excel tách theo chi nhánh; cảnh báo bất thường chuyển từ "phát hiện sau vài ngày khi rà sổ" sang "trong vòng một giờ"; tải database giảm nhờ caching dù số người xem tăng nhiều lần. (Các con số minh hoạ cho mô hình vận hành.)

Ghi nhớ

• Native SQL question là lối thoát khi query builder chạm trần: JOIN nhiều bảng, window function (OVER), CTE nhiều tầng — Metabase chạy nguyên câu qua tài khoản read-only.
• SQL variables {{...}} biến câu SQL cứng thành báo cáo có tham số; Field Filter ánh xạ tới cột và tự sinh mệnh đề + dropdown; optional clause [[...]] cho filter không bắt buộc.
• Models là lớp ngữ nghĩa curated: đóng gói JOIN, đặt lại tên/type, dùng lại một định nghĩa khắp nơi → tự phục vụ sạch, nền của semantic layer.
• Dashboard nâng cao: linked filter, click behavior/drill-through (truyền tham số sang dashboard khác), conditional formatting, tabs.
• Alerts gửi theo điều kiện (goal/results) khác subscriptions gửi theo lịch; caching (TTL) giảm tải database, đánh đổi độ tươi số liệu.
• Data Sandbox (Pro/Enterprise) làm row-level security theo user attribute — một dashboard, mỗi chi nhánh một khung nhìn; nền cho BI đa chi nhánh (multi-tenant).
• Embedding ba mức: public link (không nhạy cảm), static/signed (JWT khoá tham số), interactive (SSO + sandbox) — ngân hàng ưu tiên hai mức sau.
• Ba SQL "▶ Chạy được" trong bài là native query thực: date_trunc theo tháng, JOIN ba bảng lấy top khách, và window SUM() OVER () tính tỷ trọng.