The Integration Engineer
When it just has to work.

“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.

(more…)

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.

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.

(more…)

Finish The Paper Map

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. (more…)

Continuing Mapping Exercise

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)

(more…)

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. (more…)

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. (more…)

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.

(more…)

What is a flat file?

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. (more…)

I found this video on YouTube.

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

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.

Upcomming Articles:

1. EDI mapping excersize.
2. XML mapping excersize.