Mapping Excersizes: EDI Invoice to Open Office Tables (part One)
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.
The Target
Open Office tables are divided into two tables. Invoice and Invoice Details. This can be mapped in two ways; The first way is to map the data into one common format and rely on whatever ETL tool is importing the data to catch and split the data. Second, a key can be acquired or constructed in the transformation and then the the data can be divided in to matching input formats. Then when these inputs are moved into the database, they will relate to each other on this key.
The choice of how you will do this will depend on your environment. Questions like, “Will I have enough data to provide a unique key?”, or “is there a way to get a key with an API call or database query?” The answers to these questions will determine what course you will take.
If the system ultimately receiving the data is asynchronous to the transformation, and you need to send the invoice and invoice details data separately, some care needs to be taken to ensure that the data can be related after it is separated.
So what data in the invoice can be used to tie the invoice to the invoice details? The first answer might be, “The Invoice Number.” But this number is not guaranteed to be unique across multiple vendors. In EDI and cXML there are document unique identifiers. Since we are using EDI, we can use a combination of the ISA Sender, Receiver, and Control number. We will also want to use the GS control number, and ST control number in the event that more than one invoice is sent in a single envelope. And we might as well tack on the actual invoice number from the BIG_02.
Database Tables
|
Invoice Table: |
Invoice Details Table: |
 |
 |
The Paper Map
Now that we know what the target looks like, we fill out the target side of the paper map. Since we will create two “files” in our output. We are creating the Invoice and the InvoiceDetails file, but we can use one paper map for both, and will distinguish this with a bar between the two “files”. (I am saying files, but this could be a queue, or a post, or an insert over odbc, etc)
What’s Next
Today we went through the process of identifying the target, and creating a paper map with the target format identified. We talked about some of the strategy that we use in deciding what to map and how to map it. Next time we will identify the source, and begin mapping data from the source.
September 17th, 2010 at 3:15 am
The mapping excersize is not an easy job because you must be skilled enough in computing and memorizing different formulas with the help of the EDI invoice.
September 17th, 2010 at 12:14 pm
Bruce,
Thanks for your comment. EDI knowledge is important as we move down the path of a mapping project.
Part of the purpose of a mapping exercise is to separate the formula from the mapping. We do this so that we figure out what data is going to go where before we create the logic to change data format etc.
Many times people dive into a project and start to build the transformation code to move the data from the source to target. This causes many problems down the road.
When we start with the target, and then just map the sources of the data, we get a better field of vision and avoid running into many of the problems that come from diving in blind.
Thanks again for your comment. Hope your mapping projects are going well.
Roy