3

Here is a high level outline of the project:

  • We frequently need to convert data from a new incoming system to our in house system (sort of a basic ETL process)
  • We would prefer to do this dynamically, allowing an analyst to use a GUI to map source fields to target fields and define basic transformations
  • The basic flow will be as follows:
    1. Client provides flat files with source data
    2. Program dumps these flat files into a SQL Server database (no transformations)
    3. User maps data and defines transformations in a web-based GUI
    4. GUI calls web services that perform the transformations and generate new files in the target system format

My responsibility will mainly be the services mentioned in step #4.

My question is: does anyone have any suggestions on best practices for designing an application like this? My initial thought was to to have the services accept objects that represent a business object (say an Account), and all the fields and mapping/transformation info for those fields.

Really rough example:

//BusinessObject --> Field

//Example: Account

{
    fields:[

    {
        targetFields: ["NUMBER"],
        sourceFields: ["AccountNum"],
        transformations:
        [
            {
                type:"trimspaces",
                expression:""
            },

            {
                type: "format",
                expression: "%03d"
            }
        ] 
    },

    {
        targetFields: ["NAME"] //etc...
    }

    ]

}

In this example, a target field belonging to the Account object called "NUMBER" would be populated with the source field "AccountNum" with two transformations applied (trim spaces and pad with 3 leading zeros).

The user would have a finite number of transformation options to choose from, so this would make back end coding a little easier.

I am also thinking that the transformations would each their own sub-classes of a Transformation super-class so as to avoid having to do special logic for each one. Each sub-class would just implement a common interface. I have only used the "type" attribute here for example purposes.

Does this approach make sense at all for the API side? Or am I totally off with this?

Any suggestions would be appreciated!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.