1. OLTP & OLAP
微軟推出的Analysis Services從1998年開始推出第一版,為了解決資料分析時,反應時間過慢的問題。
本篇主要會介紹Analysis Services以前的處理觀念
首先要了解OLTP和OLAP的概念:
- OLTP
全名為Online Transactional Processing。這是傳統資料庫(DB Server)處理資料的方式,秉持著ACID的精神,將每一筆資料(每一筆交易)完整的儲存到資料庫。舉凡SQL Server、Oracle DB...等,著重在資料的Insert、Delete、Update。
- OLAP
全名為Online Analytical Processing。此架構的Server主要是用來做商業資料(BI)的分析。原因在於,使用OLTP的Server來分析資料時,會遇到Aggregate的效能問題。若Index建得不好、資料選取範圍過大時,會導致整個DB的效能下降,影響其他User的使用。尤其在20多年前那種硬體沒有很厲害的年代,下SQL時使用聚合函數(SUM、COUNT、AVG、MAX、MIN)時,會到硬碟裡找出所有資料,再將數值聚合運算。這是涉及到硬體的IO,所以為了解決硬體的效能限制,衍伸出OLAP的概念。
OLAP的概念就是「預先聚合(Aggregate)、處理(Processing)需要分析的維度,將值存在Server中」。預先將欲分析的維度做好,用這些維度建立一個Cube(是一種資料結構),OLAP Server會依據Cube的維度,自行組出一段SQL,定時向OLTP Server取資料並存在自己的儲存裝置內。User請求查詢時,都向OLAP Server取資料,直接就可以把聚合過的資料呈現給User。
例如:User下了一個時間超長的查詢,此時就算有Index其實也沒用,畫面就會Hang住,然後重新整理,又再下一次。
SELECT month, sum(sales)
FROM sales_table
WHERE month between to_date('2010/01/01', 'yyyy/mm/dd') and trunc(sysdate)
GROUP BY month;
此時就需要用到OLAP架構的BI系統,以提升查詢效率。
概念圖如下:
(1) User發送Request查詢(使用MDX語言)
(2) OLAP Server解析Request,挑選特定的Cube,將資料取出,回傳給User
(3) OLAP Server會依據Cube的維度,自行組出一段SQL,定時向OLTP Server取資料並存在自己的儲存裝置內
2. Cube
Cube是一個多維度資料結構,取出資料的方式分以下幾種(圖片均從Wiki來),很文謅謅,取一堆名字:
- Slice
只用某一維度取出資料
- Dice
用多種維度取資料
- Drill Up & Down
可以透過子分類查詢母分類(Drill Up),也可以從母分類查詢子分類(Drill Down)
3. MDX
全名為Multidimension Expression,是多維度資料庫(OLAP Server)的查詢語言,OLAP Server會解析MDX,將其轉換成SQL。
SELECT
{ [Measures].[Sales] } on columns,
{ [Date].[Q1, 2021], [Date].[Q2, 2021], [Date].[Q3, 2021], [Date].[Q4, 2021] } on rows
FROM [SalesCube]
WHERE ([SalesRegion].[ASIA])