|
|
ERP Implementations Series
|
Below is exemplified the migration of Products from AdventureWorks database to
Dynamics 365 (D365), where a minimum of steps were considered. Variations (e.g.
enrichment of data, successive migrations) and other Entities (Product
Variants, Released Products, Released Product Variants, etc.) will be considered
in future posts.
As the AdventureWorks database is available only for testing and
exemplification purposes, there is no need for a
data import layer, the data being prepared into the “Map” schema created for this purpose. In
theory the same approach can be used in production systems as well, though
usually it’s better to detach the migration layer from the source system(s) from
performance or security reasons.
-- creating a schema into the AdventureWorks database
CREATE SCHEMA [Map]
Step 1: Data Discovery
Within this step one attempts getting a sorrow understanding of the systems
involved within the data migration, in this case AdventureWorks and D365. As
basis for this will be analyzed the tables for each entity, respectively the
relations existing between them, the values, the distribution as well the
relations existing between attributes. Is needed to analyze the similarities as
well differences between the involved data models at structural as well at value
level.
In AdventureWorks the SKU (Stock Keeping Unit) has a Color, Size and Style as
Dimensions, a Product being created for each SKU. In D365 one differentiates
between Products and Dimensions associated with it, having thus two levels
defined. In addition, in D365 a Product has also the Configuration as
dimension:
In addition, the Products and their Dimensions are defined at master level with
a minimal of attributes like the Dimension Group. After that the Products and
their Dimensions can be released for each Business Unit (aka Data Area), where
the detailed attributes for Purchasing, Sales, Production or Inventory are
maintained. For those acquainted with Dynamics AX 2009 it’s the same structure.
Once the structural differences identified, one can start looking at the values
that define a product in both systems. The following queries are based on the
source system.
-- reviewing the sizes
SELECT Size
, count(*) NoSizes
FROM [Production].[vProductDetails]
WHERE CultureId = 'en'
GROUP BY Size
ORDER BY Size
-- reviewing the colors
SELECT Color
, count(*) NoColors
FROM [Production].[vProductDetails]
WHERE CultureId = 'en'
GROUP BY Color
ORDER BY Color
-- reviewing the styles
SELECT Style
, count(*) NoStyles
FROM [Production].[vProductDetails]
WHERE CultureId = 'en'
GROUP BY Style
ORDER BY Style
If the above queries show what values are used, the following shows the
dependencies between them:
-- reviewing the sizes, colors, styles
SELECT Size
, Color
, Style
, count(*) NoValues
FROM [Production].[vProductDetails]
WHERE CultureId = 'en'
GROUP BY Size
, Color
, Style
ORDER BY 1,2,3
-- reviewing the dependencies between sizes, colors, styles
SELECT CASE WHEN IsNull(Size, '') != '' THEN 'x' ELSE '' END HasSize
, CASE WHEN IsNull(Color, '') != '' THEN 'x' ELSE '' END HasColor
, CASE WHEN IsNull(Style, '') != '' THEN 'x' ELSE '' END HasStyle
, count(*) NoValues
FROM [Production].[vProductDetails]
WHERE CultureId = 'en'
GROUP BY CASE WHEN IsNull(Size, '') != '' THEN 'x' ELSE '' END
, CASE WHEN IsNull(Color, '') != '' THEN 'x' ELSE '' END
, CASE WHEN IsNull(Style, '') != '' THEN 'x' ELSE '' END
ORDER BY 1,2,3
The last query is probably the most important, as it shows how the products
need to be configured into the target system:
As can be seen a product can have only Color, Color and Style, Size and Color,
respectively no dimensions or all dimensions. It will be needed to define a
Dimension Group for each of these cases (e.g. Col, ColSty, SizCol, SizColSty,
None). (More information on this in a future post.)
Unfortunately, unless the target system is already in use, there are no values
usually, though one can attempt entering a few representative values manually
over the user interface, at least to see what tables get populated.
Step 2: Data Mapping
Once the main attributes from source and target were identified, one can
create the mapping at attribute level between them. Typically, one includes
all the relevant information for a migration, from table, attribute,
description to attributes’ definition (e.g. type, length, precision,
mandatory) in all the systems:
The mapping was kept to a minimum to display only the most relevant
information. Except a warning concerning the length of an attribute,
respectively a new attribute (the old item number), the mapping doesn’t
involve any challenges.
A data dictionary or even a metadata repository for the involved systems can
help in the process, otherwise one needs to access the information from the
available documentation or system’s metadata and prepare the data manually.
The relevant metadata for D365 can be obtained from the Microsoft documentation.
The data can be loaded into system via the
EcoResProductV2Entity
(see also
data entities
or the AX 2012 documentation for tables and enumeration data types).
Step 3: Building the source entity
AdventureWorks already provides a view which models the Products entity, though
because of its structure it needs to suffer some changes, or sometimes more
advisable, do the changes in a separate view as follows:
-- Products source entity
CREATE VIEW Map.vProductDetails
AS
SELECT CASE WHEN Size<>'' THEN dbo.CutLeft(ProductNumber, '-',1) ELSE ProductNumber End ItemIdOld
, CASE WHEN Size<>'' THEN dbo.CutLeft(Name, '-',1) ELSE Name End Name
, row_number() OVER(PARTITION BY CASE WHEN Size<>'' THEN dbo.CutLeft(ProductNumber, '-',1) ELSE ProductNumber End ORDER BY ProductNumber) Ranking
, ProductNumber
, Description
, Color
, Size
, Style
, CultureId
, Subcategory
, Category
, MakeFlag
, FinishedGoodsFlag
, SellStartDate
, SellEndDate
, StandardCost
, ListPrice
, SafetyStockLevel
, ReorderPoint
FROM [Production].[vProductDetails]
-- reviewing the data
SELECT *
FROM Map.vProductDetails
WHERE CultureId = 'en'
ORDER BY ProductNumber
To prepare the data for the migration the Product Number as well the Name were
stripped from the Size, this being done with the help of
dbo.CutLeft
function. The row_number ranking window function was used to allow later
selecting the first size for a given Product.
The discovery process continues, this time in respect to the target. Its useful
to understand for example whether a Product has more than one Color or Style,
whether the prices vary between Sizes, whether attributes like the Subcategory
are consistent between Sizes, etc. It’s useful to prove anything that could have
impact on the migration logic. The list of tests will be extended while building
the logic, as new information are discovered.
-- checking dimensions' definition
SELECT ItemidOld
, count(Size) NoSizes
, count(DISTINCT Color) NoColors
, count(DISTINCT Style) NoStyles
FROM Map.vProductDetails
WHERE CultureId = 'en'
--AND Ranking = 1
GROUP BY ItemidOld
ORDER BY ItemidOld
-- checking the price variances between dimensions
SELECT ItemidOld
, Min(StandardCost) MinStandardCost
, Max(StandardCost) MaxStandardCost
FROM Map.vProductDetails
WHERE CultureId = 'en'
AND Ranking = 1
GROUP BY ItemidOld
HAVING Min(IsNull(StandardCost, 0)) != Max(IsNull(StandardCost, 0))
ORDER BY ItemidOld
-- checking attribute's consistency between dimensions
SELECT ItemidOld
, Min(Subcategory) MinSubcategory
, Max(Subcategory) MaxSubcategory
FROM Map.vProductDetails
WHERE CultureId = 'en'
AND Ranking = 1
GROUP BY ItemidOld
HAVING Min(IsNull(Subcategory, '')) != Max(IsNull(Subcategory, ''))
ORDER BY ItemidOld
When the view starts performing poorly, for example because of the number of
joins or data’s volume, it might me useful to dump the data in a table and
perform the tests on it.
Even if it’s not maybe the case, it’s useful to apply defensive techniques in
the logic by handing adequately the nulls.
Step 4: Implementing the Mapping
The attributes which need to be considered here are based on the target
entities. It might be needed to include also attributes that are further needed
to build the logic.
-- Product Mapping
CREATE VIEW [Map].vEcoResProductV2Entity
AS
SELECT ProductId
, ItemidOld ItemId
, 'Item' ProductType
, 'ProductMaster' ProductSubtype
, Left(Replace(Name, ' ', ''), 20) ProductSearchName
, ItemidOld ProductNumber
, Name ProductName
, Description ProductDescription
, CASE
WHEN IsNull(Size, '') !='' AND IsNull(Color, '') !='' AND IsNull(Style, '')!='' THEN 'SizColSty'
WHEN IsNull(Size, '') !='' AND IsNull(Color, '') !='' THEN 'SizCol'
WHEN IsNull(Style, '') !='' AND IsNull(Color, '') !='' THEN 'ColSty'
WHEN IsNull(Color, '') !='' THEN 'Col'
WHEN IsNull(Style, '')!='' THEN 'Sty'
WHEN IsNull(Size, '')!='' THEN 'Siz'
ELSE 'None'
END ProductDimensionGroupName
, 'WHS' StorageDimensionGroupName
, CASE WHEN MakeFlag = 1 THEN 'SN' ELSE '' END TrackingDimensionGroupName
, 'PredefinedVariants' VariantConfigurationTechnology
, Subcategory ProductCategory
, ItemidOld ItemIdOld
, 1 IsNewItem
FROM Map.vProductDetails
WHERE CultureId = 'en'
AND Ranking = 1
The Dimenstion Group is based on the above observation. It was supposed that all
Products have inventory (see Storage Dimension), while the manufactured
products will get a Serial Number (see Tracking Dimension). IsNewItem will be
used further to migrate deltas (and thus to partition migrations).
Step 5: Building the Target Entity
The target entity is in the end only a table in which usually are kept only the
attributes in scope. In this case the definition is given by the following
DDL:
CREATE TABLE Map.EcoResProductV2Entity(
Id int IDENTITY(1,1) NOT NULL,
ProductId int NULL,
ProductType nvarchar(20) NULL,
ProductSubtype nvarchar(20) NULL,
ProductsearchName nvarchar(255) NULL,
ProductNumber nvarchar(20) NOT NULL,
ProductName nvarchar(60) NULL,
ProductDescription nvarchar(1000) NULL,
ProductDimensionGroupName nvarchar(50) NULL,
StorageDimensionGroupName nvarchar(50) NULL,
TrackingDimensionGroupName nvarchar(50) NULL,
VariantConfigurationTechnology nvarchar(50) NULL,
ProductCategory nvarchar(255) NULL,
ItemIdOld nvarchar(20) NULL,
IsNewItem bit,
CONSTRAINT I_EcoResProductV2Entity PRIMARY KEY CLUSTERED
(
Id ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The table was build to match the name and definition from the target
systems. The definition is followed by a few inserts based on the logic defined
in the previous step:
-- preparing the data for EcoResProductV2Entity
INSERT INTO [Map].EcoResProductV2Entity
SELECT ITM.ProductId
, ITM.ProductType
, ITM.ProductSubtype
, ITM.ProductsearchName
, ITM.ProductNumber
, ITM.ProductName
, ITM.ProductDescription
, ITM.ProductDimensionGroupName
, ITM.StorageDimensionGroupName
, ITM.TrackingDimensionGroupName
, ITM.VariantConfigurationTechnology
, ITM.ProductCategory
, ITM.ItemIdOld
, ITM.IsNewItem
FROM [Map].VEcoResProductV2Entity ITM
WHERE ITM.IsnewItem = 1
ORDER BY ProductType
, ITM.ItemIdOld
-- reviewing the data
SELECT *
FROM [Map].EcoResProductV2Entity
ORDER BY ItemId
The business might decide to take over the Product Number into the target system
as unique identifier, though it’s not always the case. It might opt to create a
new sequence number, which could start e.g. with 10000000 (8 characters). In
such a case is changed only the logic for the Product Number, the value being
generated using a ranking window function:
-- preparing the data for EcoResProductV2Entity
DECLARE @StartItemId as int = 10000000
--INSERT INTO [Map].EcoResProductV2Entity
SELECT ...
, @StartItemId + Rank() OVER(ORDER BY ITM.ProductType, ITM.ItemIdOld) ProductNumber
, ...
FROM [Map].VEcoResProductV2Entity ITM
WHERE ITM.IsnewItem = 1
ORDER BY ProductType
, ITM.ItemIdOld
Step 5: Reviewing the Data
Before exporting the data it makes sense to review the data from various
perspectives: how many Products of a certain type will be created, whether the
current and old product numbers are unique, etc. The scripts make sure that
the consistency of the data in respect to the future systems was
achieved.
-- checking values' frequency (overview, no implications)
SELECT ITM.ProductType
, ITM.ProductSubtype
, ITM.ProductDimensionGroupName
, ITM.StorageDimensionGroupName
, ITM.TrackingDimensionGroupName
, ITM.VariantConfigurationTechnology
, count(*) NoRecords
FROM [Map].EcoResProductV2Entity ITM
WHERE IsNewItem = 1
GROUP BY ITM.ProductType
, ITM.ProductSubtype
, ITM.ProductDimensionGroupName
, ITM.StorageDimensionGroupName
, ITM.TrackingDimensionGroupName
, ITM.VariantConfigurationTechnology
-- check ProductNumber's uniqueness (no duplicates allowed)
SELECT ProductNumber
, Min(ItemidOld)
, max(ItemIdold)
, count(*)
FROM [Map].EcoResProductV2Entity
GROUP BY ProductNumber
HAVING count(*)>1
-- check old Product's uniqueness (no duplicates allowed)
SELECT ItemIdOld
, count(*)
FROM [Map].EcoResProductV2Entity
GROUP BY ItemIdOld
HAVING count(*)>1
This section will grow during the implementation, as further entities will be
added.
Step 6: Exporting the data
The export query is usually reflecting the entity and can include further data’s
formatting, when needed:
-- Export Products
SELECT ITM.ProductType
, ITM.ProductSubtype
, ITM.ProductsearchName
, ITM.ProductNumber
, ITM.ProductName
, ITM.ProductDescription
, ITM.ProductDimensionGroupName
, ITM.StorageDimensionGroupName
, ITM.TrackingDimensionGroupName
, ITM.VariantConfigurationTechnology
, ITM.ProductCategory RetailProductCategoryName
, ITM.ItemIdOld
FROM [Map].EcoResProductV2Entity ITM
WHERE ITM.isNewItem = 1
ORDER BY ProductNumber
Depending on the import needs, the data can be exported to Excel or a delimited
text file (e.g. “|” pipe is an ideal delimiter.
Step 7: Validating the Data
before Import
Before importing the data into the target system, it makes sense to have the
data checked by the business or consultants. A visual check at this stage can
help save time later.
Step 8: Validating the Data after Import
Unfortunately Microsoft doesn’t allow direct access to the D365 Production
database, however one can still access various tables and entities’ content
via the table browser. Anyway, the validation usually takes place into the
UAT (User Acceptance Testing) system. So, if everything went well into the UAT
and all measures were taken to have the same parameters across all systems,
there should be no surprises during Go-Live.
