ABAP on HANA - enhance performance through code push down

ABAP on HANA - enhance performance through code push down

Via Content in SCN

With SAP Netweaver AS ABAP 7.4, I thought of a simple ABAP program to compare the performance between ABAP program consisting of classic SQL(multiple joins & currency conversion)  against the ABAP program calling HANA calculation view directly (classic SQL with joins & currency conversion logic converted into HANA calculation view).

• ABAP with SQL embedded  – SQL query with multiple joins & currency conversion in ABAP program. vs

• ABAP program calling HANA calculation view directly  a.k.a code push down to HANA DB.

You can manage ABAP program, modeling & ABAP profiling all in one place with SAP HANA studio. You need to install additional ABAP development plugins for eclipse and I used SAP HANA Studio version:  1.0.58.

Below is the screen I captured while profiling ABAP program from HANA studio. ABAP program uses classic SQL to fetch data from BKPF, BSEG (multiple joins) and perform currency conversion using standard function module. Actual database time that it took to execute ~ 24 seconds.

From the image below

Screen 1:
SQL statement navigated from hit list (Screen 3) shows more time consumed at DB during execution. SQL query from screen 1 is later converted into calculation view in HANA DB to improve the performance drastically.

Screen 2:
Snapshot from ABAP profilin g analysis done in HANA studio with database time: ~ 24seconds

Screen 3:
Complete Hit list of ABAP program showing BKPF SQL fetch and currency conversion (Perform CONV_CC) which consumed most of the time. Both the SQL statement and currency conversion is pushed down to calculation view for performance boost.

Screen 4:

Currency conversion code inside - Perform CONV_CC.

ABAP code optimization using HANA 24 seconds to 2 seconds:

SQL query & currency conversion is converted into HANA calculation view and called from SQL query as shown below: ZBC_BSEG_DOC is the dictionary view created from HANA calculation view.

Dictionary view: ZBC_BSEG_DOC

HANA Calculation view:

HANA views can be accessed by standard SQL With ABAP 7.40 they are natively supported in ABAP

• Mapping to DDIC types possible

ABAP profiling & performance after code push down to HANA

Screen 5: ABAP code optimized for HANA. All SQL joins & currency conversi on pushed down to HANA DB as calculation view CA_BSEG_DOC.

Screen 6:
Proxy ABAP dictionary view generated from HANA calculation view so it can be used in ABAP SQL.

Screen 7:
ABAP profiling snapshot showing approx. 2 seconds spent in database during execution compared to 24 seconds with classic SQL.

Screen 8:
HANA calculation view inside HANA DB.

Screen 9:
Hit list fr om ABAP profiling.

How to group free characteristics in Portal or Web Analyzer

How to group free characteristics in Portal or Web Analyzer

Via Content in SCN

Introduction

                      We use several free characteristics in our reports for drill down purpose. At times, we are forced to use huge no. of drill down chars. It will be cumbersome to view all of them together in a single screen. There are chances for our users to get confused by looking all of them like below.

    
                  To deal this issue in a better way, I am going to demonstrate on How to group these free chars as per the entity type

in WAD by using Navigational Block Web Item.

1.     Open WAD and add different "Navigational Block" items to your web template
         
2.    If you want to group as per the Header Customer entity type, then click on the Navigational Block Web item from above and specify the Entity name in the Title like below

  

3.     You can select the required Chars and Nav Attrs in the  in Web Item properties like below.

4.     You can create separate groups with different web items in the above fashion and your users can select the required drill down char f rom the Nav Block web item in the Portal like below

  

Note : You can correlate with Dimensions in your cube. But with above steps, you can also add Nav Attrs to the group along with dimension chars. You can create total new groups also without concerning the dimension design in the Cube.

0 records problem in Master Data load

Zero records problem in Master Data load

Via Content in SCN

On a rainy noon of Friday faced strange type of problem while loading master data text in SAP APO server (EHP2 for SAP SCM 7.0).

Problem: There is one generic transaction data source for Master data which was coming from BW side. We have to load its data in different-different info objects like plant, division, sales organisation etc. by putting selection of particular info object in Info package. e.g. - Division
After schedule the Info package we were able to see the 76 records of division at PSA level. Then putting the same selection at DTP level

                     

After executing the DTP we was getting 0 records at manage level instead of 76 records for Division at PSA level.

Solution:

1. Check the data at PSA level,how it looks

                         

2. At DTP level filter is same as data in PSA not as selection of info package(all upper case letter)

                 

3. After executing the DTP we were able to got 76 records of division at info object manage level.

The solution is very simple but some times simple things do wonderful things.

Note: While doing master data load check Handle Duplicate records in update tab of DTP and activate master data after loading.

Thanks for reading..

SAP BW/BO data flow and separation between front end and back end with the help of a iceberg

SAP BW/BO data flow and separation between front end and back end with the help of a iceberg

Via Content in SCN

When I was a absolute beginner in the SAP world a while ago, the relations between the Data from the operative source systems, the BW system and the various BI visualization tools was not so simple explainable for me.

The dataflow from the source systems over the SAP BW and the Query Design to the Data visualization was for me a long time a black box.

To make the start for beginners into the world of SAP BI easier and to show the relations other colleagues from SAP ERP modules (e.g. FI, CO, SD, HR, etc.) and decision makers as primary users of BI in a transparent and easy way, some time ago I have designed the following figure:

 

The aim of this figure is to simplified illustration of the data flow starting with the different possible source systems (e.g. ERP, CRM, 3rd party, XLS, etc.) over the data modelling and data management with respect to the LSA principles of Layered, Scalable Architecture (by Juergen Haupt as godfather) in the Data Warehouse (SAP NetWeaver Business Warehouse) and the Query creation in the BEx Query Designer to the data visualization with the tools of the SAP Business Explorer (SAP BEx) and BusinessObjects BI for SAP.

But above all, the picture is supposed to show the separation between BI front end and back end. The decision makers as the consumers of the reports always see only the tip of the iceberg where they use the frontend tools of BEx (Analyzer, Web Application Designer) and BO (SAP BusinessObjects Design Studio, SAP Lumira (formerly SAP Visual Intelligence), SAP BusinessObjects Analysis, edition for Microsoft Office, SAP Crystal Reports, SAP BusinessObjects Dashboards etc.).

The back end area begins (water surface) with the creation of BEx Queries. The Queries are based on the MultiProviders of the SAP BW system which bring data from different InfoCubes together. The data from the different source systems passing through the ETL-process (extraction, transformation, loading) and provided in a consolidated and adjusted form in the InfoCubes.

Ultimately, with the help of this figure should be underlined the „Extract once - deploy many" principle of the Corporate Information Factory (CIF) Resources by Bill Inmon, Inmon Data Systems.

Note: As mentioned in the beginning, these form of the iceberg figure should make an easy overview of the SAP BW/BO dataflow and the separation between front end and back end. Further possibilities, such as BW on HANA or operative Reporting in the ERP system are not taken in this figure.

Success Story: How SAP consulting helped a retail giant realize their HANA journey

Success Story: How SAP consulting helped a retail giant realize their HANA journey

Via Content in SCN

While 2012 was all about closing BW on HANA deals, 2013 has been all about BW on HANA implementations. This article describes the journey of my recent BW on HANA success story at a retail customer in Nordics.

Customer Use case:
The customer has implemented SAP ERP and SAP BW systems. Recently, they have incorporated point-of-sale data analytics in the BW system. In brief, the use case was as follows:
8PM: Retail stores across the country closes.
9PM: Point-of-Sale (POS) data is ready to be loaded in the BW systems.
9PM-3AM: Data should be loaded in the BW system and KPI calculations need to be performed in the BW system.
3AM: BW system must be ready by this time to load the data back in the ERP system. This aggregated data with the KPI calculations is then used in the ERP system to execute more application logic.
7AM: All the processing must be complete by this time in the ERP system so that store managers have relevant information available before sto res open.

Challenges
The challenge with the above timeline is that there is only a six hour window to:

1. Get the data transferred from the SAP ERP system to the BW system.
2. The process chains in the BW system must be run where the data is loaded from the PSA to DSO and Infocubes and propagated through various complex application logic layers.
3. Various KPI calculations are performed in the BW layer issuing many look-ups
Once the calculations are performed, the data must be fed back to the source system.

As the entire chain depends to getting the KPIs from the BW system, if the load fails or the processing takes too much time, the store managers won't have the needed information when stores open the following day.

Set up and Performance Results:
We installed BW on HANA and connected it to the source ERP system. There was an existing BW on a traditional DB (BW on ODB) system already existing. This arrangement made it possible to load the data in parallel in the two BW systems and compare the performance numbers.

DSO Activation Times:
We loaded same number of records on both the systems and ran the process chains and recorded the below numbers:

BEX Query Response times:
During the realization, our BW on HANA expert found possibilities to optimize customer's current ABAP code and BW modeling. Just with these optimizations, customer may roll out their existing implementation to a nother smaller market without any issues in near future, though they would need HANA as soon as they want to include bigger markets.

Conclusion:
As proven during the performance tests, there were huge gains in DSO activation times. Usually, in a multi-layer architecture, DSOs feed other DSOs, which was also the case here. The activation gains for each DSO got multiplied and this resulted in great performance improvements.
We also demonstrated loading more than 600 million records in the BW on HANA system without any issues with only 3 parallel jobs. By this we showed that, the system can handle heavy loads without any issues.

Next step at the customer would be to bring in data in the the BW on HANA system from more countries and be able to run the process chains in the BW system with hundreds of millions of rows empowering the store manager more and more.