《商務智能解決方案》由會員分享，可在線閱讀，更多相關《商務智能解決方案（61頁珍藏版）》請在裝配圖網上搜索。

1、Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,*,Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,Sybase/Business Intelligence,SYBASE 數據倉庫/商務智能解決方案,魏健,商務智能咨詢顧問,SY

2、BASE 軟件(中國)有限公司,議程,數據倉庫解決方案概述,數據倉庫設計工具,數據倉庫引擎 Sybase Adaptive Server IQ Multiplex,“數據倉庫是在企業(yè)管理和決策中,面向主題的,集成的, 與時間相關的,和不可修改的數據集合”,Bill Inmon,數據倉庫定義,OLTP系統,5-10 年,過去,詳細數據,當前,詳細數據,輕度,匯總數據,高度,匯總數據,數據集市,用戶分析,網絡資源分析,數據倉庫,數據倉庫/決策分析系統,數據倉庫是完全不同的數據庫系統,RDBMS,Sybase,SAP/ERP,VSAM,EXCEL,操作(業(yè)務)系統特性,事務處理性能是第一位的,支持日

3、常的業(yè)務,事務驅動,面向應用,數據是當前的并在不斷變化,存儲詳細數據 (每一個事件或事務),針對快速預定義的事務優(yōu)化設計,可預見的使用模式,支持辦事人員或行政人員,數據倉庫應用系統特點,支持長遠的業(yè)務戰(zhàn)略決策,分析驅動,面向主題,數據是歷史的,數據反映某個時間點或一段時間,數據是靜態(tài)的，除數據刷新外,數據是匯總的,優(yōu)化是針對查詢而不是更新,支持管理人員和執(zhí)行主管人員,數據倉庫解決方案解決從數據庫中獲取信息的問題。,INFORMATION,信 息,信 息,INFORMATION,什么是數據倉庫解決方案？,應用價值,時間,1.,日常報表,2. 即席查詢,3. 分析,4. 數據挖掘,專題應用,1 2

4、 3 4,數據倉庫應用類型,數據倉庫應用,數據倉庫系統體系架構,Relational,Package,Legacy,External,source,Data,Clean,Tool,Source Data,Data,Staging,WareHouse,Admin.,Tools,Enterprise,Data,Warehouse,Data Extraction,Transformation,and load,Datamart,Datamart,Enterprise/,Central,Data,Warehouse,RDBMS,ROLAP,RDBMS,Dimension Modeling,Confor

5、med dimension&fact,Including atomic&aggregate,Architected,Datamarts,Central,Metadata,Data,Modeling,Tool,End-User,Tool,End-User,Tool,MDB,End-User,Tool,End-User,Tool,Local Metadata,Local Metadata,數據倉庫/商務智能應用成功的關鍵,做什么，怎么做？,數據倉庫性能,Sybase & Partner 專業(yè)服務,數據倉庫顧問咨詢,Industry Warehouse Studio,Sybase IWS 方法學,E

6、R Design Tool,Impact Analysis,Metadata Management,Sybase Industry Warehouse Studio打包的,數據倉庫基礎平臺概述,業(yè)務,模型,物理,模式,元數據,ETL 工具,例子,報表,算法,ETL Tool Metadata Exchange,Smart ETL Maps (Future),SQL Templates,Cognos,Business Objects,MicroStrategy,Business Models focused on Key Industry Events,Enterprise-wide, Star

7、 Schema-based design,IWS產品介紹,TABLE,TABLE,TABLE,TABLE,TABLE,Industry-specific,Data Models,Data,Warehouse,“Open RDBMS*”,ORACLE, IBM, MICROSOFT, NCR, SYBASE, etc.,BI Partners,Sample Applications,Analytical CRM,Sales Analysis,Customer Profiling,Campaign Analysis,Customer Care Analysis,Loyalty Analysis,B

8、usiness,Performance,Analysis,Industry Specific,Sample Data,General -,Representative,Systems,Integrators Guide,Project Plans,Implementation,Protocol,e.g. Informatica,ETL Tool,Warehouse Architect,Multi-Dimensional Design Tool,SQL Sample,Reports,Warehouse Control Center,Meta Data Management,客戶構成,分析,營銷活

9、動,分析,客戶興趣,分析,忠誠度,分析,銷售,分析,行業(yè)相關的,經營業(yè)績分析,收益率,分析,EVT_TYP_ID = EVT_TYP_ID,PRD_ID = PRD_ID,ENTY_ID = ENTY_ID,ENTY_ID = EMP_ID,GEO_ID = GEO_ID,LANGUAGE_ID = LANGUAGE_ID,PRODUCT_ID = PRODUCT_ID,DEMO_ID = DEMO_ID,ENTY_ID = V_E_ENTY_ID,ENTY_ID = ENTY_ID,ENTY_ID = F_C_ENTY_ID,COR_EVT_TYP_ID = COR_EVT_TYP_ID,C

10、OR_RPT_STRC_ID = COR_RPT_STRC_ID,ENTY_ID = CNTC_RSOL_EMP_ID,GEO_ID = GEO_ID,FNCL_SCOR_ID = FNCL_SCOR_ID,MEASURE_UNIT_ID = MEASURE_UNIT_ID,COR_EVT_TXN_ID = COR_EVT_TXN_ID,LANGUAGE_ID = LANGUAGE_ID,COR_EVT_TXN_SEQ_NB = COR_EVT_TXN_SEQ_NB,PN_BHVR_SCOR_ID = PN_BHVR_SCOR_ID,PRODUCT_ID = PRODUCT_ID,DEMO_I

11、D = DEMO_ID,ENTY_ID = ENTY_ID,FNCL_SCOR_ID = FNCL_SCOR_ID,MEASURE_UNIT_ID = MEASURE_UNIT_ID,DEMO_ID = DEMO_ID,PRODUCT_ID = PRODUCT_ID,PN_BHVR_SCOR_ID = PN_BHVR_SCOR_ID,LANGUAGE_ID = LANGUAGE_ID,FNCL_SCORES_ID = FNCL_SCOR_ID,MEASURE_UNIT_ID = D_M_MEASURE_UNIT_ID,MEASURE_UNIT_ID = MEASURE_UNIT_ID,GEO_

12、ID = GEO_ID,COR_RPT_STRC_ID = COR_RPT_STRC_ID,EVT_TYP_ID = COR_EVT_TYP_ID,ENTY_ID = F_C_ENTY_ID,GEO_ID = GEO_ID,LANGUAGE_ID = LANGUAGE_ID,EVT_TYP_ID = EVT_TYP_ID,DV_HR_EVT_TYPE,EVT_TXN_ID,INTEGER,EVT_TYP_ID,INTEGER,EVT_TYP_SHRT_NM,CHAR,EVT_TYP_FULL_NM,char,EVT_TYP_CAT_SHRT_N,CHAR,EVT_TYP_CAT_FULL_N,

13、char,F_HR_EVT,V_E_ENTY_ID,INTEGER,V_E2_ENTY_ID,INTEGER,EVT_DT_PRD_ID,INTEGER,ADMIN,INTEGER,EVT_EMP_ID,INTEGER,EVT_EMP_DEMO,INTEGER,EVT_ADMIN_DEMO,INTEGER,CORE_EXT_ID,INTEGER,CORE_RPTG_STRUC,INTEGER,GEO_ID,INTEGER,MU_ID,INTEGER,FIN_SCORE_ID,INTEGER,LANGUAGE_ID,INTEGER,PB_SCORE_ID,INTEGER,F_C_ENTY_ID,

14、INTEGER,PRODUCT_ID,INTEGER,DEMO_ID,INTEGER,EMP_ID,INTEGER,CDEX_SEQ_NO,INTEGER,QTY,integer,F_CORE_EVT,COR_EVT_TXN_ID,INTEGER,COR_EVT_TYP_ID,INTEGER,D_M_MEASURE_UNIT_ID,INTEGER,COR_RPT_STRC_ID,INTEGER,GEO_ID,INTEGER,MEASURE_UNIT_ID,INTEGER,FNCL_SCOR_ID,INTEGER,LANGUAGE_ID,INTEGER,PN_BHVR_SCOR_ID,INTEG

15、ER,PRODUCT_ID,INTEGER,DEMO_ID,INTEGER,ENTY_ID,INTEGER,V_E_ENTY_ID,INTEGER,COR_EVT_TXN_SEQ_NB,NUMBER,PRD_ID,INTEGER,AMOUNT,NUMBER,D_CORE_EVT_TYP,EVT_TYP_ID,INTEGER,EVT_TYP_SHRT_NAM,VARCHAR(15),EVT_TYP_LONG_NAM,VARCHAR(35),EVT_TYP_SUBTYP_NAM,VARCHAR(15),D_CORE_RPT_STRC,COR_RPT_STRC_ID,INTEGER,HOLDING_

16、COMPANY,VARCHAR(35),ORG_TYPE,VARCHAR(20),ORG_NAME,VARCHAR(35),REGION,VARCHAR(20),SALES_TEAM_TYPE,VARCHAR(15),SALES_TEAM,VARCHAR(15),SALES_PERSON_NAME,char,SALES_PERSON_GRADE,CHAR,SALES_PERSON_TYPE,CHAR,CHNL_CATEGORY1,char(18),CHNL_TYPE,CHAR,CHNL_SUBCAT,CHAR,CHNL_NAME,char,CHNL_CEASED_TRD_DT,DATE,CHN

17、L_ENTY_ID,INTEGER,CHNL_CITY,VARCHAR(20),CHNL_POSTCODE,VARCHAR(20),BEGIN_DATE_PRD_ID,INTEGER,END_DATE_PRD_ID,INTEGER,D_GEOGRAPHY,GEO_ID,INTEGER,ALL_ENTRIES,CHAR,POSTAL_CODE,CHAR VARYING(15),CITY,char,POSTAL_CD_PFX,char(3),HZRD_WTHR_AREA,CHAR,HZD_WTHR_TYPE,CHAR,DMA_CODE,CHAR,SMSA_CODE,CHAR,ST_PROV_ARE

18、A,CHAR,TV_REGION,CHAR,NTL_RADIO_AREA,CHAR,LCL_RADIO_AREA,CHAR,REGION,CHAR,COUNTRY,char(3),CONTINENTY_ABBR,char(3),GEO_SUB_CNTNT_ABBR,char(3),SMRY_EFF_DT,INTEGER,SMRY_END_DT,INTEGER,PRISN_ADRS_IND,CHAR,D_MSR_UNIT,MEASURE_UNIT_ID,INTEGER,SHRT_DESC,char(6),LONG_DESC,char(20),D_DEMOGRAPHICS,DEMO_ID,INTE

19、GER,ALL_ENTRIES,CHAR,INCOME_BAND,VARCHAR(50),AGE_BAND,VARCHAR(50),GNDR,CHAR,MRTL_STAT,CHAR,HIGH_VALUE_INDICAT,CHAR,ACMDTN_CTGRY,CHAR,NBR_IN_HH_BAND,VARCHAR(50),CHLD_AT_HOME_BAND,VARCHAR(50),SIZE_CLS,CHAR,LEGAL_ORG_TYPE,CHAR,NBR_EMP_BAND,VARCHAR(50),SECTOR_CLS,CHAR,MAIL_PRMSN_IND,CHAR,TELMKT_PRMSN_IN

20、D,CHAR,D_FNCL_SCOR,FNCL_SCORES_ID,INTEGER,INTERNAL_FNCL_SCOR,VARCHAR(50),EXPERIAN_SCOR_BAND,VARCHAR(50),SCOR_N_BAND,VARCHAR(50),PRFT_IND_BAND,VARCHAR(50),DEBT_INCOME_RATIO,NUMBER,D_LANGUAGE,LANGUAGE_ID,INTEGER,ISO_LANG_CODE,CHAR,ISO_LANG_NAME,char,LANG_GROUP,VARCHAR(20),D_PN_BHVR_SCOR,PN_BHVR_SCOR_I

21、D,INTEGER,SCORE1_BAND,VARCHAR(20),SCORE_N_BAND,VARCHAR(20),D_PRODUCT,PRODUCT_ID,INTEGER,ENTY_ID,INTEGER,PRODUCT_LINE,CHAR,PRODUCT_GROUP,CHAR,PRODUCT_CODE,CHAR,PRODUCT_NAME,CHAR,PD_VARIANT_CODE,CHAR,PRODUCT_VARIANT,VARCHAR(35),GRP_INDV_IND,CHAR,PD_START_PRD_ID,INTEGER,PD_END_PRD_ID,INTEGER,F_SALES_EV

22、ENT,EVT_TXN_ID,INTEGER,EVT_TYP_ID,INTEGER,RPT_STRC_ID,INTEGER,MEASURE_UNIT_ID,INTEGER,FNCL_SCOR_ID,INTEGER,PN_BHVR_SCOR_ID,INTEGER,ENTY_ID,INTEGER,EMP_ID,INTEGER,EVT_TXN_SEQ_NBR,INTEGER,F_CUS_CNTC_EVT,V_E_ENTY_ID,INTEGER,CUS_CNTC_ID,INTEGER,D_C_CTCT_RSOL_ID,INTEGER,LGCY_SYS_CUS_CNTC,INTEGER,CUS_CNTC

23、_REF,char,CUS_CNTC_EVT_ID,INTEGER,F_C_ENTY_ID,INTEGER,CUS_STSF_RT_ID,INTEGER,CNTC_INIT_DT_ID,INTEGER,HOUR_ID,INTEGER,MINUTE_ID,INTEGER,INIT_CNTC_EMP,char,COR_EVT_TXN_ID,INTEGER,COR_EVT_TYP_ID,INTEGER,COR_RPT_STRC_ID,INTEGER,GEO_ID,INTEGER,MEASURE_UNIT_ID,INTEGER,FNCL_SCOR_ID,INTEGER,LANGUAGE_ID,INTE

24、GER,PN_BHVR_SCOR_ID,INTEGER,PRODUCT_ID,INTEGER,DEMO_ID,INTEGER,CNTC_RSOL_EMP_ID,INTEGER,CUS_ID,INTEGER,SRSNS_CUS_CO_ID,INTEGER,DV_EMP,ENTY_ID,INTEGER,RPT_STRC_ID,INTEGER,GEO_ID,INTEGER,ADR_ID,INTEGER,EMP_DEMO_ID,INTEGER,EMP_NAME_PFX,CHAR,EMP_SNAME,VARCHAR(15),EMP_FNAME,VARCHAR(15),EMP_MNAME,VARCHAR(

25、15),EMP_NAME_SFX,CHAR,EMP_NTL_INS_NBR,CHAR,EMP_HOME_TEL_NBR,CHAR,EMP_PRIM_FAX_NBR,CHAR,EMP_EMAIL_ID,INTEGER,EMP_DOB,DATE,EMP_GNDR,CHAR,EMP_MRTL_STAT,CHAR,EMP_LIFE_STAT,CHAR,EMP_PREF_LANG,VARCHAR(20),F_CPGN_CNTC_EVT,CCE_ID,INTEGER,PROMO_EPSD_ID,INTEGER,ENTY_ID,INTEGER,CNTC_PRD_ID,integer,CCH_COUNT,IN

26、TEGER,CORE_EVT_TYPE_ID,INTEGER,COR_RPTG_STRUCT_ID,INTEGER,GEO_ID,INTEGER,MU_ID,INTEGER,FINANCIAL_SCORE_ID,INTEGER,LANGUAGE_ID,INTEGER,PB_SCORE_ID,INTEGER,PRODUCT_ID,INTEGER,DEMO_ID,INTEGER,EMP_ID,INTEGER,COR_EVT_TX_SEQ_NO,SMALLINT,TRGT_GRP,char(3),CORE_EVENTY_TYPE_ID,INTEGER,CNTCT_CNTRL_GRP_IN,CHAR,

27、CCE_RESULT,CHAR,P_PSYCH_ID,INTEGER,AFFILIATION_ID,int,PA_ID,INTEGER,CC_COMM_EVT_AMT,decimal(10,2),D_TIME_PERIOD,PRD_ID,INTEGER,DT_NA,char(4),DATE,DATE,DAY_NAME,char(8),DAY_ABR,char(3),DAY_IN_WEEK,SMALLINT,DAY_IN_MONTH,SMALLINT,DAY_IN_YEAR,SMALLINT,WEEK_IN_MONTH,SMALLINT,WEEK_IN_YEAR,SMALLINT,CLNT_SV

28、C_WK_IN_YR,char(18),MONTH_NAME,char(10),MONTH_ABR,char(3),MONTH_IN_YEAR,SMALLINT,CALENDAR_QTR,char(6),MONTH_IN_QTR,SMALLINT,WEEK_IN_QTR,SMALLINT,DAY_IN_QTR,SMALLINT,FINANCIAL_QTR,char(6),COMPETITOR_FSCL_YR,char(6),MONTH_IN_FNCL_QTR,SMALLINT,WEEK_IN_FNCL_QTR,SMALLINT,DAY_IN_FNCL_QTR,SMALLINT,SEMI_YEA

29、RLY,SMALLINT,YEAR_NAME,char(18),YEAR_ABR,char(4),SEASON_NAME,char(18),SEASON_ABR,char(6),NBR_DAYS_SINCE_90,integer,HOLIDAY_IND,CHAR,XMAS_HLDY_IND,CHAR,EASTER_HLDY_IND,CHAR,D_CPGN_COM_EVT_TYP,EVT_TYP_ID,INTEGER,CPGN_COMM_DESC,CHAR,分析型,CRM,經營業(yè)績管理,Sybase Industry Warehouse Studio,分析型應用框架,Time,資源,搜集需求,理

30、解業(yè)務線,設計模式,ETL 模板,構造分析需求,實施,測試,用戶反饋,精練,測試,第,二代倉庫,典型的數據倉庫,項目從這里開始,Sybase IWS 提供的時間上的價值,快速啟動數據倉庫項目,搜集需求,理解業(yè)務線,設計模式,ETL 模板,構造分析查詢,實施,測試,第,一代倉庫,Sybase IWS,從這里開始,IWS,節(jié)省,3 到 6,個月,更多的價值 =,更快地訪問信息,Sybase Industry Warehouse StudioValue Proposition 回顧,預先建立的業(yè)務和物理模型優(yōu)化了項目進度的安排和加快了對數據的訪問,基于經過驗證的實施經驗和行業(yè)經驗,設計和方法論是可擴

31、展/可定制的,安全,企業(yè)范圍,數據庫獨立,面向行業(yè),集成的模型和基礎平臺,靈巧,節(jié)省資源 一半的投入,節(jié)省時間 更快的實施,節(jié)省資金 降低成本,節(jié)省,數據倉庫系統體系架構,Relational,Package,Legacy,External,source,Data,Clean,Tool,Source Data,Data,Staging,WareHouse,Admin.,Tools,Enterprise,Data,Warehouse,Data Extraction,Transformation,and load,Datamart,Datamart,Enterprise/,Central,Data

32、,Warehouse,RDBMS,ROLAP,RDBMS,Dimension Modeling,Conformed dimension&fact,Including atomic&aggregate,Architected,Datamarts,Central,Metadata,Data,Modeling,Tool,End-User,Tool,End-User,Tool,MDB,End-User,Tool,End-User,Tool,Local Metadata,Local Metadata,Adaptive Server IQ Multiplex,是專門為滿足數據倉庫和商務智能設計的高性能的關

33、系數據庫系統。,IQ Multiplex,的主要特點是：,高可擴展性,支持數以千計的并發(fā)用戶存取,TB,級的數據。,突破性的速度,閃電般的查詢速度，比傳統,RDBMS,快,10 100,倍以上。,無限的靈活性,支持任意類型的即席查詢。,最低的擁有總成本,高效的數據壓縮存儲，達到,30% 60%,；簡單的維護和管理。,集成的主要產品,Design,Warehouse Architect,Manage,Sybase ASIQM,Integrate,Informatica,Enterprise Connect,Replication Server,PowerMart,Visualize,Bo、Bri

34、o,Cognos,SPSS,Administer,Warehouse Control Center,Warehouse,Control,Centre,Sybase數據倉庫相關產品集的構成,Relational,Package,Legacy,External,source,Data,Clean,Tool,Source Data,Data,Staging,WareHouse,Admin.,Tools,Enterprise,Data,Warehouse,Data Extraction,Transformation,and load,Datamart,Datamart,Enterprise/,Cent

35、ral,Data,Warehouse,RDBMS,ROLAP,RDBMS,RDBMS, Star Schema,Architected,Datamarts,Central,Metadata,Data,Modeling,Tool,End-User,Tool,End-User,Tool,MDB,End-User,Tool,End-User,Tool,Local Metadata,Local Metadata,PowerCenter,PowerMart,Sybase IQM,Sybase IQM,Brio/BO,PowerMart,Warehouse,Architect,WCC,Cognos,設計:

36、 成功的關鍵,數據庫的設計對數據倉庫系統的整體性能、裝載和,建立索引的時間以及數據量的增長等的影響超過,任何其它方面。,數據倉庫設計,在支持分析和決策的查詢環(huán)境中，使業(yè)務用戶可以,訪問，理解和利用數據,以業(yè)務用戶理解和運用信息的方式組織數據,可預見的查詢方式,基于時間的,匯總的數據,向下/上的鉆?。―rill-down / drill-up）,多維模型設計,傳統的數據建模方法(如ER,模型)可能非常復雜且不易理解,按照最終用戶的想法定義信息 (以查詢?yōu)橹行慕?,Star(星型), Snowflake(雪花型)，Constellation(星座型)，Snowstorm(雪暴型),Facts(,

37、事實)： 可度量數據，如 數量、價格,Dimensions(維)：用于分類Fact的詳細數據,Grocery Transaction,Store Number,Transaction Date,Customer,Product,Quantity,Amount,Customer,Customer,From Date,To Date,First Name,Last Name,Address 1,Address 2,Address 3,City,State,Country,Postal Code,Time,Transaction Date,Store,Store Number,Store Name,

38、City,State,Country,Telephone,Product,Product,Description,Category,Fact Table,Dimension,Tables,Dimension,Tables,多維模型: 星型模式,Grocery Transaction,Store Number,Transaction Date,Customer,Product,Quantity,Amount,Customer,Customer,First Name,Last Name,Address 1,Address 2,Address 3,City,State,Country,Postal

39、Code,Customer Category,Time,Transaction Date,Store,Store Number,Store Name,City,State,Country,Telephone,Region,Product,Product,Description,Category,Product Category,Product Category,Description,Region,Region,Description,Sales Period,Period Identifier,Sales Period,From Date,To Date,Customer Category,

40、Category,Customer Category,為了避免數據冗余, 用多張表來描述一個復雜維,在星型模式的基礎上, 構造維表的多層結構,多維模型: 雪花模式,Grocery Transaction,Store Number,Transaction Date,Customer,Product,Purchase Quantity,Amount,Customer,Customer,First Name,Last Name,Address 1,Address 2,Address 3,City,State,Country,Postal Code,Customer Category,Time,Tra

41、nsaction Date,Store,Store Number,Store Name,City,State,Country,Telephone,Region,Product,Product,Description,Category,Product Line,Sales Period,Period Identifier,Sales Period,From Date,To Date,Customer Category,Category,Customer Category,Product Purchases,Product,Purchase Date,Supplying Vendor,Purcha

42、se Order,Unit Quantity,Purchase Cost,Vendor,Vendor,Vendor Name,Address 1,Address 2,Address 3,City,State,Country,Postal Code,Product Inventory,Product,Warehouse Location,Quantity On Hand,Quantity Back Ordered,Warehouse,Warehouse,Address 1,Address 2,Address 3,City,State,Country,Postal Code,具有多個事實表,多維模

43、型: 星座模式,Grocery Transaction,Store Number,Transaction Date,Customer,Product,Purchase Quantity,Amount,Customer,Customer,First Name,Last Name,Address 1,Address 2,Address 3,City,State,Country,Postal Code,Customer Category,Time,Transaction Date,Store,Store Number,Store Name,City,State,Country,Telephone,R

44、egion,Product,Product,Description,Category,Product Line,Product Category,Product Category,Description,Region,Region,Description,Sales Period,Period Identifier,Sales Period,From Date,To Date,Customer Category,Category,Customer Category,Promotion Period,Promotion Id,Promotion,From Date,To Date,Product

45、 Line,Product Line ID,Description,Product Purchases,Product,Purchase Date,Supplying Vendor,Purchase Order,Unit Quantity,Purchase Cost,Vendor,Vendor,Vendor Name,Address 1,Address 2,Address 3,City,State,Country,Postal Code,Product Inventory,Product,Warehouse Location,Quantity On Hand,Quantity Back Ord

46、ered,Warehouse,Warehouse,Address 1,Address 2,Address 3,City,State,Country,Postal Code,具有多個事實表與多層維表,多維模型: 雪暴模式,數據模型中的事實和維度,事實和維的概念對應于:,數據倉庫數據庫中的數據模型對象,星型模式（Star schema）,DSS / OLAP 系統中的數據模型對象,多維模型（Multidimensional model）,Sales fact,Sales measures,Time dimension,Attributes of the time dimension,星型模式-St

47、ar Schema,Sales Cube,Sales measures,(Metrics),Time dimension,Attributes of thetime dimension,多維模型-Multidimensional Model,數據倉庫設計工具WarehouseArchitect,為數據倉庫的設計提供三大功能：,多維建模,度量、維、屬性,事實表，維表,維層次表，事實層次表,設計向導,聚合（Aggregation Wizard）,分片（Partitioning Wizard）,逆向工程數據源,優(yōu)化代碼生成,目標數據倉庫引擎（IQM，RDBMS),OLAP,分析環(huán)境,Time ide

48、ntifier = Time identifier,Product identifier = Product identifier,Customer identifier = Customer identifier,Store identifier = Store identifier,Customer,Customer identifier,double,Customer name,char(30),Sales Fact,Product identifier,double,Time identifier,double,Customer identifier,double,Store iden

49、tifier,double,Sales total,real,Profits,real,Store,Store identifier,double,Store name,char(50),Time,Time identifier,double,Date,timestamp,Month,char(50),Quarter,double,Year,double,Product,Product identifier,double,Product description,char(80),WarehouseArchitect,WarehouseArchitect,Data Warehouse or Da

50、ta Mart,Database,Operational Source,OLAP,Engine,Interface,External Objects,Decision Support / OLAP Model (WA Multidimensional Hierarchy),Dimensional,Analysis,Transformation,Relational,and/or,Dimensional,Analysis,Data Warehouse Model (WAM),WarehouseArchitect的支持范圍,數據倉庫設計-小結,WarehouseArchitect,對數據倉庫設計過

51、程的每一步都提供支持：,數據源中的元數據導入。,設計和優(yōu)化數據倉庫的數據模型（星型模式/多維模型）。,與抽取、轉換工具對接，實施數據移動。,基于數據倉庫模型，為前端DSS/OLAP,工具生成所需的數據立方體。,為設計過程的每一步生成文檔和報告。,數據存儲、管理,挑戰(zhàn),數據規(guī)模,查詢性能,裝載速度,易于管理,存取訪問,成功的關鍵,快速，高效數據存儲技術,出色的查詢性能 - 特殊的索引,技術，并行查詢,可伸縮性 - GB 到 TB 級,易于管理 - 方便，靈活，GUI,存取訪問 - 數據隨時可用,數據管理,解決的方案,通用的關系數據庫系統,專門的數據倉庫服務器,Sybase IQM,專門為數據倉庫

52、/數據集市設計的關系型數據庫,專門針對OLAP/DSS而優(yōu)化的索引和查詢處理技術,Adaptive Server IQM,數據存儲： Adaptive Server IQM,垂直存儲技術(Vertical Partitioning),無處不索引(Index EVERYWHERE),專利的Bit Wise索引技術跨越Bitmap的限制,多種索引類型：FP,LF,HNG,HG,CMP,WD,低級數的限制從100擴充到1000,數據壓縮(通常達到原始數據的 70 - 75%),預連接的索引提供額外的顯著提高性能手段(Join Index),支持任意設計模式,星型、雪花、雪暴、星座模式,普通關系模式,

53、支持任意加載方式,文件、內部數據、外部數據庫直接加載,開放的接口,Index,傳統RDBMS,Relational Table,Typical RDBMS,數據按行存儲,數據與索引分開存放,很少的索引類型 -,B-,樹,普通關系數據庫為,OLTP,系統進行優(yōu)化,B-tree Index best for retrieving one row at a time,計算“NY”,州,A類商店的,平均銷售額,當表的記錄數從幾萬條變?yōu)榍f和上億條時，,傳統RDBMS技術面對的問題:,表掃描的性能極端低下,冗余設計代價高昂、查詢讀取的無效字段過多,低級數類型數據上索引的失效,普通索引加載和空間代價，造成

54、不能任意建造,即席查詢的SQL順序對性能有顯著影響,數值型比較和運算，無恰當手段加速處理,傳統RDBMS不適合數據倉庫,IQM的特殊存儲方式-垂直存儲（按列存儲）,Sybase IQM:,數據是按列存儲的，而不是按行存儲,好處:,只存取查詢所需的數據,數據類型是一致的，因而可以很容易被壓縮,數據庫易于修改和管理,Sybase IQM:,只讀完成查詢所 涉及到的列,計算在紐約的“A”,類商店,的平均銷售額,好處:,無須使用其他的技術，Sybase IQM就可以減少I/O 超過 90%,IQM的特殊存儲方式-垂直存儲（按列存儲）,“How many MALES are NOT INSURED in

55、 CALIFORNIA?,Gender,M,M,F,M,M,-,800 Bytes/Row,10M,ROWS,State,NYCACTMA,CA,-,RDBMS,Insured,YYN,Y,N,MYCA,MNCA,FYNY,MNCA,1,2,4,3,Gender,Insured,State,+,+,1,1,0,1,1,1,0,1,0,1,0,1,10M,Bits,10M Bits x 3 col / 8,16K Page,=,235,I/Os,800 Bytes x 10M,16K Page,=,500,000,I/Os,基本上只能使用表掃描,查詢過程讀取了太多的無效數據,IQM,Exampl

56、e: I/O 的明顯減少,IQM的索引特點,索引即是數據,沒有索引和數據的分別,任何一列可以建立多個索引,系統保證至少會存在一個索引（FP）,索引的選擇和設計主要基于：,數據的級數（離散值的個數）,在查詢中的使用方式,和SQL語句的順序無關,索引的種類,Fast Projection(FP),數據壓縮存儲,根據數據的特點會自動使用三種方式中的一種,Low Fast (LF),Bit map 索引,High Non Group (HNG),Bit-wise 索引,High Group (HG),G-Array (包括一個改進的B-tree),Compare(CMP),列比較,Word(WD),字

57、符串查找,FP索引有三種內部形態(tài),根據數據級數特征，,IQ自動選擇 FP中最合適的一種表現形式,If 級數 65536,FP index,If 級數 256,FFP Index (Fast-Fast Projection),If 級數,Between 256 and 65536,FFFP Index ( Fast-Fast-Fast Projection),FP形式1：FP Index,該列的級數超過,65536,原始數據在磁盤上壓縮存儲,alpha,alpha,beta,gamma,beta,beta,FP形式2：FFP Index,列級數,7,高級數Bit-Wise索引：,HNG,Sale

58、s in binary form,8 bit,4 bit,2 bit,1 bit,0,1,1,0,1,0,0,1,0,1,0,1,1,0,1,1,1,0,0,1,0,0,1,1,0,1,1,1,1,1,0,0,Sales in HNG form,8 bit,4 bit,2 bit,1 bit,0,1,1,0,1,0,0,1,0,1,0,1,1,0,1,1,1,0,0,1,0,0,1,1,0,1,1,1,1,1,0,0,高基數分類索引：HG,在Bit-Wise的基礎上增加一個B-Tree，并保證樹在加載時不會重建,最佳使用場合：,多表的連結查詢Joins,Select Distinct, Cou

59、nt Distinct,Group By，Order by,高級數分類索引：HG,在任何一塊充滿后，指針被轉化,成一個bitmap塊,B-Tree,Index,a,b,c,1,2,4,1011010101001,1010001001001,0010100101010,ptr,加載速度更快；,在數據平衡失去后，更好的位圖,優(yōu)化方法；,每減少1,TB,數據可以節(jié)省50至100萬美元的硬件投資,AS IQM 的數據壓縮與傳統數據庫的數據膨脹,5 to 10,Times,the,Cost,of IQ-M,1TB數據加載到不同數據倉庫引擎后占用的空間（索引+數據）,IQ Multiplex并發(fā)支持的無限

60、性,IQ （Multiplex） functions,IQ,Compaq,Server,IQ VLM,Unix/NT,VLM Alpha,Server,IQ VLM,Unix,Compaq,Server,IQ VLM,Unix/NT,Compaq,Server,IQ VLM,Unix/NT,IQ,沒有數據的重新分布,沒有模式改變的維護工作,系統同步所需的I/O最小,(為其他并行系統的1/10),Compaq,Server,IQ VLM,Unix/NT,Compaq,Server,IQ VLM,Unix/NT,Compaq,Server,IQ VLM,Unix/NT,IQ,156 CPUs, 160 GB of RAM,48.2 TB of data stored in,22TB,of storage,:,disk/data=0.46,Traditional DBMS (I.e DB2, Teradata) need,300 TB,(disk/data=10),Loading speed: 5-20 Billion records per day,Sun-IQM Reference A