1 / 26

SQL 2005 Customer Evidence

SQL 2005 Customer Evidence. Contact: Ram Ramanathan . SQL Server 2005 TB+ Customers Data Warehousing w/ Relational Query. 5 TB Retail Data Warehouse on HP Superdome Uses SQL Server OLAP, SSIS, SSRS. 5.3 TB Credit Card DW, 5 Mil card holders 300 users, OLAP, SSIS, SSRS, Office BSM.

meli
Download Presentation

SQL 2005 Customer Evidence

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. SQL 2005 Customer Evidence Contact: Ram Ramanathan

  2. SQL Server 2005 TB+ CustomersData Warehousing w/ Relational Query • 5 TB Retail Data Warehouse on HP Superdome • Uses SQL Server OLAP, SSIS, SSRS • 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

  3. SQL Server 2005 TB+ CustomersGeneral VLDB & DW/BI Workloads • 3 TB document management system, Sybase move • 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 SS2005 • 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

  4. SQL Server 2005 TB+ CustomersData Warehousing w/ Relational Query • 1 TB gaming data warehouse, 24 casino • Heavy daily loads, relational query • 1 TB data warehouse for CRM support • Runs core systems SQL Server, BASELL II on SS2005 • 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, DB2 migration • HeavyComplexRelationalQuery

  5. SQL Server 2000 TB+ CustomersGeneral VLDB & DW/BI Workloads • 20 TB European digital payment archive system • 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, 35K+ Tx/Min • 325 concurrent connections

  6. SQL Server 2000 TB+ CustomersGeneral VLDB & DW/BI Workloads • 20 TB European digital payment archive system • 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

  7. SQL Server 2000 TB+ CustomersPrivate NDA References • 15 TB credit card DW, 11 mil card holders • 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 10K report runs/day • Mortgage closing: 3000 checks (Reports) /day • Monitoring strategic ventures • Visual Studio .NET & .NET Framework

  8. 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 • Faster access to information • Deeper view into key performance indicators and trends • Better decisions for greater profitability • Improved customer experience • 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

  9. Barnes & Noble Retail DWCustomer & Project Profile Live on SS2005! Scenario/Business • Largest bookseller in the USA, 850 stores, plus online • 40K 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 IDW13 for a DM project • Out-of-stock prediction using Decision Trees, potential for enhanced sales of 150K/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

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

  11. First Premier Bankcard DWTechnical Profile Scenario/Business • Specialized high-risk credit card issuer, 10th largest in the USA • 5 million card holder portfolio, 25K average new bookings per month • Runs 100% of business on SQL Server • Great cost savings and business agility • Looking at SS2005 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

  12. First Premier Bankcard DWData 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.

  13. First Premier Bankcard DWData 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.

  14. First Premier Bankcard DWChange 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.

  15. Microsoft Excel Adobe Acrobat HTML HTTP Presentation Layer SQL Server 2005 Reporting Services Report Portal Framework OLE DB Staging Weekly Transactions Volumetrics ODS52-week History Reports Report & Portal Information Data Processing Layer SQL Server 2005 Integration Services Transformation Layer TDLinx SalesTrak ACNielsenICE Data Sources ACNielsen Volumetrics CROSSMARK Data WarehouseSystem Architecture Live on SS2005!

  16. CROSSMARK Data WarehouseSS2005 Solution Profile Live on SS2005! Scenario/Business • 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 60K UPC codes from sales in over 30K 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 ProLiant DL585 w/4 dual-core 2.4 GHz AMD 64-bit Opteron & 16 GB of RAM • HP Storageworks

  17. Talbots Retail Data WarehouseTechnical 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 • 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 SS2005 Reporting Services • Parametrized reports from 50+ users hit the database all day DB Platform • Windows Server 2003, HP 64-bit Itanium

  18. Clalit Health Patient DWSS2005 Solution Profile Scenario/Business • Largest HMO in Israel, 2nd largest WW, covers 60% of pop (3.7 mil) • 14 hospitals, 1400 clinics, 6,000 doctors, 500 pharmacies, 32K employees • IBM Informix/Sun 10000 Solaris migration in 2002 • 67% TCO savings, 25% perf improvements: 15x 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, 10K queries/month • OLAP cubes using Microsoft Analysis Services • 3000 OLAP users, 45K 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

  19. Business Objects Clalit Health Patient DWBefore & After Informix Migration Before: SUN 10000Informix UnisysSQL Server SUN 6000Informix Legacy Systems ProductionDWH SourceFiles StagingDWH Files for OLAP OLAP Replicated DWH for OLAP Proclarity After: Partitioned Unisys64 bit Legacy Systems Unisys 64-bit SQL Server Unisys 64-bit SQL Server Unisys 32-bit SQL Server ProductionDWH StagingDWH OLAP 8 CPU32 GB1.5 TB 4 CPU16 GB Proclarity

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

  21. US Veteran’s Health Admin CDWTechnical 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, 200K 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: ProClarity Analytics & Dashboard Servers, MS MapPoint DB Platform • MS Windows Server 2003 Enterprise Edition • 4 x HP RX5670 64-bit 4-way Itianium-2 Servers, 32 GB RAM, HP EVA SAN

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

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

  24. VHA CDW Hardware Architecture

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

More Related