Many experienced developers are not aware that moving BW to HANA can in some cases result in slower transformations during data loads. Thankfully, SAP is providing a tool to identify areas in your ABAP code that may benefit from an optimization. In this blog we take a quick look at this valuable tool.
By Dr. Berg
Background
During development of SAP BW Extract Transforms and data Load (ETL) there are many options to create transformations in DTPs in BW 7.x and in older Update and Transfer rules in BW 3.x. Unfortunately, depending on your developers, some of the custom transforms may have sub-optimal ABAP coding can have impacts on how SAP BW ETL performs after the migration to HANA.
Thankfully, SAP’s talented developer Marc Bernard provides an automated tool to help you identify potential coding issues. This program is called the “SAP NetWeaver BW ABAP Routine Analyzer”, or technically ZBW_ABAP_ANALYZER.
Figure 1: Input Screen for SAP NetWeaver BW ABAP Routine Analyzer
Overall, the tool looks for and suggests the following fixes:
a. Select for all entries (SFAE) statements without HANA DB hints --> add hints
b. Select * --> specify fields to select
c. Database access in the field routines --> move to start routine
d. Loops which do not assign field symbols --> use field symbols
e. Selects from master data tables --> Use the read master data rule
f. Selects from DSOs --> Use the read DSO rule
g. Direct updates to BW object tables --> Do not update tables directly
h. and many more areas...
You can run this program either as on-line, or as a background job. If you run the program on-line (only recommended for a small selection), you can click a link in the output to edit the transformation, transfer rule or update rule.
Figure 2: On-Line Output for SAP NetWeaver BW ABAP Routine Analyzer
If you run in the background, you can save a log to SLG1 and also create a spool list for further analysis.
Figure 3: SAP NetWeaver BW ABAP Routine Analyzer - On-Line Spool list
For organizations that have slow data loads where most of the time is used on transforms and loads, this is a key program that should be run as part of the BW HANA migration planning. The tool should be available for general access in April this year.
Some Observations
The last six weeks in this HANA blog series we have looked at many of the HANA migration tools that Marc and SAP has provided, and I continue to be amazed at both the quality and the speed at which SAP is rolling out tools to make any HANA migration as easy as possible.
In the next blog we will look an in-depth look at some of the ways you can balance the load on the nodes in a HANA system when working with a scale out environment.
SAP has released an ABAP based tool that generates a report significantly better for sizing than using just the SAP QuickSizer. This program takes into consideration existing database compression, different table types, and also includes the effects of non-active data in the SAP HANA system. In this blog we take a quick look at this valuable tool.
- By Dr. Berg
Sizing Tool Background and Settings
This program is attached to SAP Note 1736976 and can be downloaded from SAP Marketplace and run on a production system to get very accurate sizing information.
This includes sizing for RAM and dynamic runtime memory, log space and disk space. It even provides details on items such as data sizes with corresponding dynamic runtime memory for row store and column stores, as well as the calculated size and the estimated size in HANA memory for each table in your BW system. Technically, the program uses sampling of the database for sizing, so you should refresh the database statistics before running the program.
Figure 1: HANA Sizing Program for SAP NetWeaver BW
The higher precision you run the estimate at (selected by radio buttons in Figure 1), the longer the program is going to run. With 14 parallel processors and 8Tb data warehouse, it is not unusual to see 45-75 minutes run time. To increase speed, you can also suppress analysis tables with less than 1 MB size.
In addition, since timeouts are common when running this sizing program, you should temporarily change the parameter in rdisp/max_wprun_time to 0. You can do this in BW Transaction RZ11. Finally, you estimate the growth for the system over a time period. This can be done as a percentage, or as absolute growth in GB.
After all this is done, you have downloaded and installed the program, and selected the parameters above, you can go to Transaction SE38 and run SDF/HANA_BW_SIZING as a background job.
Figure 2: SAP HANA Sizing Results
The output is stored in the file you specified and the file can now be emailed to to hardware vendors for sizing input and hardware selection.
Need more Information?
If you have any questions, just stop by my sessions at SAP BI 2013 Conference in Las Vaegs next week (March 19-22); Amsterdam (June 11-13) or inSingapore in September where I will cover more on these timely topics and also do a HANA book signining event with SAP Press...
There are many tasks and technical requirements to migrate a BW system to HANA. This includes BW technical settings and software levels required to start the process. Thankfully, SAP has provided a checklist tool for SAP NetWeaver BW powered by HANA. In this Blog we take a quick look at the key features. By Dr. Berg
Automated HANA Readiness Tool
In version 2.x of this tool available since December 2012, hundreds of checks are done automatically in the BW system. This includes platform checks on database and application and system information.
There are even basis checks for support packs, ABAP/JAVA stacks, Unicode, BW releases, and add-ons to your system. The program is found in SAP Note 1729988, and the tool provided automatic check programs for both the 3.5 version and the 7.x version of BW
FIGURE 1: INPUT FOR SELECTING READINESS CHECKS FOR BW TO HANA MIGRATION
First you enter the checks you want to perform to see if your system is ready for a HANA Migration. This should include most of the options seen in the figure above, such as basis and database, but you also can choose to ignore customer objects or checks you are planning to run at a later time.
The idea of the checklist tool from SAP is that you run it several times throughout the project. Once before you start, then periodically as you resolve issues and upgrade requirements, and then finally when the system has been migrated to HANA. This last step is important, since the checklist tool also has specific checks for the HANA system that can help you identify any issues before turning over the system to end users. If the tool shows a ‘red’ flag, you should resolve this before starting your HANA migration, or if a new red flag shows up, fix it before completing the migration.
FIGURE 2: BW READNIESS RESULTS FOR HANA MIGRATION
The output results in a list of actions that should be undertaken before the migration starts. In our example, we can see that for our system database patch 110 should be applied as part of our preparation steps.
Getting Started
If you want to try this program in your system, simply install the latest version from SAP Marketplace in note 1729988 and then execute the program using ZBW_HANA_CHECKLIST for those on a BW 7.x system, or ZBW_HANA_CHECKLIST_3X for those on older BW 3.x systems. The tool even allows you to download any recommended SAP notes to fix issues found in the check through the automated transaction call of SNOTE. This program should be executed by anyone who is in the planning stages of a BW to HANA migration.
More Information at SAP BI 2013 in Las Vegas
Next month at BI 2013 in Las Vegas, I will be showing more tools and give a detailed presentation on how to create “Strategies for budgeting, planning, and sizing your SAP NetWeaver BW on SAP HANA project”. The session will be at the MGM on Tuesday, March 19, from 12:00 to 1:15 pm.
During the conference I will also be signing copies of the SAP HANA Book I wrote for SAP press with Penny Silvia and you can stop by to see me at the ‘meet-the-expert’ session and get your HANA questions answered…
On Jan 10th, SAP Announced that SAP ERP can run on the in-Memory Platform called HANA. What does that mean to BW? Is data warehousing dead? Can we simply Report on ERP instead and skip data movement, DSOs, infoCubes, modeling and the extra hardware? In this blog we look at where we are now and what it may mean for the future. By Dr. Berg
ERP on SAP HANA
As of January 10, 2013, SAP said it supported the deployment of their ERP Business Suite on HANA. The solution requires enhancement pack 6 for SAP ERP version 6.0 and ABAP AS version 7.4 which was made available at the same time.
This raise many questions about the future of traditional On-Line Transaction processing (OLTP) and separate On-Line Analytical Processing (OLAP) such as data warehouses. Is there really a need to separate the reporting from the transaction processing?
The answer depends on the time horizon you are planning on. In the short run, there are simply too many valuable models in SAP Business Warehouse (BW), Advanced Planning and Optimization (APO), Business Planning and Consolidation (BPC) , Integrated Planning (IP) and all the other DW based tools to throw them all away. However, these tools also has several Achilles heels; they are not true real time and require separate infrastructure and data movements.
But, SAP Makes a Bold Statement...
Two days ago, SAP's Vishal Sikka wrote in his blog: “with the collapse of OLTP and OLAP in one platform there is a massive simplification on the way” . There is naturally a lot riding on this vision and a substantial amount of development must take place to organize the data in the OLTP system in a way that is designed for reporting.
I can see that some operational version of this can be done in the future through separate HANA reporting tables in the ERP system (much like what we used to have with EIS, SIS, LIS in the early days of ERP), or by creating views on the data using HANA studio, XS or other tools.
Dr. Berg Speculates
However, for the foreseeable future, I believe SAP BW provides an integrated platform for model driven analysis on data that needs to be preserved at the time of transaction, integrated with external data, and which contains the corporate ‘memory’ of what happened in the past. So, the vision of an integrated BI and ERP system on the same platform is just that; a vision. Technically, it is not possible to install BW and ERP on the same HANA system today.
On the other hand, for real-time operational reporting the ability to move your SAP ERP system to a HANA platform allows you to execute transactions faster, reduces the database size, simplifies the administration (really!) and provides new analytical capabilities inside the ERP system that simply was not possible before.
Some Early Examples of ERP on HANA Analytics
An example of these is the new abilities is found in the SAP Business One Application, version for the HANA Platform. In this ERP solution, SAP included embedded contextual dashboards in the transactional screens.
This include graphs for items such as most purchased products by customers, credit dashboards, real-time inventory trends, vendor analysis, cash flow forecasting, and much more. SAP has also announced that they plan to develop even more of these types of applications in future release (so this may be the future of operational real-time analytics).
Conclusions
There is a lot of people wanting to skip the model driven approach and go straigh to ERP for analytics and BI. For those it is important to be reminded that there is a reason why the models were build in the first place. It was to simplify reporting and provide a business view to cleansed, integrated and often transformed transactional data to provide meaning. This is not found in a transactional table. So we may get there as Vishal stated in his blog, but there still along way to go..
SAP BW is still an integral part of the data warehouse landscape for many years to come, and with HANA there is even more benefits to keep it around...
An interview of the co-author of the new #1 best-selling HANA book from SAP Press on how HANA works, what it can do and how to implement with practical advice (9 minutes).
