Overview of How SAP IQ Augments the SAP Technology Landscape with Temperature Sensitive Data Management

(1)

Orange County Convention Center

Orlando, Florida | June 3-5, 2014

Overview of How SAP IQ Augments the SAP

Technology Landscape with Temperature Sensitive

Data Management

(2)



The SAP HANA platform is a cohesive and

integrated set of technologies, which supports

a comprehensive suite of business applications



SAP IQ is a key component of the HANA

platform for warm/cool data management, and

extreme data volume scenarios



SAP IQ has become the preferred technology

for temperature sensitive data management at

SAP as demonstrated by these solutions:



SAP BW NLS (Near Line Store)



SAP ERP ILM (Information Lifecycle Management)



SAP HANA SDA (Smart Data Access) to SAP IQ



SAP HANA extended storage

(3)



SAP IQ is a highly scalable analytics database that

runs on commodity hardware.



SAP IQ can scale out an SAP HANA big data

deployment at an economical price point.



SAP HANA with SAP IQ together can handle the

bulk of your big data analytic problems without

bringing in alternative technologies such as

Hadoop.

(4)



Business today needs a new data platform



SAP IQ value in the SAP HANA platform



Smart Data Access: SAP HANA and SAP IQ federation



SAP IQ as Near-Line Storage (NLS) for BW



SAP IQ as Information Lifecycle Management (ILM) for

Business Suite



HANA extended tables



Key learnings

(5)

BUSINESS TODAY NEEDS A NEW

DATA PLATFORM

(6)

Business & Consumer Applications

Big Data

End to End Data Management for Real Time Business

SAP DATA MANAGEMENT

STORE

TRANSACT ANALYZE PREDICT

Custom

Development ISVs & OEMs ERP

Internet of Things Workforce of the Future Cloud Industries

(7)

SAP In-Memory Data Fabric

SDA SAP Data Services Hadoop

Results at

the Speed of

Memory

Business Applications SQL or SAP River SDA

Data Fabric Layer

Real-time Events/ Machine-generated Data SAP HANA Streams MapReduce / Hive

SAP

HANA

Petabytes of Structured Data Op RDBMS Load Source Databases Other Sources

(8)

SAP IQ VALUE IN THE SAP HANA

PLATFORM

(9)

SAP HANA Platform

SAP HANA Platform foundations

● Cross-paradigm data access for new models of value discovery

● Hyper-performance on all classes of application and usage scenarios

● Price-performance value across all use cases

Benefits

● Execute, record, analyze, and optimize without system limitations

● Embrace & extend across variations of data forms and processing models

● Common modeling, integrated development environment, shared systems management infrastructure, and deployment-independent solutions

● Trusted and unified data environment

In-memory

/

real-time

SAP

HANA

SAP Real-Time Data Platform

SAP ESP

streams SAP SQL Anywhere mobile/embedded

SAP IQ

EDW

SAP ASE

transactions

SAP Data Services / SAP Information Steward

information management

Common

programming APIs

SAP

Po

w

erD

es

ig

ne

r

m odel ing

SAP

Con

trol

C

en

ter

mon ito ring

Hadoop Big data

See Appendix for abbreviations

(10)

What is SAP IQ?

SAP IQ (formerly SAP Sybase IQ) is a market leading, high-speed columnar analytics server and data

warehouse which is designed specifically for Data Analytics.

• Columnar Database designed for high speed analytics

• Eliminates the problems caused by traditional row-based systems

Designed for Analytics

• Up to 1,000X faster than row-based systems

• Enables high speed performance of complex queries against large datasets

Unsurpassed Performance

• Reduce the need for aggregates and indexes

• Greatly decreased ongoing maintenance costs when compared with row-based systems

Lowest TCO

(11)

SAP IQ + SAP HANA

Extended storage for BW/SAP

HANA

Next generation near-line

‘smart store’ solution for

BW/SAP HANA

Integrate

Optimize

Synthesize

SAP innovation without customer disruption

This is the current state of planning and may be changed by SAP at any time.

See Appendix for abbreviations

Extended storage for SAP

HANA

Evolution in integration...



Extend large BW/SAP HANA

system tables into SAP IQ for high

data volume scenarios



Cost effective store for SAP BW

near-line, cool data



Extend HANA tables into IQ for

flexible and value-based

(12)

SAP IQ Relevance

Source: Magic Quadrant for Data Warehouse Data Management Systems, Gartner, Inc. February, 2012. Mark A. Beyer, Donald Feinberg, Merv Adrian, Roxane Edjlail

Exadata Numbers are from data warehouse and OLTP installs

SAP IQ Teradata ExaData Netezza

Number of Customers

• 100x Faster Queries then row-based RDBMS

• 10X Faster Data Loads

• Reduced TCO by keeping older data in IQ

SAP NetWeaver NLS for SAP BW

SAP IQ for BusinessObjects BI Platform

• Bundled into platform • Superior analytic performance • Reduced TCO compared to

competitors

SAP HANA, BIG DATA EDITION • Bundled into edition

• Data Tiering, Hadoop Access, Cold store

IQ is bundled into various SAP Offerings – and will likely be bundled into more going

forward.

(13)

Guinness World’s Fastest Data Loader

HP ProLiant DL980 G7

• 8 x Intel® Xeon® E7-4870 @ 2.40GHz

• 1TB RAM

• 16 x Fibre Channel HBA @8Gbps

• 3PAR StoreServ 10800 (8 nodes) 320 x 600GB HDD @ 15Krpm

SAP® Sybase® IQ 16 BMMsoft EDMT® 9

Red Hat® Enterprise Linux® 6.2 Intel 64

13 parallel streams

2 parallel streams

1 stream Structured data

Documents, audio, video

Email, SMS

6.5 million

500 million

5 million

Fastest Data Loader Audited Record:

• Peak loading: 34.3TB / hour with 80 cores

• Sustained loading: 30TB / hour (even as tables and indexes

(14)

Guinness World’s Largest Data Warehouse



SAP HANA



Running on 5 HP ProLiant

DL580 G7 Servers



4 Active nodes with 1

standby



6.2TB of data



SAP IQ



SAP IQ multiplex running

on 20 HP ProLiant DL580

G7 Servers



12.1PB of data

(compressed into approx.

3.1PB of storage)

(15)

SMART DATA ACCESS: SAP HANA

AND SAP IQ FEDERATION

(16)

SAP HANA smart data access capability

Data virtualization for on-premise and hybrid cloud environments



Benefits



Enables access to remote data

access just like “local” table



Provides SAP HANA to SAP

HANA queries



Smart query processing

including query decomposition

with predicate push-down,

functional compensation



Supports data location agnostic

development



No special syntax to access

heterogeneous data sources



Non-disruptive evolution



Heterogeneous data sources



SAP HANA to Hadoop (Hive)



SAP HANA to Teradata



SAP HANA to SAP HANA



SAP HANA to SAP ASE



SAP HANA to SAP IQ

Transactional + Analytical

Teradata Hadoop SAP HANA ASE IQ SAP HANA Virtual Tables HANA Tables

(17)

• Easy to setup via virtual tables, and start writing apps –

using HANA features

• No need to load data from source to start the project –

saves cost, and is non-disruptive

Easily utilize enterprise

wide data leveraging HANA

capabilities

• Significant query optimizations to leverage processing

capability of target sources

• Move minimal data between SAP HANA and sources

High Performance

• Access remote data securely utilizing secondary credentials

Secure access to remote

data

• Integrate output of Map-Reduce jobs in Hadoop/HIVE and

access seamlessly from SAP HANA

Leverage Big Data

processing

• Store hot data in HANA, warm data in systems like SAP IQ

and Hadoop, - get seamless access from HANA

• Queries in SAP HANA can integrate data from these

systems

Seamless archived data

access

(18)

SIMPLE TO USE

Decide

Data Needs

• Decide what remote data sources are needed for the application

running on HANA

Define

Sources

• Define the remote data source with the appropriate security

credentials

Create

Virtual

Tables

• Create virtual tables which reference the remote data source (table)

Write The

App

• Write your application, using HANA tables and virtual tables

• Query processor in HANA does the rest of the optimizations and data

access

(19)

USE CASES

Developing Applications Leveraging Data from across the

Enterprise - where it sits with minimal data movement

Big Data – Integration with Hadoop / Hive

Using SAP IQ to store ‘warm’ archived data while

providing seamless access to it from SAP HANA

SAP NetWeaver Business Warehouse on HANA leveraging

data in SAP Business Suite on HANA seamlessly

(20)

Use SAP IQ as a warm archive for SAP HANA

Hana Server

Hana tables

Virtual tables

Hana Studio

SAP IQ

Analytical and

Transactional

Applications

READ

• SAP IQ can be used as

a cost effective

archive for warm data

with SAP HANA

• SAP HANA contains

the critical hot data

and SAP IQ retains

and process the

archived warm data

that is seamless

accessible from SAP

HANA .

(21)

SAP IQ AS Near-Line Storage (NLS)

FOR SAP BW

(22)

• Data is read and/or written frequently

• In memory

• No restrictions, all features available

• Infrequent access

• On disk, no need to keep in memory all the time

• No restrictions, all features available

• Sporadic access

• Not stored in HANA DB; stored in Near-line Storage

• Restricted to NLS capabilities

SAP BW/HANA: Multi Temperature Data Approach

Non-Active Data Concept

Providing lower TCO by optimized data volume management

hot

warm

cold

(23)

ODBC Driver from IQ

HANA DB

BW NLS Implementation for SAP IQ

InfoProvider

NLS Interface

Near-Line Adapter (ABAP based )

BW

DBSL for

SAP ASE (/w IQ ext.)

(Kernel based; Open SQL + Native IQ SQL via ADBC) BW Porting General Porting DB Client

SAP

IQ

(24)

“Optimized” BW Query-access to NLS via SDA

Query InfoProvider with NLS CalcEngine Facts Master data Master data Archive Facts

Online Query _{Nearline Query}

HANA

SAP IQ

Virtual Table SQL-Engine SELECT "S1"."SID" ,"X1"."S__PGRO" , SUM ( "O1".“AMOUNT" ) FROM

“Virtual Table" "O1"

JOIN “/BIC/SCUST" "S1" ON "O1" . "CUSTOMER" = "S1" . "/BIC/CUST" JOIN "/BIC/XPROD" "X1" ON "O1" . "PRODUCT" = "X1" . "/BIC/PROD" WHERE … GROUP BY "S1"."SID" ,"X1"."S__PGRO"

BW

(25)

SAP IQ AS Information Lifecycle

Management (ILM)

(26)

ILM Retention Management (RM) on SAP IQ

Benefits at a Glance

Innovation

Benefits

SAP ILM

RM

 Store and manage your archive index and data on

column-based DB



Eliminate costly 3

rd

_{party compliant store}

SW and HW



Foundation for next generation ILM



Reduce system landscape complexity

 Increase performance for archiving and data access

 Increase search capabilities



Optimal data compression



WORM-like security (certificates, signatures)

SAP

IQ

+

=

One point of contact (vendor)

One storage infrastructure

One archiving strategy

Availability

• Storage of archive information system (AS) indexes (available since Jan 2013 with NetWeaver 7.31 SP07)

(27)

Classic Storage in ERP and BW

ERP DB Archiving / ILM Framework 3rd Party Store BW DB IQ 3rd_Party NLS

Classic storage in ERP and BW

St

or

e

• SAP ERP ILM (Information

Lifecycle Management) utilizes a

third party store for archiving

cold data.

• SAP BW NLS interface utilizes a

third party solution to

implement a near line store on

IQ or another database

(28)

ILM Database Storage Option (on SAP IQ)

ERP DB ILM Framework 3rd Party Store BW DB IQ St or e *archive information structures SAP IQ Arch. Data AS* ERP DB ILM Framework 3rd Party NLS

Classic storage in ERP and BW

write-once space

SAP ERP ILM now

owns the complete

archiving solution, and

stores both archive

indexes and archive

data in SAP IQ.

(29)

ILM DB Storage combined with NLS storage

ERP DB ILM Framework 3rd Party Store BW DB IQ 3rd Party NLS

Classic storage in ERP and BW

St or e BW DB SAP BW NLS** SAP IQ AS * Arch. Data, & BW data ERP DB ILM Framework

With SAP ILM RM on SAP IQ + BW NLS, customers can store both types of data in a single storage environment!

(30)

(31)

Warm and cold data archiving for SAP products

NLS (Near Line Store) for SAP BW

• Optimized NLS data transfer throughput using IQ Loader functionality

• SAP HANA and IQ share the same columnar paradigm, and similar data

compression rates

• Ready for large data volumes

• Suitable for ad-hoc queries with long history

• Minimum administration effort

ILM for SAP Business Suite

• Fast archive index read w/o additional secondary DB indexes

• Increased search capabilities

• Faster archive I/O – fewer layers (software, network, storage hardware)

• ERP archive files as well as archive indexes stored in SAP IQ

Warm archive for HANA using smart data access (SDA)

• Using SDA, HANA customers can access IQ as a federated store

• Store warm data in IQ, and real time data in HANA

• Push query processing down to IQ

(32)

SAP HANA Extended Storage with SAP IQ

NLS Store for SAP BW

ILM for SAP

Business Suite

Warm archive for

HANA, federated

via SDA

Extended storage for SAP HANA

• Handle all data seamlessly via HANA tables

• Steady performance across data volumes, variety, velocity

with scalable capacity

• Practical for both on-premise and hosted models

• Native Big Data solution

allows customers to gain real-time

insights by cost-effectively managing and analyzing ALL

enterprise data

Warm data management

Cool data archival

(33)

HANA EXTENDED STORAGE

• While SAP HANA provides excellent performance for big data applications, size and cost constraints may preclude a HANA-only solution

• A significant portion of enterprise data is considered “warm” and does not require the continuous in-memory residence and real-time processing of “hot” data

• Keeping warm data segregated allows SAP HANA to perform streamlined processing of only the most critical, hot data

Why is warm data management

important for HANA?

• Hadoop plays an important role in SAP’s big data story, for storing and processing massive volumes of data.

• SAP has integrated Hadoop into the HANA platform through data movement and federation methods

• Although Hadoop improves all the time, it does not match the performance characteristics of a columnar store when processing data at a scale that can be accommodated within a DBMS – in the case of HANA extended storage, petabyte scale!

• Hadoop does no data compression, and data expands to at least 3x its raw size!

What about Hadoop for warm

data storage and processing?

• HANA extended storage is not simply a storage tier – but a smart column store with superlative data loading, data compression, and query

performance characteristics

• HANA extended storage has been proven to scale capably to petabyte levels, at an economical price point

• HANA extended storage is exhibiting promising performance already, with additional improvements to come

• HANA extended storage allows both “hot” and “warm” data to be accessed natively through HANA tables and views

Why is SAP HANA extended

storage the best solution for

(34)

HANA extended storage requirements

• Manage data cost effectively, yet with desired performance based on SLAs

• Application defines which data is “hot”, and which data is “warm”

• Handle very large data sets – terabytes to petabytes

• Update and query all data seamlessly via HANA tables

• Provide a native Big Data solution that covers most enterprise use cases without Hadoop

Table Table Queries and updates initiated from HANA

SAP HANA

SAP IQ

Extended table

(35)



One-time definition of extended storage source server



Tables mapped to extended storage using CREATE or

ALTER TABLE

Extended Storage Configuration and Usage

CREATE EXTENDED STORAGE CONFIGURATION

'Driver=libdbodbc16_r.so;ServerName=hana_iq;CommLinks=tcpip(host=localhost:30015)'

WITH CREDENTIAL TYPE 'PASSWORD' USING

'user=DBA;password=sql;hanauser=SYSTEM;hanapassword=sql';

CREATE TABLE table2(c1 integer, c2 integer) USING EXTENDED STORAGE; ALTER TABLE table2 NOT USING EXTENDED STORAGE;

ALTER TABLE table2 USING EXTENDED STORAGE ENABLE DELTA;

--- Rest is SQL as usual ---

INSERT INTO table2(c1 c2) VALUES(4, 7); SELECT C1 FROM table2 WHERE C2 < 10; ALTER TABLE table2 add C3 int;

(36)

Extended Storage Optimizer

Query Decomposition with Forwarded Search Arguments (SARG)

IQ Instance

HANA Instance

IQ Table <fact>

Extended

Storage

Optimizer

IQ

Query

Engine

HANA

Connection

Query

Fragment

SELECT fact.c1 FROM fact, dim WHERE fact.c2=dim.c2 AND dim.c1<10