Optimizing SAP BW on SAP HANA Part 1

Optimizing SAP BW on SAP HANA Part 1 Via Content in SCN Hi Everyone, During the TechEd I attended lecture RDP303 - "Master Class for Optimizing SAP Netweaver Business Warehouse on SAP HANA" It was only a 1 hour session but there were many things to learn. The session was aimed at Optimizing SAP BW on SAP HANA and best practices to be followed I would like to share learning from the session. Also check Part 2: Share the Knowledge - Optimizing SAP BW on SAP HANA Part 2 Best Practice #1: Master The Basics and Understand the Path to BW on HANA The session started with migration options for implementing BW on HANA There are three options available for implementing BW on SAP HANA and they have their own advantages and disadvantages as shown below: To learn more about migration, check the below document: SAP NetWeaver BW on SAP HANA Project Approaches - Webinar Presentation Upgrade and Migration - BW on HANA | SAP HANA Upgrade / Migration - General | SAP HANA Best Practice #2: Know the Tools That Do the Work For You After that we were told about BW Migration Cockpit for SAP HANA tool that can  be used for migration and a demo was shown for it Check SAP Note 1909597 - SAP NetWeaver BW Migration Cockpit for SAP HANA for this and install attached ABAP program - "ZBW_HANA_MIGRATION_COCKPIT" Once you have installed the program and you execute it, you get the below screen Best Practice # 3: Ch eck Your BW System : Before and After Migrating to SAP HANA The first tab is checking It has three main tools "Checklist Tool", "Consistency Checks" and "Planning Function Check" Checklist Tool automates the check of best practice guidelines for operations and pre-requisites for migration of an existing SAP NetWeaver BW deployment to the SAP HANA platform. Once you click on checklist tool, the below screen appears and you can select which all checks you want to perfo rm Once the checklist is completed you get the below screen: To learn more about the checklist tool, check th e SAP Note 1729988 - SAP NetWeaver BW powered by SAP HANA - Checklist Tool Planning Function Check tool determines whether planning functions are executed in ABAP or in SAP HANA It uses program RSPLS_PLANNING_ON_HDB_ANALYSIS and it should be installed in your system otherwise you will see the below screen stating "Program RSPLS_PLANNING_ON_HDB_ANALYSIS does not exist" Check the below blog to learn more about it: How to Check whether a Planning Function or a Disaggregation in PAK is executed in Memory Best Practice # 4: Size Your BW System  Using a Fully Automated Sizing Tool Go to Sizing tab In this tab, we can determine memory requirements for SAP HANA using the Sizing tool, can configure NLS, set up Data Archive Process and execute Data Archiving process To learn more about sizing, check SAP Note 1736976 - Sizing Report for BW on HANA Also check the below blog on Sizing BW on HANA: How NOT to size a SAP NetWeaver BW system for SAP HANA How To Size SAP BW on HANA | SAP HANA Sizing | SAP HANA Best Practice # 5: Lower TCO by Leveraging  Non-active Data Concept and Off-loading Data to  Near-line Storage (or Archive) As we all know that non-active data concept was introduced for BW on HANA last year http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/300347b5-9bcf-2d10-efa9-8cc8d89ee72c?overridelayout=t… Non active tables are loaded into RAM only when they are accessed and are displaced from the RAM with highest priority in case of RAM shortage In BW on HANA, PSA tables and Write Optimized DSO tables are automatically marked as "Non-Active" To learn more, check the SAP Note 1767880 - Non-active data concept for BW on SAP HANA DB After that we were told a bit about Near Line Storage with Sybase IQ To learn more about it, check the below documents Data Management with SAP NetWeaver BW powered by SAP HANA in the SAP Real-Time Data Platform  - Webinar Presentation SAP BW Near-Line Storage (NLS) Implementation f... | SCN SAP First Guidance - SAP-NLS Solution with Syba... | SCN Also check SAP Note 1796393 - SAP BW near-line solution with Sybase IQ Best Practice # 6: Keep Your System Lean and Fast by Performing Housekeeping Task Regularly Go to Housekeeping tab As we all know Housekeeping activities are an integral part of BW as they ensure optimum utilization of system resources and increase system performance Some of t he most common activities are: To learn more about Housekeeping activities, check the below document: House Keeping / Performance Tuning Activities in SAP BW Systems -- P ART 1 We can set automated Housekeeping using Task Manager and use "Housekeeping Tasks" Check SAP Note 1829728 - BW Housekeeping Task List to generate a a task list with several common tasks associated with housekeeping/upkeeping an BW system We can also extend this task list Check the below document on how to install Housekeeping task list: Create #SAP #BW Housekeeping task list Also check the below document: Important Housekeeping Principles for keeping your SAP NetWeaver BW in good shape – Webinar Presentation For Task Manager Configuration, check SAP Note 1589145 - Task Manager for Technical Configuration We can also perform few housekeeping tasks manually present in the cockpit as shown above, such as IDoc Archiving OLAP Statistic Deletion For cleaning Large tables, check SAP Note 706478 - Preventing Basis tables from in creasing considerably

How to optimize Reporting Performance in SAP BI

How to optimize Reporting Performance

Via Content in SCN

Introduction

We face many challenges in our BI projects in terms of Reporting Performance. Users always expect outstanding performance of our BI reports. No matter what ever we do in the backend (Modeling and Query Designer). I am going to share very important tips & techniques which I have learned through out my experience which consists of some standard thumb rules too.

It is recommended to use Inclusions Instead of Exclusions wherever possible as Exclusions cant access DB indices and will impact performance. Because, when huge no. of RKFs & CKFs are Included in a Query, Restricting, Conditioning and Computations are done for each of them during query execution. This is very time consuming and a high number of RKFs & CKFs can seriously hurt Query Performance that means, please open your query definition and pick up the fields which are used in the query while defining Aggregates. Delete the unused Aggregates 

If the aggregates are too Large, those not only degrades the Query performance but also loading performance by longer times to roll up and Attribute Change runs also takes longer times. 

Make sure you get good valuation indicators for your Aggregates, but not like below 

It is recommended to to Suppress Result rows Wherever possible. 

It is recommended to use SAP Exits whe re ever possible and bring down the Customer Exits

SAP Suggests free characteristics in reports should be limited to 8-10. 

In RSRV checks, free characteristics usage is marked in red which has very high impact on reports

It is recommended to reduce RKFs & CKFs in the Query to as few as possible.

It is recommended to redefine your Aggregates in an optimized way by taking Statistics(BI Admin Cockpit should be in place) & Query definitions into Consideration.



Archiving (NLS) is recommended to archive unused data.

a. Reduction of online disk storage

b. Improvement in BW query performance

c. Increased data availability as rollup, change runs and backup times will be shorter

d. Reduced hardware consumption during loading and querying

Reports should be designed on Multiproviders wherever possible.

Logical and physical partitioning is recommended for bet ter performance.

Extensive use of Filters at Query level is recommended.

Select appropriate Read mode settings for Multiproviders with "H" and Infosets with "X" in RSDIPROP t-code

Reporting on Infosets should be considered below tips :

a. Do not select all the fields which are part of Infoset definition like below. You can select the fields which we want to use in query only.



b. You should select "Use Selection of Structure Elements" in RSRT-->Properties



c. Do not make too many joins as it cause high runtimes to fetch data after the joins

Statistical Reports performance can be improved by broadcasting query result to cache and prefilling cache.

Deletion of unused Queries is recommended

Delete temporary Query Views is recommended.

It is recommended to be careful while creating Cell Structures as they require high query run times and will lead to performance degradation.

Program RSR_CACHE_RSRV_CHECK_ENTRIES can be scheduled to run on regular basis to remove the unused Cache entries .< /p>

Make proper Query read mode and Cache mode settings in RSRT-->Properties. The recommended Cache mode could be 1 or 5.

Conclusion :
It is always better to keep an eye on above points while developing our Business models and Queries. This blog will help us to satisfy our users with good reporting performance.