axsa: MDM Adapter - Extending Dynamics AX 2012 R3 Master Data Management

[Blog bot]

Источник: http://blogs.msdn.com/b/axsa/archive...m-adapter.aspx
==============

AX 2012 R3 Master Data Management (MDM) subsystem features a set of powerful functionalities, such as data synchronization between AX instances, conflict management, single/multi master modes, policy based data filtering, etc. It provides a great platform to build a robust enterprise-level MDM strategy.

Out of box, R3 MDM supports managing master data among AX instances. This blog entry gives a simple example on how one can extend existing MDM functionalities to support managing master data between AX and non-AX systems. We have also included a sample app, attached to this post as zip, to demonstrate the topics covered(more details in Appendix). Since R3 MDM is built on top of DIXF, we assume readers have experience with DIXF and R3 MDM. If you don’t, you can learn and read about it here and here.

Disclaimer: design and code mentioned in this blog post are only for demonstrating purposes. Please do not use it in your production environment. This post only demonstrates one possible way to connect other systems with SQL Server MDS to leverage MDM functionalities built in AX 2012 R3.

First, let’s take a look at high level architecture of R3 MDM and the sample MDM adapter.



R3 MDM is built on top of DIXF. During data synchronization, synchronization batch job running on AOS communicates with DIXF service to create SSIS packages, which integrates data between MDS and AX DB. MDM adapter follows a similar pattern. It communicates with the same DIXF service to create SSIS packages, which performs integration between MDS and non-AX DB.

When AX client performs synchronization with MDS, it first exports local changes made since the last time synchronization was performed. It then imports changes from MDS. Import is also incremental such that only changed data gets imported back. During export, DIXF writes changes from target table into staging table and performs integration between staging table and MDS. During import, data first flow from MDS into staging table in AX. DIXF then moves the data from staging table to target table, as depicted by the diagram below.



When MDM adapter performs synchronization with MDS, it only integrate data to and from staging table. During export, a staging writer is responsible for writing change data from target table into staging table. MDM adapter then integrates the data from staging table to MDS. During import, MDM adapter integrates data from MDS to staging table. An entity writer is responsible for moving data from staging table to target table.

Data synchronization sequence



As mentioned before, MDM adapter follows very similar pattern for performing data synchronization between MDS and non-AX DB. It follows these steps.

1. Call StagingWriter to gather all the changes took place since last sync and write them into the staging table.
2. Call DMFEntityProxy::CreateAndExecuteExportPackageToMDS to generate SSIS packages that pushes data from staging table to MDS.
3. Call DMFEntityProxy::CreateAndExecuteImportPackageFromMDS to generate SSIS packages that pulls data from MDS to staging table.
4. Call CleanUpStaging to remove imported records that conflict with local changes.
5. Call EntityWriter to write data from staging table to target tables.

Set up and configuration

• Make sure the user account running DIXF web service has access to non-AX DB.
• On the machine where DIXF service is running, make sure port 7000 is open for inbound traffic.
• Create staging tables in the non-AX DB. They should be based on the DMF*Entity staging tables in AX. Non-AX staging table creation scripts can be generated from corresponding staging tables in AX. In the sample implementation, we are using the same DMF*Entity schema from AX as our staging table schema. The SSIS packages generated by DIXF looks for matching column names between MDS deployed schema and staging schema in the non-AX system, columns will be mapped for integration if their names match. So, as long as you match the column names in your non-AX staging table to the ones deployed in MDS, integration should happen properly. Your staging table can share exactly the same number of fields with the deployed DMF*Entity schema, or it could be a subset or a superset.
• The SSIS packages generated by DIXF calls the following three stored procedures in the target non-AX DB. Creation scripts can be generated from existing AX DB.
  • sp_GetNextRecId – called to get new RecId for generating DMF entities in staging tables. This stored proc uses a table called SystemSequences, which is used to keep track of RecIds generated for each table. Therefore, if sample implementation is used, a table named SystemSequences need to be created in the non-AX DB.
  • UpdateMDSVersionNumber – update the last AXVersion synchronized by the client. In MDS DB, a table, named AxVersionTable, tracks all the synchronization happened between all AOS instances and MDS.
  • UpdateMDSExtraVersionNumber – update the last MDS change tracking version synchronized by the client. This is for scenario where data steward makes changes directly in MDS.

Now, let’s look at the major components used in the data synchronization process. Most of the components used here share the same structure and functionality with their counterparts in AX. Since MDM adapter lives outside of AX, these concepts need to be implemented on top of the framework of your choice.

MDMSyncGroup

This is the same concept used in AX to keep track of groups of entities to perform synchronization for. Sync group name should be used to link MDMEntities back to the owning MDMsynGroup. This table needs to be created in the non-AX DB. Please refer to MDMEntity in AX for implementation details.

MDMEntity

MDMEntity is used to keep track of the following information related to data synchronization, exactly the same way it is used in AX R3 MDM. This table needs to be created in the non-AX DB. Please refer to MDMEntity in AX for implementation details.

• Sync group name
• Local change tracking version number
• MDS change tacking version number
• Synchronization type
• Name of the entity
• Name of the subscription view
• Conflict management related info
• Import definition group name
• Export definition group name
• Last exported execution id
• Last imported execution id

Staging tables

Besides the fields used to store master data information, the following fields also need to be tracked.

• Definition group name
• Execution id
• AXMDMCODE – this maps to the natural key on the corresponding entity. MDS uses this code to uniquely identify each record. Please refer to DMF*Entity::insert() in AX for implementation details.

Together with the information stored on MDMEntity, one can figure out which records in the staging table need to be moved from staging to target and which ones need to be exported from staging to MDS, exactly the same way the same concepts and mechanisms are used in AX R3 MDM. Please refer to DMF*Entity tables for details.

StagingWriter

StagingWriter writes into staging table the changes took place in target tables since the last sync . This is where the heavy lifting is done and things can get pretty complicated depending on the complexity of your data model. One could implement a simple time stamp based change tracking, where each modification is tagged with a timestamp, which is used to compare against last sync timestamp during synchronization. Or, as implemented in AX, SQL change tracking can be enabled on the target entity table and local change tracking version number should be stored on the MDMEntity that corresponds to the entity being exported. Please refer to DMFStagingWriter and MDMChangeTracking in AX for details on how to implement. For demo purposes, the sample implementation uses the simple timestamp approach.

EntityWriter

EntityWriter moves data in staging table to target tables. The execution id attached to each record in staging table acts as marker indicating which records to move for a given synchronization. Things should be pretty straight forward when there is a one to one mapping between staging and target entities. The complexity comes when one staging entity could be mapped to multiple target entities. Please refer to DMFEntityWriter in AX for details on how to implement.

DMFEntityProxy

Namespace: Microsoft.Dynamics.AX.Framework.Tools.DMF.ServiceProxy

Assembly:

\Program Files (x86)\Microsoft Dynamics AX\6.3\Client\Bin\Microsoft.Dynamics.AX.Framework.Tools.DMF.ServiceProxy.dll

Dependent assembly:

\Program Files (x86)\Microsoft Dynamics AX\6.3\Client\Bin\Microsoft.Dynamics.IntegrationFramework.dll

This class is the gateway to reaching all the rich functionality exposed by the DIXF service. The following sample code shows how to instantiate this class.

private static DmfEntityProxy CreateDmfEntityProxy()

{

DmfEntityProxy dmfEntityProxy = new DmfEntityProxy();

ServiceContractClient helperClient;

string configFilePath = ServiceReference.GetConfigFilePath(typeof(ServiceContractClient), "");

helperClient = (ServiceContractClient)ServiceReference.CreateServiceClient(typeof(ServiceContractClient), configFilePath);

dmfEntityProxy.ClientProxy = helperClient;

return dmfEntityProxy;

}

NOTE

Since this is the proxy that accesses the DIXF web service, the client configuration file, Microsoft.Dynamics.AX.Framework.Tools.DMF.ServiceProxy.dll.config, need to be included as part of your client application. This configuration file can be found under \Program Files\Microsoft Dynamics AX\6.3\Server\AxaptaDev\bin.

Functionalities exposed by DMFEntityProxy

• CreateAndExecuteExportPackageToMDS
• CreateAndExecuteImportPackageFromMDS
• GetListOfSubscriptionViewsForEntityInMDS
• GetListOfDMFEntitiesInMDS
• GetEntityNumberOfConflictsInMDS

Here’s some sample code on how to use these functionalities from your client application.

public static ICollection GetListOfSubscriptionViewsForEntityInMDS(string entityName)

{

ICollection subscriptionViews = new List();

DmfEntityProxy dmfEntityProxy = MdmImportExport.CreateDmfEntityProxy();

MDSClientConnectionObject connectionObj = CreateMDSClientConnectionObject();

try

{

subscriptionViews = dmfEntityProxy.GetListOfSubscriptionViewsForEntityInMDS(connectionObj, entityName);

}

catch (Exception e)

{

throw new ApplicationException(ex.Message, ex);

}

return subscriptionViews;

}

private static MDSClientConnectionObject CreateMDSClientConnectionObject()

{

MDSClientConnectionObject connectionObj = new MDSClientConnectionObject();

connectionObj.mdsWebServiceEndpoint = MDMCONFIGURATION.GetMDSWebServiceEndPoint();

connectionObj.mdsDatabaseName = MDMCONFIGURATION.GetMDSDatabase();

connectionObj.mdsDbServerName = MDMCONFIGURATION.GetMDSServer();

connectionObj.axDbServerName = MDMCONFIGURATION.GetNonAXServer();

connectionObj.axDatabaseName = MDMCONFIGURATION.GetNonAXDatabase();

return connectionObj;

}



Export

By using DMFMDMSSISPackageHelper, the export process sets up source and destination settings so that DIXF service knows where to pull data from and push data to. Here’s some sample code that uses DMFEntityProxy to export data from staging to MDS. It is based on the AX implementation in MdmAxToMdsExporter::createAndExecuteExportPackageToMDS.

private static string CreateAndExecuteExportPackageToMDS(MDMENTITY entity, string executionId)

{

string result = string.Empty;

if (entity != null)

{

DmfEntityProxy dmfEntityProxy = CreateDmfEntityProxy();

DMFMDMSSISPackageHelper dMFMDMSSISPackageHelper = new DMFMDMSSISPackageHelper();



string destinationTableName = string.Format("[stg].[{0}_Leaf]", entity.ENTITYTABLENAME); //entity.ENTITYTABLENAME is the name of the corresponding staging table, e.g., DMFCustomerEntity

string SSISCONNECTIONSTRING = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog={0};Data Source={1};";



dMFMDMSSISPackageHelper.SourceConnectionString = string.Format(SSISCONNECTIONSTRING, MDMCONFIGURATION.GetNonAXDatabase(), MDMCONFIGURATION.GetNonAXServer());

dMFMDMSSISPackageHelper.SourceTable = entity.ENTITYTABLENAME;

dMFMDMSSISPackageHelper.DestinationConnectionString = string.Format(SSISCONNECTIONSTRING, MDMCONFIGURATION.GetMDSDatabase(), MDMCONFIGURATION.GetMDSServer());

dMFMDMSSISPackageHelper.DestinationTable = destinationTableName;

dMFMDMSSISPackageHelper.MDSStagingStoredProcName = string.Format("[stg].[udp_{0}_Leaf]", entity.ENTITYTABLENAME);

dMFMDMSSISPackageHelper.DMFEntityName = entity.ENTITYNAME;

dMFMDMSSISPackageHelper.DMFStagingTableId = entity.ENTITYTABLEID; //This is used by PackageGenerator to call storeproc sp_GetNextRecId to get the next unique identifier for inserting entities into the staging table



dmfEntityProxy.UpdatePackageVariable(dMFMDMSSISPackageHelper, "DefinitionGroup", entity.EXPORTDEFINITIONGROUP);

dmfEntityProxy.UpdatePackageVariable(dMFMDMSSISPackageHelper, "ExecutionID", string.Format("{0}", executionId));

dmfEntityProxy.UpdatePackageVariable(dMFMDMSSISPackageHelper, "ImportType", IMPORTTYPEZERO);

dmfEntityProxy.UpdatePackageVariable(dMFMDMSSISPackageHelper, "LastSyncVersionNumber", entity.MDSSYNCVERSION);

dmfEntityProxy.UpdatePackageVariable(dMFMDMSSISPackageHelper, "MdsChangeTrackingVersionNumber", entity.MDSSYNCVERSIONEXT);

dmfEntityProxy.UpdatePackageVariable(dMFMDMSSISPackageHelper, "BatchTag", string.Format("{0}_{{{1}}}", System.Environment.MachineName.Substring(0, 11), Guid.NewGuid()));



result = dmfEntityProxy.CreateAndExecuteExportPackageToMDS(dMFMDMSSISPackageHelper);

}

return result;

}



Import

By using DMFMDMSSISPackageHelper, the import process sets up source and destination settings so that DIXF service knows where to pull data from and push data to. Import process should use DMFEntityProxy to import data from MDS to staging by calling DMFEntityProxy::createAndExecuteImportPackageFromMDS. Please refer to MdmMdsToAxImporter::createAndExecuteImportPackageFromMDS in AX for implementation details.

Summary

We’ve covered all the key architecture components and processes for implementing MDM adapter on top of the existing R3 MDM functionalities. Essentially, the idea is to re-create how AX manages data synchronization with DIXF service outside of AX. This approach takes advantage of all the existing functionalities exposed by the DIXF web service, giving developers a very quick way to integrate non-AX systems as part of the overall MDM solution.

While extending existing DIXF functionalities can integrate data to and from your staging table, the heavy lifting is done by your application specific StagingWriter and EntityWriter for moving data between staging table and your target tables. Based on the complexity of your system, one staging entity could be mapped to either one target entity or multiple target entities. Majority of your development time would probably end up being spent here.

The design documented here takes advantage of existing DIXF functionalities, but comes with the cost of flexibility. If you want more flexibility interacting with MDS, you can build directly on top of MDS. However, most of the concepts we talked about here, staging to/from target, version control, conflict management, data filtering, still apply.

Note

Due to limitation of DIXF, integration with other non-AX systems is limited to systems running on SQL server only. However, for systems running on other type of DB, such as Oracle, there is nothing preventing one from performing integration with SQL MDS by using custom SSIS packages. Most of the concepts covered in this blog post would still apply.

Appendix

Instructions on how to deploy the sample MDM adapter app.

Disclaimer: This sample app is only configured to perform synchronization on Customers. However, it should be quit straight forward to extend it to include support for additional entities. This sample code is only to demo concepts discussed in this blog post. It is not production quality and please do not use this code in your production environment.

Pre-requisites

• One box setup with AX 2012 R3, DIXF, MDS, and SQL
• R3 MDM enabled with at least one AX instance connected with MDS. Specific steps can be found here: MDM in Dynamics AX 2012 R3
• Customer is deployed to MDS from AX and synchronized
• Visual Studio 2012

Set up and configuration

• Download zip file attached to this post.
• Create sample database, NONAX_DB, by restoring from file NONAX_DB.bak found in the sample code zip file.
• Make sure the user account running DIXF web service has access to NONAX_DB.
• On the machine where DIXF service is running, make sure port 7000 is open for inbound traffic.
• Open the solution in visual studio and run. One can also run MDMSyncManager.exe from the bin\debug
• Click on Configure on menu bar to configure MDS and local DB related info.
• Click Test configuration is make sure the connection to MDS is working properly.
• Click Save to save changes and close the configuration form.

Please refer to the sample configuration screen below for a sample configuration.



Data synchronization

1. On sync group form, input sync group name and click Save.
   
2. Click on Entities to bring up the sync group detail form.
3. Input “1“ as sync sequence number, “Customer” as the entity name, and click Save.
   
4. On sync group form, click Sync to sync data between MDS and non-AX client.
   
5. Click Customer, customer form should pop up, displaying customers synced from MDS.

Conflict management

1. In AX, go to AR\Customers, edit one customer, change customer name, save change and sync changes to MDS.
2. In non-AX client customer form, modify the same customer, change to a different name and hit enter to confirm change, click save to save the change.
3. On sync group form, click “View conflict”, it should show no conflict, which is expected because we haven't sync yet.
4. Click Sync. After sync is done, click “View conflict”, it should show one conflict for customer.
   
5. Go to Excel master data add-in and resolve the conflict.
6. On sync group form, click Sync. One customer should be synced back from MDS and conflict should be resolved.

Data filtering

1. Create a subscription view in MDS for customers who belongs to a given CUSTGROUP, say 10.
2. On sync group form, click Entities.
3. On sync group details form, select an entity and click on Settings.
4. One can change synchronization type to be “Push and pull” or “Pull only”. To illustrate filtered pull, change subscription view to the view created in step 1. Save change.
   
5. In AX, make change to two customers, each belong to a different CUSTGROUP, say 10 and 20. Sync change to MDS.
6. On sync group form, click Sync, only one customer belong to CUSTGROUP 10 should be imported

Источник: http://blogs.msdn.com/b/axsa/archive...m-adapter.aspx