Create flexible database tables for your applications.
Most of us today, working on SAP solutions have to create custom applications. These applications may be simple program to as complex as designed across multiple SAP / Non-SAP systems. One of the most important design requirements to tackle for these custom applications is the application configuration. If it is custom, 90% of the cases require custom configurations on which the application runs. These configurations also validate the underlying assumptions on which the application is designed.
Sometimes these configurations might be so small that they end up as constants or hard coded values. Sometimes they might end up as tables. The main problem is to understand how to define these configurations so that the code consuming it can remain generic to support reuse, scalability and flexibility objectives.
In my early days as a developer I was responsible for developing configuration and logic, which would for the first time be consumed by one application but over the years the same would be used by multiple similar applications. I was lucky to have a seasoned SAP developer who was collaborating with me on this development. He told me that from his experience for the first and may be even second time when we are designing these models it is very difficult to make them generic and I second that thought since it is very difficult for the application teams to have that long a vision about the applications as well as the availability of time to accomplish the same.
Also most of the teams across the world are switching onto Agile Development methodologies (which most of them abuse) in which the requirements and design evolve over a period of time, it is more than necessary to make the foundation (configuration) flexible enough to reduce the impact of continuous
So here I used several learning’s from the past and came out with a concept which is flexible enough to work in any application that you create and is complimented by similar code support in other development languages like JAVA.
The case with which I want to explain this concept is that of a website. I have to create a website which will be used to sell my products in multiple subsidiaries. Each subsidiary will have its specific pricing, promotions, quota logic, order types and a lot more. For simplicity sake I will take examples of Switzerland and Germany. Apart from business logic, I want to reuse the code that I am developing for my site like the UI rendering language and other things. I also have web services to integrate my SAP backend with the site (another blog would come up soon to talk about creating just one web service to cater to all logic).
So how do I define my application configuration:-
- Create a database table with some key fields ( I would use just one string or char field to store values like SWISS_UI, SWISS_PRICING, SWISS_QUOTA, SWISS_ORG, GERMAN_ORG etc) which can help you create unique config types.
- Have a string define which will store all the attributes as an XML string.
- Create a XSLT transformation to encode and decode the XML string.
<xsl:transform version=”1.0″ xmlns:xsl=”http://www.w3.org/1999/XSL/Transform“>
- Have a mapping structure which is a universal set of all relevant attributes related to a config type.
- Create a setter ( an FM, Report, Class Method – Most preffered ) which will generate the record for the table
data : tab1 type ref to data,
vbak_tab type table of vbak,
vbak_wa type vbak.
create data tab1 type table of vbak.
field-symbols : <tab1> type any table.
assign tab1->* to <tab1> .
select * from vbak into table <tab1> up to 1 rows.
call TRANSFORMATION transformation
SOURCE record_data = <tab1>
result xml xml_string.
- Create a getter that will read the config record and reverse transform into my mapping structure which can be consumed in relevant areas of my application.
Call TRANSFORMATION transformation
source xml xml_string
result record_data = <tab1>.
- To map you can also use CL_ABAP_TYPEDESCR class to create another layer of generic code.
So this is a just a concept which you can use in your applications. You can also use it to create custom log tables, staging tables just anything where you are working with an evolving data model. Do comment if you have used any other similar approach for creating applications.
Whilst very flexible, how do you see this fitting with the move to HANA? In order to make payroll run on HANA the cluster tables (which this approach appears to mirror) have been removed. Flexibility comes at a cost which is search speed and DB compression, perhaps in some cases this is a good compromise.
As already mentioned in the blog, this approach is more oriented towards config tables and not transaction data. Although I mentioned staging tables as well but their the case will be when u r using the same tab,e to store staging data for similar applications.
It seems very flexible, but what about the basic search functionality using SE16 or SE16n. What about when the data in table grows huge? I believe if that field is not part of your key field, you would need to parse each XML, collect the fields, filter and display. The runtime of using any field to filter would be same - drawback on performance.
Similar design is adopted in IDOC processing. Table EDID4 contains the data for each segment. When you run WE09, it has to do the same thing, of selecting data for all the relevant IDOCs, map to its segment structure and compare with the input. Thus it is very slow from the performance point.
I am not going to replace all the database tables with this approach. This is a design concept for evolving applications and not necessarily for their transaction data.
Second to the point of searching, you are writing a getter api, the search can be built upon the these apis with a variety of algorithms, in a way whatever suits your need. Also the main emphasis in this concept is to atleast define your key fields according to your applications requirement. Again I am not recommending this for reporting databases but truly for creating flexible config data which is relatively very very small.
I've used something similar within an Oracle DB schema supporting a SAP Portal suite of applications, however as Pratik has mentioned, it was only used for high level configuration, where there were typically only a small handful of records (i.e. 1 per landscape for Dev, QA, Pre-Prod, Prod, etc.)
I've found this approach useful where you are building completely custom solutions that evolve a bit over time but only where the config table has a limited structure, say of less than 10 fields, and the data values are relatively stable (system-wide global parameters for instance.)
End of the day, it's just another approach to storing data in tables and should be used alongside other data strategies - most important thing is to use it when it is appropriate, and not when it isn't.
aptly said 🙂