Dynamics AX 2012 Integration using AIF: July 2013

Tuesday, July 16, 2013

Inside Microsoft Dynamics AX 2012 Master Data Management Scenario

Inside Microsoft Dynamics AX 2012 Master Data Management Scenario

Purpose: The purpose of this document is to describe Master Data Management capabilities included in standard Microsoft Dynamics AX 2012 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 which have Master Data Management capabilities. The challenge is to understand usage scenarios and how to apply them properly. Please note that Master Data Management capabilities included in standard product may be enough for some of your scenarios.

Solution: Microsoft Dynamics AX 2012 does provide some out-of-the-box Master Data Management capabilities for number of business entities which we are going to review in this document.

Scenario

In this walkthrough for the sake of simplicity I’ll be focusing on 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 USMF company as a part of Demo data

Let’s first consider Customers. The customer business entity in Microsoft Dynamics AX 2012 has associated Global Address Book record which can be one of two types: Person or Organization

Global Address Book

Please note that in Maintain section on Global address book tab in the menu bar we have number of functions which may help us to manage Global address book records such as “Merge duplicates” and “Check for duplicates”

Check duplicates

Check duplicates function allows you to find similar Global address book records based on set of criteria such as Name, Phone, E-mail, State, City, ZIP/postal code. In this case I was looking for Global address book records also belonging to WA State

Maintain

After you identify duplicate Global address book records you can merge them by using “Merge records” function

Merge party records

On the dialog screen you will have to select Survivor party

All customers

Please note that you can also export information to Excel workbook from any list page in Microsoft Dynamics AX 2012

Now let’s look at Products. In Microsoft Dynamics AX 2012 you can manage products and product masters definitions separately from released products. Product and product master definitions are shared across all companies, but released products are typically only released to selected companies. This allows for centralized management of products within enterprise, at the same time you may want to create released product right away (decentralized approach) and the system will create appropriate product definition for you automatically

All products and product masters

Using centralized approach once you created product definition you will release it to one or more companies

Release products – Select products

Release products – Select companies

Product release session batch

Please note that you may want to execute release of products as a batch job

Ultimately when the product is released to particular company you will see it on Released products list page as shown below

Released products

Microsoft Dynamics AX 2012 also provides other data management capabilities applicable to any business entity or table. For example, Database log functionality allows you to log details of changes done to selected tables. Let’s see how we can apply Database log functionality to Customers and Products in Microsoft Dynamics AX 2012.

Logging database changes - Welcome

//Configuration key = Ledger basic

Logging database changes – Tables and fields

In this particular case I’ll select Customers

Logging database changes – Type of change

I’ll be also interested to log all changes made to Customer records

Logging database changes - Finish

By the result of Logging database changes wizard the following record will be created on Database log setup form

Database log setup

Now when you do any changes to Customer records they will be appropriately tracked

Database log - Overview

You can also review details of those changes including info who changed the data, when, and what exactly was changed (old value vs new value)

Database log - History

Please note that when you activate Database log you introduce certain performance overhead

Microsoft Dynamics AX 2012 also provides Electronic Signature feature which works in conjunction with Database log functionality. Let’s review how this works!

First off Electronic Signature feature first has to be enabled in License Configuration

License Configuration

Microsoft Dynamics AX 2012 provides number of standard templates for Electronic Signature controlled business entities. For example, BOM and Route

Electronic signature requirements

In this particular case I’ll activate Electronic Signature for BOM Activation process (“Activate BOM version”)

I’ll also need to get a certificate, designate approver and set up a password on Options form in Electronic Signature section

Options

When all preparation work is done we can now test Electronic Signature functionality out

I’ll go ahead and try to activate BOM version so I see “Sign document” dialog popped up

Sign document

Now I’ll provide approver password so I can successfully activate BOM version

Database log

When I’m done the fact of change will be logged in Database log as shown above

Signature review

You can also review detailed information regarding change including Signature information

In this document we reviewed just some of Master Data Management capabilities included in standard Microsoft Dynamics AX 2012

Summary: This document describes how to leverage existing Master Data Management capabilities included in standard Microsoft Dynamics AX 2012. In particular I explained how to use global address book and product management features related to Master Data Management, and some generic Master Data Management features such as Database log ad Electronic signature. Please review my other articles to learn how to implement Master Data Management scenario for Microsoft Dynamics using SQL Server Master Data Services.

Tags: Dynamics AX 2012, Master Data Management, Customers, Products, Global Address Book, Released Products, Database Log, Electronic Signature.

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

Microsoft SQL Server 2012 Data Quality Services

Microsoft SQL Server 2012 Data Quality Services

Purpose: The purpose of this document is to provide a walkthrough of how to leverage Microsoft SQL Server 2012 Data Quality Services in conjunction with 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. In fact what we would also love to do is to control the quality of the data and be able to perform data cleanse, data matching and other data quality related activities.

Solution: The data-quality solution provided by Data Quality Services (DQS) enables a data steward or IT professional to maintain the quality of their data and ensure that the data is suited for its business usage. DQS is a knowledge-driven solution that provides both computer-assisted and interactive ways to manage the integrity and quality of your data sources. DQS enables you to discover, build, and manage knowledge about your data. You can then use that knowledge to perform data cleansing, matching, and profiling. You can also leverage the cloud-based services of reference data providers in a DQS data-quality project.

Scenario

By now we have already implemented Master Data Management scenario using Microsoft SQL Server 2012 Master Data Services. The next step is to integrate Master Data Services with Data Quality Services to deliver end-to-end Master Data Management solution. 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 and Microsoft Dynamics CRM Online (free trial)

After you install Microsoft SQL Server 2012, Master Data Services and Data Quality Services (Server and Client components) you have to run Data Quality Server Installer to perform necessary post installation tasks by using dedicated installer which will create appropriate DQS databases, set up security, etc. In case you have multiple instances of Microsoft SQL Server installed please locate appropriate DQSInstaller.exe file in corresponding Microsoft SQL Server Binn folder

Please note that the default Microsoft SQL Server instance may be different from the desired (MSSQLSERVER) which is the case for Microsoft Dynamics AX 2012 R2 Demo VM (AX2012R2A), that’s why it is critical to run appropriate DQSInstaller.exe during DQS installation

Data Quality Server Installer

Once DQS installation is complete you can launch DQS Client

SQL Server Data Quality Services – Client

The next step will be to create Knowledge base for future data quality related activities. In particular I’ll be doing data matching trying to detect duplicates

Knowledge base management

Please note that number of domains are available for Knowledge base “DQS Data”. In particular I’ll be using “US – Last name” domain to detect duplicates based on last name of customers

Actual data matching we’ll perform in Microsoft Excel

That’s why first off we have to establish connection to MDS Server

Excel Add-in – Add new connection

Manage connections

Once connection has been established

Now we can use Master Data Explorer to open up appropriate Model (“Dynamics”)

In this scenario I’ll work with customers and at the moment I have one customer record in Customers table in MDS database

In Microsoft Excel I can introduce new (External) data as shown below

But before I publish this data to MDS I can perform data matching and find out if I have any duplicates or similar data based on criteria defined. For these purposes I’ll use Combine data feature which allows me to combine MDS data and External data in order to perform data matching activities

Combine data

I’ll select what data I want to combine and map the fields appropriately. Once this is done the system shows “who is who” in SOURCE field

When I have the data for analysis I can use Match data feature to perform data matching

Match data

Please note that for Name field I specify Domain = US – Last name, so the system will apply matching rules for the analysis based on Last name. The data will be clustered in accordance to similarity as shown below in CLUSTER_ID field

Finally I’ll Show Details to see the results of extended analysis

Please note that for External data the system assigned a score in SCORE field which shows degree of similarity within the cluster. It appears that “Alex Anikiiev” is 90% similar to “Alex Anikiev” which allows me to define and eliminate duplicate data before publishing this to MDS

You can also perform periodic data cleanse, data matching and other data quality related activities using MDS and DQS to ensure the quality of your master data

Summary: This document describes how to leverage integration of Master Data Services and Data Quality Services when implementing Master Data Management scenario for Microsoft Dynamics AX 2012 and Microsoft Dynamics CRM. In particular I explained how to use Data Quality services in order to perform data matching based on certain criteria. Microsoft SQL Server 2012 provides a robust platform for Master Data Management which not only includes Master Data Services, but also Data Quality Services, SQL Server Integration Services, etc. Master Data Services provides Web front-end as well as Excel Add-in which allows you to choose your favorite tool to perform Master Data Management activities.

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.

Author: Alex Anikiev, PhD, MCP

Monday, July 15, 2013

Data Import using Visual Studio Test Framework

<!--[if !mso]><![endif]

Data Import using Visual Studio Test Framework

Purpose: The purpose of this document is to illustrate how to use Visual Studio Test Framework to import data into Microsoft Dynamics AX 2012

Challenge: For the purposes of POC (Proof of Concept) or testing in you need to import business data into Microsoft Dynamics AX 2012 with minimum effort without knowing the specifics of the data model and business logic, you can do it via User Interface with help of Visual Studio Test Framework

Solution: Using Microsoft Visual Studio Test Project you can create a Coded UI Test that tests whether the User Interface for an application functions correctly. Coded UI Test performs actions on the user interface controls for an application and verifies that specified controls display the correct values. However in this scenario we’ll use coded UI test, specifically Data Driven Coded UI Test, to automate tedious manual task of entering the data into the system via User Interface

Diagram

Walkthrough: Data Driven Coded UI Test

Create Test project

TestProject1

Add Coded UI Test

Generate Code for Coded UI Test: Record actions, edit UI map or add assertions

Coded UI Test Builder

Start recording

Now we will go through the usual process of customer creation in Microsoft Dynamics AX 2012 while recording all the actions

You can pause recording and then resume it if required

Once done we’ll give a name to the method. For example, CustomerImport

You can record actions for more methods if required. For example, for import of vendors, etc

The system will generate all necessary code for you

Source code

public void CustomerImport()

{

#region Variable Declarations

WinMenuItem uICustomerMenuItem = this.UIMicrosoftDynamicsAXDWindow.UIHomeTabWindow.UINewGroup.UICustomerMenuItem;

WinEdit uINameEdit = this.UICustomer1usmfNewRecoWindow.UIItemWindow.UINameEdit;

WinClient uICustomergrouplookupbClient = this.UICustomer1usmfNewRecoWindow.UIItemWindow1.UICustomergrouplookupbClient;

WinCell uIItem10Cell = this.UIItem1usmfWindow.UIItemWindow.UIGridTable.UIRow1Row.UIItem10Cell;

WinButton uISaveandcloseButton = this.UICustomer1usmfNewRecoWindow.UIItemWindow2.UISaveandcloseButton;

#endregion

// Click 'Customer' menu item

Mouse.Click(uICustomerMenuItem, new Point(35, 40));

// Click 'Name' text box

Mouse.Click(uINameEdit, new Point(23, 0));

// Type 'Alex Customer' in 'Name' text box

uINameEdit.Text = this.CustomerImportParams.UINameEditText;

// Click 'Customer group lookup button' client

Mouse.Click(uICustomergrouplookupbClient, new Point(4, 8));

// Click '10' cell

Mouse.Click(uIItem10Cell, new Point(43, 11));

// Click 'Save and close' button

Mouse.Click(uISaveandcloseButton, new Point(66, 10));

}

[GeneratedCode("Coded UITest Builder", "10.0.40219.445")]

public class CustomerImportParams

{

#region Fields

/// <summary>

/// Type 'Alex Customer' in 'Name' text box

/// </summary>

public string UINameEditText = "Alex Customer";

#endregion

}

XML

<?xml version="1.0"?>

…

<IsGlobalHotkey>false</IsGlobalHotkey>

<ActionType>Click</ActionType>

</MouseAction>

<IsGlobalHotkey>false</IsGlobalHotkey>

<ActionType>Click</ActionType>

</MouseAction>

<SetValueAction UIObjectName="UIMap.UICustomer1usmfNewRecoWindow.UIItemWindow.UINameEdit">

<ParameterName />

<Value Encoded="false">Alex Customer</Value>

<Type>String</Type>

</SetValueAction>

<IsGlobalHotkey>false</IsGlobalHotkey>

<ActionType>Click</ActionType>

</MouseAction>

<MouseAction UIObjectName="UIMap.UIItem1usmfWindow.UIItemWindow.UIGridTable.UIRow1Row.UIItem10Cell">

<ParameterName />

<ModifierKeys>None</ModifierKeys>

<IsGlobalHotkey>false</IsGlobalHotkey>

<Location X="43" Y="11" />

<WheelDirection>0</WheelDirection>

<ActionType>Click</ActionType>

<MouseButton>Left</MouseButton>

</MouseAction>

<IsGlobalHotkey>false</IsGlobalHotkey>

<ActionType>Click</ActionType>

</MouseAction>

<Direction>Start</Direction>

<Disabled>false</Disabled>

<MarkerRegionType>Action</MarkerRegionType>

</TestStepMarkerAction>

</ExecuteActions>

<Maps>

…

</Maps>

</ValueMap>

</UITest>

Please note that XML will be different depending on how I enter a data. Below I describe a difference between using a lookup for data selection versus keying in the data

Option 1 – Lookup

<IsGlobalHotkey>false</IsGlobalHotkey>

<ActionType>Click</ActionType>

</MouseAction>

<MouseAction UIObjectName="UIMap.UIItem1usmfWindow.UIItemWindow.UIGridTable.UIRow1Row.UIItem10Cell">

<ParameterName />

<ModifierKeys>None</ModifierKeys>

<IsGlobalHotkey>false</IsGlobalHotkey>

<Location X="43" Y="11" />

<WheelDirection>0</WheelDirection>

<ActionType>Click</ActionType>

<MouseButton>Left</MouseButton>

</MouseAction>

Option 2 – Key in

<IsGlobalHotkey>false</IsGlobalHotkey>

</SendKeysAction>

<SetValueAction UIObjectName="UIMap.UICustomer1usmfNewRecoWindow.UIItemWindow1.UICustomergroupEdit">

<ParameterName />

<Value Encoded="false">10</Value>

<Type>String</Type>

</SetValueAction>

Now we can run the test and the system will create a customer for us

Result in Microsoft Dynamics AX 2012

We can technically create one more customer by changing the code appropriately

Source code

[GeneratedCode("Coded UITest Builder", "10.0.40219.445")]

public class CustomerImportParams

{

#region Fields

/// <summary>

/// Type 'Alex Customer 2' in 'Name' text box

/// </summary>

public string UINameEditText = "Alex Customer 2";

#endregion

}

Result in Microsoft Dynamics AX 2012

In fact we are interested in creating multiple customers at once

For these purposes we can introduce a loop

Source code

public void CustomerImport()

{

#region Variable Declarations

WinMenuItem uICustomerMenuItem = this.UIMicrosoftDynamicsAXDWindow.UIHomeTabWindow.UINewGroup.UICustomerMenuItem;

WinEdit uINameEdit = this.UICustomer1usmfNewRecoWindow.UIItemWindow.UINameEdit;

WinClient uICustomergrouplookupbClient = this.UICustomer1usmfNewRecoWindow.UIItemWindow1.UICustomergrouplookupbClient;

WinCell uIItem10Cell = this.UIItem1usmfWindow.UIItemWindow.UIGridTable.UIRow1Row.UIItem10Cell;

WinButton uISaveandcloseButton = this.UICustomer1usmfNewRecoWindow.UIItemWindow2.UISaveandcloseButton;

#endregion

int i;

for (i = 2; i <= 10; i++)

{

// Click 'Customer' menu item

Mouse.Click(uICustomerMenuItem, new Point(35, 40));

// Click 'Name' text box

Mouse.Click(uINameEdit, new Point(23, 0));

// Type 'Alex CustomerN' in 'Name' text box

uINameEdit.Text = this.CustomerImportParams.UINameEditText + i.ToString();

// Click 'Customer group lookup button' client

Mouse.Click(uICustomergrouplookupbClient, new Point(4, 8));

// Click '10' cell

Mouse.Click(uIItem10Cell, new Point(43, 11));

// Click 'Save and close' button

Mouse.Click(uISaveandcloseButton, new Point(66, 10));

}

[GeneratedCode("Coded UITest Builder", "10.0.40219.445")]

public class CustomerImportParams

{

#region Fields

/// <summary>

/// Type 'Alex Customer' in 'Name' text box

/// </summary>

public string UINameEditText = "Alex Customer";

#endregion

}

Result in Microsoft Dynamics AX 2012

This approach will work if all our customers will have uniform/similar name

However when we really want to do is to import customers based on a file. For example, we have CSV (Comma Separated Value) file with a list of customers

For these purposes we will introduce Data Driven Coded UI Test

Please find more info about Data Driven Coded UI Test here: http://msdn.microsoft.com/en-us/library/ee624082.aspx

CSV File

Name

Alex Customer2

Alex Customer3

Alex Customer4

Alex Customer5

Alex Customer6

Alex Customer7

Alex Customer8

Alex Customer9

Alex Customer10

Source code

[DeploymentItem("Customers.csv"),

DataSource("Microsoft.VisualStudio.TestTools.DataSource.CSV",

"C:\\Users\\Administrator\\Documents\\Visual Studio 2010\\Projects\\TestProject\\TestProject\\Customers.csv", "Customers#csv",

DataAccessMethod.Sequential),

TestMethod]

public void CodedUITestMethod1()

{

// To generate code for this test, select "Generate Code for Coded UI Test" from the shortcut menu and select one of the menu items.

// For more information on generated code, see http://go.microsoft.com/fwlink/?LinkId=179463

string name = TestContext.DataRow[0].ToString();

this.UIMap.CustomerImport(name);

}

public void CustomerImport(string _name)

{

#region Variable Declarations

WinMenuItem uICustomerMenuItem = this.UIMicrosoftDynamicsAXDWindow.UIHomeTabWindow.UINewGroup.UICustomerMenuItem;

WinEdit uINameEdit = this.UICustomer1usmfNewRecoWindow.UIItemWindow.UINameEdit;

WinClient uICustomergrouplookupbClient = this.UICustomer1usmfNewRecoWindow.UIItemWindow1.UICustomergrouplookupbClient;

WinCell uIItem10Cell = this.UIItem1usmfWindow.UIItemWindow.UIGridTable.UIRow1Row.UIItem10Cell;

WinButton uISaveandcloseButton = this.UICustomer1usmfNewRecoWindow.UIItemWindow2.UISaveandcloseButton;

#endregion

// Click 'Customer' menu item

Mouse.Click(uICustomerMenuItem, new Point(35, 40));

// Click 'Name' text box

Mouse.Click(uINameEdit, new Point(23, 0));

// Type 'Alex Customer' in 'Name' text box

uINameEdit.Text = _name;// this.CustomerImportParams.UINameEditText;

// Click 'Customer group lookup button' client

Mouse.Click(uICustomergrouplookupbClient, new Point(4, 8));

// Click '10' cell

Mouse.Click(uIItem10Cell, new Point(43, 11));

// Click 'Save and close' button

Mouse.Click(uISaveandcloseButton, new Point(66, 10));

}

Note: Please note that all changes to your test to enable it to use a data source for the parameters will be made to your test's source code file (i.e. CodedUITest1.cs). You cannot modify the code in the UIMap.Designer.cs file.

Result in Microsoft Dynamics AX 2012

Remark: Please note that in order for Coded UI Test to interact with desktop the computer running the test should not be locked during test execution time. Otherwise you will see the following error: “Automation engine is unable to playback the test because it is not able to interact with the desktop. This could happen if the computer running the test is locked or its remote session window is minimized.”

Summary: This document describes how to use Visual Studio Test Framework to import data into Microsoft Dynamics AX 2012 for the purposes of POC or testing by automation of data entry via User Interface. You can organize parallel import of data by running multiple instances of the test at the same time from different clients or investigate how to potentially make use of Load Test Visual Studio Test Framework. Please note that the recommended tool for data import in Microsoft Dynamics AX 2012 is Data Import Export Framework which provides number of standard templates for main business entities and documents, ability to use various entity types (Entity, Composite Entity, Table) and extend it for custom data.

Author: Alex Anikiev, PhD, MCP

Tags: Microsoft Dynamics ERP, Microsoft Dynamics AX 2012, Data Migration, Data Import, Data Conversion, Test Automation, Visual Studio Test Framework, Data Driven Coded UI Test.