How do you do analysis for a BizTalk map?

I found myself getting frustrated a while back with the analysis phases of integration projects and how ineffective projects are at communicating the requirements and various other information to help the developer be effective at building an integration solution quickly.  This lead to me doing an integration monday session about the analysis phase of integration projects.  In this session I talked briefly about how data mappings are an important think but I wanted to take some more time to discuss these.

Ok so setting the scene, we are discussing integration requirements for a project with various stakeholders in the early phase of a project.  We are starting to see the coming together of definitions of what messages might look like.  Often this might be some kind of high level data model diagram or perhaps we have documents describing flat file structures or even wsdl/swagger defining API’s.  The problem however comes when we need to start discussing amongst the team how to transform the data from one format to another.

If your a BizTalk developer your first thought is probably “well why cant i just use the biztalk mapper”.  There are a few problems with this:

1. You need to have schema’s defined to be able to use the mapper
2. You need to have visual studio to use the mapper so a business analyst cant work on the data transformation without the developer being involved
3. You cant easily communicate and collaborate with the rest of the team/stakeholders on the mapper

The key thing here is that before development starts you are working with analysts and members from other teams to define the high level view of this transformation piece and you cant easily do this with the BizTalk mapper.  Its intended for a BizTalk developer to use.

I spent a lot of time looking around for an appropriate tool that I could give to a Business Analyst and architect to allow them to easily be able to draw visualisations of what the mappings might look like and to make some notes on how the transformation might work which they could then pass on to the developer so the developer would have an easier time creating the map.  Basically I want something that looks like the BizTalk mapper where I can define two tree structures to represent the data and draw lines between the trees and write notes on those lines.

Unfortunately I just couldnt find a tool that would help me design a data transformation at this high level.  The best I have been able to come up with is an approach using excel.  In excel I would create something that looks like the below picture.

As you can see you can create a primitive tree structure on the left and right side of the worksheet.  You can even include multiple message types.  You can then use the drawing menu to draw arrows to visualise these mappings and use cell comments to be able to write notes on what the transformation may include.

With this approach I was able to achieve my aim of allowing non BizTalk developers to have an effective way to do the analysis side of data transformation up front before the BizTalk developer needed to be involved in a way that was easy to transition into development for a BizTalk developer to work with.  This was much more effective than the hideously difficult to use word documents I am used to seeing.

So while this approach worked, Id love to hear if anyone has a better tool to do this kind of thing?  Also would love to hear about other approaches people are using.