patrickmouwen: Dynamics AX Retail 3rd party POS and E-commerce integration – Trigger mechanism

[Blog bot]

Источник: http://patrickmouwen.com/technicalin...ger-mechanism/
==============


In my main blog post regarding Dynamics AX Retail 3rd party sales channel integration I presented the mechanism to trigger BizTalk to take action after an AX scheduler job run. In this blog I’ll provide some more details on this particular topic. Let me first explain how the change tracking mechanism is leveraged on AX HQ side and let’s then take a closer look on how change tracking can be leveraged on the channel database and Async client database for use by middleware like Microsoft BizTalk.



Change tracking on AX HQ

On AX HQ the Generate classes button on the Retail > Setup > Retail scheduler> Retail channel schema form runs business logic in the RetailCDXCodeGen class. One of the steps is here is to enable change tracking for all tables reflected by the subjobs in AX – this saves us the time of having to enable change tracking on SQL level itself (thanks MSFT ;-)). Another step is to create queries for each table which is defined as a subjob (Retail > Setup > Retail scheduler > Scheduler subjobs). Here’s what such a query looks like for the standard AX currency table (source: RetailCDXChangeTrackingSQL table):

As you can see in the CHANGETABLE and CHANGE_TRACKING_IS_COLUMN_IN_MASK statement, this query enables AX to list the records for which any of the indicated fields (columns) in the Currency table has been updated for a particular change tracking version (passed through with the %2 parameter). So when a scheduler job runs, AX does the following:

• AX selects the set of tables which is relevant for the particular job run (here: the 1000 – Currency).
• AX gathers the changes for these tables since the last change tracking version which has been addressed in the last job run. Note: this change tracking version is visible as ‘row version’ under Retail > Periodic > Data distribution > Distribution schedule > [Select schedule] > [History button in ribbon]:

Important here is that SQL Server does not keep change tracking history forever as this might expand the database size disproportionally in case of high-transactional use of AX. So to limit the number of transactions, SQL Server works with a retention period which can be set on the database properties in SQL Server:


The default retention period is 2 days. If the retention period is exceeded (read: if a particular job has not been running once in 2 days), AX has no other option than to run a full synch for the particular flow since the change tracking history is not completely available anymore. You’ll receive the following infolog in AX when running the scheduler job:

The change tracking information related to scheduler job and data group are only partially available, due to the AX database change tracking retention period setting. A complete snapshot of data will be generated instead.

Be careful with this… your target system might only be designed to receive changes and not the full load. What if your 1040 – Product flow would ship out its 150.000 items in the channel’s assortment… So be aware of this function when designing your specific interface flows.

Another point to consider is the ability to trigger a full sync manually. In AX 2012 R2 we had our A-jobs (forcing to synch changes only) and N-jobs (forcing a full sync). In R3 the N-jobs seem to have been removed from the scene, but that’s not really the case. In fact, a full data sync can still be triggered from the Retail > Setup > Channel integration > Channel database form:


So train your system administrators to handle these features in a way which suits your interface designs.

Change tracking on Async client and Channel database

As mentioned in my main blog post regarding Dynamics AX Retail 3rd party sales integration, the following conditions should be met before BizTalk actually starts doing something:

• [Async client meta database].dbo.DownloadSession.JobId = [Job ID one of the BizTalk applications is designed for to process
• [Async client meta database].dbo.DownloadSession.Status = ‘4’ (=Applied)

This might for example be the actual situation in the Async client database:


As each job ID will trigger a different output flow to the 3rd party system, the job IDs are coupled with a specialized BizTalk process each. However, to avoid having them all polling on the dbo.DownloadSession, a generic BizTalk process is taking care of that task. It’s this process that triggers another BizTalk process to handle the respective Job ID subsequently. In order for this generic BizTalk process to keep track of the records it has triggered specific BizTalk processes for, change tracking is enabled for the dbo.DownloadSession table. So after each query cycle, BizTalk writes the last change tracking version for the dbo.DownloadSession table into its own local administration. So at each cycle new records can easily be identified and validated. Filtering the records by both the criteria mentioned above and change tracking is done by stored procedure.

A similar mechanism has been applied to the channel database. Let’s take an example to illustrate that. Say we have two custom scheduler jobs in AX with job IDs 8000 and 8010 respectively. These jobs ship data for the following tables:


But only the following tables are supposed to eventually trigger output to be shipped to the 3rd party system:


Say that we update 1 record in table 1 within AX and 1 record in table 6 within AX. We run both 8000 and 8010 jobs for our channel, so we have CDX shipping the updates over to the related channel database and writing meta data records into the related Async client database. Consequently, middleware (in this example: BizTalk) kicks in to process the data to 3rd party. The full flow of things in this layer is pictured in the following overview:

BizTalk process #1 spots new finished job runs (status = 4) for both flows and triggers BizTalk processes #2 and #3 to figure out if there’s actual data to be shipped to the 3rd party system. Similar to the Async client change tracking mechanism, BizTalk processes #2 and #3 inquire the latest change tracking versions for their respective job runs. For both job runs this is #9000 in this example, but it could have easily been different for each of the flows.

So BizTalk process #2 runs a stored procedure to only have a recordset returned which addresses all changes later than version #9000 and changes to table 1 only. Example:

In this example, biztalk.[PMOCHANNELITEMSAVAILABILITVIEW] returns the dataset which is the baseline for the output to the 3rd party system. The standard AX/Retail table ax.RETAILINVENTAVAILABILITY is indicated as the CHANGETABLE (=table 1 in our example) and @LastSyncVer indicates the last change tracking version for this job run (version #9000 in our example). So if this stored procedure returns any data, then BizTalk process #2 will have work to do to transform and hand over the data to the 3rd party system. After successful processing, BizTalk process #2 registers a combination of Job ID and last processed change tracking version (#9001) in its administration to avoid any duplicate output in next processing cycles.

Process #3 has still the job ID 8010/change tracking version #9000 combination in his administration. So the same original change to table 1 can still trigger process #3 to process his output for the 3rd party system.

I hope this blog has given you a better insight of how you can trigger a middleware solution on top of standard AX/Retail CDX.

Happy DAX’ing!
Patrick


Het bericht Dynamics AX Retail 3rd party POS and E-commerce integration – Trigger mechanism verscheen eerst op Patrick Mouwen.



Источник: http://patrickmouwen.com/technicalin...ger-mechanism/