The accuracy of the timeline will only be as acurate as the accuracy of the least acurate task estimate.
The time estimate part of project planning is a tricky thing. Generally there is some guessing involved. To keep us from falling short a good practice is to pad estimates to account for the unforeseeable. Project planners can’t just pad and pad. There is pressure from the other side to keep your estimates accurate and keep your timeline short. Time is money after all.
So how do we put enough time in the timeline to not fall short, and keep the timeline from getting too long and expensive? Well that is a dance that takes some practice to learn.
Just remember, when you pad your estimate to your project leader or get and estimate from someone on your project, that padding is necessary, and you should discuss it openly.
Learn the basic tools. Learn them first, and learn them well. Sure, you can find some fancy XSLT tools or text editors. And you can carry them around on a thumb drive and install them on all of you computers, workstations, laptops, and servers. But sometime, some day, you will need to make a change, or fix a problem and you won’t have the fancy tool. My experience says that this will also happen in the middle of the night when you are under pressure. Knowing the basic tools like VI and Notepad, and any other tools that are a standard part of your systems will allow you to get your integration back up and running quickly and get you back to bed so you can go in and answer the questions about what happened with just that much more sleep.
This is like the Covey proverb, “Begin with the end in mind.” In an integration project, the first step is always to define the target of where your data is going to end up. This is true for a document conversion like XSLT or a EDI or Flat File transformation to XML or another version. It is also true for ETL tasks where we are getting data in and out of a Database or repository.
Starting the work of transforming data or files without a well defined target for the data will cause you to do extra and unnecessary work, and to repeat some steps when the true destination is known. It may seem obvious, but many many people fall into this trap, and then don’t understand why their projects keep exceeding their time budgets.
Keeping a copy of all of the documentation you create is a pretty general benifit. It helps you in three major ways;
Having a personal copy means that if the systems that have the public copies become unavilable, you will still have access to them.
Some times projects that get shelved, lose their documentation. If you have a personal copy, when the project comes back to life, you will not be starting over.
And you never know what future project you will be working on that will spark the memory, “Hey we solved a problem like this on this other project…” And having the documentation for it will help you.
I have never regretted keeping a personal copy of documentation. But I have always regretted knowing that I didn’t keep one when I could have used it.
Not to be masochistic, but among friends and neighbours a common topic of conversation between men is our occupations. “What do you do?” is a common question and conversation starter. As we all know, sometimes it is difficult to explain what we do to those outside of our business. There are people that I have known for years that still don’t really understand what it is that I do at work. Read the rest of this entry »
I remember a day when we had a problem with our order processing. When we finally figured out what was wrong, there were a few embarrassed people. We had introduced some new monitoring to the system, but had done so in such a way that we added a point of failure to the ordering process.
This was and is a bad thing. And it can be avoided easily by making sure that you use a passive monitoring system. By its very nature the state of a passive system has no impact on the system being monitored. Read the rest of this entry »
“So, how long will that take?” Is a question that some of us have grown to hate. And it seems that it is a question, that in various forms, we are asked daily. (If not more frequently) And if you have been doing this for any time, you have probably come up with a way to answer these questions. There was a time when I started answering this question with a standard answer of, “2 weeks.” And let me explain why.
Its time for another mapping exercise. This time we will receive an EDI 832 in 4010 format, and map the data to a DB or flat file. Getting catalog data into your procurement system is an important task. And creating variety of mapping exercises provides us with a better understanding of how mapping projects work than just having one that tries to be everything. And mapping is one of the fun things that we get to do. Following this exercise we will have worked through the the common issues and demonstrated how this process comes together.
Mapping Steps
There are five steps to this mapping process. Here is a list with a brief description of each one;
Defining the target: This is the first step to mapping. We must know where we are going if we plan on getting there. And using a Target based mapping process we decrease the time and efort of the other steps.
Defining the Data Source: This is the second step to mapping. Once we know what the target looks like, we know what data we need to complete it. So this naturally leads us to defining the source(s) of the data. This can be a single input data file or record, or it can be multiple types of data from multiple sources.
Defining the Processes: Some of the data that we will need in our target will not be in the proper form or format in the source. We will have to have a process defined in our map to convert the source data into the correct form for the target format.
Handling customisation Points: When we are mapping data, we will see points where we will want to make a decision on what to do. Not all data is created or received in an equal form. Thus we may have points in our mapping process where we will need to do a different process depending on the source or content of the data.
Monitoring the Process: After we have created a mapping process we will need to monitor it so that we can know when something unexpected happens. To do this we must identify the right places in the mapping process for it to report its status to a monitoring process. This is the last step in the mapping process.
Catalog Target
If you have a catalog, then you will have your own schema. We are getting our schema from a standard Open Office DB template. This way you can create your own version for this exercise.
The Product table in the Open Office Business table has these fields:
ID, INT
CategoryID, INT
Discontinued, BOOLEAN
LeadTime, VARCHAR
ProductID, INT
ProductDescription, VARCHAR
ProductName, VARCHAR
ReorderLevel, INT
Serialnumber, VARCHAR
SupplierID, INT
UnitPrice, DECIMAL
UnitsInStock, INT
UnitsOnOrder, INT
These are the basic fields that we will be inserting data into. We will probably have a process that will validate the supplier ID before inserting new records, but that will come in during the process step.
Next Steps
Following defining our target, we will define our source. But we will do that in the next instalment of this exercise. Download and create start your paper map now, we will show them in the next post as well.
Starting off work on my On-line Status Repository, one of the things that I will be starting with is uploading and downloading SEF file from a data repository. SEF stand for Standards Exchange Format. SEF files are repositories of standards information that can then be exchanged between people and applications to define the format of EDI documents.
If you have used a standards editor, you probably know what an SEF file is, or have used it. Some applications and EAI even use SEF files as part of their document creation and validation processes. It becomes useful to describe briefly what SEF files look like, and what type of information they contain.
One of the basic tasks Integration Engineers do is to compare files that we use or receive. There are some interesting and useful tools that people can get out there to DIFF files. But on Linux and Unix machines around the world there is a native tool that is almost always present. Amazingly it is called DIFF.
Like some other command-line tools, its interface is not really intuitive. Lets walk through the basics of how to get use out of this handy file comparing tool. (If you are working with and comparing EDI files, you might want to look at the post on how to unwrap your EDI file so that our line by line comparison is more meaningful.)
If you have just a few Trading Partners, having a unique and separate map for each of them might be a good option. However, if you have plans to scale your integration to 10s, 20s, or 100s and 1000s of trading partners, having a one map to one trading partner strategy is a recipe for a difficult to maintain and support integration solution. And it doesn’t have to be this way. There are a few strategies that will help you create an integration that will scale and be easy to support.
When two trading partners agree to send each other electronic documents. And they begin to describe what EDI documents they will exchange and how the documents will flow, they should also exchange EDI specification documents. EDI usage or specification documents describe what fields and what segments a trading partner will send or expect to convey the information necessary to complete a transaction. It doesn’t matter if we are ordering widgets, or invoicing, or transmitting catalog data, or checking insurance claims eligibility, the EDI needs to contain the data that the two parties need to communicate. To explain this, and document it to that both trading partners know what is expected, we create an EDI usage specification.
I started thinking that I would create a comprehensive integration guide and framework. A document that would guide a person from the beginning to end of an integration, covering all the bases, and addressing all the issues. Then I ditched the idea. No one would ever want to read it. (not even me.) Instead here are 7 simple steps that should get your going, and that you should follow in each integration project. The rest of the plan is largely, and subjectively up to you. Read the rest of this entry »
ACK is an Acknowledgement segment. It is commonly found on response transactions to Purchase Orders or 855. It is one of the lines found in the PO1 loop or group. As this line modifies the PO1 line data from the Purchase Order, what it can contain and how that data is handled is important for the stability of the supply chain integration. For clarity, we are going to use the 4010 x12 standard.
If I had said this a decade ago, you would have thought that I was confessing to something akin to being a peeping tom or voyeur. But today, everyone reading this knows that I am telling you that I look people up on google to see what the internet says about them. If you have ever done or said anything on-line, (or if anyone has done or said anything that references you on-line) it can be found. And it will be by someone at some time. Don’t let this on-line first impression be random or worse, in someone else’s control.
Like the PO1 segment, IT1 is another line item segment used in the supply chain. This is the line item segment found in invoices. And since invoices make up an important part of a supply chain, I wanted to spend another article on this segment. (IT1 is a group or loop so it is more than just a segment.) In this article we will discuss what data is found on the IT1 segment, and how it should be handled on both inbound and outbound document. For clarity, we are going to use the 4010 x12 standard.
One of the things that I advocate is keeping a copy of the documentation you produce, and the documentation that you encounter and use. Over a short period of time, this can become a large amount of stuff. If you are just throwing it all in your MyDocuments folder, it can quickly get out of hand. To help out in this ongoing task and fight against the chaos, I am going to share some basic approaches that can help keep the sanity and utility in your documentation collection. Read the rest of this entry »
Sometimes a picture can bring more clarity to a concept. For Canonical Data, an animation is what is called for. I found this animation of canonical data and its implementation. I think the first minute and a half paint a very good picture of how canonical data is implemented and can be leveraged. Later in the animation they start to describe a global vision of implementation. Unfortunately I must disagree with this vision. I don’t think that having a global canonical form of data will ever truly be a solution that works.
When we are dealing with supply chain or e-commerce related EDI, we probably will deal with line item data. On a Purchase Order, (PO) or 850, the line item data is contained on the PO1 segment or the PO1 group. (PO1 is a group or loop so it is more than just a segment.) In this article we will discuss what data is found on the PO1 segment, and how it should be handled on both inbound and outbound document. For clarity, we are going to use the 4010 x12 standard. The PO1 hasn’t changed in content for a while, but it has gotten longer, (you will see how this works when we talk about the PO1 elements). Read the rest of this entry »
Like the exchange of data between Sellers and Suppliers, the exchange of data within the company is vital to successful processing of transactions. This could be the Seller or the Supplier, it doesn’t really matter. As data passes from the processing application, to the external interface. We have a concept that is called a Canonical form of data. Canonical data is the data that is required and essential to completing your business. I have written about this in passing before, but today I want to talk about what goes in it, and what it looks like. Read the rest of this entry »
I have been studying some on the SEF format. SEF stands for Standards Exchange Format. This is a file that defines the EDI standard so that you can use a validation or standard and usage document editor to create nice, and clean specifications for you and your trading partners.
I have used Foresights EDISIM, but I wonder what others use. I have found a few links to SEF software, and will list them below.
If you are going to work with XML, you will need to understand Xpath. Xpath is the syntax by which elements or data inside an XML file are referenced. Parsers and XSLT use Xpath so that they can find the data that needs to be parsed or transformed. If you understand the fundamentals of XML and how it is formed, understanding Xpath is a logical next step. This is a brief explanation of how Xpath works and how it is used. Read the rest of this entry »
A reader asked me a question, and I want to pass it along: “Do you know of any DOT NET frameworks for working with EDI?”
I didn’t, and a search of the web didn’t reveal anything that I could, in any confidence, recommend. So I bring this question to you. Do you know of a DOT NET framework that can be used for generating and or parsing EDI data? If you do, please comment below and let everyone know. I will also pass it on to my friend who is looking for this.
Whether we are talking about e-commerce or any other movement of data, we will need to capture points of data to ensure proper monitoring and reliability. There is an overall strategy to this, and three types of data points that should be monitored; Points that are convenient. Points that are helpful. And points that are necessary. With these points monitored and the data that passes them recorded and aggregated properly, a reliable and supportable system is created. Read the rest of this entry »
The Integration Engineer has been on-line for a whole year now, and I feel like it is time to look back and take stock of what I set out to do here, and what happened to that vision. So today I am departing from my normal task of writing integration tips, tutorials, and experiences and am going to talk about this blog itself.
Starting out over a year ago, I started toying with the idea of going on-line and starting a blog. I saw other people doing this and read some of their stories of how they got started. I toyed with doing something on one of my hobbies, but settled on the area that I knew the most about, and that would be integration engineering. I did some additional research and picked a hosting provider and a blog platform that seemed to be the easiest to work with. I settled on wordpress as there seemed to be the most support for this and everyone seemed to be talking about it. (And I just wanted to write, not learn a new EAI) Read the rest of this entry »
One of the common data formats that we encounter today is XML. This could be just plane XML, or any of the standardized versions; cXML, ebXML, etc. But there are some basic commonalities between these. And if you haven’t dealt with XML much, you will need to know something about it sooner or later. This post is a brief introduction. Read the rest of this entry »
From time to time I am asked if I know where such and such standard can be found. This is more than idle curiosity. People are trying to integrate, or update integrations, and the standard documentation is not available or has been lost. From the perspective of an integration engineer, such standards are more precious than gold. I keep a collection of the standards that I have used over the years, (something that I recommend) but what I really encourage is for companies to publish their standards in a publicly accessible location. Booknet Canada is an example of a company that has done this. Lets take a look and see what it says….
After you have created your usage specification, it can be useful to use a validation tool to check to verify that new trading partners comply during your boarding process. I have used Foresight’s EDI Analyzer for this many times, and it lets be quickly see where the EDI file departs from the specification.
There is a temptation to use this same validation in the production integration. But this would be a mistake. I’m not saying, “Don’t validate.” I highly encourage validation on both standard compliance and required data validation in mapping and integration. But to use the usage specification has a side effect that I witnessed once. (Only once.) Read the rest of this entry »
Sometimes we hear and then use industry jargon without having a clear definition of what they are. I think all of us are guilty of this from time to time. This post clarifies what a SKU is, and discusses how SKUs are used in the supply chain, and in EDI.
Definition: SKU stands for “Stock keeping Unit” initially used for identifying items that a company keeps in stock so that they can track how many they have and such. Now also used for services, and for contracts or warranties. Read the rest of this entry »
I have sitting on my desk, a very expensive book published by the ASC that contain the guidelines for the X12 3010 EDI standard. I have rarely used them. The EDI standards tool that I have used the most and can highly recommend is EDISIM from Foresight. The last version that I used was version 5.0 and they are on version 6.8 as I write this post. But for a long time, Foresight has nailed the conceptualisation of working with EDI standards.
One of the things that in invaluable is the ability to share your standard documentation both internally and externally. EDISIM is a tool that has allowed me to do that. They produce a very professional looking document for external consumption and the standard is exportable so that everyone using EDISIM can share the same standards file in a format called SEF.
I was reading an article on Spend Matters, by Jason Busch, and I had an idea. Jason was writing about Twitter entering the B2B Mainstream. Now to be honest I had heard about Southwest’s proactive use of twitter to monitor and shape how their customer experience was being perceived. And like many things with Southwest’s business, I find it to be touched with brilliance. So as I read about it, I was suddenly struck with a much deeper way that twitter and twitter like technologies could be used. Read the rest of this entry »
As part of our series “EDI Primer” we are going to go over the various parts of EDI. EDI is neither truly delimited, nor fixed width. EDI has some fixed position components, and uses delimiters in a different way than a truly delimited file. This post it going to overview some facts about EDI and its parts in an introductory fashion. If you are unfamiliar with EDI, you might want to start with the post, “What is EDI?”
Want to get in the head of SAP CEO Leo Apotheker? Charlie Rose does in this interview.
Leo answers a question from Charlie about what makes a software company successful. Leo responds and explains that it is taking good ideas, and incorporating them in software. He then goes on to explain that industry leaders like SAP are made by specializing in one line of business. Companies that only have Enterprise software as one of their business lines will never achieve the same level of expertise.
Today we will finish the three part series mapping exercise. If you haven’t gotten a chance to read the first two posts in this exercise you may want to start with them, or to review them before moving on to the conclusion. (read part One or read part Two)
In last post, we began mapping an EDI Invoice to invoice tables in an Open Office Database. I chose these as Open Office is free and anyone can get a copy to repeat this exercise, and because Invoices are common documents that need to be handled in an e-commerce and supply chain scenario. Read the rest of this entry »
When setting up a supply chain integration, there is a lot of information that is exchanged. One thing that should be explored is what the supplier will do if they can’t process the order. This should be explored by making a list of all the errors that you can conceive of. This is the beginning of your test plan. Then having the Vendor respond with what they will do. (this doesn’t have to be actual transfer of erroring files, it can just be filing out a form with what will happen when said error is sent. But of course, actually sending bad files to and from your test systems is a good idea when possible.) In the end you will have an idea of what will happen and what to expect when something goes wrong. However, sometimes this doesn’t cover all the bases.
To paraphrase a quote from Frank Herbert’s Dune, “The Data must flow.” And in the same sense that spice was the life blood of Arakis, the flow of data is the life blood of any e-commerce integration. In the past I have talked about how RFID technology can be used to enhance the flow of data. In an e-commerce supply chain it can give more up to date information as to where products are located, and thus what state they are in. Read the rest of this entry »
Today we will identify our data source, and begin mapping the source data to the target data. We identified our target format and placed that in the paper map last time. If you didn’t read that post yet, you might want to review it quickly before continuing. (read part One)
Today at Spend Matters, Jason Busch posted the first in a series of articles about the gap between finance, and procurement in today’s business; “When Will finance Take Procurement Seriously?“ Jason quotes from a Baswarestudy and professor Mark Frohlich, one if its authors, and makes some hard hitting and insightful points;
27% of companies surveyed have CFO’s holding a positive view of procurement’s impact on the bottom line.
This is a mapping exercise that will go through the process of creating a paper map, or mapping document. We will start with an empty paper map that you can get here. And we will end with a completed paper map document that documents what data from the source goes into what fields on the target. This process will take more than one post, and I will link them together so that you can follow from one to the next. Along the way, we will discuss the things that we are doing so that you can apply this technique in your mapping using the target and source in your own mapping tasks. Read the rest of this entry »
But in a quick recap of what you will find in this whitepaper, Nahid Jilovec talks about the historical progress of B2B and some of the triumphs, roadblocks, and challenges that B2B as faced over the years. But then he goes farther and describes Supply Chain Synchronization as containing three phases; Connection, Communication, and Collaboration.
I admit that during the first few pages it was reviewing things that I learned first hand, but would be a good introduction to someone new to B2B integration. But then he goes farther and describes some more advanced tactical and strategic ideas that are worth the read.
—-
Do you have some Supply Chain News or Product that should be shared? Or do you use an integration tool or service that other should know about? If so, please send me a note with a link or reference to news@theintegrationengineer.com and I will try to blog about it here.
In EDI there is a construct called a Loop. But this is not like you think of a loop using an while statement. EDI is a file format, not a programming language. When we speak of Loops in EDI, what we are really talking about is repeated structures, or segments.
EDI is not a flat file, but it does have repeated lines or segments. Some times this repetition comes in groups and sometimes its just a line that get repeated in part of the document. Repeating structured data is fundamental to EDI, but can become difficult to interpret at times. Understanding the types of repeating structures and when and where they may occur can save a lot of time. Read the rest of this entry »
Looking for innovation in Supply Chains isn’t hard to do, and it can have a great impact on the efficiency and therefore profitability of a business and market. One place that greatly impacts the success of an integration is the desire to build something new and sexy. I rant sometimes about companies or development teams I have seen go down this path to their own detriment.
But today I want to tell you about a company I heard about that has created new efficiencies through integration efforts of a part of business you may have thought was already integrated. The company is INTTRA. And instead of coming up with an untried technology, INTTRA used a combination of existing technologies to do something new. Read the rest of this entry »
There are job or task specific tools that will have a high importance to each integration task. When working on an SAP system, your SAP tools will be very important. But there are tools and skills that are also important regardless of the systems and technologies that you are working on. For me, these are the top 5 tools that an Integration Engineer should be able to use proficiently. Do you use any of these? Do you have others? Read the rest of this entry »
Definition: A database is a structured collection of records or data.
Many moons ago, I was shown an database that was constructed back in the 50s. It was hand held and consisted of a stack of cards that contained information about plant biology. There was a series of holes that wrapped around the cards, some of the holes were notches, and others were not.
To query the database, one placed a pin through one of the wholes and let the cards that had a notch fall out of the stack. You could continue this process by removing and adding pins to select different combinations of data.
Once you had narrowed your search, you could read the cards, and retrieve the data.
This was a very manual process, and I doubt that anyone today would take the time to learn to use such a tool, let alone update or add records to it. Read the rest of this entry »
RFID is exciting technology for integration engineers. All of this data coming in will have to be aggregated and integrated by someone. Also, one of the focuses of integration is to provide more and better information to people and systems. With more and better information people, businesses and systems can make better choices and decisions. Read the rest of this entry »
The 3rd layer of the EDI envelope is the ST segment. The ST segment contains a Document Identifier, and a control number. The ST and its partner the SE segment define the beginning and ending of the Document. The SE segment contains a counter of segments within the document, and the corresponding control number to the ST. Relatively simple compared to the ISA and the GS. Read the rest of this entry »
EDI in its proper form is wrapped. This means that there is nothing between the segment terminator and the beginning of the next segment. Sometimes this is hard to read, for humans anyway. And at those times people will unwrap the EDI so that it looks more like a flat file. This is not hard to do, and is useful for debugging and trouble shooting processes. A good parser or validator should be able to read either file. Read the rest of this entry »
One of the things that Integration Engineers are asked to do is create documentation. But as we all know, many times documentation is the last and poorest part of a project. Developers and programmers don’t generally like writing documentation, and are generally considered the most qualified.
In comes the Integration Engineer to make the system work. Producing effective documentation at this point is important. We want to make the system work, and then hand if off to the team that will support it. If we don’t create effective documentation, this last step can never happen, and we will be unable to undertake new integration work because we are still supporting the first one. And if we are a contractor, we need this even more. Read the rest of this entry »
So you have done everything right. You have gathered standards, documented your processes and deployed a successful integration. Now what? Do we just sit back watch the data flow and sip soft drinks?
I don’t think so. You will now start to handle exceptions. This means errors. Most likely you will start to have repeating errors. Someone will have overlooked or misunderstood something, and now every time this thing happens there is an exception. Read the rest of this entry »
A character set is a collection or library of characters, (letters and symbols), and their identifying number. Included with the printable characters, (letters and punctuation) are some unprintable yet important characters. Characters are used to form messages.
Characters are not fonts. Characters exist under the font that represent the definition of the character the font is attempting to display. When you change the font on a document the A is changed to an A, but the underlying character that identifies its meaning remains the same. The font identifies how the character is displayed. You can even convert to Wing Dings and the underlying character remains the same. Read the rest of this entry »
Introduction: This is a quick exercise to familiarize you with mapping from an EDI file to a Flat File. If you are new to mapping, or want an idea of what mapping EDI will be like, this exercise should be a good place to start. If you are familiar with mapping this should be a quick review with a few tips. I use Target Based mapping. Check out my post on Target Mapping here. If EDI is unfamiliar and you need some basic information, my EDI primer is here. If you are ready to map, and understand EDI basics, then lets get started.
A circular file is not a nickname for the waste can. Circular files, sometimes called round files, are useful in some applications and support tasks. With a normal log file or repository, the log grows as logged events are added to the log. The obvious danger is that if the space where the log is located becomes saturated when the log grows to fill this space. Many applications will shut down and refuse to restart if this happens. For some applications, having the log write to a circular file is the answer.
If you are tracking inventory, or transactions, you need to get data about the whereabouts of the things you are tracking. In this instance, more data is better. I found this video on YouTube where Sanjay Sarma from MIT gives a lecture on Supply Chain Management and using RFID technology.
Whether we are integrating the data gathered from RFID tracking, or using the techniques in our tracking of data transactions, the benefit is the same; A more robust, and flexible set of data and better issue resolution work flow. Read the rest of this entry »
In the context of delimited data files, an element is the contents between two delimiters. Like in a spreadsheet, the element is the contents of a cell. In EDI, we have delimited segments, and just like a delimited file, the contents between the delimiters is the element.
So in this segment “PO1*1*8*EA*10**SW*H11510.REF*CT*089480-00-B-0336~”, the 5th element or PO1_04 is “10″, while the 6th element or PO1_05 is empty.
Empty elements are not necessarily null. Calling something null when the data type is know like an empty element can cause some data theory debates. I don’t want to start one here, but may write about that philosophical discussion at a later time.
Files are called “Flat Files” when they contain a single data structure. Generally this structure is the column and row structure like a spreadsheet or table, but a file in binary or encrypted with a single encryption key could also be called a flat file. Files that are not flat; marked up files like XML or HTML, EDI files, other formats like HL7 or SEF files and others. Here I am going to briefly discuss two flat file types; Delimited Files, and Fixed Width Files. Read the rest of this entry »
Professor Mansour Javidan, Ph.D talks about supply chain considerations using blue jeans as a case study. This clip is 3 minutes long.
Professor Javidan gives an amusing illustration of some of the challenges that can occur in supply chain integration. 9 different countries, currencies, cultures to get Blue Jeans to your store. Each of these will have orders and payments that flow between companies, and across boarders. Read the rest of this entry »
“Is not the beginning of wisdom the words: ‘I do not know’?”
– Data, Star Trek: Next Generation: “Where Silence Has Lease“
If the beginning of wisdom is to realize what it is that we do not know. NULL, by its definition is this not knowing. We do not know what NULL is, this is why it is NULL. Read the rest of this entry »
Theoretical discussion of integration of data and information.
Speaker is Alan Nash
Alan discusses two fundamental problems in information integration:
(1) How to answer a query over a public interface which combines data from several sources and
(2) How to create a single database conforming to the public interface which combines data from several sources.
Alan is using databases as both source and target and uses them in his examples of how data exchange and integration problems are addressed and solved. This is a little bit heavy, but if you feel up to it, it is 53 minutes long.
This is a still a pretty high level discussion, even the practical examples are abstracted. I have used a tool that helps do the tasks described here. If you have this type of task, you might want to check out a company called Convertabase at http://www.convertabase.com
When EDI was born, (in the 1970s), the committee did its best adopt a forward thinking plan and architecture. But there was really no way for people living before the birth of e-mail to make place for what we use today to do business. So the early versions of EDI had no place for e-mail addresses, and other things that we use in e-commerce today.
The goal of EDI was to provide a way to support communications between dissimilar computer systems. EDI was intended as a light weight, reliable means to transmit data. Read the rest of this entry »
EDI is a standard, and having standards in e-commerce or any type of integration, is important in achieving success in your integration efforts. I found this old video of Bill Gates talking about this very topic, and I decided to share it here. Read the rest of this entry »
The GS segment is the second mandatory enveloping segment. It shares some properties with the ISA segment. There is a sender and receiver, version Identifier, time stamp and control number. But one thing that the GS has that the ISA does not is a Functional Identifier.
That’s right, Functional. This means that within the GS envelope are only EDI messages or documents that have the same function. Read the rest of this entry »
EDI stands for “Electronic Data Interchange” It is a file based format that goes back before the modern Internet and our current concept of e-commerce. The problem that EDI was trying to solve was, “How do we get data from one system to another reliably, cheaply and in a common format?” These problems are still with us today, not because EDI didn’t provide a workable solution, but because it was not implemented on a wide scale. And because competing solutions also exist so that no one has a corner on the market of data formatting. Read the rest of this entry »
The ISA Segment is the first segment in any EDI document. Once you understand what it is saying, This long random seeming string will make sense, and be very helpful in helping you relate EDI documents to the real world task that you need to do. Read the rest of this entry »
An EDI file contains a structure known as enveloping. Everything that is necessary for the file transmission and parsing, is part of or inside of the envelope.
The first segment in and EDI file is the ISA segment. The very last segment in and EDI file is the IEA. For those of you familiar with XML, you may be thinking of this as <File> and </File>.
This approach may make EDI seem familiar, but XML was not around when EDI was being created. It is more like a Beginning Of File (BOF) and End Of File (EOF) marker. However, there is no reason in EDI not to place multiple EDI envelopes in the same file. Read the rest of this entry »
In short, delimiters are characters that separate the elements of data so that one piece of data can be distinguished from another. EDI files don’t have externally set delimiters. This means in a pure sense, that an EDI parser may not know what the delimiters will be until it has begun to parse the file. This may sound chaotic for someone familiar with strict delimited files. But we need to remember that in and EDI file, the first segment is fixed position. This is how EDI addresses the problem. Read the rest of this entry »
Beyond just compliance with the EDI standards, good form is using good practices in planing the format of your EDI. Below are a set of rules or guidelines that will help you create EDI in good form. Good form helps others to received your EDI and process it without encountering common exceptions.
Eliminate trailing delimiters.
When EDI was first created, modems were much slower, and high speed internet and Wi-Fi didn’t exist. Moving large, verbose files over a network took time, and e-commerce solutions charged by the kilo-character. This means that having unneeded characters cost money. Read the rest of this entry »
The segment is what makes an EDI file an EDI file. They are at the very heart of EDI. There are hundreds of different types of segments and each holds a different type of data.
N1^BT^Bill Gates^01^123456789~
For instance the ‘N1′ segments holds some common data that is used in many EDI documents. First, the ‘N1′ type. This is the name type. The first elements following is the qualifier for the second field. If this is were an invoice it might be the code ‘BT’ indicating that the value in the second field is the name of the “Bill To’ party, in this case ‘Bill Gates’. Following the name is another qualifier that determines the type of the account number in the fourth filed. In this case we will use the DUNS Number, and so on. We stop now with the segment terminator to indicate to the parser that the next character will start a new segment. Read the rest of this entry »
For those who may be unfamiliar with the EDI standard, and those who may have used EDI but have encountered a new, unfamiliar question.
This introduction aims at addressing the basic questions of convention and implementation of the EDI technology. I have a list of topics, and hope that over time I can cover all of them. I also hope that people reading here may have questions that I haven’t thought to write about. Please let me know what your questions are and I will see what I can do about providing answers.
Of course understanding how EDI presents and represents data is the first step. From here we may want to go to our Data Mapping Guide, or if we are familiar with mapping there are some examples in our mapping case studies of EDI to and from other formats. Again, if you have an interesting problem or solution, I would love to here about it. Please send it to roy@TheIntegrationEngineer.com and i will see what I can do.
In the beginning we start with the data target. This may be strange if you have not done any mapping, but the first, best thing that you can do to make a mapping project successful and fast is to start with a well defined target for your data.
I believe that Steven Covey says it this way, “Begin with the end in mind.” We do the same thing in data mapping, except that we begin with the end or target if you will. The data target naturally leads us to the data sources that we need.
If one starts with the source, a lot of work goes into the project, like organizing the source data, before we know if we need any of it. Sure, you may know that you will need the shipping addresses if you are mapping to a shipping transaction, but spending time gathering the information on the source of this data is still not effective until we know where we will be putting it.
A guide to mapping data from source to target
Spreadsheets are great for mapping. We start with six columns. From left to right we have source, source data type, rules, target, target data type, and notes. We start wit the target and list the data elements that the target needs in the order that the target needs them to approximate the target form. If this is a database table then this is really simple, and we are just listing the columns names in order.
If the target is something less list like, we will need to add some location information. In XML we might want to include the xpath to the spreadsheet either in the cell above, or in a logical grouping. Both approaches work and I will show you and example of each.
As we list the target we will also want to note the data type in the column to the right. As we note the type of any data with specific formatting, like date or email addresses, we can place that definition here.
After we have the target well defined it is time to decide where this data will come from. We probably already have some idea, so now it is time to work down the source column and fill in the data reference. There may be more than one field that maps to a single field on the right. And there may be one field on the left that maps to multiple fields on the right. There may also be completely derived data that is calculated at runtime. There can also be multiple data sources. You will probably encounter all of these and more in your career as and integration engineer.
The accuracy of the timeline will only be as acurate as the accuracy of the least acurate task estimate.
This is like the Covey proverb, “Begin with the end in mind.” In an integration project, the first step is always to define the target of where your data is going to end up. This is true for a document conversion like XSLT or a EDI or Flat File transformation to XML or another version. It is also true for ETL tasks where we are getting data in and out of a Database or repository.
Not to be masochistic, but among friends and neighbours a common topic of conversation between men is our occupations. “What do you do?” is a common question and conversation starter. As we all know, sometimes it is difficult to explain what we do to those outside of our business. There are people that I have known for years that still don’t really understand what it is that I do at work. 
I remember a day when we had a problem with our order processing. When we finally figured out what was wrong, there were a few embarrassed people. We had introduced some new monitoring to the system, but had done so in such a way that we added a point of failure to the ordering process.
“So, how long will that take?” Is a question that some of us have grown to hate. And it seems that it is a question, that in various forms, we are asked daily. (If not more frequently) And if you have been doing this for any time, you have probably come up with a way to answer these questions. There was a time when I started answering this question with a standard answer of, “2 weeks.” And let me explain why.
Its time for another mapping exercise. This time we will receive an EDI 832 in 4010 format, and map the data to a DB or flat file. Getting catalog data into your procurement system is an important task. And creating variety of mapping exercises provides us with a better understanding of how mapping projects work than just having one that tries to be everything. And mapping is one of the fun things that we get to do. Following this exercise we will have worked through the the common issues and demonstrated how this process comes together.
Starting off work on my On-line Status Repository, one of the things that I will be starting with is uploading and downloading SEF file from a data repository. SEF stand for Standards Exchange Format. SEF files are repositories of standards information that can then be exchanged between people and applications to define the format of EDI documents.
If you have just a few Trading Partners, having a unique and separate map for each of them might be a good option. However, if you have plans to scale your integration to 10s, 20s, or 100s and 1000s of trading partners, having a one map to one trading partner strategy is a recipe for a difficult to maintain and support integration solution. And it doesn’t have to be this way. There are a few strategies that will help you create an integration that will scale and be easy to support.
When two trading partners agree to send each other electronic documents. And they begin to describe what EDI documents they will exchange and how the documents will flow, they should also exchange EDI specification documents. EDI usage or specification documents describe what fields and what segments a trading partner will send or expect to convey the information necessary to complete a transaction. It doesn’t matter if we are ordering widgets, or invoicing, or transmitting catalog data, or checking insurance claims eligibility, the EDI needs to contain the data that the two parties need to communicate. To explain this, and document it to that both trading partners know what is expected, we create an EDI usage specification.
I started thinking that I would create a comprehensive integration guide and framework. A document that would guide a person from the beginning to end of an integration, covering all the bases, and addressing all the issues. Then I ditched the idea. No one would ever want to read it. (not even me.) Instead here are 7 simple steps that should get your going, and that you should follow in each integration project. The rest of the plan is largely, and subjectively up to you. 
ACK is an Acknowledgement segment. It is commonly found on response transactions to Purchase Orders or 855. It is one of the lines found in the PO1 loop or group. As this line modifies the PO1 line data from the Purchase Order, what it can contain and how that data is handled is important for the stability of the supply chain integration. For clarity, we are going to use the 4010 x12 standard.
I google people.
Like the PO1 segment, IT1 is another line item segment used in the supply chain. This is the line item segment found in invoices. And since invoices make up an important part of a supply chain, I wanted to spend another article on this segment. (IT1 is a 
One of the things that I advocate is keeping a copy of the documentation you produce, and the documentation that you encounter and use. Over a short period of time, this can become a large amount of stuff. If you are just throwing it all in your MyDocuments folder, it can quickly get out of hand. To help out in this ongoing task and fight against the chaos, I am going to share some basic approaches that can help keep the sanity and utility in your documentation collection. 
Sometimes a picture can bring more clarity to a concept. For Canonical Data, an animation is what is called for. I found this animation of canonical data and its implementation. I think the first minute and a half paint a very good picture of how canonical data is implemented and can be leveraged. Later in the animation they start to describe a global vision of implementation. Unfortunately I must disagree with this vision. I don’t think that having a global canonical form of data will ever truly be a solution that works.
When we are dealing with supply chain or e-commerce related EDI, we probably will deal with line item data. On a Purchase Order, (PO) or 850, the line item data is contained on the PO1 segment or the PO1 group. (PO1 is a 
Like the exchange of data between Sellers and Suppliers, the exchange of data within the company is vital to successful processing of transactions. This could be the Seller or the Supplier, it doesn’t really matter. As data passes from the processing application, to the external interface. We have a concept that is called a Canonical form of data. Canonical data is the data that is required and essential to completing your business. I have written about this in passing before, but today I want to talk about what goes in it, and what it looks like. 
I have been studying some on the SEF format. SEF stands for Standards Exchange Format. This is a file that defines the EDI standard so that you can use a validation or standard and usage document editor to create nice, and clean specifications for you and your trading partners.
If you are going to work with XML, you will need to understand Xpath. Xpath is the syntax by which elements or data inside an XML file are referenced. Parsers and XSLT use Xpath so that they can find the data that needs to be parsed or transformed. If you understand the fundamentals of XML and how it is formed, understanding Xpath is a logical next step. This is a brief explanation of how Xpath works and how it is used. 
A reader asked me a question, and I want to pass it along: “Do you know of any DOT NET frameworks for working with EDI?”
Whether we are talking about e-commerce or any other movement of data, we will need to capture points of data to ensure proper monitoring and reliability. There is an overall strategy to this, and three types of data points that should be monitored; Points that are convenient. Points that are helpful. And points that are necessary. With these points monitored and the data that passes them recorded and aggregated properly, a reliable and supportable system is created. 
The Integration Engineer has been on-line for a whole year now, and I feel like it is time to look back and take stock of what I set out to do here, and what happened to that vision. So today I am departing from my normal task of writing integration tips, tutorials, and experiences and am going to talk about this blog itself.
One of the common data formats that we encounter today is XML. This could be just plane XML, or any of the standardized versions; cXML, ebXML, etc. But there are some basic commonalities between these. And if you haven’t dealt with XML much, you will need to know something about it sooner or later. This post is a brief introduction. 
From time to time I am asked if I know where such and such standard can be found. This is more than idle curiosity. People are trying to integrate, or update integrations, and the standard documentation is not available or has been lost. From the perspective of an integration engineer, such standards are more precious than gold. I keep a collection of the standards that I have used over the years, (something that I recommend) but what I really encourage is for companies to publish their standards in a publicly accessible location. Booknet Canada is an example of a company that has done this. Lets take a look and see what it says….
After you have created your usage specification, it can be useful to use a validation tool to check to verify that new trading partners comply during your boarding process. I have used 
Sometimes we hear and then use industry jargon without having a clear definition of what they are. I think all of us are guilty of this from time to time. This post clarifies what a SKU is, and discusses how SKUs are used in the supply chain, and in EDI.
I have sitting on my desk, a very expensive book published by the ASC that contain the guidelines for the X12 3010 EDI standard. I have rarely used them. The EDI standards tool that I have used the most and can highly recommend is 
As part of our series “
Want to get in the head of SAP CEO Leo Apotheker? 
Finish The Paper Map
When setting up a supply chain integration, there is a lot of information that is exchanged. One thing that should be explored is what the supplier will do if they can’t process the order. This should be explored by making a list of all the errors that you can conceive of. This is the beginning of your test plan. Then having the Vendor respond with what they will do. (this doesn’t have to be actual transfer of erroring files, it can just be filing out a form with what will happen when said error is sent. But of course, actually sending bad files to and from your test systems is a good idea when possible.) In the end you will have an idea of what will happen and what to expect when something goes wrong. However, sometimes this doesn’t cover all the bases.
To paraphrase a quote from Frank Herbert’s Dune, “The Data must flow.” And in the same sense that spice was the life blood of Arakis, the flow of data is the life blood of any e-commerce integration. In the past I have talked about how 
Today at Spend Matters, Jason Busch posted the first in a series of articles about the gap between finance, and procurement in today’s business; “
This is a mapping exercise that will go through the process of creating a paper map, or mapping document. We will start with an empty paper map that you can get 
In the news, 
In EDI there is a construct called a Loop. But this is not like you think of a loop using an while statement. EDI is a file format, not a programming language. When we speak of Loops in EDI, what we are really talking about is repeated structures, or segments.
Looking for innovation in Supply Chains isn’t hard to do, and it can have a great impact on the efficiency and therefore profitability of a business and market. One place that greatly impacts the success of an integration is the desire to build something new and sexy. I rant sometimes about companies or development teams I have seen go down this path to their own detriment.
There are job or task specific tools that will have a high importance to each integration task. When working on an SAP system, your SAP tools will be very important. But there are tools and skills that are also important regardless of the systems and technologies that you are working on. For me, these are the top 5 tools that an Integration Engineer should be able to use proficiently. Do you use any of these? Do you have others? 
The 3rd layer of the EDI envelope is the ST segment. The ST segment contains a Document Identifier, and a control number. The ST and its partner the SE segment define the beginning and ending of the Document. The SE segment contains a counter of segments within the document, and the corresponding control number to the ST. Relatively simple compared to the ISA and the GS. 
EDI in its proper form is wrapped. This means that there is nothing between the segment terminator and the beginning of the next 
One of the things that Integration Engineers are asked to do is create documentation. But as we all know, many times documentation is the last and poorest part of a project. Developers and programmers don’t generally like writing documentation, and are generally considered the most qualified.
So you have done everything right. You have gathered standards, documented your processes and deployed a successful integration. Now what? Do we just sit back watch the data flow and sip soft drinks?
What is a “Character Set?”
Introduction: This is a quick exercise to familiarize you with mapping from an EDI file to a Flat File. If you are new to mapping, or want an idea of what mapping EDI will be like, this exercise should be a good place to start. If you are familiar with mapping this should be a quick review with a few tips. I use Target Based mapping. Check out my post on Target Mapping 
A 
What is an Element?
What is a flat file?
When EDI was born, (in the 1970s), the committee did its best adopt a forward thinking plan and architecture. But there was really no way for people living before the birth of e-mail to make place for what we use today to do business. So the early versions of EDI had no place for e-mail addresses, and other things that we use in e-commerce today.
What is the GS segment?
EDI stands for “Electronic Data Interchange” It is a file based format that goes back before the modern Internet and our current concept of e-commerce. The problem that EDI was trying to solve was, “How do we get data from one system to another reliably, cheaply and in a common format?” These problems are still with us today, not because EDI didn’t provide a workable solution, but because it was not implemented on a wide scale. And because competing solutions also exist so that no one has a corner on the market of data formatting. 
What is in the ISA?
An EDI file contains a structure known as enveloping. Everything that is necessary for the file transmission and parsing, is part of or inside of the envelope.
What are Delimiters
Beyond just compliance with the EDI standards, good form is using good practices in planing the format of your EDI. Below are a set of rules or guidelines that will help you create EDI in good form. Good form helps others to received your EDI and process it without encountering common exceptions.
Why are segments Important?
For those who may be unfamiliar with the EDI standard, and those who may have used EDI but have encountered a new, unfamiliar question.
In the beginning we start with the data target. This may be strange if you have not done any mapping, but the first, best thing that you can do to make a mapping project successful and fast is to start with a well defined target for your data.
