A quick look at DSM HANA Expressions using Dynamic Database View
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:
- Business rules are far superiour to customizing and makes SAP Business Suite and custom development more transparent and easier to change.
- BRFplus (and also HANA) has the concept of decision tables which is one of the most useful expression type when using business rules. They are easy to use and also business experts can understand them.
- With DSM you can push business rules down to HANA and I think this has enormous potential. One killer application is simulation of rule changes. So far this is a very complex task where business experts und technical consultants have to work hand in hand and they need system landscapes for tests. Wouldn’t life be much easier if this simulation possibility would be supported directly by your SAP application running on HANA?
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.:
- better deployment mechanisms for rules
- new tools for testing and debugging
- better governance: you can centralize the development of rules systems and also introduce additional metadata that help you for migration and maintenance of rule systems
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:
- In Big Data scenarios code pushdown to SAP HANA is reasonable. So decision services should be deployable to HANA.
- Even in classic scenarios with data of moderate size the use of HANA is still promising since it allows you to simulate the effects of changes of rules systems. With HANA you can calculate the effects of changed rule systems on the fly and can analyze the results.
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:
- They rely on a database table and can change the data.
- The second option (and will discuss this), creates a calculation view having a result column that is calculated.
- Another option is that the view relies on an Analytical or Attribute View and the decision table performs a projection: a certain row can be in- or excluded from the result set.
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.
A simple but realistic example
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:
- I have table ZITEM and ZPOSITION and both are in a one to many relationship.
- ZITEM has an association to the central business data and in my decision table would like to use the birth date from transparent table BUT000.
- The elements of ZPOSITION have a classification attribute that should be used in my decion table and can contain values like VERY LOW, LOW, MIDDLE, HIGH, VERY HIGH.
- Moreover I don’t want to hard code these values since they rely on customizing (transparent table ZCLASSIFICATIONS).
To use a decision table in this scenario I have to transpose this item/position relationship ino a single table:
- ITEM – for each entry of table ZITEM
- BUYER – a Business partner number. This would be only useful in my decision table if I would like to define special rule for some partners
- BIRTHDATUM – one attribute of BUYER form transparent table BUT000
- CLASSIFCATION1 – corresponding to a value CLASSIFCATION1 in a customizing table ZCLASSIFICATIONS
- CLASSIFCATION2 – corresponding to a value CLASSIFCATION2 in a customizing table ZCLASSIFICATIONS
- CLASSIFCATION3 – corresponding to a value CLASSIFCATION3 in a customizing table ZCLASSIFICATIONS
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.
The Design of a HANA Decision Table
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:
- The DDBV object is the link to a HANA artifact (f.e. a Calculation View) that is on a remote HANA DB (think of a schema of BW on HANA system perhaps containing data even from non-SAP systems) or ABAP on HANA Systems (of course having HANA as primary persistence).
- The decision table working on the view is optional. It is generated from a BRFplus decision table but the expressiveness is limited since you can’t use all
expressions (think of custom defined ABAP formulas for example). Unfortunately the supported features doesn’t seem to be documented so you have to try it out.
- Usually the data are consumed using an ordinary DB Lookup expression in BRFplus. But since you know the HANA artifact you can call them directly using ADBC for example or tie them to a ALV on HANA grid for further analysis.
The Main Challenge: View Building
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.
SAP’s “Secret” Weapon: Core Data Services
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:
- Pushdown of a decision table to HANA needs view building which should be done using CDS since CDS is superior to classic ABAP Dictionary DB Views.
- If there would be a way extend above view by result columns in CDS then it could easily linked to DSM.
- IMHO ABAP CDS views should get parameters and we should use them not only in a decision table but also pass them to CDS for the reasons of flexibility. Moreover it should be possible to pass parameters from DSM to CDS.
- We should start to think whether the update after execution of a decision table should be performed on the application server. Perhaps it would also good to do it using a database procedure.
- For more complex calculations we should start to think about nested decision tables but as far as I know this currently out of scope of HANA decision tables.
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
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”,
“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”,
“CLASSIFICATION1” ) AS SELECT
DISTINCT ZVCLSFD_POS1.“MANDT” AS “MANDT”,
FROM “ZVCLSFD_POS1” WITH READ ONLY
CREATE VIEW “SAPDAT”.“ZVCLSFD_POS1” ( “MANDT”,
“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.