So here’s the challenge… we have Retail CDX up-and-running… how do we hook up any middleware to integrate with 3rd party POS systems, E-commerce and other sales channels?
Hmmm… let me add one nuance here which we might overlook: how do we hook up middleware without touching the integrity of Retail CDX?
In order to answer this question I think it’s good to shortly refresh our minds about Retail CDX. Then we’ll take a look at my solution for both the batch processing as well as transactional types of interfacing.
Standard AX/Retail CDX
Dynamics AX Retail CDX builds upon the principle that data should efficiently be distributed to decentral sales systems like POS systems. This efficiency should maximize data actuality in the sales system, even in low bandwidth environments and even when large datasets (for example products) are exchanged. This principle is relevant as a sales system should have the highest possible uptime and therefore should never be directly dependent on interfaces with central systems for its day-to-day operations. Further, Retail CDX is designed for enterprise environments where multiple sales systems are to be served from 1 or multiple AX instances with a variety of time zones, currencies, local tax requirements etc. Keep this in mind when looking at CDX overview picture below which only shows a 1 AX instance/1 sales channel situation.
So what exactly is the flow of things within the CDX landscape?
For the batch processing side of the landscape, it’s basically all about the AX database, channel database and two ‘helpers’ to ship the data over between the two databases: a central helper (Async server; HQ side) and a local helper (Async client; channel side). The helpers only do something when they receive instructions. After receiving instructions, they do their job and write a standardized report on their job (meta data) back into their own meta databases. In addition, the local helper also reports his meta data to the central server helper, so all local meta data about everything happening in the CDX landscape can be synched back into AX by the Retail > Periodic > Data distribution > Process status messages batch job.
To be more precise:
- AX hands over an instruction to download information to the central helper.
- The central helper contacts the AOS which is the gateway to the AX database , downloads the required data, serializes that data into XML files, wraps these XML files into 1 binary file and outputs that file to a working folder which was configured in AX and synched to the central helper’s interface meta database as part of the CDX setup.
- The local helper periodically requests the central helper if there any new instructions to be executed. The frequency for this request is defined in the Async server profile configuration in AX. In case of any data to be shipped, the central helper responds with a download instruction including a reference to the working folders. The local helper then writes this instruction into its interface meta database, executes the instruction (downloads the binary file), unpackages the binary file into XML files, de-serializes the XML files, injects the content of the files into the channel database, writes meta data about this operation into the interface meta database and reports the same information to the central helper (which the central helper writes into its own interface meta database).
Hooking up middleware: batch processing (non-real time)
So the actual transport of data between AX and the channel databases is done by XML files?
YES – let’s take a short look into those files (note: you can use the tool DDPACKVIEW for this) – the example here is the 1000 scheduler job for currencies:
As you can see the binary ‘package’ contains 1 header XML file and multiple XML files with data. The header XML files indicates which XML files containing data (‘data files’) are attached in the binary package and which operations should take place for the different data files (delete or insert/update). The data files ‘just’ contains the data to be deleted or inserted/updated:
As you can conclude from the “ax.” prefix of the tables, the files and data 1:1 reflect the target Retail channel database schema and data in AX, which is recognizable from the RecId value for the currency table. This is because we picked the AX 2012 R3 Retail channel schema for this example, where channel database schema and AX database schema are perfectly matched.
So why wouldn’t we just skip the channel database in the architecture and let middleware intercept and process the binary file with XML files?
I didn’t choose that path for the following reasons:
- The AX/channel database integration is proven technology which goes well back into the history of the LS Retail solution (say 15 years ago). So ‘decoupling’ the binary file transfer from this architecture will impose project risks.
- Some scheduler jobs (like the 1040 – product) ship over 80 XML files. Processing these quantities of XML files for 1 flow will impact performance negatively. Middleware specialists rather integrate through SQL server than through XML files as SQL server is faster and provides more technical opportunities for integration.
- By using the CDX XML files within the middleware orchestration, we have to make the middleware aware of the AX data model in some way, while the AX channel database offers many out-of-the-box views and stored procedures which can be leveraged as an access gateway for the middleware solution.
Details of the solution for batch processing
So instead, I chose to hook up BizTalk to the channel database. But how could I trigger BizTalk to perform action without impacting the standard AX/Retail CDX integrity? To answer this question we need to cover the same dimensions which are covered by the standard AX/Retail Scheduler: WHEN, WHAT, WHERE. The WHEN refers to the trigger for the middleware solution to take action. Let’s take the example of a BizTalk application which is functionally linked to the 1000 scheduler job run in AX and technically to the local client’s helper for a specific channel. I took the following conditions as criteria for BizTalk to start taking action:
- [Async client meta database].dbo.DownloadSession.JobId = [Job ID BizTalk application is designed for]
- [Async client meta database].dbo.DownloadSession.Status = ‘4’ (=Applied)
In other words: the BizTalk applications is continuously monitoring the local helper’s interface meta database for in-scope channel database changes which have been confirmed by the local helper.
But at this point in time the BizTalk application does not have a clue yet about the WHAT yet: what data has been changed exactly? In order to answer this question I decided to imitate the behavior of the trigger to ship data on HQ side: SQL change tracking. See my other blog post for more details if you’re interested in this feature. Bottom line, a similar SQL change tracking mechanism has been be applied to the SQL database. BizTalk builds upon stored procedures which confront data from one more standard AX channel database tables/views or custom views with change tracking. Here’s an example for a stock availability flow:
The core table which triggers BizTalk to create an output file for the 3rd party system is the RETAILINVENTAVAILBILITY table (see directly behind the CHANGETABLE statement in the picture above). But the trigger can easily be on more tables and/or individual columns. Be aware that this is dependent on the requirements of the target 3rd party system.
So our situation is now as depicted below: a BizTalk application is monitoring the local helper’s interface meta database to spot for in-scope data changes. If this is the case, then the application fires a stored procedure to the channel database to check if the changes are relevant for the target system.
The customer where I implemented this solution had over 20 E-commerce instances across the globe and different types of topologies varying from a central BizTalk instance/regional AX instance situation to a regional BizTalk/local AX instance situation. In that situation the WHERE question goes beyond the target system only. It’s about HOW to implement flexibility across the whole architecture as a whole. What if the customer decides to move a channel database to another server or if the central/regional topology is changed into a regional/regional topology? We cannot simply hard code all the server names, database instance names and service addresses into BizTalk. That would mean a redeployment of many applications if something is changed in the infrastructure landscape.
Analyzing these needs and looking over the powerful tools we have in hand with Retail CDX I came to an out-of-the-box solution. I decided to define BizTalk as a Retail store in AX and create a custom scheduler job 9000 (by configuration ;-)) to ship all technical/infrastructure related channel meta data to a special AXBizTalkConfiguration database, which is technically nothing more than the channel database for the virtual BizTalk channel. I only needed to add fields to AX reflecting the location of the 3rd party system, Async client database and meta database instance name:
The information from the view is consumed by the BizTalk application at run time and utilized until the application is restarted. This prevents hard coding and having to republish BizTalk applications over and over again in case of changes. See my blog post Agility (blog post will follow) for more details if you’re interested in this feature.
So.. We’ve now made BizTalk aware of WHERE to deliver the output information in an agile way. Here’s the full solution overview including triggers:
Before we go along the triggers in the landscape, there’s one feature left unspoken. As we’ve seen for CDX, all local helpers indirectly report the results of their actions back into AX. This provides us the means to control the CDX landscape from our one and only AX headquarters.
When it comes to integration, everybody who has some practical experience in this area knows the by far biggest vexation in this area: FINGER POINTING :(. This is often a clear indication that either frameworks for efficient maintenance are missing or clear procedures for problem solving or the worst of all… both. So the best way to avoid this is to hook up both BizTalk and the 3rd party systems to existing CDX meta data framework – Let all systems in the landscape report the results of their interface related actions back to AX. In order to offer a standardized entry point for this I’ve leveraged Real Time Service to directly write into the standard AX/Retail Inquiries > Commerce data exchange > Upload sessions/download sessions forms. The result looks like this:
We can follow the chain of interface links up till its destination and easily intervene in case of any issues! See my blog post Control dashboard for more details if you’re interested in this feature. So let’s get back to our main solution above again and consider each and every trigger in the interface landscape:
- Trigger 1: batch job in AX initiating the (potential) transfer of data to the channel database. Frequency: to be determined by the business.
- Trigger 2: The local helper (Async client) requesting the central helper for new instructions. Frequency: as configured in the respective Async server profile in AX. Common setting: 1 minute or immediate.
- Trigger 3: a BizTalk process polling for in-scope data changes. Frequency: immediate.
- Trigger 4: a BizTalk process transforming source to target data and delivering this data. Frequency: immediate
- Trigger 5: a BizTalk pocess reporting back to AX on the result of its actions. Frequency: immediate
So from this trigger landscape we can conclude that all links in the chain ‘follow’ the AX frequency: when the business decides to ship out products (1040 scheduler job) with a frequency of once an hour, the rest of the interface chain will follow… if the target system (the ‘unidentified’ trigger 6) supports this as well… For the upload flow (3rd party to AX) this is no issue as the frequency of the AX pull job will still decide the ultimate frequency of data exchange. My advice: configure all the other interface links on ‘immediate’, so AX has full control over the frequency across the complete landscape.
Hooking up middleware: transactional information exchange (real time)
Most of you will be familiar with request/response mechanisms. But For the sake of completeness I’d like to spend a few more words on the transactional information exchange through Real Time Service as well. Most important to notice here is that I leveraged Real Time Service not only when timing or direct response was a requirement for an interface flow. Actually I weighed with every flow design where standard AX/Retail CDX could offer the best fit, independent from the type of interfacing on 3rd party system side. For example, I faced a situation where the 3rd party system sent customer data to BizTalk in a batch processing manner. But for my AX side it was better to leverage the standard AX/Retail RetailTransactionServiceCustomer::newCustomer method instead of trying to implement a new pull flow for batch processing customer records.
Further, I also applied the CDX meta data service call through Real Time Service (you know.. To report on the result of interface actions) to the real time flows. This gives a nice ‘ping-pong’ effect where the responses of 1 or multiple service calls are reported back to AX by another service call:
To conclude this overview of a Dynamics AX/Retail CDX based solution for 3rd party sales channel integration, I’d like to stress the comfort this solution brings to your day-to-day use of standard AX/Retail. Within AX you’ll be able to handle a 3rd party sales channel as a ‘normal’ AX Retail store or Online store. This means that also conceptually you can fully leverage standard AX/Retail to support your 3rd party sales channels:
Some of you will argue here: yeah nice, but what about price calculations in the 3rd party channels or tax calculations?? For the native AX POS and online store, we have Retail server which manages to bring the same pricing logic to the local native AX sales channel as we have available on HQ – what about the 3rd party sales channels?? As I might spend another blog on this topic in the future, my answer for now is simple: either integrate your 3rd party channels also with Retail server or Real time service or limit your AX/Retail configuration options (for example for discount structures) based on what you 3rd party sales channels can support. In this latter case, my recommendation would always be to minimize the footprint of your sales channels and maximize the footprint of AX. It’s not for no reason that AX supports many localizations – you don’t want to re-implement all those localizations in your sales channels when rolling out your 3rd party system worldwide ;-).