Every data-warehouse application has its own model how to deal with the keyfigures. At least there are two
ways on how to store them:
- Every keyfigures are stored as they are in the model, which is analogous to most of the relational data-models
- Keyfigures will be mapped to an account and all values are stored in the same keyfigure
The following picture will show the structure of a sample record with classical keyfigures and a sample
record with an accounting-based structure:
The main difference between both models is the fact, that every keyfigure of the source-data will be stored
in the same keyfigure and is only identified by the corresponding account. At this point, I will stop any discussion about the advantages and disadvantages or consequences when using a specific model. This should happen at another place.
The question here is, how can you transform a keyfigure-based data record into an accounting based record? Well, with SAP Netweaver BW you will have several options:
- A transformation, build with rulesets or a lot of ABAP Coding within the startroutine
- A transformation based upon an expertroutine
- Using another tool like APD
The first and third option are possibilities - but if you see the expertroutine, you will not try to use them. The first option is 100% pure standard but hard to maintain and will not really quick transform your data. The third option is also standard, but with the price, that you will not have BW-capabilities like "delta" and you will even have not enough flexibility without ABAP coding.
I will show how to build an expertroutine, which will transform into an accounting-based model and where is not too much development effort, because a lot of logic will be hold in the customizing.
Overview
For this example, I have created two DSOs based on the flight-model example of SAP.
Source-DSO ( Keyfigure-based 😞
This DSO will store all booking informations 1:1 from the table SBOOK.
Destination-DSO ( accounting-model 😞
This DSO will have an accounting based structure and which takes transformated data from the source-DSO.
Implementation
The expertroutine will be used as transformation from the source DSO into the destination DSO. Just create an expertroutine from the source DSO to the destination DSO like in the following picture:
There will also be customizing-table, which holds all data, which is necessary to transform: source-fields,
destination-fields, unit-mapping, accounting-information. This table will be described later.
Within the expertroutine, the source_package will be processed and for every row, the mapping information
of the customizing table will be applied to the source-keyfigures.
The basic sourcecode is quite small:
The main focus is the part, where you will loop over the mapping table and create your new entries with accounting informations. Do not worry, this coding is very fast compared with an APD- or transformation ruleset. Of course, you can define all mappings statically, but in this case you will lost all flexibility and you will have to change this every time a new keyfigure will be defined or when your accounting-model changes.
This coding is on the transforming process from keyfigure-based model to a account-based model. Indeed, you will need a good encapsulation of this ABAP coding, so you can reuse and maintain it. I would propose to create a ABAP class, which will be used within the coding of the expert-routine. So you can
maintain it with the standard ABAP Workbench tools. Second, you will need to stabilize your coding, e.g. checks if your customizing is stable and checks
if your transformations will match.
Customizing
For building a flexible and enhanceable expert-routine, you will need a customizing option. For the example, I have shown here, it will be a customizing-table where a source/dest relationship, an accounting information and the necessary unit-mapping will be maintained.
A short description of these fields:
- KEYFIGURE Name of the Keyfigure-Field in the source structure
- ACCOUNT Number of the destination account
- DESTKYF Name of the destination keyfigure field ( e.g. „AMOUNT” )
- UNITFIELD Name of the field which holds the unit of the source keyfigure
- UNITCHA Name of the field which stores the unit in the destination structure
At least, just start the view-maintenance generator and you will be finished.
Example entry:
Meaning: Every value in the keyfigure „amount” of the source datarecord will be assigned to the account „1000”. The unit-relation will be mapped from „currency” to „currency”. Note: In my customizing-dialog, I have assigned a foreign key to the table DD03L restricted to the source and destination structures. With this relationship, I have a search-help, which gives me automatically the correct fieldnames - included „/BIC/” for example.
If you use this customizing option seriously, you will need at least two things:
- Searchhelps for every field, so the user cannot make too much mistakes within the configuration
- An option - ideally based on ABAP-OO Interfaces - where you can make dynamic ABAP calls for transformations
Conclusion
In this blog, I have shown, how easy it is, to create a powerful and flexible expertroutine for transformation from keyfigure-based model into an accounting-based model. And this, with out to much „black-box” coding which will only be understood by an expert developer.
By using a customizing option, there will be no code changes when a new keyfigure is introduced or a change in the accounting model will happen.
At least, you can reuse this coding, if you have different transforming steps. Therefore, you should create an ABAP-Object which do the transformation and inherit for each single use-case. This class will be instantiated within the expertroutine and performs all transformations.
- SAP Managed Tags:
1 Comment
