- SAP Managed Tags:
I've been writing Virtual Data Models (VDM) with CDS Views in Suite on HANA and S/4 for the past 5 years, and they're a great tool for building compelling analytics. I've created a blog on the VDM which you can look at for reference on the general structure (https://blogs.sap.com/2018/03/19/s4-embedded-analytics-the-virtual-data-model/ ).
But in this blog, I want to take it a step further and discuss the advantages of using table functions, as well as run through an example of how to build one. To be clear, table functions don't replace CDS views, but it's another tool in which we can perform some slightly complex logic (more complex than within a CDS view), and significantly simplify the overall VDM architecture.
What do I mean by VDM Architecture: it's the number of CDS views you need to build a VDM. I argue that in it's most simplest/basic form, a VDM would look like the image below:
In a robust build, we'd include master data, however, technically, depending on the requirement, you might not even need it, so the minimum number of CDS Views we'd use would be 3 on the transactional side: Basic -> Composite -> Consumption.
For those of you more experienced in writing CDS views, you will know that depending on the requirement, we might need more than one basic view to harmonize the data and build the fact table.
One very common example of this is when we need to join two tables, at the line item level, and one table might have the data type of the item as NUMC5 and another table as NUMC6. The CDS view will not allow us to join those two items given the discrepancy in the data element, so we'd first have to CAST one of the items to be the same as the other. Then build a 2nd view, that joins the 1st view with the CAST item, to the other table.
In the example above, we created two CDS views to perform a simple join operation. It works, and is doable, but the overall VDM architecture can become very complex with many CDS views to achieve some basic operations.
This is where table functions become handy. A table function is directly linked to a single CLASS->METHOD.
The Method is where all the logic is written. The table function simply exposes the output of the logic from the Method in a tabular format. For the purposes of the CDS view, the table function behaves like a table, and can be selected or joined in a CDS view like any table in the system.
Methods allow us to perform some SQL calculations that we can't necessarily do in a CDS view, as well as string calculations within a join and where clauses. Another advantage is that a table function allows for internal tables, which is something a CDS view cannot do.
So for the example above, I can create a single table function that casts/converts/manipulates one of the items directly in the join criteria.
My general rule of thumb for deciding when to use a table function vs multiple CDS views is relatively simple: if it takes me 2 or more basic CDS views to achieve a specific logic, I'll do it in a table function.
Below is my comparison of some of the functionalities between the two:
Let's go through a step-by-step process of creating a table function.
Full disclosure - (notice how it's in italic, bold and underlined to really stress this point!!!!):
I'm not an ABAPer, never was, don't intend on becoming one. I've used ABAP extensively during my BW days for transformations, start, end and expert routines, and some basic ABAP programs. I know how to leverage code when needed, but sometimes don't fully understand the architecture behind it. There are more specialized people within this area as well as numerous blogs that can help clarify specific questions around the ABAP programming architecture itself.
So with that, hopefully you can learn something new in the steps that follow.
1. Create a table function
First step is to create a table function. Within HANA Studio/Eclipse, in the ABAP perspective, create a new data definition
Give it a name and a description. For this example I'm creating it in my local package, but you will be prompted for a transport if it's not in a local package. Hit finish:
You will be presented with the screen below:
Now we need to make a few changes
A few things to note:
1. We create the table function 1st. The Class/Method don't need to exist yet.
2. We don't need any specific table function annotations
3. The definition is different than a CDS view, where we Define Table Function
4. The body (fields within the curly brackets) will contain the fields that will be returned from the method. Need to specify the field name (on the left), as well as it's data element (on the right)
5. Finally on the bottom, it has an explicit reference to the method and class being used. Even though the Class/Method doesn't exist, it will allow you to activate it.
6. Activate the table function
Next, we will create the class, then the method.
2. Create the Class/Method
First, within the package or local area, right click and select New->ABAP Class
Ensure you create it with the same name you gave on the table function earlier.
Alternatively, you can use an existing Class to capture all the analytics specific methods to be used in table functions.
Hit the Finish button.
You will end up with the class below:
We need to make a few changes now to make it usable for table functions.
1. First we need to add the IF_AMDP_MARKER_HDP interface (more below: https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-us/abenamdp_classes.htm)
That is added after the public section.
2. We then define the method, provide it the same name we gave it in the table function, and also tie it back to that table function.
- You'll notice there's a circular reference from the table function to the class/method and from the class/method to the table function. It's a 1:1 relationship.
3. Under the CLASS .... IMPLEMENTATION, we then add the Method, and the logic for that method
3. Preview the Table Function
Once all that is in place, and the logic checks out, activate the class, and you should be good to go. To preview the data, simply go to the table function and hit the F8 button, or right click anywhere in the table function -> Open With -> Data Preview
4. Code Review
Now let's go through a few snippets of the code just to highlight on a very basic level, some of the key things you can do.
1. First thing, you need to explicitly mention which tables will be used
2. You can define internal tables with any name you chose. Different from ABAP where you might have to specify a TYPE with the actual structure, here, the fields you chose to populate the internal table will drive it's structure. So all you have to do, is determine which fields from the table being selected you want in the internal table, and it will have that structure
Also notice, that an internal table can be referenced in another internal table. In this case, I'm doing a select into itab2 from itab1. Notice that to use an internal table in another select statement, you need to add the colon ( : ).
3. The return select portion of the code is where we're sending the data from the method back to the table function. The field names here need to match exactly what's in the table function. You can use aliases to name it correctly.
4. If you're using a table that starts with a forward slash in a select statement, it needs to be encapsulated within double quotes, and all in upper case
Now let us walk through a different example, and I'll also show how to debug a table function.
In the second internal table itab2, I've added a simple where clause, where I'm taking the two left characters from the material field (matnr) and making it equal to a criteria. All of that directly in the select clause.
Notice how I'm using the string function directly in the where clause. A CDS View does not allow for that. This is where I'd need a 1st CDS view to do that string function in a new field, and then a 2nd one to apply that new field in the where clause.
5. Debugging the Method
Now depending on how complex or simple your logic is, you might be able to simply validate this by looking at the output of the table function. But maybe you want to step through the code to see what it's doing at every step.
The breakpoint will be set within the method, and it can be done two ways:
1. Right click on the side column and a menu will appear, then select Toggle Breakpoint.
2. Or place the cursor on the line to set the breakpoint, and use the shortcut Ctrl + Shift + B
Once it's set, it should look like the image below:
You are now ready to debug. To do so, do a data preview on the table function. HANA Studio/Eclipse should open a the Debugger perspective (top right of the screen), and the execution will stop at the breakpoint that was set.
The two main buttons you will need are F6, which steps into the next statement, and F8 which runs the code either to completion, or to another breakpoint set further down the code.
For my example, I hit the F6 button to step to the next line of code. You can see on the top right hand side, under the Variables, my two internal tables itab1 and itab2. If you double click on it, you will see the contents of the internal table. And I can see that itab2 only has records where the first 2 characters of MATNR start with T4.
You can apply filters to further troubleshoot any modelling/select issues with the code.
And that's pretty much it.
6. Consuming the Table Function in a CDS View
Now that the table function is ready, I can easily select/join it to a CDS view to start the process of building that fact table, and adding semantics to enrich the data. At the end of the day, the table function behaves like a table. See the example below (I'm not using all the required CDS View annotations since this is just for illustration):
7. Conclusion
In conclusion, I'm using table functions significantly more these days in order to encapsulate complex logic that would require > 2 CDS views to achieve. If you're already used to creating CDS views for analytics (think SAP Analytics Cloud, Analysis for Office, Fiori, ALV Grid lists, Tcodes), learning to build Class/Methods + Table Functions should be fairly easy, and it's not a steep learning curve at all.
I'd encourage all of analytics practitioners and data modelers working with embedded analytics, to give this a try and see if you can make your architecture leaner and easier to maintain.
As always, any and all feedback is appreciated, I encourage you to let me know what your experience is and share with the community your thoughts on this approach.
CDS Views are evolving fairly quickly within S/4 HANA from when I originally started, so I expect things will be changing in the near future, hopefully for the better.
If you read all the way here, as my kids like to say, I hope you have a scrumptious day!
All the best and be safe!
14 Comments
