As mentioned in the previous post, building the Products Master paginated report via the base D365 FO tables synchronized in Data Lake involves more effort and implies duplicating the logic from the used data entity(-ies). One can attempt recreating the entities 1:1 as they are defined in the D365 database (an SQL Server Azure database), however there are high the chances that in certain areas (e.g. financial dimensions) the features used in building the logic are not supported. Moreover, it might be the case that only a subset of the functionality was implemented, and with this, pieces of logic becoming obsolete.
There's also a limitation on the number of tables which can be synchronized, and therefore one needs to compromise. In the dbo.EcoResProductV2Entity there are about 20 tables involved, though probably only half of them are used. The base table dbo.EcoResProduct contains most of the fields available also in the data entity, though the names for the various attributes need to be retrieved from the various tables.
Some of the issues met in working with data entities appear in this scenario as well: (2) there are fields based on Enums that store only the code and it's needed to map the corresponding values, (4) further transformations are needed (e.g., converting fields to formats). It makes sense to encapsulate similarly the logic in views, though this time one or two layers of logic are needed in addition. One can choose to do this for each table in scope, or replicate data entity's logic by focusing only on the needed parts. Both approaches will prove to be challenging in certain scenarios. Let's consider the latter:
CREATE OR ALTER VIEW TDM.vEcoResProductV2Entity AS /* name: Products - Master (base view) created: 01.04.2021 modified: 01.04.2021 */ SELECT ITM.ProductType , CASE ITM.ProductType WHEN 1 THEN 'Item' WHEN 2 THEN 'Service' END ProductTypeName , CAST ((CASE WHEN ITM.InstanceRelationType = 13678 THEN 1 ELSE 2 END) AS INT) ProductSubtype , CASE WHEN ITM.InstanceRelationType = 13678 THEN 'Product' ELSE 'Product Master' END ProductSubtypeName , ITM.DisplayProductNumber ProductNumber , Replace(Replace(ILT.Name, char(10), ' '), char(13), ' ') ProductName , Replace(Replace(ILT.Description, char(10), ' '), char(13), ' ') ProductDescription , PCA.CategoryName RetailProductCategoryName , PCA.CategoryCode RetailProductCategoryCode , PDG.Name ProductDimensionGroupName , SDG.Name StorageDimensionGroupName , TDG.Name TrackingDimensionGroupName , ITM.RetailColorGroupId ProductColorGroupId , ITM.RetailSizeGroupId ProductSizeGroupId , ITM.RetailStyleGroupId ProductStyleGroupId , ITM.VariantConfigurationTechnology , CASE ITM.VariantConfigurationTechnology WHEN 0 THEN 'None' WHEN 1 THEN 'Predefined Variants' WHEN 2 THEN 'Dimension Based' WHEN 3 THEN 'RuleBased' WHEN 4 THEN 'Constraint Based' END VariantConfigurationTechnologyName , CASE WHEN KIT.ProductMaster IS NOT NULL THEN 1 ELSE 0 END IsProductKit , CASE WHEN KIT.ProductMaster IS NOT NULL THEN 'Yes' ELSE 'No' END IsProductKitName , ITM.PDSCWProduct IsCatchWeightproduct , CASE ITM.PDSCWProduct WHEN 1 THEN 'Yes' ELSE 'No' END IsCatchWeightproductName , ITM.IsProductVariantUnitConversionEnabled , CASE ITM.IsProductVariantUnitConversionEnabled WHEN 1 THEN 'Yes' ELSE 'No' END IsProductVariantUnitConversionEnabledName -- system , PPD.ProductDimensionGroup ProductDimensionGroupRecId , PSG.StorageDimensionGroup StorageDimensionGroupRecId , PTD.TrackingDimensionGroup TrackingDimensionGroupRecId , PCA.RetailCategoryRecId , ITM.RecId , ITM.Partition FROM dbo.EcoResProduct ITM LEFT JOIN dbo.EcoResProductTranslation ILT ON ITM.RecId = ILT.Product AND ITM.Partition = ILT.Partition AND ILT.LanguageId = 'en-us' LEFT JOIN dbo.EcoResProductDimensionGroupProduct PPD ON ITM.RecId = PPD.Product AND ITM.Partition = PPD.Partition LEFT JOIN dbo.EcoResProductDimensionGroup PDG ON PPD.ProductDimensionGroup = PDG.RecId AND PPD.Partition = PDG.Partition LEFT JOIN dbo.EcoResStorageDimensionGroupProduct PSG ON ITM.RecId = PSG.Product AND ITM.Partition = PSG.Partition LEFT JOIN dbo.EcoResStorageDimensionGroup SDG ON PSG.StorageDimensionGroup = SDG.RecId AND PSG.Partition = SDG.Partition LEFT JOIN dbo.EcoResTrackingDimensionGroupProduct PTD ON ITM.RecId = PTD.Product AND ITM.Partition = PTD.Partition LEFT JOIN dbo.EcoResTrackingDimensionGroup TDG ON PTD.TrackingDimensionGroup = TDG.RecId AND PTD.Partition = TDG.Partition LEFT JOIN (-- product retail category SELECT PCA.Product , PCA.Partition , CAT.Code CategoryCode , CAT.Name CategoryName , PCA.Category RetailCategoryRecId FROM dbo.EcoResProductCategory PCA JOIN dbo.EcoResProductRetailCategoryHierarchy RCH ON PCA.CategoryHierarchy = RCH.RetailCategoryHierarchy AND PCA.Product = RCH.Product AND PCA.Partition = RCH.Partition JOIN dbo.EcoResCategory CAT ON PCA.Category = CAT.RecId AND PCA.Partition = CAT.Partition ) PCA ON ITM.RecId = PCA.Product AND ITM.Partition = PCA.Partition LEFT JOIN dbo.RetailKit KIT ON ITM.RecId = KIT.ProductMaster AND ITM.Partition = KIT.Partition WHERE NOT(ITM.InstanceRelationType = 4211)
As can be seen, the logic is quite complex and only half of the tables were used. There will be entities even more complex than this (the query was restructured for understandability). On the other hand, there will be environments where only half from the above tables will be used (e.g., when no inventory and/or dimensions are needed).
To test the view, one just needs to change the schema referenced in the logic. The view is created under the TDM (Table Data Model) schema, so there should be no issues as long the schemas are used correctly. However, when duplicating logic, one should check whether the values match, respectively whether the objects have the same record count:
-- checking whether the values match for an example SELECT * FROM TDM.vEcoResProductV2Entity WHERE ProductNumber = '0169' SELECT * FROM EDM.vEcoResProductV2Entity WHERE ProductNumber = '0169' -- checking the number of records SELECT count(*) FROM TDM.vEcoResProductV2Entity SELECT count(*) FROM EDM.vEcoResProductV2Entity
There are several optimizations or restructuring opportunities in the logic, e.g., the 'product retail category' subquery can be created as a separate view. Also, the groupings of two tables for the 'dimension groups' could be encapsulated individually in views. This can increase the number of views created considerably, though upon case the views could prove to be useful for troubleshooting or reuse.
Moreover, in environments with only a partition the constraints on the respective field could be removed (important decision though), while the value for the retail category could be hardcoded. Even if hardcoding values should be in general avoided, it's needed to compromise when there are more important constraints.
One way to reduce logic's complexity is to create a "base view" for each table in which the needed transformations are made, respectively only the needed columns are used. Reducing the number of columns simplifies the identification of attributes. For example, the base view for dbo.EcoResProduct could be written as follows:
CREATE OR ALTER VIEW [TDM].[vEcoResProduct] AS /* name: Products - master data (base view) created: 01.04.2021 modified: 01.04.2021 */ SELECT PRD.DisplayProductNumber ProductNumber , PRD.ProductType , CASE PRD.ProductType WHEN 1 THEN 'Product' WHEN 2 THEN 'ProductMaster' WHEN 3 THEN 'ProductVariant' END ProductTypeName , CASE WHEN PRD.INSTANCERELATIONTYPE = 15969 THEN 1 ELSE 2 END ProductSubtype , CASE WHEN PRD.INSTANCERELATIONTYPE = 15969 THEN 'Item' ELSE 'Product master' END ProductSubtypeName , PRD.RetailSizeGroupId , PRD.RetailColorGroupId , PRD.RetailStyleGroupId , PRD.RetailFlavorGroupId , PRD.VariantConfigurationTechnology , CASE PRD.VariantConfigurationTechnology WHEN 0 THEN 'None' WHEN 1 THEN 'PredefinedVariants' WHEN 2 THEN 'DimensionBased' WHEN 3 THEN 'RuleBased' WHEN 4 THEN 'ConstraintBased' END VariantConfigurationTechnologyName , PRD.IsProductVariantUnitConversionEnabled , CASE PRD.IsProductVariantUnitConversionEnabled WHEN 1 THEN 'Yes' ELSE 'No' END IsProductVariantUnitConversionEnabledName -- system , PRD.RecId , PRD.Partition , PRD.ModifiedBy FROM dbo.EcoResProduct PRD WHERE NOT(PRD.InstanceRelationType = 4211) GO
A similar approach can be used for each table, or at least the tables that need further transformations. There are several choices - ideally one should find a good-enough approach and stick to it. There will be also exceptions, though the general design should prevail!
The bottom line, when using the Export to Data Lake add-in (*), as only the export of tables is supported, the logic can become occasionally complex, though still manageable (in other areas the tables are straightforward to use). When using Link to Data Lake, one can rely on data entities, as long they are synchronized correctly. However, one still needs to rely on tables to fill the gaps.
Note:
(*) Existing customers have until 1-Nov-2024 to transition from Export to Data lake to Synapse link. Microsoft advises new customers to use Synapse Link.
Happy coding!
Previous Post << ||>> Next Post
No comments:
Post a Comment