Designing Financial Data Model Power BI for Portfolio Analysis

Designing financial data model Power BI là nền tảng quyết định toàn bộ hiệu suất của báo cáo portfolio.

Một data model được thiết kế sai sẽ khiến DAX chạy chậm, KPI tính sai và dashboard không thể scale. Ngược lại, một Star Schema đúng chuẩn giúp mọi measure hoạt động chính xác và refresh nhanh — dù danh mục có hàng triệu giao dịch.

Bài viết hướng dẫn toàn bộ quy trình thiết kế data model cho portfolio analysis — từ xác định Fact Table, xây Dimension Tables, thiết lập Relationships, đến tối ưu DAX performance. Tham khảo thêm tài liệu chính thức tại Microsoft Power BI Star Schema Guide.

📌 Đây là bài số 4 trong series Power BI Financial Analytics tại MCNA:

Bài 1: Power BI Wealth Management Dashboard — Từ Zero đến Production
Bài 2: Connecting Financial Data Sources in Power BI
Bài 3: Cleaning and Preparing Investment Data with Power Query
Bài 4: Designing Financial Data Model Power BI ← Bạn đang đọc
Bài 5: Advanced DAX cho Financial Reporting — sắp ra mắt

Mục Lục

1. Tại Sao Data Model Quan Trọng Hơn DAX?

Sai lầm phổ biến nhất của DA mới

Nhiều DA dành hàng giờ tối ưu DAX measure nhưng bỏ qua data model bên dưới.

Đây là sai lầm ngược. Một DAX measure viết tệ nhưng chạy trên model tốt vẫn cho kết quả đúng. Ngược lại, DAX viết hay nhưng model sai thì không thể cứu được.

Designing a financial data model in Power BI đúng ngay từ đầu tiết kiệm hàng chục giờ debug sau này.

Ba hậu quả của data model sai

Lỗi model	Triệu chứng	Hậu quả thực tế
Dùng flat table thay Star Schema	DAX chạy chậm, nhiều vòng lặp	Dashboard refresh mất 5–10 phút
Relationship nhiều-nhiều (M:M)	KPI bị double-count	AUM và return tính sai
Thiếu Date Table chuẩn	Time Intelligence không hoạt động	YTD, MTD, QoQ tính sai hoàn toàn
Quá nhiều cột không dùng	File .pbix nặng bất thường	Chậm khi publish lên Power BI Service
Cross-filter sai chiều	Slicer filter không hoạt động đúng	Báo cáo lọc sai client hoặc asset

💡 Nguyên tắc: Fix model trước, tối ưu DAX sau. Không có thứ tự nào khác hoạt động hiệu quả hơn.

2. Star Schema — Kiến Trúc Chuẩn cho Financial Data

Star Schema là gì?

Star Schema là kiến trúc data model gồm một Fact Table ở trung tâm và nhiều Dimension Tables xung quanh.

Các Dim Table kết nối với Fact Table qua relationship một-nhiều (1:N). Không có relationship trực tiếp giữa các Dim Table với nhau.

Kết quả là sơ đồ trông giống ngôi sao — đây là lý do tên gọi Star Schema.

Cấu trúc Star Schema cho Portfolio Analysis

-- Sơ đồ Star Schema chuẩn cho Wealth Management:

                    ┌─────────────┐
                    │  DimClient  │
                    │ ClientID PK │
                    └──────┬──────┘
                           │ 1
                           │
┌─────────────┐            │           ┌─────────────┐
│  DimAsset   │    N       ▼      N    │   DimDate   │
│ AssetCode PK├──────► FactTrade ◄─────┤  DateKey PK │
└─────────────┘            ▲           └─────────────┘
                           │ N
                           │
                    ┌──────┴──────┐
                    │  DimMarket  │
                    │ MarketID PK │
                    └─────────────┘

Tại sao không dùng flat table?

Flat table lưu mọi thứ trong một bảng duy nhất — tên client lặp lại mỗi row, tên asset lặp lại mỗi giao dịch.

Power BI phải scan toàn bộ bảng mỗi khi filter. Với 10 triệu giao dịch, đây là thảm họa về performance.

Star Schema tách riêng dữ liệu mô tả vào Dim Tables. Fact Table chỉ lưu số liệu và foreign keys. Filter diễn ra trên Dim Table nhỏ — cực kỳ nhanh.

3. Thiết Kế Fact Table cho Portfolio

Fact Table chứa gì?

Fact Table chứa các sự kiện đo lường được — giao dịch, giá, NAV.

Mỗi row là một sự kiện. Fact Table không chứa thông tin mô tả — đó là việc của Dim Tables.

FactTrade — bảng giao dịch chuẩn

-- Cấu trúc FactTrade chuẩn cho portfolio analysis
-- Foreign Keys (kết nối với Dim Tables)
TradeID        INT          -- Surrogate key (tự sinh)
DateKey        INT          -- FK → DimDate
ClientID       VARCHAR(20)  -- FK → DimClient
AssetCode      VARCHAR(10)  -- FK → DimAsset
MarketID       INT          -- FK → DimMarket

-- Measures (các con số thực sự)
Quantity       DECIMAL(18,4)
CostPrice      DECIMAL(18,4)
MarketPrice    DECIMAL(18,4)
CostValue      DECIMAL(18,2)  -- = Quantity × CostPrice
MarketValue    DECIMAL(18,2)  -- = Quantity × MarketPrice
UnrealizedPnL  DECIMAL(18,2)  -- = MarketValue - CostValue

-- Metadata
TradeType      VARCHAR(10)    -- BUY / SELL / TRANSFER
Currency       VARCHAR(3)     -- VND / USD / EUR
LoadTimestamp  DATETIME       -- dùng để audit và dedup

Nguyên tắc thiết kế Fact Table

Chỉ lưu FK và measures — không lưu tên client, tên asset vào Fact Table
Dùng Surrogate Key — không dùng business key làm Primary Key
Grain rõ ràng — mỗi row là một giao dịch, không phải tổng hợp theo ngày
Không tính toán trong Fact Table — để DAX tính MarketValue, UnrealizedPnL

⚠️ Lưu ý: Đừng lưu MarketValue trực tiếp nếu price thay đổi mỗi ngày. Thay vào đó, lưu Quantity trong FactTrade và tính MarketValue = Quantity × ClosePrice từ FactDailyPrice qua DAX.

4. Thiết Kế Dimension Tables

DimClient — bảng thông tin khách hàng

-- DimClient: thông tin mô tả khách hàng
ClientID        VARCHAR(20)   -- Natural key (từ Core Banking)
ClientName      NVARCHAR(100)
Segment         VARCHAR(20)   -- HNWI / Mass Affluent / Retail
RMCode          VARCHAR(10)   -- Mã Relationship Manager
RMName          NVARCHAR(50)
Region          VARCHAR(20)   -- North / South / HCM
RiskProfile     VARCHAR(10)   -- Conservative / Moderate / Aggressive
OnboardDate     DATE
IsActive        BIT           -- 1 = đang hoạt động

DimAsset — bảng thông tin tài sản

-- DimAsset: thông tin mô tả tài sản đầu tư
AssetCode       VARCHAR(10)   -- Mã chứng khoán (luôn type text)
AssetName       NVARCHAR(100)
AssetClass      VARCHAR(20)   -- Equity / Bond / ETF / Cash
Sector          VARCHAR(30)   -- Banking / Real Estate / Consumer...
Exchange        VARCHAR(10)   -- HOSE / HNX / UPCOM
ISIN            VARCHAR(12)
Currency        VARCHAR(3)
Country         VARCHAR(3)    -- VN / US / SG
IsListed        BIT

DimMarket — bảng thị trường

-- DimMarket: thông tin thị trường giao dịch
MarketID        INT
MarketName      VARCHAR(30)   -- HOSE / HNX / Bloomberg / OTC
MarketType      VARCHAR(20)   -- Exchange / OTC / Derivatives
OpenTime        TIME
CloseTime       TIME
TimeZone        VARCHAR(30)

5. Thiết Lập Relationships Đúng Chuẩn

Hai loại relationship trong Power BI

Active relationship — được dùng mặc định trong mọi DAX measure.

Inactive relationship — chỉ được kích hoạt khi gọi hàm USERELATIONSHIP() trong DAX. Dùng khi cần nhiều relationship giữa hai bảng — ví dụ TradeDate và SettleDate đều kết nối với DimDate.

Thiết lập trong Power BI Desktop

-- Các relationship cần tạo cho Portfolio Model:

-- 1. FactTrade → DimDate (Active: dùng TradeDate)
FactTrade[DateKey] → DimDate[DateKey]
Cardinality  : Many to One (N:1)
Cross Filter : Single (từ Dim sang Fact)

-- 2. FactTrade → DimDate (Inactive: dùng SettleDate)
FactTrade[SettleDateKey] → DimDate[DateKey]
Cardinality  : Many to One (N:1)
Active       : False

-- 3. FactTrade → DimClient
FactTrade[ClientID] → DimClient[ClientID]
Cardinality  : Many to One (N:1)
Cross Filter : Single

-- 4. FactTrade → DimAsset
FactTrade[AssetCode] → DimAsset[AssetCode]
Cardinality  : Many to One (N:1)
Cross Filter : Single

-- 5. FactDailyPrice → DimDate (Active)
FactDailyPrice[DateKey] → DimDate[DateKey]
Cardinality  : Many to One (N:1)

-- 6. FactDailyPrice → DimAsset
FactDailyPrice[AssetCode] → DimAsset[AssetCode]
Cardinality  : Many to One (N:1)

Dùng Inactive Relationship trong DAX

-- Tính tổng giao dịch theo SettleDate thay vì TradeDate
SettledAmount =
CALCULATE(
    SUM(FactTrade[CostValue]),
    USERELATIONSHIP(FactTrade[SettleDateKey], DimDate[DateKey])
)

⚠️ Không dùng Cross Filter = Both trừ khi thực sự cần. Bidirectional filter làm model phức tạp và dễ gây vòng lặp tính toán. Luôn dùng Single và giải quyết bằng DAX nếu cần.

6. Date Table — Nền Tảng của Time Intelligence

Tại sao phải có Date Table riêng?

Power BI có auto date/time nhưng không đủ cho financial reporting.

YTD, MTD, QoQ, rolling 12M — tất cả đều cần Date Table đầy đủ với các cột tùy chỉnh theo fiscal year của tổ chức.

Tạo DimDate chuẩn bằng M Language

// DimDate đầy đủ cho Financial Reporting
let
    StartDate = #date(2020, 1, 1),
    EndDate   = Date.From(DateTime.LocalNow()),
    DateList  = List.Dates(StartDate,
        Duration.Days(EndDate - StartDate) + 1,
        #duration(1,0,0,0)),

    DateTable = Table.FromList(DateList,
        Splitter.SplitByNothing(), {"Date"}),

    // Cột cơ bản
    AddDateKey   = Table.AddColumn(DateTable, "DateKey",
        each Date.Year([Date])*10000 + Date.Month([Date])*100
           + Date.Day([Date]), Int64.Type),
    AddYear      = Table.AddColumn(AddDateKey, "Year",
        each Date.Year([Date]), Int64.Type),
    AddMonth     = Table.AddColumn(AddYear, "Month",
        each Date.Month([Date]), Int64.Type),
    AddMonthName = Table.AddColumn(AddMonth, "MonthName",
        each Date.ToText([Date], "MMM yyyy"), type text),
    AddQuarter   = Table.AddColumn(AddMonthName, "Quarter",
        each "Q" & Text.From(Date.QuarterOfYear([Date])), type text),
    AddWeekday   = Table.AddColumn(AddQuarter, "Weekday",
        each Date.DayOfWeekName([Date]), type text),
    AddIsWeekend = Table.AddColumn(AddWeekday, "IsWeekend",
        each Date.DayOfWeek([Date]) >= 5, type logical),

    // Fiscal Year (ví dụ: FY bắt đầu từ tháng 4)
    AddFY        = Table.AddColumn(AddIsWeekend, "FiscalYear",
        each if Date.Month([Date]) >= 4
             then "FY" & Text.From(Date.Year([Date]))
             else "FY" & Text.From(Date.Year([Date]) - 1),
        type text),
    AddFYQ       = Table.AddColumn(AddFY, "FiscalQuarter",
        each
            let m = Date.Month([Date])
            in if m >= 4 and m <= 6 then "FQ1"
               else if m >= 7 and m <= 9 then "FQ2"
               else if m >= 10 and m <= 12 then "FQ3"
               else "FQ4",
        type text),

    // IsMarketDay (loại trừ T7, CN — chưa tính lễ)
    AddIsMarket  = Table.AddColumn(AddFYQ, "IsMarketDay",
        each Date.DayOfWeek([Date]) < 5, type logical)
in
    AddIsMarket

✅ Bắt buộc: Sau khi tạo DimDate, vào Table Tools → Mark as Date Table và chọn cột Date. Power BI sẽ không nhận diện Time Intelligence nếu bỏ bước này.

7. DAX Measures Cơ Bản cho Portfolio Analysis

Tổ chức Measures đúng cách

Tạo một bảng trống tên _Measures và đặt tất cả measures vào đó.

Không để measures rải rác trong các Fact Tables. Khi model có 50+ measures, việc tổ chức này tiết kiệm rất nhiều thời gian tìm kiếm.

Core measures cho portfolio

-- Tổng AUM hiện tại
Total AUM =
SUMX(
    FactTrade,
    FactTrade[Quantity] *
    CALCULATE(
        LASTNONBLANK(FactDailyPrice[ClosePrice], 1),
        FILTER(FactDailyPrice,
            FactDailyPrice[AssetCode] = FactTrade[AssetCode])
    )
)

-- Portfolio Weight per Asset
Asset Weight % =
DIVIDE(
    [Market Value],
    CALCULATE([Total AUM], ALL(DimAsset))
)

-- Unrealized P&L
Unrealized PnL =
[Total AUM] - SUM(FactTrade[CostValue])

-- Unrealized P&L %
Unrealized PnL % =
DIVIDE([Unrealized PnL], SUM(FactTrade[CostValue]))

-- YTD Return
YTD Return =
CALCULATE(
    [Unrealized PnL %],
    DATESYTD(DimDate[Date])
)

-- Portfolio Return (Weighted)
Portfolio Return =
SUMX(
    VALUES(DimAsset[AssetCode]),
    [Asset Weight %] * [Asset Return %]
)

Time Intelligence measures

-- AUM so sánh tháng trước
AUM MoM =
CALCULATE([Total AUM],
    DATEADD(DimDate[Date], -1, MONTH))

-- AUM MoM Change %
AUM MoM % =
DIVIDE([Total AUM] - [AUM MoM], [AUM MoM])

-- Rolling 12 Month Average AUM
Rolling 12M AUM =
AVERAGEX(
    DATESINPERIOD(DimDate[Date],
        LASTDATE(DimDate[Date]), -12, MONTH),
    [Total AUM]
)

8. Tối Ưu Performance của Data Model

Tại sao model lại chậm?

Power BI dùng VertiPaq engine để nén và lưu data trong memory.

Model chậm thường không phải do DAX phức tạp. Phần lớn là do data model chứa quá nhiều cột không cần thiết hoặc cardinality quá cao.

5 kỹ thuật tối ưu quan trọng nhất

Xóa cột không dùng: Mỗi cột thừa tốn memory và làm chậm compression. Xóa ngay trong Power Query, không phải trong model view.
Dùng Integer cho DateKey: Thay vì join qua cột Date (datetime), dùng DateKey dạng INT (20260316). VertiPaq nén integer hiệu quả hơn nhiều.
Tránh calculated columns: Tính toán trong DAX calculated column chạy lúc refresh, tốn RAM. Thay bằng M Language trong Power Query.
Không dùng DISTINCTCOUNT thường xuyên: Đây là phép tính nặng nhất trong DAX. Cache kết quả nếu cần dùng nhiều lần.
Tắt Auto date/time: File → Options → Data Load → Time Intelligence → bỏ chọn Auto date/time. Power BI tạo hidden date table cho mọi date column — rất tốn memory.

Kiểm tra performance với DAX Studio

-- Dùng DAX Studio để đo thời gian thực thi
-- Download miễn phí tại: daxstudio.org
-- Kết nối vào Power BI Desktop đang mở

-- Chạy query và xem Storage Engine vs Formula Engine time:
EVALUATE
SUMMARIZECOLUMNS(
    DimClient[Segment],
    "Total AUM", [Total AUM],
    "YTD Return", [YTD Return]
)
-- Nếu SE time >> FE time: model tốt, DAX có thể cải thiện
-- Nếu FE time >> SE time: cần review lại DAX logic

💡 Mục tiêu performance: Dashboard portfolio với 5M rows nên refresh dưới 3 giây. Nếu chậm hơn, kiểm tra trước model rồi mới xem DAX.

9. Best Practices cho Financial Data Model

Naming convention chuẩn

Tables: Fact + tên (FactTrade), Dim + tên (DimClient), _Measures
Measures: viết hoa chữ cái đầu mỗi từ (Total AUM, YTD Return)
Columns: PascalCase (ClientID, AssetCode, TradeDate)
Hidden columns: đặt prefix underscore (_DateKey) hoặc ẩn trong model view

Checklist trước khi publish

Hạng mục	Kiểm tra	Tool
Relationship	Tất cả là 1:N, không có M:M không chủ đích	Model View
Date Table	Đã mark as Date Table, không có gap ngày	Table Tools
Auto date/time	Đã tắt	Options → Data Load
Unused columns	Đã xóa hoặc ẩn	Power Query / Model View
Measures folder	Tất cả measures trong _Measures table	Model View
Performance	Dashboard load dưới 3 giây	DAX Studio
Cross filter	Không có Bidirectional không cần thiết	Model View

10. Kết Luận

Data model tốt = mọi thứ phía sau đều dễ hơn

Designing a financial data model in Power BI không phải bước làm một lần.

Model cần được review mỗi khi thêm nguồn dữ liệu mới hoặc có yêu cầu KPI mới từ portfolio manager.

Nhưng khi model được thiết kế đúng ngay từ đầu, mọi DAX measure về sau đều đơn giản hơn, dashboard nhanh hơn và ít lỗi hơn.

Ba điều cốt lõi cần nhớ

Luôn dùng Star Schema — không bao giờ dùng flat table cho financial data model.
Luôn có Date Table riêng và mark as Date Table — đây là điều kiện tiên quyết cho mọi Time Intelligence measure.
Luôn tắt Auto date/time và xóa cột thừa ngay trong Power Query — không phải sau khi publish.

Tài liệu tham khảo

📘 Understand Star Schema and the importance for Power BI — Microsoft Docs
📘 DAX Overview — tài liệu chính thức DAX
📘 Active vs Inactive Relationships in Power BI — Microsoft Docs
📘 DAX Studio — công cụ miễn phí để tối ưu DAX performance

Về tác giả

Triều Đặng — Middle Data Analyst tại MCNA Technology School. Chuyên về financial data analytics, Power BI và automated reporting trong môi trường Wealth Management.

MCNA Technology School là đơn vị tiên phong đào tạo AI, Big Data, Business Intelligence, Power BI, Python, SQL tại Việt Nam. Đối tác đào tạo của 300+ doanh nghiệp bao gồm Viettel Global, Masan Group, Techcombank, VPBank, Daikin.

📍 30 Trung Liệt, Đống Đa, Hà Nội
📍 Liền kề 44B, TT2, Văn Quán, Hà Đông, Hà Nội
📍 The BIB Space, 50B Phan Tây Hồ, Cầu Kiệu, TPHCM