Presentation Transcript

1. Why IWA Changes the World

   Session: A12 5/17/11, 3:30 – 4:30PM

2. Why IWA Changes the World(of Business Intelligence)

   Craig Humphreys PreferredPartner.com craig.humphreys@preferredpartner.com (866) 885-3249 x904
3. What is IWA? Informix Warehouse Appliance Part of Informix Ultimate Warehouse Edition Completely changes the “game” of business intelligence (BI) “real-time” transactional warehouses “thinking-time” analytics “task-time” query responses IWA Changes the World - Session A12
4. Trends: Key Drivers of Data Warehouse Change This is just a lot of data that says: Executives don’t get: Answers Analysis Insights When they want How they want The Data Warehouse Institute - 2010 IWA Changes the World - Session A12
5. Great Technology, Simple To Use Install Set up loads Often from other RDBM’s via Informix HPL Typically daily loads (no trickle-loading yet) Eliminating aggregation is a compelling option Redirect BI tool connect string Experience the performance IWA Changes the World - Session A12
6. Why is IWA so important? For the first time in history: Affordable Datamartscan be built, Which answer executive-level questions, At the time they are asked, “Thinking Time” Transactional-Datamartcapabilities are now possible E.g. full market-basket and historical analysis and coupon/incentive offering at the time of checkout IWA Changes the World - Session A12
7. BI, Redefined BI has historically been about: Reporting, usually inflexible Long cycle times for analytical results Little to no opportunity for optimization IUWE changes the game Real-time analytics are possible Supermarket checkout, Financial analytics, Police stops, Security screening, Health care usage analytics, Merger integration analysis, … the possibilities are endless IWA Changes the World - Session A12
8. Why does “Thinking-Time” Matter? Insight TRIZ, the Russian art of creative thinking, teaches us that solutions are developed through brainstorming, asking questions, getting ideas, and iterating Understanding Until IUWE/IWA came along, this kind of interactive Q&A/brainstorming simply wasn’t possible or affordable. IWA changes everything… Question IWA Changes the World - Session A12
9. BI Challenges and Issues Cannot provide business-time responses to complex queries Cannot give the executive/analyst “thinking-time” responses to ad-hoc queries i.e. BI is currently: Too expensive or Too Slow (or both) And, doesn’t economically provide answers to all the executive-level questions about an organization IWA Changes the World - Session A12
10. Today’s BI Process Complexand expensive report/query development process Business users explain what they want to report-writers Problems/organizations/processes change, but reports are static Updates and fresh reports are like the old JCL card deck code/debug process Design Implement Hope the output answers the questions IWA Changes the World - Session A12
11. Trends: Key Drivers of Data Warehouse Change The Data Warehouse Institute - 2010 TOO SLOW SCALABILITY INFLEXIBLE IWA Changes the World - Session A12
12. What do Executives want from BI? Executives want their problems solved That’s it So, BI ideally should provide a capability to accept questions and provide answers to those questions in “thinking time” Not hours or days or weeks or months later Now, while the issue is at hand IUWE is the first affordabledatamart that can provide this level of performance IWA Changes the World - Session A12
13. Typical Data Warehouse Architecture IWA Changes the World - Session A12
14. Third Generation of Database Technology According to IDC’s Article (Carl Olofson) – Feb. 2010 1st Generation: - Vendor proprietary databases of IMS, IDMS, Datacom 2nd Generation: - RDBMS for Open Systems, dependent on disk layout, limitations in scalability and disk I/O - Database tuning by adding updating stats, creating/dropping indexes, data partitioning, summary tables & cubes, force query plans, resource governing 3rd Generation: IDC Predicts that within 5 years: Most data warehouses will be stored in a columnar fashion Most OLTP database will either be augmented by an in-memory database (IMDB) or reside entirely in memory Most large-scale database servers will achieve horizontal scalability through clustering IWA Changes the World - Session A12
15. Example of 2nd Generation Database Disk I/O Issue IWA Changes the World - Session A12
16. How Oracle/Exadata Solves That Problem:Add an I/O Layer at High Cost Oracle’s Solution is technically very interesting. However, the cost, at 10x-20x IUWE, makes a positive ROI hard to achieve on the client’s Data Warehouse investment IWA Changes the World - Session A12
17. What if we could… Instantaneously obtain all criminal “event” data related to a traffic stop? Get instant answers to questions abouthealth-care usage, delivery, performance, utility, efficacy, and cost information across huge populations? And… do it affordably? IWA Changes the World - Session A12
18. Informix Warehouse Accelerator Configuration SQL Queries (from apps) TCP/IP Informix Warehouse Accelerator IDS SQL (via DRDA) Query Processor Query Router Results Compressed DB partition Data Warehouse Bulk Loader IDS: Routes SQL queries to accelerator User need not change SQL or apps. Short queries can continue to run in IDS Informix Warehouse Accelerator: Connects to IDS via TCP/IP & DRDA Analyzes, compresses, and loads Copy of (portion of) warehouse Processes routed SQL query and returns answer to IDS IWA Changes the World - Session A12
19. 7 7 1 1 6 6 2 2 5 5 3 3 4 4 Breakthrough Technologies for Performance Extreme Compression Required because RAM is the limiting factor. Row & Columnar Database Row format within IDS for transactional workloads and columnar data access via accelerator for OLAP queries. Multi-core and Vector Optimized Algorithms Avoiding locking or synchronization In Memory Database 3rd generation database technology avoids I/O. Compression allows huge databases to be completely memory resident Predicate evaluation on compressed data Often scans w/o decompression during evaluation Frequency Partitioning Enabler for the effective parallel access of the compressed data for scanning. Horizontal and Vertical Partition Elimination. Massive Parallelism All cores are used within used for queries IWA Changes the World - Session A12
20. Number of Occurrences Rare values CommonValues Compression: Frequency Partitioning Trade Info (volume, product, origin country) Column Partitions Histogramon Origin Vol Prod Origin ChinaUSA GER,FRA,… Rest Origin Top 64 traded goods – 6 bit code Cell 1 Cell 3 Cell 4 Product Rest Cell 2 Cell 5 Cell 6 Histogram on Product Table partitioned into Cells Field lengths vary between cells Higher Frequencies  Shorter Codes (Approximate Huffman) Field lengths fixed within cells IWA Changes the World - Session A12
21. Mapping Freq-Tuples to Fields 0110 10110 0110 1110110 01100 001101011 Cell 1 Cell 3 Cell 4 1 6 Cell 2 Cell 5 Cell 6 Compressed records are processed in “vector” format, dramatically increasing query processing speed. Data is processed in compressed format! Table partitioned into Cells IWA Changes the World - Session A12
22. 11111 0 1111 0 Simultaneous Evaluation of Equality Predicates State==‘CA’ && Quarter == ‘Q4’ CPU operates on 128-bit units Lots of fields fit in 128 bits These fields are at fixed offsets Apply predicates to all columns simultaneously! Translate value queryto Code query State==01001 && Quarter==1110 State Quarter Row … … … … & Mask == Selectionresult 01001 0 1110 0 IWA Changes the World - Session A12
23. Informix Warehouse AcceleratorOverview . Query parsing and matching to the Optimizer. Routing query blocks. IDS . Worker Processes . Coordinator Process Orchestrating the distributed tasks like Load or Query execution Have all the data in main memory spread across all cores. Do the compression and query execution. IWA Changes the World - Session A12
24. Characterized by: “Star” or “snowflake” schema: Region Brand City Store Product SALES Month Category Period Quarter Target Market: Business Intelligence (BI) Dimensions Fact Table Complex, ad hoc queries: Look for trends and exceptions to make actionable business decisions Touch large subset of the database (unlike OLTP) Involve aggregation functions (e.g., COUNT, SUM, AVG,…) And require results FAST! IWA Changes the World - Session A12
25. What IWA is Designed For Selective, fast scansover large (fact) tables Joinswithsmaller Dimension tables OLAP-style queriesover large facttables in relational starschemawithgroupingandaggregations SELECT PRODUCT_DEPARTMENT, REGION, SUM(REVENUE) FROM FACT_SALES F INNER JOIN DIM_PRODUCT P ON F.FKP = P.PK INNER JOIN DIM_REGION R ON F.FKR = R.PK LEFT OUTER JOIN DIM_TIME T ON F.FKT = T.PK WHERE T.YEAR = 2009 AND R.GEOID = 17 AND P.TYPEID = 3 GROUP BY PRODUCT_DEPARTMENT, REGION IWA Changes the World - Session A12
26. Business-Time Analytics w/IUWE! Alameda County/Livermore PD is now collecting information from: Traffic incidents and police reports Bookings Local and regional databases and CAD/RMS systems And making this information available from a simple “Google-like” query Even for Mobile Data Terminals and iPads Police officers call this “Link Analysis” This is a solution to the “Polly Klass” problem IWA Changes the World - Session A12
27. How Patrol Queries Work Today Officer contacts Dispatch Dispatch manually types into disparate systems Slow, error-prone, lengthy queue wait times State County Sheriff Dept Specialty IWA Changes the World - Session A12
28. Why LPD Chose IUWE Affordable As fast as decision-time at a traffic stop Simple to integrate with existing data sources Supports low-bandwidth Verizon links Custom application for query/results to minimize network traffic A truly excellent solution developer! IWA Changes the World - Session A12
29. LPD “Link Analysis” Diagram CAD/RMS IDS HPL ETL from RDBMs (including sound-matching algorithm) Google-like query-pattern analyzer application, extremely lightweight results Daily-to-Hourly Loads County Verizon 3G Link State IDS 11.7/IWA in single Linux server IWA loaded daily from IDS Future: load hourly with new version or ping-ponged systems IWA Changes the World - Session A12
30. Law Enforcement Homophone Problem Sindy Cindy Cindi Sindee Etc… “Soundex” algorithm converts all names to homophone equivalents during ETL. All queries are converted to Soundex values before comparison. So, Sindy == Cindi IWA Changes the World - Session A12
31. IWA in Health Care BLN-LLC A health care consultancy/data service provider Provides analytics for health care delivery systems, efficacy, and employee health remediation programs Typical client saves 3-7% on health care costs the first year Major clients include AT&Tand Rolls-Royce Determined that a major, unmet need among large-scale consumers of health care is comprehensive analytics To improve health and productivity To decrease organizational health care costs To improve the efficiency of health care delivery systems IWA Changes the World - Session A12
32. Why BLN chose IUWE Because…. Nothing else (affordable) was fast enough Existing data sources could be imported nightly Existing OLTP systems remained unaltered Query patterns, usage, and types could change constantly at the whim of the user Responses are in “thinking time” Small analytical findings are valuable A ¼% reduction in health-care costs in a large organization is a LOT of money IWA Changes the World - Session A12
33. Health Care Data is Complex Comes from many sources Interpretation and normalization errors are rampant Errors are common IWA Changes the World - Session A12
34. Health Care Data Challenges Disparate systems ETL is very complex No reliable data model exists, typically A procedure != A procedure A shift != A shift Etc… Huge variation in data formats High rate of data entry and data recording errors Patient behavior rarely matches health care provider’s intent IWA Changes the World - Session A12
35. PreferredPartner’s Role PD Link Analysis Designed schema Developed ETL import system Built light-weight UI for officers in the field Designed and maintain data security capabilities Configured and optimized networks to support the application BLN Health Analytics Developed import scheme by data source Set up secure communication and integration with data providers Developed PII cleansing mechanism (HIPAA) Providing ongoing schema updates and query tools Hosting and operating the system IWA Changes the World - Session A12
36. System Attributes PD Link Analysis Approximately 20-50GB of data per jurisdiction/region Approximately 2,000-10,000 link-analysis queries per day Average response < 2 seconds, per-query < 1 second BLN Health Analytics Cost-reduction data ranges in size from 0.2TB to 3TB of data per institution Queries run from sub-seconds to several minutes The normal assortment of standard trend, efficiency, and utility reports are run nightly IWA Changes the World - Session A12
37. A “Thank You” To Fred Ho, Program Director, Competitive Technologies, IBM For early support with IUWE/IWA For product slide content used in this presentation (the little logos you saw) For being part of the team to produce one of the most exciting technologies in years! IWA Changes the World - Session A12
38. Questions ?!? IWA Changes the World - Session A12