I believe two database setps are necessary for inbound. The first is a load into staging tables where the validation can be performed efficiently using SQL to validate primary keys against the production tables. Then after validation the staged data can be loaded into production tables.
For outbound, I would perfer a flat file input, because the extract must carry integrity. I feel its easier to reprocess an existing flat file rather than re-extract data that might have changed since the previous extract.
-----Original Message-----
From: Andrew Clifford [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 02, 2000 3:27 PM
To: [EMAIL PROTECTED]
Subject: Re: Mapping to/from Access and/or Excel
I'm interested to hear how people feel about these two approaches to EDI
integration: flat-file interface vs. direct database calls.
I would think that direct DB calls would be more efficient on outbound since
the data is presumed clean and is what you want to send to your trading
partner. Even if the data goes through a flat-file intermediate step first
before hitting the mapper, the source is the same.
On inbound, however, I think it's more fraught with peril since you risk
garbage coming into your database without an adequate opportunity to review
the data. If you add edits into the mapping to- for example- kick out
questionable data or outright errors, you'll have to constantly tweak the
maps to keep up with the inventive ways trading partners can make life
interesting. After you fix the offending data or ask for re-sends, you'll
have to reprocess the stream and perhaps keep track of duplicates.
Should you decide to change your EDI software - no one would actually do
that, would they? ;) - you may have an entirely new API to deal with.
For flat-files, I think that overhead is higher and of course batch
processing is not in "real" time.
However, if you use a flat-file approach, you will still have to deal with
edits on inbound but you'd be changing load code that you will be able to
manage through version control and code libraries.
If you then change your EDI software, you'd need only to insure that the
mapper writes out identically formatted flat-files, preserving your load
programs.
I haven't touched on all pros and cons and I'd like to hear what others
think on this. It's a fundamental issue and I wonder how you arrived at
your choice.
