An A-to-Z Guide to Implementing SAP HANA: Planning, Scoping, Staffing, Budgeting, and Execution

An A-to-Z Guide to Implementing SAP HANA: Planning, Scoping, Staffing, Budgeting, and Execution Dr. Bjarne Berg COMERIT

In Part 2 of This Session • Examine modeling options in SAP HANA Studio and see how views and tables can be created • Step through a demo on how to use SAP Data Services for loading data, building views, and accessing them from SAP BusinessObjects Explorer • Learn the various steps and options for migrating BW to HANA or building your own EDW on HANA • Take away a solid understanding of how HANA can be implemented in your organization

What We’ll Cover • Background • The standalone HANA project: Creating views and tables • SAP NetWeaver BW 7.4 on HANA – New features • Preparing for the BW upgrade and HANA migration • Cleaning up existing BW system and reducing HANA size • BW transformation and HANA optimization • Demo of non-BW HANA development • The Direct Migration Option (DMO) for BW • Project execution examples • Wrap-up

Background • SAP HANA is fundamentally a database and not “magic” • However, it provides major benefits in terms of: • Landscape simplification • Speed of queries and data access • Simplification of environment management • Long-term lower Total Cost of Ownership (really!) • An enabler of the next generation of ERP and data warehouses for real-time access, model simplification, agility, and support for Big Data volumes HANA projects should think big and act small. After the installation project is ended, what do you plan on deploying in terms of new capabilities?

Inside SAP HANA — In-Memory Computing Engine(ICME) 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

Tables Inside HANA Viewed Using HANA Studio We can view table definitions and data inside HANA Studio. This includes SAP BW tables, ERP tables, and tables we created ourselves.

An attribute view is normally used to expose master data to the front-end tools, such as those found in the SAP BusinessObjects tool suite They can also be accessed by any ODBC/JDBC compliant third-party tool Creating an Attribute View A view is a logical way to simplify the complexity of a database for users who need access

Adding Table Joins to More Customer Information In this example we are adding more information, such as country and language, to the view. This data was stored in other tables, but we want to make it easy for our users to access without database knowledge.

Applying a Filter to an Attribute View Sometimes we don’t want to give users access to all the information, and we can “hard code” a filter in our view In this example, we are limiting the view only to valid customers

Deciding What Users Should See and Assigning Key Attribute Sometimes, we don’t want to give access to everything available in the tables. We can then decide what fields are available to the users (these are flagged with orange balls)

Validate and Activate a View • HANA also provides a validation of each view to make sure it is correctly defined and meets all standards before it is activated • Once activated, you can expose it to your target users via standard HANA security

Creating an Analytical View • An analytical view is a way to join master data and transactional data together for simple access

Data Foundations and Logical Joins • The resulting models in the view can be further refined by controlling what is exposed to the end users • We call the transaction-oriented data the “data foundation” and can link other attribute views to this

Adding a Calculated Field to an Analytical View • We can also create our own calculations in the view • For example, we are now creating a new field called “net revenue” • This we have defined as “revenue” minimum “discount” • Complex math functions can be called and custom coding can also be added

Multi-Currency Handling • If we have loaded other currencies to the system, we can also add these capabilities to the view • I.e., in the advanced section of the calculated column, we can flag the field as using a source and a target currency and let the system take care of the translation

The Resulting Analytical View Views are found in the navigator section of the modeler inside HANA Studio. You can preview the results and also view metadata about the view and its definition.

Deploying Analytical View in SAP BusinessObjects Explorer The SAP BusinessObjects tools can easily access the views inside HANA Here we are building an Information Space in SAP BusinessObjects Explorer that is accessing the view we just created

Accessing the View Through an Information Space All the fields we exposed and created in our analytical view are now displayed here for easy access by the SAP BusinessObjects Explorer tool We don’t have to do any more development work!

The Result in SAP BusinessObjects Explorer Users can now navigate the HANA views at sub-second speed without having to develop anything further More complex analysis can also be added via other SAP BusinessObjects tools

HANA Development Demo — Step by Step In this demo, we will look at our example in more detail and also see how to load data to our HANA system using SAP Data Services

SAP HANA — Some of the Front-End Connections All SAP BusinessObjects tools can be connected to SAP HANA. You can even access HANA using Microsoft Query and other tools.

New Business Content Optimized for BW on HANA SAP has developed specific, new content for BW on HANA This takes advantage of HANA optimized transformations, consolidated InfoObjects, more line item details, and in some cases leverages the BW models and the HANA models in combined scenarios For those “green fielding” a HANA implementation, or planning to develop analytics in these areas, serious consideration should be spent on leveraging this new content

SAP HANA and BW 7.4 • BW 7.4 on HANA introduces the Smart Data Access that allows us to create: • Virtual table (real-time) links to a source system • CompositeProviders to link BW DSOs and “native” HANA views and tables • Create a flexible logical EDW that can be remodeled without moving data • We also get a shared modeling tool based on Eclipse Using the new Operational Data Provisioning (ODP) we can also remove the need for PSAs, thereby simplifying data architectures and reduce data latency

SAP HANA and BW 7.4 — Open ODS View • For BW 7.4, we get a new object called an Open ODS View • This new object allows us to include external data models in BW and to query BW and non-BW data in a single BEx query In the past, BW has been criticized for the complexity to integrate non-BW data in an EDW. The new Open ODS view and the new CompositeProvider solves most of these issues.

Cool New Feature: Automatically Generate HANA Models from BW • You can now automatically create new HANA models based on InfoCubes and DSOs in BW • The generated HANA views access the data in the InfoProviders • All HANA View capabilities are available for the BW data • BW Security is leveraged, and views can be accessed by tools such as SAP BusinessObjects Explorer, SAP Lumira, and other SAP BusinessObjects tools In addition to the BW model, import in the HANA modeler, this functionality leverages the existing metadata in BW to create new views

Some of the features in BW 7.4 are HANA specific Others are available for traditional RDBMs This is an overview of what is available for HANA and non-HANA BWs

For More Information on BW 7.4 Feature • For more on this and an in-depth view of the new BW 7.4 features, join me at:“Deep dive into leveraging new features and functions of SAP NetWeaver BW 7.4”

The SAP_BW_HOUSEKEEPING Task List • If you are on 7.0 SP32 of higher, you can generate an SAP BW Housekeeping task list and get automated help in cleaning the system weeks before upgrading it • Checks BW metadata with DDIC • Delete RSTT traces • Delete BW statistical data • Delete Aggregate data via deactivation • Ensure partitioned tables are correctly indexed for PSA • Ensure request consistencies in the PSA • Re-assign requests written into the incorrect PSA partition • Verify DataSource segments assignment to PSA • Deletes the entries no longer required in table RSIXW • Clear all OLAP Cache parameters • Repair InfoCube fact table indices at Data Dictionary level • Reorganize and delete bookmark IDs & view IDs You first have to install the program from SAP Note 1829728 before you can generate the SAP_BW_HOUSEKEEPING task list using tcode STC01

The SAP_BW_BEFORE_UPGRADE Task List • If you are on 7.0 SP31 or higher, you can generate a before the upgrade tasks list and get help in preparing the system for the upgrade • The more of these tasks you complete, the faster the upgrade may proceed since you are reducing size and complexity, while assuring that your system is ready for the 7.4 upgrade You first have to install the program from SAP Note 1734333 before you can generate the SAP_BW_BEFORE_UPGRADE task list using tcode STC01

Pre-Steps — Cleaning Up Your BW System • You can save significant amounts of work by doing a cleanup effort before you start your HANA migration or BW upgrade project • For example, an international company had a BW system with over 108TB, with only 36TB in the production box and the remaining data on their Near-Line Storage (NLS) solution • This cleaned BW system saved them potentially millions of dollars in hardware and HANA licensing costs It is not unusual to reduce a BW system size by 20-30% during a clean up effort

12 Pre-Steps — Cleaning up Your BW System • Clean the Persistent Staging Area (PSA) for data already loaded to DSOs. • Delete the Aggregates (summary tables). They will not be needed again. • Compress the E and F tables in all InfoCubes. This will make InfoCubes much smaller. • Remove data from the statistical cubes (they start with the technical name of 0CTC_xxx). These contain performance information for the BW system running on the relational database. You can do this using the transaction RSDDSTAT or the program RSDDSTAT_DATA_DELETE to help you. • Look at the log files, bookmarks, and unused BEx queries and templates (transaction RSZDELETE). • Remove as much as possible of the DTP temporary storage, DTP error logs, and temporary database objects. Help and programs to do this are found in SAP Notes 1139396 and 1106393.

12 Pre-Steps — Cleaning up Your BW System (cont.) • For write-optimized DSOs that push data to reportable DSOs (LSA approach), remove data in the write- optimized DSOs. It is already available in higher level objects. • Migrate old data to Near-Line Storage (NLS) on a smallserver. This will still provide access to the data for the few users who infrequently need to see this old data. You will also be able to query it when BW is on HANA, but it does not need to be in-memory. • Remove data in unused DSOs, InfoCubes, and files used for staging in the BW system. This includes possible reorganization of master data text and attributes using process type in RSPC.

12 Pre-Steps — Cleaning up Your BW System (cont.) • You may also want to clean up background information stored in the table RSBATCHDATA. This table can get very big if not managed. You should also consider archiving any IDocs and clean the tRFC queues. All of this will reduce the size of the HANA system and help you fit the system tables on the master node. • In SAP Note 706478, SAP provides some ideas on how to keep the Basis tables from growing too fast in the future; if you are on Service Pack 23 on BW 7.0 or higher, you can also delete unwanted master data directly (see SAP Note: 1370848). • Finally, you can use the program RSDDCVER_DIM_UNUSED to delete any unused dimension entries in your InfoCubes to reduce the overall system size.

A Tool to Help to Migrate and Clean Up • SAP has created a cockpit to: • Clean up the SAP BW system • Reduce system size • Conduct pre-checks (readiness checks) • Size the system • Find sub-optimal code (i.e., transformations) • Look at table distributions and loads • There are over 235 tests in this tool as of version 3.0 in March 2014 These tools are thanks to SAP’s Marc Bernard and his team at SAP Labs Canada

Tips to Make the Database Smaller • Use write-optimized DSOs as first level data stores. These caneasily be off-loaded out of main memory in HANA and save you money. • Keep your Persistent Staging Tables (PSA) clean. BTW: The PSA is often not needed at all in BW 7.4. • If you are on BW 7.3 Service Pack 8 and HANA with at least Service Pack 5, the write-optimized DSOs and PSAs are flagged as “early unload” from the HANA memory. This will help you keep the system smaller and require less memory. • You can also flag other InfoCubes, DSOs, tables, and partitions as “not active.” If you do so, they will only be loaded into memory when actually required. The sizing program in SAP Note 1736976 takes these size savings settings into account when sizing your HANA system

Clean Up of SAP BW Before Migration — Tool Support • Areas of database size reduction • Delete application logs (and/or archive) • Delete job logs • Delete OLAP and planning statistics • Delete data from the change log • Delete requests from the PSA • Delete master data and texts for a characteristic • Delete data selective • DTP temporary storage reduction • Archive IDocs • Archive request administration data • The Technical InfoCube (0TCT_C25) contains information on large tables that can be candidates for further reduction For more details on how to get sizes down and what each of these areas mean, see the Intro in SAP HANA: An Introduction, Chapter 5

BW Transformation Finder • Some custom data transformations in SAP BW can be sub-optimally written and have negative impact on HANA performance • The BW Migration Cockpit provides a tool to help you identify them This part of the tool can be run on SAP BW 3.5 or higher. See SAP Note 1908367 for more details.

Code Inspector and Analyzer for ABAP in BW • After you have found the codes you searched for, you can analyze the code to see if improvements can be made to make it even faster in HANA • You get a lot of additional information in the log (scroll to the end for some great ideas of how to optimize the code) This tool also runs on all BW releases 3.5 or higher (see SAP Note 1847431)

System Migration Optimization and SUM • The Software Update Manager (SUM) was enhanced and now also contains an option called the “Database Migration Option (DMO)” • The DMO allows you to combine the BW upgrade, Unicode conversion, and other tasks that are often required, as well as the actual HANA migration in one interface instead of two “projects” You can use the DMO if your are on minimum SAP BW version 7.0 and have applied Service Pack 17 (see SAP Note 1799545)

BW 7.4 DSOs and Re-Conversion of DSOs • BW optimized DSOs were recommended in 2013. • HANA optimized DSOs were available for BW 7.3. As of February 2014, SAP recommends that you do not to do any optimization of DSOs and actually convert back to the old DSOs for SAP (Note: 1849498)

Converting InfoCubes and/or Data Flows • While not required, InfoCubes can be optimized further for HANA performance • This basically means “flattening” the data structures and removing the dimensions in BW from the physical layer (they still look as if they exists) Many refer to this optional step as a “functional migration” and do this after the HANA migration has been completed, often as a separate initiative (see SAP Note 1849497) PS! Optimization of DSOs are no longer recommended

Converting InfoProviders and/or Data Flows • To help you, the SAP Migration Cockpit also allows you to migrate your data flows from 3.x to Data Transfer Processes (DTPs) as used in versions 7.0 and higher • If you convert the data flows you get better automated data package DTP optimization, which loads data faster into HANA. • You can also simulate the data flow before you do the real conversion. When doing so, data is loaded for both versions (3.x and 7.x) of the dataflows and the results are stored in cluster tables. The data is then compared to verify that the dataflow after migration calculates the same data as it did before migration. • Since the differences are displayed separately, you can analyze the results and changes in details

The Application-Specific Upgrade (ASU) Toolbox • Once you have completed the SAP_BW_HOUSEKEEPING and the SAP_BW_BEFORE_UPGRADE task lists, you can start the ASU schedule manager and prepare the environment • While there are some overlapping tasks in these task lists, you are required to complete all tasks listed as “obligatory” • You start the upgrade by executing the transaction /ASU/START (see SAP Note 1000009) The Direct Migration Option (DMO) for upgrading BW and moving to HANA at the same time also uses these task lists and the ASU Toolbox. DMO is an option in the SUM tool.

Demonstration: Non-BW HANA Development

An A-to-Z Guide to Implementing SAP HANA: Planning, Scoping, Staffing, Budgeting, and Execution