Wednesday, May 8, 2013

Microsoft Dynamics AX 2012 Master Data Management Scenario (Part 2)

Microsoft Dynamics AX 2012 Master Data Management Scenario (Part 2)
 
Purpose: The purpose of this document is to provide a walkthrough of how implement Master Data Management scenario for Microsoft Dynamics AX 2012. Please note that in the previous article I described how to do initial configuration of Microsoft SQL Server 2012 Master Data Services for Microsoft Dynamics Master Data Management scenario.
 
Challenge: The pain that organizations are experiencing around data accuracy, consistent reporting, regulatory compliance, etc. generated a lot of interest in Master Data Management. Master Data Management becomes extremely important when you have multiple different systems and you have to manage the data in different systems, each system may potentially have its own version of the same data which means not only to duplication of the data but also to data inconsistencies and data conflicts. Master Data Management is a solution in this case with ultimate goal to have a single version of the truth in data terms. The main functions of Master Data Management are content management, relationship management, access control, change management and data processing.
 
Solution: In order to resolve Master Data Management challenges Microsoft offers an excellent solution which is based on Microsoft SQL Server platform. SQL Server Master Data Services provides a central data hub that ensures the integrity of information and consistency of data is constant across different applications. In this document I’ll explain how to build a solid Master Data Management solution leveraging Microsoft SQL Server, SQL Server Master Data Services and SQL Server Integration Services.  
 
Scenario
 
Microsoft Dynamics AX 2012 and Microsoft Dynamics CRM 2011 have some built-in Master Data Management capabilities, however in this document I’ll focus on a more generic scenario when we have not only Microsoft Dynamics products in play but also some other systems. For the sake of simplicity in this document I’ll consider usual Master Data Management entities such as Customers and Products.
 
Walkthrough
 
In this walkthrough I’m using Microsoft Dynamics AX 2012 R2 Demo VM (AX2012R2A) which has Microsoft SQL Server 2012 installed. Once all appropriate infrastructural components are installed and configured we’ll have to consume the data which is centrally managed in Master Data Services and synchronize it to Microsoft Dynamics AX 2012.  In particular I’ll implement SSIS project which will grab the data from Master Data Services using Subscription Views and synchronize the data into Microsoft Dynamics AX 2012 using Web Services.
 
Let’s get started!
 
One of prerequisite steps before we will be able to create Customer with specific Account number in Microsoft Dynamics AX 2012 would be to change Number sequence setting for Account number to Allow user changes 
 
Number sequences – Account number
 
 
The data from Master Data Services will be synchronized into Microsoft Dynamics AX 2012 by means of Web Services, that’s why we need to activate appropriate Inbound ports. In this instance I manually created Enhanced Inbound port “DynamicsServices”. Please note that Adapter = HTTP 
 
Inbound ports
 
 
And included all required Web Services operations for Customer account and Product definition creation
 
Select Service operations
 
 
Now we can review Configuration details associated with Inbound port. In this instance we’ll be interested in using basicHttpBindingWithWindowsAuth binding with HTTP Adapter
 
Configuration (Configure)
 
 
Configuration (Configure AOS)
 
 
Once Inbound port is activated you can also review the details of deployed Web Service. RoutineService Service gets deployed when you use HTTP Adapter
 
RoutingService Service
 
 
You can also review the details of WSDL associated with Web Service
 
WSDL
 
 
Now once we have all infrastructure ready from Microsoft Dynamics AX 2012 side we can start with Integration Services Visual Studio project
 
Visual Studio – Dynamics AX
 
 
The first step would be to add Data Flow Task which will contain actions to grab the data from Master Data Services and synchronize it to Microsoft Dynamics AX 2012 by means of Web Service call
 
Data Flow Task
 
 
Within Data Flow Task I’ll add OLE DB Source component which will help me to grab the data from Master Data Services using Subscription View
 
OLE DB Source
 
 
Now I’ll have to add OLE DB Connection and specify the location of MDS database, name of Subscription View, etc. I assume that Integration User who will be accessing business data in Master Data Services through Subscription View will have appropriate permissions set up in Master Data Services 
 
Configure OLE DB Connection Manager
 
 
In this instance I’ll use Windows Integrated authentication and access MDS database on the same server
 
Connection Manager - Connection
 
 
Configure OLE DB Connection Manager
 
 
Now LocalHost.MDS OLE DB Connection Manager has been created and I can proceed to the next step
 
Connection Manager
 
 
Next step would be to define where I’ll grab Customer and Product data from in Master Data Services database. And this is what I will specify below by referring to “DynamicsCustomer” Subscription View in MDS database
 
OLE DB Source Editor – Connection Manager
 
 
Having appropriate permissions now you can see Customer data exposed from Master Data Services by means of Subscription View in external application 
 
Preview Query Results - Customer
 
 
I’ll select all available columns to have access to all Customer data
 
OLE DB Source Editor – Columns
 
 
OLE DB Source Editor – Error Output
 
 
Similarly we can set up OLE DB Source component to read Product data from Master Data Services
 
OLE DB Source Editor – Product
 
 
Preview Query Results – Product
 
 
OLE DB Source Editor – Columns
 
 
OLE DB Source Editor – Error Output
 
 
Now OLE DB Source component is set up to read the data from Master Data Services
 
OLE DB Source – Done
 
 
And the next step is to add Script component which will help us to take business data from OLE DB Source component and synchronize it to Microsoft Dynamics AX 2012 by means of Web Services. I’ll drag and drop Script component to the canvas and select Component Type = Transformation
 
Select Script Component Type
 
 
Now we have to configure Script component
 
Script Component
 
 
But before doing this we’ll connect OLE DB Source component and Script component to pass the data through
 
OLE DB Source + Script Component
 
 
Next step is to configure Script Component
 
Script Transformation Editor – Script
 
 
For the sake of simplicity what I really need is just a Name (Customer Name or Product Name) 
 
Script Transformation Editor – Input Columns
 
 
Script Transformation Editor – Inputs and Outputs
 
 
Script Transformation Editor – Connection Managers
 
 
Finally I’ll click Edit Script button in order to implement the code which will read the business data and invoke Microsoft Dynamics AX 2012 Web Service for data synchronization. This is how the project looks like initially
 
VstaProjects – C#.NET project
 
 
Now as usually we can add Web Service Reference to C#.NET project
 
Add Service Reference – CustomerService
 
 
Add Service reference - EcoResProductService
 
 
Once Web Service Reference is added C#.NET project will look like this. Please note that I added using XYZ.DynamicsAX; statement in Namespaces region
 
VstaProjects – C#.NET
 
 
Now let’s review the simple implementation of Microsoft Dynamics AX 2012 Web Service call in SSIS Script Component C#.NET project  
 
Source code - Customer
 
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using SC_9bef93d9b7ca45bd9f9d44ac25c89d32.DynamicsAX;
 
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    CustomerServiceClient client;
    CallContext context;
    AxdCustomer customer;
    EntityKey[] entityKey;
 
    public override void PreExecute()
    {
        base.PreExecute();
 
        client = new CustomerServiceClient();
        context = new CallContext();
        customer = new AxdCustomer();
        customer.CustTable = new AxdEntity_CustTable[1];
    }
 
    public override void PostExecute()
    {
        base.PostExecute();
 
        try
        {
            entityKey = client.create(context, customer);
        }
        catch (Exception e)
        {
            throw;
        }
    }
 
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {       
        customer.CustTable[0] = new AxdEntity_CustTable();
        customer.CustTable[0].AccountNum = "Dynamics";//Row.Name
        customer.CustTable[0].CustGroup = "10";
    }
}
 
 
For the sake of simplicity here I just process 1 single record, that’s why I hard-coded array element “0” in Input0_ProcessInputRow method which will be executed for every Customer record retrieved 
 
Source code – Product
 
<TBD>
 
Finally you will Rebuild the project and save changes to SSIS Script Component. And now we’ll be ready to execute SSIS package
 
VstaProjects – C#.NET
 
 
I tried to execute this in Debug mode first and I got the following Error
 
Script Component: Runtime Error
 
 
The reason why I got this error is because SSIS Runtime doesn’t know about “DynamicsAX.CustomerService” Web Service configuration yet. In order to provide Web Service configuration details I’ll have to modify the following files in …\Microsoft SQL Server\110\DTS\Binn folder
<![if !supportLists]>-          <![endif]>DTExec.exe.config
<![if !supportLists]>-          <![endif]>DTSDebugHost.exe.config
<![if !supportLists]>-          <![endif]>DTSHost.exe.config
 
File system
 
 
You can simply add Web Service Reference into fake C#.NET Console app project first and then copy <configuration> section from App.config into files specified above 
 
Config: App.config
 
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <system.serviceModel>
        <bindings>
            <basicHttpBinding>
                <binding name="reqReplyEndpoint">
                    <security mode="TransportCredentialOnly">
                        <transport clientCredentialType="Windows" />
                    </security>
                </binding>
                <binding name="BasicHttpBinding_CustomerService">
                    <security mode="TransportCredentialOnly">
                        <transport clientCredentialType="Windows" />
                    </security>
                </binding>
                <binding name="BasicHttpBinding_EcoResProductService">
                    <security mode="TransportCredentialOnly">
                        <transport clientCredentialType="Windows" />
                    </security>
                </binding>
            </basicHttpBinding>
        </bindings>
        <client>
            <endpoint address="http://ax2012r2a.contoso.com:89/MicrosoftDynamicsAXAif60/DynamicsServices/xppservice.svc"
                binding="basicHttpBinding" bindingConfiguration="reqReplyEndpoint"
                contract="DynamicsAX.IRequestReplyRouter" name="reqReplyEndpoint" />
            <endpoint address="http://ax2012r2a.contoso.com:89/MicrosoftDynamicsAXAif60/DynamicsServices/xppservice.svc"
                binding="basicHttpBinding" bindingConfiguration="BasicHttpBinding_CustomerService"
                contract="DynamicsAX.CustomerService" name="BasicHttpBinding_CustomerService" />
            <endpoint address="http://ax2012r2a.contoso.com:89/MicrosoftDynamicsAXAif60/DynamicsServices/xppservice.svc"
                binding="basicHttpBinding" bindingConfiguration="BasicHttpBinding_EcoResProductService"
                contract="DynamicsAX.EcoResProductService" name="BasicHttpBinding_EcoResProductService" />
        </client>
    </system.serviceModel>
</configuration>
 
 
So now I’ll add configuration details into these files:  DTExec.exe.config, DTSDebugHost.exe.config and DTSHost.exe.config
 
Config: DTExec.exe.config
 
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0"/>
  </startup>
  <system.serviceModel>
    <bindings>
        <basicHttpBinding>
            <binding name="reqReplyEndpoint">
                <security mode="TransportCredentialOnly">
                    <transport clientCredentialType="Windows" />
                </security>
            </binding>
            <binding name="BasicHttpBinding_CustomerService">
                <security mode="TransportCredentialOnly">
                    <transport clientCredentialType="Windows" />
                </security>
            </binding>
            <binding name="BasicHttpBinding_EcoResProductService">
                <security mode="TransportCredentialOnly">
                    <transport clientCredentialType="Windows" />
                </security>
            </binding>
        </basicHttpBinding>
    </bindings>
    <client>
        <endpoint address="http://ax2012r2a.contoso.com:89/MicrosoftDynamicsAXAif60/DynamicsServices/xppservice.svc"
            binding="basicHttpBinding" bindingConfiguration="reqReplyEndpoint"
            contract="DynamicsAX.IRequestReplyRouter" name="reqReplyEndpoint" />
        <endpoint address="http://ax2012r2a.contoso.com:89/MicrosoftDynamicsAXAif60/DynamicsServices/xppservice.svc"
            binding="basicHttpBinding" bindingConfiguration="BasicHttpBinding_CustomerService"
            contract="DynamicsAX.CustomerService" name="BasicHttpBinding_CustomerService" />
        <endpoint address="http://ax2012r2a.contoso.com:89/MicrosoftDynamicsAXAif60/DynamicsServices/xppservice.svc"
            binding="basicHttpBinding" bindingConfiguration="BasicHttpBinding_EcoResProductService"
            contract="DynamicsAX.EcoResProductService" name="BasicHttpBinding_EcoResProductService" />
    </client>
  </system.serviceModel>
  <runtime>
    <gcServer enabled="true"/>
    <disableCommitThreadStack enabled="true"/>
    <generatePublisherEvidence enabled="false"/>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.ManagedDTS" publicKeyToken="89845dcd8080cc91" culture="neutral" />
        <bindingRedirect oldVersion="10.0.0.0" newVersion="11.0.0.0"/>
      </dependentAssembly>
    </assemblyBinding>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.DTSRuntimeWrap" publicKeyToken="89845dcd8080cc91" culture="neutral" />
        <bindingRedirect oldVersion="10.0.0.0" newVersion="11.0.0.0"/>
     </dependentAssembly>
    </assemblyBinding>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.DTSPipelineWrap" publicKeyToken="89845dcd8080cc91" culture="neutral" />
        <bindingRedirect oldVersion="10.0.0.0" newVersion="11.0.0.0"/>
      </dependentAssembly>
    </assemblyBinding>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.SqlServer.PipelineHost" publicKeyToken="89845dcd8080cc91" culture="neutral" />
        <bindingRedirect oldVersion="10.0.0.0" newVersion="11.0.0.0"/>
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>
 
When I try to run my SSIS package again now I’ll get “Package execution completed with success” message
 
Dynamics AX – Run package (Debug)
 
 
After successful execution of SSIS package we can verify the result in Microsoft Dynamics AX 2012. As the result we’ll have Customer (and Product) created in Microsoft Dynamics AX 2012
 
Dynamics AX – Customer
 
 
Dynamics AX – Product
<TBD>
 
Please note that for the sake of simplicity in current implementation of SSIS Script Component I only do creation of Customer (and Product). However you can obviously implement business logic to update existing Customer (and Product) if it already exists in Microsoft Dynamics AX 2012  
 
The very last step for us is to deploy SSIS package, so we can use it in production. For these purposes we’ll use Deployment Wizard
 
Deployment - Introduction
 
 
Deployment – Select Destination
 
 
After you select Destination you may get the following message
 
SQL Server Integration Services
 
 
The reason for it is because that you may not have SSISDB catalog created yet. And this is exactly the case in my scenario, so I’ll go ahead and create new SSISDB catalog
 
SQL Server Management Studio – Create Catalog
 
 
Create Catalog
 
 
As the result I’ll have Integration Services Catalog SSISDB created
 
SQL Server Management Studio – Integration Service Catalogs
 
 
Now I’ll create a folder for DynamicsAX
 
Integration Services Catalog Folder
 
 
Create New Folder
 
 
So now I have created DynamicsAX folder
 
Browse for Folder or Project
 
 
And I can proceed with deployment of my SSIS package
 
Deployment – Select Destination
 
 
Deployment - Review
 
 
Deployment - Results
 
 
Now I’m done with deployment of my SSIS package
 
SQL Server Management Studio
 
 
And I can export package/folder from Integration Service Catalog, import it into SSIS instance and set up for periodic execution
 
SQL Server Management Studio – Export folder
 
 
Or execute package right away
 
Execute package - Parameters
 
 
Execute package – Connection Managers
 
 
Execute package - Advanced
 
 
As the result you will be able to consistently manage Customer and Product master data in Master Data Services and continuously synchronize it into Microsoft Dynamics AX 2012 using SSIS package we have just created
 
Summary: This document describes required minimum implementation of SQL Server Integration Services package to synchronize master data from Master Data Services into Microsoft Dynamics AX 2012. In particular I explained the process of SSIS project creation, design, development and deployment to support Master Data Management scenario for Customers and Products. SQL Server Integration services provides a robust platform for data synchronization in Master Data Management scenario which allows us to create records, update records, do any kinds of data transformations, etc. as required.
 
Tags: SQL Server 2012, Dynamics AX 2012, Dynamics CRM Online, Master Data Management, Master Data Services, Data Quality Services, SQL Server Integration services, Customers, Products.
 
Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the issues and describe the solutions.
 
Author: Alex Anikiev, PhD, MCP

2 comments:

  1. Many organizations choose to go for a particular enterprise data management solutions because everyone in their industry is going for it.

    ReplyDelete
  2. This is such a great resource that you are providing and you it away for free. I love seeing blog that understand the value.Microsoft Dynamics Implementation

    ReplyDelete