 |
| ERP Implementations Series |
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
-- 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.)
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.
No comments:
Post a Comment