SQL Server 2005 Analysis Service

Presentation on theme: "SQL Server 2005 Analysis Service"— Presentation transcript:

1 SQL Server 2005 Analysis Service
錢曉明 資策會 資深講師

2 Agenda SQL Server Analysis Services Architecture 統一維度模型(UDM)
使用Cube auto-build精靈 導出成員(Calculated Member) 關鍵績效指標KPI設計與管理 商業智慧範本 動作 (Action) 構面 (Perspectives) 支援多國語言 (Translations) 資料分割(Partitions) 儲存設定與效能 權限設定

3 Agenda SQL Server Analysis Services Architecture 統一維度模型(UDM)
使用Cube auto-build精靈 導出成員(Calculated Member) 關鍵績效指標KPI設計與管理 商業智慧範本 動作 (Action) 構面 (Perspectives) 支援多國語言 (Translations) 資料分割(Partitions) 儲存設定與效能 權限設定

4 Analysis Services Architecture
Server Component是一個 Windows Service SQL 2005 Analysis Service 可支援多個Instance Clients Component透過 XML for Analysis (XMLA) 與Server溝通

5 Server Component Server Component 即是 msmdsvr.exe
其中包含 XML/A listener 元件及其他許多內部元件，負責下列工作: Parse clients 端傳來的 Query statements 管理 meta data 處理 transactions Process calculations 儲存 dimension and cell data Create aggregations Schedule queries Caching objects Manage server resources

6 Client Architecture

7 Agenda SQL Server Analysis Services Architecture 統一維度模型(UDM)
使用Cube auto-build精靈 導出成員(Calculated Member) 關鍵績效指標KPI設計與管理 商業智慧範本 動作 (Action) 構面 (Perspectives) 支援多國語言 (Translations) 資料分割(Partitions) 儲存設定與效能 權限設定

8 統一維度模型 （Unified Dimensional Model (UDM) ）
二○一八年十二月三日 統一維度模型 （Unified Dimensional Model (UDM) ） Analysis Services Spreadsheets XML/A or ODBO SQL Server Datamart BI Front Ends UDM Teradata DW Ad-Hoc Reports Cache Rich Reports Oracle DB2 LOB Dashboards 8 © 2006 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

9 UDM的好處 不必維護個別資料模型，所有資料採礦演算法均支援資料倉儲及多維度分析作為資料來源
整合異質資料來源(如SQL，Oracle)，透過資料來源檢視並可定義異質資料表間的關聯 前端工具可透過OLE DB Provider及XML/A共通介面存取資料 可使用Friendly Name，可使用前端使用者熟悉的產業專用術語進行分析 即時分析，透過不同等級的快取模式，同時兼顧即時性及存取效率

10 Agenda 使用Cube auto-build精靈 資料分割(Partitions)與儲存設定
SQL Server Analysis Services Architecture 統一維度模型(UDM) 使用Cube auto-build精靈 導出成員(Calculated Member) 關鍵績效指標KPI設計與管理 商業智慧範本 動作 (Action) 構面 (Perspectives) 支援多國語言 (Translations) 資料分割(Partitions)與儲存設定 權限設定

11 建立Analysis Service Solution
Data Source 資料來源可不只一個 異質型資料來源中至少要有一個是來自SQL Server Data Source View 不只一個資料來源時要先加一個Primary Data Source Fact Table – 包含Foreign Key 與 Measure（如金額，數量） Dimension Table – 包含Primary Key及與Measure相關資訊

12 使用Cube auto-build精靈 啟動Cube Wizard 自動判斷Fact table與Dimension table
Star Schema—透過Primary Key/Foreign Key 與Fact table有直接關聯的 SnowFlake Schema —透過另一個Table 與Fact table 有間接關聯的 設定時間維度 自動偵測量值與量值群組 自動偵測階層 命名與發佈

13 量值群組(Measure Group) 一個Cube可以支援多個Fact Table 存在於同一個Fact Table
裡面的量值構成一個集合，稱之為「量值群組」 全新彙總函數Aggregate Function

14 Attribute Hierarchies
由 Source Database 欄位產生 為方便瀏覽可結合多個Attribute形成Hierarchy 可將欄位直接放在報表上，而不需透過Member properties and virtual dimensions

15 Agenda 使用Cube auto-build精靈 資料分割(Partitions)與儲存設定
SQL Server Analysis Services Architecture 統一維度模型(UDM) 使用Cube auto-build精靈 導出成員(Calculated Member) 關鍵績效指標KPI設計與管理 商業智慧範本 動作 (Action) 構面 (Perspectives) 支援多國語言 (Translations) 資料分割(Partitions)與儲存設定 權限設定

16 導出成員(Calculated Member)
導出成員係客製化之量值或維度成員

17 Agenda 使用Cube auto-build精靈 資料分割(Partitions)與儲存設定
SQL Server Analysis Services Architecture 統一維度模型(UDM) 使用Cube auto-build精靈 導出成員(Calculated Member) 關鍵績效指標KPI設計與管理 商業智慧範本 動作 (Action) 構面 (Perspectives) 支援多國語言 (Translations) 資料分割(Partitions)與儲存設定 權限設定

18 關鍵績效指標 (Key Performance Indicator (KPI))
一系列財務與非財務性的數值 用以定義企業組織的目標 反映執行目標過程的績效 與一般量值最大的差異在於，KPI 提供比較性的資訊，如目標值，達成率，成長率，可提供決策管理人快速掌握企業營運的狀況

19 關鍵績效指標 (Key Performance Indicator (KPI))
KPI -- 必須至少與一個Measure Group 相關聯 亦可同時與所有Measure Group 相關聯 KPI包含下列元素： Value Expression(實際量值)，如Sales, Profit，可透過MDX 運算式產生 Goal Expression(目標值)，可透過MDX 運算式產生 Status Expression(狀態值)，以實際量值與目標值比較，將業績達成率轉換成+/-1之間的值，可以圖形顯示 Trend Expression(趨勢值)，以實際量值與目標值比較，是往好的還是壞的發展，可以圖形顯示

20 Status Expression範例(一)
Case When [Measures].[Sales Amount - Fact Reseller Sales] > [Measures].[Sales Amount Quota] Then 1 When [Measures].[Sales Amount - Fact Reseller Sales] >= .90 * ( [Measures].[Sales Amount Quota] ) Then .5 When [Measures].[Sales Amount - Fact Reseller Sales]>= .80 * ( [Measures].[Sales Amount Quota] ) Then 0 .70 * ( [Measures].[Sales Amount Quota] ) Then -.5 Else -1 End

21 Trend Expression範例(一)
Case When IsEmpty([Dim Time].[CalendarYear].PrevMember) Then 1 When [Measures].[Sales Amount - Fact Reseller Sales] > ( [Dim Time].[CalendarYear].PrevMember, [Measures].[Sales Amount Quota] ) When [Measures].[Sales Amount - Fact Reseller Sales] >= .95 * ( [Dim Time].[CalendarYear].PrevMember, Then .5 When [Measures].[Sales Amount - Fact Reseller Sales]>= .90 * ( [Dim Time].[CalendarYear].PrevMember, Then 0 .85 * ( [Dim Time].[CalendarYear].PrevMember, Then -.5 Else -1 End

22 Goal Expression範例(一) 較去年同期成長一成
1.1*([Measures].[Sales Amount], ParallelPeriod([Order Date].[CalendarYear],1, [Order Date].[CalendarYear]. [CalendarYear].CurrentMember))

23 Status Expression範例(二)
case when KpiValue("Internet Sales")/ KpiGoal("Internet Sales")>=1 then 1 KpiGoal("Internet Sales")>=0.8 then 0 else -1 end

24 Status Expression範例(三)
Case When [Measures].[Total GPM] <=.18 Then -1 When [Measures].[Total GPM] >=.19 And [Measures].[Total GPM] <=.24 Then -.5 When [Measures].[Total GPM] >=.25 And [Measures].[Total GPM] <=.28 Then 0 When [Measures].[Total GPM] >=.28 And [Measures].[Total GPM] <=.32 Then .5 Else 1 End

25 Trend Expression範例(三)
Case When IsEmpty([Date].[Fiscal Time].PrevMember) Then 1 When [Measures].[Total GPM] > ([Date].[Fiscal Time].PrevMember, [Measures].[Total GPM]) When [Measures].[Total GPM] = Then 0 Else -1 End

26 Agenda 使用Cube auto-build精靈 資料分割(Partitions)與儲存設定
SQL Server Analysis Services Architecture 統一維度模型(UDM) 使用Cube auto-build精靈 導出成員(Calculated Member) 關鍵績效指標KPI設計與管理 商業智慧範本 動作 (Action) 構面 (Perspectives) 支援多國語言 (Translations) 資料分割(Partitions)與儲存設定 權限設定

27 商業智慧範本 透過範本修改Cube或Dimension進階設定 可由Cube Designer上方工具列加入或
定義時間智慧 年初至今 12個月移動平均 年成長% 年成長 自動產生時間維度導出成員 透過MDX Script 撰寫

28 Agenda 使用Cube auto-build精靈 資料分割(Partitions)與儲存設定
SQL Server Analysis Services Architecture 統一維度模型(UDM) 使用Cube auto-build精靈 導出成員(Calculated Member) 關鍵績效指標KPI設計與管理 商業智慧範本 動作 (Action) 構面 (Perspectives) 支援多國語言 (Translations) 資料分割(Partitions)與儲存設定 權限設定

29 動作 (Action) 可在所點選的項目上，觸發Analysis Service產生對應的指令，以執行其他應用程式 有下列選項： URL
開啟另一個網頁 Statement 執行 OLE DB command. Dataset 傳回一個資料集 to a client application. Rowset 傳回一個資料列集 to a client application. Drillthrough 傳回細部資訊 Reporting 傳送一個帶參數的 URL 給 report server 傳回一份報表 to a client application. Proprietary 使用上述方式以外的作業操作

30 動作 (Action) URL 範例 – Google 搜尋
" Product].[English Product Name].currentMember.Properties("Caption")+"&meta="

31 Agenda 使用Cube auto-build精靈 資料分割(Partitions)與儲存設定
SQL Server Analysis Services Architecture 統一維度模型(UDM) 使用Cube auto-build精靈 導出成員(Calculated Member) 關鍵績效指標KPI設計與管理 商業智慧範本 動作 (Action) 構面 (Perspectives) 支援多國語言 (Translations) 資料分割(Partitions)與儲存設定 權限設定

32 構面 針對特定目的，簡化User看到的內容

33 Agenda 使用Cube auto-build精靈 資料分割(Partitions) 儲存設定與效能
SQL Server Analysis Services Architecture 統一維度模型(UDM) 使用Cube auto-build精靈 導出成員(Calculated Member) 關鍵績效指標KPI設計與管理 商業智慧範本 動作 (Action) 構面 (Perspectives) 支援多國語言 (Translations) 資料分割(Partitions) 儲存設定與效能 權限設定

34 支援多國語言 (Translation) 可定義Cube Object 與Dimension Object 對應到資料庫中資料表所提供的翻譯語言

35 Agenda 使用Cube auto-build精靈 資料分割(Partitions) 儲存設定與效能
SQL Server Analysis Services Architecture 統一維度模型(UDM) 使用Cube auto-build精靈 導出成員(Calculated Member) 關鍵績效指標KPI設計與管理 商業智慧範本 動作 (Action) 構面 (Perspectives) 支援多國語言 (Translations) 資料分割(Partitions) 儲存設定與效能 權限設定

36 資料分割(Partitions) 預設以個別FactTable 為基本資料分割 資料分割優點： 可減少單一分割資料量，加速查詢效能
可平行處理，縮短process時間 可儲存於不同空間 可設計不同彙總比例 分割方式： 多個FactTable 單一FactTable，用where條件篩選

37 設定篩選條件 需注意資料不要重疊，亦不要遺漏

38 資料分割儲存位置 可選擇儲存本機或遠端Server

39 Agenda 使用Cube auto-build精靈 資料分割(Partitions) 儲存設定與效能
SQL Server Analysis Services Architecture 統一維度模型(UDM) 使用Cube auto-build精靈 導出成員(Calculated Member) 關鍵績效指標KPI設計與管理 商業智慧範本 動作 (Action) 構面 (Perspectives) 支援多國語言 (Translations) 資料分割(Partitions) 儲存設定與效能 權限設定

40 儲存設定與效能 進階的快取(Proactive Cache)：不同等級的快取，區隔不同資料瀏覽的需求，取大量資料與效率間的平衡
MOLAP—效能最佳，不會主動通知更新，不會自動重新 Process Scheduled MOLAP--不會主動通知更新，24小時自動重新 Process Automatic MOLAP--會主動通知更新，會保留Cache，資料更新延遲最長兩小時 Medium Latency MOLAP --會主動通知更新，當Process Cache時，會切到Real Time ROLAP， 資料更新延遲最長四小時 Low Latency MOLAP --會主動通知更新，當Process Cache時，會切到Real Time ROLAP， 資料更新延遲最長30分鐘 Real Time HOLAP Real Time ROLAP—效能最差，資料最新

41 Agenda 使用Cube auto-build精靈 資料分割(Partitions) 儲存設定與效能
SQL Server Analysis Services Architecture 統一維度模型(UDM) 使用Cube auto-build精靈 導出成員(Calculated Member) 關鍵績效指標KPI設計與管理 商業智慧範本 動作 (Action) 構面 (Perspectives) 支援多國語言 (Translations) 資料分割(Partitions) 儲存設定與效能 權限設定

42 權限設定 預設所有Administrators群組的成員即為Analysis Service成員，可執行任何工作
可透過Server Level 的Server Role或Database Level 的群組設定Process Database 或View Definition 權限