Jan 12, 2015

Optimizing SAP BW on SAP HANA Part 1

Optimizing SAP BW on SAP HANA Part 1


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:
Capture1.JPG
To learn more about migration, check the below document:

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
Checking.JPG
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
Checking1.JPG
Once the checklist is completed you get the below screen:
Checking2.JPG
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"
Checking3.JPG
Check the below blog to learn more about it:

Best Practice # 4: Size Your BW System  Using a Fully Automated Sizing Tool

Go to Sizing tab
Sizing.JPG
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
Capture2.JPG
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:

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

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
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
HouseKeeping.JPG
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:
Capture4.JPG
To learn more about Housekeeping activities, check the below document:
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:
Also check the below document:
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
Idoc.JPG
OLAP Statistic Deletion
BWStatic.JPG
For cleaning Large tables, check SAP Note 706478 - Preventing Basis tables from in creasing considerably

Jan 7, 2015

How to optimize Reporting Performance in SAP BI


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.