SAP BW transformation expert routines best practices

[dsakq]

Hello,

recently I was wondering if there are any best practices for designing an expert routine within a transformation in SAP BW. We've decided to use a central class for all transformations and call the corresponding method within the expert routine. This allows us to change the source code of the expert routine without having to reactivate the transformation and DTP again after changes. It also brings all features of ABAP oop. Speaking of ABAP oop I asked myself if it was better to use static methods for these transformation routines instead of instance methods. So far we're creating an instance of the class in the expert routine. But doesn't this affect the runtime if a new object has to be created whenever a new data package is running during the transformation? Please let me know what you're thinking of this. I've learned to use instance methods by default but right now this doesn't make too much sense for me anymore. Are there best practices?

Thanks.

[FCI]

Hi David,

I've never used expert routine on BW so I guess we can live without. But I understand what you're trying to do and it may make sense in your context.

I have some doubts on this architecture on the long term by going so far from the SAP recommendation ("This type of routine is only intended for use in special cases. You can use the expert routine if the other transformation functions are not sufficient.").

You don't mention if you are on a Hana DB but if you are (if not you will probably be at one time), the expert routine will forbid any pushdown of the transformation into the DB. Seems counterproductive to me.

Also, if one day you have to change this approach for any reason (new BW version, new DB, new needs ...) it will be a hell of a job 😉

Regards,

Frédéric

[MKreitlein]

Hello David,

to be honest, I myself always tried to avoid Expert Routines because I'm a "copy&paste" programmer ... and usually startroutine or endroutine did their job as well.

I'm asking myself what exactly you put into your class, since I can't imagine what to reuse in several dataflows, which require an expert routine?!

However, bundling every method you need in all your dataflows, like determining additional characteristics from master data or similar things, like currency/unit conversion etc. makes a lot of sense to put the coding only in one place and then call it from any TRFN within your whole BW system.

Btw. the easiest thing to re-use routine coding in dataflows of logically partitioned objects, like e.g. separate DSOs for data from American/Asian/Europe source systems, but with same logic, is to create the routine once as an Abap INCLUDE and then re-use the same in all the TRFNs of the corresponding dataflows.

Btw., nowadays the best practices for an expert routine is an AMDP script, since you don't select the data into the application server, but it is transformed directly on the HANA database:

See e.g. https://blogs.sap.com/2016/05/24/hana-based-bw-transformation/

Regards, Martin

[former_member278313]

Hi dsakq,

in case you have InfoObjects / fields only on the left (source) and not on the right (target) there are two ways how I get around expert routines.

a) With Infosets you can use a virtual layer and you have access to all InfoObjects / fields.

b) Load the data from the source package in the start routine into internal tables (global data). Simple and works.

c) Just map the source fields you need into the target as well. Of course this is not a perfect design in regards to saving data. Unless you don't have billions lines of data or 100 of InfoObjects / fields, the additional data doesn't outweigh the benefits: 1. The easier design - not using Infosets or expert routines. 2. Being able to see the source fields or the input fields of a given transform rule.

So far I have been successful in avoiding expert routines and did not come across one scenario where expert routines are the only way. I came across some expert routines and they can be nasty. No one really wants to touch them.

My 2 cents 🙂

Best regards,

Corvin

[dsakq]

Thanks for the answers. The reason I'm using expert routines is that often times I have InfoObjects on the right side of the transformation that need to be derived from the left InfoObjects in a complex logic (complex mappings for dimensions). So to me it usually makes no sense to use start- or end-routines instead.

As I'm sourcing out the actual logic to a class, I avoid having to activate the transformations and the DTP all over again whenever I change something.