Session Title - Lenoir-Rhyne University

Session Title - Lenoir-Rhyne University

Advanced performance optimization with SAP BW 7.3 and SAP BW 7.4 Dr. Bjarne Berg Comerit Copyright 2015 Wellesley Information Services, Inc. All rights reserved. In This Session Get practical tips and techniques for maintaining and cleaning an SAP BW system for optimal performance, including PSA optimization, compression, maintaining statistical cubes, and controlling growth, reducing log file sizes, removing DTP temporary storage, DTP error logs, and temporary database objects. Reduce the size of an SAP BW system by as much as 70% by taking steps such as removing PSAs, aggregating, and optimizing InfoCubes, and implementing the new LSA++ architecture How to clean batch tables and reduce the footprints un-needed data.

Learn how to take advantage of new performance features in BW 7.3 and BW 7.4 2 What Well Cover BW 7.3 Performance Basics Householding tasks in SAP BW 7.3 and 7.4 Demo: Optimal SAP BW 7.4 on HANA performance 12-step pre-HANA migration cleanup tasks Demo: BW 7.4 performance monitoring BW Optimization after HANA migration LSA vs. LSA++ Optimization and SPOs Wrap-up 3 BW 7.3 (non-HANA) InfoCube Design - Line Item Dimensions

Line item dimensions are basically fields that are transaction oriented Once flagged as a line item dimension, the field is actually stored in the fact table and has no table joins This may result in improvements to query speeds for cubes not in BWA or HANA Explore the use of line item dimensions for fields that are frequently conditioned in queries. This model change can yield faster queries. 4 BW 7.3 (non-HANA) InfoCube Design High Cardinality Flags High-Cardinality flag for large InfoCubes with more than 10 million rows InfoCube FIUC_C03 ZGAT_C01 FIIF_C02

FIGL_C01 Number of rows 12,859,780 20,793,573 68,090,967 156,738,973 Entries in dimension compared for F table 37% 46% 102% 88% Real Exam p le At this company there were 11 InfoCubes with a ratio of more than 30% of the records in the dimensions vs. fact table SAP recommends for Indexing and performance reasons to flag these as highcardinality dimensions. However, it has minor impact to smaller cubes. In this example, there were four medium and large InfoCubes that are not

following the basic design guidelines, and subsequently had slow performance Many companies should redesign large InfoCubes with high-cardinality to take advantage of the standard performance enhancements available. 5 BW 7.3 (non-HANA) DSO Design and Locks on Large Oracle Tables Real Exa Additionally, 101 DSO objects were flagged as being reportable. This resulted in System IDs (SIDs) being created during activation. Combined, these resulted in frequent locks on the Oracle database and failed parallel activation jobs Millions In this example, many of the very large DSOs are not partitioned, and several objects have over 250 million records 425 400 375

350 325 300 275 250 225 200 175 150 125 100 75 50 25 - mpl e DSO Number of Records Partition DSOs. The lock on very large DSOs during parallel loads are well known and SAP has issued several notes on the topic: 634458 'ODS object: Activation fails - DEADLOCK' and 84348 'Oracle deadlocks, ORA-00060.' 6 The BW 7.3 DataFlow Generation Wizard SAP

NetWeaver BW 7.3 has a new, step-bystep wizard that allows you to generate data flows from flat files or existing data sources A great benefit is that the wizards work against any InfoProvider; i.e., you can use the wizards to create loads from DSOs to DSOs or InfoCubes This Thiswizard wizardreduces reducesthe thenumber numberor ormanual manualsteps stepsneeded neededtotoload loaddata. data.ItIt also alsosimplifies simplifiesthe thedevelopment developmentprocess processand andmakes

makesETL ETLwork workmuch mucheasier. easier. 7 Database Performance (non-HANA systems) Real Exa Database statistics are used by the database optimizer to route queries. Outdated statistics leads to performance degradation. Outdated indexes can lead to very poor search performance in all queries where conditioning is used (i.e., mandatory prompts) The current sampling rates for this example were too low, and statistics should only be run after major data loads, and could be scheduled weekly

For many systems, database statistics are outdated and may cause database performance to perform significantly poorer than otherwise would be the case. Sampling should often be changed and process chains may be re-scheduled. mpl e 8 The OLAP Memory Cache Size Utilization The OLAP Cache is by default 100 MB for local and 200 MB for global use Real Exam p le The system at this company was consuming no more than 80MB on average This means that most queries were re-executing the same data (good hit ratio of over 90%) 9 SAP HANA and BW 7.4

For BW 7.4 on HANA, SAP has continued to move more of the process intensive functions from the application to the DB server This takes advantage of the performance improvements of an in-memory DB It also reduces the need for data transfers between application and DB server The benefits of this approach are dramatically faster data activation, data transformations, and query executions 10 What Well Cover BW 7.3 Performance Basics Householding tasks in SAP BW 7.3 and 7.4 Demo: Optimal SAP BW 7.4 on HANA performance 12-step pre-HANA migration cleanup tasks

Demo: BW 7.4 performance monitoring BW Optimization after HANA migration LSA vs. LSA++ Optimization and SPOs Wrap-up 11 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 108 TB, with only 36 TB 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

The SAP_BW_HOUSEKEEPING Task List 1. 2. 3. 4. 5. 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 6. Ensure request consistencies in the PSA 7. 8. 9. 10. 11. 12. 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 13 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 tool are over 235 tests in this

These tools are thanks to SAPs Marc Bernard and his team at SAP Labs Canada 14 More Tips to Make the Database Smaller Use write-optimized DSOs as first level data stores. These can easily 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 partitioned 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

15 What Well Cover BW 7.3 Performance Basics Householding tasks in SAP BW 7.3 and 7.4 Demo: Optimal SAP BW 7.4 on HANA performance 12-step pre-HANA migration cleanup tasks Demo: BW 7.4 performance monitoring BW Optimization after HANA migration LSA vs. LSA++ Optimization and SPOs Wrap-up 16 Demo: Optimal SAP BW on HANA performance 17 What Well Cover

BW 7.3 Performance Basics Householding tasks in SAP BW 7.3 and 7.4 Demo: Optimal SAP BW 7.4 on HANA performance 12-step pre-HANA migration cleanup tasks Demo: BW 7.4 performance monitoring BW Optimization after HANA migration LSA vs. LSA++ Optimization and SPOs Wrap-up 18 12 Pre-Steps Cleaning up Your BW System 1. 2. 3. 4. 5.

6. 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. 19 12 Pre-Steps Cleaning up Your BW System (cont.) 7. For write-optimized DSOs that push data to reportable DSOs (LSA approach), remove data in the writeoptimized DSOs. It is already available in higher level objects. 8. Migrate old data to Near-Line Storage (NLS) on a small server. 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. 9. 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. 20 12 Pre-Steps Cleaning up Your BW System (cont.) 10. 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. 11. 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). 12. Finally, you can use the program RSDDCVER_DIM_UNUSED to delete

any unused dimension entries in your InfoCubes to reduce the overall system size. 21 What Well Cover BW 7.3 Performance Basics Householding tasks in SAP BW 7.3 and 7.4 Demo: Optimal SAP BW 7.4 on HANA performance 12-step pre-HANA migration cleanup tasks Demo: BW 7.4 performance monitoring BW Optimization after HANA migration LSA vs. LSA++ Optimization and SPOs Wrap-up 22 Demo: BW 7.4 Performance Monitoring In this demo we will explore the BW 7.4 on HANA DBA Cockpit Features 23

Demo: BW 7.4 Performance Monitoring 24 BW 7.3 Performance and Cockpit Capabilities BW 7.3 monitors and cockpit capabilities also include: Monitor of database usage and object sizes (i.e., InfoCubes, DSOs) Query usage statistics are more visible (similar to RSRT, RSRV, RSTT) We can see more of the use of SAP NetWeaver BW Accelerator and sizes Monitor for the actual use of OLAP/MDX Cache and hit ratios You can now selectively delete internal statistics in RSDDSTATWHM by date through the updated RSDDSTAT_DATA_DELETE ABAP program

There is also a MDX Editor for coding and syntax assistance Solution Manager has been updated to take advantage of these new monitors. 25 What Well Cover BW 7.3 Performance Basics Householding tasks in SAP BW 7.3 and 7.4 Demo: Optimal SAP BW 7.4 on HANA performance 12-step pre-HANA migration cleanup tasks Demo: BW 7.4 performance monitoring BW Optimization after HANA migration LSA vs. LSA++ Optimization and SPOs Wrap-up

26 Converting InfoProviders 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) 27 HANA Optimized BW 7.4 DSOs and Performance Improvements BW optimized DSOs are now created by default in HANA This means that data activations are done much faster at the HANA database layer The

change log is kept in a calculation view resulting in smaller DSOs HANA optimized DSOs are also available for BW 7.3, but now they are created by default, so do not convert DSOs to HANA-optimized. Fast activation is available for all standard DSOs without conversion to HANA-optimized. 28 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 29 What Well Cover BW 7.3 Performance Basics Householding tasks in SAP BW 7.3 and 7.4 Demo: Optimal SAP BW 7.4 on HANA performance 12-step pre-HANA migration cleanup tasks Demo: BW 7.4 performance monitoring BW Optimization after HANA migration LSA vs. LSA++ Optimization and SPOs Wrap-up 30 EDW Design Vs. Evolution An organization has two fundamental choices:

1. 2. 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 addons and workarounds. Failure to break with the past can be detrimental to an EDWs long-term success 31 Data Design The Use of Layered Scalable Architecture (LSA) Since SAP BW 7.3 SP-3 we have had a set of 10 templates to help build a layered data architecture for large-scale data warehousing The LSA consists logically of: Acquisition layer Harmonization/quality layer

Propagation layer Business transformation layer Reporting layer Virtualization layer 32 Example: Current LSA Data Architecture in SAP BW ERP BW DATA ACQUISITI ON CORPORATE MEMORY Germany DATA PROPAGATI ON Germany BUSINES S TRANS. Germany FLEXIBLE

REPORTI NG DIMENSION AL REPORTING Germany Germany 6 LSA Layers Europe (excl.Germany) Europe (excl.Germany) Europe (excl.Germany) Europe (excl. Germany) Europe (excl. Germany) Europe 2 Europe 2

Europe 2 Europe 2 ERP Table 41 total Data Source Transfer Rule Info Source objects Europe 2 Europe 3 Europe 3 Europe 3 Europe 3 Data Acquisition Europe 3 USA USA

USA USA USA Americas 1 8 semantic partitions Americas 1 Americas 1 Americas 1 Americas 1 Americas 2 Americas 2 Americas 2 Americas 2 Americas 2 Asia Asia

Asia Asia Asia 33 33 Example: Simplified LSA++ Data Architecture ERP BW DATA ACQUISITION CORPORAT E MEMORY DATA PROPAGATI ON BUSINES S TRANS. FLEXIBLE REPORTING

DIMENSIONAL Remove 3 LSA REPORTING layers ERP Table Europe Europe Europe Americas Americas Americas Asia Asia Asia Data Source

Transfer Rule Info Source 41 shrinks to 9 total objects Remove 5 semantic partitions 34 34 EDW - Complex Layered Architectures This BPC on BW system was experiencing substantial load performance issues Some of this was due to underlying SAP BW configuration, while some was due to the technical configuration of the data store architecture and data flow inside SAP BW Consolidation Cube

(OC_CON) BPC Staging Cube (BPC_C01) Consolidation Processes: 1) Clearing 2) Load 3) Foreign Exchange 4) Eliminations 5) Optimizations Real Exa m p le GL Summary Cube (FIGL_C03) Production Issues included: 1) Dependent jobs not running sequentially, i.e., load from Summary cube to Staging cube is sometimes executed before the summary cube data is loaded and activated, resulting in zero

records in the staging cube. 2) Long latency with 6 layers of PSA, DSOs, and InfoCubes before consolidation processes can be executed. Conformed Reportable DSO Write Optimized DSO FIGL_D21 FIGL_D15S FIGL_D20 FIGL_D17 FIGL_D14 FIGL_D18 FIGL_D13S FIGL_D10S

FIGL_D08 FIGL_D11S Persistent Staging Area (PSA) ECC 6.0 AsiaPacific ECC 6.0 NorthAmerica ECC 4.7 LatinAmerica R/3 3.1i EU ECC 4.7 ASIA 35 Fixes to Complex EDW Architecture The fix to this system included removing the conformed DSO layer, with BEx flags for data stores that are never reported on.

Also, the BPC staging cube served little practical purpose since the data is Consolidation Processes: 1) Clearing 2) Load already staged in the GL Summary cube 3) Foreign Exchange 4) Eliminations and the logic can be maintained in the 5) Optimizations load from this cube directly to the Real consolidation cube. Exam p le Consolidation Cube (OC_CON) GL Summary Cube (FIGL_C03) Long-term benefits included reduced data latency, faster data activation, less data replication, smaller system backups as well as simplified system maintenance.

Write Optimized DSO FIGL_D15S FIGL_D13S FIGL_D10S FIGL_D08 FIGL_D11S Persistent Staging Area (PSA) ECC 6.0 AsiaPacific ECC 6.0 NorthAmerica ECC 4.7 LatinAmerica R/3 3.1i EU ECC 4.7

ASIA 36 EDW Data Design Classical Use of MultiProvider Hints in BW Problem: To reduce data volume in each InfoCube, data is partitioned by Time period. 2002 2003 2004 2005 2006 2007 2008 A query must now search in all InfoProviders to find the data. This is very slow. Solution: We can add hints to guide the query execution. In the RRKMULTIPROVHINT table, you can specify one or several characteristics for each MultiProvider, which are then used to partition the MultiProvider into BasicCubes. If a query has restrictions on this characteristic, the OLAP processor is already checked to see which part of the cubes can return data for the query.

The data manager can then completely ignore the remaining cubes. An entry in RRKMULTIPROVHINT only makes sense if a few attributes of this characteristic (that is, only a few data slices) are affected in the majority of, or the most important, queries (SAP Notes: 911939. See also: 954889 and 1156681). 37 BW 7.3 and higher - Semantic Partitioned Objects (SPO) When data stores and InfoCubes are allowed to grow over time, the data load and query performance suffers Normally objects should be physically partitioned when the numbers of records exceed 100 200 million However, this may be different depending on the size of your hardware and the type of database you use In SAP NetWeaver BW 7.3 we get an option to create a Semantic Partitioned Object (SPO) through wizards You can partition based on fields such as calendar year, region, country, etc. 38 Data Design - Semantic Partitioned Objects (cont.)

When an SPO is created, a reference structure keeps track of the partitions. The structure is placed in the MultiProvider for querying. SPO Wizards create all Data Transfer Processes (DTP), transformations, filters for each data store, and a process chain 39 What Well Cover BW 7.3 Performance Basics Householding tasks in SAP BW 7.3 and 7.4 Demo: Optimal SAP BW 7.4 on HANA performance 12-step pre-HANA migration cleanup tasks Demo: BW 7.4 performance monitoring BW Optimization after HANA migration LSA vs. LSA++ Optimization and SPOs Wrap-up 40

Where to Find More Information Introduction to SAP HANA by Bjarne Berg and Penny Silvia, SAP Press 3rd edition. http://scn.sap.com/docs/DOC-35096 SCN SAP NetWeaver Business Warehouse 7.4 http://help.sap.com/nw_platform Help SAP BW 7.4 web site http://www.stechno.net/sap-notes.html?view=sapnote&id=153967 SAP Business content release note for BW 7.4 41 7 Key Points to Take Home BW 7.4 is the first release to take full advantage of SAP HANA

Some of the functions in 7.4 are also available to non-HANA customers The new CompositeProviders and the Open ODS View makes HANA and BW tightly integrated and capable to support EDWs better Yu should break-from the past and start designing with the new BW 7.4 features in-mind The new monitoring features in the BW DBA Cockpit and the HANA systems makes it much easier to see what is occurring from a database level for the non-basis team. Before you size your system, clean it up and save hardware costs. All customers should consider the BW move to HANA in 2015! 42

Your Turn! How to contact me: Dr. Berg [email protected] Please remember to complete your session evaluation 43 Disclaimer SAP, R/3, mySAP, mySAP.com, SAP NetWeaver, Duet, PartnerEdge, 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 in several other countries all over the world. All other product and service names mentioned are the trademarks of their respective companies. Wellesley Information Services is neither owned nor controlled by SAP. 44

Recently Viewed Presentations

  • Présentation PowerPoint - APPAC

    Présentation PowerPoint - APPAC

    Un peu d'histoire 1970 Ryerson Toronto 1975 HEC Montréal 1980 Université Laval 1981 UQTR 1986 Cégep Limoilou et Cégep Ahuntsic 1988 Centre d'entrepreneuriat du Cœur du Québec (Micheline Locas, ACEE de la francophonie) 1990 Intégration de l'entrepreneuriat dans les universités...
  • PowerPoint Presentation

    PowerPoint Presentation

    In Barron Field's poem 'The Kangaroo' (1819) the kangaroo was described as looking like a cross between a deer and a squirrel; the platypus as a cross between a duck and a mole. These linguistic and graphic inaccuracies are intimately...
  • New Developments in WordNet: An Electronic Lexical Resource

    New Developments in WordNet: An Electronic Lexical Resource

    Reorg of the top levels All noun hierarchies are now subsumed under a single synset, { entity }. The reorganization brings WordNet more in alignment with ontologies that would map into it (e.g., SUMO) The noun file is now structured...
  • Review: Uses of the Subjunctive

    Review: Uses of the Subjunctive

    The Purpose Clause. The Purpose clause is a subordinate use of the subjunctive expressing purpose or obligation. - "We come to class so that we may learn about Latin." A . purpose clause. answers the question "why" or "for what...
  • Container Classes & Exception Handling

    Container Classes & Exception Handling

    Operator Overloading. ostream& operator << (ostream & out, const point & p) {out << "(" << p.m_X << ", " << p.m_Y << ")"; return out;} // example 1
  • Title of Presentation - Rexburg Hams

    Title of Presentation - Rexburg Hams

    Focus of HamNet is Emergency Communications. Since the beginning of the BroadBand-HamNetproject, the focus has been on emergency communications. HamNet is one more tool in our emergency communication tool belt. More emergency communicators with HamNet knowledge and experience are needed
  • Figurative Language - English Language Arts

    Figurative Language - English Language Arts

    Unnecessary Words Redundancies An expression, usually two words, in which the first or second word is not needed because the second word cannot be qualified. Figurative Language Cheat Sheet Alliteration Assonance Allusion Simile Metaphor Imagery Oxymoron Symbol Hyperbole Personification Onomatopoeia...
  • NEW WAYS OF THINKING - Alvord Unified School District

    NEW WAYS OF THINKING - Alvord Unified School District

    new ways of thinking about . government. Laissez Faire . economists argued that government should leave business alone to operate "naturally" ... "iron law of wages" "There can be no rise in the value of labor without a fall of...