In my last blog entry about ABAP for HANA I discussed the overall strategy of ABAP for HANA, now I want to discuss properties of analytical ABAP for HANA applications:
- What are their properties?
- What are their unique selling points?
- What techniques and frameworks can we use to develop those applications?
Moreover, I want to describe some pesky little details in ABAP for HANA development in this short blog – this part is addressed to HANA and ABAP for HANA newbies. Compared to Open SQL which is known to every ABAP developer HANA offers many new possibilities. In this blog entry I discuss how to use HANA features to calculate account balances with a single SQL statement and how to use them in ABAP for HANA. You could use this technique for development of programmatic accelerators that increase speed of existing SAP transactions as well as for new ABAP applications that are optimized for HANA. But let me first introduce the use case – an application written in ABAP for HANA.
Account Balance Display
In the “Trailblazer” Customer Engagement Initiative I programmed a dialog application for transaction FPL9 that gives account information about a certain business partner, calculates account balances on the fly and gives out the information in a dashboard.
The picture below shows a screenshot of the SAP UI5 application in SAP gold reflection motif. You can see the header data for a business partner, the balance for the current moment and during the last time and the account positions (3) which come from transparent table DFKKOP which will we discuss later on.
The dashboard on the right side (1) of the above picture is a visualization of account balances (2) of the last months which are calculated with techniques explained below. The graphic shows that this specific business partner has been in the red for a couple of months (otherwise a blue bar would have been shown on the right). The dashboard is more informative than the list of balances below because it shows that within the last months the negative balance was constant, so the customer had trouble to pay his dues one time but managed it to settle monthly payments. In the last three months the customer managed to discard some of his debts but in the last month he wasn’t successful and so he returned to the “normal” level.
A dashboard will give the user an immediate overview of payment practices of a business partner in real time even on a huge set of transactional data. Another important aspect for speeding up applications is browsing in huge data sets using paging mechanism which I developed using a SAP UI5 table control consuming OData feeds but the details are topic for another blog.
Properties of Analytical ABAP for HANA Applications
If you compare the application mentioned above with SAP transaction FPL9 from FI-CA there are some obvious differences: We have a cool new UI and a dashboard – but we could probably enhance the SAPGui transaction and add a dashboard there, too. So the major difference is the shift in programming style which is necessary when dealing with huge data sets. Transaction FPL9 selects all the necessary data into internal tables in ABAP application server, performs calculations and then displays everything which can take a long time for huge data sets.
A HANA optimized applications uses different techniques to gain amazing speed:
- Only data are loaded into the main memory which is necessary for the user.
- If the user scrolls we use paging techniques to refresh the UI.
- Calculations are pushed down to the database by means of HANA views and database procedures.
The second step is necessary if and only if Open SQL doesn’t give you the expected result. So we could perform several calculations of balances for some consecutive months with some Open SQL calls which are called sequentially and work with that data. But if we want to gain speed we could try to use only single call so that HANA can use inherent parallel processing. Now I will discuss how to achieve this with a single stored procedure call.
Use Case Calculation of Account Balances
In this blog entry I discuss the following use case: We have a database table of account positions for business partners (“GPART”) and each account position has monetary amount (“BETRH”) and due date (“FAEDN”). Please excuse the awkward names but if you are familiar with SAP applications you will recognize that I’m talking about transparent table DFKKOP which is one of most important tables of this solution and its add-ons which are industry solutions which deal usually with high data volume and this is why HANA enters the stage. In my example I’ll use the following transparent table DFKKOP with these entries:
MANDT | GPART (aka business partner) | BETRH (aka monetary amount) | FAEDN (due date) |
030 | 4711 | 100 | 10/15/2012 |
030 | 4711 | -200 | 10/1/2012 |
030 | 4711 | 1000 | 9/20/2012 |
030 | 4711 | 1200 | 9/08/2012 |
030 | 4711 | -800 | 9/02/2012 |
030 | 4711 | -1000 | 8/16/2012 |
030 | 4711 | 400 | 8/2/2012 |
This is of course a simplified example – transparent table DFKKOP is quite wide so that there are more columns which make the WHERE condition somewhat more complex but this and the resulting consequences “will be left to the reader as easy exercise”.
Now you know the data model I’ll tell you the use case: I want to sum up the monetary amounts (“BETRH”) of a business partner (“GPART”) but not only once for all entries but for a set of different date – so all entries with FAEDN <= “11/1/2012”, then FAEDN <= “10/1/2012” and FAEDN <= “09/1/2012” with a single SQL query. This is a typical example that a developer of an HANA accelerator or AS ABAP application has to solve.
Non-Equi-Joins as a Solution
In the following I’ll present a solution using non-equi-joins. The non-equi join is a join that uses comparison operators like >, <, >=, <= along with conditions instead of the equal sign. As a consequence the cardinality usually is bigger compared with an equi-join you know from Open SQL. If you want to know more about the solution I recommend you to read http://www.w3resource.com/sql/joins/perform-a-non-equi-join.php .
We create a temporary table which is input parameter and with the following structure of line items: MANDT, GPART, DATUM. In the example from above it would contain the following entries:
MANDT | GPART (aka business partner) | DATUM (due date) |
030 | 4711 | 11/1/2012 |
030 | 4711 | 10/1/2012 |
030 | 4711 | 9/1/2012 |
This is a temporary table which contains the dates for which the account balances should be calculated and is given as input to the stored procedure. Here we want to sum up the monetary amount for three values. With the following non-equi-join as stored procedure with the database table DFKKOP:
/********* Begin Procedure Script ************/
BEGIN
et_balances = SELECT dat.datum AS "DATE",
sum( so.betrh ) AS ACCOUNTVALUE FROM :it_control as dat
LEFT OUTER JOIN "DFKKOP" as so
ON so.gpart = dat.gpart and so.faedn <= dat.datum
and so.mandt = dat.mandt
GROUP BY dat.gpart, dat.datum
ORDER BY dat.gpart asc, date asc;
END;
/********* End Procedure Script ************/
I get the following result:
DATE | ACCOUNTVALUE |
9/1/2012 | -600 = 400 -1000 |
10/1/2012 | 800 = 400 -1000 -800 + 1200 + 1000 |
11/1/2012 | 700 = 400 -1000 -800 + 1200 + 1000 – 200 + 100 |
The calculation is very easy: The non-equi-join creates a huge cartesian product of the internal table and DFFKOP so for each of this three dates all rows with smaller (or equal) date are joined and then we do a grouping and sum up the monetary amount.
I want to discuss above solution. At first we could simplify it be removing the MANDT field from the interface of the stored procedure and use a variable that gets the client information from the session context which can be done in SQLScript as follows:
SELECT SESSION_CONTEXT('client') INTO lv_client FROM dummy;
Instead of a non-equi-join we could use a FOR-loop in SQLScript. I can’t recommend it because I’m not sure whether it makes the code easier to understand. Another reason is that procedural commands in SQLScript can slow the calculation.
Perhaps I can give you following advice: if you want to perform difficult calculations in HANA you should build up HANA skills and as newbie you have to try different possibilities out.
Calling the non-Equi-Join in ABAP for HANA
If we want to create an analytical application in ABAP we there are two possibilities:
- We can develop it in SAP Business Suite system with secondary persistence in a side-by-side scenario. Here we would have to use native SQL or ADBC (which is recommended by SAP).
- In NW 7.4 and coming releases there are more possibilities and in fact the command CALL DATABASE PROCEDURE is planned to be available.
I won’t discuss all NW 7.4 possibilities, their strengths and weaknesses here. I made the experience that the CALL DATABASE PROCEDURE command is easily done because of the tight integration with internal tables: We can fill input parameters with internal tables and get back the calculated results of the stored procedure which can be used in our ABAP program. This was a simple example of a code pushdown from ABAP down to HANA.
What would we do without CALL DATABASE PROCEDURE?
- We would have to code complex and error prone native SQL in ABAP or ADBC which I don’t recommend: It makes your code complex und unreadable.
- We could use Open SQL. In fact the calculation of account balances can be done within an ABAP loop with different SELECTs. This would be reasonable for a small number SELECTs but if you have thousands of SELECTs the overhead of the database calls can be significant. Moreover we a sequential logic in ABAP in better support of parallelization in HANA.
If you want to develop analytic ABAP application in side-by-side-scenarios and use the power of HANA you have two possibilities:
- You can develop them in SAP Business Suite using secondary database connections and native SQL/ADBC. When HANA becomes primary persistence you can change the code to get rid of secondary database connection and access HANA directly. You don’t need an additional AS ABAP 7.4 on the one hand but on the other hand you cannot benefit from ABAP for HANA and you will create legacy code.
- In NW 7.4 you have better development support and can use advanced techniques and you don’t have a legacy code base when HANA becomes primary persistence. As a drawback you have an additional system and you have to take care of integration (think of dialogue integration in SAP Business Suite).
So if you want to choose the right development platform for analytical ABAP for HANA applications you should ask you the following questions: How many and what applications do want to develop? Now I come to the last topic of my blog: What are the characteristics of an analytical ABAP for HANA applications? What are their strengths and unique selling points?
Towards Operational Reporting
Code pushdown can make applications faster but there is another, very challenging use case: You can help the SAP user (or the SAP system) to make smarter decisions by giving access to highly aggregated data in real time that would usually be calculated in a BW system in a non-HANA environment. So you can regard this as a first step into Operational Reporting.
And now we come to the most important message of this blog: “HANAlytic” ABAP applications will be able to add following aspects to SAP Business Suite:
- aggregated information about the business context (e.g. market segments)
- forecast
- simulation
As first step we will start to add dashboards to many transactions – perhaps as NWBC sidepanel or HTML-control using one of the following technologies:
- Adobe Flex/Silverlight as WDA-Islands or within BSP
- BI/BOBJ dashboards
- charts created with JavaScript/HTML5
In September SAP announced at DSAG Jahreskongress WDA Islands as part of WDA Floorplan Manager which gives amazing possibilities for next generation ABAP Apps. The reason is that with SAP UI5 and JavaScript you can create super fast and beautiful frontends with state-of-the-art web technologies which make development very easy because they are very mature.
For the account balance application I mentioned above I used the open Source JavaScript Library D3 ( http://d3js.org/ ). The reason is very simple:
- D3 has a big repository of different charts – please have a look at https://github.com/mbostock/d3/wiki/Gallery .
- D3 is Open Source (http://opensource.org/licenses/BSD-3-Clause) and comes for free.
- You can integrate D3 in SAP UI5 easily.
In the following I will sketch the integration of D3 in SAP UI5: If you work with an XML view just insert a div-element within the view perhaps in a MatrixLayoutCell element: <html:div id="chart"></html:div> Within the onRequestCompleted function you get the name of the div-element in the SAP UI5 view as follows: var sDivName = "#" + oEventData.sViewId + "--chart"; Then you can append an svg-element and use D3 as usual: var svg = d3.select(sDivName).append("svg")Then you can read out the model of the model of a SAP UI5 application and expose it as a chart.
In some cases BOBJ tools could be a better choice. If you are looking for a solution then I recommend that you check the requirements to choose the right technology. Let me give you an example: In some use cases it is necessary that the dashboard knows the context of the SAP transaction (think of the business object that is currently selected).
I think a very interesting question is when to use which technology for a special use case. If you are familiar with JavaScript and D3 then this is a very lightweight solution. I think it is a very interesting challenge to find best practices when to use what technique.
Summary
At first I want to thank Welf (“The man who tried to kill TRMAC”) from the ABAP for HANA team who helped me to implement the solution. I knew that a non-equi-join would help but he gave me the right hints to deal with pesky little details. The picture below is a nice recollection of a visit in Walldorf during "Trailblazer" Customer Engagament Initiative. It was a challenging as well as inspiring time and I hope the developers in Walldorf enjoyed it, too. So want to thank everyone who contributed to the blog by helping me developing the prototype: Ingo and Chris from SAP from whom I learned a lot about SAP UI5 and Thorsten from SAP for many hints.
I believe ABAP developers will need a set of best practices for developing analytic applications in the future:
- an ABAP for HANA “cookbook” with tips & tricks that explain how to solve recurring problems
- skills in predictive analysis like forecast and simulation
- skills how to use BI/BOBJ tools
- last but not least ABAP developers will have to sharpen their SQL skills
I think SAP TechEd is a great starting point to acquire those skills.
For me it seems natural that building HANA optimized ABAP applications will be a step by step approach:
- At first we will start to optimize existing applications by removing bottlenecks.
- Then we will develop only analytical applications that remove existing pain points.
- When we have more experience we will introduce the aspect of operational reporting to ABAP using dashboards and introduce BI features like forecast and simulation.
Last but not least I recommend everyone to stay tuned and learn about latest SAP innovation and announcements not only at TechEd but also at user groups like DSAG.
- SAP Managed Tags:
2 Comments
