Microsoft SQL Server 2012 Master Data Services (Part 1)

Microsoft SQL Server 2012 Master Data Services (Part 1)

 

Purpose: The purpose of this document is to provide a walkthrough of 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 which in my case includes Microsoft SQL Server 2012 and Microsoft SQL Server 2012 Master Data Services we’ll have to do initial configuration of Master Data Services

 

Master Data Services Configuration Manager – AX2012R2A

 

 

Master Data Services Configuration Manager – Database Configuration

 

 

The first step would be to create MDS database in Microsoft SQL Server 2012

 

Master Data Services Configuration Manager – Database Configuration (Create Database - Welcome)

 

 

Master Data Services Configuration Manager – Database Configuration (Create Database – Database Server)

 

 

Master Data Services Configuration Manager – Database Configuration (Create Database - Database)

 

 

Master Data Services Configuration Manager – Database Configuration (Create Database – Administrator Account)

 

 

Master Data Services Configuration Manager – Database Configuration (Create Database - Summary)

 

 

Master Data Services Configuration Manager – Database Configuration (Create Database – Progress and Finish)

 

 

Master Data Services Configuration Manager – Database Configuration (Done)

 

 

Now once we have created MDS database we’ll have to do Web Configuration

 

Master Data Services Configuration Manager – Web Configuration

 

 

Master Data Services Configuration Manager – Web Configuration (Create Website)

 

 

HTTP Binding Information Warning

 

 

Master Data Services Configuration Manager – Web Configuration (Connect to Database)

 

 

 

 Master Data Services Configuration Manager – Web Configuration (Done)

 

 

Master Data Services Configuration Manager – Web Configuration (Configuration Complete)

 

 

Now once we have completed Web Configuration step we can access Master Data Service Web front-end

 

Getting Started with Microsoft SQL Server 2012 Master Data Services

 

 

As you can see on Master Data Services Welcome page there’s a lot of information with links to documentation, etc. Now we’ll go ahead and open Master Data Manager Home page

 

Microsoft SQL Server 2012 Master Data Services – Home

 

 

From Master Data Manager Home page you can perform different tasks 

 

Information Worker Tasks

 

 

Master Data Services provide Web front-end which allows you to do all activities in Web Browser. However please note that you can also install a very useful Master Data Services Excel Add-in and perform the same tasks from Excel

 

Install Master Data Services Add-in for Microsoft Excel

 

 

Administrative Tasks

 

 

The next step in exploration of Master Data Services will be to load some sample data and get a feel of what it looks like to work with Master Data Services UI (User Interface). Master Data Services ship with sample data model which you can deploy and this is exactly what we’re going to do now

 

Model Deployment Wizard

 

 

Deploy Package

 

 

You can find number of sample models in Master Data Services installation folder

 

Choose File to Upload

 

 

In this particular case I’ll deploy Customer model

 

Deploy Package

 

 

Deploy Package – Error

 

 

As you can see in case Deployment package contains some data we’ll have to use Command Line Utility to deploy the package

 

Command Line Utility

 

 

Once deployment process is finished we can see Customer model in the list of models in Master Data Services. As you can see I went ahead and deployed Product model as well

 

Models

 

 

Now you can explore entities and entity attributes included into deployed models. But my next step would be to create my own model for Microsoft Dynamics Master Data Management scenario, that’s why I’ll go to Model Maintenance area

 

Model Maintenance

 

 

Add Model (+ sign)

 

 

I’ll name my model “Dynamics” because it will be used in Master Data management scenario for Microsoft Dynamics AX 2012 and Microsoft Dynamics CRM Online

 

Entity Maintenance

 

 

Model

 

 

As the result you will see “Dynamics” model in the list of models. Now I can define entities for my newly created model. I’m going to add “DynamicsCustomer” and “DynamicsProduct” entities

 

Add Entity – Customer

 

 

Add Entity – Product

 

 

Model Entities

 

 

Now you will see my newly created entities associated with “Dynamics” model. The next step would be to define required attributes for Customer and Product entities

 

Attribute Group Maintenance – Customer

 

 

Attribute Group Maintenance – Product

 

 

Model View

 

 

For the sake of simplicity I’ll add only “Name” attribute to my Customer and Product entities. Please also note that the system automatically created “Code” attribute which uniquely identified a record within Master Data Services (this is the requirement for Master Data Services)

 

Edit Entity – Customer 

 

 

Entity – Customer 

 

 

Edit Entity – Product

 

 

Entity – Product

 

 

As you remember during configuration of Master Data Services we created MDS database. So now after we created required entities for our “Dynamics” model we can verify that appropriate tables were created on the SQL side

 

Microsoft SQL Server database

 

 

In Master Data Services you can expose master data to other systems by means of Subscription Views. That’s why I’ll go ahead and create appropriate Subscription Views for my Customer and Product entities

 

Subscription View – Customer  

 

 

 

Subscription View - Product

 

 

Subscription Views

 

 

Again the work we did in Master Data Manager will find its reflection on the SQL side in the form of views

 

Microsoft SQL Server database

 

 

Obviously Master Data Services provides robust capabilities to manage different aspects of Master Data Management such as content management, relationship management, access control, change management and data processing. And I did describe only required minimum setup to be able to proceed with Master Data Management scenario for Microsoft Dynamics. Please see some of the screenshots below which speak to version management, security control, etc. in Master Data services 

 

Manage Versions

 

 

Manage Users

 

 

Remark: In this walkthrough I’ll be focusing on only data management aspects. However please note that Master Data Services is natively integrated with Microsoft SQL Server 2012 Data Quality Services to handle data quality aspects which may open up whole another discussion.

 

Summary: This document describes required minimum setup for implementing Master Data Management scenario for Microsoft Dynamics AX 2012 and Microsoft Dynamics CRM using Microsoft SQL Server 2012 Master Data Services. In particular I explained the process of Master Data Services configuration and then went into details of how to create a model, model entities and entity attributes to support Master Data Management scenario for Customers and Products. 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.

 

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 Dynamics AX 2012 – Windows 8 Store App

Microsoft Dynamics AX 2012 –

Windows 8 Store App
 

Purpose: The purpose of this document is to illustrate how to develop Windows 8 Store Apps which are integrated with Microsoft Dynamics AX 2012.
 

Challenge: For the purposes of demonstration or Proof of Concept it may be needed to quickly develop Windows 8 Store App which is integrated with Microsoft Dynamics AX 2012. In order to complete this task at the minimum you will need a machine with Windows 8 OS, Visual Studio 2012 and access to Microsoft Dynamics AX 2012 instance. 

Solution: For the purposes of this walkthrough I'll use Microsoft Dynamics AX 2012 R2 Demo VM which can be downloaded from Customer/Partner Source. I also have Windows 8 OS and Visual Studio 2012 installed on my laptop. Windows 8 now includes Hyper-V, so I'll deploy Microsoft Dynamics AX 2012 R2 Demo VM under Hyper-V. This is all I need to develop Windows 8 Store App and integrate it with Microsoft Dynamics AX 2012.

Task: Windows 8 Store App which I'll develop in this walkthrough will display Product Catalog (list of released products in particular company) retrieved from Microsoft Dynamics AX 2012.

Walkthrough:

Connect Microsoft Dynamics AX 2012 R2 Demo VM to the Network

Please note that I created additional External network virtual adapter in Hyper-V Virtual Switch Manager

If you successfully connected Microsoft Dynamics AX 2012 R2 Demo VM to the Network you will see the following indication

Retrieve current IP Address of Microsoft Dynamics AX 2012 Demo VM

Please use ipconfig command to retrieve Windows IP Configuration which includes IPv4 Address

Please use ping command to make sure your host computer can access Microsoft Dynamics AX 2012 Demo VM connected to the same network using IP Address 

Modify Hosts file on host machine (C:/Windows/system32/Drivers/etc) to include mapping to Microsoft Dynamics AX 2012 Demo VM machine  
 

192.168.0.102     ax2012r2a.contoso.com

 

Create a list of Released products in Microsoft Dynamics AX 2012

Development:

Inbound port – AlexServices

Please note that AlexServices is Enhanced port using HTTP Adapter

Select service operations – AlexServices

Please note that AlexServices Inbound port exposes InventItemService Web Service operations

On host machine please make sure you can access WSDL URI for AlexServices Inbound port

Create Visual Studio 2012 Windows Store App project

Add Service reference to DynamicsService in Visual Studio 2012 Windows Store App project

 

http://ax2012r2a:89/MicrosoftDynamicsAXAif60/AlexServices/xppservice.svc

 

Please note that I used FQDN (fully qualified domain name) of Microsoft Dynamics AX 2012 Demo VM machine which includes machine name and domain

After Service reference to DynamicsService is added Visual Studio 2012 will generate a number of proxy classes automatically

Implement ConfigureEndpoint partial method to configure the service endpoint in Reference.cs
 

static partial void ConfigureEndpoint(System.ServiceModel.Description.ServiceEndpoint serviceEndpoint, System.ServiceModel.Description.ClientCredentials clientCredentials) {     clientCredentials.Windows.ClientCredential = new System.Net.NetworkCredential("Administrator", "pass@word1", "Contoso");     clientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;       BasicHttpBinding basicHttpBinding = new BasicHttpBinding(BasicHttpSecurityMode.TransportCredentialOnly);     basicHttpBinding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Ntlm;       serviceEndpoint.Binding = basicHttpBinding; }

 

This is required because likely your host machine will belong to one domain and Microsoft Dynamics AX 2012 Demo VM to another. In my case my host machine belongs to microsoft.com domain and Microsoft Dynamics AX 2012 Demo VM belongs to contoso.com domain. By implementing ConfigureEndpoint partial method you configure the service endpoint to use impersonation and specific credentials which will allow you to successfully log in into Microsoft Dynamics AX 2012 Demo VM when consuming Microsoft Dynamics AX 2012 Web Service from your host machine. Please note that specified credentials are default credentials for Administrator account in contoso.com domain in Microsoft Dynamics AX 2012 Demo VM.

Load application data from Microsoft Dynamics AX 2012 by calling Item Web Service
 

public async Task LoadApplicationData() {     ItemServiceClient client = new ItemServiceClient();       try     {                        QueryCriteria queryCriteria = new QueryCriteria();         CriteriaElement criteriaElement = new CriteriaElement();         criteriaElement.DataSourceName = "InventTable";         criteriaElement.FieldName = "NameAlias";         criteriaElement.Operator = Operator.Equal;         criteriaElement.Value1 = "Alex";                       queryCriteria.CriteriaElement = new CriteriaElement[1];         queryCriteria.CriteriaElement[0] = criteriaElement;                            ItemServiceFindResponse results = await client.findAsync(queryCriteria);           this.products = new ObservableCollection<CatalogProduct>();           if (results != null)         {                                foreach (AxdEntity_InventTable item in results.Item.InventTable)             {                                                                                                             CatalogProduct product = new CatalogProduct();                 product.ProductId = item.ItemId;                 product.Title = item.ItemId;                                        product.Description = item.ItemId;                 product.ImageUri = "../Microsoft_Dynamics_Logo.png";                                                              this.products.Add(product);             }         }     }     catch (FaultException ex)     {         client.Abort();     }               await client.CloseAsync(); }

 

Please note that in this project I use ObservableCollection of CatalogProduct objects describing Released products in Microsoft Dynamics AX 2012

Result

My Windows 8 Store App has its own Tile on Start screen

As the result my Windows 8 Store App will display Product catalog with info about Released products retrieved from Microsoft Dynamics AX 2012 

 

Version: Windows 8, Visual Studio 2012, Microsoft Dynamics AX 2012 R2

Summary: In this walkthrough I demonstrated how to quickly develop Windows 8 Store App which is integrated with Microsoft Dynamics AX 2012. For the purposes of demonstration or Proof of concept you can use Microsoft Dynamics AX 2012 R2 Demo VM which can be downloaded from Customer/Partner Source. Please also note that the same development techniques (exposing and consuming Web Services) will be used when developing production Windows 8 Store Apps integrated with Microsoft Dynamics AX 2012. From authentication perspective Microsoft Dynamics AX 2012 supports various mechanisms such as Windows Integrated authentication, Federated authentication and Claims based authentication (Microsoft account, etc.). Please note that in this walkthrough for the sake of simplicity I used impersonation and Windows Integrated authentication.

Author: Alex Anikiev, PhD, MCP

Tags: Microsoft Dynamics ERP, Microsoft Dynamics AX 2012, Windows 8, Windows 8 Store App, Modern UI, Visual Studio 2012, AIF, Application Integration Framework

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 concepts and describe the examples.