Windows Azure SQL Database Marketplace
Anyone who has done serious mapping would have encountered the problem of transforming related data from one set of complex structures to another. This is because of the fact that different applications and systems use different ways to normalize data to best suit their needs. When such systems need to interoperate the data must be mapped from one schema structure to another.
One of the most prevalent patterns used to solve this problem, especially when dealing with complex restructuring, is to de-normalize the data into one flat structure and then normalize it to the target schema structure(s). BizTalk users had to deal with this logic using scripts (in the language of their choice). This was because BizTalk had no support storing intermediate de-normalized data, while the scripting languages did (For example, XSLT programmers typically de-normalize the data into one or more hashtables of complex data strings and then parse them out to the destination).
Lack of support for storing intermediate data would force users to develop scripts by themselves or scripting experts. The more such cases in a map the more it becomes a collection of scripts making mapping a programmer’s domain and mostly unmanageable and unreadable for non-programmers (IT pros, SMEs, Business Analysts). In most such cases, the visual mapping tool is typically sacrificed and a programming tool is adopted, resulting in higher costs in development and maintenance.
In the new transforms functionality of the Service Bus EAI & EDI Labs release, we have introduced support for storing intermediate data in Lists, and perform operations on Lists, the same way one would on source message tree. And all this through visual modelling on the mapper surface. Lists can store multiple members making them two-dimensional and almost equivalent to storing data in tables. Let me illustrate the usage of Lists in solving a complex problem of heterogeneous structural mapping.
LIN Loops commonly help capture the information of items in an inventory tracking application. A nested structure of LIN segments, ZA and SDQ segments full information pivoted on each item type is reflected. The SDQ segment is a bit complicated as it stacks up multiple location and quantity pairs for the different line items. Mapping this structure to a target application structure that is pivoted on locations makes a challenging task; this can be addressed in a two stage procedure.
Stage1: De-normalize LIN,ZA,SDQ data into a list containing all the data. Here are the steps we followed:
This nested set of loop operations within List scope helped us denormalize all the item/location/qty/qty_type data. This data can now be restructured into any relevant target structure.
Stage 2: Use the denormalized data to build the target structure. Here are the steps we followed:
This second stage operations help us complete the target schema structure that is pivoted on locations and items within each location.
SUCCESS!!! Once you get the hang of it, this two-stage procedure supported by the new mapper, is simple, intuitive and readable. You could apply this to more complex HL Loop and other complex nested structures.
With great power comes great responsibility; storing data in Lists will add tax to the performance and other SLAs. For not-so-complex structural transformations, one could do without intermediate storage. We recommend that you use Lists only when you don’t have a workable alternative.
We are continuously striving to provide you with more usable and powerful mapping tools. Do optimize their usage against your maintainability and performance needs. And kindly share your feedback and suggestions to help us improve the mapper tools.
Ravi Bollapragada, Senior Program Manager