With the Export to Data Lake add-in (*), respectively with Azure Synapse Link for Dataverse with Azure Data Lake, it's possible now to create Power BI paginated reports based directly on the Dynamics 365 Finance & Operations (D365 FO) data model as available in the Data Lake. With the add-in it's possible to export the tables from D365 FO, while with the second option it's possible to export data entities and hopefully soon base tables as well.
The two features can be used in parallel in the same environment and even if they are based on different mechanisms, they synchronize D365 FO data to the Data Lake in standard data files that can be further consumed via a serverless/dedicated SQL server pool by reporting tools. Thus, one can build a data lakehouse/warehouse on top of the data for reporting. At least this was the approach before Microsoft Fabric.
So, in the end a developer will have the option to choose between base tables and data entities, as synchronized to the Data Lake. Data entities encapsulate the logic as used by the D365 FO application and can thus allow developing a report with the minimum of overhead, at least in theory. Unfortunately, there are other downsides that make the use of base tables a better approach, at least for certain scenarios (e.g. data entities are not available for synchronization or doesn't include all needed fields).
For the developer of reports the synchronization mechanism is less important. A set of database objects will be available, and reports can be built on top of them. Ideally, the base objects will have the same or similar names as in D365 FO, otherwise the differences between the two data models need to be translated in each piece of logic, which adds more complexity and overhead to the development.
Thus, to build a report to show the Product master data the developer can use the dbo.EcoResProductV2Entity data entity. Unfortunately, there are several important issues: (1) the name of the source entity can change as versions change, (2) there are fields based on Enums that store only the code and it's needed to map the corresponding values, (3) some fields from the base table need to be made available, (4) further transformations are needed (e.g., converting fields to formats). Thus, it's useful to encapsulate the logic into a view, when creating views is possible:
CREATE OR ALTER VIEW EDM.vEcoResProductV2Entity AS /* name: Products - Master (base entity) created: 01.04.2021 modified: 01.04.2021 */ SELECT ITM.ProductType , CASE ITM.ProductType WHEN 1 THEN 'Item' WHEN 2 THEN 'Service' END ProductTypeName , ITM.ProductSubtype , CASE ITM.ProductSubtype WHEN 1 THEN 'Product' WHEN 2 THEN 'Product Master' WHEN 3 THEN 'Product Variant' END ProductSubtypeName , ITM.ProductNumber , Replace(Replace(ITM.ProductName, char(10), ' '), char(13), ' ') ProductName , Replace(Replace(ITM.ProductDescription, char(10), ' '), char(13), ' ') ProductDescription , ITM.RetailProductCategoryName , ITM.ProductDimensionGroupName , ITM.StorageDimensionGroupName , ITM.TrackingDimensionGroupName , ITM.ProductColorGroupId , ITM.ProductSizeGroupId , ITM.ProductStyleGroupId , ITM.VariantConfigurationTechnology , CASE ITM.VariantConfigurationTechnology WHEN 0 THEN 'None' WHEN 1 THEN 'Predefined Variants' WHEN 2 THEN 'Dimension Based' WHEN 3 THEN 'Rule Based' WHEN 4 THEN 'Constraint Based' END VariantConfigurationTechnologyName , ITM.IsProductKit , CASE ITM.IsProductKit WHEN 1 THEN 'Yes' ELSE 'No' END IsProductKitName , ITM.IsCatchWeightproduct , CASE ITM.IsCatchWeightproduct WHEN 1 THEN 'Yes' ELSE 'No' END IsCatchWeightproductName , ITM.IsProductVariantUnitConversionEnabled , CASE ITM.IsProductVariantUnitConversionEnabled WHEN 1 THEN 'Yes' ELSE 'No' END IsProductVariantUnitConversionEnabledName -- system , ITM.ProductDimensionGroupRecId , ITM.StorageDimensionGroupRecId , ITM.TrackingDimensionGroupRecId , ITM.RetailCategoryRecId , ITM.RecId , ITM.Partition FROM dbo.EcoResProductV2Entity ITM
As can be seen, the view was created in the EDM (Entity Data Model) and has the "v" prefix added to the original name. The EDM schema was created to store the objects based on data coming via data entities.
With this view's data can be consumed in the paginated report:
--Products - Master SELECT ITM.ProductTypeName , ITM.ProductSubtypeName , ITM.ProductNumber , ITM.ProductName , ITM.RetailProductCategoryName , ITM.ProductDimensionGroupName , ITM.StorageDimensionGroupName , ITM.TrackingDimensionGroupName , ITM.ProductColorGroupId , ITM.ProductSizeGroupId , ITM.ProductStyleGroupId , ITM.VariantConfigurationTechnologyName , ITM.IsProductKitName , ITM.IsCatchWeightproductName FROM EDM.vEcoResProductV2Entity ITM WHERE ITM.ProductName LIKE 'Mens%' ORDER BY ITM.ProductNumber
One can use directly this query to generate the report, though in a second step, once the report was created, one might prefer to provide the query as variable to the corresponding dataset, as this allows better handling of the parameters and thus create the query dynamically as fit:
= "--Products - Master" & vbCrLf & "SELECT ITM.ProductTypeName" & vbCrLf & ", ITM.ProductSubtypeName" & vbCrLf & ", ITM.ProductNumber" & vbCrLf & ", ITM.ProductName" & vbCrLf & ", ITM.RetailProductCategoryName " & vbCrLf & ", ITM.ProductDimensionGroupName " & vbCrLf & ", ITM.StorageDimensionGroupName " & vbCrLf & ", ITM.TrackingDimensionGroupName " & vbCrLf & ", ITM.ProductColorGroupId " & vbCrLf & ", ITM.ProductSizeGroupId " & vbCrLf & ", ITM.ProductStyleGroupId" & vbCrLf & ", ITM.VariantConfigurationTechnologyName" & vbCrLf & ", ITM.IsProductKitName" & vbCrLf & ", ITM.IsCatchWeightproductName" & vbCrLf & ", ITM.IsProductVariantUnitConversionEnabledName" & vbCrLf & "FROM EDM.vEcoResProductV2Entity ITM" & vbCrLf & "WHERE 0=0 " & vbCrLf & IIf(Parameters!ProductName.Value<> "", " AND ITM.ProductName LIKE @ProductName ", "") & vbcrlf & IIf(Parameters!ProductNumber.Value<> "", " AND ITM.ProductNumber LIKE @ProductNumber", "") & vbcrlf & IIf(Parameters!ProductType.Value<> "", " AND ITM.ProductType = @ProductType", "") & vbcrlf & IIf(Parameters!ProductSubtype.Value<> "", " AND ITM.ProductSubtype = @ProductSubtype ", "") & vbcrlf & "ORDER BY ITM.ProductNumber" & vbCrLf
The expression can be created using an Excel formula (after vbCrLf observer the empty space needs to be set, otherwise the report will fail):
="& """ & A1 & """ & vbCrLf
The four parameters created for exemplification need to be defined also in the dataset accordingly. And here's report's output:
For Product Number and Name the User can use wildcards - that's what the "*" besides parameter's Prompt signifies.
Unfortunately, the logic needs to be extended accordingly as soon as further attributes not available in the data entities are needed. In this scenario and several others, using directly the base tables allows more flexibility even if the logic behind the data entity needs to be duplicated.
Notes:
(1) When building and testing the report use only a small subset of the data.
(2) For the dropdown parameters the "(all)" label was added which has an empty value. It allows in the expression to ignore the parameter, when selected.
(3) In text values with a high probability of coming from Excel sheets (e.g. Product Name or Description) it might be a good idea to replace char (10) line feed, char (13) ENTER, and even char (9) horizontal Tab values, otherwise the data might not be exported correctly to Excel.
(4) If the definitions for Enums change, the mappings need to be changed accordingly.
(*) 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