How To Size SAP BW on HANA
Thomas Becker – TIP In-Memory Platform BW P&QE
October 2012
SAP & SAP Customer Internal
DISCLAIMER
Sizing recommendations apply for certified hardware only. Please contact
hardware vendor for suitable hardware configuration.
Note that HANA is constantly being optimized. This might have impact on
sizing recommendations, which will be reflected in this document. Therefore,
check for the latest version of relevant sizing notes.
Note that the sizing guideline in this document refers to SAP BW on HANA
(aka Orange) only. Additional applications running on top of HANA are not
covered in this document. Please refer to application specific sizing
guidelines for details in that case.
© 2012 SAP AG. All rights reserved.
2
SAP BW on HANA Sizing Elements
SAP BW on HANA sizing consists of
Memory sizing for column store data
Memory sizing for row store data
Memory sizing for objects created during runtime
(data load and query execution)
Disk Sizing
© 2012 SAP AG. All rights reserved.
3
Sizing Approaches
Depending on project status, there are three sizing approaches available for
HANA:
•
QuickSizer Approach
•
Suitable for customer that start „from scratch“
•
•
Some details on data models known (such as largest cubes, DSOs, user numbers, etc.)
Not suitable for customers who plan to migrate an existing BW
•
DB specific scripts
•
•
Analyze data volumes of an existing BW system, based on database dictionary and statistics
information
Scripts available for Oracle, DBx, MSSQL, MaxDB
•
OS access / DB admin required to run scripts
•
Fast (runtime: < 5 mins)
•
New: ABAP based sizing report
•
Analyzes data volume of an existing BW system, based on data samples
•
•
DB independent report coming with Support Tools Plug-in
Much more accurate than script based sizing
• Strongly recommended, preferred sizing tool
© 2012 SAP AG. All rights reserved.
4
QuickSizer For SAP HANA
1. Start QuickSizer tool: https://websmp103.sap-ag.de/quicksizer
– QuickSizer general help
2. Create new project
3. Choose ‘SAP NetWeaver BW powered by SAP HANA’ questionnaire
© 2012 SAP AG. All rights reserved.
5
QuickSizer For SAP HANA (cont.)
4. Read help ‘How to fill in questionnaire’
5. Fill out the relevant sections
o Table 5: Definition of InfoCubes for HANA
(relevant for HANA sizing)
o Table 6: Definition of DataStore Objects on HANA (relevant for HANA sizing)
All other sections are used for BW Application Server Sizing!
© 2012 SAP AG. All rights reserved.
6
Sizing with DB Specific Scripts
DB specific scripts and sizing information provided by note 1637145
•
Unix/Linux and Windows scripts draw information from database dictionary tables to
determine size of tables in database (excluding indexes, temp table spaces, etc.)
•
Distinction between row store tables and column store tables: different compression
factors Scripts deliver two values: row store tables footprint and col store tables
footprint. These values need to be fed into sizing formula (see next slide)
•
Scripts for DB2 family (DB2, DB4, DB6) take into account source database
compression, other scripts don‘t (compression factor has to be applied manually)
•
To execute scripts, operating system access with DB administrator privileges is required
•
Scripts rely on up-to-date database statistics
•
Note: after a system cleanup (deletion of unnecessary data) the database system might
not reflect the freed space before table reorganization has been performed
Important: ABAP application server sizing to be done separately!
© 2012 SAP AG. All rights reserved.
7
BW on HANA Sizing Formula
RAM:
RAM = ( colstore tables footprint * 2 / 4 +
rowstore tables footprint
/ 1.5 ) * c1) + 50 GB
Disk:
DISKpersistence = 4 * RAM
DISKlog = 1 * RAM
1)
c = source database specific compression factor (only required if source
database compression is used and source database is other than any
of the DB2 family)
© 2012 SAP AG. All rights reserved.
8
BW on HANA Sizing Formula: Remarks
Explanation of constants:
RAM = ( colstore tables footprint * 2 / 4 +
rowstore tables footprint
/ 1.5 ) * c1) + 50 GB
•
Uplift for runtime objects
•
Average compression factor for col store
•
Average compression factor for row store
•
Source DB compression factor
•
Uplift for HANA caches, additional services, etc.
© 2012 SAP AG. All rights reserved.
9
BW on HANA Sizing Formula: Procedure and Example
1. Download archive „get_size.zip“ from SAP note 1637145 attachments
2. Extract script suitable for your DB and Operating system
3. Extract file „load_RowStore_List.sql“ into same folder
4. Execute script, result will be stored in file „Extent.txt“
5. Read sizing figures from script output:
6. Feed figures into Sizing formula:
Assumption: source DB compressed by factor 1.8
RAM = ( colstore tables footprint * 2 / 4 +
rowstore tables footprint) / 1.5 ) * DB compression + 50 GB
= (672 GB * 2 / 4 + 50 GB / 1.5) * 1.8 + 50 GB = 715 GB
Diskpersistence = 715 GB * 4 = 2,860 GB
Disklog
= 715 GB
© 2012 SAP AG. All rights reserved.
10
ABAP Based Sizing
ABAP report developed to overcome deficiencies of DB scripts:
•
DB independence
•
Easier deployment
•
Distinct size information for different types of tables (InfoCubes, DSOs, PSAs, …)
•
Extrapolation of memory consumption based on future growth
•
More sophisticated sizing approaches (e.g. non-active data) coming soon!
Availability
•
Prerequisite: Support Tools Plug-in ST-PI 2008_1_7xx SP7
•
Preliminary version based on ST-PI 2008_1_7xx SP6 available with note 1736976
•
Report: /SDF/HANA_BW_SIZING for full system sizing
•
Function Module: /SDF/HANA_TABLE_SIZE for single tables or list of tables
Documentation
•
Please read documentation attached to note 1736976 for details and tips how to run the
report
© 2012 SAP AG. All rights reserved.
11
ABAP Based Sizing: How it works …
Sizing based on sample data
•
•
•
•
For each DB table read small set of sample records into ABAP source DB
compression can be neglected!
Determine average record length
Calculate table size based on row count from DB statistics (need to be up to date!)
Automatic distinction between row store and column store tables
Versatile parameterization
•
•
•
Parallel execution: List of tables distributed across multiple processes to speed up
runtime
Different levels of precision (resulting in different sample sizes) – ‘Low’ good enough in
most cases!
Future growth projection
Distinct results
•
•
•
Total sizes for InfoCubes, DSOs, Change Logs and PSA tables are determined
Sizes of individual tables help identify potential for clean-up operations
Automatically applies sizing formula: result contains data + runtime memory
© 2012 SAP AG. All rights reserved.
12
/SDF/HANA_BW_SIZING: Selection Screen
Store output to file directly. If not set, a result
screen will be displayed (wich still can be
saved as a file at a later point of time).
File name for output (relative to SAP work
directoy, or absolute path name)
Number of parallel work processes used for
sampling. Note: free dialog porcesses
required for each process!
In detailed list of tables, suppress those with
sizes lower than 1 MB.
Precision setting: decreases / increases
data sample rate. Note: lower precision
means less run time!
Enter number of years and amount of growth
(absolute in GB or relative in %)
Future growth: calculate future HANA
requirements for next few years, based
on relative or absolute yearly growth
© 2012 SAP AG. All rights reserved.
13
/SDF/HANA_BW_SIZING: Output Screen / Report (1)
Total source size and number of tables
relevant for row store
Total size and number of tables relevant for
column store
Size and number of tables corresponding to
InfoCubes, DSOs, Change Logs, PSAs and
Master Data, number of aggregate tables
Total number of tables processed
Share of resources for row store and
column store (data ony and data including
dynamic runtime memory)
© 2012 SAP AG. All rights reserved.
Minimum amount of physical memory
for HANA server (including runtime
memory, memory for additional
services, etc.), and disk space.
14
/SDF/HANA_BW_SIZING: Output Screen / Report (2)
Sizing recommendations considering future
growth with detailed sizing requirements for
each year (only if future growth was selected)
System information, incuding report run time,
parameter settings, operating and DB system
information and SAP BW release
© 2012 SAP AG. All rights reserved.
15
/SDF/HANA_BW_SIZING: Output Screen / Report (3)
Detailed size information for each row store
table (sorted by size descending). Large
tables may be good candidates for data
cleansing to reduce memory requirements.
Report shows ABAP size and size in HANA
memory
Detailed size information for each column
store table (sorted by size descending).
Helps to identify good candidates for data
cleansing to reduce memory requirements
(e.g. very large PSA tables, etc.)
Distinuishes between PSA (P) and
Change Log (C) tables
© 2012 SAP AG. All rights reserved.
16
Outlook: Not-Active Data in HANA
Non-active data concept available with NetWeaver BW SPS8 and HANA SP5
hot
warm
cool
Data is read/written frequently
In Memory, additional memory required for dynamic objects (merge, intermediate results, etc.)
Mostly read access – lookups, transformations, etc.
In Memory, no additional memory required for dynamic objects
Infrequent access – no need to keep in memory all the time
On disk, loaded to memory only on demand, good candidate for displacement if memory runs short
Will be reflected in next version (V1.3) of Sizing Report – coming soon!
•
•
Optimized usage of HANA memory resources
Modeling of LSA concepts (Corporate Memory, …)
NOTE: Due to the complexity of the sizing algorithm, only the ABAP report will
be able to consider non-active data. Database scripts and QuickSizer will NOT
be changed to reflect this feature!
© 2012 SAP AG. All rights reserved.
17
BW on HANA Sizing: Scale Out
If a single HANA node cannot accommodate data due to limited memory, data
has to be distributed across multiple nodes (scale-out).
Symmetric solution: 1 master node, n slave nodes, all on identical hardware
Master node will handle system load and transactional load: ABAP system tables and
general operational data of the BW are stored on the master node. DDL statements are
executed on this node, global locks are acquired here.
Slave nodes will handle OLAP queries as well as loading/staging/activation/merging.
BW data (master data + cubes/DSOs/PSAs) is distributed across all slaves. This ensures a
balanced utilization of the available CPU and memory resources.
Easy extensibility: When a new slave is added, BW data can quickly be reorganized to fit
the new system. More information here: http://help.sap.com/hana/hana_db_part_en.pdf
Optional: stand-by node(s) can take over in case of node failure
© 2012 SAP AG. All rights reserved.
18
HANA BW Scale Out Concept:
Overview 1/2 – Setup with 512 GB Nodes
Master Node
512 GB of memory
holds system tables, all row tables
DDL execution, global locks, database
metadata
Up to 14 Slave Nodes
512 GB of memory each
master data tables will be distributed across
slave nodes
fact, DSO and PSA tables are partitioned
and distributed
Master
System
Tables
Slave 1
Slave 2
BW
Data
BW
Data
Slave n
…
BW
Data
Standby
Standby Node(s) (optional)
512 GB of memory
can take over role of master node or of one
of the slaves for failover
© 2012 SAP AG. All rights reserved.
19
HANA BW Scale Out Concept:
Overview 2/2 – Setup with 1 TB Nodes*
Master Node
1 TB of memory
holds system tables, all row tables
DDL execution, global locks, database
metadata
Up to 14 Slave Nodes
1 TB of memory each
master data tables will be distributed across
slave nodes
fact, DSO and PSA tables are partitioned
and distributed
Master
System
Tables
Slave 1
Slave 2
BW
Data
BW
Data
Slave n
…
BW
Data
Standby
Standby Node(s) (optional)
1 TB of memory
can take over role of master node or of one
of the slaves for failover
* For Hardware vendors that support it
© 2012 SAP AG. All rights reserved.
20
HANA BW Scale Out Concept:
Pure High Availability Solution with a Standby Node
Worker
Worker Node
512 GB or 1 TB of memory
single worker node
Standby Node
Standby
same amount of memory as worker node
can take over role of worker node for failover
© 2012 SAP AG. All rights reserved.
21
HANA BW Scale Out Concept:
Exception for systems with a single slave node
Please see note 1702409 why this setup is not recommended.
Master Node
holds system tables, all row tables
DDL execution, global locks, database metadata
Master
System Tables
Slave Node
holds master data, fact, DSO and PSA tables
Slave
Standby Node (optional)
can take over role of master or slave node for failover
For special requirements, master data tables may
optionally be moved to the master node. This can lead
to performance degradation. Master data tables may
be moved manually (there is no official tool support) or
with the help of a consulting note.
© 2012 SAP AG. All rights reserved.
BW Data
Standby
22
Thank You!
Contact information:
Dr. Thomas Becker
Quality Manager
TIP In-Memory Platform
BW Performance & Quality Engineering
[email protected]
© 2012 SAP AG. All rights reserved.
No part of this publication may be reproduced or transmitted in any form or for any
purpose without the express permission of SAP AG. The information contained
herein may be changed without prior notice.
Some software products marketed by SAP AG and its distributors contain
proprietary software components of other software vendors.
Microsoft, Windows, Excel, Outlook, and PowerPoint are registered trademarks of
Microsoft Corporation.
IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5,
System x, System z, System z10, System z9, z10, z9, iSeries, pSeries, xSeries,
zSeries, eServer, z/VM, z/OS, i5/OS, S/390, OS/390, OS/400, AS/400, S/390
Parallel Enterprise Server, PowerVM, Power Architecture, POWER6+, POWER6,
POWER5+, POWER5, POWER, OpenPower, PowerPC, BatchPipes,
BladeCenter, System Storage, GPFS, HACMP, RETAIN, DB2 Connect, RACF,
Redbooks, OS/2, Parallel Sysplex, MVS/ESA, AIX, Intelligent Miner, WebSphere,
Netfinity, Tivoli and Informix are trademarks or registered trademarks of IBM
Corporation.
Linux is the registered trademark of Linus Torvalds in the U.S. and other
countries.
Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either trademarks or
registered trademarks of Adobe Systems Incorporated in the United States and/or
other countries.
SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects
Explorer, StreamWork, 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.
Business Objects and the Business Objects logo, BusinessObjects, Crystal
Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business
Objects products and services mentioned herein as well as their respective logos
are trademarks or registered trademarks of Business Objects Software Ltd.
Business Objects is an
SAP company.
Sybase and Adaptive Server, iAnywhere, Sybase 365, SQL Anywhere, and other
Sybase products and services mentioned herein as well as their respective logos
are trademarks or registered trademarks of Sybase, Inc. Sybase is an SAP
company.
All other product and service names mentioned are the trademarks of their
respective companies. Data contained in this document serves informational
purposes only. National product specifications may vary.
The information in this document is proprietary to SAP. No part of this document
may be reproduced, copied, or transmitted in any form or for any purpose without
the express prior written permission of SAP AG.
Oracle and Java are registered trademarks of Oracle and/or its affiliates.
UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group.
Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and
MultiWin are trademarks or registered trademarks of Citrix Systems, Inc.
HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®,
World Wide Web Consortium, Massachusetts Institute of Technology.
© 2012 SAP AG. All rights reserved.
24
© 2012 SAP AG. Alle Rechte vorbehalten.
Weitergabe und Vervielfältigung dieser Publikation oder von Teilen daraus sind,
zu welchem Zweck und in welcher Form auch immer, ohne die ausdrückliche
schriftliche Genehmigung durch SAP AG nicht gestattet. In dieser Publikation
enthaltene Informationen können ohne vorherige Ankündigung geändert werden.
Die von SAP AG oder deren Vertriebsfirmen angebotenen Softwareprodukte
können Softwarekomponenten auch anderer Softwarehersteller enthalten.
Microsoft, Windows, Excel, Outlook, und PowerPoint sind eingetragene Marken
der Microsoft Corporation.
IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5,
System x, System z, System z10, System z9, z10, z9, iSeries, pSeries, xSeries,
zSeries, eServer, z/VM, z/OS, i5/OS, S/390, OS/390, OS/400, AS/400, S/390
Parallel Enterprise Server, PowerVM, Power Architecture, POWER6+, POWER6,
POWER5+, POWER5, POWER, OpenPower, PowerPC, BatchPipes,
BladeCenter, System Storage, GPFS, HACMP, RETAIN, DB2 Connect, RACF,
Redbooks, OS/2, Parallel Sysplex, MVS/ESA, AIX, Intelligent Miner, WebSphere,
Netfinity, Tivoli und Informix sind Marken oder eingetragene Marken der IBM
Corporation.
Linux ist eine eingetragene Marke von Linus Torvalds in den USA und anderen
Ländern.
Adobe, das Adobe-Logo, Acrobat, PostScript und Reader sind Marken oder
eingetragene Marken von Adobe Systems Incorporated in den USA und/oder
anderen Ländern.
Oracle und Java sind eingetragene Marken von Oracle und/oder ihrer
Tochtergesellschaften.
HTML, XML, XHTML und W3C sind Marken oder eingetragene Marken des
W3C®, World Wide Web Consortium, Massachusetts Institute of Technology.
SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects
Explorer, StreamWork und weitere im Text erwähnte SAP-Produkte und
-Dienstleistungen sowie die entsprechenden Logos sind Marken oder
eingetragene Marken der SAP AG in Deutschland und anderen Ländern.
Business Objects und das Business-Objects-Logo, BusinessObjects, Crystal
Reports, Crystal Decisions, Web Intelligence, Xcelsius und andere im Text
erwähnte Business-Objects-Produkte und -Dienstleistungen sowie die
entsprechenden Logos sind Marken oder eingetragene Marken der Business
Objects Software Ltd. Business Objects ist ein Unternehmen der SAP AG.
Sybase und Adaptive Server, iAnywhere, Sybase 365, SQL Anywhere und
weitere im Text erwähnte Sybase-Produkte und -Dienstleistungen sowie die
entsprechenden Logos sind Marken oder eingetragene Marken der Sybase Inc.
Sybase ist ein Unternehmen der SAP AG.
Alle anderen Namen von Produkten und Dienstleistungen sind Marken der
jeweiligen Firmen. Die Angaben im Text sind unverbindlich und dienen lediglich zu
Informationszwecken. Produkte können länderspezifische Unterschiede
aufweisen.
Die in dieser Publikation enthaltene Information ist Eigentum der SAP. Weitergabe
und Vervielfältigung dieser Publikation oder von Teilen daraus sind, zu welchem
Zweck und in welcher Form auch immer, nur mit ausdrücklicher schriftlicher
Genehmigung durch SAP AG gestattet.
UNIX, X/Open, OSF/1 und Motif sind eingetragene Marken der Open Group.
Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame und
MultiWin sind Marken oder eingetragene Marken von Citrix Systems, Inc.
© 2012 SAP AG. All rights reserved.
25