Data Warehouse Presentation Toto.Horvli@Teradata-NCR.com November - PDF document

Data Warehouse Presentation Toto.Horvli@Teradata-NCR.com November 10th 2004 A LARGE Data Warehouse � 30,000 users, 174+ applications � 296+ 2 nodes • Any question on any data from • 1016 - I ntel CPUs any user anytime (within • 1192 GB RAM Memory security and privacy constraints) • 242 TB raw disk – 10,864 drives • Enterprise data model – • 105 TB Max Perm addressable thousands of tables • ~ 36 GB/ sec interconnect • Exceeding 300K queries/ day bandwidth � 60% < 1 second • ~ 41 GB/ sec I / O bandwidth � 95% < 1 minute • > 650TB/ day max physical I / O VPROCs VPROCs VPROCs VPROCs VPROCs VPROCs VPROCs VPROCs VPROCs VPROCs VPROCs VPROCs VPROCs VPROCs VPROCs VPROCs Amps Amps Amps Amps Amps Amps Amps Amps Amps Amps Amps Amps Amps Amps Amps Amps 1

Data Warehouse & Data Warehousing Toto.Horvli@Teradata-NCR.com November 10th 2004 Enterprise Data Warehouse � Enterprise Data Warehouse: • An Enterprise Data Warehouse is a historical repository of detailed data used to support the decision-making process throughout the organization. I t spans multiple subject domains and provides a consistent view of data objects used by various business processes throughout the on-line enterprise environment. � Data Warehousing: • Data Warehousing is a process of building the data warehouse and leveraging information gleaned from analysis of the data with the intent of discovering competitive enablers that can be employed throughout the enterprise. � Traditional Data Warehousing focuses on reporting and extended analysis: • What happened • Why did it happen • What is expected to happen next 2

What is Traditional Data Warehousing? Traditional Data Warehousing is… … an integrated & logically consistent store of detailed data used to aid the decision making process. � Little to no interaction with customer or supplier channels � Primarily batch data feeds � Many “standard” reports … Late Arrivals: CHI: 21 LAX: 13 NYC: 8 Operational Data Batch and/or Warehouse Transactional Customer Impact: Systems Plat: 13 Gold: 5 Silv: 8 What is Traditional Data Warehousing? In addition, Traditional Data Warehousing is… Hmm… • Ad-hoc queries in support of strategic decisions… I wonder why • … but these questions are typically asked by a I’m loosing high margin business analyst based on their own experience customers? and intuition. • I n som e cases, the questions are asked too late to take profitable action… 13 Platinum Operational customers Data Batch and/or were affected Warehouse Transactional in S ept. Systems 3

What is Active Data Warehousing? Active Data Warehousing is traditional DW plus… … very current detailed data (combined with historical data) for strategic, tactical, & event driven business decision making. � Timely updates - close to real time � Pre-designed triggers & queries designed to detect significant events. Bob Smith is a � Event Notification Services (ENS) PLAT INUM � Assisted decision making via analytic applications CUST OMER � Tracking of tactical decision and actual results and will miss his connecting flight!! Operational Continuous and/or Active Plane is Transactional ENS re-routed Data Systems Warehouse Batch Make an offer CRM Notify Call Center What Makes a Warehouse “Active” ? � I nter-Active • Integrated customer channels • Integrated supplier channels • Integrated data analysis � Re-Active • Manage inventory Your current rate • Manage product cycles of production • Manage costs will not meet the forecasted seasonal demand � Pro- Active • Event Notification Services (ENS) • Automated marketing campaigns • Automated pricing • Automated replenishment 4

Active Data Warehouse � Active Data Warehouse (ADW): • A repository of detailed data required to support � Strategic decision-making: Long range decisions covering broad domains � Tactical decision-making: Short term decisions focused on a narrow topic � Event based decision-making: Decisions made as a result of an event • Tactical decision support often requires data freshness service levels that are much more aggressive than strategic decision support. • This more up-to-date data is integrated with historical data in the active data warehouse. • Data spans multiple subject domains and provides a consistent view of data objects used by various business processes throughout the online enterprise environment. Active Data Warehouse � Active Data Warehousing: • A process of building the active data warehouse • Leveraging information gleaned from analysis of the data with the intent of providing assisted predictive analysis • Delivering actionable information to decision-making agents (human or software) on a near real-time basis. • Automation of business processes and decision-making, where appropriate, through the use of event detection and software based business rules. 5

Strategic Decision Making Active data warehousing moves all analysis into the database to answer complex business questions quickly and with scalability... Businesses need to perform analysis for planning, forecasting, profiling, fraud detection, trending, and pattern analysis to identify the proper action based on business drivers. Tactical Decision Making Active data warehousing is also about supplying information to front-line decision makers... Businesses need repeatable, consistent execution of data-driven decisions by all constituencies, regardless of their number. 6

Traditional vs. Active Data Warehouse Dimension Traditional Active Query Types Strategic & Ad Hoc queries only Tactical, Strategic, and Ad Hoc queries Granularity of Temporal granularity is coarse. Temporal granularity is fine. Time Typically on the order of days. Typically on the order of seconds or minutes. Usage Capacity Limited number of concurrent Very large number of concurrent users and / Attributes users and / or concurrent queries. or concurrent queries. Usage Power users, Knowledge workers, Traditional users, plus… Attributes & Internal users. • Customers ( via touch-points or portals ) • Suppliers ( via B2B brokers or portals ) • Automated applications • Autonomous Agents Application Periodic Report Generation Traditional Applications, plus… Attributes • Deep analysis of data Ad-hoc queries used to answer • Optimization of quantitative models “new questions.” • Event driven notification & action • Rapid decision making ROI ROI is measured in course time Measurable ROI based on easily quantified units business metrics An Integrated, Centralized DW Solution Transactional Data Business & Technology - Consultation & Education Netw ork, Database , & System s Managem ent Data Optional Transform ation ETL Hub Metadata Logical Data Model Physical Data Base Design Centralized Data W arehouse: Data Transform Middlew are/ EAI Layer ELT Services ORDER ORDER NUMBER ORDER DATE STATUS ORDER ITEM BACKORDERED QUANTITY Detail CUSTOMER CUSTOMER NUMBER CUSTOMER NAME ORDER ITEM SHIPPED CUSTOMER CITY Co-located Data Layer CUSTOMER POST QUANTITY SHIP DATE External CUSTOMER ST CUSTOMER ADDR CUSTOMER PHONE Dependent ITEM CUSTOMER FAX ITEM NUMBER Dependent QUANTITY DESCRIPTION Mart Mart Logical Mart Data Dim ensional View Mart Layer View View Business and Operational Users Strategic Tactical OLAP Event-driven/ Data Users Users Users Closed Loop Miners 7

Enterprise Data Warehouse Evolution ACTI VATI NG � Query com plexity grows MAKE it happen! � Workload m ixture grows � Data volum e grows I nitiate � Schem a complexity grows OPERATI ONALI ZI NG � Depth of history grows W HAT I S happening? � Number of users grows W orkload Com plexity � Expectations grow PREDI CTI NG Event-Based W HAT W I LL Triggering Execute happen? Takes Hold Chasm Optim ize ANALYZI NG from static Continuous Update & W HY to dynamic did it happen? Time-Sensitive Queries Become Important decision- REPORTI NG Batch making W HAT Analytical Understand happened? Ad Hoc Modeling Grows Analytics Measure Increase in Continuous Update/Short Queries Ad Hoc Analysis Event-Based Triggering Primarily Batch & Some Ad Hoc Reports Data Sophistication Data Warehousing – The “Must Remember” List � A data warehouse is a solution to a business problem not a technical problem � The warehouse needs to constantly overcom e obstacles that are as yet undefined � “Mores Law”: m ore users wanting m ore applications that have m ore complex and varied analysis against m ore data with m ore frequent updates in a m ore timely manner. � The goal behind the warehouse is consistency and agreem ent , not just access � The foundation put in place determines the speed, and duration of the business evolution 8