sql 2005 customer evidence n.
Skip this Video
Loading SlideShow in 5 Seconds..
SQL 2005 Customer Evidence PowerPoint Presentation
Download Presentation
SQL 2005 Customer Evidence

Loading in 2 Seconds...

play fullscreen
1 / 26

SQL 2005 Customer Evidence - PowerPoint PPT Presentation

  • Uploaded on

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.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'SQL 2005 Customer Evidence' - meli

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sql 2005 customer evidence

SQL 2005 Customer Evidence

Contact: Ram Ramanathan

sql server 2005 tb customers data warehousing w relational query
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
sql server 2005 tb customers general vldb dw bi workloads
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
sql server 2005 tb customers data warehousing w relational query1
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
sql server 2000 tb customers general vldb dw bi workloads
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
sql server 2000 tb customers general vldb dw bi workloads1
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
sql server 2000 tb customers private nda references
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
how do you fit 22 million books into 1 data warehouse
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
barnes noble retail dw customer project profile
Barnes & Noble Retail DWCustomer & Project Profile

Live on SS2005!


  • 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


  • 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
barnes noble retail dw dw architecture
Barnes & Noble Retail DWDW Architecture

Live on SS2005!




POS transactions






Inserts / Updates








Inventory deltas














first premier bankcard dw technical profile
First Premier Bankcard DWTechnical Profile


  • 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
first premier bankcard dw data flow
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.

first premier bankcard dw data flows
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.

first premier bankcard dw change control
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.

crossmark data warehouse system architecture

Microsoft Excel

Adobe Acrobat



Presentation Layer

SQL Server 2005 Reporting Services

Report Portal Framework



Weekly Transactions

Volumetrics ODS52-week History


Report & Portal Information

Data Processing Layer

SQL Server 2005 Integration Services

Transformation Layer




Data Sources

ACNielsen Volumetrics

CROSSMARK Data WarehouseSystem Architecture

Live on SS2005!


CROSSMARK Data WarehouseSS2005 Solution Profile

Live on SS2005!


  • 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
talbots retail data warehouse technical profile
Talbots Retail Data WarehouseTechnical Profile


  • 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


  • 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
clalit health patient dw ss2005 solution profile
Clalit Health Patient DWSS2005 Solution Profile


  • 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


  • 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
clalit health patient dw before after informix migration

Business Objects

Clalit Health Patient DWBefore & After Informix Migration




UnisysSQL Server








Files for OLAP




for OLAP



Partitioned Unisys64 bit





SQL Server



SQL Server



SQL Server




8 CPU32 GB1.5 TB

4 CPU16 GB


clalit health dw subject areas
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
us veteran s health admin cdw technical profile
US Veteran’s Health Admin CDWTechnical Profile


  • 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


  • 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
vha data warehousing framework
VHA Data Warehousing Framework

Data Warehouse System

Business Intelligence System

Closed Loop Information System















Query, Reporting,

Analysis, and

Data Mining










Extract, Transform, Load





Prog Office

Data Marts


  • Program Offices
  • Pharmacy Benefits
  • Prosthetics
  • Dental


Data Marts



Value Added Data





Acquire Populate Create Access DataWarehouseMarts 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

Dual Core


Future Expansion

Future Expansion

  • Discussed many scenarios
  • and obtained many
  • configurations.
  • 64-bit end-to-end