I finally was able to start working with BizTalk R2, and we do HIPAA/EDI. Within hours of ‘playing’ with it, I was SHOCKED! How did this get out the door?! Who signed off saying this solves anyone’s EDI integration problems?
Okay — so you are right — it allows the EDI shop to be able to handle eleventeen million different transactions out of the box, yes that is cool, and actually quite useful.
However, the part that keeps me up at night is not: “how am I going to translate this transaction?” but “what happened to this file yesterday, or the other file two weeks ago?”
I had gone to plenty of MS conferences where during the beta bits were shown and the reporting was going to be done using BAM, “cool” I thought, if MS is going down the BAM trail, I should too, so my whole paradigm of reporting changed and now I am an even bigger proponent of BAM.
Once I started playing with the R2 bits, maybe hour two, I was scratching my head, where was all of this famed reporting?
Let me not bore you too much with what R2 has for reporting, I will bullet list the things I required that are not present in the current reporting architecture
Error information in a repository that associated the file with the error
I am not a big fan of reading through the event log to try to find the message that failed and then equally tasking process of finding the event log entry that actually tells me what the error actually is. Even MS was not correct when they stated that the BizTalk 2006 Server message id error and the corresponding BizTalk Server 2006 EDI error are paired together:
Being able to associate the TA1/997 to the original message.
Yes, I know that there is a column in the BAM tables to associate it, but it is not used! The only suggestion was to put a warm and fuzzy query that pulls the control number/sender id/receiver id to associate the original message to the corresponding acknowledgment. This did not work for me, as I test like crazy, throwing the same file at a process eleventeen million times a time before I a satisfied, and there isn’t a great way to pair up the values programmatically.
Finding the original filename.
I know that in the perfect world, we don’t care about file names, we simply care about sender id/control numbers, etc: but our trading partners are in love with filename, bordering on creepy! When they call up the first thing that they say is “I dropped of ‘ABCDEFG12345.edi’ last Wednesday – I never got an ACK, what happened?” I have never gotten, without pulling out my crowbar; what their id is, and the control number.
Ability to see the entire EDI on both the receive and send side.
The transaction (that is stored deep in the DTADb database) doesn’t help me if I have a hard time associating it with it’s entire interchange.
What I did:
I wrote a little email to Steve Ballmer that got some people calling me to assist on an issue where if I wanted to reject an entire EDI interchange if any transaction was in error. It was/still is pretty hard to get a non documented feature limitation corrected. I decided that I had fought my battles and was not going to do it again, so I wrote some of my own components:
MY NEW EDI REPORTING
I have a separate datastore that is located on the same database as the BAMPrimaryImport (yes I know I shouldn’t) it is SQL 2005 so I can store quite a large amount of data in the following table:
The other things I wanted to log was all of the context properties that are part of the message (and some that aren’t). Here is a snapshot of the view that takes all that will need to know when a trading partner calls up:
Some notes, I have been testing with 50mb – 100mb files and the view was coming up pretty slowly, so I limited what is shown in the last column is limited in the view to 2500 bytes, and added the Archive Interchange Id so if you need to look at the whole interchange you can run a query against the EDI_Repository.
To get all of this data to show up, I created a couple of pipeline components:
Receive Pipeline
Which you have the following pipeline properties to set when you define your receive location:
And for our outbound EDI data:
Send Pipeline
Which you have the following pipeline properties to set when you define your send port:
You have should specify the database and server, if it can’t find one that is associated with them message (if it wasn’t already attached from the receive side (an automatically generated 997/TA1 for example)) then it will put it in the database you specify.
This is only geared to ANSI X12 transactions, including HIPAA transactions, I have not tested it for UN/EDIFACT.
If you are interested in having this, refer to this page.