商业智慧 -09 SSAS分析服务

1、Microsoft SQL Server 2005SSAS分析服務Instructor：Su-Hsien Huang.商業資料的特性決策援助的需求比較特定期間內, 各部門生產力成長的相對比率?依所選擇的產品線定義相關的市場佔有率定義廣告類型和銷售程度之間的關聯. 而這個關聯可以運用於預測營運前台的資料龐大到無法人為解毒細節需求適度的彙總(Aggregate)，把明細資料轉成有意義的資訊，透過報表呈現.傳統資料庫與資料倉儲資料的不同傳統資料庫資料倉儲關聯式資料正規化的資料,資料正確性高著重在交易最佳化可擷取即時資訊常用動作：新增、刪除、修改資料庫中某個時間的資料著重在資料總和,查詢速度要快常用動

2、作：“清空-載入”與”刪除-附加”.商業智慧市場概況1998: SQL Server 7 OLAP Service2000: SQL Server 2000 Analysis Service2002年擊敗Hyperion成為多維度分析霸主市佔率由14.3%(2001) 20% (2004)迫使IBM收購Informix (2000)，並且與Hyperion維持OEM關係2003: Business Object收購Crystal Decision (原微軟夥伴)2003 : Hyperion併購Brio (報表與ETL專家)動搖了IBM與Hyperion的协作基礎2005: IBM併購Alph

3、ablox並且與Hyperion分手2005: SQL Server 2005 Analysis Service.2005商業智慧市場概況關聯式資料庫市場IBM (34.2%)Oracle (33.9%)Microsoft (20.0%)NCR (3.0%)Others (8.9%)OLAP市場Microsoft (28%)Hyperion (19%)Cognos (14%)Business Object (7%)Microstrategy (7%)SAP (6%).6OLAP 市場概況olapreport.Inmon v.s. KimballInmon資料倉儲之父一個整合的全公司資料倉儲資料

4、整合性高本钱較高Kimball商業智慧之父創立star schema、snowflake schema、dimension architecture每個部門各自維持本人的資料超市(Data mart)資料分散本钱較低查詢效率高.8Inmon 資料超市架構Current detailOperationalTransformationOld detailHighly SummarizedLightly Summarized(Data mart)Monthly salesBy product line1981-1992Weekly sales by Sub product line1984-1992

5、Sales detail1990-1991Sales detail1984-1989.9Data Mart建立架構來源一來源二來源三資料倉儲銷售Mart員工Mart財務Mart 資料來源固定、確保資料完好性 資料格式與單位一致，確保跨不同Data Mart分析的正確性 Data Mart可以共享欄位 須花費較多時間來設計Replication.10Data Warehousing架構DataSourcesDataAcquisitionDataWarehouseDataDeliveryDataConsumption operational systems transactional system

6、s Extraction Transformation modeling Loading central repository subject-based data marts metadata operational data store BI decision-support OLAP querying reportingSourceDatabaseOther Sources(files, Exceletc.)ETLMetadataOperational Data StoreServer ApplicationsWeb User ClientsDesktop User Clients us

7、er-facing apps report generation subject-based data cubes data miningData Mart.11Dimensional Data ModelDimensional Data Model是提供user進行查詢分析, 最受歡迎的資料結構, 也是建置OLAP cube的基礎. (OLAP cube是一種高效率的維度模型)Dimensional Data Model通常具備一個 Fact tables一套 Dimension tablesDimension table 和 fact table 組成 “Star Schema通常事實資料

8、表資料量很大，維度資料表資料量很小.12Star Schema*source: Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel.13Star Schema事實 (Facts): 是數值上的衡量(值), 代表了特定的商業部份或活動. 通常存在於事實資料表, 事實表包含了經由其維度加以連結的實實.維度 (Dimensions): DSS資料幾乎都會以與其他資料關聯的角度來查看, 維度是普通認可的分類, 對給定的事實提供了額外的觀點.屬性 (Attributes): 每個維度表都包含

9、了屬性, 維度經由其屬性提供了關於事實的描画性特質.屬性階層 (Attribute hierarchies):屬性階層提供了由上而下的資料組織, 它被用於聚集與資料鑽研(drill-down)/向上捲算(roll-up).維度資料模型與ER模型比較維度資料表(4 tables) v.s. ER資料表(11tables).ER模型轉換成維度模型步驟一、找出需求匯總的資料成為事實資料表Eg:Order資料表中的Quan(數量)與Price_EA(價格)步驟二、根據分析的維度，把其他資料表反正規化Eg: Date+Date_typeTime維度表Eg: Customer+Cstomer_Type+C

10、ity+State Customer 維度表Item+Size+Colors+MaterialsProducts維度表步驟三、把各維度表的主鍵参与事實資料表.16資料立方體 (Data Cube)*source: Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel.17 通常做決策援助分析時，為了利於統計分析，经常將一個根本的Information Object分類成數個Hierarchies。也就是Information Object間存在著一對多的邏輯關係，即Multiple

11、Hierarchies。 Multiple Hierarchies的維度分類方式又可以分成兩種方式(Consolidated Dimensional Hierarchies及SnowFlake Hierarchies)產品總類產品類別產品細項分類產品名稱文具禮品雜誌書籍電腦類商業類小說類文藝小說科幻小說武俠小說天龍八部神雕俠侶倚天屠龍記書店書籍類別小說類別武俠小說Dimension Attribute Hierarchies .18將不同Hierarchies 的Information Objects完成合併於同一個Dimension中。產品編號產品名稱產品總類產品類別產品細類產品類別分類特征

12、： 查詢簡單、速度快需求較多的硬碟儲存空間產品的Dimension TableStar Schema 的Dimention .19類似正規化，將一切類別以獨立的Table來儲存資料，再用PK及FK來維持彼此的關係。產品編號產品名稱產品細類編號(FK)特征：節省硬碟儲存空間，做過正規化，資料不重複存在查詢較複雜產品的Dimension Table產品細類編號產品細類名稱產品類別編號(FK)產品類別編號產品類別名稱產品總類編號(FK)產品總類編號產品總類名稱SnowFlake 的多階層Dimension .20Star Schema與 Snow Flake 比較StarSnowFlake整體資料列

13、(Row)數較少較多所佔硬碟空間大小較大較小設計難易度較容易較困難Table數量較少較多查詢複雜度較簡單較複雜維度搜尋較快較慢支援Bitmapped索引是否.21多維度模型的設計步驟定義OLAP的Data MartFact 的選擇Dimension 的建立Aggregation 的設計.22多維度模型的設計步驟(一)定義OLAP的Data Mart 著重於企業的單一商業行為(select the business process)決定單元資料的精細程度(Declare the granularity of atomic data)決定运用Star Schema 或 SnowFlake決定資料的

14、時間需求.23Fact 的選擇Fact 應與時間的階層相符Fact 應符合一切Dimension的條件的分割資料設計時以最低Level來回應运用者查詢時非預期的需求四種常見的Fact Transaction Fact: 买卖次數SnapShot Fact : 某一特定時間的特殊狀況Line Item Fact：與企業相關的個別項目的一切Meansure條件Event/State Fact : 只觀察事情發生與否多維度模型的設計步驟(二).24Dimension 的建立:要建立一個完好的維度模型，應該考慮三個構面：維度的共同特性、維度階層、維度資料異動。維度的共同特性：維度存在於關連式的Tabl

15、e中，因此包含了FK及援助的屬性。屬性與Fact相關亲密。簡單及有用的文字資訊。解析過的時間、名字、或地址元素。Surrogate Key(代理鍵)，另外新增一個額外的独一鍵欄位。Degenerate Dimension(退化維度)：存在fact中，與實際dim並無關連 (如訂單編號)Dimension Table特性：包含代理鍵的主鍵跟fact Table有一對多的關係至少包含一個決策因子包含Multi-Level的維度階層欄位包含隨時間變化的資料記錄欄多維度模型的設計步驟(三).25Aggregation 的設計利用預先計算好的加總來提高分析的速度。因為Aggregation可以在提交查詢

16、前，就先準備好以改善查詢的反應時間，因此可以降低擷取資料時，系統動態計算結果所呵斥的效能負荷。但是必須留意：動態建立Aggregation 或在資料載入階段時建立AggregationAggregation可以儲存在資料倉儲中以便重複运用，或是在做每一次查詢時動態的建立起來。以儲存空間與處理時間為考慮要素多維度模型的設計步驟(四).26Data Cell 與 Aggregation Cell銷售數量硬體軟體台北100150高雄250100銷售數量硬體軟體電腦產品台北100150250高雄250100350台灣350250600Data Cells : 4Data Cells : 4Aggreg

17、ation Cells : 5為了加快終端決策者获得資料的反應時間，必須利用儲存空間將Aggregation資料事先算好並儲存起來。.27Cube 範例.28二 三 四時間季)DimensionsIBMHPBellAcerCompaq台北台中高雄DimensionMeansure銷售數量高雄地區第四季HP的銷售量Cube 範例( 銷售分析架構).29OLAP Cube儲存方式M (multidimensional) OLAP是將多維度資料及彙總資料Aggregation直接存放在特定的資料結構中如：CUBE，藉由事先運算及彙總存放於CUBE中使MOLAP的儲存可提供最快速的查詢回應時間 R (

18、relational) OLAP關聯式線上即時分析系統，藉由維度 (Dimensions) 直接的串聯關係存及Aggregation存放於關聯式資料庫(RDBMS)，或說資料倉儲的資料庫中，提供有效的資料存放，不再另外建一CUBE。 H (hybrid) OLAPHOLAP combines MOLAP and ROLAP, it stores aggregation in a multidimensional structure without the copy of source dataMOLAPROLAP優查詢快速 省空間靈活性大 沒有擴充的問題缺浪費空間靈活性低 不易擴充建置Cube

19、時間長速度較慢，每次查詢皆從資料庫中重新抓資料.30OLAP functionality Uses relational DB query toolsExtensions to RDBMSMultidimensional data schema support Data access language and query performance optimized for multidimensional dataSupport for very large databases (VLDBs)Relational OLAP (ROLAP).31Typical ROLAP Client/Serv

20、er Architecture*source: Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel.32OLAP functionality to multidimensional databases (MDBMS)Stored data in multidimensional data cubeN-dimensional cubes called hypercubesCube cache memory speeds processingMultidimensional OLAP

21、(MOLAP).33MOLAP Client/Server Architecture*source: Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel.34SQL Server 2000SQL Server 2005SQL Server 2000 Reporting ServicesBI High availabilityScalabilityAd hoc ReportingEnhanced SecurityDeveloper productivityFirst to inclu

22、de Enterprise ETL and Deep Data MiningFirst to include Enterprise Reporting solutionRich Report Design, Deployment & ManagementReal-Time OLAPFirst RDBMS to include Data MiningComprehensive Dimension Types64Bit Support Lowest TCO Automatic Tuning Reliability and Security Integrated Business Intellige

23、nceCross-release objectivesSQL Server 7.0First RDBMS to integrate OLAP and ETLExcel IntegrationMulti-vendor supportClustering out of the box“OLAP for Everyone1st Generation2nd Generation3rd Generation微軟商業智慧.硬體配置圖NCROracleSQLDWOLAP CubeWeb ServerSource Data12335.36Unified Dimensional ModelPro-active

24、cachingAdvanced Business IntelligenceKPI/PerspectivesCustom/Limited Aggregations and Semi-Additive MeasuresWeb servicesData Mining in the platformIntegrated Developer ToolsFailover ClusteringDecision TreesClusteringTime SeriesSequence ClusteringAssociationNave BayesNeural NetIntroduced in SQL Server

25、 2000SSAS特征plusLogistic RegressionLinear RegressionText Mining.SSAS特征Unified Dimensional Model不需轉成星狀架構，直接由RDB資料對應簡化資料整合任务多維度物件擴充改用屬性作為為杜物件的底層架構多國語言轉換同一個報表可以用多國語言呈現提升商業智慧分析才干提供不同等級的快取方式 (Proactive caching)多種資料探勘演算法援助.38SQL Server 2005 Analysis Services Data MiningNew AlgorithmsTwo enhanced Eight new

26、algorithmsNew VisualizationsEnhanced tools custom visualizationsDeep IntegrationOLAP, DTS, and Reporting Integration.NET programming modelCompletely extensible framework.39Value of Data Mining8 new algorithms, 10 in totalGraphical tools/wizards12 embeddable viewersSQL Server 2005 makes it easierTigh

27、tly integrated with AS, DTS, ReportingIntegration with Web/Office appsSQL Server 2005OLAP Reports (Adhoc)Reports (Static)Data MiningBusiness KnowledgeEasy DifficultUsabilityRelative Business Value.40Complete Set of AlgorithmsDecision TreesClusteringTime SeriesSequence ClusteringAssociationNave BayesNeural NetIntroduced in SQL Server 2000LogisticRegressionLinear RegressionText Mining.41Putting Data Mining to Wo