Session2: Implementing SAP HANA

Session2: Implementing SAP HANA Dr. Bjarne Berg Associate professor Computer Science Lenoir-Rhyne University

Introduction This second session is more technical as we explore what HANA is and how it works for real-time data warehousing and fast access to big data. We will look inside HANA, examine its capabilities and interfaces, and see how to size, plan, and install HANA based on examples from several real companies. You will also see several HANA demos on how to create tables, how to quickly load data, and how to execute hyper-fast reports using BusinessObjects Analysis, WebIntelligence, dashboards, Crystal Report, BI Self-Service through modules, and BI Workspaces on HANA.

EDW Design Vs. Evolution An organization has two fundamental choices: • Build a new well architected EDW • Evolve the old EDW or reporting system Both solutions are feasible, but organizations that selects an evolutionary approach should be self-aware and monitor undesirable add-ons and ‘workarounds”. Failure to break with the past can be detrimental to an EDW’s long-term success…

What is Analytics? • Discovery and communication of meaningful patterns in data. • Applied to business data, to describe, predict, and improve business performance. • Analytic applications often favor data visualization to communicate insight. • Intelligent data • Behavioral data, sensor data, transactional data, market research, … • Questions (e.g., marketing): • Who are my core customers? • What drives their behavior? • Can I predict change? • Can I segment? • What has led up to this point?

Session Agenda • Introduction • The Components of SAP HANA • Sizing, planning and installing HANA • New EDW Data Design In HANA • Demo: Performance dashboard from from real implementation • Demo: HANA building tables, data loads & deploying BusinessObjects • Demo: Deploying Analyics with HANA and BOBJ • Questions and Answers

Looking Inside SAP HANA — In-Memory Computing Engine Persistence Layer Disk Storage Data Volumes Page Mgmt. Session Manager Load Controller Replication Server Metadata Manager Relational Engine -Row Store -Column Store Logger Log Volumes MDX SQL Parser Authorization Manager SQL Script Transaction Manager Calculation Engine Inside the Computing Engine of SAP HANA, we have many different components that manage the access and storage of the data. This includes MDX and SQL access, as well as Load Controller (LC) and the Replication Server. BusinessObjects Data Services

Session Agenda • Introduction • The Components of SAP HANA • Sizing, planning and installing HANA • New EDW Data Design In HANA • Demo:Performance dashboard from from real implementation • Demo: HANA building tables, data loads & deploying BusinessObjects • Demo: Deploying Analyics with HANA and BOBJ • Questions and Answers

A T-Shirt Model for Sizing HANA on BW A T-shirt model is a quick way to get some basic ideas on what a system may look like. While very inaccurate for sizing, it provides basic information for those just staring considering SAP HANA The number of processors are largely driven by the number of users and usage patterns. Serious consideration should be made before buying hardware.

A Rule-Of-Thumb Approach to Sizing HANA - Memory Memory can be estimated by taking the current system size, and running the programs in ”get_size.zip” in SAP Note 1637145 to get row and column store sizes for your system. The 50 GB is for HANA services and caches. The 1.5 is the compression expected for rowstore tables and the 4 is the compression expected for column store tables. The 2-factor refers to the space needed for run-time objects and temporary result sets in HANA. Finally the term ‘existing DB compression’ is to account for any compression already done in your system (if any). Memory = 50 GB + [ (rowstore tables footprint / 1.5) + (colstore tables footprint * 2 / 4) ] * Existing DB Compression Remember, these are quick rules-of-thumb, so don’t rely on it for finalized budgeting and hardware purchases.

A Rule-Of-Thumb Approach to Sizing HANA - Disk The next item you need is disk space, which can be estimated by the following In this example, you need 4 x 710 GB disk for the persistence layer and about 710 GB for the logs. This equals around 3.5TB (don’t worry, disk space of this size is now almost “cheap”). The persistence layer is the disk that keeps the system secure and provides for redundancy if there are any memory failures, so it’s important not to underestimate this. Disk for persistence layer = 4 Memory Disk for the log = 1 Memory Remember, these are quick rules-of-thumb, so don’t rely on it for finalized budgeting and hardware purchases.

A Rule-Of-Thumb Approach to Sizing HANA - CPU The CPUs are based on the number of cores that you include. For example 10 core CPUs now exist (depending on when you bought your system). If you have a single node with 4 x 10 cores, you will have 40 cores and can handle 200 active users on that hardware node, and quite a larger number of named users. CPU = 0.2 CPU cores per active user Remember, these are quick rules-of-thumb, so don’t rely on it for finalized budgeting and hardware purchases.

SAP QuickSizer tool for HANA There are three versions of the tool for each versions of SAP HANA. The Quick Sizer for the Rapid Deployment Solutions (RDS) allows you to size for specific tools. The second Quick Sizer version is for SAP HANA on SAP NetWeaver BW The last is for those who want to use SAP HANA as a standalone platform for in-memory data (i.e., using SAP Data Services to load data to). SAP’s Quick Sizer for SAP HANA, is available at http://service.sap.com/quicksizer.

An Alternative SAP BW on HANA Sizing Tool SAP has released a new ABAP based tool that generates a report significantly better sizing fro SAP BW than using just the QuickSizer above. This program takes into consideration existing database compression, different table types and also include the effects of non-active data on the HANA system. The higher precision you run the estimate at the longer the program is going to run. To increase speed, you can also suppress analysis tables with less than 1 MB size. With 14 parallel processors and 8Tb data warehouse, it is not unusual to see 45-75 minutes run time.

SAP BW on HANA Sizing Tool Since timeouts are common when running the sizing program, you can temporarily change the parameter in rdisp/max_wprun_time to 0 in BW transaction RZ11. Finally, you estimate the growth for the system as a percentage, or as absolute growth. After you have downloaded and installed the program, and-selected the parameters above, you can go to SE38 and run SDF/HANA_BW_SIZING as a background job. The output is stored in the file you specified and the file can now be email emailed to hardware vendors for sizing input and hardware selection. This program is attached to SAP Note: 1736976 on SAP Marketplace

Summary of HANA Sizing Approaches Approach Quality of Estimate Effort Required T-Shirt Sizing  Sort of ‘OK” Very Low Rule-of-Thumb  Better accuracy Low SAP QuickSizer  Much better High Sizing for BW program  Excellent Moderate • Work with your preferred vendor before ordering your hardware or finalizing your budgets. Doing a correct HANA sizing effort is essential for not buying a too small, or too big HANA system.

Staffing a HANA Migration Project – Small Team Real Example System Profile Raw data size: 2.7 TB Complexity: Medium DataStores: 87 InfoCubes: 63 Queries: 409 Duration: 14 weeks Environments: 4+1 Risk aversion: Medium Other usage: IP • This assumes that the test team is dedicated for 3 weeks during the migration of QA and Prod environments • The test team from the business is already experienced users of the BW system and need minimal training • HANA Optimization of InfoCubes and DSOs are currently for SD only for this organization. This organization is using BWA and will be retiring it as part of the HANA migration

Staffing a HANA Migration Project – Medium Team Real Example System Profile Raw data size: 5.6 TB Complexity: Medium DataStores: 439 InfoCubes: 603 Queries: 1,300+ (incl. BOBJ) Duration: 18 weeks Environments: 4 Risk aversion: HIGH Other usage: None • This assumes testing of core queries in BEx and WebIntelligence is done by the business • The data reconciliation and process chain testing is done by dedicated resources in each team. • This team must be staffed with experienced resources. HANA training for team members and hardware installs must be in place prior to project start.

Staffing a HANA Migration Project – Very Large Team System Profile Raw data size: 38TB Complexity: High DataStores: 1,300+ InfoCubes: 1,720+ Queries: 2,200+ Duration: 5 mos Environments: 4 Risk aversion: HIGH Other usage: APO, IP, BPC Real Example This assumes minimal additional functional optimization

Budgeting a HANA Migration Project - Systems There are a set of items you need to budget for. From a system perspective you will need to consider: • Hardware quotes Give at least two vendors your sizing estimate and ask for quotes. • Vendor Support Make sure your hardware vendor include 3-years support in your purchase • Upgrades Plan and budget for any DW upgrades required before going to HANA Do DW data cleanup, archiving and move non-needed data to Near-Line Storgae, and then conduct a sizing effort, before requesting a hardware quote.

Hardware Options for HANA There are currently 7 different certified HANA hardware vendors with 13 different products. Some boxes can be used as single nodes with others are intended for scale-out solutions for large multi-node systems

Small Example HW Quotes - Dell Real Example This is example is a quote for a smaller 128 GB Memory Box with 2 x 10 cores is based on Dell’s R910 platform for a HANA sidecar usage for less then $40,000 (including tax!) Most of the smaller HANA systems from the other vendors are similarly prices and depends on the number of boxes you buy, existing discount agreements and the size of the deals you are requesting. Expect competitive bids for larger systems and similar vendor pricing for similar capabilities

Mid-Size Budgeting Example HW Quotes - HP Real Example This example quote is for a mid-sized 512 GB memory box with 4 x 10 cores CPUs and 7 TB disks based on Hewlett-Packard's high-end DL-980 Box. Including all services and support agreements, this quote is only $150,000 Certified HANA vendors such as HP, IBM, Dell, Cisco, NEC, Hitachi and Fujitsu has dedicated staff to help you get a detailed quote in matter of days.

Large Example HW Quotes - Fujitsu Real Example This is example is a quote for a Large 1 TB Memory Box for only $105,000

Budgeting a HANA Migration Project - People Remember to budget for HANA training for your employees before the project starts Class schedules are found at: training.sap.com On average plan for $3,000 to $6,000 to train each team member on average plus travelling costs.

Budgeting a HANA Migration Project - People • Experienced HANA consultants are in very high demand, so budget $1,600 to $2,300 per day for these resources (US) • Testers with BW experience and some HANA training can be found for more normal consulting rates. • Solid hands-on migration experience with the latest versions is key a HANA migrations project. When staffing your HANA project, don’t schedule the start date before you get your staff. You want the best resources, not whoever is available.

On-Going Support Tasks and Staff Required Major on-going support tasks consists of: • User and role maintenance • Security maintenance • Backup and disaster recovery • Load balancing, monitoring and hardware maintenance • Software patches and notes for HANA, BW and Components • Cleanup, NLS, Archiving, log deletions • Transports, table copies, system copies and data copies • Periodic system upgrades While most tasks are similar to the old relational database systems, the way we do this is quite different. Make sure your HANA support staff is onboarded early and trained before cut-over to production of your migration project.

The Layered Scalable Architecture (LSA) • The LSA consists logically of: • Acquisition layer • Harmonization/quality layer • Propagation layer • Business transformation layer • Reporting layer • Virtualization layer

Data Design - Real Example of LSA Implementation Real Example This company implemented a full LSA Architecture and also partitioned the Infoproviders for faster data loads and faster query performance. While this architecture has benefits, there are significant issues around data volumes and the Total cost of Ownership when changes are made to the data model(s)

Data Design Example of LSA Simplification In HANA Real Example Since many of the benefits sought by the LSA architecture are inherent in HANA, significant simplifications can be made to the data design and data flows This design has a dramatically smaller cost of ownership (fewer objects to maintain, design and change) than the traditional LSAs

HANA Performance Demo Real Example

HANA Design, Build and Deploy Demo

Demo: Deploying Analyics with HANA and BOBJ

Questions and Answers Dr. Berg Bergb@lr.edu

Moving EDW to SAP HANA – Automated tool 1 SAP has a checklist tool for SAP NetWeaver BW powered by HANA (thanks Marc Bernard). In this tool, SAP provided automatic check programs for both the 3.5 version and the 7.x version of BW. These are found in SAP Note: 1729988. In version 2.x of this tool, hundreds of checks are done automatically in the BW system. This includes platform checks on database and application and system information. There are even basis checks for support packs, ABAP/JAVA stacks, Unicode, BW releases, and add-ons to your system.

Moving EDW to SAP HANA – Automated tool 1 The idea of the checklist tool from SAP is that you run it several times throughout the project. Once before you start, then periodically as you resolve issues and upgrade requirements, and then finally when the system has been migrated to HANA. The checklist tool also has specific checks for the HANA system that can help you identify any issues before turning over the system to end users..

Moving EDW to SAP HANA – Automated tool 2 SAP has released a new ABAP based tool that generates a report significantly better sizing fro SAP BW than using just the QuickSizer above. This program takes into consideration existing database compression, different table types and also include the effects of non-active data on the HANA system. The higher precision you run the estimate at the longer the program is going to run. To increase speed, you can also suppress analysis tables with less than 1 MB size. With 14 parallel processors and 8Tb data warehouse, it is not unusual to see 45-75 minutes run time.

Moving EDW to SAP HANA – Automated tool 2 Since timeouts are common when running the sizing program, you can temporarily change the parameter in rdisp/max_wprun_time to 0 in BW transaction RZ11. Finally, you estimate the growth for the system as a percentage, or as absolute growth. After you have downloaded and installed the program, and-selected the parameters above, you can go to SE38 and run SDF/HANA_BW_SIZING as a background job. The output is stored in the file you specified and the file can now be email emailed to hardware vendors for sizing input and hardware selection. This program is attached to SAP Note: 1736976 on SAP Marketplace

Moving EDW to SAP HANA – Automated tool 3 Many experienced developers are not aware that moving BW to HANA can in some cases result in slower transformations during data loads. a. Select for all entries (SFAE) statements without HANA DB hints --> add hints b. Select * --> specify fields to select c. Database access in the field routines --> move to start routine d. Loops which do not assign field symbols --> use field symbols e. Selects from master data tables --> Use the read master data rule f. Selects from DSOs --> Use the read DSO rule g. Direct updates to BW object tables --> Do not update tables directly

Session2: Implementing SAP HANA