1 / 36

線上分析式處理 On-line Analytical Processing OLAP

線上分析式處理 On-line Analytical Processing OLAP. 張珀銀、李建祥 2008.1.23. 大 綱. 功能特色 維度模式 特殊型態 塑模步驟 儲存方式 MDX 簡介 結 論. 功 能 特 色. 含 階層式關係的 多維度結構 有效的交叉比較與分析規格 資料結構與呈現方式的分離 多樣且彈性的操作 Roll-up Drill-down Slice and Dice Pivot. 維 度 模 式. 事實資料表 需要以彙總函數計算的屬性 維度資料表 分析所需的分群屬性 維度模式結構

phailin
Download Presentation

線上分析式處理 On-line Analytical Processing OLAP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 線上分析式處理On-line Analytical ProcessingOLAP 張珀銀、李建祥 2008.1.23

  2. 大綱 • 功能特色 • 維度模式 • 特殊型態 • 塑模步驟 • 儲存方式 • MDX簡介 • 結 論

  3. 功能特色 • 含階層式關係的多維度結構 • 有效的交叉比較與分析規格 • 資料結構與呈現方式的分離 • 多樣且彈性的操作 • Roll-up • Drill-down • Slice and Dice • Pivot

  4. 維度模式 • 事實資料表 • 需要以彙總函數計算的屬性 • 維度資料表 • 分析所需的分群屬性 • 維度模式結構 • 星狀綱目(Star Schema) • 雪花綱目(Snowflake Schema)

  5. 特殊型態 • 非事實資料表(Factless Fact Table) • 事件追蹤管理(Event Tracking) • 問題：學生出席的次數? • 解法：增加虛擬欄位，再利用Count()加總 • 覆蓋分析(Coverage Analysis) • 問題：促銷期間沒有賣出的商品? • 解法：建立覆蓋事實資料表，存入所有促銷商品，再與銷售事實資料表進行差集處理 • 快照事實資料表(Snapshot Fact Table) • 彙總某時點的資料並儲存成預存資料，做為OLAP的資料源，以提升處理效能

  6. 塑模步驟 • 建立事實資料表 • 資料表格中需要被彙總的欄位 • 建立維度資料表 • 將其他非彙總性欄位根據分析角度歸類進行反正規化 • 將各維度資料表主鍵加入事實資料表 銷售事實 ----------------------- ProductId TimeId CustomerId StoreId SalesQty SalesAmount

  7. 儲存方式 • Relational OLAP (ROLAP) • 明細資料與彙總資料存於RDB • 即時但效率差 • 儲存空間耗用較低 • Multi-dimensional OLAP (MOLAP) • 明細資料與彙總資料存於CUBE • 即時性差但效率好 • 儲存空間耗用較高 • Hybrid OLAP(HOLAP) • 明細資料於RDB • 彙總資料存於CUBE • 查詢效率近似MOLAP • 儲存空間介於MOLAP與ROLAP之間

  8. MDX簡介 • MDX (Multidimensional Expressions) • 針對OLAP多維資料庫的多維度查詢語言 • 1997年由Microsoft 所提出，為OLE DB FOR OLAP的一部分，之後即於1998年內建於SQL SERVER中，名為Analysis Services。 • 儘管MDX並非開放式的規格，其他廠商仍採用此規格，可見MDX確實有它的重要性。 • 因MDX由MS所主導，之後的範例以SQL 2005中的Analysis Services為主。 • OLAP資料庫中的CUBE結構 • 多維度的資料結構 (CUBE)，瞭解MDX之前，須先瞭解CUBE的結構

  9. CUBE結構 • OLAP資料庫 • 多維度的資料結構 (CUBE)

  10. CUBE的物件架構與命名原則 • 在Analysis Services中每個物件都要有個識別碼，以中括號[]括起來。 • 找2005年的二月 • [TIME].[ALL].[2005].[Q1].[二月] • 有一原則：結構後面可以接成員，成員不能接結構

  11. MDX語法的分類 • 資料定義語言：CREATE，DROP等等 • 資料操作語言：SELECT，UPDATE • 指令碼陳述式：管理變數範圍，流程控制的指令碼：如SCOPE，CACULATE

  12. 基本的MDX查詢句 • SELECT[AXIS的定義 [, <AXIS的定義>…]]FROM <CUBE的定義>WHERE <SLICER 定義> • AXIS：在軸上所要看到的維度成員或計量值, 亦可以使用index的方式指定0：COLUMNS，1：ROWS2：PAGES，3：SECTIONS4：CHAPTERS • CUBE：指定想要用的CUBE • SLICER：指定要出現在結果集中的成員或量值

  13. 基本的MDX查詢句 • 範例：以SQL 2005中的範例資料庫”Adventure Work”為例 • MDX CODE • SELECT {[Measures].[Sales Amount], [Measures].[Order Quantity]} on columns, {[Product].[Category].[Bikes],[Product].[Category].[Clothing]} on rowsFROM [Adventure work] on columns on rows

  14. 基本的MDX查詢句 • SELECT {[Measures].[Sales Amount], [Measures].[Order Quantity]} on columns, {[Product].[Category].[Bikes],[Product].[Category].[Clothing]} on rowsFROM [Adventure work] WHERE {[Date].[Calendar Year].[CY 2002]} • 此結果只有2002年的總合 on columns on rows

  15. 基本的MDX查詢句 • SELECT {[Measures].[Sales Amount], [Measures].[Order Quantity]} on columns, {[Product].[Category].[Bikes],[Product].[Category].[Clothing]} on rowsFROM [Adventure work] WHERE {[Date].[Calendar Year].[CY 2002], [Date].[Calendar Year].[CY 2003]} • 此結果為2002與2003年的總合 on columns =16,927(2002)+35,331(2003) on rows

  16. 基本的MDX查詢句 • SELECT {[Sales Channel].[Sales Channel].[Sales Channel].Members}*{[Measures].[Sales Amount], [Measures].[Order Quantity]} on columns, {[Product].[Category].[Bikes],[Product].[Category].[Clothing]} on rowsFROM [Adventure work] WHERE {[Date].[Calendar Year].[CY 2002], [Date].[Calendar Year].[CY 2003]} 使用*運算子將Members 與Sales Amount跟Order Quantity兩個集合交叉乘積

  17. 在SELECT中以WITH 建立導出成員，命名集，計算資料表格。WITH所建立的物件僅限於當次查詢有效。 • WITH [<導出成員的定義>] • [<自訂命名集的定義>] • [<資料格計算的定義>] • 導出成員的定義方式 • MEMBER [<成員名稱>] AS ‘<計算公式>’ • [, SOLVE_ORDER =<解決順序>] • [,CELL_PROPERTY>=<VALUE_EXPRESSION>…] • [,FROMAT_STRING=<顯示格式設定>] 進階的MDX查詢句

  18. 展示一綜合範例如下 • WITH • -- 新增導出成員 達成率 • Member [Measures].[達成率] AS • [Measures].[Sales Amount]/ • [Measures].[Sales Amount Quota] • , Format_String =‘Percent’ – 顯示為百分比 • -- 新增導出成員 去年同期達成率Member [Measures].[去年同期達成率]AS {ParallelPeriod([Date].[Calendar].[Calendar Year]), Measures.[達成率]} • , Format_String = ‘Percent’ --顯示為百分比 進階的MDX查詢句

  19. 續上之範例如下 • SELECT {[Measures].[Sales Amount], [Measures].[Sales Amount Quota], • [Measures].[達成率], • [Measures].[去年同期達成率] on 0, • {Sales Territory}.[Sales Territory Country].Members on 1 • FROM [Adventure Works] • WHERE • [Date].[Calendar].[Calendar Year].[CY 2004]} 進階的MDX查詢句

  20. 進階的MDX查詢句 使用 WITH 所建立的導出成員

  21. 進階的MDX查詢句 • NON EMPTY: 查詢非空值之結果 • 假設ML為非空值之ROWS • Select • {[Measures].[Sales Amount]} on 0, • Non Empty [ML] on 1 • From [Adventure Works]

  22. SUBCUBE:若只需要與cube中的一小部分進行互動，可將查詢範圍限制在subcube中，可以改善查詢效能。CREATE SUBCUBE <Subcube 別名> AS <Subcube 運算式> • 範例 • CREATE SUBCUBE [Adventure works] AS • SELECT • {[Measures].[Sales Amount]} on 0, • {[Q3 CY 2003] on 1} • FROM [Adventure work] • Select • {[Measures].[Sales Amount]} on 0, • Non Empty [ML] on 1 • From [Adventure Works] 進階的MDX指令

  23. 進階的MDX指令 • Drill Through：一個cube中存放了來源與匯總資料，select可取後匯總資料，而drill through則可以取得來源資料。 • DrillThrough 語法 • DrillThrough [<資料筆數>][<起始筆數>] <MDX SELECT> [<回傳欄位>] • 範例 • DrillThrough • Select • {[十月 30, 2003], [Order Quantity]} on 0, • {[Bikes]} on 1

  24. 結論 • OLAP解決了SQL在RDB上彙總資料的彈性與效能問題 • 階層式的多維度CUBE結構，能提供給決策者以Roll-up 或 Drill-down方式分析資料 • 多種儲存結構(ROLAP, MOLAP, HOLAP)滿足效能及即時性的需求 • MDX提供開發者存取CUBE的語言，能直接存取彙總資料，不需要利用SQL彙總交易資料，能發揮OLAP的優點

  25. OLAP階層與量值 • CUBE的結構裡有維度和量值，而維度下有階層，階層下有層級，每個層級下裡有許多成員，其從屬關係如下 維度 成員 階層 成員 層級 成員 量值

  26. OLAP階層與量值│範例 • 以某商品的銷售量的時間維度 為例 成員 時間維度 年 2001 2002 2003 季 Q1 Q2 Q3 Q4 月 JAN. FEB. ….. 日 MON. ….. 銷售量

  27. 有效的分析規格

  28. 資料結構與呈現方式

  29. 交叉分析範例

  30. 聚總與下鑽

  31. CUBE結構

  32. 事實與維度資料 事實資料： 銷售量 維度資料： 銷售年度 維度資料： 車型

  33. 星狀綱目 產品維度 ---------------------- ProductId BrandName ProductName SKU 時間維度 ---------------------- TimeId TheYear TheMonth TheDay 銷售事實 ----------------------- ProductId TimeId CustomerId StoreId SalesQty SalesAmount 顧客維度 ---------------------- CustomerId AccountNum Name Address 門市維度 ---------------------- StoreId StoreName StoreType StoreAddress

  34. 雪花狀綱目 產品類別維度 ---------------------- ProductClassId ProdSubcategory ProdCategory ProdDepartment 門市區域維度 ---------------------- RegionId SalesCity SalesRegion SalesCountry 產品維度 ---------------------- ProductId BrandName ProductName SKU 門市維度 ---------------------- StoreId StoreName StoreType StoreAddress 銷售事實 ----------------------- ProductId TimeId CustomerId StoreId SalesQty SalesAmount 顧客維度 ---------------------- CustomerId AccountNum Name Address 時間維度 ---------------------- TimeId TheYear TheMonth TheDay

  35. 維度資料表的建立 尺寸資料表 --------------- SizeId SizeUnit SizeQty 產品維度資料表 ---------------------- ProductId ProductName ColorName TypeName SizeUnit SizeQty 產品資料表 ----------------- ProductId ProductName ColorId TypeId SizeId 反正規化 顏色資料表 --------------- ColorId ColorName 型別資料表 --------------- TypeId TypeName

  36. 塑模步驟三 產品維度 ---------------------- ProductId BrandName ProductName SKU 時間維度 ---------------------- TimeId TheYear TheMonth TheDay 銷售事實 ----------------------- ProductId TimeId CustomerId StoreId SalesQty SalesAmount 顧客維度 ---------------------- CustomerId AccountNum Name Address 門市維度 ---------------------- StoreId StoreName StoreType StoreAddress

More Related