1 / 51

Mike Wheeler Data Warehouse Architect Mwheeler@sybase August 15-19, 2004

BID 205: Reduced Risk+ Reduced TCO + Reduced Time to Implementation = Business Intelligence Success. Mike Wheeler Data Warehouse Architect Mwheeler@sybase.com August 15-19, 2004. The Enterprise. Unwired. The Enterprise. Unwired. Industry and Cross Platform Solutions. Manage Information.

naiara
Download Presentation

Mike Wheeler Data Warehouse Architect Mwheeler@sybase August 15-19, 2004

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. BID 205: Reduced Risk+ Reduced TCO + Reduced Time to Implementation = Business Intelligence Success Mike Wheeler Data Warehouse Architect Mwheeler@sybase.com August 15-19, 2004

  2. The Enterprise. Unwired.

  3. The Enterprise. Unwired. Industry and Cross Platform Solutions Manage Information Unwire Information Unwire People • Adaptive Server Enterprise • Adaptive Server Anywhere • Sybase IQ • Dynamic Archive • Dynamic ODS • Replication Server • OpenSwitch • Mirror Activator • PowerDesigner • Connectivity Options • EAServer • Industry Warehouse Studio • Unwired Accelerator • Unwired Orchestrator • Unwired Toolkit • Enterprise Portal • Real Time Data Services • SQL Anywhere Studio • M-Business Anywhere • Pylon Family (Mobile Email) • Mobile Sales • XcelleNet Frontline Solutions • PocketBuilder • PowerBuilder Family • AvantGo Sybase Workspace

  4. Enabling Analytic Applications Comprehensive Data Architecture “Although vendors like PeopleSoft, SAP, and Siebel are closer than others, currently the market offers no inclusive commercial platform for operationally, collaboratively, and analytically managing all relationships with all major business partners (e.g., partners, employees, customers, suppliers), across all channels and touchpoints (e.g., storefront, Web, e-mail, telephone) and throughout the entire customer life cycle.” -- Aaron Zornes, Meta Group Scalable, Fast, and Cost Effective Access to Data • According to Kevin Strange of Gartner Group, in order to be successful in business intelligence you need to be able to address all of the following with a BI analytics execution environment: • · Complexity of the data model • · Amount of data • · Query complexity • · Scalability (Concurrent Users)

  5. Legacy APPS Flat Files Other Data Marts The Business Intelligence Technology Stack

  6. Sybase IQ Sybase IQ is a Unique Relational DBMS That Is Optimized for Data Warehousing and Business Intelligence. • unsurpassed query performance • lowest total cost of ownership for analytical applications. • Unlike traditional databases designed from the start for analytics and not transactions • Column-based structure and patented indexing make it the leader in price/performance when it comes to data warehousing.

  7. Data is Stored Vertically Each column is stored separately Bit-Mapped Index Index on every column Optimized Storage Input data is typically compressed Usually = 30-40% Database smaller than input data Even with all the indexes Query Engine Retrieves Only Columns Used in the Query Reduces system I/O dramatically Average 90% Less than competition Permits better data manipulation Schema Design Not Restricted Design based on application use Flat, Star, Relational, Snowflake Any Schema Sybase IQ is Designed for Analytics – What’s Different?

  8. Sybase IQ Delivers … • SPEED •  Faster Answers • Lightning fast query response • High-performance data access • LOWER TCO •  Economical • Data compression saves storage • Low maintenance cost • SCALABILITY •  BI for the Many • 100’s to 1000’s of simultaneous users • Gigabytes to Terabytes of data • Low maintenance cost • FLEXIBILITY •  Open Standards • Any query, • Any schema, • Any configuration

  9. IQ IQ IQ IQ IQ CPU CPU CPU CPU CPU CPU CPU CPU CPU Mem Mem CPU CPU Mem CPU CPU CPU Mem Mem Mem CPU Mem CPU Mem Mem Mem CPU Mem Mem Mem Fiber Channel 64-bit Enables Multiplexing for Scalability and Flexibility Can start with 1 server and add CPUs and memory as needed Multiplexing enables you to add servers and CPUs with little or no loss in scalability Terabytes of disk can be added to the SAN and IQ-M will manage it efficiently • Max DB size: 192 Petabyte • Max row per table: 286 trillion rows • Max nodes:12,000 nodes • Max CPUs: 1.2 million CPUs (+12,000*106)

  10. Sybase IQ Key Business Benefits Enable your business to build applications with powerful access to valuable databases: • Larger amounts of data – greater detail in data • Faster access – quicker answers • Cost effective: • Low entry costs (initial system) • Minimize storage costs (critical in multi-Terabyte systems) • Maximize computing power effectiveness (throughput per CPU) • Flexible scaling – the right power to meet the specific need

  11. Sybase IQ Proof Points: Performance • TPC/H results show that Sybase IQ owns price/performance and TCO leadership results in DW: • 100GB • 300GB • 1TB

  12. HP 9000 Oracle 100 GB 643 GB 6.43 HP 9000 Sybase IQ 100 GB ~70 GB ~ .70 Traditional vs IQ-M COMPETITIVE DATABASE EXPLOSION FACTORS (From TPC-D Tests) Hardware Database Raw Data Total Disk Ratio Digital 8400 Oracle 100 GB 361GB 3.61 HP 9000 Oracle 100 GB 643 GB 6.43 IBM SP2 DB2/6000 PE 100 GB 377 GB 3.77 NCR 5100 Teradata 100 GB 880 GB 8.80 Sun UE Oracle 100 GB 594 GB 5.94 Tandem K1K Non-stop SQL 100 GB 286 GB 2.86 IBM SP2 Oracle 300 GB 1,977 GB 6.59 Pyramid Oracle 300 GB 1,535 GB 5.12 NCR 5100 Teradata 300 GB 880 GB 2.93 NCR 5100 Teradata 1,000 GB 3,280 GB 3.28 HP EPS Informix XPS 300 GB 3,532 GB 11.6 Sun Sybase IQ 100 GB < 100 GB <1.00

  13. Sybase IQ Proof Points: Data Compression • IQ Data Compression: Save money, save time • 4 to 10x less storage • Save your customer USD$1 million per TB and keep Hardware Costs Down

  14. Telco POC (8 CPU): Indexes & compression IQ loading: -concurrent load and query -batch load into IQ: - 12.5 : up to 1M rows/sec - near Real-Time: load every 10 sec -Real-Time data replication into IQ: -12.5 : 1-2 rows/sec -12.6 : +500 rows/sec -more to come

  15. Sybase IQ Proof Points: Scalability and Complexity with Ease

  16. Sybase IQ Dominates Top Ten Awards • Sybase IQ won Grand Prize in Windows category • comScore #1, #2, #3 largest data warehouses • Sybase IQ won 22 out of 80 awards in Decision Support Systems (DSS) categories – on UNIX & Windows • More wins than Teradata, DB2, Oracle & Microsoft

  17. Customer Example: Nielsen Media Research • Business Issues • Huge data volume – 10 years of TV viewer history • Goal – one central data source for multiple applications • Requires extreme flexibility – many unique queries and reports • Each television station wants to slice and dice differently • Need to reduce TCO & increase flexibility • Results • 12 TB detailed input data in production – plans to grow to 30 TB • Sybase IQ: 12 TB => 12 TB (equiv: 36 TB – 84 TB) • Fast access and data load • Full disaster recovery of 12 TB: 60 seconds “We are able to deliver one data warehouse for all our applications, at one-third the storage of conventional technologies, while seeing performance gains as advertised with Sybase IQ.” Kim Ross CIO Nielsen Media Research

  18. Customer Example: BizRate.com • Business Issues • Delivers analysis of internet utilization • Leading online customer survey producer • Cost and logistics becoming unwieldy • Microsoft SQL Server could not scale • Simplify data deliver and analysis for sellers • Business Results • Manage 15 million customer data sets • Tight integration through Reference Architecture • Scaleable solution that will grow • Delivered on Sun Fire V880 Together, Sun and Sybase have created a solution that packs an extraordinary amount of data processing and analytical power into a small footprint that represents a realistic investment for small and mid-sized firms. Sybase’s tight architectural integration with Sun technology provides us with the assurance we need that the technology foundation of our data warehouse will scale to meet our growing needs in the future. Henri Asseily, Chief Technology Officer and Founder of BizRate.com

  19. Customer Example: TranStats • Business Issues • Congressional legislation required consolidated, single-point of access to all transportation statistics • Needed to deliver over the Web • Over 250+ databases of source data • Results • 2.5TB of detailed input data compressed to 1 TB • Query complexity with 18-way joins • Reduced data gathering time • Easy linkages across many data sets allows new insights on transportation safety • The new website is aimed at transportation researchers and analysts • Website gets 15,000 hits per day www.transtats.bts.gov Sybase IQ reduced loading and indexing from 30 minutes to 2.5 to 3 minutes. Query speeds were 20 – 50 times faster than Oracle. Time to add a column was reduced from 4 hours with Oracle to 15 minutes with Sybase IQ. Jeff Butler Assistant Director, Office of Statistical Computing Department of Transportation Bureau of Transportation Statistics

  20. Global Credit Card Company 000 000 000 000 0000 Customer Example: Global Credit Card Company • Business Issues • Unable to perform advanced analysis of fraud patterns for credit card transactions with competing solutions due to performance issues, query complexity limitations • Needed solutions to handle 700-column table to describe every transaction, at least one year of transactions online • Results • Advanced fraud analysis possible for last 4 years • Over 1,600 users worldwide • 10TB of input data; (Sybase IQ 7TB) • equiv. 30TB – 70TB • 10 billion records (last 13 months) of credit card transactions online • All fraud managers worldwide use Sybase IQ system • Over 90% of database is fraud detection information Global Credit Card Company

  21. Customer Example: Internal Revenue Service • Business Issues • Analysis virtually impossible • Lost productivity • Loss of potential billions in revenue • VLDB management • Results • 10+ TB detailed input data (2 yrs of taxpayer records) fits in 5TB of storage • Query complexity with 14-way joins • Average 120 ad-hoc analysis users • Modeled entire population of commercial tax returns • Supports advanced analysis like data mining • Revenue protection & fraud detection • ROI of $250 Million • Portal-enabled The primary technology challenge was to build a system that could manage such large volumes of data and yet was sufficiently open to facilitate queries from various off-the-shelf products. We selected Sybase IQ as the data-management server, based on its strength with decision support type queries.” Jeff Kmonk Manager, Office of ResearchCompliance Research Division Internal Revenue Service

  22. Customer Example: Bank of Montreal • Business Issues • Identify and retain most profitable customers • Increase effectiveness of marketing programs • Attract new customers • Access to multiple information systems and “touch points” • Cutting edge technology and architecture • Results • 1 Terabyte data warehouse • Avg. query complexity with 18-way joins • IRR over 100% • Average credit card volume up 59% • Average credit card balances up 129% • Market share up 60 basis points • System up in 4 months • Component architecture enables flexibility “We felt you should develop the data warehouse component by component because that allows you to apply what you learn.” “Sybase was truly committed to ensuring that we used technology in a way that really impacted the business.” Carl A. Touchie Sr. Manager Electronic Financial Services Bank of Montreal

  23. Dynamic Operational Data Stores A Road Map to Real Time Analysis

  24. What our Customers are saying • Strong competition demanding better business analysis: • More users needing ‘near real-time’ data access to data, for longer periods of time • Users demanding more analytical flexibility

  25. Business Issues • Strong competition demands better business analysis • More users needing ‘real’ time data • Users demanding more analytical flexibility • New operational systems need more precise business analysis • Financial - Fraud • Telco - billing, provisioning, sales, support • Insurance/Healthcare – billing, claims • Ballooning costs of managing data • Maintenance • Storage • People • Processes • Large capital and political investment in existing systems

  26. The Driving Events • Compliance • Demand for ‘near real-time’ access to data over a longer period of time • Basel II, Sarbanes-Oxley, Patriot Act • Data warehouses today only address limited data/populations • The data explosion usually requires costly infrastructure upgrades • Dramatic rise in business users BAM needs • New Data Center Challenges • 3000 systems potentially need upgrades • Managing the business through transition • Unprecedented data explosion and user population growth • Sybase has experience in mainframe co-existence

  27. Possible Solution Options • Traditional Enterprise Data Warehouse / Data mart • Data Offload • Report Servers • Dynamic Operational Data Stores • Traditional Archiving • The Sybase Solution • single layer of data shared for all systems • DODS (Dynamic Operational Data Stores) • Users could directly access the data • Single source of data to “feed” the Data Warehouse • Data Synchronization through a common architecture • Information nearly on-line (near real time)

  28. Technical Architecture DODS Reporting Environment Operational Systems Heterogeneous Environment Dynamic Operational Data Store (DODS) DB2,VSAM & IMS Application Harvesting & Staging Environment Operational Data Store (ODS) Informix Application Server Microsoft SQL Server Application Sybase Replication Server & ASE Storage Oracle Application . . . etc. ASE Application

  29. Operational Data Store: Components Operational Data Store Server • Value Proposition • Non intrusive, risk free • No interference with operational systems • Provide significant data compression (up to 60%) • both structured and un-structured data • Optimized for fast read access • column based not row based • Database and analytics agnostic • standards-based SQL access to data Storage

  30. Results • Single Adaptable Dynamic ODS • Existing Operational Data Systems in a single data layer • Data synchronization every 5-15 minutes • Build ‘right-time’ based on your business need • ‘Near real time’ loading • Hundreds of GB per hour. • Manage hundreds of Terabytes storage • Start small and scale • Users accessing information through any SQL Reporting Application • i.e.. Business Objects, Cognos, SPSS, MicroStrategy and ACCESS…..

  31. Case Study Business Challenge: The Finance Division needed to: collect data from disparate sources and create a financial management system efficiently provide information to state government citizens of Utah state agencies with accurate accounting, payroll, and personnel data Provide analysis tools so agencies could maximize the use of tax revenue Avoid the ballooning costs of data management on the mainframeSolution: Sybase Adaptive Server IQ Multiplex Results: Eliminated dozens of hard copy reports User self service model with access to data they never had before/generate own reports. Reduced data storage on the mainframe - savings of approximately $100,000 per month $5 million savings in report development costs

  32. Case Study Brent Sanderson (IT Manager – State of Utah): "In many ways it has changed the culture of financial and personnel reporting in the state," “The old system generated over 900 hardcopy reports each month. The new system generates less than 90. This saved the state over $5 million in development costs." "As we store more data in the Sybase data warehouse, we use the mainframe less and less. This has led to the state saving $100,000 per month in mainframe disk storage costs."

  33. Case Study – Yapi Credit Business Challenge: The Turkish Bank needs to service 6 million customers: Oracle the incumbent outside the mainframe Data on IBM DB2 Analysis of customer profiles profiles transactions history logs Provide analysis tools to maximize customer service Avoid the ballooning costs of data managementSolution: Sybase Adaptive Server IQ Multiplex Results: 10x improvement in service compared to DB2 Dramatic reduction in storage costs (over 1TB of raw data) Savings of approximately $1.05 million 154% ROI (10 month payback) No additional staff training required from Oracle to Sybase Competition : Oracle (cost of infrastructure too high) IBM (poor DB2 performance)

  34. Case Study – HIRA (Health Insurance) Business Challenge: The Korean Agency has to consolidate data across 46 million customers: Oracle the incumbent outside the mainframe Analysis of customer profiles single data view expand from 6 months of data to 5 years of data Avoid the ballooning costs of data managementSolution: Sybase Adaptive Server IQ Multiplex Results: Analysis reduced from 3 – 4 days to 30 seconds Rapid implementation for country-wide system Reduced raw data storage by 60-80% (saving of over $2 million) Increased data store from 5-6 months to 5 years Reduced manually intensive data management tasks Competition : Oracle (25TB vs Sybase 5TB) NCR Teradata (hardware scalability, poor integration, cost)

  35. Case Study - HIRA Han Beom‑su, General Manager, HIRA "According to published benchmark results, performance of NCR Teradata was also excellent. But the weak point with NCR Teradata was that it was hardware dependent. That would cause a great deal of difficulty in linking with other hardware and expanding its capacity.”

  36. Integrate Organize Analyze Present Data Acquisition Layer Present Results Analytic Applications Analytics Data Architecture Finance Customer EDI CRM Analytics Supplier Products • Some Examples: • Campaign Analysis • Customer Profiling • Customer Care • Loyalty Analysis • Sales Analysis Legacy APPS Industry Event Data LOB APPS Sources of Customer Data Enriched Data Flat Files BPM Analytics • Some Examples: • Profitability Analysis • Persistency Analysis • Fraud Detection • Call Detail Analysis • Network Analysis • Distributor Inventory • Channel Management Other Data Marts ETL Analytics Data Warehouse External Data Successful Analytic Applications RequireAn Enterprise Analytics Architecture

  37. Sybase Industry Warehouse Studio • Physical Data Model for an Enterprise Data Warehouse • Proven Methodology and Tools Necessary to Implement and Manage the IWS • CRM Analytics for all Industries • BPM Analytics for Specific Industries • IWS is Database Agnostic (use any database)

  38. Sybase Industry Warehouse Studio Experience Provides Solid Foundation • Built on experience • Comprehensive industry data models • Common physical database design for each industry optimized for decision support • Common treatments of workflow, measures, reporting, terminology and data types • Incorporating the “best practices and domain knowledge” of industry experts as well as the best practices from the fields of data design and architecture • Leveraging the lessons learned from “hands on” experience gained over many years and numerous implementations of large scale data warehouse in vertical industries The Underlying Philosophy Behind IWS

  39. Sybase Industry Warehouse Studio Accelerates Development • Pre-defined business models – Enterprise Scope • Pre-determined database structures • Open Architecture – Database Agnostic • Sample Data + Sample Report Templates • Enterprise Analytic Data Modeling and Metadata Management Tools • Pre-developed Metadata Definitions • Integration with Leading ETL Tools • Integration with Leading BI Visualization Tools • 1st Application Implemented and Running in 60-120 days

  40. Retail Banking Financial Services Capital Markets Credit Card Healthcare Telco IWS Core Analytics Blueprint Core Tables Insurance Treatment Utility Telco Summary Events Cross Industry Tables + Views Base Views Insurance Media Life Property & Casualty Sybase Industry Warehouse StudioThe Enterprise Blueprint

  41. Sybase Industry Warehouse StudioCaptures the 360° View Of the Customer • Marketing Analytics • Analyze positive and negative responses to refine subsequent Campaigns • Measure and manage media effectiveness for products or regions • Perform cost-benefit analysis of your campaign • Sales Analytics • Evaluate Sales by products and channels • Identify relationships between customer categories & product preferences • Perform customer latency analysis to improve sales Sales Effectiveness Marketing Effectiveness CUSTOMER Customer Care Customer Loyalty • Care Analytics • Analyze complaints, suggestions, defect reports and inquiries • Identify organizational units with high contact incidences or slow response • Identify your most satisfied & dissatisfied customers • Loyalty Analytics. • Analyze customer value by purchase volume, frequency and revenue • Analyze attrition by a variety of measures – product, region and demographics • Proactively contain and avoid customer attrition • Customer Profiling (Segmentation) • Provides customer base categorization • Qualitatively groups similar customers by demographics • Quantitatively groups similar customer by behavior

  42. What if you could… Identify and respond to changes in traffic patterns? Determine your most profitable customers and channels? Anticipate customer churn by customer attributes? Discover trends in network repair? Identify your most successful marketing campaigns? Sybase Industry Warehouse StudioSupports Telco Analytics! Key Subject Areas • Customer Loyalty • Sales Analysis • Marketing Campaign Analysis • Customer Care • Customer Profiling • Financials • HR Analysis • Generic Call Facts • Seasonal Analysis • Product Profitability • Customer Profitability • Channel Profitability • Residential Traffic • Business Traffic • High Volume Analysis • Rated & Unrated Calls

  43. What If You Could ... Target marketing requirements for your customers as well as your advertisers? Integrate existing products such as ROP, preprints, direct mail, telemarketing, Internet, etc into customer focused messages? Understand the attributes of your customers as to both demographic and lifestyle data? Understand your target audience for circulation and advertising? Sybase Industry Warehouse StudioSupports Media Analytics! Key Subject Areas • Customer Loyalty • Sales Analysis • Marketing Campaign Analysis • Customer Care • Customer Profiling • Financials • HR Analysis • Customer Complaints • Home Delivery Analysis • Product Profitability • Customer Profitability • Channel Profitability • Subscriber Analysis • Advertiser Analysis • Advertisement Analysis • Advertising Sales Analysis

  44. Sybase Industry Warehouse StudioSupports Financial Services Analytics! • What If You Could ... • Analyze and Profilecustomers based on their product holdings? • Determinegeographic usage patterns of products? • Identifycharacteristics of profitable responders to recent marketing campaigns? • Rankcustomers by profitability and risk? • Cross-sellfinancial products to their checking customers? • Analyzethe purchasing habits of profitable credit card holders? Key Subject Areas • Customer Loyalty • Sales Analysis • Marketing CampaignAnalysis • Customer Care • Customer Profiling • Financials • HR Analysis • Customer Complaints • Product Profitability • Customer Profitability • Channel Profitability • Account Analysis • Profitability • Types • Investment Analysis

  45. What If You Could ... Identify customers likely to increment their policies within a year? Identify the total claims liability after a natural disaster? Analyze the demographics and psychographics of your customers? Identifycustomers who will be experiencing a major life event such as marriage, birth or retirement? Develop an up-to-date view of the total relationship with your customers? for Insurance Sybase Industry Warehouse StudioSupports Insurance Analytics! Key Subject Areas • Product Profitability • Customer Profitability • Channel Profitability • Underwriting & Policy Analysis • Reinsurance • Payments • Claims Analysis • Agent KPIs • Customer Loyalty • Sales Analysis • Marketing Campaign Analysis • Customer Care • Customer Profiling • Financials • HR Analysis • Customer Complaints

  46. What If You Could ... Enhance customer (member) relationship management? Analyze claims-based experience and service utilization data in detail? Improve provider management? Respond to government adherence reporting requirements? Address HEDIS and HIPAA reporting requirements? Perform disease management? Reduce fraud? Sybase Industry Warehouse StudioSupports Healthcare Analytics! Key Subject Areas • Customer Loyalty • Sales Analysis • Marketing Campaign Analysis • Customer Care • Customer Profiling • Financials • HR Analysis • Customer Complaints • Provider Anlaysis • Group Analysis • Charges • Drugs • Laboratory tests • Encounters • Facilities • Usage • Payment Details • Ailment • Recovery

  47. Minimize Risk • Reduce Cost • Enterprise Scope • Clear ROI Business Value • First Application Running 60-120 days • Dramatically Reduce the Effort • Provides Unlimited Expandability Time Value • Database Independent[Oracle, DB2, Informix, Microsoft, ASE, Sybase IQ] • Based on Proven Deployment • Commoditized Application • Integrated Environment Technical Value Sybase Industry Warehouse Studio Value Proposition

  48. Sybase Power Designer Multi-Dimensional Design Tool TABLE TABLE Industry-specific Data Models Sample Data General - Representative IWS Analytic Applications TABLE TABLE TABLE • Customer Analytics • Campaign Analysis • Sales Analysis • Customer Profiling • Customer Care • Loyalty Data Warehouse “Open RDBMS*” BI Partners Implementation Protocol • Industry Analytics • Infrastructure • Telco/ Utilities • Media • Banking • Credit Card • Capital Markets • Insurance • Healthcare Systems Integrators Guide Project Plan ORACLE, IBM, MICROSOFT, NCR, SYBASE, etc. ETL Tool • Business Analytics • Telco EBA • Telco BDA • Media Circulation • Media Advertising Warehouse Control Center Meta Data Management (i.e.: Informatica) Sybase Industry Warehouse Studio Packaged Data Warehouse Infrastructure NOTE: YELLOW box items are NOT included in the IWS

  49. Others: Others: Legacy APPS Others: SAS LOB APPS GROUP 1 Flat Files Other Data Marts IBI Focus External Data Sybase Solutions in End-to-end BI Platform Sources Decisions Integrate Organize Analyze Present Sybase ASE

  50. ERICSSON Guardian Life Insurance Scottish Widows Insurance Select List of Global Business Intelligence Client and Partners

More Related