Selecting an EDI/Integration Platform

A month ago, a co-worker and I presented at NEECOM on our methodology in selecting an EDI platform to replace our existing platform.  I'm not going to blog about the entire presentation, but you can download it here and let me know if you have any questions.  What I would like to do is address some of the feedback and answer some of the questions that we did get after the presentation.  On a side note, even if you don't know what EDI is, you might find some of the information presented below helpful.  If you live in New England, consider coming to NEECOM in the fall.

That's not how it works - I live in the real world

Believe it or not, this comment came up multiple times in various forms...and I agree with them all.  In fact, as we presented our methodology, we pointed out in several areas where we did not do what we said.  You know the phrase "Do as I say and not as I do"?  Well that certainly applied to us.  We stuck to this methodology as much as possible, but ultimately we skipped a few steps along the way either by design (you can do that) or by mistake.  Looking back if we could do it all over again, we would have done more along the lines of what we presented.

Probably the biggest reason given about the real world is that people don't really have the time or the resources to go through an in depth selection process.  On the contrary...if you think you don't have the resources or time to go through this process, think about what it will be like after you've spent the money and time implementing the first/cheapest solution you figure out that you have to start from scratch because the solution you chose is not capable.  I'm thinking if you don't have a lot of resources, you can't afford to screw it up the first time...so you better do it right.  Just like you wouldn't go buy the first car on the first dealer lot you came across, you shouldn't buy the first software package you find.

Ultimately, we presented a methodology that worked (for the most part) for us along with some lessons learned along the way.  I think if people can take some of that and apply that to their next software selection process then you'll be much better off.

I liked your presentation, but I didn't need to know that - I have a solution already

I think this comment only came up once, but it was enough to stick with me.  I think it stuck with me because the comment seemed so short sighted.  The world is constantly changing. One of our reference checks told us that they go through a full evaluation process for their EDI platform every 3-5 years.  The average IT professional on average changes companies every 3 years.  If you're not willing to change to adapt to the times, the times will leave you behind.  You may not have a need for this information now because you have a stable platform, but what if your software vendor sunsets your solution and now you have an unsupported piece of software.  Either you keep the solution and risk it, or you have to find something new.  I'm thinking at some point in every IT professional's career you will need to know how to select a software solution.  And even if you don't, this methodology will work for just about anything.

Great presentation...who did you choose?

At this point, I am still not able to share who we selected as a platform.  Hopefully that time will come soon.

Gentran Application Integration - Import Map Missing line items

After creating and testing several EDI 810 invoice export maps in Gentran Application Integration, we moved most of them into production. The problem occured when we ran these 810 maps in a batch and were importing many documents at the same time. For some reason some of the invoices were missing line items. At first there was no pattern...a five line item invoice might have two lines while a six line item invoice may only have one.

Rather than go into much detail about what I tried (and what Sterling Tech support tried) here is the problem and the solution:

Problem
The EDI 810 IT101 segment was being generated with the Gentran Application Integration provided code in the Standard rules. The standard rule we used is called loop_count and it was counting the loops through the IT1 group. I realized this might be the problem when one of the maps that did not use this feature (as our trading partner did not require this segment) was not failing.

Solution
In plain terms...use your own code. I created a global variable (under file->session rules) loopcount and initialized it to zero. Each time the IT101 segment is reached, I incremented the loopcount by 1 and set the IT101 segement to this value. Pretty simple.

Added Bonus
So before making this change, I had to audit each invocie by looking at each invoice and comparing the number of line items to the CTT01 segement (usually total line count) to figure out if there were any errors. I would then import all of This was quite a chore. So after adding the loopcount code, I also added a bit of error checking in the CTT01 segement. Here is the code:

If #0354 != loopcount then
begin
empty(#0354)
end

The #0354 value is the value loaded into the CTT01 segment. I happen to have a totalLines field in my database that I map to this segment. If the loopcount value does not match the CTT01 segemnt, the CTT01 segment is set to NULL. Since the CTT01 is mandatory when it is activated, a null value will cause the invoice document to fail and be sent to your red outbox? folder where it can be imported individually, but since getting rid of the canned loop_count standard rule I have not had a failure.

SQL Duplicate Records - Distinct keyword

Every run into a situation where records in your tables have no unique field and for some reason there are duplicate records? Well, this happened to me in our EDI database in our invoice tables and was cause some errors during the import process in Gentran Server. Here is what I did:

First I created temporary tables that mirrored my production tables. (You can get the script for this through SQL Enterprise Manager by selecting the tables and under Action->All Tasks->Generate SQL Script).

Now pull only one of each of the duplicate records into the temp database using the the 'distinct' keyword:

insert into invoiceheadertemp
select distinct * from invoiceheader where tranno = 217589 or tranno = 217590 or tranno = 217591

insert into invoicedetailtemp
select distinct * from invoicedetail where tranno = 217589 or tranno = 217590 or tranno = 217591

Now delete the records from the production database:

delete from invoiceheader where tranno = 217589 or tranno = 217590 or tranno = 217591

delete from invoicedetail where tranno = 217589 or tranno = 217590 or tranno = 217591

Now insert the unique records from the temp tables:

insert into invoiceheader select distinct * from invoiceheadertemp where tranno = 217589 or tranno = 217590 or tranno = 217591

insert into invoicedetail select distinct * from invoicedetailtemp where tranno = 217589 or tranno = 217590 or tranno = 217591

Someone might ask: "Why not use real temp tables with the # prefix?" Well, these temp tables are also my testing tables that I use to test modified stored procedures as well as testing maps in Gentran Server, so it made sense for me to go ahead and duplicate the tables for this dual purpose.

I hope this helps someone who finds themselves in a similar situation and let me know if it does!

EDI System Design

Intro

My company has been doing EDI now for many years. Initially our IT Director was manually processing the 850s (Purchase Orders) into our system for larger companies that mandated we do EDI with them. Gradually these big dogs mandated that we start to send them 856 (advance ship notice) and 870s (order status report). These documents were designed to make their life and business seamless while pushing our IT staff and resources to the max. This continues to happen on a regular basis. We have within the last year begun to move our EDI service to a more scalable product from Sterling called Gentran Server. Although I did not design the current setup, I thought it might be helpful for those out there that are looking at EDI solutions for your company.

The Basics

Since I assume most reading this will know the basics about EDI, I will not go into the details of sending and receiving documents (maybe in another post). I will instead deal with what happens with a document once we received it as well as how an EDI document is exported out of our enterprise system. The two diagrams to the left give you a general idea of how we handle EDI.

The Interim EDI Database was something that was designed and implemented before my time as the EDI guy. This step in the document process is perhaps the most important and was only made possible with the use of Gentran Server. While it is possible to map from Gentran Server directly into our Enterprise System, the thought was that our company is still growing and who knows what the future holds as far as our Enterprise System. Lets talk a little more about this database and its structure.

Interim EDI Database

Our Interim EDI Database has a set of tables for each set of document transactions we send or receive with our trading partners. For instance, we have an InvoiceHeader, InvoiceDetail and a PartnerControlInvoice table that are used to process only Invoices. We have a few other tables that are shared between multiple processes such as ItemTranslation, ItemCustSkuTranslation, and CarrierCode tables that have global data that is the same regardless of the transaction type.

When designing these tables, it is important to make sure all of the data you will ever need to process that document is contained in these tables. One might not think that the tracking number is important on an invoice, but some of our trading partners require us to send that to them on the invoice. Gather all of your trading partner EDI specifications and make sure you know what data you will need in your table design. It also doesn't hurt to gather a little more information from your Enterprise System or EDI documents even if you don't need it now. You may find a future trading partner may need this data.

Database Interfaces

The most time consuming part of the process was putting together the stored procedures to pull/push the data out of and into our Enterprise System. Getting the data out of the system required digging into the its tables and finding the data you need to create the outgoing document. Again, it helps to know what data you absolutely need before you begin this dig. A helpful tip here is to get as much data from one set of tables as you can. For example, if you are looking for invoice data, look for it in the invoicing table(s) before you go and look in the sales order or shipment table(s). This will save you many headaches later...trust me.

Getting the data into the Enterprise System is a completely different process. You need to know what data your system needs in order to process the document. In our case, Sales Orders are the only documents we automate into our Enterprise System. The best way to figure out what you need and where it needs to go is to run a SQL trace while someone manually processes an order. It is very important that you follow each step of the process exactly like the system does. Failure to do so will result in either errors in the Enterprise System (helpful but frustrating) or no errors at all with the Sales Order not getting in the system (just plain frustrating). It helps if someone who designed or maintains the Enterprise System can help you with this process. A SQL guru would be nice as well, if not, Google is your friend.

Why not an out the box product for the database and stored procedures?

For some the option of creating your own database and stored procedures is not feasible. For those I would say it makes sense to look at a product that interfaces with your Enterprise System. Our old system used this type of interface, and while it worked, it did not fit into our long term goals and needs. The product only works with our current Enterprise System. If we made an Enterprise System change, we have had to purchase a new product as well as rewrite all of the maps and go through the testing with our trading partners all over again. With our current set up, we need only redo the stored procedures to move the data into and out of a new Enterprise System. Nothing else changes.

In addition to learning a new Enterprise System, we would have had to learn a new mapping process as well. Learning another proprietary mapping language was not something we were fond of doing either. There are other reasons we didn't like the old out of the box solution, but I would rather focus on what is good about our current set up.

Gentran Server

Gentran Server is an all in one product that allows for mapping and document transmit/receive management. I was not involved with selection of this product, but I was told that the mapping process was more intuitive than any of the other products that were looked at. I can't speak for the other examples, but Gentran Server is quite intuitive. I am sure it also helped that we have an outstanding relationship with Sterling Commerce with us using some of their other product lines.

I think I will reserve some additional thoughts about Gentran Server for later posts. I have learned a few things about the product that may benefit a some of you out there including a few undocumented issues that can be worked around, but can be quite frustrating trying to figure out. On a plus note, their technical services team is reachable and generally responsive to the questions I have.

Summary

It is important to evaluate what your long term EDI goals are before designing your EDI system. We were not entirely convinced that our current Enterprise System will be our last. This thought necessitated us designing a loosely couple EDI system. So far it has served us well and will likely serve us well in the future.

Future Topics

Here are some future EDI topics I have been thinking about. Leave me a comment and let me know what you would like to see...even if it isn't on this list.

EDI Web Interface - Getting EDI troubleshooting in the hand of the less technical

Gentran Server - common issues and the corresponding solutions

EDI Basics - Frequently used EDI terminology

EDI Data Files - Displaying them in web interfaces (812's, 820's, 852's)

EDI Error handling - Dealing with missing mandatory data