In this blog entry I would like to discuss some experience with decision tables created by DSM that are running on HANA. In my opinion it is one the most exciting technologies so far:
In this blog I’ll introduce some concepts, show a basic but somewhat realistic example and discuss some best practices and last but not least possible improvements for coming DSM versions.
If you want to use HANA expressions in BRFplus you habe use the AddOn Decision Service Management called DSM. For the ones who don’t know the difference between BRFplus and DSM let me try an explanation. BRFplus is a technology of the SAP NetWeaver platform. Technically DSM is an AddOn to BRFplus that is seamlessly integrated into BRFplus - so BRFplus is not disrupted by DSM. The opposite is true: DSM completes BRFplus by introducing a number of features that are necessary for the management of business rules, f.e.:
In fact the DSM strategy goes even further and there also partner solutions that help you to deploy business rules to non-SAP systems. But all those features are beyond the scope this blog and instead I will discuss only the feature of pushing decision tables down to HANA. You may ask why are business rules in HANA important? Usually decision services implemented in BRFplus/DSM are called from ABAP for a single line item and return calculated values for single item. This is useful but with HANA we can do more:
The last aspect is most important since it makes SAP Business Suite transparent and easier to change and you can react faster if your business rules and practices when you have to react on competition, compliance, legal requirements and more.
In an ideal (but not always realistic) architecture we would use the same decision table both for decision services on single business objects but also for mass operations on HANA database model. And in there are good news: DSM is HANA-ready! And this is how it goes: DSM has a new object type called Dynamic Database View and the view connects a data source to a decision table and the result is a HANA artifact which can be transformed into a transportable HANA artifact afterwards. So you still have to deal with transport of HANA artifacts which is still a little bit unhandy but if you built HANA side-by-side scenarios you will know everything you’ll have to know.
So DDBV uses HANA decision tables which are well known. For the ones who have no experience with this technology I will explain in a few word: There are three possible ways HANA decision tables can operate:
By combing the data model (f.e. a HANA Calculation View) with a BRFplus decision table you can use the object in DB lookup expression in a BRFplus application.
Wolfgang Schaper explained the steps for creating a decision table and in his Whitepaper he chose a database table but usually this won’t work since the data is usually scattered across many different tables – perhaps even customizing tables are involved. This makes it even more difficult since often you have to avoid hard coded constants. So what should you do? In general you will have no choice and create HANA Calculation Views. So if you are lucky and your developers have HANA skills then this will be an easy task.
So let’s look at an example which is a typical and somewhat realistic use case since different tables and customizing tables are involved:
To use a decision table in this scenario I have to transpose this item/position relationship ino a single table:
The values CLASSIFCATION1, CLASSIFCATION2 and CLASSIFCATION3 should be not equal NULL if for an item there exists a row in the ZPOSITION table having that value.
This will be more clear when you see an example. These are the items, the first item is associated to a Business Partner:
Each item has a certain number of positions, each has a classification:
The classifications in my scenario are defined in a customizing table. Only those classifications are relevant in my scenario – the other ones can be omitted here:
When I join everything together I get the following (flat) result. Please remark that some values (depending on above customizing table) should be take from the positions to the items:
Now I can define a decision table that works on each item and defines a decision service that calculates output results. But therefore I have to join all those data in the described manner together. One possibility is to create a HANA view that performs the join.
And this is exactly what you have to do when using DDBV: You may a Calculation View in your HANA Studio to perform some SELECTs and JOINs. You see the view such a view here:
I’ll come back to the SQL statement used in the view later but at first I want to complete the workflow for creating a decison table based on a HANA view.
To access HANA data there is a new object type called dynamic database view (DDBV). With this object you can consume a table or an HANA view. Therefore I define DDBV for a calculation view and select database fields as result fields like shown below:
As result data I define a new calculated field called CLASSIFICATION which is calculated in a decision table POSITIONS_DT:
This decision table can be defined arbitrarily, f.e. as follows.
As result HANA decision table is generated from the BRFplus decision table having the HANA Calculation View as datasource:
In fact you can also define different input parameters for the decison table that can be used in the column expression but are not passed through into the view.
So let me summarize:
As I told before, decision tables work on relational data and due to normalization they are spread over many database tables. So the main challenge is view building to create the input data for a decision table.
Since DDBV supports HANA artifacts (working on a single ABAP table is often not realistic) HANA development is necessary and so are HANA artifacts that can be transported using CTS+. So you can start implementing this scenario but from my point of view it is not that comfortable as it could be. But here my opinion is very simple: If you can solve pain points with HANA development then you should definitely do this and start creating HANA views.
For the sake of completeness I will mention another option: in scenarios where BW and HANA is involved you can generate HANA views from DSO: this is described in the HANA development guide. Please remark that this is possible when you are using an Enterprise BW but it should work also when working with an Embedded BW on operational data.
Since decision management is about making decisions based on operational data IMHO a plain ABAP approach would be best. Above mention DSO should be avoided unless you don’t have good reasons to do.
In my opinion a future Version of DSM should support ABAP Database Views and these could extended them by result columns since this lead to pure ABAP based programming model, But wait, didn’t I wrote that classical database view are not useful. Yes, but this is only half of the truth since NW 7.40 ABAP supports new view building possibilities using ABAP Core Data Services. The advantage of this approach is that it is seamlessly integrated into ABAP Workbench and you don’t need care about the clumsy HANA transport mechanisms like HANA Transport Container. Unfortunately we can’t define calculation views in CDS (=extension of normal views) otherwise it would be very easy. At first you define a join for each relevant classification:
Since multiple entries can occur we select only distinct ones:
Then we join everything together:
An SQL expert will immediately recognize possibilities for simplification and optimization. I show this example nevertheless since it was the only possibility to solve above described join in NW 7.40 SP4. In SP5 and later we have completely new possibilities and the optimization of above CDS views is “left to the reader as easy exercise”.
This is what we can learn the following from example:
Unfortunately DSM doesn’t support those techniques at the moment and so HANA development will be necessary in most real-word scenarios.
So far DSM’s decision tables built on Dynamic Database Views are a little bit limited. Of course they do their job but together with advanced ABAP view buildung the implementation would be much easier. On the other hand the possibility of using arbitrary HANA Views in DSM is also a chance since you can also access data from non-SAP systems and even consume BW objects suing BRFplus which is useful especially in BW on HANA scenarios. If you need further information about this scenario you should look here and here.
Another interesting aspect is that obviously the HANA-power of DSM is related to the features of the HANA database. So far decision tables are the only expression that can be pushed to down to HANA but it is obvious that every extension of this mechanism will make DSM much more valuable – think of nested decision tables for example. Here I expect more to come and this is why I already looking forward to SAP TechEd && d-code especially
SAP TechEd && d-code 2014 session about SAP Decision Service Management.
So my impression is: DSM made a good start on its HANA road. Now SAP has improved its infrastucture (especially HRF and CDS) and therefore synergies should be possible. So I think the DSM HANA story will continue and I am eagerly waiting for further annoucements.
And last but not least I recommend every ABAP developer to inform about latest ABAP features (SAP Inside Track Munich this weekend is a perfect chance) and to fresh up his SQL skills since the invention of means a reinvention of the ABAP Dictionary and extension of the SQL capabilities of the AS ABAP.
In the case you are interested I show you the SQL statements generated from above shown CDS views. You can use them directly to build HANA views using SQL:
CREATE VIEW "ZMYSCHEMA"."ZVJOINED_POS" ( "MANDT",
"ITEM",
"BUYER",
"BIRTHDATUM",
"CLASSIFICATION1",
"CLASSIFICATION2",
"CLASSIFICATION3" ) AS SELECT
ITEM."CLIENT" AS "MANDT",
ITEM."ITEM" AS "ITEM",
ITEM."BUYER" AS "BUYER",
BP."BIRTHDT" AS "BIRTHDATUM",
C1."CLASSIFICATION1" AS "CLASSIFICATION1",
C2."CLASSIFICATION2" AS "CLASSIFICATION2",
C3."CLASSIFICATION3" AS "CLASSIFICATION3"
FROM ( ( ( "SAPDAT"."ZITEM" ITEM
LEFT OUTER JOIN "SAPDAT"."ZVCLSFDDPOS1" C1 ON ITEM."CLIENT" = C1."MANDT"
AND C1."ITEM" = ITEM."ITEM" )
LEFT OUTER JOIN "SAPDAT"."ZVCLSFDDPOS2" C2 ON ITEM."CLIENT" = C2."MANDT"
AND C2."ITEM" = ITEM."ITEM" )
LEFT OUTER JOIN "SAPDAT"."ZVCLSFDDPOS3" C3 ON ITEM."CLIENT" = C3."MANDT"
AND C3."ITEM" = ITEM."ITEM" )
LEFT OUTER JOIN "SAPDAT"."BUT000" BP ON ITEM."CLIENT" = BP."CLIENT"
AND BP."PARTNER" = ITEM."BUYER" WITH READ ONLY
Please remark that ZVCLSFDDPOS1, ZVCLSFDDPOS2and ZVCLSFDDPOS3 resp. are defined as follows:
CREATE VIEW "SAPDAT"."ZVCLSFDDPOS1" ( "MANDT",
"ITEM",
"CLASSIFICATION1" ) AS SELECT
DISTINCT ZVCLSFD_POS1."MANDT" AS "MANDT",
ZVCLSFD_POS1."ITEM",
ZVCLSFD_POS1."CLASSIFICATION1"
FROM "ZVCLSFD_POS1" WITH READ ONLY
CREATE VIEW "SAPDAT"."ZVCLSFD_POS1" ( "MANDT",
"ITEM",
"PSTN",
"CLASSIFICATION1" ) AS SELECT POSITION."CLIENT" AS "MANDT",
POSITION."ITEM" AS "ITEM",
POSITION."POSTN" AS "PSTN",
POSITION."CLASSIFICATION" AS "CLASSIFICATION1"
FROM "ZPOSITION" POSITION
INNER JOIN "ZCLASSIFICATIONS" CLASSIFICATION ON POSITION."CLIENT" = CLASSIFICATION."CLIENT"
AND POSITION."CLASSIFICATION" = CLASSIFICATION."CLASSIFICATION1" WITH READ ONLY
Please remark that CDS not only restricted to HANA but also available for any DB. But don't forget: they work in ABAP NW 7.40
SP4 – so in later SPs CDS is much more powerful. And for really challenging scenarios I think HANA optimized SQL is still the best solution.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
37 | |
10 | |
5 | |
4 | |
4 | |
4 | |
3 | |
3 | |
3 | |
2 |