SQL Expressions in BW/4HANA Composite Provider (HCPR)

Veröffentlicht am 16. Januar 2021 von

Jörg Brandeis

With the SPS04 for BW / 4HANA 2.0, SAP has built some useful features into the composite provider (list of new features in the SAP documentation: https://help.sap.com/viewer/b3701cd3826440618ef938d74dc93c51/2.0.6/de-DE/d8b12ec099e04e6aa77a312be687db63.html ):

• New join types are possible: Full Outer, Referential, Right Outer Join
• The restrictions regarding the order of JOIN and UNION nodes no longer apply.
• The operators , =, != now also allow non-equi joins.
• Projection and aggregation nodes can be created
• Columns calculated with SQL expressions can be defined
• The data can be filtered with SQL predicates the data.

Especially the last two points are of course interesting for me as a SQLScript expert. This opens up a whole new range of possibilities, which I would like to analyse in this article. Unfortunately, the interface for the SQL features is an imposition, which is why I'm giving a bit of a tutorial on how to use them here.

The differences to the Calculation Views (CV) of SAP HANA are visibly disappearing. Only the modeling interface for the CVs is much more comfortable, especially in the WebIDE. I will return to this striking similarity to the CVs later.

Filtering in BW/4HANA Composite Providers with SQL Expressions

In each node type, except for the part providers themselves, it is possible to specify an SQL filter. To do this, you have to go to the context menu of the respective node.

The interface that then pops up in a popup is anything but self-explanatory. At first it looks like a typical formula editor. Because it is supposed to be a filter, I guess a predicate has to be entered. So let's start with a simple comparative predicate. The first question here is how we can refer to fields or InfoObjects. Unfortunately, there is no corresponding support by value help or selection and we have to experiment willy-nilly. Here it turns out that we have to refer to the names in the target of the respective node, using the special notation with quotation marks and capital letters for identifiers. Errors in the SQL expressions are only displayed when the BW/4HANA Composite Provider is activated.

The following predicates work:

• Comparison predicates with the comparison operators specified in the selection list
• Comparison with BETWEEN, e.g. "0CO_AREA" BETWEEN 'A' AND 'B'
• Quantity comparison with IN, e.g. "0CO_AREA" IN ( 'A' , 'B')
• LIKE for filtering with simple patterns
• LIKE_REGEXPR for filtering with regular expressions. Caution: The value "like_regex" suggested in the selection box does not work.
• IS (NOT) NULL - This could be handy for filtering OUTER JOINs data to simulate the EXISTS predicate.
• CASE-expressions in comparisons

"0CO_AREA" = CASE "GXXMSSID" 
                  WHEN 'P01100' THEN 'ABC'
                  WHEN 'Q01100' THEN 'CDE'
                  WHEN 'D01100' THEN 'DEF'
                  ELSE ''
             END

Not allowed are:

• The EXISTS predicate
• IN-Predicate with subquery
• The quantifiers ANY, ALL and SOME

Unfortunately, this means that all options for filtering with subqueries are missing. But many things you can do with EXISTS and IN/ANY/ALL/SOME with subquery can now be done with the extended JOIN conditions and aggregation nodes.

Fields in HCPR calculated with SQL expressions

• Group
• Type
• Name
• Description
• association with
• Unique name
• Checkbox Force Group By
• Data type & Length

With the experiences from the filtering I tried here also again, which expressions are permitted and which not.

Permitted expressions for calculated fields

• SQL functions from the selection box - with a few exceptions such as the LIKE_REGEX already mentioned above. However, here are SQL functions that are not mentioned in the most recent reference documentation. For example, the ABAP_EXTRACT_DAY( ) function. However, this is also available normally in the SQL console on a HANA Express.
• Other SQL functions not listed here. An example of this would be the SQL function WEEKDAY(), which can be used without further ado, but is not offered for selection.
• Simple and complex CASE expressions.
• Literals, also typed
• Fields from the target list in special notation with quotes and in capital letters

And also all combinations of these expressions to more complex expressions are allowed.

Not allowed expressions are:

• Other SQL functions known in SAP HANA will cause errors when enabling HCPR. For example, the RAND() function.
• Aggregate functions, not even in aggregation nodes :-(
• Window Functions
• Sub-Queries

So you're left with a bit of trial and error as to which functions work here and which don't. Especially with the SQL functions it is very experimental. By the way, the error messages are identical to those of the Calculation Views. From there, I suspect it's the same amount of features allowed here.

Does anyone know an overview of what is allowed there and what is not?

Conclusion on SQL Expressions in BW/4HANA Composite Providers

The new features are nice, but not 100% mature yet. Specifically, I would like to see:

• A value help for the available fields in an expression
• A complete and correct list for the selection of possible SQL functions
• An ad-hoc check of the syntax and not only when activating the HCPR
• Better a good code editor with syntax highlighting and code completion than a bad formula editor.
• That all SQL functions displayed are also included in the SAP HANA SQL reference documentation.
• That also the other SQL functions are allowed, because the selection seems to me very arbitrary

The other missing features, for example the window functions, can have an extreme impact on the execution plan and optimization. SAP obviously does not want to take this risk. Personally, I think that's a shame, but I can understand it well.

Addendum (January 29th, 2021): Today I saw the use of both features in a project. Using it had a positive effect on the architecture used, which I really liked.