Why isn't EDI easier? Part I - One Standard to Rule Them All

EDI really should be less difficult than it is. If I am a start-up company and I want to purchase timber, metal, paper, widgets or some other commodity item, EDI is just too hard.

The overhead on establishing a relationship, agreeing a standard, and testing communications is huge. EDI is supposed to reduce cost but if it results in a supplier tie-in then it will produce unwelcome influences.

If it takes a department of expensive-to-employ / prickerly-to-manage IT geeks to support, then this "cost saving" thing called EDI, just got too expensive.

The first thing that springs to most peoples mind is Standards. How may ways do we need to represent a Purchase Order? Why are there many Standards? Wouldn't it be easier to just all agree on one standard? The answer to the last question is, not necessarily.

To explain this, consider the classic example of Purchase Order Delivery date. On a multi-line purchase order, a customer will probably want all the items delivering together. But while some customers will send a delivery date in the order header section of the message, some will send delivery dates in the order line sections. Some will send both. Some will have differing dates in the detail line section. Some customers will omit dates altogether indicating the goods are required ASAP.

Don't stop me, I'm on a roll...

Sometimes it will be appropriate to send an Earliest and a Latest date range. This could be in either section. Given a free text field some will write 9/11/01, some will write 11/9/2001 and some computers will generate 20010911. By the way, not everyone in the world agrees what year it is or how many months there are.

At the other extreme, timing might be important. For example when ordering services like an aircraft flight time, or an insurance period start and finish. This brings in the question of time zones and daylight saving adjustments.

• So if a Standard is to be universal, it has to be large and complex.

• But if it is large and complex, it won't be easy to implement.

• And your next partner will use the Standard in a way that is at least slightly different to all your others.

When you read "X12 is popular in North America, EDIFACT in Europe, TRADACOM in Britain" remember, just agreeing one Standard doesn't solve anything. One Standard used in two different ways is like two Standards. So the incentive to converge isn't there.

I have been watching the ODF v OOXML Standards dust up with interest. The difference with EDI is that an EDI Standard has many thousands of individual implementations. If we accept Standards overlapping as much as ODF/OOXML damage each other. If interpretation of the specification will lead to differing implementations, which will lead to interchange problems between office applications. Then we shouldn't be surprised if EDI is in trouble.

I think this is the real source of the excitement over XML. It gives structure to data even without a standard. If we accept that even with a standard, there is a need for a "mapping" function, then maybe we should aim to make this as easy as possible.

When did XML become a good Idea?

It is so easy to list what is wrong with XML for EDI.

• It produces large files
• It is very processor intensive to parse
• Needs another new standard
• Or can be used/abused without a standard
• It isn't even very human readable (despite what some say)

And yet... a lot of people find it very compelling.

To understand why, imagine you are talking to a developer who knows nothing about EDI. Show them an EbizXML file, an EDIFACT file, a Tradacom file and a X12 file. All for the same document. The chances are they will immediately be able to tell you the first file is XML as they know what that is. They will probably even be able to begin to translate its contents.

Explain to this developer you want a subroutine / function / method, to extract and return certain elements from the document. They will not blink as they reach to the keyboard and fire up their perfered language tools. They will have a first draft ready in less time than it will take to explain the mere structure of any of the other files.

Any non-developer who has just read this is now scatching their head wondering why file structures would have to be explained. They are also wondering why the problems listed at the beginning are... problems?

EDI System Design Part - Overview

Part I - Message Control
Part II - Export Mapping
Part III - Import Mapping
Part IV - Transaction Processing

EDI Overview

Part I - Formats
Part II - Communication
Part III - Why

EDI System Design Part IV - Transaction Processing

I have mentioned before that this is the "Elephant in the room" no one likes to talk about. It is hard to talk in abstract terms about something so involved so I am going to take a specific example and use that of a customer sending their Purchase Order. We would want to turn this into a Sales Order.

So we get hold of a copy of the message format definition (Edifact, X12, Tradacom etc.), blow off the dust and start to study it. It might have, say 100 fields - Gulp! Then we look at our Sales Order database tables. These might have 500 fields - Double Gulp! How can we possibly map or translate from one to the other? The answer is we don't, you can't possibly. We have to stop thinking in terms of messages and documents, and think in terms of process.

When our company recieves an order in the mail or by fax, it gets typed into our ERP system. The user does not typically type hundreds of fields. The system should be optimised for speedy entry. A quick list of things that might be entered,

customer account, purchase order reference, delivery address, product code, product quantity, unit of measure, price, delivery date... eh... I've started to run out already... that is only 8.

Then why all those fields on the database tables? Well they do get updated, but they don't all get entered. For example, one of the fields might be Payment Terms, but we don't re-enter it on every order. It gets defaulted. Probably to a value stored on the customer database table. We might have the option of amending it when we type in an order but we lose that option for EDI orders. It doesn't mean it can't be amended sometime after the order was raised. It doesn't mean we can't introduce a rule to flex the setting based on various parameters. The point is we have to remove the human element and we certainly aren't going to let a customer enter the Payment Terms.

We do the analysis of what fields are actually entered. The Import Mapping routine finds where they are in the EDI message, and has stored them in an "pending" database table that looks like a very trimmed down version of the sales order table.

There are some fields that the customer might find problematic to send. Typically our codes for things like unit of measure. We might use EACH, PACK, M, KG. They might use ONE, BOX, MTR, KGS. This is where a universal standard would be useful, but there is no such thing. So we have to set up look-up tables to translate the standards codes or customers codes to ours. Codes are a large subject, but in order not to get bogged down I will just mention that the subject includes, among others, customers branch codes and product codes.

Our processing function will take each input in turn and apply the same field defaulting and validations as our order input screen. If any field fails input validation, the order must be abandoned. At the end we must perform the same update process.

To do this we don't want to re-write the whole order input process. We want to re-use the existing code. We have to hope we have an object-oriented system with methods to call, or structured programming with functions, or at the very least re-useable subroutines. If we have spaghetti code, or no access to source code at all, then we are in trouble.

Any way, when this routine has finished processing an order (wether it was sucessfull or not), we move the "pending" database object to a "processed" or "archived" table. We add any successfull sales order ids or unsucessfull error messages (such as "Order XXXX, Line XXXX : Failed to validate item code XXXX"), so we can produce an audit report. We might go on to develop an edit screen to amend failed order messages and allow them to be unarchieved and processed again.

Most important of all is the entry of price and delivery date. If our existing system allows free entry of these fields, then we need to tighten up it up. We can't allow the customer to enter whatever value they like and anyway, some customers might not send any price or date. We certainly aren't going to send it to them for free! What is a good idea, is to accept any values higher than our system default. If it is less, replace it with our minimum and add a message to the audit trail like "Order XXXX, Line XXXX : Price 11.11 requested, 99.99 used".

One final idea. If the system doesn't already check the customers purchase order reference to make sure that the order isn't a repeat that we have already processed, then we need to add it. I have had batches of hundreds of messages sent twice. I have had customers who insist on sending hard copies in the post and the sales order entry people enter them a second time!

EDI System Design Part III - Import Mapping

OK, I know I've missed something out. Between EDI Exporting and EDI Importing is Communicating. But to understand that, it's best to first have an understanding of both the inputs and outputs. Besides, it's my Blog.
What are we going to do with these EDI messages? Possibilities include,

• Print them out in human readable form
• Covert them into an email alert
• Store them for querying and reporting
• Store them for transaction processing

The first option of printing the message out is surprisingly common. I hope you can spot what is wrong with this approach. You see this sort of configuration has a technical name. It is called a Facsimile machine (or a Fax for short). Joking apart, it would be a shame to have spent a large effort of time and resources to discover we have re-invented the Fax machine (and an expensive one at that).

Now it should be obvious what is wrong with the second approach. Our trading partner can send email alerts and cut out the "EDI" system.
Querying and reporting is fine for some types of data. For example if a customer is updating us on EPOS sales data from their branches. We would probably want to run various analysis reports, maybe comparing against forecast, or projecting future stock replenishment orders. This means getting the information into our database. For most this means SQL compatible tables.

If the message is transactional in nature, and we want to action a transaction on our ERP system, then there are also sound reasons for storing the message in our database first. I don't want to go into too much detail (see Part IV), but one reason is you may have transactions sourced from many different EDI message types/standards, and our database table is a good linga franca to pass to the transaction routine.

Another reason is, we should be phasing in EDI software functionality with little updates often, rather than a long wait, followed by a big bang implementation. We can implement a "glorified fax machine", running reports from our database tables, first. Then move to a more functioning system later. I know I ridiculed this arrangement early on, but the point is this should be a transitional stage. We don't want to leave things like this for long.

So our EDI Import Mapping routine will take each message and loop through each segment updating our object and maybe a list of object details. When we reach a message header marker we write the object to the database. Different message types of the same encoding standard will update different object tables but it is worth all these routines updating an "Envelope" table with message meta data, and putting the id field on the object table. This will enable us at a later date to translate various partner identity codes (products, branches etc.) through a look up with a sender identity.

Once these updates have been done we can delete the EDI message from the "pending" folder and move it to an "archive" folder. There will be times when you get unexpected output (or no output) and we will want to check what our trading partner sent in the raw.

Is this what hm.gov.uk thinks is EDI?

Electronic Data transfer in the news. I think it is called pigeon protocol.

No, you can't ridicule it because it is too serious. Unfortunately I suspect this "behind the mailbox" infrastructure is all too common.

I hope the little I.T. guy archives his email.

EDI System Design Part II - Export Mapping

I'm not going to go into the details of creating the text of EDI messages in certain formats. Well not in this post anyway. It is a big subject and the last post was long enough. What I am trying to do is lay out the System Design and APIs.


So you have a routine in your ERP system that created a document or a transaction e.g. a Sales Invoice. The options you have are,

1. Edit the routine to - Check EDI control. Pass the document to a export mapping routine. Store the returned EDI message in a folder for later batched communication.


2. Edit the routine to - Check EDI control. Store the document reference in a folder for later batched export mapping and communication.


3. Create an external routine to - Periodically select all unchecked documents. Check EDI control. Pass the document to a export mapping routine and communicate.

If you don't have access to your existing source code then your only option is 3. If you do have access to the source code you may still want to do option 3. There is less chance of introducing error and, in these days of Sarbanes-Oxley, the administrative work in getting changes approved, in something like an invoicing routine, must be considered. It doesn't mean you can't move to option 1 or 2 at a later date.


For traditional EDI formats (e.g. EDIFACT, TRADACOM etc.) there will be sections of messages that are common to several documents. Consider the senders address. For each document, this will probably be read in from the same place. The format may say addresses must be presented in a certain way. The first line of the address may be limited to a fixed number of characters but our system allows longer lines. We can't chop off the end of lines, so we will have to move the excess to the second line. This isn't that difficult a task but we don't really want to have to type out this code several times, testing and debugging each one. And if a format version change means a code change is required, then a small task will have just turned into a code hunt.

For all these reasons we want a single export mapping routine per format. Things like, senders address, can then be a single local function or subroutine. One of the calling parameters is document type. The next calling parameter will be a list of documents objects, or pointers to documents - probably the key fields in our SQL table. The returning parameter will be a list of formatted EDI documents. The calling program can then take care of writing to the pending folder or passing them to the communication routine.

There is one other less obvious parameter needed by the export mapping routine and that is the Communication Type data. Even though we have designed a system to separate mapping form communication, this flag is still needed. The reason is envelopes and addressing. Especially in traditional EDI formats, the message document is not complete without an envelope wrapper and we will need the communication type.


Part I

EDI System Design Part I - Message Control

Assuming you have an ERP system that processes sales orders, invoices, remittances, stock balances etc. how do add EDI capabilities? The first thing we need is to decide what is going to whom, in what format, by what means.

Let us suppose we want to start sending Sales Invoices to a key customer. The routine that will create the EDI invoice could be called from the ERP routine that does the invoicing, or it could be called from a periodic routine run sometime after. We will need a EDI Sales Invoice Control screen.
Customer:
Message Format:
Protocol:

Suppose we want to start sending Order Acknowledgements. We would have to create a similar screen. To avoid this, we add a Document field. Suppose the standards body releases a latest version of the message format. Some of our partners start to use it. Some stick with the old version. We could add a Format Version field or incorporate the version in the format.
Document:
Customer:
Message Format:
Message Format Version:
Protocol:

Some customers will have multiple branches or delivery points. I warn you from experience, there is a high probability that customer A only has half their branches EDI enabled, while customer B will have all branches enabled. So customer can't be the group account and must be the branch account.

Now a key supplier want us to send our purchase orders, so we add a Supplier field. One of, Customer or Supplier, must be entered. Next the taxman wants our sales tax information. Is he a customer or a supplier? Neither really. With luck you will have a Contacts file that incorporates all these entities and you can use that identity. Otherwise we need a Contact Type field (answer customer/supplier/third party) and Contact ID.
Document:
Contact Type:
Contact ID:
Message Format:
Message Format Version:
Protocol:

Some Document, Format and Protocol combinations will be compatible with immediate delivery e.g. sending purchase orders by email PDF attachment. However some formats are designed for batching up several messages into one. Also a partner might want a steady trickle of messages through the day and prefer one combined message say, once a day or once a week. If you are communicating by Dial-up (facsimile or point-to-point) there will be savings if messages can be combined. To achieve this we need to add a Batched Y/N option. If 'Yes' then the document ID is placed in a pending file to be translated and communicated at a later time.

At last we come to Protocol. We can't just store FTP, HTTP, SMTP, P2P etc. in each case we require more information e.g. URL, user name, password, phone number, alternative phone number. When you have a partner with lots of branches but central communication, this would mean a lot of repeated set up. It would also make batching very difficult because we would have to check every field.

The solution is another level of abstraction. We create a concept of Communication Type and this points to another screen where the additional fields are stored. Then, when Acme Widgets have all their stores documents going through one VAN mailbox, we only have to store the Communication Type identity.

So finally we have...
Document:
Contact Type:
Contact ID:
Message Format:
Message Format Version:
Batched? Y/N:
Communication Type:

Update - I was trying to keep this post as short as possible, but it just keeps on growing. I realised I missed another field. There some other pieces of information that you might need. Examples are where a customer wants EDI invoices but only for orders raised by EDI. Or the customer prohibits amendments to EDI sourced orders. Or the Trading Partner requests special formatting of a reference used in the message. There could be a need for several flags for minor functionality. Rather than clutter this screen with more fields, I tend to add a generic "Options" list on a sub screen.

Part II

EDI Assumptions

I was starting to sketch out my next post. It was going to be on EDI System design and I started with "Assuming you have an ERP system that processes sales orders, invoices, remittances, stock balances etc. how do add EDI capabilities?"

Then I thought wait!!! Important EDI life experience lesson - assume nothing!!!

EDI has a wide reach. It is used in all sorts of areas. While I am most familiar with EDI in ERP systems, it is also used in health care, logistics, military etc. So I thought I'd better at least provide a link to explain the term. This got me thinking about the different types of people who might be interested in these posts, what your situation is and how you came to EDI. And this brought to mind 2 experiences that challenged contrasting assumptions.

1) Assuming Everyone is Big

After many years of being familiar with ERP systems running businesses that employ many people, I had a shock. I came across a software package being used to run a small family service business. It was the sort of thing you can buy off the shelf in PCWorld. It was one of those friend-of-a-friend, help desk situations. I took the opportunity to familiarise myself with it, always looking to learn something new.

My shock came from the fact it had an invoicing function but no orders or quotes. It could purchase but had no stock control. Most of the functionality I thought was needed was missing. How could they run their company with this? The answer was, quite well thank you. You see they did have an order book but the number of orders you could count on your fingers. High value, but low volume. They didn't have a product line, no two jobs were the same. They would produce whatever the customer wanted. If they wanted to know what material stock they had, they looked in the shed.

It made me think. Could I configure my current, all singing, all dancing ERP system to run a similar company? Would it be as easy to use? If they experienced rapid growth, at what point would they need additional functionality?

What if they got an important contract from a big customer who wanted to send orders by EDI and receive invoices by EDI? How would they cope? How would they benefit?

2) Assuming Everyone is Small

Three years later I am summoned to a meeting with our then biggest customer, a major retailer. Lots of suppliers are there as they announce their Electronic Trading imitative. They have done their research and decided that past EDI initiatives have failed because they were too technically complicated and costly for all their small suppliers. They had a solution and from the audience response it went down well.

All suppliers needed was a browser and an Internet connection. They could log on to a website, print off PDF's of orders, confirm acceptance, record supplier order references, alter pricing, update delivery dates, confirm deliveries and even read the latest news from our important customer.
The small suppliers, who had feared warnings of "integrate or die", loved it. Suddenly I could see this was the solution for the small company I had experienced earlier.

The big suppliers sighed. How were we going to explain? You see we already did all this inputting on our computer systems. To repeat it on a web page would dramatically increase our workload. We organised hundreds of deliveries a week to hundreds of stores. Confirming a full order delivery was 1 click, detailing a partial delivery (or split delivery) was click, type, click, type, click, type... Unless we could persuade them to add another option, we were going to have to throw human resource at the problem.

Meeting after meeting followed.They eventually offered an FTP connection, but although most suppliers already outputted information in one of two EDI formats, the customer wouldn't accept them. They invented their own format. Every time we requested more time, we were told we could go live with the browser interface.

After we did go live, the customer provided a telephone help line. When we called we would get nonsense questions about what we had clicked on the web page.

Me: [Sigh, keep calm] We don't use the web page. We communicate server-to-server.

Operator: Eh... I'll just speak to my supervisor.

Lessons Learnt?

Trading partners have differing levels of ability.

Trading partners have many varied business styles and processes.

When volume splits 80/20 and problems split 20/80, 80 x 20 = 20 x 80 (if you see what I mean). Both half's are important. Don't design a solution to one half without consideration of the other.

Maybe Mashups are the future.

Story from the Trenches

I'm working at this company. No one wanted to be responsible for EDI. It was seen as a problem (not a solution), so it gets dumped on the new guy - me.

With a lot of patience and time I slowly train myself and begin to understand. I had inherited a third party communication and translation software. It used 1 of only 2 modems in the entire company - it was along time ago. Users hated it. Apart from processing everything twice, it kept breaking down.

Time passes and I slowly improve the system and increase reliability. More customers start to use EDI. We had several customers connected by EDI. 2 different standards. 2 different networks. They were sending us Sales Orders and we were sending the Invoices. Hey! I was proud of what I achieved and everyone else (who didn't want to touch EDI in the first place) saw me as an expert.

Then one day a key supplier suggested we send our Purchase Orders to them by EDI. Hmm EDI with a supplier, not a customer. This was new. So I ring up my contact at our EDI software supplier. I know I need to get the trading partner/document relationship validated on the VAN. Oh, and the software will need some mapping tables for purchase orders. I know they are going to cost.

Me: Can I buy the translation tables for Purchase Orders please?

Contact: According to our records you purchased them some time ago. See your reference XXXXXXX

Me: Let me see (my files are organised). Ah yes, I have it in front of me. No, that was for Sales Orders. I want Purchase Orders.

Contact: What's the difference?

Me: Duh! I see what you mean. Never mind. Click

Overview Part III - Why EDI? (or, What to do with EDI Messages)

How is all this beneficial? How is it better than faxing or emailing a word attachment? In a human sense it isn't. The EDI formats are often not human readable (e.g. Edifact, Tradacom). The point is, it is meant to be machine readable.

Eh? So what? These days we have grown used to unstructured information but it wasn't always the way. The triumph of HTML (and the Internet) is the organising of unstructured information. This brings us on the often over-looked EDI 5th element. The elephant in the room no-one talks about. I left it off the list in Part I because I didn't want to scare you.

So what most people were, and are, doing was putting these messages into an database. For most people this means an RDBMS. For most this means SQL. It is a relatively easy task to design a database schema for a particular type of message (e.g. stock balance, planned delivery etc.) in a particular format (e.g. Edifact, Tradacom). But remember you will have many customers using their own choice of format and their own interpretation of the standard. They all need to go into the same schema.

Other EDI guides will tell you EDI is all about accuracy. Reducing typing errors. This is true. You will now have in your RDBMS an accurate representation of the message. So what are you going to do with it? Report it? Print it out? Change it from electronic data to hard copy? You will be surprised how common this is because if you do, as far as your customer (eh... I mean trading partner) is concerned, you will appear to be "EDI Enabled". You will have ticked the boxes.

EDI becomes really useful when the message is a transaction. If your customer sends you a purchase order and you can create a sales order on your business computer system (sales order / ERP system), without any typing, then there are opportunities for labour cost reduction. So EDI is very popular in high volume situations, like supermarket supply chain.

Unfortunately a transaction is more than just a message or a schema. What if the customer orders a product you don't recognise? Requests a delivery date that doesn't respect your quoted lead time? Asks for a ridiculous price? Each and every field has to be rigorously validated and you have to have a plan to cope when it doesn't validate. After all, who turns down a customer order? Equally you don't want to all that Call Centre labour saving to be swallowed up in increased IT admin costs.

So if you are a small fish supplying a big shark, it is possible you decide to find the 80/20 split by turning all this complicated EDI into a glorified fax machine. It happens.

Part I, Part II

Overview Part II - Communication

Communicating... sending and receiving electronic messages... ummm... sounds like email!

We all understand email and are familiar and comfortable with the concept of email. Then why is EDI more complicated? Well I think part of the answer lies in its age. EDI has been in wide spread use in business longer than email. The other reason may be down to security concerns, but it is certainly technically possible to set up an EDI system using email.

Imagine we didn't have the internet (it wasn't that long ago!). To communicate with another computer I needed either a dedicated cable, or a modem, or a network. If I have 100 trading partners (there's that phrase again) that is a lot of cables or telephoning. So the obvious answer is a network all users communicated with, that forwarded all our messages onwards. Like an out-going postbox and an in-comming mailbox. Yes, this is just like internet email.

It seems so obvious to us now, but back then TCP/IP was not the dominant networking technology. So third party businesses sprung up to operate these networks (called VANs - Value Added Networks). Unfortunately, just like EDI standards, there are lots of EDI networks. So if your customer (eh... I mean trading partner) used a particular one, you were more or less forced to use it, though networks could have their own mailboxs on other networks, to provide network to network communication.

But wait a minute. You still have to communicate with the VAN. Good VANs will offer a choice of ways - modem dial up, secure FTP (File Transfer Protocol), web pages (HTTP - HyperText Transfer Protocol). These days some of these middle-men, third parties, are really just a URL and a server.

Part I, Part III

Overview Part I - Formats

There are 4 elements to EDI.

1. Composing a message
2. Sending a message
3. Receiving a message
4. Processing a message

Steps 1 & 4 are sometimes refered to as "translating" or "mapping". Steps 2 & 3 are the communication.

By translating you take a document on your computer system, say an order or an invoice, and convert it into another format. Or you take a document that someone has sent you from their computer system, and convert into a document on your system.

Senders Format --> Another Format --> Receivers Format

Why use this third format? Why not translate directly into the receivers format? Well this is quite a lot of work. Think VHS --> Betamax. Think Paralell port --> USB port. If you have lots of receivers all with their own formats then you are going to have to write a lot of different translation routines. The big idea of EDI was, if we could all agree on one standard format, and we could all write our own translation routine into, and out of, the standard and save a lot of work.

[SAP / Salesforce.com / Oracle] --> Standard --> [Oracle/ Salesforce.com / SAP]

Unfortunately everyone couldn't agree. There are lots of standards. Edifact, Ebiz-XML, Tradacom... and many more. Some are tailored to specific industries. Some are more popular in certain countries. In IT invovation is very fast. People are always thinking up new "improvements".

So when your trading partner tells you they will be using the new "DLite buzzword compliant standard", ignore the "standard" word. Don't try and enter into a conversation/debate about the merits of it and others. Just think of it as their format. Oh did I say "Trading Partner"? That is another term you have to get used to. It means customer or supplier. If they are a customer, you will be accepting their "standard". If they are a supplier, they will be accepting your "standard".

Part II, Part III

Welcome

EDI - Electronic Data Interchange. Do those letters fill you with dread? They seem to frighten a lot of people I know and they really shouldn't. After too long learning much more than I ever wanted, I now realise what I wish I'd known at the start. However the information just wasn't out there. I am going to try and bring things together on this site.I hope you find it informative.