Sybase IWS Solution, tkang@sybase.com
Agenda CRM CRM Sybase IWS Sybase IQ 2
Business Challenges 3
CRM Initiative,,, 4
CRM Type Operational CRM Analytical CRM ERP SCM Customer Profile Loyalty Legacy Systems Customer Service Campaign Business Performance Customer Care Marketing Automation Sales Force Automation Profitability Sales
Analytical CRM 7P of Analytical CRM 1. Profiling (segmentation, risk, propensity) 2. Promotions (campaign management) 3. Persistency (loyalty, retention, churn) 4. Performance (sales by product, category, store, channel) 5. Profitability (product margin - gross, net, brand) 6. Prospecting (customer acquisition, cross-sell, up-sell) 7. Product (design, affinity, supply-chain) 6
Agenda CRM CRM Sybase IWS Sybase IQ 7
CRM and DW CRM & BI Solutions End User Applications Information Content Applications Data Models Data Warehouse Infrastructure Data Acquisition Database Platform Data Integration Data Management 8
CRM Solution Evolution Market Time 9
Packaged Solution Components / 10
Key Technical Challenges Data Model Data Integration Performance & Scalability 11
Agenda CRM CRM Sybase IWS Sybase IQ 12
Internet Applications: Enterprise Application Studio Mobile and Embedded Computing: SQL Anywhere Studio E n Enterprise Solutions: Adaptive Server t e r p r i s e C o n n e c t Business Intelligence: Industry Warehouse Studio 13
( ) (& ) Enterprise Portal CRM Business Applications Applications Layer Personalization e-information e-commerce Integrated Intelligence Real-time Operational Data Access Not Real-time Analytical Data Access Analytical Marketing Automation Call Center Sales Automation Order Processing Legacy Systems Financial IWS CRM Business Productivity 14
Warehouse Architect Data Warehouse Data Warehouse SYBASE IQ CRM ETL (Option) Informatica Warehouse Control Center BI (Option) 15
Data Model & Applications CRA SQL QuickStart Query Sets CRA Business Objects Integration CRA Cognos Integration CRA MicroStrategy Integration Campaign Web Analytics Household Match Sales Loyalty Customer Care Customer Relationship Analytics (CRA) IWS Customer Analytics Infrastructure (CAI) IWS Core Models IWS Tools Customer Profiling Color-coding Key BAI = Business Analytics Infrastructure BDA = Business Decision Analytics Application Add-on Yet to be Deployed Healthcare BAI Retail Banking BAI Life Insurance BAI P&C Insurance BAI Capital Markets BAI Credit Card BAI Media BAI Telco BAI Healthcare BDA Retail Banking BDA Insurance BDA Insurance BDA Capital Markets BDA Credit Card BDA Media Circulation Analytics Telco BDA 16 Media Advertising Analytics Electronic Bill
D_CORE_RPT_STRC COR_RPT_STRC_ID <pk> HOLDING_COMPANY VAR(35) ORG_TYPE ORG_NAME REGION SALES_TEAM_TYPE SALES_TEAM SALES_PERSON_NAME SALES_PERSON_GRADE SALES_PERSON_TYPE CHNL_CATEGORY1 CHNL_TYPE CHNL_SUBCAT CHNL_NAME CHNL_CEASED_TRD_DT CHNL_ENTY_ID CHNL_CITY CHNL_POSTCODE BEGIN_DATE_PRD_ID END_DATE_PRD_ID DV_HR_EVT_TYPE EVT_TXN_ID <pk,fk> EVT_TYP_ID <fk> EVT_TYP_SHRT_NM EVT_TYP_FULL_NM EVT_TYP_CAT_SHRT_N EVT_TYP_CAT_FULL_N VAR(20) VAR(35) VAR(20) VAR(15) VAR(15) char char(18) char F_CPGN_CNTC_EVT CCE_ID <pk> PROMO_EPSD_ID <pk> ENTY_ID <pk,fk> CNTC_PRD_ID <pk> integer CCH_COUNT <pk> CORE EVT_TYPE_ID <fk> COR_RPTG_STRUCT_ID <fk> GEO_ID <fk> MU_ID <fk> FINANCIAL_SCORE_ID <fk> LANGUAGE_ID <fk> PB_SCORE_ID <fk> PRODUCT_ID <fk> DEMO_ID <fk> EMP_ID <fk> COR_EVT_TX_SEQ_NO <fk> SMALLINT TRGT_GRP CORE_EVENTY_TYPE_ID CNTCT_CNTRL_GRP_IN CCE_RESULT P_PSYCH_ID AFFILIATION_ID PA_ID CC_COMM_EVT_AMT D_CPGN_COM_EVT_TYP EVT_TYP_ID <pk,fk> CPGN_COMM_DESC DATE VAR(20) VAR(20) char char EVT_TYP_ID = EVT_TYP_ID EXPERIAN_SCOR_BAND EVT_TYP_ID = EVT_TYP_ID D_CORE_EVT_TYP EVT_TYP_ID <pk> SCOR_N_BAND PRODUCT_ID = PRODUCT_ID PN_BHVR_SCOR_ID = PN_BHVR_SCOR_ID PRFT_IND_BAND EVT_TYP_SHRT_NAM VAR(15) ENTY_ID = ENTY_ID ENTY_ID = EMP_ID GEO_ID = GEO_ID LANGUAGE_ID = LANGUAGE_ID PRODUCT_ID = PRODUCT_ID DEMO_ID = DEMO_ID EVT_TYP_ID = COR_EVT_TYP_ID ENTY_ID = F_C_ENTY_ID GEO_ID = GEO_ID COR_RPT_STRC_ID = COR_RPT_STRC_ID LANGUAGE_ID = LANGUAGE_ID int F_HR_EVT V_E_ENTY_ID <fk> V_E2_ENTY_ID <fk> EVT_DT_PRD_ID ADMIN <pk,fk> EVT_EMP_ID <pk,fk> EVT_EMP_DEMO <pk,fk> EVT_ADMIN_DEMO <pk,fk> CORE_EXT_ID <pk,fk> CORE_RPTG_STRUC <pk,fk> GEO_ID <pk,fk> MU_ID <pk> FIN_SCORE_ID <pk,fk> LANGUAGE_ID <pk,fk> PB_SCORE_ID <pk> F_C_ENTY_ID <fk> PRODUCT_ID <pk> DEMO_ID <pk,fk> EMP_ID <pk,fk> CDEX_SEQ_NO <pk> QTY char(3) decimal(10,2) EVT_TYP_LONG_NAM EVT_TYP_SUBTYP_NAM integer D_LANGUAGE LANGUAGE_ID <pk> ISO_LANG_CODE ISO_LANG_NAME LANG_GROUP D_PN_BHVR_SCOR char PN_BHVR_SCOR_ID <pk> SCORE1_BAND SCORE_N_BAND FNCL_SCORES_ID <pk> LANGUAGE_ID = LANGUAGE_ID INTERNAL_FNCL_SCOR VAR(50) VAR(35) VAR(15) VAR(20) VAR(20) VAR(20) ENTY_ID = ENTY_ID FNCL_SCOR_ID = FNCL_SCOR_ID MEASURE_UNIT_ID = MEASURE_UNIT_ID FNCL_SCORES_ID = FNCL_SCOR_ID F_CORE_EVT COR_EVT_TXN_ID <pk> COR_EVT_TYP_ID <pk,fk> D_M_MEASURE_UNIT_ID <fk> COR_RPT_STRC_ID <pk,fk> GEO_ID <pk,fk> MEASURE_UNIT_ID <pk,fk> FNCL_SCOR_ID <pk,fk> LANGUAGE_ID <pk,fk> PN_BHVR_SCOR_ID <pk,fk> PRODUCT_ID <pk,fk> DEMO_ID <pk,fk> ENTY_ID <pk,fk> V_E_ENTY_ID <fk> COR_EVT_TXN_SEQ_NB <pk> NUMBER PRD_ID <fk> AMOUNT F_SALES_EVENT DEBT_INCOME_RATIO NUMBER D_PRODUCT PRODUCT_ID <pk,fk> ENTY_ID <fk> PRODUCT_LINE PRODUCT_GROUP PRODUCT_CODE PRODUCT_NAME PD_VARIANT_CODE PRODUCT_VARIANT GRP_INDV_IND PD_START_PRD_ID PD_END_PRD_ID EVT_TXN_ID <fk> EVT_TYP_ID <fk> RPT_STRC_ID <fk> MEASURE_UNIT_ID <fk> FNCL_SCOR_ID <fk> PN_BHVR_SCOR_ID <fk> ENTY_ID <fk> EMP_ID <fk> EVT_TXN_SEQ_NBR <fk> D_FNCL_SCOR PRD_ID = PRD_ID VAR(35) VAR(50) VAR(50) VAR(50) NUMBER MEASURE_UNIT_ID = D_M_MEASURE_UNIT_ID ENTY_ID = V_E_ENTY_ID MEASURE_UNIT_ID = MEASURE_UNIT_ID ENTY_ID = ENTY_ID DEMO_ID = DEMO_ID ENTY_ID = F_C_ENTY_ID COR_EVT_TYP_ID = COR_EVT_TYP_ID GEO_ID = GEO_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 D_TIME_PERIOD PRODUCT_ID = PRODUCT_ID DEMO_ID = DEMO_ID D_MSR_UNIT MEASURE_UNIT_ID <pk> SHRT_DESC LONG_DESC PRD_ID <pk> DT_NA DATE DAY_NAME DAY_ABR DAY_IN_WEEK DAY_IN_MONTH DAY_IN_YEAR WEEK_IN_MONTH WEEK_IN_YEAR CLNT_SVC_WK_IN_YR MONTH_NAME MONTH_ABR MONTH_IN_YEAR CALENDAR_QTR MONTH_IN_QTR WEEK_IN_QTR DAY_IN_QTR FINANCIAL_QTR COMPETITOR_FSCL_YR MONTH_IN_FNCL_QTR WEEK_IN_FNCL_QTR DAY_IN_FNCL_QTR SEMI_YEARLY YEAR_NAME YEAR_ABR SEASON_NAME SEASON_ABR NBR_DAYS_SINCE_90 HOLIDAY_IND XMAS_HLDY_IND EASTER_HLDY_IND char(4) DATE char(8) char(3) SMALLINT SMALLINT SMALLINT SMALLINT SMALLINT char(18) char(10) char(3) SMALLINT char(6) SMALLINT SMALLINT SMALLINT char(6) char(6) SMALLINT SMALLINT SMALLINT SMALLINT char(18) char(4) char(18) char(6) integer char(6) char(20) D_DEMOGRAPHICS DEMO_ID <pk> ALL_ENTRIES INCOME_BAND AGE_BAND GNDR MRTL_STAT HIGH_VALUE_INDICAT ACMDTN_CTGRY NBR_IN_HH_BAND CHLD_AT_HOME_BAND SIZE_CLS LEGAL_ORG_TYPE NBR_EMP_BAND SECTOR_CLS MAIL_PRMSN_IND TELMKT_PRMSN_IND F_CUS_CNTC_EVT V_E_ENTY_ID <fk> CUS_CNTC_ID <pk> D_C_CTCT_RSOL_ID <fk> LGCY_SYS_CUS_CNTC CUS_CNTC_REF CUS_CNTC_EVT_ID char F_C_ENTY_ID <fk> CUS_STSF_RT_ID <fk> CNTC_INIT_DT_ID ENTY_ID RPT_STRC_ID GEO_ID ADR_ID EMP_DEMO_ID EMP_NAME_PFX EMP_SNAME EMP_FNAME EMP_MNAME EMP_NAME_SFX EMP_NTL_INS_NBR EMP_HOME_TEL_NBR EMP_PRIM_FAX_NBR EMP_EMAIL_ID EMP_DOB EMP_GNDR EMP_MRTL_STAT EMP_LIFE_STAT EMP_PREF_LANG HOUR_ID <fk> MINUTE_ID <fk> INIT_CNTC_EMP <fk> char COR_EVT_TXN_ID <fk> COR_EVT_TYP_ID <fk> COR_RPT_STRC_ID <fk> GEO_ID <fk> MEASURE_UNIT_ID <fk> FNCL_SCOR_ID <fk> LANGUAGE_ID <fk> PN_BHVR_SCOR_ID <fk> PRODUCT_ID <fk> DEMO_ID <fk> CNTC_RSOL_EMP_ID <fk> CUS_ID <fk> SRSNS_CUS_CO_ID <fk> DV_EMP D_GEOGRAPHY GEO_ID <pk> ALL_ENTRIES POSTAL_CODE CITY POSTAL_CD_PFX HZRD_WTHR_AREA HZD_WTHR_TYPE DMA_CODE SMSA_CODE ST_PROV_AREA TV_REGION NTL_RADIO_AREA LCL_RADIO_AREA REGION COUNTRY <pk,fk> CONTINENTY_ABBR GEO_SUB_CNTNT_ABBR SMRY_EFF_DT SMRY_END_DT VAR(50) VAR(50) VAR(50) VAR(50) VAR(50) PRISN_ADRS_IND VAR(15) VAR(15) VAR(15) DATE VAR(20) VARYING(1 5) char char(3) char(3) char(3) char(3) Industry-Specific Business Performance Industry-Specific Profitability Sales Loyalty Campaign Customer Profiling Customer Care 17
Key Business Measurements CRM CRM -- -- Business Business Performance Performance -- -- 18 Industry Warehouse Key Studios Business Measures Campaign Customer Profiling Customer Care Customer Loyalty Sales Profitability Traffic Persistency Management Churn Management Network Management Risk Management Usage Healthcare Treatment Market Basket Asset Management Outage Sales & Returns Fault Payment Method Location Cost Discovery Circulation, Advertising Retail Banking Insurance Credit Card Telco Utilities Healthcare Retail Media
IWS Project Life Cycle Typical Projects Start Here Start Here with Sybase IWS Resources Gather Requirements Understand Line-of-Business Design Schema ETL Templates Build Queries for Implement Test Sybase IWS 1stSaves Generation Both Time Warehouse & Money Time User Feedback Refine Test 2nd Generation 19
IWS Key Benefits Business Orientation Industry-oriented Data Reduced Risk Rapid Implementation Modular & Extensible High Performance Cost Management Integrated Open 20
Agenda CRM CRM Sybase IWS Sybase IQ 21
DW DBMS Requirements,,, D/W DBMS Partition Parallel Processing I/O Index Node Ad-hoc( ) Schema( ) 22
Sybase IQ vs. Traditional RDBMS Sybase IQ Column oriented - optimal for BI Ad-hoc access Data compression Little to no aggregation Efficient parallel access to Vertical Data Storage Simple data load, no table partition management Traditional RDBMS Row oriented - optimal for OLTP Repeatable access to data Index Explosion Pre-aggregation - Data Explosion Clustering, Partitioning, MPP - high cost Complex data loading and management across partitions 23
Sybase IQ Features Sybase IQ,,, DSSDW RDBMS Partitioning Vertical Partition Horizontal Partition (Bit-Wise) 7 ASIQ Multiplex Shared Disk Node 24
Flexibility Preplanned reports 51% Ad-hoc Queries 49% Data Warehouse Ad-Hoc Full Table Scan Ad-Hoc Plan Ad-Hoc Table Sybase IQ ColumnIndex. 25
Scalability Node SMP Cluster Shared Disk Sybase IQ Multiplex Node Sybase IQ Sybase IQ Multiplex (MPP, SMP Cluster) Node NodeH/W C P U C P U C P U System Memory System Memory System Memory Sybase IQ MPX Sybase IQ MPX Sybase IQ MPX I/O I/O I/O I/O I/O I/O NodeDB Shared DB 26
Peformance Column Read : 90% I/O : DW. RDBMS SYBASE IQ c1 c2 c3 c4 c5 c6 c7 c8 c9 c1 c2 c3 c4 c5 c6 c7 c8 c9 Vertical Processing (Column-Based) Input/Output Data ( Column ) I/O 90% 27
Economy Sybase IQ - Raw Data~70% Data I/O Overhead H/W CPU, Memory Cache, Multi-Processing 10000 Raw Data 1TB 9000 8000 7000 6000 5000 4000 3000 2000 1000 0 7010 DB2 UDB IBM 5410 NCR 8520 Informix Sun 9870 Oracle8 Sun 970 Sybase IQ 28
Agenda CRM CRM Sybase IWS Sybase IQ 29
The Next Generation CRM Solution - Sybas IWS Business Model Applications Data Model Data Management 30