Comprehensive Guidelines to Speed Data Analysis Using the In-Memory Analytical Engines from SAP

1. Comprehensive Guidelines to Speed Data Analysis Using the In-Memory Analytical Engines from SAP Dr. Bjarne Berg COMERIT

2. In This Session … • Get strategic advice for leveraging data warehousing and analytical engines from SAP to increase the speed and efficiency of data processing and analysis including: • SAP NetWeaver® BW Accelerator • SAP BusinessObjects Explorer • SAP HANA • SAP BusinessObjects Analysis • Explore ways to deploy OLAP tools within SAP BusinessObjects Analysis — via Microsoft Excel, PowerPoint, or the Web interface — and get criteria to map each approach to your analytical requirements • Demo the key differences between the Microsoft and OLAP editions of SAP BusinessObjects Analysis • You will come away with a deep understanding of what these solutions can do and when to use them

3. What We’ll Cover … • Introduction • SAP BW Accelerator Overview • BO Explorer Overview • SAP HANA Capabilities and Impact to Organizations • SAP BusinessObjects Analysis Capabilities • Other Performance Considerations • A Roadmap for getting to Next Generation BI • Wrap-up

4. Why In-Memory Processing? Focus Technology 1990 2012 Improvement 0.05 MIPS/$ 304.17 MIPS/$ 6083x CPU 0.02 MB/$ 52.27 MB/$ 2614x Memory 216 264 248x Addressable Memory 100 Mbps 100 Gbps 1000 x Network Speed 5 MBPS 620 MBPS 124x Disk Data Transfer Source: 1990 numbers SAP AG, 2012 numbers, Dr. Berg Source: BI Survey of 534 BI professionals, InformationWeek, 2010 Disk speed is growing slower than all other hardware components, while the need for speed is increasing.

5. What We’ll Cover … • Introduction • SAP BW Accelerator Overview • BO Explorer Overview • SAP HANA Capabilities and Impact to Organizations • SAP BusinessObjects Analysis Capabilities • Other Performance Considerations • A Roadmap for getting to Next Generation BI • Wrap-up

6. In Memory Processing — General Highlights — BWA BWA = SAP BW Accelerator

7. It Is All About Performance, Performance, Performance • It is hard to build a fast dashboard with many queries and panels without SAP NetWeaver BW Accelerator • This provides in-memory processing of queries that is 10-100 faster • What we simply do is place the data in-memory and retrieve itmuch faster • There is also some limited OLAP functionality that can be built into SAP BW Accelerator 7.3, but most data processing still occurs in the BI Analytical engine You can also place non-SAP data in-memory, using SAP BusinessObjects Data Services

8. In SAP BW Accelerator 7.2 you can handle more of the analytics processing, such as “top-5 products sales,” which was previously done in the BI analytical engine SAP BW Accelerator is accessed by queries and also by any jobs that use the application programming interface (SAPRSDRI_INFOPROV_READ) or the data read transaction (LISTCUBE), when aggregate flag is selected or any of the OLAP interfaces to third-party tools How Does SAP BW Accelerator Work in Reality? • SAP BW Accelerator 7.0 performs aggregation and data selection for the query, all other processing is done by the BI analytical engine (i.e., as the “OLAP processor”) • Therefore, not all query processing will be dramatically faster

9. Compression and TREX The TREX engine is NOT the same as the traditional implementation of the search engine BWA comes with its own TREX engine that collects the data During the index build, the data is also compressed using advanced mathematical methods The result is therefore a much smaller sized index than the original InfoCube SAP BI Accelerator (BIA) was renamed to SAP BW Accelerator in 2009

10. 3 Steps of InfoCube Index Creation First master data is processed (Y, S, and X) tables. Second, the fact tablesare grouped and indexed. This occurs by merging the E and F tables into one F table prior to indexing it. This is done automatically and does not change the basic cube. Third, the dimension tables are indexed. This include all D tables (potentially 16). The process starts with a database lock on each table. The next step is a data transfer to a temporary BWA file and transferring the data to BWA. The final step is to write the indexes to memory and activate it for queries.

11. The Rule of 50% and Impacts Example-1: Your sizing program says you need 48Gb of memory You need 48GB + 48Gb for temp indexes and 48Gb failover = 144Gb (66.66% overhead) Example-2: Your sizing program says you need 192 Gb of memory You need 192GB + 192Gb for temp indexes and 48 Gb failover = 432Gb (44% overhead) Buying hardware is not as easy as you may think … • First you need to make space for temporary index files. The recommended size is about 50%. • Second you need to have one fail-over blade in case of hardware issues.

12. Performance is Near-Linear: Benchmarks SAP BW Accelerator’s strength resides in its near-linear scalability Performance is measured in terms of: BW Accelerator index creation time Multi-user throughput per hour Average report response time Average number of records touched by each report SAP BW Accelerator reads data from InfoCubes. DSOs and InfoObjects are still read from base/physical tables (even when the InfoObject is indexed as part of master data).

13. BI Analytical Engine’s Query Executing Priorities Query ExecutionWithout SAP NetWeaverBW Accelerator Query ExecutionWith SAP NetWeaver BW Accelerator Information Broadcasting /Precalculation Information Broadcasting /Precalculation Query Cache Query Cache Aggregates SAP BW Accelerator InfoProvider Aggregates can be replaced with SAP BW Accelerator, while the memory cache is still useful.

14. Query Performance Improvements — Real Example of 70 Queries The slowest queries that spent most of their time on the database side benefitted the most Overall 36 of 70 queries were at least twice as fast. Before BWA, the average query execution took 58.8 seconds; after BWA the average query took 17.9 seconds (295% faster overall).

15. Query Performance Improvements OVERALL The major improvement is to make query execution more predictable and overall faster Number of Queries Seconds HINT: BWA will not be used if a query has a key figure set to NO1, NO2, or NOP (no aggregation) Number of Queries Seconds

16. Workbook Performance Improvements with BWA Workbooks require embedding of JavaScripts; can contain multiple queries; and may have macros, lookups, and internal logic It is therefore hard to estimate the real performance benefits of implementingSAP BW Accelerator In this real example of 20 slow workbooks, the average performance improvement for workbooks was 363% faster

17. Performance Information — BWA Indexes Build Times Creating new indexes (refreshes) can be time consuming. The build time depends on: a) The size of the records (length) b) The number of records c) The number of SAP BW Accelerator processors d) The network transfer speed In this example, 23 InfoCubes with over 232 million records were indexed in 90.56 minutes On average, 2.56 million records per minute were indexed!

18. Most Customers Have Found BWA Admin to Be Minimal The Admin work is done through a single interface The admin interface is available under the transaction code RSDDBWAMON/ RSDDBIAMON Health checks for SAP BW Accelerator are available under the transaction code RSRV Most companies plan for a maximum of 2-5 days of SAP BW Accelerator training. You need a maximum of 1-2 administrators (1 for backup).

19. RSDDBIAMON — BWA Administrator Tools • Restart BIA server: Restarts all the BWA servers and services • Restart BIA Index Server: Restarts the index server • Reorganize BIA Landscape: If the BWA server landscape is unevenly distributed, redistributes the loaded indexes on BWA servers • Rebuild BIA Indexes: If a check discovers inconsistencies in the indexes, delete and rebuild the BWA indexes.

20. Selective Usage of SAP BW Accelerator • You can turn off the SAP BW Accelerator usage for certain roles. This reduces the stress on the hardware for non-essential users. • You can also turn off the BWA uses forindividual users in • transaction code • SU01 You can see if anyone is using the BWA indexes in the table “RSDDSTATBIAUSE” and some details are also available in ‘RSRT’ after patch 16.

21. RSDDBIAMON — BWA Administrator Tools and Control • Connectivity checks • Checks if BW is still connected physically to the BW system • System Check • Checks if the blades and file system is operating normally • Load Monitoring • Keeps track of performance of the load process (read, writes, compression, and time spent) You can turn off the BWA index query availability for InfoCubes through the transaction “RSDDBIAMON2.”

22. Health-Checks and Reconciliation The BWA interface allows you to compare the data in SAP BW vs. the indexes. This means that you can easily check if they are outdated (service pack 12 required) Other tools include the ability to run queries to see if the numbers in the two databases match. You can check your installation using the function module “TREX_CHECK_BIA_INSTALLATION” or using the transaction code RSDDBIAMON.

23. Proposals and Estimations The Analysis and Repair options include proposals and time estimation tools that you may use The interface can propose delta-indexes for periodic updates You can estimate the runtime of indexing the fact table of an InfoCube before you place it into a process chain or a manual job You can estimate the memory needed before adding new records into memory You can use the ABAP program ‘’ZZ_SET_QUERY_NOHPA_FLAG to turn off BWA access for single queries in the RSRREPDIR table (SAP Note: 1161525 for BWA 7.0)

24. The SAP BW Accelerator “Reset Button” The simple way to fix most issues is to delete all indexes and rebuild them during a weekend Think of this as the ultimate “reset” button. You can also rebuild master data indexes • In TREXADMIN you can see the number of records in the BWA indexed Infocube. • Select the RFC Server, Execute. • Click on tab “Index Admin.” • For each InfoCube select BIA as the Index ID. The records are equal to the # of documents. • Estimations of this, based on statistics, are also available in: • RSDDV - BIA Indexes • SE16 - RSDDTREXDIR

25. Increasing Index Creation Performance You can check global parameters in RSRV and you can change global parameters in RSBATCH and RSDDBIAMON. To increase index creation performance, you can change the global parameters in SAP BW Accelerator Pay particular attention to the number of parallel processes available (max 10 per available physical processors) Make sure the memory buffers are set large enough to accommodate large InfoCubes Review consistency check recommendations in SAP Notes 1052941 and 1161967

26. Planning a 6-Week BWA Implementation An SAP BW Accelerator project can be completed in six weeks However, due to hardware lead-times (normally 4-6 weeks), most projects should plan a 12-week duration The time waiting on hardware should be used on performance tuning of existing queries and dashboards We will now look at the major tasks of implementing an SAP BW Accelerator solution

27. Sizing Is Critical, but Good Tools Are Available SAP has integrated key sizing parameters for SAP BW Accelerator in the new Quick Sizer. You can give it a try at:http://service.sap.com/quicksizer (requires login credentials for the SAP Service Marketplace) Customers with existing BI systems and data loaded can get good sizing and compression estimates by running the SAP program available in SAP Note 917803.

28. Training for SAP BW Accelerator SAP BW Accelerator training is included in the SAP courses: BW-310, BW-360, and in the online course BW361 In the past, SAP has also offered a two-day workshop called “BI Upgrade & Accelerator” (WNASAP BW Accelerator)

29. Testing You can see what InfoCubes are indexed by going to RSDDV and clicking the button “BIA Indexes.” Make sure you identify real benefits and benchmark with pre- and post-performance after SAP BW Accelerator is implemented This requires that all critical performance data is captured before SAP BW Accelerator is installed You should document the post performance and build times as early as possible so that you know what the performance of complex dashboards will be and how the indexing will fit into existing process chains

30. The New SAP BW 7.2 Features — Modeling in BWA In BWA 7.2 you can complete many of the joins and aggregation that was previously conducted in the BI analytic engine, and was therefore hard to performance tune It also reduces the data being transferred to the app server and therefore is much faster as well Image source: SAP AG, 2011, SDN

31. The New SAP BW 7.2 Features — Faster Performance Image source: SAP AG, 2011, SDN The major benefit of BWA 7.2 is that you can move more of the calculation over to the accelerator and materialize the result in-memory. This improves the reads, and calculations in the OLAP/ BI analytic engine on the BW side are substantially reduced. The following calculations are now included: SUM MIN MAX CNT (count all detailed values) CN0 (Count all values not zero or null) AVG (average) AV0 (Average for all values not zero or null)

32. Prerequisites for SAP BW Accelerator 7.2 For SAP BusinessObjects Explorer • BWA needs to be upgraded to BWA 7.20 • BW should be upgraded to minimum SAP BW 7.0.1 EHP 1; SPS 5 (Source SAP AG BWA 7.2 Installation guide, and SAP Notes 1392524 and 1148111) SAP Technology RIG Recommendations • If the system is on SAP BW 7.0 or 7.0 EHP1, then use SAP BW Accelerator 7.0 • If the system is on SAP BW 7.0 EHP1 SPS 5 or higher AND you are using BO Explorer (accelerated version), then use BWA 7.20 • If the system is on SAP BW 7.3, then use BWA 7.20 (Source Marc Bernard, SAP AG, 2011)

33. The SAP BW 7.2 Limitations and Query Settings • There are still some limitations. For example you cannot use the exception aggregation for single key figures in SAP BW Accelerator if it uses: • Virtual key figures • Conversion before aggregation • Formula calculation before aggregation • Non-cumulative key figures • Key figures with elimination of internal business volume You also get more detailed control on each query as to how it reads the BWA. Think of it as similar to Query read modes (more details at SDN: http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70950003-f7ef-2d10-b1bc-ee483800b25c?QuickLink=index&overridelayout=true

34. The New SAP BW 7.2 Performance Example Source: Sapphire, SAP AG, 2011, 90-95% FASTER!! Queries with large OLAP processes, such as average and counts, benefit the most from the new BWA 7.2 system SAP has published the following performance example:

35. What We’ll Cover … • Introduction • SAP BW Accelerator Overview • BO Explorer Overview • SAP HANA Capabilities and Impact to Organizations • SAP BusinessObjects Analysis Capabilities • Other Performance Considerations • A Roadmap for getting to Next Generation BI • Wrap-up

36. Why the Rapid Adaptation of BusinessObjects Explorer? One of the driving forces for BO Explorer has been the rapid implementation of blade solutions running on-top of SAP NetWeaver BW. To date, according to SAP, over 1,000 of these systems has been installed.

37. BO Explorer Overview • You can connect in several ways to data sets • External data may be indexed by the SAP BusinessObjects Data Services and stored on dedicated SAP BW Accelerator blades • SAP BW data may be indexed on SAP NetWeaver BW Accelerator blades for fast in- memory processing (also through SAP HANA) • External data may also be accessed as “non-accelerated” via SQL/MSU universes • Excel spreadsheets may also be accessed Source: SAP, Aug, 2011 BusinessObjects Explorer does not require SAP NetWeaver® BW. You can accelerate all data sources.

38. BO Explorer — Big Picture BWA = SAP BW Accelerator

39. Creating Indexes from External Data • Using the Accelerator Index Designer in SAP BusinessObjects Data Services, you can also index data from external data files and databases • It is important to note that the SAP BusinessObjects indexes are on dedicated blades • For SAP BW Accelerator you should always have one “spare” blade for failover • If a blade fails, the system can reload the indexes from the SAP BW Accelerator file system (i.e., GPFS) • For the BO Explorer blade, you can use only one blade

40. BO Explorer Overview • Accelerated (BWA) or non-accelerated solutions

41. BO Explorer Non-BW Data Using the SAP BusinessObjects Data Services tool, you can access non-SAP data and model it in the BWA Cube Designer tool Version 4.0 Version 3.2 and 4.0

42. BO Explorer Improved Features • Many customers looked at earlier releases of BO Explorer and noted several limitations • Many of these limitations are now remedied. These include: • The possibility to import your own Excel files • Create your own calculations at the user level • You can group related dimensions for simplicity of analysis • Security in improved and row-level data security can be implemented • You can personalize the view by filtering information • Users may navigate hierarchies instead of “flat” data PS! Currently the BO Explorer solution is licensed based on number of blades and the number of named users.

43. Ad Hoc Search The core idea of BO Explorer is that users can search the BI data like they would using Google, Yahoo, Bing, or other search engines Users should not have to know how the data is structured, what query to execute, or how to display the data BO Explorer takes a “first stab” at presenting the data in a usable format From there, users may navigate and change the display The users may save their settings and the display at any time

44. Ad Hoc Search Example 1. First we searched on“sales” and found threepossible choices 2. We selected “sales Information” and searched after the term "Texas” This shows the sales amount by state as a table, the gross margin as a graph and as a table Any display can be exported to Excel, saved as an image, emailed, or bookmarked

45. A Step-by-Step Demo: Searching Step 1. We load the BO Explorer Web interface into a browser Step 2. We Search for “sales” Step 3. We get the searchresults and a list of “information spaces” The search result list is sorted based on an internal “relevancy” score.

46. A Step-by-Step Demo: The Initial View Step 4. The system looks at the data and formats it based on implied hierarchies (i.e., time, geography, customer) as well as measures.Users may navigate and change measures, graphs, and tables.

47. A Step-by-Step Demo: Searching in a Result Set & Measures Step 5. Again, we are interested in sales around Texas, and can search the initial result set Step 6. By changing the Measure from “margin” to“sales revenue” all graphsand tables change

48. A Step-by-Step Demo: Changing Charts and Drill Down Step 7. We can change the chart by selecting from the left menu options Step 8. While the firstdisplay was based on the“best guess,” we can nowdrill down to the different product lines The best graphingoptions, based on ourdata, is highlighted by a star:

49. A Step-by-Step Demo: More Graphing Options Step 9. There are many graphingoptions and some are more useful than others “Proportional” view is best when you are looking for size relations. i.e., what are the largest contributors to sales? Most of the time, the reccomended graphs works best for the data set, but not always Try several graphing options before deciding on “your” view.

50. A Step-by-Step Demo: Explore More — Filtering Step 10. By clicking on “Explore more” you are promted to filter on the characteristic you selected Since we clicked “Explore more” in the state box, we can now select only the states we are interested in Filtering data makes the images more meaningful. It is harder to analyze 50 states and scroll through the data.