BW HANA - BI Data Extraction Benefits
I recently done a review of several client sites that have done a technical upgrade to a HANA DB. This is a follow on from the benefits of HANA report performance article and will cover the data extraction performance improvements of a HANA DB upgrade.
This business intelligence survey covered 20 SAP client’s sites which 45% have already done a DB upgrade to HANA and 80% of these will complete the upgrade by 2020.
Infopackage & ODP Extraction
The first observation is that by only upgrading BW on to a HANA DB will not improve the performance of the infopackage extraction from a source system. For example, the GL Line item (0FI_GL_14) data extraction from ECC pre-HANA DB upgrade extracted 2.1 million records in 56 mins to PSA and extracted 2 million records in 62 mins to PSA post-HANA DB upgrade.
Operational Data Provisioning (ODP) provides a new technical infrastructure for data extraction and replication. I won’t go into detail about the benefits of ODP in this article but the ODP allows to skip the PSA layer and load directly with DTP from the source system into a target object in SAP BW or SAP BW/4HANA. SAP lab results have shown a reduction in runtime by more than 40% and throughput of > 35 million records per hour is achieved without tuning (3 times faster than parallel processing). Please note if the extractor logic is the ‘bottle neck’ the throughput won’t change.
HANA-Optimized InfoCubes
It is recommended during the HANA DB upgrading process to convert all your Standard InfoCubes to HANA-Optimized InfoCubes.
After system migration to HANA DB all InfoCubes remain unchanged. You need to execute a conversion to HANA-optimized infocubes. This conversion is done via transaction RSMIGRHANADB (program RSDRI_CONVERT_CUBE_TO_INMEMORY). After conversion InfoCubes work without disruption regarding data staging and querying (DB Changes are transparent to the application).
The conversion performance benchmark noted on a HANA-optimized infocube by SAP labs was 250 Million records in 4 mins. The exact runtime depends on the distribution of the data, number of fields, especially characteristics, and the hardware of the HANA appliance.
From my experience, I’ve seen a 33% improvement of data extractions into a SAP HANA-Optimized InfoCubes compared to a standard infocube. For example, one million extraction for a FI GL standard infocube was 14 mins 15 seconds and after optimization is now 9 mins 41 seconds.
Process Chains
The following items are no longer required on process chains
Index drop/build
Prior to upgrading to a HANA DB, the system generated indexes that are created in the fact table for each dimension allow you to easily find and select the data. These indexes displayed are the secondary indexes of the F and E fact tables for the InfoCube. The infocube index creation and deletion are not needed when using HANA-Optimized InfoCubes, this optimised data retrieval is handled for us by the HANA DB. Depending on how frequently you executed the creation and deletion on indexes, the actual saving is maximum a few minutes for one index depending on the size of the index. From experience, I’ve seen 1000 deletion/creation executions on a BW system daily with a total execution time of 3hrs 17 mins.
DB statistics
Prior to upgrading to a HANA DB, the database statistics are used by the system to optimize the query performance. The DB statistics are not needed when using SAP HANA-Optimized InfoCubes. Depending on how frequently you executed the DB statistics step, the actual saving is maximum a few minutes per infocube. From experience, I’ve seen 30 DB Statistics executions on a BW system daily with a total execution time of 2hrs 14 mins
Aggregate roll-up
BW aggregates are no longer required on a HANA DB and the roll up step on the process chain can be removed. Depending on how many aggregates are on your system, the actual saving is from 30 seconds to a few minutes per aggregate depending on the size of the aggregate. From experience, I’ve seen 192 roll ups on a BW system daily with a total execution time of 1hr 16 mins
DSO Activation
The DSO activation step prior to a HANA DB upgrade was done between the database server and the applications servers. Now the DSO activation step is processed on the SAP HANA database server directly. From experience, I’ve seen 1800 plus DSO activations on a BW system daily with a total execution time of 34 hours prior to HANA DB upgrade. Post HANA upgrade this was been reduced to 12 hours which is a saving of 65%.
Conclusion
All the changes that a BW HANA upgrade brings are of great benefit. Overall, you will be able to save time on not having to execute activities that have been made redundant – Index creation/deletion; DB statistic updates and aggregate roll ups. The main extraction benefits are on the loads to the HANA-Optimized InfoCubes (33% improvement) and DSO activation (65% improvement). Along with these performance benefits, if the ODP Extraction is also implement, the extraction time from source to reporting infocube could be reduced by at least 60%.
With all the benefits of upgrading to a HANA DB along with the new functionality available means that data can be delivered to the business quicker and potentially more frequently.
Business Intelligence Solutions Support Survey
If you’re open to contributing, we’d love to hear your views in a survey that we’re conducting.
The survey has 17 questions but we’ve carefully designed and tested it to ensure it can be completed quickly, it should take no longer than 10 minutes.
In return for doing so, we’ll send you a report on the study’s full findings and we think you’ll find the results to be of value to your business.
Survey
