Transcript Document

<Insert Picture Here>
DWH @ Quelle Neckermann - Germany
Reiner Zimmermann
Product Management – Oracle DWH Development
[email protected] - +41 79 237-7443
Presentation Agenda





Company Background
DWH usage and applications
DWH Architecture
SLA
Current Activities
<Insert Picture Here>
Company Background
Karstadt – Quelle Holding
Facts (2003)
Revenue Total :
Mailorder :
15,3 Mrd. €
8,0 Mrd. €
Employees : 80.046
Mailorder : 28.557
Quelle - Mailorder
Two strong brands
Facts (2003)
 Nr. 1 mailorder company in germany
Kennzahlen (2002)
 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
<Insert Picture Here>
DWH usage and applications
Usage of the DWH
CRM-Actions
CampagnManagement
Data Mining,
Customer Scoring,
Analysis
MIS,
Reporting
Computing Power
30%
30%
Granular Data Warehouse
Source Systems
30%
DWH processes
Sales
ProductManagement
MIS
Business Intelligence
Controlling
Complaints
Complaints
Granular Data Warehouse
Call-Center
Service

Internet
Diversification
of marketing material
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
<Insert Picture Here>
DWH architecture
... once upon a time ....
a DWH platform was seached ...
• Benchmark of several hardware vendors :
- HP
- Sun
- Bull
- Tandem
- IBM
- .......
• ... and the winner was :
• SUN E10k
• EMC
... some years later ....
a new DWH platform was seached with a better price
performance ratio ...
• ... and the winner was :
• HP Superdome
• HP EVA SAN
... 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
Current DWH Infrastructure
Mandantes/companies:
Datenbases/Filesystems:
•
•
•
•
•
•
• DBQDWH:
Quelle
neckermann.de
Quelle, „Mode und Preis“, Madeleine, Elegance
Quelle goup-DWH
ca. 5,8 TB, ca. 600 User
Mode und Preis
Madeleine
Elegance (incl. Bogner, …)
Quelle-Gruppen-DWH
(Peter Hahn, KQV, …)
• 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
Cluster Distribution of DWH Services
Datenbase
on 11 nodes
DBQDWH
DBNDWH
DBKTO
DBMA
DB-Structur
on 2 nodes
IAS/OID/SSO
OS-Struktur
on ~11 Knoten
NFS, Filesystem
TWS, SSH/SFTP
XCOMM
Testsystem on 1 node
ENQDWH
ENKTO, ENMA
ENSVSDWH
Clusterware
Cluster-Configuration (Standard)
Quelle
RAC
Quelle
RAC
Quelle
RAC
Quelle
RAC
ne.de
RAC
Server
DBMA
Loadbalancer
Quelle
RAC
Je 4x
Xeon64/3,66GHz
16/32 GB Mem.
Quelle
RAC
Quelle
RAC
Test
Server
DBKTO
Fibre-Channel
Quelle
neckermann.de
ne.de
RAC
3x EVA8000
249 discs á146GB
• Database DBMA and DBKTO
• IAS [Discoverer, OID (SSO / LDAP)]
• Periphere Software
(XCOMM, SFTP, SSH,…)
Cluster-Re-Configuration (Workload/Peak Mgmt)
Quelle
RAC
Quelle
RAC
Quelle
RAC
Quelle
RAC
ne.de
RAC
Server
DBMA
Loadbalancer
Quelle
RAC
Quelle
RAC
Quelle
RAC
Quelle
RAC
Quelle
RAC
Test
ne.de
RAC
ne.de
RAC
ne.de
RAC
Server
DBKTO
ne.de
RAC
Server
DBMA
Loadbalancer
Quelle
RAC
Quelle
RAC
Quelle
RAC
ne.de
RAC
ne.de
RAC
Server
DBKTO
Cluster-Re-Configuration (Testing)
Quelle
RAC
Quelle
RAC
Quelle
RAC
Quelle
RAC
ne.de
RAC
Server
DBMA
Loadbalancer
Quelle
RAC
Quelle
RAC
Quelle
RAC
Quelle
RAC
Quelle
RAC
Quelle
RAC
Test
Quelle
RAC
ne.de
RAC
ne.de
RAC
Server
DBKTO
Server
DBMA
Loadbalancer
Test
Test
Test
Test
ne.de
RAC
Server
DBKTO
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
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)
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 complete
change 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
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
<Insert Picture Here>
DWH SLA’s
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
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
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
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
<Insert Picture Here>
Current Activities
Current Activities
 customer Story w/ German marketing
 technical story for DOAG / BI Magazine …
 Participation @ EMEA Terabyte Club - Bologna
More Information
 Dr. Gernot Schreib
Head DWH – Quelle AG
[email protected]
 Norbert Gösswein
Head DB Management – AtosOrigin AG
[email protected]
 Herbert Rossgoderer
DWH Consultant – Oracle Germany
[email protected]
 Reiner Zimmermann
Product Manager – Oracle DWH Development
[email protected]
Quelle AG - Snapshot
Business 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.
Technology facts
•
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
•
•
•
- 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
Used Software
•
Oracle 10g R2
•
RAC / Partitioning / Express
•
ASM
•
OWB
•
Discoverer / OFA / Reports
•
Suse – Linux
•
> 600 users