Data Visualization and Applications: Power BI (Practical)

1. Power BI 與 Business Intelligence

Dashboard：At-a-glance view of key performance indicators (KPIs)。

Power BI：Product of Microsoft；easy to build dashboard for BI；cloud-based service and offline version。Key features：Dashboard、Reporting、Online analytical processing (OLAP)。Other similar products：Tableau。

Business Intelligence (BI)：Technology-driven process 去 analyse data 同 presenting actionable information（Moodle 有 interactive dashboard 例子）。

2. Excel vs Power BI

好多人覺得「Power BI is just an advanced Excel」。佢哋確實有好多共通點：

3. Power BI tools：五個步驟

3.1 DAX：calculated column vs measure（概念）

Slides 只提到 DAX 用嚟整 measures (KPIs) 同 calculated columns，詳細語法列為 self-study（slide 53 附 Microsoft 的 DAX basics 教學）。以下係幫你理解概念的簡單例子（非 slide 內容，用課堂 Student / Module / Results 例子的欄位示意）：

	Calculated column	Measure
計算時間	Load / refresh data 時逐 row 計好，存喺 table	Query 時先計，按當時 filter context（例如 slicer 揀咗邊個 major）動態計
結果	每 row 一個值（新增一欄）	一個 aggregate 值（KPI）
類比 SQL	SELECT …, CASE WHEN … END AS Pass	SELECT COUNT(*) … GROUP BY …（group 由 visual 決定）

-- Calculated column：每一行標記 pass / fail
Pass = IF(Results[Grade] = "F", "Fail", "Pass")

-- Measure：A grade 數目 / 比率（會跟住 slicer、drill-down 自動重算）
Num A   = CALCULATE(COUNTROWS(Results), Results[Grade] = "A")
A Rate  = DIVIDE([Num A], COUNTROWS(Results))

4. Data Model：OLTP vs OLAP

A robust data model is the foundation of performant BI。Transactional systems (OLTP) are optimized for data entry, not data analysis——佢哋有好多 tables 同 complex joins。Solution：A Dimensional Model simplifies the data structure for fast querying and easy summarization。

4.1 OLTP（Online Transactional Processing）

Function：to process (database) transactions and record them。Requirements：Online（real-time, extremely fast, low latency）、ACID compliance、High throughput（concurrently process 大量 transactions）。例子：Student enrollment system（HSU 幾千個學生同時揀科）、e-shop sales processing、bank transaction processing。

4.2 OLAP（Online Analytical Processing）

Perform interactive data analytics：explore and discover interesting findings from the data (statistics)。Online：user 可以做各種 actions 去睇唔同 statistics，response 要快；Data visualization is a key component。

例子（slides 12–13）：一科的 basic statistics → Anything interesting? 睇 breakdown 搵 insights：例如 interaction — check the pass rate according to sex；其他可以 explore：Breakdown by age? Major? Year?

5. Star Schema vs Snowflake Schema

Star Schema：中央係 Fact table，周圍係 Dimension tables（provide additional context to the facts）。Design principles：

• Fact table — What happened? Example：Enrollment
• Dimension tables — Who, What, When, Where? Example：Student / Teacher / Course / Location information

5.1 Example（slides 16–17）

Star Schema	Snowflake Schema
Enrollment (StudentID, Course, Year, Sem) — Fact table Student (StudentID, Major, Department, School)	Enrollment (StudentID, Course, Year, Sem) Student (StudentID, Major) Programme (Major, Department) Unit (Department, School)

Star 版本的 Student dimension：

StudentID	Major	Department	School
1	CS	CS	DS
2	CS	CS	DS
3	DSAI	CS	DS
4	DSAI	CS	DS
5	Math	Math	DS

Snowflake 版本拆成三個 tables：Student (1 CS, 2 CS, 3 DSAI, 4 DSAI, 5 Math)；Programme (CS→CS, DSAI→CS, Math→Math)；Unit (CS→DS, Math→DS)。

觀察：Star 版本 "CS, DS" 重複好多次（redundancy）；Snowflake 每個事實只記一次，但要搵某學生的 School 要 join 三個 tables。

-- 「每個 School 有幾多 enrollment？」
-- Star：1 個 join
SELECT s.School, COUNT(*) FROM Enrollment e JOIN Student s ON e.StudentID = s.StudentID
GROUP BY s.School;
-- Snowflake：3 個 joins
SELECT u.School, COUNT(*) FROM Enrollment e
JOIN Student s   ON e.StudentID = s.StudentID
JOIN Programme p ON s.Major = p.Major
JOIN Unit u      ON p.Department = u.Department
GROUP BY u.School;

6. OLAP Actions (exploration)

6.1 Roll-up / drill-down example 1：Moving up/down in a concept structure

Grades of A and B are "pass"：

Detailed (drill-down)				Summarised (roll-up)
Major	Grade	Count		Major	Grade	Count
DSAI	A	10		DSAI	Pass	25
DSAI	B	15		DSAI	F	3
DSAI	F	3		CS	Pass	28
CS	A	7		CS	F	6
CS	B	21
CS	F	6

Roll-up：DSAI Pass = 10 + 15 = 25；CS Pass = 7 + 21 = 28。由此可計 pass rate：DSAI 25/28 ≈ 89%，CS 28/34 ≈ 82%。

6.2 Roll-up / drill-down example 2：Adding / removing a dimension

加入 dimension "sex" = drill-down；拎走 = roll-up：

Major	Grade	Sex	Count
DSAI	A	M	2
DSAI	A	F	8
DSAI	B	M	8
DSAI	B	F	7
DSAI	F	M	2
DSAI	F	F	1
…	…	…	…

檢查：DSAI A = 2 + 8 = 10 ✓；DSAI B = 8 + 7 = 15 ✓。

6.3 Drill-through example

由 summary（DSAI, A, 10）open another page，列出該 context 的詳細記錄：StudentID / Name / Major / Grade（1 Alice DSAI A；5 Amy DSAI A；11 Bob DSAI A；…）。The other page may use a different set of attributes / metrics（例如 Name 喺 summary 頁冇出現）。

6.4 Slicing example

Select only records of grade A → 剩 (DSAI, A, 10)、(CS, A, 7)。

-- OLAP actions 用 SQL 理解（Enrol(Major, Grade, Sex, StudentID, Name)）
-- Drill-down（加 Sex dimension）
SELECT Major, Grade, Sex, COUNT(*) FROM Enrol GROUP BY Major, Grade, Sex;
-- Roll-up（concept hierarchy：A/B → Pass）
SELECT Major, CASE WHEN Grade IN ('A','B') THEN 'Pass' ELSE 'F' END AS G, COUNT(*)
FROM Enrol GROUP BY Major, G;
-- Slicing（filter）
SELECT Major, Grade, COUNT(*) FROM Enrol WHERE Grade = 'A' GROUP BY Major, Grade;
-- Drill-through（去明細頁）
SELECT StudentID, Name, Major, Grade FROM Enrol WHERE Major = 'DSAI' AND Grade = 'A';

7. VertiPaq：Power BI's core engine

• In-memory
• Columnar database（NoSQL chapter 會再講）
• With compression technique
• Multi-threading
• Caching

High computational speed for usual workload on modern hardware（although the computation complexity is still $O(n)$）。

8. Data cube in OLAP

Efficiency challenge：如果有 billions of records，一個 query 可能要幾分鐘 / 幾粒鐘；user 每次有 follow-up query 都要等。Idea：Pre-compute the common intermediate components for answering all potential queries the user may ask。

8.1 Example data cube（Features: Grade, Major, Sex）

3 個 features，每個 2 個值 → 2 × 2 × 2 = 8 cells。Slide 28 的大數據版本（按每面加總核對過的排列）：

Sex	Grade: A		Grade: F
	DSAI	CS	DSAI	CS
M	2355	711	211	595
F	8712	15	103	16

8.2 Analysis of data cubes

• Size of data cube is much smaller than the database：例子中 cube 只有 8 cells，但 database 可以有 10,000+ records（上表 8 個數加埋 = 12,718）
• Advantage：The system can react to OLAP actions much faster
• Limitations：(1) 分析涉及 new feature → 要 recalculate a new data cube；(2) New data added → 要 maintenance 去 update cube，否則分析唔包括新資料

8.3 用 cube 做 drill-down / roll-up（slides 30–31）

細數據版本 cube（Grade × Major × Sex）：

Sex	Grade: A		Grade: F
	DSAI	CS	DSAI	CS
M	2	7	2	5
F	8	0	1	1

Summarizing the data along some dimension(s)（加總 Sex）→ (A, DSAI, 10)、(A, CS, 7)、(F, DSAI, 3)、(F, CS, 6)；再加總 Major → (A, 17)、(F, 9)。反方向 break down the cube 就係 drill-down：Grade → Grade × Major。全部只需要加 8 個 cells，完全唔使掂原始 database。

8.4 Generating the data cube：It is just a GROUP BY query

SELECT Grade, Major, Sex, COUNT(*) AS Count
FROM Enrol
GROUP BY Grade, Major, Sex;
-- (A, DSAI, M, 2), (A, DSAI, F, 8), (F, DSAI, M, 2), (F, DSAI, F, 1), …

-- 之後所有 OLAP action 都喺 cube 上做，例如 roll-up 到 Grade：
SELECT Grade, SUM(Count) FROM Cube GROUP BY Grade;   -- A 17, F 9

8.5 VertiPaq vs Data cube

9. Power BI practical walkthrough

1. Getting data：Home > Get data。Data sources：Databases（Microsoft SQL (MSSQL)、other databases）、Files（CSV、Excel）
2. In-class example：Moodle 上 3 個 CSV files（exam results database）：Student、Module、Results
3. Import Module.csv：Check the data values. Are they what we want? Remember data quality is very important! 問題：The column headers are not recognized! → 用 Power Query interface 轉換
4. Power Query Editor：Make the first row as column name（Use First Row as Headers）。Self-study：explore 其他 Power Query operations
5. Exercise：Home > New Source，加埋另外兩個 CSV
6. Joining the tables：Home > Combine > Merge Queries。Options：select the table to join、select the columns to join、select the join type
7. Exercise：用 Merge Queries 將 Student 同 Module 合併入 Results table
8. Preparing the column for aggregation（for preparing the underlying data cube）：Home > Group by——count the number of records; group by student, module and grade
9. Result：Every record has a count of 1。Don't worry：之後可以做更高層次 grouping；呢個 data cube 係用嚟 support further aggregation
10. Close Power Query Editor → add visual components [self-study] → Done!

9.1 Merge Queries 的 join types

A = Module (COM6001 Computing, COM6002 Database)；B = Result (123456 COM6001, 234567 COM6003)。

Join type	結果
A INNER JOIN B	(COM6001, Computing, 123456, COM6001)
A Left Outer Join B	(COM6001, Computing, 123456, COM6001)；(COM6002, Database, Null, Null)
A Full Outer Join B	上面兩行 + (Null, Null, 234567, COM6003)
A Left Anti Join B	(COM6002, Database, Null, Null) — 只要 records in A but not B

-- Left Anti Join 用 SQL 表達：
SELECT m.* FROM Module m LEFT JOIN Result r ON m.ModuleCode = r.Module
WHERE r.Module IS NULL;            -- COM6002（冇人修）

10. Exercises & self-study

Exercise（Moodle 上的 Power BI demo）：

• Who received the most A grades? → 101101 Amy（lecturer notes）
• Which module(s) is/are easier to get A? → COM6002 and COM6003
• Which module(s) is/are not easy to get a pass? → COM6102
• What else interesting can you find from the dashboard?

In-class participation：download Microsoft 官方 Power BI financial sample（或 Moodle 的 xlsx），design a dashboard（hint：研究 columns，例如 COGS = Cost of goods sold），提交 Power BI file（2 marks）。

Self-study area：DAX (Data Analysis Expressions)、Publishing reports and dashboards、Scheduling Data Refresh for datasets。

自我測驗 Self-test

Q1. 列出 Excel 同 Power BI 的三個共通點同三個分別。

共通：都係 Microsoft 產品；都用 Power Query Editor 做 ETL；Power Pivot / Power BI 都用 VertiPaq engine；都用 DAX 做 calculated columns 同 measures；都可做 visualization。分別：Purpose（spreadsheet vs BI dashboard）、Capacity（~1M rows 變慢 vs big data）、Data model（optional vs mandatory）、Refresh（manual vs scheduled）、Sharing（file vs cloud）。

Q2. 大學選科系統同 BI dashboard 分別屬於 OLTP 定 OLAP？各自的要求係乜？

選科系統 = OLTP：大量 concurrent users、每次改少量 records、ACID 非常重要、response time 要極短、query 簡單。Dashboard = OLAP：少量 users、read-only aggregate queries、數據來自 warehouse/lake、唔需要 strong consistency、response 可以稍長但仍 real-time、query 複雜、可接受較長 initial setup。

Q3. 比較 star schema 同 snowflake schema，並解釋點解 OLAP 偏好 star schema。

Star：central fact table + 非 normalized 的 dimension tables；joins 少、query 快、用多啲 disk（redundancy）。Snowflake：dimension tables normalized（有 child dimension tables）；joins 多而複雜、query 慢、慳空間。OLAP 主要讀取分析，速度重要過空間，所以偏好 star。

Q4. Normalization 的兩個目的係乜？

(1) Minimize data redundancy；(2) Improve data integrity。

Q5. 由 (Major, Grade, Count) 表：DSAI A 10、B 15、F 3；CS A 7、B 21、F 6。將 A/B roll-up 成 Pass，並計各 major 的 pass rate。

DSAI Pass = 25、F = 3 → 25/28 ≈ 89.3%。CS Pass = 28、F = 6 → 28/34 ≈ 82.4%。

Q6. 以下屬於邊種 OLAP action？(a) 加入 Year dimension 睇每年分佈 (b) 只睇 2025 年數據 (c) 由 "CS, F, 6" 跳去另一頁列出 6 個學生名 (d) 將 Major 合併成 School 層次

(a) Drill-down（加 dimension）；(b) Slicing（filter）；(c) Drill-through；(d) Roll-up（concept hierarchy 向上）。

Q7. Data cube 的三個 assumptions 同兩個 limitations 係乜？Features Grade (A, B, F)、Major (DSAI, CS, Math)、Sex (M, F) 的 cube 最多有幾多 cells？

Assumptions：知道 user 會 explore 的 features F；F 夠細；F 值組合數遠少於 database size。Limitations：新 feature 要重新建 cube；新數據要 maintenance 更新 cube，否則分析唔包括新數據。Cells = 3 × 3 × 2 = 18。

Q8. 寫 SQL 由 Enrol(StudentID, Grade, Major, Sex) 生成 data cube，再由 cube roll-up 到每個 Major 的總人數。

CREATE TABLE Cube AS SELECT Grade, Major, Sex, COUNT(*) AS Count FROM Enrol GROUP BY Grade, Major, Sex; 然後 SELECT Major, SUM(Count) FROM Cube GROUP BY Major;（要用 SUM(Count) 唔係 COUNT(*)。）

Q9. VertiPaq 有咩特點？佢同 data cube 比較有咩取捨？

VertiPaq：in-memory、columnar、compression、multi-threading、caching；快但 complexity 仍 $O(n)$。對比 data cube：VertiPaq 係 native、可處理任何 query、唔使額外 storage 或準備時間，適合 general workload；data cube 對 anticipated queries 近乎即時，但要額外（少量）storage、只支援預計的 queries、建 cube 喺大 database 好慢、而且 Power BI 要外部 cube。

Q10. Power Query Merge Queries 中，A Left Anti Join B 會回傳乜？用 slide 例子答。

只回傳喺 A 但唔喺 B 的 records：A = Module、B = Result，結果係 (COM6002, Database)——冇學生修的 module。

一頁總結 Cheat sheet