DSXchange

Is it advisable to put all the column manipulation in the stage variables?

By doing these, it is easier for me to keep track of all the derivation. Else, I need to scroll down the transformer for manipulated derivation. A bit lazy I am..

However, I am a bit concern on the impact on the performance.
Can anyone advise on the best design.

Hello Christina,

> Is it advisable to put all the column manipulation in the stage variables?

In general its not a bad idea. I've seen customers doing so. There is, of course, some overhead as all this stage variables have to be maintained.

My personal preference is to put "some" of the more complex transformations into stage variables, and, of course, all transformations that will be reused in more than one output link.

I personally find it more usable to have the transformations there we're I'm looking for them: in the specific output link. And not in one long list of stage variables.

However, it may be my individual preference...

Klaus

It definitely does no harm.

I always use stage variables if there's any likelihood that that particular evaluation may need to occur more than once - for example in multiple output columns and/or links, or in output constraint and output column derivation.

The only place I'd advise against using a stage variable is where an output column derivation is "straight through", direct from input with no transformation. If you inspect the Transfomer stage's generated code you will see that this is handled in a very efficient way, with zero processing, by linking the references to the same variable. Introducing a stage variable would introduce two assignments; in themselves fairly efficient, but never as efficient as doing nothing.

I like to put ALL constraints in Stage variables too. Sometmes it makes them more readable but the biggest reason is that you can document a stage variable in its description. You can describe in plain language exactly what the stage variable and hence the constraint is doing. Also if named properly the stage variable itself can be self documenting.
For example a stage variable -- svIsAscentialCustomer instead of a constraint of DSLINK1.CustType = 'A'