1 / 32

<Insert Picture Here>

<Insert Picture Here>. DWH @ Quelle Neckermann - Germany. Reiner Zimmermann Product Management – Oracle DWH Development reiner.zimmermann@oracle.com - +41 79 237-7443. Presentation Agenda. Company Background DWH usage and applications DWH Architecture SLA Current Activities.

gazit
Télécharger la présentation

<Insert Picture Here>

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. <Insert Picture Here> DWH @ Quelle Neckermann - Germany Reiner Zimmermann Product Management – Oracle DWH Development reiner.zimmermann@oracle.com - +41 79 237-7443

  2. Presentation Agenda • Company Background • DWH usage and applications • DWH Architecture • SLA • Current Activities

  3. <Insert Picture Here> Company Background

  4. Facts (2003) Revenue Total : 15,3 Mrd. €Mailorder : 8,0 Mrd. € Employees : 80.046Mailorder : 28.557 Karstadt – Quelle Holding

  5. Facts (2003) Two strong brands • Nr. 1 mailorder company in germany • 30% market share in germany • 140 daughter companies • present in 22 european countries • 10.000 stationary, supporting units/shops in europe • 65 mio shipments / year • 38 mio main catalogues p. a. • 95% brand knowledge Kennzahlen (2002) Quelle - Mailorder

  6. <Insert Picture Here> DWH usage and applications

  7. CRM-Actions Campagn-Management Data Mining, Customer Scoring, Analysis MIS, Reporting Computing Power 30% 30% 30% Granular Data Warehouse Source Systems Usage of the DWH

  8. Sales Product-Management Complaints Complaints MISBusiness IntelligenceControlling MISBusiness IntelligenceControlling Granular Data Warehouse OperativeCRM Call-CenterService Call-CenterService Diversificationof marketing material Diversificationof marketing material Internet Internet DWH processes AnalyticCRM • Collection, Consolidation and Storage of all customer related transactions and information • add Product – Sales Information (analytical CRM) • allocation of targeted information to steer the CRM processes

  9. <Insert Picture Here> DWH architecture

  10. SUN E10k • EMC ... once upon a time .... a DWH platform was seached ... • Benchmark of several hardware vendors :- HP- Sun- Bull- Tandem- IBM- ....... • ... and the winner was :

  11. HP Superdome • HP EVA SAN ... some years later .... a new DWH platform was seached with a better price performance ratio ... • ... and the winner was :

  12. ... last year .... The system was too expensive – but the vendor was OK ... • So they evaluated alternatives • Oracle RAC was presented • The Amazon case was presented • ... and finally the solution was made ... • To replace the HP Superdomes with a Oracle RAC – Blade infrastructure

  13. Current DWH Infrastructure Mandantes/companies: • Quelle • neckermann.de • Mode und Preis • Madeleine • Elegance (incl. Bogner, …) • Quelle-Gruppen-DWH(Peter Hahn, KQV, …) Datenbases/Filesystems: • DBQDWH: • Quelle, „Mode und Preis“, Madeleine, Elegance • Quelle goup-DWH • ca. 5,8 TB, ca. 600 User • DBNDWH: • neckermann.de • ca. 3,1 TB, ca. 70 User • DBKTO: • Reporting – Customers - Finance Q/N • ca. 1,4 TB, ca. 120 User • DBMA: • Quelle foreign companies • ca. 0,4 TB, ca. 130 User • Filesystem: • ca. 2,5 TB

  14. Cluster Distribution of DWH Services Datenbaseon 11 nodes DBQDWH DBNDWH DBKTO DBMA DB-Structuron 2 nodes IAS/OID/SSO OS-Strukturon ~11 Knoten NFS, Filesystem TWS, SSH/SFTP XCOMM Testsystem on 1 node ENQDWH ENKTO, ENMAENSVSDWH Clusterware

  15. Quelle RAC Quelle RAC Quelle RAC Quelle RAC Cluster-Configuration (Standard) ne.de RAC Server DBMA Loadbalancer Quelle RAC Quelle RAC Quelle RAC ne.de RAC Server DBKTO Test Fibre-Channel Je 4xXeon64/3,66GHz16/32 GB Mem. • Database DBMA and DBKTO • IAS [Discoverer, OID (SSO / LDAP)] • Periphere Software • (XCOMM, SFTP, SSH,…) Quelle 3x EVA8000 249 discs á146GB neckermann.de

  16. Quelle RAC Quelle RAC Quelle RAC Quelle RAC Quelle RAC Quelle RAC ne.de RAC ne.de RAC ne.de RAC Server DBMA ne.de RAC Server DBMA Loadbalancer ne.de RAC Quelle RAC Quelle RAC Quelle RAC ne.de RAC Server DBKTO Loadbalancer Quelle RAC Quelle RAC Quelle RAC ne.de RAC Server DBKTO Test Cluster-Re-Configuration (Workload/Peak Mgmt)

  17. Quelle RAC Quelle RAC Quelle RAC Quelle RAC Quelle RAC Quelle RAC Quelle RAC Quelle RAC ne.de RAC Server DBMA Loadbalancer Quelle RAC Quelle RAC Quelle RAC ne.de RAC Server DBKTO Test Cluster-Re-Configuration (Testing) ne.de RAC Server DBMA Loadbalancer ne.de RAC Server DBKTO Test Test Test Test

  18. Hardware Architecture & Cost (Capex) • Nodes : 12x DL580 – each 15k EUR = 180.000 EUR (32MB RAM – Xeon64 3.66 Ghz) • Infrastructure / Switches / FC = 35k EUR(2 GB / I/O Throughput) • Disc Subsystem 3x EVA8000 – each 250k = 750k (17 TB Net capacity / Raid 1) • Total 1.000.000 EUR

  19. Software Architecture • OS : Suse Linux 64bit • Disk Layout : Oracle ASM • DB – Software : Oracle 10g • Backup & Recovery : Oracle RMAN / ILM (planned) • System Mgmt : OEM – Diag / Tuning / Change / Conf • DB-Options : Partitioning Option Real Application Cluster ETL Option (OWB)

  20. Goal of a (joined) Cluster Conceptual Goals • Consolidation of all Marketing-DWHs of mail-order devision • Building one platform for DWH-Applications Technological Goals • Basis for scalability of computing power and storage without completechange of HW / SAN - normal growth (add now nodes / add additional storage) - cope with short-term peaks through load balancing and reconfiguration of existing ressources. • Increase availability through fail-over scenarios • Increase availability through rolling upgrade of software • Ressource sharing of DB-near services (IAS – SSO – Applications) • Speed up system – 3x faster

  21. Goal of a (joined) Cluster Commercial Goals • Use of cheap HW – new Hardware ½ price BUT : watch out for Outsourcing concepts : (ex. Adidas) • mostly no cost savings for the application owner • Existing contacts in place • Talk with the outsources

  22. <Insert Picture Here> DWH SLA’s

  23. Backup / Recovery Concept (current) • Daily full backup on Tape using Oracle RMAN • Backup parallelized on 4 steams/nodes • duration 3.5 h • Disaster Recovery Scenario – full restore from tape

  24. Administration of the DWH • 3 shifts / 3 DBA per shift managing 100 Oracle DBs • DWH administration : - System / DB admin ~ 4 month / year ~ 0.3 FTE - Disk admin ~ 1 month / year ~ 0.1 FTE - B/R admin ~ 1 month / year ~ 0.1 FTE • Total DWH admin 0.5 FTE

  25. Volume evolution & influence of Administration • no visible influence • tables are range partitioned – hence query time is not influenced • old data / partitions are compressed – hence even positive effect on performance

  26. Downtime management • current SLA’s allow 2 days downtime • max planned downtime since cluster – 4h • max unplanned downtime since cluster – 0h • Upgrade clusterware and patches – rolling upgrades

  27. <Insert Picture Here> Current Activities

  28. Current Activities • customer Story w/ German marketing • technical story for DOAG / BI Magazine … • Participation @ EMEA Terabyte Club - Bologna

  29. More Information • Dr. Gernot Schreib Head DWH – Quelle AG gernot.schreib@quelle.de • Norbert Gösswein Head DB Management – AtosOrigin AG norbert.goesswein@atosorigin.com • Herbert Rossgoderer DWH Consultant – Oracle Germany herbert.rossgoderer@oracle.com • Reiner Zimmermann Product Manager – Oracle DWH Development reiner.zimmermann@oracle.com

  30. Quelle AG - Snapshot Business facts Technology facts • Quelle AG is the largest mail-order company in Germany with a market share of 30% and 142 subsidaries. Quelle has presents in 22 european countries with 10.000 mail-order supporting affilates • Quelle AG send 65 Mio orders and around 38 Mio main catalogues to clients every year and around 140 Million special catalogues. • Quelle has a 95% brand awareness in germany. • Migration from Sun E10k / HP Superdome / HP DL580 • 12 node RAC cluster - HP DL 580 • Storage : HP, 3x EVA 8000 / 249 discs a 146 GB Business Solution Used Software • - Management Reporting (MIS) - Oracle OLAP & Discoverer technology - around 300 KPI within 10 Dimensions - 200 users • - Analytical CRM - Time Series analysis - ABC-Analysis - Campaign-Controlling - Retour-analysis - Market Basket Analysis - Kredit – Risk - Fraud Detection • Departments : Marketing / Sales / Product Management Controlling / Call Center • Oracle 10g R2 • RAC / Partitioning / Express • ASM • OWB • Discoverer / OFA / Reports • Suse – Linux • > 600 users

More Related