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.

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

Any suggestions would be appreciated!

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!