Tuesday, May 14, 2013

Microsoft Dynamics Connector in Master Data Management Scenario

Microsoft Dynamics Connector in Master Data Management Scenario
 
Purpose: The purpose of this document is to explain what Master Data Management capabilities are provided by Microsoft Dynamics Connector. Please note that in the previous articles I described how to implement Master Data Management scenario for Microsoft Dynamics using Microsoft SQL Server 2012 Master Data Services.
 
Challenge: There is number of tools and services available for Microsoft Dynamics AX 2012 and Microsoft Dynamics CRM 2011 which have Master Data Management capabilities. The challenge is to understand usage scenarios and how to apply them properly. Microsoft Dynamics Connector allows you to synchronize master data between Microsoft Dynamics AX 2012 and Microsoft Dynamics CRM 2011.
 
Solution: Microsoft Dynamics Connector is an integration solution that is targeted specifically at the Microsoft Dynamics family of products, and can integrate data between Microsoft Dynamics CRM and other Microsoft Dynamics ERP databases. This tool is intended for an implementation where Microsoft Dynamics CRM is used to manage business contacts, track leads, enter sales orders, and perform other sales and marking activities, and where the Microsoft Dynamics ERP system is used to perform accounting functions, manage a company’s chart of accounts, and maintain customer, vendor, item, employee, and other records.
 
Scenario
 
Microsoft Dynamics Connector can be used in some Master Data Management scenarios when Microsoft Dynamics CRM business data has to be synchronized with Microsoft Dynamics AX business data. For the sake of simplicity in this document I’ll consider usual Master Data Management entities such as Customers and Products.
 
Walkthrough
 
For the purposes of this walkthrough I installed Microsoft Dynamics Connector (for AX) on Microsoft Dynamics AX 2012 client machine. In this scenario I’ll be using Microsoft Dynamics CRM Online, so I also subscribed to free trial of Microsoft Dynamics CRM Online here: http://www.microsoft.com/en-us/dynamics/crm-free-trial-overview.aspx
 
Now let’s set up an integration between Microsoft Dynamics CRM 2011 and Microsoft Dynamics AX 2012 in Microsoft Dynamics Connector
 
New Integration
 
 
Please note that Microsoft Dynamics Connector ships with number configuration templates and “Dynamics AX 2012/Dynamics CRM 2011” is one of them
 
As the result new integration will be created
 
Connector for Microsoft Dynamics
 
 
Under Maps node you will see the number of standard maps defined as a part of Microsoft Dynamics AX 2012 / Microsoft Dynamics CRM 2011 template. In particular for this walkthrough we are interested in the following standard maps
<![if !supportLists]>-          <![endif]>Account to Customer Service
<![if !supportLists]>-          <![endif]>Customer Service to Account
<![if !supportLists]>-          <![endif]>Item Service to Product
 
Now it’s time to configure adapters so we can connect to the instances of Microsoft Dynamics AX 2012 and Microsoft Dynamics CRM Online. Let’s start with Microsoft Dynamics AX 2012 Adapter
 
Adapter Settings
 
 
Here you have to specify AOS Server Name, Document Port, Services Port, Domain, User Name and Password. After that in order to configure specific Services in Microsoft Dynamics AX 2012 for their use in Microsoft Dynamics Connector you will have to click on “Configure Microsoft Dynamics AX 2012”
 
Microsoft Dynamics AX 2012 Adapter Configuration
 
 
Please note that DynamicsConnectorDefaultServices Services group is selected by default because it supports Microsoft Dynamics Connector standard maps. Consequently there’s a dedicated Inbound port in Microsoft Dynamics AX 2012 which contains all these Web Services
 
Inbound ports
 
 
Please note that as a part of Microsoft Dynamics Connector (for AX) installation DynamicsConnector XPO gets installed which has the definitions of all required Web Services
 
Project
 
 
Now let’s switch to configuration of Adapter for Microsoft Dynamics CRM 2011
 
Adapter Settings
 
 
Please note that I selected Microsoft Dynamics CRM 2011 Adapter for configuration, and NOT Microsoft Dynamics CRM Adapter, because the latter one is obsolete. Please also note that you have to provide Integration account credentials which is different from Administrator account credentials, that’s why I created brand-new integration@usmf.onmicrosoft.com user in Microsoft Dynamics CRM Online
 
Microsoft Dynamics CRM 2011 Adapter Configuration - Welcome
 
 
At this point we have to provide
 
Microsoft Dynamics CRM 2011 Adapter Configuration – Get Organizations
 
 
Please note that here you have to provide Administrator account credentials, and then you will be able to retrieve the list of available organizations. In my case I have only one available, so I’ll select “Microsoft”
 
Microsoft Dynamics CRM 2011 Adapter Configuration – Entities
 
 
By default those entities which support standard maps in Microsoft Dynamics Connector will be selected for configuration, however you can select more entities in the list for configuration if you plan to create your own maps. In this instance we’ll be good with standard entities for Account and Product which are selected by default
 
Microsoft Dynamics CRM 2011 Adapter Configuration – Progress  
 
 
Then the system will go ahead and configure selected entities for Microsoft Dynamics Connector
 
Okay, now let’s review the details of maps to find out about what data elements will be synchronized between Microsoft Dynamics AX 2012 and Microsoft Dynamics CRM 2011. For example, looking at “Account to Customer Service” map we’ll see particular fields to be synchronized as well as different functions which the system will apply for data transformation 
 
Account to Customer Service map
 
 
Customer Service to Account map
 
 
Item Service to Product map
 
 
Please note that by default the system Microsoft Dynamics Connector only provides one map for Products which allows you to synchronize Products data from Microsoft Dynamics AX 2012 to Microsoft Dynamics CRM which makes sense taking into account the fact that Microsoft Dynamics CRM is a Customer Relationship Management system and products management activities are usually done within ERP system
 
Please see more details about standard maps in Microsoft Dynamics Connector related to Customers and Products below 
 
CRM Account to AX Customer map
 
 
Please note that Microsoft Dynamics Connector also allows you to use a modeling language in order to accommodate for different types of data transformation scenarios
 
 
 
CRM Account to AX Customer map - Table
 
CRM Field
AX Field
Blocked
If(Credit Hold, “Invoice”, “No”)
Credit Max
Credit Limit\Value
Currency
Currency\Dynamics Integration Key
Cust Group
Account Category\Name
DAX Integration Id
If(IsEntityReferenceKey(Dynamics Integration Key), GetEntityReferenceKeyIdAsString(Dynamics Integration Key), Concatenate("{", Account ID, "}"))
Dlv Term
Address 1 Freight Terms\Name
Main Contact Personnel Number
Owning User\Dynamics Integration Key
Memo
Description
Paym Term Id
Payment Terms\Name
 
AX Customer to CRM Account map
 
 
AX Customer to CRM Account map - Table
 
AX Field
CRM Field
Account Name
GetPropertyFromInstance(Organization, 0, “Name”)
Account Number
Account Num
Address 1 Dynamics Integration Key
GetPrimaryAddressId(Organization)
Address 2 Dynamics Integration Key
GetSecondaryAddressId(Organization, 1)
Credit Hold
If(EqualTo(Credit Max, 0), true, false)
Description
Memo
Do not allow Bulk E-mails
False
Do not allow Bulk Mails
False
Do not allow E-mails
False
Do not allow Faxes
False
Do not allow Mails
False
Do not allow Phone Calls
False
Dynamics Integration Key
CreateEntityReferenceKey(Account Num, DAX Integration Id)
E-mail
GetPrimaryCommunicationForType(Organization, “Email”)
E-mail Address 2
GetCommunicationForType(Organization, “Email”)
E-mail Address 3
GetCommunicationForCustomerType(Organization, “Email”, 2)
Fax
GetPrimaryCommunicationForType(Organization, “Fax”)
Main Phone
GetPrimaryCommunicationForType(Organization, “Phone”)
Other Phone
GetCommunicationForType(Organization, “Phone”)
Ready for Integration
True
Send Marketing Materials
False
Web Site
GetPrimaryCommunicationForType(Organization, “URL”)
 
AX Item to CRM Product map
 
 
AX Item to CRM Product map - Table
 
AX Field
CRM Field
Dynamics Integration Key
Item Id
ID
Item Id
Product Name
Name Alias
Decimals Supported
2
Is Kit
IsKit(BOM Version)
Quantity On Hand
If(Or(EqualTo(GetTotalAvailable(InventSum), DefaultValue()), LessThan(GetTotalAvailable(InventSum), 0)), 0, GetTotalAvailable(InventSum))
Stock Volume
Unit Volume
Stock Weight
Net Weight
Vendor
Primary Vendor Id
Components
GetBomLines(BOM Version)
Default Unit
 
Substitutes
GetArrayOfDictionary(“dynamics_integrationkey”, Alt Item Id)
Unit Group
 
 
Now we can compare Microsoft Dynamics Connector maps with standard import Excel templates in Microsoft Dynamics CRM 2011
 
Account - Fields
 
 
Account – Import Template (Table)
 
Table Name
Field Name
Data Type
Maximum Length/Value
 
Account Name
Text
160
 
Account Number
Text
20
 
E-mail
Text
100
 
Parent Account
Lookup
 
 
Primary Contact
Lookup
 
 
Owner
Lookup
 
 
Main Phone
Text
50
 
Other Phone
Text
50
 
Fax
Text
50
 
Web Site
Text
200
 
Relationship Type
Option Set
 
 
Account Manager
Lookup
 
 
Territory
Lookup
 
 
Category
Option Set
 
 
Industry
Option Set
 
 
Annual Revenue
Decimal Number
0 .. 100000000000000
 
No. of Employees
Whole Number
0 .. 1000000000
 
Ownership
Option Set
 
 
Ticker Symbol
Text
10
 
Description
Text
200
 
Address Name
Text
200
 
Street 1
Text
250
 
Street 2
Text
250
 
Street 3
Text
250
 
City
Text
80
 
State/Province
Text
50
 
ZIP/Postal Code
Text
20
 
Country/Region
Text
80
 
Currency
Lookup
 
 
Managing Partner
Lookup
 
 
Originating Lead
Lookup
 
 
Preferred Method of Contact
Option Set
 
 
Do not allow E-mails
Option Set
 
 
Do not allow Phone Calls
Option Set
 
 
Do not allow Mails
Option Set
 
 
Do not allow Bulk E-mails
Option Set
 
 
Do not allow Faxes
Option Set
 
 
Send Marketing Materials
Option Set
 
 
Credit Limit
Decimal Number
0 .. 100000000000000
 
Credit Hold
Option Set
 
 
Payment Terms
Option Set
 
 
Price List
Lookup
 
 
Account Rating
Option Set
 
 
SIC Code
Text
20
 
Preferred Service
Lookup
 
 
Preferred Facility/Equipment
Lookup
 
 
Preferred User
Lookup
 
 
Preferred Day
Option Set
 
 
Preferred Time
Option Set
 
 
Account – Fields (Details)
 
 
Account – Relations (1-N)
 
 
Account – Relations (N-1)
 
 
Account – Relations (N-N)
 
 
Products – Fields
 
 
Products – Import Template (Table)
 
Table Name
Field Name
Data Type
Maximum Length/Value
 
ID
Text
100
 
Product Name
Text
100
 
Subject
Lookup
 
 
Unit Group
Lookup
 
 
Default Unit
Lookup
 
 
Currency
Lookup
 
 
Product Type
Option Set
 
 
Quantity On Hand
Decimal Number
0 .. 100000000000000
 
URL
Text
255
 
Decimals Supported
Whole Number
0 .. 5
 
List Price
Decimal Number
0 .. 100000000000000
 
Standard Cost
Decimal Number
0 .. 100000000000000
 
Current Cost
Decimal Number
0 .. 100000000000000
 
Vendor
Text
100
 
Vendor Part Number
Text
100
 
Stock Weight
Decimal Number
0 .. 100000000000000
 
Stock Volume
Decimal Number
0 .. 100000000000000
 
Description
Text
2000
 
Product – Fields (Details)
 
 
Product – Relations (1-N)
 
 
Product – Relations (N-1)
 
 
Product – Relations (N-N)
 
 
You can also review standard entity templates for Customers and Products available in Data Import Export Framework in Microsoft Dynamics AX 2012 here: http://ax2012aifintegration.blogspot.com/2013/05/microsoft-dynamics-ax-2012-data-import.html
 
As you can see Microsoft Dynamics Connector standard maps can be effectively used to synchronize master data between Microsoft Dynamics AX 2012 and Microsoft Dynamics CRM 2011. Please note that standard maps in Microsoft Dynamics Connector provide a quick start for you and you can modify them as required
 
Summary: This document describes how to use Microsoft Dynamics Connector in Master Data Management scenarios for Microsoft Dynamics AX 2012 and Microsoft Dynamics CRM 2011. In particular I explained how to configure an integration in Microsoft Dynamics Connector as well as define a map for particular types of business data which includes Customers and Products. Microsoft Dynamics Connector provides a robust and extensible platform for data synchronization in Master Data Management scenario for Microsoft Dynamics AX 2012 and Microsoft Dynamics CRM 2011 which allows us to synchronize records, do any kinds of data transformations, etc. as required. Please note that Microsoft Dynamics Connector is specifically designed to integrate Microsoft Dynamics CRM with Microsoft Dynamics ERP and it doesn’t provide full-fledged Master Data Management capabilities (Content, Relationship, Access, Change, Process), that’s why please review my other articles on how to implement Master Data Management scenario for Microsoft Dynamics using SQL Server Master Data Services.   
 
Tags: Dynamics AX 2012, Microsoft Dynamics CRM 2011, Dynamics Connector, Master Data Management, Customers, Accounts, Products, Items.
 
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

3 comments:

  1. hi
    i did the same steps which u showed but after create new account "Axconnector" in CRM and run the account to customer service mapping, error log giving me below error

    ERROR TID:TP-16 [2014-01-09T01:33:36.6021753+05:00]: [Account to Customer Service] has encountered an error while reading records. Processing will be aborted. Error text: A record level exception has occurred for record [Axconnector, a152391b-a378-e311-80d1-001e0bc9f1b2]. Error text : Requested value 'Axconnector' was not found.
    --- Exception Dump ---
    Caught Exception: [Microsoft.Dynamics.Integration.Service.Tasks.RecordException] A record level exception has occurred for record [Axconnector, a152391b-a378-e311-80d1-001e0bc9f1b2]. Error text : Requested value 'Axconnector' was not found.
    Stack trace:


    Inner Exception: [System.ArgumentException] Requested value 'Axconnector' was not found.
    Stack trace:
    at System.Enum.TryParseEnum(Type enumType, String value, Boolean ignoreCase, EnumResult& parseResult)
    at Microsoft.Dynamics.Integration.Adapters.AX.Common.ExtensionMethods.SetFieldValue(FieldDefinition fieldDef, Object newValue, Object target)
    at Microsoft.Dynamics.Integration.Adapters.AX.Common.ExtensionMethods.UpdateComplexInstance(FieldDefinition complexFieldDef, Dictionary`2 sourceValues, Assembly targetAssembly, Object instance, Boolean isNew)
    at Microsoft.Dynamics.Integration.Adapters.AX.Common.ExtensionMethods.CreateComplexInstance(FieldDefinition complexFieldDef, Dictionary`2 sourceValues, Assembly targetAssembly, Boolean isNew)
    at Microsoft.Dynamics.Integration.Adapters.AX2012.DynamicServiceObjectProviderBase.WriteObject(Object value)
    at Microsoft.Dynamics.Integration.Service.Tasks.RecordWork.DoRecordWork(DbMap map, Object key, OperationType operationType, DateTime startTime, String loggingSourceName)

    ReplyDelete
  2. Microsoft Dynamics AX is best accounting software it really does help save a lot of bucks of the shelf.

    ReplyDelete