Hi Folks,
It took a while for me before I tried my hands on expert routine ( and it happened very accidentally ) and I have started loving it so much so that it is almost impossible for me, now to think BW without it.
Just to give a background,
In a transformation between any two BW objects ( like DSO, cube ), there is a option of having either a transformation with mapping rules ( 1:1, formulae, ABAP routine ) along with start routine and end routine or you could create an 'Expert Routine'.
When to go for Expert Routine
1) For complex transformations
If the transformation is more complex that you have to write a start routine, end routine and also routines in the transformation mapping, it is better to go for expert routine.
Expert routine brings all the transformation algorithms to one page, which makes it simpler for testing and debugging the transformation.
If there has to be a change in the business logic, the code can be rewritten without having to make any structural changes in the connected BW objects. This gives huge flexibility to the business warehouse.
2) When multiple sources are feeding to one data target
It is nightmarish to find out from where a particular infoobject is sourced, if the data target is populated from multiple sources.
The complexity increases further, if a same infoobject is populated from more than one source.In this case, the value held in this infobject depends on the sequence of load schedule from both the sources. Typical BW scenario! Isn't it?
If the target is a DSO, the updates from multiple sources would create huge unwanted change log.
In our case, we have used an expert routine that fetched information from all the sources and combined that in one single expert routine before populating the target.
If same infoobject has to be populated based on the values in two different sources, you do not have to rely on the scheduling of the process chains rather your expert routine will do it for you.
There is no unwanted change log, as you are hitting the target only once for given key combination.
Most importantly, you have only one place to look at erratic transformation mapping.
Should there arise a necessity to add a new source, we are just changing the expert routine and that makes it simple as very few objects are getting changed.
3) When the granularity and/or structure of the source and target are different
Say for example, you have a source and target with completely different structure and connected together based on the look up from another table/DSO, it is impossible to achieve this transformation in a single 'go' without staging this information in between.
Needless to say, expert routine helps you in achieving this in one step without staging.
You save disk space by not staging data and time by not loading the data in one more layer.
Few tips and Tricks while implementing expert routines
1) Accelerate the development effort
One general concern against expert routine is amount of code that has to be written in completing it.
In simple terms, expert routine is nothing but 'set of ABAP statements' that replaces the graphical transformation you achieve with drag and drop.
For example, the following statement will map '0plant' between source and target.
source_fields-plant = result_fields-plant.
Once you understand this simple syntax, you can apply that to all the source fields and target fields and complete the base coding required for expert routine, before adding further complexity.
We have used ‘concatenate’ formulae of excel to arrive at the final syntax for all the fields quickly at one go and copy pasted the excel output into expert routine to create first cut transformation and this style is famously known as 'excel coding' in our team.
2) Proper commenting of the code
It is important to write proper comments in your expert routine, as it would be the case with any ABAP code, else there would be a chance that you would get lost in the sea of ABAP statements.
3) Expert routine can not skip updating of any infobject
This is one genuine drawback while working with expert routine. You can not make the expert routine not to update a particular infoobject in the target object, as you would do with normal transformation by not mapping the infoobject. If the same target is populated by expert routine and normal transformation, expert routine will make the values blank, which are populated only from the other transformation.
We worked around this, by selecting the data from the targets and compared the values with incoming data before finally updating the data target in the expert routine.
4) Error Traps
It is important to pass monitor messages for any missing data and this would prove vital in understanding where things go wrong.
Also create error internal tables to trap errraneous records and this would help while debugging the code.
Overall,
Expert routine is good for
1) Complex transformations ( almost all warehouse transformations are getting complex these days )
2) Getting information from multiple sources
3) Getting the transformation to single page.
Please try your hands on expert routine and share the experience.
