Expert-Routine: No transformation

[marco_simon2]

Hi experts,

when defining 'normal' transformations, you've got the possiblity to define which fields of the target-structure shall be modifed or stay untoched (by using the option "No transformation"). This allows for example to load master-data attributes from several sources, while every source only needs to provide a subset of all target-fields.

I'm looking for something similar in conclusion with the expert-routine.

Is there a way to write an expert routine which only updates e.g. target-field 1,2,3,7 while 4,5,6 are not modified at all?

Please note that writing a _SPACE_ to any of the not wanted target-fields will erase the already existing value in the master-data-tables.

Hence transfering _SPACE_ into this fields is certainly not solution I'm looking for.

In other words: Is it possible to reduce the target-structure in an expert-routine without changing the target itself?

Thanks for your hints,

best regards,

Marco

[esjewett · Accepted solution]

Hi Marco,

My first answer is: Don't use expert routines. They are almost never required.

However, assuming that you have a good reason to use expert routines (I'd like to know what it is, out of curiosity), the answer is that unfortunately you are out of luck. All fields will be updated and BW doesn't provide any tools to help you. One option is going to be to do a forward-lookup on your data target (you can find it dynamically from the DTP l_r_request object, which I think is available in the expert routine context) and populate the fields that you want to populate from the target from there.

Another option is to do your expert routine between the source and an InfoSource, and then have an normal transformation from the InfoSource to the target where you only map the fields you want to update. Come to think of it, this is probably the more elegant solution.

Regards,

Ethan

[marco_simon2 · Accepted solution]

Hi ethan,

just to calm your curiositiy:

We've got a generic DataSource which does not provide the records in the usual style:

key1     attr1val     attr2val     attr3val     attr4val

key1     attr1val     attr2val     attr3val     attr4val

but in a transponated way:

key1     attrName     attrValue

key1     attrName     attrValue

key1     attrName     attrValue

key2     attrName     attrValue

key2     attrName     attrValue

...

...

Sure, we could change the Extract-Structure and the logic of the extractor - but that is not the prefered solution at the moment. Instead we'd like to transponate the structure back in the tranformation-rules between the DS and the InfoObject's attribute-table - with the problem, that the source-structure and the target-structure are quite different.

Building a single tranformation for each (of ~100 attributes)

or generating the same quantity of tranfer-rules for each target-field seems not to be very maintainable and senseful. The "do whatever you want" idea of the expert-routines seem to be matching the requirement in this case.

The InfoSource was another idea that came to my mind - but I'm trying to prevent them because it brings up some "back to the 3.5 times" feelings... Actually we try to prevent InfoSources whereever possible, even in cases where we migrate 3.5 tranformations to 7.x.

[esjewett · Accepted solution]

Hi Marco,

Thanks for the explanation. Column-to-row transposition is exactly one of the scenarios where I would usually want to use an expert routine as well. So good to know that this is a scenario where there isn't really another option.

I'm a little confused about why you're trying to avoid InfoSources. I find them one of the most useful constructs in BW 7.x. They serve quite a different and more general purpose than in 3.x. They can actually behave basically like a write-optimized DSO without persistence, which can be very useful for making transformation logic reusable. You might want to give them a chance.

In any case, it sounds like you've got some ideas now. Good luck with addressing the problem. If you come up with other approaches, I'd be very interested to know what they are as well.

Cheers,

Ethan

[Former Member]

Marco,

Just create an 7.0 infosource between the datasource and your expert routine and then a normal transformation (just mapping what you need to update) from the infosource to the infoobject.

Please assign points if useful.

[Former Member]

Hi Marco,

It's very well possible in expert routines too.

We are using expert routines, in which the transformation mappings are maintained in a table.

This table entry contains mappings which needs to be mapped and we refer this table for mappings for every transformation for the source/target.

Br,

H

[marco_simon2]

Hi Harish,

could you pass some more information about that, please?

How do you define which InfoObjects of the target-structure shall be updated?

I can neither see a possiblity to modify the target-structure (  _ty_s_TG_1 seems to be predefined and contains all InfoObjekcts of the data-target), nor a way to tell the framework that InfoObject "xyz" should be ignored.

Thanks in advance.

Marco

[Former Member]

While you assign the data in the fields you want in the target structure in the end of the loop

It wont update any which you dint assign In other words it will retain its original value.

Cheers!

[marco_simon2]

Hi Suyash,

just to be sure that we do not missunderstand each other, let's become a little bit more concrete (using the attributes of 0material).

The DS contains (exactly) the following fields:

MATERIAL

/BIC/ZKIBCTFDA

BASE_UOM

The target of the tranformation (=attributes of 0material) contains the following fields (subset):

MATERIAL

BASE_UOM

CREATEDON

/BIC/ZKIBCTFDA

(so there's at least the field "createdon" which is contained in the target-structure but not in the source-structure)

The implementation of the (sample) expert-routine looks as follows:

     loop at SOURCE_PACKAGE ASSIGNING <SOURCE_FIELDS>.
     clear RESULT_FIELDS.
     RESULT_FIELDS-material = <SOURCE_FIELDS>-material.
     RESULT_FIELDS-base_uom = 'LE'.
     RESULT_FIELDS-/BIC/ZKIBCTFDA = <SOURCE_FIELDS>-/BIC/ZKIBCTFDA.
     append RESULT_FIELDS to RESULT_PACKAGE.
   endloop.

I'm 100% agree with you that in this case the field "changedon" in the result-table will not be changed - it keeps it's initial value.

But: It unfortunately (and this is what I'm after) this inital value is being written to the database (=0material-attributes-table).

Before running the transformation I found the following values in the 0material-attr.-table (for a particular material):

MATERIAL = '000000000000000001'

BASE_UOM = 'ST'

CREATEDON = '10.01.2007'

/BIC/ZKIBCTFDA = ''

After the update the follwing figures are persisted:

MATERIAL = '000000000000000001'

BASE_UOM = 'LE'

CREATEDON = ''

/BIC/ZKIBCTFDA = 'X'

As you can see, the createdon attribute got initialized...

So my question is still: How can I prevent unwanted attributes (like createdon) to be updated by the expert-routine.

Without the expert-routine I easily would not define a rule for that field (="No tranformation") - but how is that done using expert-routines?

Best regards,

  Marco

[Former Member]

As Ethan said I agree with the use of expert routine only when the scenario is complex for example if you doing a cost allocation or PA kind of scenario in BW.

I have a suggestion here just to innovate(might not be the best approach but gets the work done).

As you not updating created on it makes sense that system assumes it has null value for a new record coming in and during the change run it overwrites the previous value stored assuming its changed to null.

So now the solution:

Read the master data table(0material-attr.-table (for a particular material):) in the code and update the created on with the value found with material as key

So you have a new record with existing date and not null as previous.

Hope this helps

Cheers!