If you work with enterprise integration scenarios you will often come across scenarios where you have a reference data field in two systems and as data flows from one to the other you need to map these values between systems. Back when we used BizTalk for these kinds of integrations we would use a sub-system within BizTalk called “Cross-Reference Data Mapping” (aka X-Ref) which allowed us to store a mapping table for the data so you could go from one systems value to another. In the iPaaS world we dont really have an equivalent feature but you still have the requirement in many interfaces so there is a question about how you should do it.

There are a few different ways you can implement this requirement but regardless of how you do it, you want a couple of characteristics:

  • It needs to perform pretty quickly
  • It needs to be easy to modify the mappings especially if they change often
  • It needs to be low maintenance

The easy way to do this is to store the data in something like a SQL database or some data store and perform lookups, but these ways are often a bit slow and we end up performing caching and need to implement a custom caching approach like we used to do in BizTalk. You then have all of the overhead of managing the database, having new tables, maybe you need to involve functions and then you have all of the hastle of the SQL connectors in logic apps.

Recently I used Azure APIM to implement this feature in a really light weight way which ticked all of the boxes above and I thought others would find it handy.

Approach

I created a reference data mapping API in APIM and for each data attribute I wanted to support cross reference mapping I would have 2 operations. The first would be the mapping data as a json object. The 2nd would be the transformation operation where I would supply a system name which is the source system and the system value from the source system. The operation would then give me back an object listing properties with the equivelent value for each other system.

If we consider the example of mapping Customer Type I would have the following operations:

Customer Code Data

The Customer Code Data operation would be a GET operation where I would use the mock response feature of APIM to return a static json object without having to call out to an external data store. The url for my api would be:

https://[MyDomain]/eai/mapping/reference-data/customercodes

On the definition for the operation I would define an example response message for the 200 OK response and the application/json content type which returns the below json.

{
	"Mappings": [{
			"SystemA": "1",
			"SystemB": "A",
			"SystemC": "9",
			"SystemD": "Z"
		},
		{
			"SystemA": "2",
			"SystemB": "B",
			"SystemC": "8",
			"SystemD": "Y"
		}
	]
}

This json defines the values for the customer types for all systems (SystemA —> SystemD).

In the policy for this operation I would have the below xml which simply returns the json for the example I have supplied which gives me an easy way to return a static response and also if I want to add or modify the mappings I can just update the sample message in APIM.

The policy is:

<policies>
    <inbound>
        <base />
        <mock-response status-code="200" content-type="application/json" />
    </inbound>
    <backend>
        <base />
    </backend>
    <outbound>
        <base />
    </outbound>
    <on-error>
        <base />
    </on-error>
</policies>

This operation just gives me an easy way to access the mapping data without needing to call out to an external system or data source so it will be quick.

Customer Code Transformation

In the transformation operation I need to supply the System Name and System Value as input and then it will return the mapping object providing the values for each system. You can see below that I have included the source system name as a template parameter in the url and then the source system value as the query string value.

https://[MyDomain]/eai/mapping/reference-data/customercodes/SystemD?systemValue=Y

In this API I will then call on to the Customer Code Data to retrieve the mapping data and then use some C# inside a Set-Body policy expression in the outbound section to perform a json query of the mapping data to find the object I want and then I will return the data. The policy I use looks like below:

<policies>
    <inbound>
        <base />


        <set-backend-service base-url="https://[MyDomain]" />
        <rewrite-uri template="/eai/mapping/reference-data/customercodes" copy-unmatched-params="true" />
    </inbound>
    <backend>
        <base />
    </backend>
    <outbound>
        <base />
        <set-variable name="systemName" value="@(context.Request.MatchedParameters.GetValueOrDefault("systemName"))" />

        <set-variable name="systemValue" value="@(context.Request.OriginalUrl.Query.GetValueOrDefault("systemValue"))" />

        <set-body template="none">@{            
            var response = context.Response.Body.As<string>();

            var systemName = (string)context.Variables["systemName"];
            var systemValue = (string)context.Variables["systemValue"];

            var mappings = JObject.Parse(response);

            var mapping = mappings.SelectToken($"$.Mappings[?(@.{systemName} == '{systemValue}')]");

            return mapping.ToString();
        }</set-body>


    </outbound>
    <on-error>
        <base />
    </on-error>
</policies>

When I run the API operation with the url I mentioned earlier then I would get the below response:

{
    "SystemA": "2",
    "SystemB": "B",
    "SystemC": "8",
    "SystemD": "Y"
}

This easy to use json object would then be easy to use in a Logic App to get the value for System C dynamically from my mapping API so I could load the data into System C.

Caching for Performance Improvement

The mapping API performs pretty quickly. For my scenario it was running sub 100ms response times, but you could also add some response caching and when I did this in the transform operation I was getting response times of < 1ms in the Azure Portal test app which is superb for a transformation API to help you do mapping of reference data and loads quicker than Logic App calls to a database will be.

The policy steps I added to add response caching are below.

<policies>
    <inbound>
        <base />

        <cache-lookup vary-by-developer="false" vary-by-developer-groups="false" must-revalidate="true" downstream-caching-type="none" caching-type="internal">
            <vary-by-query-parameter>systemValue</vary-by-query-parameter>
        </cache-lookup>

        <set-backend-service base-url="https://[MyDomain]" />
        <rewrite-uri template="/eai/mapping/reference-data/customercodes" copy-unmatched-params="true" />
    </inbound>
    <backend>
        <base />
    </backend>
    <outbound>
        <base />
        <set-variable name="systemName" value="@(context.Request.MatchedParameters.GetValueOrDefault("systemName"))" />

        <set-variable name="systemValue" value="@(context.Request.OriginalUrl.Query.GetValueOrDefault("systemValue"))" />

        <set-body template="none">@{            
            var response = context.Response.Body.As<string>();

            var systemName = (string)context.Variables["systemName"];
            var systemValue = (string)context.Variables["systemValue"];

            var mappings = JObject.Parse(response);

            var mapping = mappings.SelectToken($"$.Mappings[?(@.{systemName} == '{systemValue}')]");

            return mapping.ToString();
        }</set-body>

        <cache-store duration="60" />
    </outbound>
    <on-error>
        <base />
    </on-error>
</policies>

Hopefully this post is really useful to any integration developers looking for low overhead way to implement an easy to maintain and modify Cross Reference mapping capability for your interfaces without having to spin up loads of other Azure Resources especially if your already using APIM.

 

Buy Me A Coffee