© 2015 IBM Corporation
IBM DB2 for SAPIBM Analytics
IBM DB2 Nearline Storage Lösung für SAP BW(und die Partnerlösung von PBS Software)
Elke Hartmann-Bakan, IBM Deutschland GmbHIBM Analytics Platform, IBM SAP International Competence Center
E-Mail: [email protected]
© 2015 IBM Corporation
Agenda
• Einsatz der SAP Nearline Storage Lösung
• NLS Umsetzung mit DB2 LUW
• Architektur und Implementierung für DB2 NLS
• Referenzen
• Weitere Informationen und Kontakte
*Die Lösungen von PBS Software werden im Lauf der Präsentation erwähnt, dort wo sie einen Mehrwert generieren!
2
© 2015 IBM Corporation
Datenflut macht auch vor den SAP Systemen nicht halt …
3
Source IBM Webseite
SAP Systeme
Social Media & Real time
Cloud Computing & SaaS
Sensors & M2M
Mobile Applikationen &Standortbezogene Dienste
Mehr Nutzer Höhere Ansprüche an Antwortzeiten
© 2015 IBM Corporation
Herausforderungen für Anwendungen und Datenbanken
Datenwachstum– Weitere Nutzer
– Mehr Datenquellen
– Neue Datentypen (e.g. RFID)
– Neue Geschäftsprozesse
– …
Performance– Antwortzeiten
– Datenverarbeitung, z.B. ETL
– Datenbank Administration, z.B. Backup, Restore
Höhere Kosten– Infrastruktur
– Administration
– SAP HANA
4
$ € £ $ € £
t
© 2015 IBM Corporation
„Data Aging Konzept“ von SAP
Data Category “Online” Database Near-Line Storage(Read Only)
Classic Archive(Read Only)
“HOT”häufiger Zugriff und
Änderungen
“WARM”gelegentlicher Zugriff,
keine Änderungen erwünscht
“COLD”kaum im Zugriff,
fixe Daten
Datenspeicherung:
optimaltolerierbarunakezpetabel
PBS: Nahtloser Zugriff zu allen Daten (CBW NLS ADK – zusätzlich die Möglichkeit zur Archivierung der PSA Daten, CBW NLS DB2)
© 2015 IBM Corporation6
SAP BW NLS - Architektur
Transparent AccessTransparent Access
BW Online Data
DBMSTREX
DB Interface Layer
Relational DBInterface
BI Data Manager
General NLS Interface
NLS / DB2 LUW Interface
BI OLAP
DBMSTREX
SAP NetWeaver BW
DB Interface Layer
Relational DB Interface
General Near-Line Interface
Near-Line PartnerInterface
BW OLAP
InfoCube200920082007
DSO200920082007
User InterfaceReporting
Data AccessFrequently Infrequent
Near-Line StorageInfoCube
20062005
…
DSO20062005
…
© 2015 IBM Corporation7
SAP BW NLS – Architektur mit IBM DB2
Transparent AccessTransparent Access
BW Online Data
DBMSTREX
DB Interface Layer
Relational DBInterface
BI Data ManagerGeneral NLS
Interface
NLS / DB2 LUW Interface
BI OLAP
DBMSTREX
SAP NetWeaver BW
DB Interface Layer
DB2 or other DB support layer
General Near-Line Interface
DB2 Near-Line Interface
DB2 DB support layer
BW OLAP
InfoCube200920082007
DSO200920082007
User InterfaceReporting
Data AccessFrequently Infrequent
Near-Line StorageInfoCube
20062005
…
DSO20062005
…
DB2 or Other BW db NLS / DB2 database
DB2 Near-Line Lösung• Separate DB2 Datenbank• Wird mit SAP BW ausgeliefert• Nur SAP BW Unicode Systeme
(Non Unicode mit PBS CBW NLS)• Teil der DB2 OEM Lizenz• Unterstützt andere Datenbanken• Nutzung von DB2 Features:
• Compression• BLU• Native Encryption• …
• Integrierte Administration und Installation (DBA Cockpit …)
• Unterstützung über SAP Support• Flat InfoCubes mit DB2 10.5 (SAP
Note: 2155374)
© 2015 IBM Corporation
IBM DB2 NLS solution for SAP BW now listed at SAP's "Certified Partner Software" site!
• The IBM Near-line Storage (NLS) solution is based on DB2 LUW and can be used with all supported SAP BW databases including SAP HANA.
• It is designed to control the growth of the SAP databases for SAP BW; and enables optimal resource utilization facilitating continued superior performance for the SAP BW business users at an attractive TCO.
• SAP ships since NW 7.01 SP 6 and higher as an integrated IBM NLS solution with DB2 LUW.
• SAP Certified Partner Software:http://scn.sap.com/docs/DOC-29787 (IBM DB2 NLS is mentioned at the page, see text extract below)
Value Add mit PBS Snapshots (CBW NLS)
9
NLSDB2 BLU
Firma: 1 Million Kunden und 100 Vertriebsorganisationen Jährlicher Umsatz pro Vertriebsorganisation Erwartetes Ergebnis: Liste mit 100 Zeilen Standard NLS: liest 1 Million Zeilen (Volumen pro Kunde), die
Aggregation nach Vertriebsorg. erfolgt durch den OLAP Prozessor
PBS Value Add: Kopiert die MD Attribute (in diesem Fall: Vertriebs org.) nach NLS, die Aggregation erfolgt auf der NLS Datenbank
CharacteristicsKey figures
MD attributesNavigation attributes
Hierarchies
SAP BWDatabank
OLAP Engine
CharacteristicsKey figures
MD attributesNavigation attrib.
HierarchiesPBS DSP – Data Snapshot Process
PBS enhancement
IBM and PBS Nearline Storage Solutions for SAP BW and SAP ERP
Value Add mit PBS Snapshots – Typische Ergebnisse
10
Query Beispiel mit Standard SAP/ DB2 NLS Prozess:
Großes NLS Result Set, Aggregation erfolgt durch SAP OLAP
Laufzeit Beispiel: ~ 300 sec.
Query Beispiel mit dem erweiterten PBS CBW Stammdaten Snapshot Kleines NLS Result Set, Aggregation erfolgt auf der NLS db (DB2 BLU)
Laufzeit Beispiel: ~ 3 sec.
100 x schneller!
3 sec
Standard NLS PBS improved NLS
300 sec
Que
ry ru
ntim
e
IBM and PBS Nearline Storage Solutions for SAP BW and SAP ERP
© 2015 IBM Corporation
DB2 NLS Installation
• Installation über SAPInst Tool
• Vergleichbar mit der Standard SAP NW DB Installation
• SAPInst erstellt eine DB2 Instanz für NLS
• Verbindung zur NLS Datenbank
• Erstellung mit dem SAP DBACockpit
• Der „Connection Name“ wird im „Data Archiving Process“ referenziert
11
Aufbau der Verbindung
© 2015 IBM Corporation
12
TA: RSA1
Source:SAP
© 2015 IBM Corporation13
• DAP with NLS archivingSAP NetWeaver BW NLS – Data Archiving Process (DAP)
© 2015 IBM Corporation14
• Definition of the NLS archiving criteria: time and optional additional criteria
SAP NetWeaver BW NLS – Data Archiving Process (DAP)
© 2015 IBM Corporation15
• Create and monitor an NLS data archiving request
SAP NetWeaver BW NLS – NLS Request
© 2015 IBM Corporation16
SAP BW NLS on DB2 – Synchronized Recovery
Synchronized NLS recovery process• Advised process for NLS database
recovery• System consistency check after BW or NLS
database recovery• Automatic corrections of data inconsistency
(if appropriate)• Use DBA Cockpit or call ABAP report
FM_RSDA_DB6_SYNC_CHECKER (SAP note 1515422)
Example with requested NLS recovery to timestamp (Report output)
Example with possible correctons (Report output)
© 2015 IBM Corporation17
SAP BW NLS on DB2 – Backup• DB backups of BW and NLS DB in less frequent intervals• Using different infrastructure (e.g. Online Backup vs. Snapshot)
BW database
NLS / DB2 database
3 years (data)
Backupdaily
Backupmonthly
timeJanuary February
Snapshot
Snapshot
Snapshot... Snap
shot... ... ...Snapshot
Tape
7 years (data)
Monthly
NLS Archiving
Daily
BWDatal Load
© 2015 IBM Corporation
db6nlsBW
database NLS-1
db6nlsBW
database NLS-2
18
SAP BW NLS on DB2 – SAP Transport
• Transport of DAPs definition within the SAP system landscape– Prerequisite: Same NLS connection name on source and target system
QA System
Production System
DAP-1, DAP-2,
DAP-3
© 2015 IBM Corporation19
SAP BW NLS on DB2 – SAP System Copy
• NLS database copy with or without data (empty objects)– Copy NLS database after BW system copy (SAP note 1799926)
– NLS system copy without data is beneficial to setup test systems as a copy from production
• Consistency check after SAP System Copy– Target system has no NLS access after system copy (per default)
– SAP DBA Cockpit support for simple verification and reconnect to NLS DB
BW database
BW database
NLS-2
SAP System Copy
NLS Copy with / without data
SourceSystem
Target System
NLS-2
© 2015 IBM Corporation
SAP BW NLS on DB2 - Best Practices for Setup
Separate NLS database for each SAP BW systemPros: - NLS performance on DB level
- independent database recoveryCons: - Additional administration effort with more NLS databases
- e.g. db backups
Production System
TestSystem
One NLS db for several SAP BW systemsSeparate db schema on NLS db for each systemPros: - Less administration effort Cons: - Other SAP BW systems impacts NLS performance
- no access to all NLS systems during NLS database recovery
Best practices Separate NLS database for production system Shared NLS database for test & quality systems
Production System
Quality System
TestSystem
BW1 DB
BW2 DB
BW3 DB
NLS Database
Schema NLS 1
Schema NLS 2
Schema NLS 3
Production System
Schema NLS-2
Schema NLS-3
TestSystem
NLS Database
Schema NLS-1
Schema NLS-2
NLS-1 NLS-2 BW-3 BW-2 BW-1 NLS-3
BW-1 BW-3
NLS Database
Schema NLS-3
BW-1 NLS-1 BW-2 BW-3
Quality System
Quality System
Quality System
TestSystem
Production System
BW-2
SAP Hinweise
Insert footer information such as internal use only (Insert>Header & Footer) 21
SAP Note Description1405664 Near-Line Storage Implementation (supplementary note)
1819734 Use of BLU Acceleration
1834310 Use of DB2 BLU with DB2 NLS required SAP BW Support Packages
2063589 Mandatory corrections for NLS with DB2 10.5 FP4 release
2155374 Flat InfoCubes: New table layouts with IBM DB2 with BLU Acceleration
1695150 Support for SAP BW on DB2 NLS for non-DB2 databases (HANA, Oracle, ...)
1515422 Recovery Synchronization: Use DBA Cockpit or call ABAP report FM_RSDA_DB6_SYNC_CHECKER
1686115 Log File shipping of DB2 NLS database
Einsatz von DB2 10.5 FP4SAP oder höher
22
(SAP Note: 1834310 - DB6: Verwendung der DB2 BLU Acceleration mit DB2-Nearline-Storage)
Unterstützte BW Objecte: • InfoCubes + Flat InfoCubes* • DataStore Objects
*Wenn die Zeilenlänge des flachen Infocubes das DDIC-Limit überschreitet oder BLU aufgrund des eingesetzten DB2 Releases nicht möglich ist, wird der NLS InfoCube mit nach Zeilen organisierten Tabellen im Star-Schema-Tabellenlayout angelegt. Dieser NLS InfoCube besteht aus einer Faktentabelle und mehreren Dimensionstabellen.
SAP BW Release Recommend SP Minimum SP Minimum SP (Flat InfoCubes)
7.01 (SAP BW 7.0 EHP1) 15 13 18
7.02 (SAP BW 7.0 EHP2) 15 13 18
7.30 11 8 14
7.31 (SAP BW 7.3 EHP1) 10 5 17
7.40 5 GA 12
© 2015 IBM Corporation23
SAP BW NLS on DB2 – Operations• Operation of the NLS / IBM DB2 solution with existing DB2 DBA skill (no
new technology introduced)
Important DB2 specific NLS extensions • Easy NLS installation with SAP installation tool• One DB2 NLS database can store NLS data from different SAP BW systems• Administration & Monitoring of NLS database with SAP DBA Cockpit • Integrated NLS Backups • Synchronized NLS Recovery • Integrated High Availability / Disaster Recovery solution• SAP System Copy support
• NLS database can be copied with or without data (empty objects)
• Consistency check after SAP System Copy
• DB2 Native Encryption available with DB2 10.5 FP5• SAP Transports with NLS / Data Archiving Process definition
© 2015 IBM Corporation
24
The Need– Production database 10TB
• Database was growing up to 500 GB a month• Struggling to provide acceptable query performance• Abandoned daily backups as they simply took too long
The Solution– IBM DB2® Near-Line Storage for SAP NW BW– IBM DB2 was selected as a database platform
The Benefits– 80% Storage Savings
• Drastically shrink the size of SAP BW db from 10TB to 2TB – 266% storage cost savings
• Moving older data onto less costly storage– 276% improved performance
• SAP NW BW Accelerator query performance improved by 276%• Even complex queries can be answered in minutes rather than in hours
– 79% faster backup times • Database backup down from 24 to 5 hours
Foodstuffs South Island cuts costs and boosts business performance
Case Study
© 2015 IBM Corporation
RI-Solutionimproves speed business reporting
Need: – Flexible solution providing faster data access
– At lower costs
– Minimizing downtime for backup and recovery
Solution:– Migrated it’s existing NLS Solution to DB2 NLS
Benefit:– €30,000 annual savings (maint. + support costs)
– Over 90% faster access to historical data
– 70% fast data load to NLS
25
Case Study
© 2015 IBM Corporation
Nearline Analytic Infrastructure
26
© 2015 IBM Corporation
PBS NAI – Architektur Überblick
27
© 2015 IBM Corporation
28
© 2015 IBM Corporation
Kontakte und weitere Information
29
Webseiten •IBM DB2 SAP Landing Page
•SAP Community Network: DB2 for Linux, Unix, Windows
•PBS Software
IBM Karl Fleckenstein, E-Mail: [email protected]
Elke Hartmann-Bakan, E-Mail: [email protected]
PBS Software:Dr. Klaus Zimmer, E-Mail: [email protected]
Dokumente: Guide: DB2 NLS Installation mit SAP NetWeaver BW 7.30
Solution Brief: IBM DB2 NLS for SAP BW
E-Learning Expert: Session on IBM DB2 NLS for SAP BW
Für alle Fragen rund um IBM und SAP:ISICC InfoService: [email protected]
© 2015 IBM Corporation
Elke [email protected]
IBM and PBS Nearline Storage Solutions for SAP BW and SAP ERP
© 2015 IBM Corporation
Backup Folien
31
© 2015 IBM Corporation32
SAP BW NLS SolutionNew NLS Features with SAP BW 7.40 SP08/09
• New Near-line connection Mode Options - System Cloning with shared usage of NLS data - improve operations if near-line data is unavailable
• Mass maintenance support for generating DAPs, DAP Variants, Process Chains
• Extended Pruning for time characteristics
© 2015 IBM Corporation
PSA
InfoCube
Source Systems
Reporting
DSO
• BW supports Since SAP BW 7.0 and higher- InfoCubes and Aggregates- Master Data - DSOs and PSA tables- BW temporary tablesAvailable with SAP BW 7.40 SP8- Flat InfoCubes
• NLS - Since SAP BW 7.01and higher- Supported objects
- NLS InfoCubes- NLS DSOs
• Benefits- Fast and stable BW reporting without tuning
- Space reduction - less Aggregates, no Indexes
ReportingLayer
EnterpriseData WarehouseLayer
Data AcquisitionLayer
BW Near-Line Storage (NLS)PSA
FlatInfo
Cube
DSODSO
PSA PSA
Master Data
Aggregate
Einsatz von DB2 BLU für NLS
FlatInfo
Cube
© 2015 IBM Corporation
Log
SAP BW NLS on DB2 – High Availability and Disaster Recovery
• DB2/NLS database with mirrored Log Path to overcome Log File failures • One or more DB2 NLS Standby databases can be setup with DB2 HADR featur
High Availability and Disaster Recovery solution
NLS Database
• Log File shipping of DB2 NLS database (described in SAP note 168115) Beneficial for Disaster Recovery Solution
Log Replication
Primary
LogNLS Database
Standby
LogNLS Database
Log File Replication
Primary
Log NLS Database
Standby
archived Log Files
archived Log Files
archived Log Files