2

We are a small team doing work on a LoB system that needs to connect to varied systems such as ERPs and CRMs to extract business processes like invoices, customer info, production orders and the such, for internal use of the application, and then return some specific result to the ERP. This is a real-time operation, not a one-off data extraction. All the business logic is properly shielded from the data layer.

The thing is, the data we extract from those systems is always the same, but the systems change a lot depending on the customer, and so do their sources/tables/fields from where the data is extracted. We have seen dozens of ERPs to date, from SAP/Dynamics to a lot of small ones, and every time we make a new installation there's code to be done so that our system knows where/how to extract the data it requires. As I said, most are small or in-house ERP systems so it ends up being a one-off library that we can't reuse on the next customer.

We want to improve this, ideally to a setup where instead of data code, there is configuration where the ERP structures are mapped to our structures for each installation, but just thinking about it seems like a lot of work.

We thought of ETL, but it isn't real-time. REST is also not an option since it still would require programming for each installation.

Is there an existing universal database/datasource mapper/translator to solve this kind of problem? Or standard pattern to use for developing this kind of thing?

  • It sounds like you want / need a wrapper layer to interface with the individual data sources and then transform that into a common set of objects your application can use. – GlenH7 Feb 6 '16 at 18:52
  • @GlenH7 I don't think that's what he is looking for. He is looking for a mechanism which is able to understand the current and also upcoming sources of data, so that he does not have to write a handler for every new source, not for a layer to abstract the source. – Andy Feb 6 '16 at 19:02
  • 1
    You are basically asking for an AI that will do programmer's work. So no, this doesn't exist. Yet. – Euphoric Feb 6 '16 at 20:10
  • @GlenH7 that's the mechanism we currently have, our data layer has libraries that "know" how to extract data from each system and convert it to our common objects.... but that has to be written for each new system, and we would like to try and automate that step. – KalaNag Feb 8 '16 at 1:27
  • @DavidPacker specifically I want to write code ONLY for handling databases/services, not specific tables and fields. I want that part of the data layer to be configurable, not coded (as in, someone configures from what fields the data comes instead of having to code it). – KalaNag Feb 8 '16 at 1:40
1

I saw an interesting talk last year with a couple related papers about using a technique called programming by example on your sort of problem. They had impressive results on spreadsheets, and the technique could probably be adapted to database tables and REST interfaces.

The basic idea is the user highlights some examples of the data they wish to extract, the engine infers some general rules from those examples, the user provides feedback of examples where the rules went wrong, and the engine uses that feedback to refine the rules. The feedback loop continues until the user is satisfied with the output.

Unfortunately, it's hardly a drop-in solution, at least for now. It's the kind of thing only a company like Microsoft can afford on a full-scale, but maybe you can adopt some of the principles on a more limited scope, or watching the talk might spark an idea of your own.

  • Thank you, that sounds interesting. I will check the links you provided. – KalaNag Feb 4 '16 at 14:31

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.