商務(wù)智能解決方案

《商務(wù)智能解決方案》由會(huì)員分享，可在線閱讀，更多相關(guān)《商務(wù)智能解決方案（61頁珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

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 數(shù)據(jù)倉庫/商務(wù)智能解決方案

2、,魏健,商務(wù)智能咨詢顧問,SYBASE 軟件(中國(guó))有限公司,議程,數(shù)據(jù)倉庫解決方案概述,數(shù)據(jù)倉庫設(shè)計(jì)工具,數(shù)據(jù)倉庫引擎 Sybase Adaptive Server IQ Multiplex,,,,,,“數(shù)據(jù)倉庫是在企業(yè)管理和決策中,面向主題的,集成的, 與時(shí)間相關(guān)的,和不可修改的數(shù)據(jù)集合”,Bill Inmon,數(shù)據(jù)倉庫定義,,,,,,,,,,,,,,,OLTP系統(tǒng),,,,,,,,,,,,,,,,,5-10 年,過去,詳細(xì)數(shù)據(jù),當(dāng)前,詳細(xì)數(shù)據(jù),輕度,匯總數(shù)據(jù),高度,匯總數(shù)據(jù),數(shù)據(jù)集市,用戶分析,網(wǎng)絡(luò)資源分析,數(shù)據(jù)倉庫,數(shù)據(jù)倉庫/決策分析系統(tǒng),數(shù)據(jù)倉庫是完全不同的數(shù)據(jù)庫系統(tǒng),RDBMS,Sy

3、base,SAP/ERP,VSAM,EXCEL,,,,,操作(業(yè)務(wù))系統(tǒng)特性,,,事務(wù)處理性能是第一位的,支持日常的業(yè)務(wù),事務(wù)驅(qū)動(dòng),面向應(yīng)用,數(shù)據(jù)是當(dāng)前的并在不斷變化,存儲(chǔ)詳細(xì)數(shù)據(jù) (每一個(gè)事件或事務(wù)),針對(duì)快速預(yù)定義的事務(wù)優(yōu)化設(shè)計(jì),可預(yù)見的使用模式,支持辦事人員或行政人員,,,,,,,數(shù)據(jù)倉庫應(yīng)用系統(tǒng)特點(diǎn),支持長(zhǎng)遠(yuǎn)的業(yè)務(wù)戰(zhàn)略決策,,分析驅(qū)動(dòng),,面向主題,,數(shù)據(jù)是歷史的,,數(shù)據(jù)反映某個(gè)時(shí)間點(diǎn)或一段時(shí)間,,數(shù)據(jù)是靜態(tài)的，除數(shù)據(jù)刷新外,,數(shù)據(jù)是匯總的,,優(yōu)化是針對(duì)查詢而不是更新,,支持管理人員和執(zhí)行主管人員,,,,數(shù)據(jù)倉庫解決方案解決從數(shù)據(jù)庫中獲取信息的問題。,INFORMATION,信 息

4、,信 息,INFORMATION,,,什么是數(shù)據(jù)倉庫解決方案？,,,,,應(yīng)用價(jià)值,時(shí)間,1.,日常報(bào)表,2. 即席查詢,3. 分析,4. 數(shù)據(jù)挖掘,,專題應(yīng)用,,,1 2 3 4,數(shù)據(jù)倉庫應(yīng)用類型,數(shù)據(jù)倉庫應(yīng)用,數(shù)據(jù)倉庫系統(tǒng)體系架構(gòu),,Relational,Package,Legacy,External,source,Data,Clean,Tool,Source Data,Data,Staging,,WareHouse,Admin.,Tools,Enterprise,Data,Warehouse,Data Extraction,,Transformation,

5、and load,Datamart,Datamart,Enterprise/,Central,Data,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,

6、,Local Metadata,,,,數(shù)據(jù)倉庫/商務(wù)智能應(yīng)用成功的關(guān)鍵,,?,做什么，怎么做？,,?,數(shù)據(jù)倉庫性能,,,Sybase & Partner 專業(yè)服務(wù),數(shù)據(jù)倉庫顧問咨詢,,Industry Warehouse Studio,Sybase IWS 方法學(xué),ER Design Tool,Impact Analysis,Metadata Management,,Sybase Industry Warehouse Studio打包的,數(shù)據(jù)倉庫基礎(chǔ)平臺(tái)概述,業(yè)務(wù),模型,物理,模式,元數(shù)據(jù),ETL 工具,例子,報(bào)表,算法,ETL Tool Metadata Exchange,Smart ET

7、L Maps (Future),SQL Templates,Cognos,Business Objects,MicroStrategy,Business Models focused on Key Industry Events,Enterprise-wide, Star Schema-based design,IWS產(chǎn)品介紹,,,,,,,,TABLE,TABLE,TABLE,TABLE,TABLE,Industry-specific,Data Models,,Data,,Warehouse,,“Open RDBMS*”,ORACLE, IBM, MICROSOFT, NCR, SYBASE,

8、 etc.,,,,,,,,,,,,BI Partners,,Sample Applications,,Analytical CRM,Sales Analysis,Customer Profiling,Campaign Analysis,Customer Care Analysis,Loyalty Analysis,Business,Performance,Analysis,Industry Specific,Sample Data,General -,Representative,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Systems,Inte

9、grators 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,,,客戶構(gòu)成,分析,營(yíng)銷活動(dòng),分析,客戶興趣,分析,忠誠(chéng)度,分析,銷售,分析,行業(yè)相關(guān)的,經(jīng)營(yíng)業(yè)績(jī)分析,,,,,,,收益率,分析,EVT_TYP_ID = EVT_TYP_ID,,PRD_ID =

10、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,COR_RPT_STRC_ID = COR_RPT_STRC_ID,ENTY_ID = CNTC_RSOL_EMP_ID,GEO_ID = GEO_ID,

11、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_ID = DEMO_ID,,ENTY_ID = ENTY_ID,FNCL_SCOR_ID = FNCL_SCOR_ID,MEASURE_UNIT_ID =

12、 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_ID = GEO_ID,,COR_RPT_STRC_ID = COR_RPT_STRC_ID,,EVT_TYP_ID = COR_EVT

13、_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,char,,,,,F_HR_EVT,V_E_ENTY_ID,,INTEGER,V_E2_ENTY_ID,,INTE

14、GER,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,,INTEGER,PRODUCT_ID,,INTEGER,DEMO_ID,,IN

15、TEGER,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,,INTEGER,PRODUCT_ID,,INTEGER

16、,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_COMPAN

17、Y,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,CHNL_ENTY

18、_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_AREA

19、,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

20、_ID,,INTEGER,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

21、_PRMSN_IND,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_B

22、HVR_SCOR,PN_BHVR_SCOR_ID,,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

23、_END_PRD_ID,INTEGER,,,,,F_SALES_EVENT,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_R

24、SOL_ID,,INTEGER,LGCY_SYS_CUS_CNTC,INTEGER,CUS_CNTC_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,ME

25、ASURE_UNIT_ID,,INTEGER,FNCL_SCOR_ID,,INTEGER,LANGUAGE_ID,,INTEGER,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_NAM

26、E_PFX,CHAR,EMP_SNAME,VARCHAR(15),EMP_FNAME,VARCHAR(15),EMP_MNAME,VARCHAR(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,,

27、INTEGER,PROMO_EPSD_ID,,INTEGER,ENTY_ID,,INTEGER,CNTC_PRD_ID,,integer,CCH_COUNT,,INTEGER,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

28、_EVT_TX_SEQ_NO,,SMALLINT,TRGT_GRP,char(3),CORE_EVENTY_TYPE_ID,INTEGER,CNTCT_CNTRL_GRP_IN,CHAR,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

29、,DAY_IN_MONTH,SMALLINT,DAY_IN_YEAR,SMALLINT,WEEK_IN_MONTH,SMALLINT,WEEK_IN_YEAR,SMALLINT,CLNT_SVC_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_Y

30、R,char(6),MONTH_IN_FNCL_QTR,SMALLINT,WEEK_IN_FNCL_QTR,SMALLINT,DAY_IN_FNCL_QTR,SMALLINT,SEMI_YEARLY,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,,

31、INTEGER,CPGN_COMM_DESC,CHAR,分析型,CRM,經(jīng)營(yíng)業(yè)績(jī)管理,Sybase Industry Warehouse Studio,分析型應(yīng)用框架,Time,資源,,搜集需求,理解業(yè)務(wù)線,設(shè)計(jì)模式,ETL 模板,構(gòu)造分析需求,實(shí)施,測(cè)試,用戶反饋,精練,測(cè)試,第,二代倉庫,典型的數(shù)據(jù)倉庫,項(xiàng)目從這里開始,Sybase IWS 提供的時(shí)間上的價(jià)值,快速啟動(dòng)數(shù)據(jù)倉庫項(xiàng)目,搜集需求,理解業(yè)務(wù)線,設(shè)計(jì)模式,ETL 模板,構(gòu)造分析查詢,實(shí)施,測(cè)試,第,一代倉庫,Sybase IWS,從這里開始,IWS,節(jié)省,3 到 6,個(gè)月,更多的價(jià)值 =,更快地訪問信息,Sybase Indust

32、ry Warehouse StudioValue Proposition 回顧,,預(yù)先建立的業(yè)務(wù)和物理模型優(yōu)化了項(xiàng)目進(jìn)度的安排和加快了對(duì)數(shù)據(jù)的訪問,基于經(jīng)過驗(yàn)證的實(shí)施經(jīng)驗(yàn)和行業(yè)經(jīng)驗(yàn),設(shè)計(jì)和方法論是可擴(kuò)展/可定制的,安全,企業(yè)范圍,數(shù)據(jù)庫獨(dú)立,面向行業(yè),集成的模型和基礎(chǔ)平臺(tái),,靈巧,節(jié)省資源 … 一半的投入,節(jié)省時(shí)間 … 更快的實(shí)施,節(jié)省資金 … 降低成本,節(jié)省,數(shù)據(jù)倉庫系統(tǒng)體系架構(gòu),,Relational,Package,Legacy,External,source,Data,Clean,Tool,Source Data,Data,Staging,,WareHouse,Admin.,Tool

33、s,Enterprise,Data,Warehouse,Data Extraction,,Transformation,and load,Datamart,Datamart,Enterprise/,Central,Data,Warehouse,RDBMS,ROLAP,RDBMS,,Dimension Modeling,Conformed dimension&fact,Including atomic&aggregate,Architected,Datamarts,Central,Metadata,,Data,Modeling,Tool,,,,End-User,Tool,,,,End-User,

34、Tool,,MDB,,,,End-User,Tool,,,,End-User,Tool,,Local Metadata,,Local Metadata,,,,Adaptive Server? IQ Multiplex?,是專門為滿足數(shù)據(jù)倉庫和商務(wù)智能設(shè)計(jì)的高性能的關(guān)系數(shù)據(jù)庫系統(tǒng)。,IQ Multiplex,的主要特點(diǎn)是：,,?,高可擴(kuò)展性,–,支持?jǐn)?shù)以千計(jì)的并發(fā)用戶存取,TB,級(jí)的數(shù)據(jù)。,,?,突破性的速度,–,閃電般的查詢速度，比傳統(tǒng),RDBMS,快,10 ~ 100,倍以上。,,?,無限的靈活性,–,支持任意類型的即席查詢。,,?,最低的擁有總成本,–,高效的數(shù)據(jù)壓縮存儲(chǔ)，達(dá)到,30% ~

35、 60%,；簡(jiǎn)單的維護(hù)和管理。,,集成的主要產(chǎn)品,Design,,Warehouse Architect,Manage,,Sybase ASIQM,Integrate,,Informatica,,Enterprise Connect,,Replication Server,PowerMart,,Visualize,,Bo、Brio,,Cognos,,SPSS,Administer,,Warehouse Control Center,Warehouse,Control,Centre,Sybase數(shù)據(jù)倉庫相關(guān)產(chǎn)品集的構(gòu)成,,Relational,Package,Legacy,External,so

36、urce,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,RDBMS, Star Schema,Architected,Datamarts,Central,Metadata,,Data,Modeling,Tool,,,,End-User,To

37、ol,,,,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,設(shè)計(jì): 成功的關(guān)鍵,數(shù)據(jù)庫的設(shè)計(jì)對(duì)數(shù)據(jù)倉庫系統(tǒng)的整體性能、裝載和,,建立索引的時(shí)間以及數(shù)據(jù)量的增長(zhǎng)等的影響超過,,任何其它方面。,數(shù)據(jù)倉庫設(shè)計(jì),在支持分析和決策的查詢環(huán)境中，使業(yè)務(wù)用戶可以,訪問，理解和利用數(shù)據(jù),以業(yè)務(wù)用戶

38、理解和運(yùn)用信息的方式組織數(shù)據(jù),可預(yù)見的查詢方式,基于時(shí)間的,匯總的數(shù)據(jù),向下/上的鉆?。―rill-down / drill-up）,多維模型設(shè)計(jì),傳統(tǒng)的數(shù)據(jù)建模方法(如ER,模型)可能非常復(fù)雜且不易理解,,按照最終用戶的想法定義信息 (以查詢?yōu)橹行慕?,,Star(星型), Snowflake(雪花型)，Constellation(星座型)，Snowstorm(雪暴型),,Facts(,事實(shí))： 可度量數(shù)據(jù)，如 數(shù)量、價(jià)格,,Dimensions(維)：用于分類Fact的詳細(xì)數(shù)據(jù),,,,,,,,,,,,,,Grocery Transaction,Store Number,Transacti

39、on 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,City,State,Country,Telephone,,Product,Product,Description,Category,Fact Table,Dimensio

40、n,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 Code,Customer Category,,Time,Transaction Date,,Store,S

41、tore 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,Category,Customer Category,為了避免數(shù)據(jù)冗余, 用多張表來描述一個(gè)復(fù)

42、雜維,在星型模式的基礎(chǔ)上, 構(gòu)造維表的多層結(jié)構(gòu),多維模型: 雪花模式,,,,,,,,,,,,,,,,,,,,,,,,,,,,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,,Sto

43、re,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,Purchase Order,Unit

44、 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,具有多個(gè)事實(shí)表,多維模型: 星座模式,,,

45、,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,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,

46、City,State,Country,Telephone,Region,,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

47、,Promotion,From Date,To Date,,Product 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 Loc

48、ation,Quantity On Hand,Quantity Back Ordered,,Warehouse,Warehouse,Address 1,Address 2,Address 3,City,State,Country,Postal Code,具有多個(gè)事實(shí)表與多層維表,多維模型: 雪暴模式,數(shù)據(jù)模型中的事實(shí)和維度,事實(shí)和維的概念對(duì)應(yīng)于:,數(shù)據(jù)倉庫數(shù)據(jù)庫中的數(shù)據(jù)模型對(duì)象,星型模式（Star schema）,DSS / OLAP 系統(tǒng)中的數(shù)據(jù)模型對(duì)象,多維模型（Multidimensional model）,Sales fact,Sales measures,Time dimension

49、,Attributes of the time dimension,星型模式-Star Schema,Sales Cube,Sales measures,(Metrics),Time dimension,Attributes of thetime dimension,多維模型-Multidimensional Model,數(shù)據(jù)倉庫設(shè)計(jì)工具WarehouseArchitect,為數(shù)據(jù)倉庫的設(shè)計(jì)提供三大功能：,多維建模,度量、維、屬性,事實(shí)表，維表,維層次表，事實(shí)層次表,設(shè)計(jì)向?qū)?聚合（Aggregation Wizard）,分片（Partitioning Wizard）,逆向工程數(shù)據(jù)源,優(yōu)化

50、代碼生成,目標(biāo)數(shù)據(jù)倉庫引擎（IQM，RDBMS),OLAP,分析環(huán)境,,Time identifier = 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,

51、Time identifier,,double,Customer identifier,,double,Store identifier,,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,Produc

52、t description,char(80),WarehouseArchitect,,WarehouseArchitect,,,,Data Warehouse or Data Mart,Database,,,,Operational Source,,OLAP,Engine,Interface,External Objects,Decision Support / OLAP Model (WA Multidimensional Hierarchy),,Dimensional,Analysis,,Transformation,,Relational,and/or,Dimensional,Anal

53、ysis,Data Warehouse Model (WAM),WarehouseArchitect的支持范圍,數(shù)據(jù)倉庫設(shè)計(jì)-小結(jié),WarehouseArchitect,對(duì)數(shù)據(jù)倉庫設(shè)計(jì)過程的每一步都提供支持：,數(shù)據(jù)源中的元數(shù)據(jù)導(dǎo)入。,設(shè)計(jì)和優(yōu)化數(shù)據(jù)倉庫的數(shù)據(jù)模型（星型模式/多維模型）。,與抽取、轉(zhuǎn)換工具對(duì)接，實(shí)施數(shù)據(jù)移動(dòng)。,基于數(shù)據(jù)倉庫模型，為前端DSS/OLAP,工具生成所需的數(shù)據(jù)立方體。,為設(shè)計(jì)過程的每一步生成文檔和報(bào)告。,,數(shù)據(jù)存儲(chǔ)、管理,挑戰(zhàn),,數(shù)據(jù)規(guī)模,,查詢性能,,裝載速度,,易于管理,,存取訪問,成功的關(guān)鍵,快速，高效數(shù)據(jù)存儲(chǔ)技術(shù),出色的查詢性能 - 特殊的索引,技術(shù)，并行查詢,

54、可伸縮性 - GB 到 TB 級(jí),易于管理 - 方便，靈活，GUI,存取訪問 - 數(shù)據(jù)隨時(shí)可用,數(shù)據(jù)管理,解決的方案,通用的關(guān)系數(shù)據(jù)庫系統(tǒng),專門的數(shù)據(jù)倉庫服務(wù)器,Sybase IQM,專門為數(shù)據(jù)倉庫/數(shù)據(jù)集市設(shè)計(jì)的關(guān)系型數(shù)據(jù)庫,專門針對(duì)OLAP/DSS而優(yōu)化的索引和查詢處理技術(shù),,Adaptive Server IQM,數(shù)據(jù)存儲(chǔ)： Adaptive Server IQM,垂直存儲(chǔ)技術(shù)(Vertical Partitioning),無處不索引(Index EVERYWHERE),專利的Bit Wise索引技術(shù)跨越Bitmap的限制,多種索引類型：FP,LF,HNG,HG,CMP,WD,低級(jí)數(shù)的限

55、制從100擴(kuò)充到1000,數(shù)據(jù)壓縮(通常達(dá)到原始數(shù)據(jù)的 70 - 75%),預(yù)連接的索引提供額外的顯著提高性能手段(Join Index),支持任意設(shè)計(jì)模式,星型、雪花、雪暴、星座模式,普通關(guān)系模式,支持任意加載方式,文件、內(nèi)部數(shù)據(jù)、外部數(shù)據(jù)庫直接加載,開放的接口,,Index,,,,,,,,,,,,,,,,,,,,,,,,傳統(tǒng)RDBMS,Relational Table,Typical RDBMS,數(shù)據(jù)按行存儲(chǔ),數(shù)據(jù)與索引分開存放,很少的索引類型 -,B-,樹,普通關(guān)系數(shù)據(jù)庫為,OLTP,系統(tǒng)進(jìn)行優(yōu)化,,B-tree Index best for retrieving one row at

56、a time,,,,,計(jì)算“NY”,州,A類商店的,平均銷售額,當(dāng)表的記錄數(shù)從幾萬條變?yōu)榍f和上億條時(shí)，,傳統(tǒng)RDBMS技術(shù)面對(duì)的問題:,表掃描的性能極端低下,冗余設(shè)計(jì)代價(jià)高昂、查詢讀取的無效字段過多,低級(jí)數(shù)類型數(shù)據(jù)上索引的失效,普通索引加載和空間代價(jià)，造成不能任意建造,即席查詢的SQL順序?qū)π阅苡酗@著影響,數(shù)值型比較和運(yùn)算，無恰當(dāng)手段加速處理,,傳統(tǒng)RDBMS不適合數(shù)據(jù)倉庫,,,IQM的特殊存儲(chǔ)方式-垂直存儲(chǔ)（按列存儲(chǔ)）,Sybase IQM:,數(shù)據(jù)是按列存儲(chǔ)的，而不是按行存儲(chǔ),,好處:,,只存取查詢所需的數(shù)據(jù),數(shù)據(jù)類型是一致的，因而可以很容易被壓縮,數(shù)據(jù)庫易于修改和管理,Sybase IQ

57、M:,,只讀完成查詢所 涉及到的列,計(jì)算在紐約的“A”,類商店,的平均銷售額,,,,好處:,,無須使用其他的技術(shù)，Sybase IQM就可以減少I/O 超過 90%,IQM的特殊存儲(chǔ)方式-垂直存儲(chǔ)（按列存儲(chǔ)）,,,“How many MALES are NOT INSURED in CALIFORNIA?,,,,Gender,M,M,F,M,M,-,800 Bytes/Row,10M,ROWS,,State,NYCACTMA,CA,-,RDBMS,Insured,YYN,Y,N,M Y CA,M N CA,F Y NY,M N CA,1,2,4,3,Gender,Insured,St

58、ate,,,,+,+,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,基本上只能使用表掃描,查詢過程讀取了太多的無效數(shù)據(jù),,IQM,Example: I/O 的明顯減少,IQM的索引特點(diǎn),索引即是數(shù)據(jù),沒有索引和數(shù)據(jù)的分別,任何一列可以建立多個(gè)索引,系統(tǒng)保證至少會(huì)存在一個(gè)索引（FP）,索引的選擇和設(shè)計(jì)主要基于：,數(shù)據(jù)的級(jí)數(shù)（離散值的個(gè)數(shù)）,在查詢中的使用方式,和SQL語句的順序無關(guān),索引的種類,Fast Proje

59、ction(FP),數(shù)據(jù)壓縮存儲(chǔ),根據(jù)數(shù)據(jù)的特點(diǎn)會(huì)自動(dòng)使用三種方式中的一種,Low Fast (LF),Bit map 索引,High Non Group (HNG),Bit-wise 索引,High Group (HG),G-Array (包括一個(gè)改進(jìn)的B-tree),Compare(CMP),列比較,Word(WD),字符串查找,FP索引有三種內(nèi)部形態(tài),根據(jù)數(shù)據(jù)級(jí)數(shù)特征，,IQ自動(dòng)選擇 FP中最合適的一種表現(xiàn)形式,,If 級(jí)數(shù)> 65536,,FP index,,If 級(jí)數(shù)< 256,,FFP Index (Fast-Fast Projection),,If 級(jí)數(shù),Between 256

60、and 65536,,FFFP Index ( Fast-Fast-Fast Projection),FP形式1：FP Index,該列的級(jí)數(shù)超過,65536,原始數(shù)據(jù)在磁盤上壓縮存儲(chǔ),,alpha,alpha,beta,gamma,beta,beta,FP形式2：FFP Index,列級(jí)數(shù)<,256,內(nèi)部生成一個(gè)單字節(jié)的lookup表,不僅擁有較好查詢效率，同時(shí)得到高效壓縮,,Data Values,Red,Blue,Green,Red,,Color,Red,Blue,Green,,1,2,3,,1,1,1,2,3,3,3,2,Lookup Table,Data,FP形式3：FFFP Ind

61、ex,列的級(jí)數(shù)界于,256和65536之間,系統(tǒng)內(nèi)建一個(gè)雙字節(jié)的lookup表,,Data Values,Red,Blue,Green,Red,,Color,Red,Blue,Green,,1,2,3,,1,1,1,2,3,3,3,2,Lookup Table,Data,,1,1,1,2,3,3,3,2,,1,2,3,LF索引的形態(tài),每個(gè)省份的取值有固定的,bitmap,行和取值的個(gè)數(shù)都可以自由增加,只需處理相應(yīng)的位，對(duì)查詢的性能提高:,select count(*) from customers where state =‘AL’,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

62、,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,示意：省份的LF存儲(chǔ),row-id,北京,上海,天津,河北,山東,安徽,江蘇,浙江,1,0,0,0,1,0,0,0,0,2,0,0,0,0,0,0,0,1,3,0,1,0,0,0,0,0,0,4,1,0,0,0,0,0,0,0,5,0,1,0,0,0,0,0,0,...,高基數(shù)Bit-Wise索引：,HNG,Bit-Wise Index,數(shù)據(jù)按照二進(jìn)制存儲(chǔ),垂直分布和處理,Sybase的專利技

63、術(shù),使用最佳范圍,高基數(shù)數(shù)據(jù)的范圍查找（>,<,between,…..),數(shù)學(xué)或函數(shù)運(yùn)算 (sum and average functions),級(jí)數(shù)任意，數(shù)據(jù)以二進(jìn)制形式存在,數(shù)據(jù)垂直分割-任何一位都可以獨(dú)立進(jìn)行內(nèi)部操作,由于大量的0和1同時(shí)出現(xiàn)，因此數(shù)據(jù)的壓縮比較容易實(shí)現(xiàn),Query Example:Select * where Sales>7,高級(jí)數(shù)Bit-Wise索引：,HNG,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Sales 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,

64、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,,高基數(shù)分類索引：HG,在Bit-Wise的基礎(chǔ)上增加一個(gè)B-Tree，并保證樹在加載時(shí)不會(huì)重建,,最佳使用場(chǎng)合：,,多表的連結(jié)查詢Joins,,Select Distinct, Count Distinct,,Group By，

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

66、iplex） 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,沒有數(shù)據(jù)的重新分布,沒有模式改變的維護(hù)工作,系統(tǒng)同步所需的I/O最小,(為其他并行系統(tǒng)的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