商務智能解決方案

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 數據倉庫/商務智能解決方案,魏健,商務智能咨詢顧問,SYBASE 軟件(中國)有限公司,議程,數據倉庫解決方案概述,數據倉庫設計工具,數據倉庫引擎 Sybase Adaptive Server IQ Multiplex,“數據倉庫是在企業(yè)管理和決策中,面向主題的,集成的, 與時間相關的,和不可修改的數據集合”,Bill Inmon,數據倉庫定義,OLTP系統(tǒng),5-10 年,過去,詳細數據,當前,詳細數據,輕度,匯總數據,高度,匯總數據,數據集市,用戶分析,網絡資源分析,數據倉庫,數據倉庫/決策分析系統(tǒng),數據倉庫是完全不同的數據庫系統(tǒng),RDBMS,Sybase,SAP/ERP,VSAM,EXCEL,操作(業(yè)務)系統(tǒng)特性,事務處理性能是第一位的,支持日常的業(yè)務,事務驅動,面向應用,數據是當前的并在不斷變化,存儲詳細數據 (每一個事件或事務),針對快速預定義的事務優(yōu)化設計,可預見的使用模式,支持辦事人員或行政人員,數據倉庫應用系統(tǒng)特點,支持長遠的業(yè)務戰(zhàn)略決策,分析驅動,面向主題,數據是歷史的,數據反映某個時間點或一段時間,數據是靜態(tài)的，除數據刷新外,數據是匯總的,優(yōu)化是針對查詢而不是更新,支持管理人員和執(zhí)行主管人員,數據倉庫解決方案解決從數據庫中獲取信息的問題。,INFORMATION,信 息,信 息,INFORMATION,什么是數據倉庫解決方案？,應用價值,時間,1.,日常報表,2. 即席查詢,3. 分析,4. 數據挖掘,專題應用,1 2 3 4,數據倉庫應用類型,數據倉庫應用,數據倉庫系統(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,數據倉庫/商務智能應用成功的關鍵,做什么，怎么做？,數據倉庫性能,Sybase & Partner 專業(yè)服務,數據倉庫顧問咨詢,Industry Warehouse Studio,Sybase IWS 方法學,ER 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 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,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è)相關的,經營業(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,經營業(yè)績管理,Sybase Industry Warehouse Studio,分析型應用框架,Time,資源,搜集需求,理解業(yè)務線,設計模式,ETL 模板,構造分析需求,實施,測試,用戶反饋,精練,測試,第,二代倉庫,典型的數據倉庫,項目從這里開始,Sybase IWS 提供的時間上的價值,快速啟動數據倉庫項目,搜集需求,理解業(yè)務線,設計模式,ETL 模板,構造分析查詢,實施,測試,第,一代倉庫,Sybase IWS,從這里開始,IWS,節(jié)省,3 到 6,個月,更多的價值 =,更快地訪問信息,Sybase Industry Warehouse StudioValue Proposition 回顧,預先建立的業(yè)務和物理模型優(yōu)化了項目進度的安排和加快了對數據的訪問,基于經過驗證的實施經驗和行業(yè)經驗,設計和方法論是可擴展/可定制的,安全,企業(yè)范圍,數據庫獨立,面向行業(yè),集成的模型和基礎平臺,靈巧,節(jié)省資源 一半的投入,節(jié)省時間 更快的實施,節(jié)省資金 降低成本,節(jié)省,數據倉庫系統(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,是專門為滿足數據倉庫和商務智能設計的高性能的關系數據庫系統(tǒng)。,IQ Multiplex,的主要特點是：,高可擴展性,支持數以千計的并發(fā)用戶存取,TB,級的數據。,突破性的速度,閃電般的查詢速度，比傳統(tǒng),RDBMS,快,10 100,倍以上。,無限的靈活性,支持任意類型的即席查詢。,最低的擁有總成本,高效的數據壓縮存儲，達到,30% 60%,；簡單的維護和管理。,集成的主要產品,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數據倉庫相關產品集的構成,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,設計: 成功的關鍵,數據庫的設計對數據倉庫系統(tǒng)的整體性能、裝載和,建立索引的時間以及數據量的增長等的影響超過,任何其它方面。,數據倉庫設計,在支持分析和決策的查詢環(huán)境中，使業(yè)務用戶可以,訪問，理解和利用數據,以業(yè)務用戶理解和運用信息的方式組織數據,可預見的查詢方式,基于時間的,匯總的數據,向下/上的鉆取（Drill-down / drill-up）,多維模型設計,傳統(tǒng)的數據建模方法(如ER,模型)可能非常復雜且不易理解,按照最終用戶的想法定義信息 (以查詢?yōu)橹行慕?,Star(星型), Snowflake(雪花型)，Constellation(星座型)，Snowstorm(雪暴型),Facts(,事實)： 可度量數據，如 數量、價格,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,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,為了避免數據冗余, 用多張表來描述一個復雜維,在星型模式的基礎上, 構造維表的多層結構,多維模型: 雪花模式,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,具有多個事實表與多層維表,多維模型: 雪暴模式,數據模型中的事實和維度,事實和維的概念對應于:,數據倉庫數據庫中的數據模型對象,星型模式（Star schema）,DSS / OLAP 系統(tǒng)中的數據模型對象,多維模型（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,數據倉庫設計工具WarehouseArchitect,為數據倉庫的設計提供三大功能：,多維建模,度量、維、屬性,事實表，維表,維層次表，事實層次表,設計向導,聚合（Aggregation Wizard）,分片（Partitioning Wizard）,逆向工程數據源,優(yōu)化代碼生成,目標數據倉庫引擎（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的支持范圍,數據倉庫設計-小結,WarehouseArchitect,對數據倉庫設計過程的每一步都提供支持：,數據源中的元數據導入。,設計和優(yōu)化數據倉庫的數據模型（星型模式/多維模型）。,與抽取、轉換工具對接，實施數據移動。,基于數據倉庫模型，為前端DSS/OLAP,工具生成所需的數據立方體。,為設計過程的每一步生成文檔和報告。,數據存儲、管理,挑戰(zhàn),數據規(guī)模,查詢性能,裝載速度,易于管理,存取訪問,成功的關鍵,快速，高效數據存儲技術,出色的查詢性能 - 特殊的索引,技術，并行查詢,可伸縮性 - GB 到 TB 級,易于管理 - 方便，靈活，GUI,存取訪問 - 數據隨時可用,數據管理,解決的方案,通用的關系數據庫系統(tǒng),專門的數據倉庫服務器,Sybase IQM,專門為數據倉庫/數據集市設計的關系型數據庫,專門針對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),支持任意設計模式,星型、雪花、雪暴、星座模式,普通關系模式,支持任意加載方式,文件、內部數據、外部數據庫直接加載,開放的接口,Index,傳統(tǒng)RDBMS,Relational Table,Typical RDBMS,數據按行存儲,數據與索引分開存放,很少的索引類型 -,B-,樹,普通關系數據庫為,OLTP,系統(tǒng)進行優(yōu)化,B-tree Index best for retrieving one row at a time,計算“NY”,州,A類商店的,平均銷售額,當表的記錄數從幾萬條變?yōu)榍f和上億條時，,傳統(tǒng)RDBMS技術面對的問題:,表掃描的性能極端低下,冗余設計代價高昂、查詢讀取的無效字段過多,低級數類型數據上索引的失效,普通索引加載和空間代價，造成不能任意建造,即席查詢的SQL順序對性能有顯著影響,數值型比較和運算，無恰當手段加速處理,傳統(tǒng)RDBMS不適合數據倉庫,IQM的特殊存儲方式-垂直存儲（按列存儲）,Sybase IQM:,數據是按列存儲的，而不是按行存儲,好處:,只存取查詢所需的數據,數據類型是一致的，因而可以很容易被壓縮,數據庫易于修改和管理,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,基本上只能使用表掃描,查詢過程讀取了太多的無效數據,IQM,Example: I/O 的明顯減少,IQM的索引特點,索引即是數據,沒有索引和數據的分別,任何一列可以建立多個索引,系統(tǒng)保證至少會存在一個索引（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),字符串查找,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,列級數<,256,內部生成一個單字節(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,列的級數界于,256和65536之間,系統(tǒng)內建一個雙字節(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,行和取值的個數都可以自由增加,只需處理相應的位，對查詢的性能提高:,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,.,高基數Bit-Wise索引：,HNG,Bit-Wise Index,數據按照二進制存儲,垂直分布和處理,Sybase的專利技術,使用最佳范圍,高基數數據的范圍查找（>,<,between,.),數學或函數運算 (sum and average functions),級數任意，數據以二進制形式存在,數據垂直分割-任何一位都可以獨立進行內部操作,由于大量的0和1同時出現，因此數據的壓縮比較容易實現,Query Example:Select * where Sales>7,高級數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,高基數分類索引：HG,在Bit-Wise的基礎上增加一個B-Tree，并保證樹在加載時不會重建,最佳使用場合：,多表的連結查詢Joins,Select Distinct, Count 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 的數據壓縮與傳統(tǒng)數據庫的數據膨脹,5 to 10,Times,the,Cost,of IQ-M,1TB數據加載到不同數據倉庫引擎后占用的空間（索引+數據）,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,沒有數據的重新分布,沒有模式改變的維護工作,系統(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