Usage of BRFplus in BW Transformations – Quick Wins and Challenges
In my last blog I discussed the Dynamic Database Expression in DSM. This expression gives you the possibility to access data from HANA database, AS ABAP on HANA and so especially BW on HANA systems. Please remark that every AS ABAP contains a local BW that you can use as Embedded BW. Using an Embedded BE you can perform operational analytics with the same technologies you may know from the Enterprise BW world. In this blog entry I’ll discuss how to use BRFplus in an BW world: Enterprise BW as well an Embedded BW.
In BW there are so called transformation which can contain ABAP implementations in so called expert routines or better rule routines or formula BAdIs. A transformation performs a so called data Transfer process from a Data Sourse/PSA or InfoProvider to an InfoProvider. The process is described in SAP Library and I “steal” the following picture from there:
Let me also cite SAP Library:
A transformation consists of at least one transformation rule. Various rule types, transformation types, and routine types are available. These allow you to create very simple to highly complex transformations:
- Transformation rules: Transformation rules map any number of source fields to at least one target field. You can use different rules types for this.
- Rule type: A rule type is a specific operation that is applied to the relevant fields using a transformation rule. For more information, see Rule Type.
- Transformation type: The transformation type determines how data is written into the fields of the target. For more information, see Aggregation
- Rule group: A rule group is a group of transformation rules. Rule groups allow you to combine various rules. For more information, see Rule
- Routine: You use routines to implement complex transformation rules yourself. Routines are available as a rule type. There are also routine types that you can use to implement additional transformations. For more information, see Routines in the Transformation.
When you use BW transformations with ABAP coded rules you can get the usual benefits of BRFplus:
- The business logic is easier to understand compared to ABAP code.
- BRFplus logic is extensible.
But one question remains: in which cases you can immediately benefit from using BRFplus? The answer is very simple: usually a transformation gets a data package as Input (i.e. an internal table) and returns a internal table with new, calculated values. So if you can loop the input data and calculate the resulting data line by line you can directly benefit from using BRFplus by calling a function.
The situtation gets more difficult when you have to enrich the data. Therefore you usually need data f.e. from DSOs that are selected using SELECT … FOR ALL ENTRIES due to performance reasons – don’t forget we are working with packages of data. Moreover in many use cases we table and loop operations to calculate the results.
In this case we have the problem that the DB Lookup expression of BRFplus doesn’t support the FOR ALL ENTRIES statement. Another but not painful drawback is that DB Lookup doesn’t know DSO object but usually can use method cl_rsd_odso=>get_tablnm to get the name of the corresponding transparent table. Fortunately BRFplus can show the texts for DSO’s generated transparent tables.
Please be also aware that BRFplus code is fast but usually not that fast to hand coded ABAP. The reason is that ABAP offers many features for internal tables like secondary indexes and with 7.40 SP8 more is to come. But I don’t consider this as a reason against BRFplus – only in time critical and high volume scenarios. I am well aware of the fact that developers love to theorize about performance. When I was I student I was a developer in a mathematical research institute and there have been discussions about C vs. C++. Today I am tired about such discussions and only wan to cite Donald Knuth: “We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil“. Of course there are use cases that must be fast but business logic must be comprehensible and flexible first.
To summarize my point of view: you can use BRFplus in BW scenarios. In later discussions of Twitter I was told to mention the drawbacks of this approach:
- ABAP code can’t be pushed down to HANA.
- Before using ABAP code resp. BRFplus you should try to evaluate DSO Looup Rules: Usage of BW7.3 Transformation Rule Type “Read from DataStore”. Unfortunately they have a limited feature set which is sometimes not enough and in this case you may start to think about using BRFplus.
I can also emphasize this: you should use this approach only in scenarios where it is necessary and makes sense. Reckless usage of ABAP code in BW artifacts is always critical.
The only challenge using BRFplus are use cases where you read data from other DSOs and here is FOR ALL ENTRIES missing in BRFplus which is painful. Of course you can call ABAP code from BRFplus but this reduces the value of a rule system in my opinion.
A new DB Lookup Expression would be useful
There are many ways how BRFplus and BW and BW can interact. For using BRFplus “inside” BW there are many scenarios but an enhanced DB Lookup expression would be very useful in these scenarios. Since the database gets more and more in focus in ABAP programming and Open SQL gets extended in NW 7.40 it is time to introduce a new DB Lookup especially for this release. This would help not only in BW scenarios but also upvalue BRFplus in general.