SQL 2005 Customer Evidence Contact Ramanathan SQL

SQL 2005 Customer Evidence Contact: Ramanathan

SQL Server 2005 TB+ Customers 5 TB Retail Data Warehouse on HP Superdome Data Warehousing w/ Relational Query SSIS, SSRS Uses SQL Server OLAP, 5. 3 TB Credit Card DW, 5 Mil card holders 300 users, OLAP, SSIS, SSRS, Office BSM 4 TB Consumer Packaged Goods (CPG) DW 350 users, complex relational query, SSIS & SSRS 2 TB DW, originally migrated from Informix 300 users, complex query, OLAP & Data Mining 2 TB of Clinical DW, 50 TB storage, 90 OLAP Marts US Dept of Veterans Affairs, 1200 facilities 2 TB Healthcare DW, HMC is part of Wellpoint Inc. Complex ETL, 800 SSIS packages, OLAP, RS

SQL Server 2005 TB+ Customers 3 TB document management system, Sybase move General VLDB & DW/BI Workloads State of Alaska Dept of Revenue, Permanent Fund 1 TB custom document management system (VS. Net) State of Washington Archive Department, 600 users 1 TB Real-time store reporting system (SSRS) Data loaded continually (MSMQ) and daily (SSIS) 2. 8 TB SAP Business Warehouse system In the process of migrating SAP R/3 OLTP to SS 2005 1. 5 TB BASEL II Compliance DW, complex SSIS Nationwide Building Society, UK 1 TB Staging Data Warehouse at Telecom Denmark CUBUS is massive BI system with up to 10, 000 users

SQL Server 2005 TB+ Customers 1 TB gaming data warehouse, 24 casino Data Warehousing w/ Relational Query query Heavy daily loads, relational 1 TB data warehouse for CRM support Runs core systems SQL Server, BASELL II on SS 2005 6 TB retail data warehouse from 700 stores, OLAP, RS 15 TB total across multiple instances 2 TB normalized DW, 250 concurrent BI users Nightly transaction loads: 900 TPS sustained 5 TB retail DW, 10 TB storage, data from 1000 stores Largest retailer in African continent, OLAP, RS, DTS 3 TB relational DW, DB 2 migration Heavy Complex Relational Query

SQL Server 2000 TB+ Customers 20 TB European digital payment archive system General VLDB & DW/BI Workloads Unisys Payments Services Archive 13 TB Geospatial DW, to reach 25 TB 4 TB data mart, 2500 field offices, 55 TB storage Multiple 1 TB instances, credit scoring analysis Uses complex OLAP & SQL Server ETL Used by 13, 000 agents in 220 offices Built portal with Visual Studio. NET, OLAP 700 restaurants, 30, 000 users, 700+ concurrent OLTP/LOB reporting on inventory & sales data Reporting on 1. 6 TB of data, 35 K+ Tx/Min 325 concurrent connections

SQL Server 2000 TB+ Customers 20 TB European digital payment archive system General VLDB & DW/BI Workloads Unisys Payments Services Archive 13 TB Geospatial DW, to reach 25 TB 4 TB data mart, 2500 field offices, 55 TB storage 1. 5 TB DW, plus multiple OLAP data marts Data feeds from JD Edwards ERP system 1 TB DW, Informix migration Heavy concurrent usage on OLAP 1. 7 TB DW, 2900 DW users ramping up to 5000 Runs on HP Superdome 6 TB Call Detail Record (CDR) DW, OLAP usage 20 -way HP Superdome

SQL Server 2000 TB+ Customers 15 TB credit card DW, 11 mil card holders Private NDA References 30 TB total storage, several TB+ instances 1 TB Retail DW, relational query + complex OLAP Acorn profitability analysis Package 1 TB relational instance with Complex OLAP Loss Prevention DW for store management Major RS operation on entire reporting tier Runs on 8 dedicated reporting servers Real-estate services, up to 10 K report runs/day Mortgage closing: 3000 checks (Reports) /day Monitoring strategic ventures Visual Studio. NET &. NET Framework

How Do You Fit 22 Million Books Into 1 Data Warehouse? “SQL Server 2005 gives us the performance we need at a price that is just far superior to anything else we’ve seen. ” Chris Troia, Chief Information Officer, Barnes & Noble World’s largest bookseller 821 bookstores, 7. 3 million retail items Needed improved business intelligence for merchandising and inventory planning 4. 5 TB end-to-end data warehouse running on SQL Server 2005 64 -bit Storing 3 years of transaction data; will grow to 5 years Insight enables better out -of-stock predictions Faster access to information Deeper view into key performance indicators and trends Better decisions for greater profitability Improved customer experience

Barnes & Noble Retail DW Customer & Project Profile Scenario/Business Largest bookseller in the USA, 850 stores, plus online Live on SS 2005! 40 K employees, 1. 5 mil titles across all stores, . 75 mil at www. bn. com Retail Data Warehouse Broad use of SQL Server BI, DW & cubes started with SQL Server 2000 Moved relational DW to SQL Server 2005 with June CTP now First went live with SQL Server Integration Services in 2004 on Oct CTP Loading Data from mostly Oracle sources Workload/Architecture 2 TB sales & inventory data warehouse on SQL Server 2000 Currently holds 3 years of transactions, will build up to 5 years SSIS using Slowly Changing Dimension transform, lookups, and configurations Will test Data Mining on IDW 13 for a DM project Out-of-stock prediction using Decision Trees, potential for enhanced sales of 150 K/week OLAP is planned to move pre-RTM: OLAP Subjects: Store sales, Store inventory, Distribution Center inventory Key dimensions: Store, Item, Time, Demographic, Buyer, Vendor, Customer DB Platform Windows Server 2003 64 -bit HP Superdome w/20 CPU partition for DW, 20 TB EMC SAN

Barnes & Noble Retail DW DW Architecture Oracle SRS database Live on SS 2005! POS transactions Dimension Updates Inserts / Updates Staging database IS IS Relational data warehouse Inventory deltas Oracle IMM database Sales Store inventory Proclarity interactive reports Distr. Center inventory Reporting Services reports Strategy

First Premier Bankcard DW Technical Profile Scenario/Business Specialized high-risk credit card issuer, 10 th largest in the USA 5 million card holder portfolio, 25 K average new bookings per month Runs 100% of business on SQL Server Great cost savings and business agility Looking at SS 2005 for entire BI stack: SSIS, Partitioning, SSRS & SSAS Workload/Architecture 4. 2 TB single instance for customer data Growth of approximately 300 GB per month, using DTS for data loading Planning to upgrade this to SQL Server 2005 Ad hoc relational query workload 30 -100 concurrent in-flight queries, 40 concurrent users at a time Analysis Services OLAP usage through Proclarity & Outlooksoft 25 -30 cubes, back-end DW is star-schema/snow-flake Broad adoption of Reporting Services across the company 500+ unique reports, 1500+ report users Core 1. 5 TB OLTP LOB system runs the business 2500 customer reps connected all day, 200+ TPS frequently Planning to upgrade this to SQL Server 2005 DB Platform Windows Server 2003, SQL Server 2000 64 -bit HP 64 -bit 12 -way, 8 -way relational, 4 -way OLAP, 27 TB EMC SAN

First Premier Bankcard DW Data Flow Data is imported on a nightly basis from the different data sources and then standardized and validated through the Extract, Transform and Load (ETL) process. After the data has been validated, it is loaded into the data warehouse. It is then available to the Reporting and Analytic Departments for data analysis and development of reports.

First Premier Bankcard DW Data Flows At the end of every month, FDR transfers a cardholder master database containing months of transactions conducted by customers of PBI to the PBI network, where the information is downloaded to the data warehouse. PBI uses SQL Server 2000 Reporting Services to create standardized company reports.

First Premier Bankcard DW Change Control The PBI data warehouse uses a 64 -bit application with 16 GB of memory. The diagram layout emphasizes the testing and development environment more than the 64 -bit technology.

CROSSMARK Data Warehouse System Architecture Live on SS 2005! Presentation Layer Microsoft Excel Adobe Acrobat HTML HTTP SQL Server 2005 Reporting Services Report Portal Framework Data Sources Transformation Layer Data Processing Layer OLE DB Staging Weekly Transactions Volumetrics ODS 52 -week History Reports Report & Portal Information SQL Server 2005 Integration Services TDLinx Sales. Trak ACNielsen ICE ACNielsen Volumetrics

CROSSMARK Data Warehouse SS 2005 Solution Profile Scenario/Business Live on SS 2005! Consumer Packaged Goods (CPG) services provider and data processor 16, 000 employees, one of the largest CPG services companies in the USA Employs in-store teams across the country Over 4 million store visits per year providing various inventory, stock and efficiency services CROSSMARK retains top CPG manufacturers as customers Processes data on 60 K UPC codes from sales in over 30 K stores Workload/Architecture 4 TB of data in a single SQL Server instance 25 TB of raw storage Uses Table Partitioning feature to increase manageability and reduce complexity Ad hoc complex query workload through 350 users (85 full-time power users) SQL Server Integration Services for Data loads Data imported from 4 sources including, biweekly AC Nielsen data loads Permitted expansion of DW from 13 weeks to 1 yr of history SQL Server Reporting Services DB Platform Windows Server 2003, separate servers for ETL, Rel DW and Reporting HP Pro. Liant DL 585 w/4 dual-core 2. 4 GHz AMD 64 -bit Opteron & 16 GB of RAM HP Storageworks

Talbots Retail Data Warehouse Technical Profile Scenario/Business Leading retailer & E-tailer with global catalog operation 1000+ stores in USA, Canada & UK WW catalog & online operation reaching 46 mil customers in 140 countries Uses SQL Server as strategic database along with IBM mainframe Key e-commerce and catalog order systems on SQL Server 2005: Retail Data Warehouse Established for business agility in retail operation Assess store operation efficiency Workload/Architecture 1 TB of data, using SQL Server 2005 Table Partitioning feature 36 -month of transaction history, partitioned using SQL Server by month Dual trickle plus batch data loading with SSIS Continuous real-time trickle data, approx 1 million unaudited sale transactions/day SSIS batch loading of audited sales data, 1 million rows in 3 hour batch-window 2 million rows total added to the database every day Extensive usage of SS 2005 Reporting Services Parametrized reports from 50+ users hit the database all day DB Platform Windows Server 2003, HP 64 -bit Itanium

Clalit Health Patient DW SS 2005 Solution Profile Scenario/Business Largest HMO in Israel, 2 nd largest WW, covers 60% of pop (3. 7 mil) 14 hospitals, 1400 clinics, 6, 000 doctors, 500 pharmacies, 32 K employees IBM Informix/Sun 10000 Solaris migration in 2002 67% TCO savings, 25% perf improvements: 15 x in some OLAP queries Also runs main clinic OLTP system on SQL Server Migrating 1000 local SQL Servers in clinics to central DB, 2 TB expected Workload/Architecture 5 TB of total data, multiple servers & instances 2 TB in DW SQL Server instance Ad hoc query workload (Business Objects), 20 concurrent in-flight often 300 users (100 full-time) users, 10 K queries/month OLAP cubes using Microsoft Analysis Services 3000 OLAP users, 45 K queries/month ISV front-end app: Procalrity ETL: Ascential Software’s Datastage ETL from S/390 mainframes Data Mining: Patient Treatment Cost Prediction Working with local DM specialist partner G-Stat DB Platform Windows Server 2003, Unisys 12 -way, 64 -bit, EMC SAN

Clalit Health Patient DW Before & After Informix Migration Before: Legacy Systems SUN 6000 Informix Source Files Production DWH Unisys SQL Server Files for OLAP Replicated DWH for OLAP After: Legacy Systems Staging DWH SUN 10000 Informix Proclarity Partitioned Unisys 64 bit Unisys 32 -bit SQL Server Unisys 64 -bit SQL Server Business Objects Staging DWH Production OLAP DWH 8 CPU 32 GB 1. 5 TB 4 CPU 16 GB Proclarity

Clalit Health DW Subject Areas Health Services & Expenses: Human Resources: • Manpower • Payroll • Outside contractors Customer Data: • • • Demographic data Call Center Mailing Churn analysis Complaints Logistics: • Supplies • Purchases • Specialized medical equipment • • Medical services Laboratory Hospital services Medication Specialists Medical imaging Chronic illnesses and more… Integrative Universes: • Clinic budget • Medical KPI’s • Medical services

US Veteran’s Health Admin CDW Technical Profile Scenario/Business United States Department of Veterans Affairs, Veteran’s Health Administration 172 Hospitals, 1260+ total points of care (including clinics & nursing homes) 5 million patients, $26 Billion budget, 200 K employees VHA Corporate Data Warehouse Uses end-to-end Microsoft SQL Server and BI technologies Currently in the midst of 50 TB project to add new relational ODS and EDW back-ends Workload/Architecture 4 TB largest instance, 17 TB storage Heavy SQL Server Analysis, Reporting and Transformation Services 100’s of cubes and subject areas Main Data Marts: Lab (53 tests), Radiology, Pharmacy, Prosthetics Outpatient: (Appointments, Encounters, Primary Care Panels) Inpatient: (Movement, Discharges) Other: Non VA Care, Human Resources, Financial Accounting Planned: Nursing, Dental, Purchasing, Health Data Repository Presentation Tier: Pro. Clarity Analytics & Dashboard Servers, MS Map. Point DB Platform MS Windows Server 2003 Enterprise Edition 4 x HP RX 5670 64 -bit 4 -way Itianium-2 Servers, 32 GB RAM, HP EVA SAN

VHA Data Warehousing Framework Closed Loop Information System Source Systems Vist. A HDR Metadata Repository Conformed Dimensions OP Data Consultants VHAc ADR ABC G/L VHAaf Do. D CDC CMS 1 Extract, Transform, Load Diabetes Data Warehouse Common Query, Reporting, Analysis, and Data Mining Tools Wait Times Other Research Data Marts Acquire Data VISN Warehouses 2 Prog Office Data Marts Program Offices PBM Value Added Data Populate Warehouse 3 Create Marts • Pharmacy Benefits • Prosthetics • Dental 4 Access Information VHAc – VHA clinical systems VHAaf – VHA administrative & financial systems

VHA Dimensional Map Standardized Type/Size conventions Naming conventions Verified “Gold” standard Business rules Optimized Primary keys Indexed Refreshed

VHA CDW Hardware Architecture

Dual Core Future Expansion • Discussed many scenarios and obtained many configurations. • 64 -bit end-to-end Future Expansion