CBW NLS High Speed Query Access to Database and Nearline Storage

CBW NLS – High Speed Query Access

to Database and Nearline Storage

Speed up Your SAP BW Queries with Column-based Technology

Agenda



Motivation – Nearline Storage in SAP BW



Architecture – Column-based Databases and CBW NLS



Retrieval – Seamless Access to NLS, Archive and Database



Success – Feedback from Customers

Challenges in Today’s SAP BW

Large and rapidly growing BW database Poor retrieval performance

Growing maintenance/admin overhead

Increasing TCO

Database,

Nearline Storage – Goals

Reduce & control DB size and growth Speed up query response time

SAP BW Data Aging Strategies

Data Category Online Database Near-Line Storage (read only) Classic Archive (read only) Frequently read/ changed data (current/’hot’)



Infrequently read data (mature/’warm’)





Very rarely read data

(aged/’cold’)







CBW NLS – Nearline Storage Solution for BW

End user query

SAP NLS – Nearline Storage Interface

BW Database

SAP HANA

Classical ADK Storage • SAP Archive Files

Column-based Architecture

 Data is stored vertically in columns

 Intrinsic indexing

 Effective data access with few I/O

 Data is stored horizontally in rows

 Reporting is very I/O-intensive

 No effective analyses / reporting Row-based Database

=> ideal for OLTP needs

Column-based Database => ideal for OLAP needs

Features of CBW NLS Nearline Repositories



Effective NLS data transfer – very fast load process



Column-based DB technology – no aggregates or indexes



Excellent for large data volumes



Huge data compression (up to 90% and more)



Very fast – especially with huge data load



Suitable for very large InfoCubes and systems with many queries



Suitable for ad-hoc queries with nearline access

CBW NLS – System Landscape

SAP BW Server PBS CBW NLS Server

PBS CBW NLS Server Instance Nearline Repository Sybase IQ Actian Vectorwise Database Instance SAP Gateway CBW NLS

Minimum Test Hardware

4 Core CPU

16 GB RAM*

130 GB+ HDD

Minimum PROD Hardware

8 Core CPU

32 GB RAM*

130 GB+ HDD

CBW NLS supports:

Linux, Unix, others on request

Nearline Storage - Admin Efforts

“Standard” nearline operation

 Monitoring and customizing directly from the SAP frontend

 Backup of nearline storage (Sybase IQ, Actian Vectorwise):

Full, incremental , incremental since full; virtual, offline OS level

 Backup of CBW NLS interface (OS level backup)

 No index and aggregate modeling

Tuning options for the nearline repository

 Hardware: Add CPU and main memory as needed

Data Transfer to Nearline Storage

PBS enhancement for NLS: PSA archiving

Database – HANA

Cubes DSO PSA

Nearline Storage

DAP IQ, Vectorwise

SAP Nearline Storage Interface: Data Archiving Process "DAP"

 Data transfer via SAP NLS and PBS CBW into the nearline storage

 Compression ≥ 90%

 Very fast and effective data transfer

SAP DAP (i)

T-RSA1

SAP DAP (ii)

Query Access

Example: Query which compares data of the current year with the previous. Current year‘s data in the SAP BW database, data of previous year in the nearline storage, accessible via the SAP nearline interface and PBS CBW NLS:

Current year (BW DB)

CBW Administration Cockpit

Select the InfoProvider

Generate InfoProvider for MultiProvider integration.

Data Browser and NLS Monitor for flexible maintenance and test options

Create a VirtualProvider for MP Integration

CBW – Master Data Snapshots

BW Query DSP InfoProvider Master Data Hierarchies NLS Repository <JOIN>

DSP – Data Snapshot Process

CBW – Master Data Snapshots

The setup and maintenance for PBS snapshots for master data and hierarchies is controlled via the CBW Administration Cockpit

NEW: InfoCube/DSO Snapshots

Original Query

Data Snapshot Process

MultiProvider MultiProvider

NLS Repository

VP VP VP

Replicate BW data with PBS DSP, e.g. to compare query performance

Copied Query

BW Database

Customer Experience

One of the first PBS CBW NLS customers reported excellent results: DAP performance storing data in the nearline repository (Sybase IQ):

 Transfer rate depends on data structures:

 6 million records/h up to 15 million records/h

Data compression in the nearline repository (IQ):

 Typical range: 88 % - 95 %

Query response time:

0 200 400 DB no aggregates DB with aggregates CBW NLS IQ

Customer Success – Query Performance



InfoCube with 17 million records



Online DB with-w/o aggregates

Runtime improvement for NLS retrieval using IQ

x 4 x 26

Summary



Flexible query access to nearline/archive data for InfoCubes and DSO



Effective storage due to large data compression



High data transfer rate into NLS repository



Excellent retrieval speed from NLS repository



Setup of nearline data and access with minimal administration efforts



Very low IQ management efforts and costs

PBS Software GmbH

Schwanheimer Strasse 144a 64625 Bensheim

Phone: +49-6251-174 0 Fax: +49-6251-174 174 Email: [email protected] Internet: www.pbs-software.com

Copyright

© Copyright 2013 PBS Software GmbH. All rights reserved.

No part of this presentation may be reproduced or transmitted in any form or for any purpose without the written permission of PBS Software GmbH. The information of this presentation might change without prior notice.

Microsoft, Windows are registered trademarks of Microsoft Corporation.

IBM, IBM DR550, IBM TSM and SSAM are trademarks or registered trademarks of IBM Corporation. Linux is a registered trademark of Linus Torvalds in the USA and other countries.

UNIX is a registered trademark in the USA and other countries, licensed exclusively through X/Open Company Limited. HP, HP-UX11 Apache are trademarks or registered trademarks of Hewlett-Packard Company.

EMC, EMC Centera, EMC Celerra are trademarks or registered trademarks of EMC Corporation. Hitachi, Hitachi HCAP, Hitachi HCP are trademarks or registered trademarks of Hitachi Ltd. NetApp, NetApp Filer are trademarks or registered trademarks of NetApp Corporation. Sybase IQ is a registered trademark of Sybase an SAP Company.

Actian Vectorwise is a registered trademark of Actian Corporation. PBS archive add ons is a registered trademark of PBS Software GmbH.

SAP, R/3, SAP NetWeaver, SAP NetWeaver Business Warehouse, SAP Industry Solutions and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries.

All other product and service names mentioned are the trademarks of their respective companies.