Skip to Content

data movement model- showing code translation

Sep 15, 2017 at 03:49 AM


avatar image

I need to represent a simple conversion of an incoming coded column to an outgoing one in the data movement model. I tried using script execution but am not sure how to do it. For example input = columnA, output columnB. In SQL I'd do something like CASE when ColumnA = 1 then 'X', when ColumnA = 2 then 'Y', when ColumnA = 3 then 'Z'.


10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Don Parker Sep 15, 2017 at 11:44 AM

It's really looking for the method you'd use to translate the values of one column into another column. If I was to code it I'd use a mapping table or a case statement in SQL. I'm really looking how to do this in Powerdesigner. I thought a script execution looked the most appropriate but couldn't find a way to do it.

Show 4 Share
10 |10000 characters needed characters left characters exceeded

What's your end objective, to generate code to transform the data, or document what you've already got?

The Script Execution object allows you to describe the logic using SQL. If you don't need that level of detail, you could probably use a combination of business rules and extended attributes to classify the transformation tasks. I suspect somebody in PowerDesigner-land can tell you what they've done. The Data Movement Model is complex, and you need to find somebody with the right level of epxerience to help - have you asked SAP?


The objective is document what I've got, which is currently Biztalk hard coded rules. But ideally I'd like to document this in the most meaningful way in Powerdesigner, and ultimately rewrite in another way. There doesn't seem to be much help available on the syntax of a SQL script and how it is used in conjunction with the input/output flows. All I could find in the documentation for a SQL script, was

Var id = get input (“id”)

Var SSN = get input (“SSN)

SetOutput (“SSN”, SSN)

SetOutput (“NAME”, “Blue Corp”)

SetOutput (“PHONE”, “(925) 236-6000”)

SetOutput (“EMAIL”, “”)

This doesn't mean anything to me and I can't find any documentation on the syntax. So I'm a bit stuck.


I suggest you start a new thread, about reverse-engineering from Biztalk - I bet somebody's looked in to that.

On the SQL front, I think the 'Script' property is up to you. I don't think there's any validation against inut and output columns, thoug it's difficult to teel, given the complete absence of a 'how-to' guide to data movement modelling. Hmm, sounds like a good idea for a new book :)


Thanks George. Yes, I do agree, there is so little documentation that's a useful "how-to" on data movement modelling. I'll try a few scenarios out and see what works best.

George McGeachie Sep 15, 2017 at 09:32 AM

I'm not an expert on Data Movement Models, but I have an insatiable curiosity for all things PowerDesigner, so I'm trying to understand your question - perhaps someone else may be able to pin down the final answer.

For clarification - is it the script in the Script Execution object you're asking about, or how to limit the columns that are input or ouput to the Data Transformation Task?

10 |10000 characters needed characters left characters exceeded