A colleague was asking me to explain a system information flow, as they are having problems with the wrong information being presented. It seems that what they have created is a custom built website, with it's own database, which gets it's data from SAP via a JSON based dataset.

SAP is the live information, the custom built solution enables other information to be captured, thereby enriching the SAP data. This is stored locally in the custom site's local database, to produce monthly reports.

I'm trying to wrap my head round why a JSON dataset is being used, rather than a direct (SQL?) query type approach. If the goal is to get live information from SAP on a monthly basis, why not just query SAP, rather than construct a static JSON payload, and have to parse it. Now it seems the argument is whether the JSON wrongly created, or wrongly interpreted... it just seems so much more complicated.

Since the JSON file is done every night too, it's not obvious to me why this particular design has been chosen.

  • What was their explanation when you asked them? Commented Feb 11, 2016 at 20:31
  • 1
    Ah, the politics. Lets just say that "IT / development" in this business is a very sensitive subject and the people who have designed and created it are not accessible to ask that question. Baffling, but true.
    – Maxcot
    Commented Feb 11, 2016 at 20:34
  • About the only thing I could think of is "security" - by preventing an external source accessing the SAP service directly, you can't have someone break into the SQL port and issue a DELETE statement... It's a bit of a naive solution, but there you go.
    – HorusKol
    Commented Feb 12, 2016 at 2:51

2 Answers 2


In the days before ORMs really took off, such an approach was reasonably common. That is: you have a database/programming language agnostic interface such as JSON/XML between the two so (in theory) you could swap out one or the other. Another benefit is that the interface between the two can remain static even if say, a stored procedure changes. You would of course have to change either end, but you'd rarely have to tweak the middleware since it just returned or passed thru a schema or such like.

This is all good in itself but as you've seen, each representation has its own foibles. We encountered a similar problem with one of our systems using a XML payload. It worked fine when the data was fully populated, but would fall over when one of the values was NULL since it wouldn't encode this as an XML node meaning we'd have to supply default values either in the returned data or in the tables themselves.

  • "...In the days before ORM..." The custom site was designed and built Nov 2015! But I take your point that every approach has it's foibles.
    – Maxcot
    Commented Feb 11, 2016 at 21:40
  • I fail to understand what an ORM has to do with the question. You wouldn't want different applications accessing the same database through various ORMs, would you? The concept of "databases as integration platform" is dead, and for good.
    – vwegert
    Commented Feb 12, 2016 at 8:57
  • @vwegert It doesn't. It just happens to be an approach that has largely superseded the platform agnostic data set approach that was prevalent in the past.
    – Robbie Dee
    Commented Feb 12, 2016 at 9:24

It's hard to say without further information, but it is very likely that it has nothing to do with any "in the days before ORM" scheme. It's likely that someone had to find a compromise between "what the SAP platform (i. e. release) is capable of producing", "what the SAP developers are capable of implementing" and "what the custom system developers are capable of handling". Using JSON (or XML) isn't a bad choice - given the complexity of the average SAP-based solution, you wouldn't get far without additional processing above the SQL layer anyway.

As for the "nightly" approach, that's not uncommon either. Depending on the amount of information that has to be processed and the complexity of processing involved, it might easily take minutes or hours to produce the output - easily longer than the average browser output. In theory, that shouldn't happen, in practice, it happens all the time. Scheduling expensive data extraction processes to run during the night when nobody will be bothered by the added load is fairly easy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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