商務智能解決方案

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ù)倉庫/商務智能解決方案,魏健,商務智能咨詢顧問,SYBASE 軟件(中國)有限公司,議程,數(shù)據(jù)倉庫解決方案概述,數(shù)據(jù)倉庫設計工具,數(shù)據(jù)倉庫引擎 Sybase Adaptive Server IQ Multiplex,“數(shù)據(jù)倉庫是在企業(yè)管理和決策中,面向主題的,集成的, 與時間相關的,和不可修改的數(shù)據(jù)集合”,Bill Inmon,數(shù)據(jù)倉庫定義,OLTP系統(tǒng),5-10 年,過去,詳細數(shù)據(jù),當前,詳細數(shù)據(jù),輕度,匯總數(shù)據(jù),高度,匯總數(shù)據(jù),數(shù)據(jù)集市,用戶分析,網(wǎng)絡資源分析,數(shù)據(jù)倉庫,數(shù)據(jù)倉庫/決策分析系統(tǒng),數(shù)據(jù)倉庫是完全不同的數(shù)據(jù)庫系統(tǒng),RDBMS,Sybase,SAP/ERP,VSAM,EXCEL,操作(業(yè)務)系統(tǒng)特性,事務處理性能是第一位的,支持日常的業(yè)務,事務驅動,面向應用,數(shù)據(jù)是當前的并在不斷變化,存儲詳細數(shù)據(jù) (每一個事件或事務),針對快速預定義的事務優(yōu)化設計,可預見的使用模式,支持辦事人員或行政人員,數(shù)據(jù)倉庫應用系統(tǒng)特點,支持長遠的業(yè)務戰(zhàn)略決策,分析驅動,面向主題,數(shù)據(jù)是歷史的,數(shù)據(jù)反映某個時間點或一段時間,數(shù)據(jù)是靜態(tài)的，除數(shù)據(jù)刷新外,數(shù)據(jù)是匯總的,優(yōu)化是針對查詢而不是更新,支持管理人員和執(zhí)行主管人員,數(shù)據(jù)倉庫解決方案解決從數(shù)據(jù)庫中獲取信息的問題。,INFORMATION,信 息,信 息,INFORMATION,什么是數(shù)據(jù)倉庫解決方案？,應用價值,時間,1.,日常報表,2. 即席查詢,3. 分析,4. 數(shù)據(jù)挖掘,專題應用,1 2 3 4,數(shù)據(jù)倉庫應用類型,數(shù)據(jù)倉庫應用,數(shù)據(jù)倉庫系統(tǒng)體系架構,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,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,數(shù)據(jù)倉庫/商務智能應用成功的關鍵,做什么，怎么做？,數(shù)據(jù)倉庫性能,Sybase & Partner 專業(yè)服務,數(shù)據(jù)倉庫顧問咨詢,Industry Warehouse Studio,Sybase IWS 方法學,ER Design Tool,Impact Analysis,Metadata Management,Sybase Industry Warehouse Studio打包的,數(shù)據(jù)倉庫基礎平臺概述,業(yè)務,模型,物理,模式,元數(shù)據(jù),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 Schema-based design,IWS產(chǎn)品介紹,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,Business,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,客戶構成,分析,營銷活動,分析,客戶興趣,分析,忠誠度,分析,銷售,分析,行業(yè)相關的,經(jīng)營業(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,COR_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_ID = 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_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,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,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,INTEGER,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_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,CHNL_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_AREA,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,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_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_BHVR_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_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_RSOL_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,MEASURE_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_NAME_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,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_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,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_YR,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,INTEGER,CPGN_COMM_DESC,CHAR,分析型,CRM,經(jīng)營業(yè)績管理,Sybase Industry Warehouse Studio,分析型應用框架,Time,資源,搜集需求,理解業(yè)務線,設計模式,ETL 模板,構造分析需求,實施,測試,用戶反饋,精練,測試,第,二代倉庫,典型的數(shù)據(jù)倉庫,項目從這里開始,Sybase IWS 提供的時間上的價值,快速啟動數(shù)據(jù)倉庫項目,搜集需求,理解業(yè)務線,設計模式,ETL 模板,構造分析查詢,實施,測試,第,一代倉庫,Sybase IWS,從這里開始,IWS,節(jié)省,3 到 6,個月,更多的價值 =,更快地訪問信息,Sybase Industry Warehouse StudioValue Proposition 回顧,預先建立的業(yè)務和物理模型優(yōu)化了項目進度的安排和加快了對數(shù)據(jù)的訪問,基于經(jīng)過驗證的實施經(jīng)驗和行業(yè)經(jīng)驗,設計和方法論是可擴展/可定制的,安全,企業(yè)范圍,數(shù)據(jù)庫獨立,面向行業(yè),集成的模型和基礎平臺,靈巧,節(jié)省資源 一半的投入,節(jié)省時間 更快的實施,節(jié)省資金 降低成本,節(jié)省,數(shù)據(jù)倉庫系統(tǒng)體系架構,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,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,是專門為滿足數(shù)據(jù)倉庫和商務智能設計的高性能的關系數(shù)據(jù)庫系統(tǒng)。,IQ Multiplex,的主要特點是：,高可擴展性,支持數(shù)以千計的并發(fā)用戶存取,TB,級的數(shù)據(jù)。,突破性的速度,閃電般的查詢速度，比傳統(tǒng),RDBMS,快,10 100,倍以上。,無限的靈活性,支持任意類型的即席查詢。,最低的擁有總成本,高效的數(shù)據(jù)壓縮存儲，達到,30% 60%,；簡單的維護和管理。,集成的主要產(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ù)倉庫相關產(chǎn)品集的構成,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,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,設計: 成功的關鍵,數(shù)據(jù)庫的設計對數(shù)據(jù)倉庫系統(tǒng)的整體性能、裝載和,建立索引的時間以及數(shù)據(jù)量的增長等的影響超過,任何其它方面。,數(shù)據(jù)倉庫設計,在支持分析和決策的查詢環(huán)境中，使業(yè)務用戶可以,訪問，理解和利用數(shù)據(jù),以業(yè)務用戶理解和運用信息的方式組織數(shù)據(jù),可預見的查詢方式,基于時間的,匯總的數(shù)據(jù),向下/上的鉆?。―rill-down / drill-up）,多維模型設計,傳統(tǒng)的數(shù)據(jù)建模方法(如ER,模型)可能非常復雜且不易理解,按照最終用戶的想法定義信息 (以查詢?yōu)橹行慕?,Star(星型), Snowflake(雪花型)，Constellation(星座型)，Snowstorm(雪暴型),Facts(,事實)： 可度量數(shù)據(jù)，如 數(shù)量、價格,Dimensions(維)：用于分類Fact的詳細數(shù)據(jù),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,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 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,Category,Customer Category,為了避免數(shù)據(jù)冗余, 用多張表來描述一個復雜維,在星型模式的基礎上, 構造維表的多層結構,多維模型: 雪花模式,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,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 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,具有多個事實表,多維模型: 星座模式,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,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,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 Location,Quantity On Hand,Quantity Back Ordered,Warehouse,Warehouse,Address 1,Address 2,Address 3,City,State,Country,Postal Code,具有多個事實表與多層維表,多維模型: 雪暴模式,數(shù)據(jù)模型中的事實和維度,事實和維的概念對應于:,數(shù)據(jù)倉庫數(shù)據(jù)庫中的數(shù)據(jù)模型對象,星型模式（Star schema）,DSS / OLAP 系統(tǒng)中的數(shù)據(jù)模型對象,多維模型（Multidimensional model）,Sales fact,Sales measures,Time dimension,Attributes of the time dimension,星型模式-Star Schema,Sales Cube,Sales measures,(Metrics),Time dimension,Attributes of thetime dimension,多維模型-Multidimensional Model,數(shù)據(jù)倉庫設計工具WarehouseArchitect,為數(shù)據(jù)倉庫的設計提供三大功能：,多維建模,度量、維、屬性,事實表，維表,維層次表，事實層次表,設計向導,聚合（Aggregation Wizard）,分片（Partitioning Wizard）,逆向工程數(shù)據(jù)源,優(yōu)化代碼生成,目標數(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,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,Product 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,Analysis,Data Warehouse Model (WAM),WarehouseArchitect的支持范圍,數(shù)據(jù)倉庫設計-小結,WarehouseArchitect,對數(shù)據(jù)倉庫設計過程的每一步都提供支持：,數(shù)據(jù)源中的元數(shù)據(jù)導入。,設計和優(yōu)化數(shù)據(jù)倉庫的數(shù)據(jù)模型（星型模式/多維模型）。,與抽取、轉換工具對接，實施數(shù)據(jù)移動。,基于數(shù)據(jù)倉庫模型，為前端DSS/OLAP,工具生成所需的數(shù)據(jù)立方體。,為設計過程的每一步生成文檔和報告。,數(shù)據(jù)存儲、管理,挑戰(zhàn),數(shù)據(jù)規(guī)模,查詢性能,裝載速度,易于管理,存取訪問,成功的關鍵,快速，高效數(shù)據(jù)存儲技術,出色的查詢性能 - 特殊的索引,技術，并行查詢,可伸縮性 - GB 到 TB 級,易于管理 - 方便，靈活，GUI,存取訪問 - 數(shù)據(jù)隨時可用,數(shù)據(jù)管理,解決的方案,通用的關系數(shù)據(jù)庫系統(tǒng),專門的數(shù)據(jù)倉庫服務器,Sybase IQM,專門為數(shù)據(jù)倉庫/數(shù)據(jù)集市設計的關系型數(shù)據(jù)庫,專門針對OLAP/DSS而優(yōu)化的索引和查詢處理技術,Adaptive Server IQM,數(shù)據(jù)存儲： Adaptive Server IQM,垂直存儲技術(Vertical Partitioning),無處不索引(Index EVERYWHERE),專利的Bit Wise索引技術跨越Bitmap的限制,多種索引類型：FP,LF,HNG,HG,CMP,WD,低級數(shù)的限制從100擴充到1000,數(shù)據(jù)壓縮(通常達到原始數(shù)據(jù)的 70 - 75%),預連接的索引提供額外的顯著提高性能手段(Join Index),支持任意設計模式,星型、雪花、雪暴、星座模式,普通關系模式,支持任意加載方式,文件、內(nèi)部數(shù)據(jù)、外部數(shù)據(jù)庫直接加載,開放的接口,Index,傳統(tǒng)RDBMS,Relational Table,Typical RDBMS,數(shù)據(jù)按行存儲,數(shù)據(jù)與索引分開存放,很少的索引類型 -,B-,樹,普通關系數(shù)據(jù)庫為,OLTP,系統(tǒng)進行優(yōu)化,B-tree Index best for retrieving one row at a time,計算“NY”,州,A類商店的,平均銷售額,當表的記錄數(shù)從幾萬條變?yōu)榍f和上億條時，,傳統(tǒng)RDBMS技術面對的問題:,表掃描的性能極端低下,冗余設計代價高昂、查詢讀取的無效字段過多,低級數(shù)類型數(shù)據(jù)上索引的失效,普通索引加載和空間代價，造成不能任意建造,即席查詢的SQL順序對性能有顯著影響,數(shù)值型比較和運算，無恰當手段加速處理,傳統(tǒng)RDBMS不適合數(shù)據(jù)倉庫,IQM的特殊存儲方式-垂直存儲（按列存儲）,Sybase IQM:,數(shù)據(jù)是按列存儲的，而不是按行存儲,好處:,只存取查詢所需的數(shù)據(jù),數(shù)據(jù)類型是一致的，因而可以很容易被壓縮,數(shù)據(jù)庫易于修改和管理,Sybase IQM:,只讀完成查詢所 涉及到的列,計算在紐約的“A”,類商店,的平均銷售額,好處:,無須使用其他的技術，Sybase IQM就可以減少I/O 超過 90%,IQM的特殊存儲方式-垂直存儲（按列存儲）,“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,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,基本上只能使用表掃描,查詢過程讀取了太多的無效數(shù)據(jù),IQM,Example: I/O 的明顯減少,IQM的索引特點,索引即是數(shù)據(jù),沒有索引和數(shù)據(jù)的分別,任何一列可以建立多個索引,系統(tǒng)保證至少會存在一個索引（FP）,索引的選擇和設計主要基于：,數(shù)據(jù)的級數(shù)（離散值的個數(shù)）,在查詢中的使用方式,和SQL語句的順序無關,索引的種類,Fast Projection(FP),數(shù)據(jù)壓縮存儲,根據(jù)數(shù)據(jù)的特點會自動使用三種方式中的一種,Low Fast (LF),Bit map 索引,High Non Group (HNG),Bit-wise 索引,High Group (HG),G-Array (包括一個改進的B-tree),Compare(CMP),列比較,Word(WD),字符串查找,FP索引有三種內(nèi)部形態(tài),根據(jù)數(shù)據(jù)級數(shù)特征，,IQ自動選擇 FP中最合適的一種表現(xiàn)形式,If 級數(shù)> 65536,FP index,If 級數(shù)< 256,FFP Index (Fast-Fast Projection),If 級數(shù),Between 256 and 65536,FFFP Index ( Fast-Fast-Fast Projection),FP形式1：FP Index,該列的級數(shù)超過,65536,原始數(shù)據(jù)在磁盤上壓縮存儲,alpha,alpha,beta,gamma,beta,beta,FP形式2：FFP Index,列級數(shù)<,256,內(nèi)部生成一個單字節(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,FP形式3：FFFP Index,列的級數(shù)界于,256和65536之間,系統(tǒng)內(nèi)建一個雙字節(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),每個省份的取值有固定的,bitmap,行和取值的個數(shù)都可以自由增加,只需處理相應的位，對查詢的性能提高:,select count(*) from customers where state =AL,示意：省份的LF存儲,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ù)按照二進制存儲,垂直分布和處理,Sybase的專利技術,使用最佳范圍,高基數(shù)數(shù)據(jù)的范圍查找（>,<,between,.),數(shù)學或函數(shù)運算 (sum and average functions),級數(shù)任意，數(shù)據(jù)以二進制形式存在,數(shù)據(jù)垂直分割-任何一位都可以獨立進行內(nèi)部操作,由于大量的0和1同時出現(xiàn)，因此數(shù)據(jù)的壓縮比較容易實現(xiàn),Query Example:Select * where Sales>7,高級數(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,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的基礎上增加一個B-Tree，并保證樹在加載時不會重建,最佳使用場合：,多表的連結查詢Joins,Select Distinct, Count Distinct,Group By，Order by,高級數(shù)分類索引：HG,在任何一塊充滿后，指針被轉化,成一個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 （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,沒有數(shù)據(jù)的重新分布,沒有模式改變的維護工作,系統(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