Passing formula as a parameter

[bobby28]

Hello:
I am trying to implement some business rules within my ETL and the requirement is to pass a formula into the ETL upon runtime. The formula may change over time, for example we maintain a formula some where external to the ETL that defines the price - Price = x + 2.5 y and may change to Price = x +0.78y in future. Assuming that number of parameters remain the same, can we pass this formula into ETL at runtime?

What are some of the ways to handle this?

Thanks for your help.
Bobby

[kcbland]

You can't pass in expressions in the normal sense. The source code is a compiled program, so a parameter of logic can't possible be compiled

There is a possibility of using an obscure method in the OCONV function of achieving what you need. It's fairly complicated and has a whole lot of conditions behind how you can use it, I don't recommend you taking this path.

Something to consider is putting the data into a table and using a SQL script to evaluate the data content. There's really no graceful way of doing this within Server.

[chulett]

Assuming the formula is static and just that value can change, make it a job parameter. Or a single record hashed file pulled in at runtime in the Initial Value of a stage variable using something akin to 'UtilityHashLookup'. Or it can be echo'd in from a flat file. Or simply use a cached reference lookup.

As Ken notes, there really isn't any graceful way to do a truly dynamic expression / formula.

[JoshGeorge]

If you standardize your link names and use them in your formula you can pass them to your ETL jobs as parameters:

For Ex:

LinkName.x + 2.5 * LinkName.y

or

LinkName.x + 0.78 * LinkName.y

In the above case where number of parameters remain the same and just a value for transformation keep changing, you might just want to pass that value as parameter:

LinkName.x + TransformValue * LinkName.y

[kcbland]

If you standardize your link names and use them in your formula you can pass them to your ETL jobs as parameters:

For Ex:

LinkName.x + 2.5 * LinkName.y

or

LinkName.x + 0.78 * LinkName.y

In the above case where number of parameters remain the same and just a value for transformation keep changing, you might just want to pass that value as parameter:

LinkName.x + TransformValue * LinkName.y

I don't think this works.

[chulett]

I don't think so either. Passing them in from outside means they are just strings inside the job, it won't recognize them as valid link and column names.

Be glad to be proven wrong, however.

[JoshGeorge]

That appears to be ambitious attempt. Yep LinkNames theory won't work,
but the last part :

In the above case where number of parameters remain the same and just a value for transformation keep changing, you might just want to pass that value as parameter:

LinkName.x + TransformValue * LinkName.y

[chulett]

Of course, and something I'd already posted earlier.