💎🤖💫SQL Reloaded: Copilot Stories (Part VI: Queries based on Dynamics 365 for Finance & Operations)

As part of ad-hoc queries, SSRS/Paginated Reports and Data Warehouse solutions, during the past 15 years I wrote hundreds of queries based on Dynamics AX, respectively on Dynamics 365 (D365) for Finance, Supply Chain, Production, Human Resources and a few other modules. So, given that the documentation for the D365 schema is available online, I've been wondering how easy would be to write a basic query using M365 Copilot. A relatively simple example would be the join between the Products (InventTable) and Prices (InventTableModule) tables, a quite simple thing to do (the joins were discussed in here).

Disclaimer: The below code was generated by Copilot via prompts used for demonstration purposes!

The first attempt proved to be far from the expectations:

Prompt:
Write a query that joins InventTable and InventTableModule to retrieve the Item Number, Description and the Item Prices from Dynamics 365 for Finance. Include tables' schema and rewrite the query by placing the comma first.

Output:

SELECT 
    , IT.ItemId AS [Item Number]
    , IT.ItemName AS [Description]
    , ITM.Price AS [Item Price]
FROM 
    dbo.InventTable IT
JOIN 
    dbo.InventTableModule ITM 
    ON IT.ItemId = ITM.ItemId 
    AND IT.DataAreaId = ITM.DataAreaId
WHERE 
    ITM.ModuleType = 0 -- Assuming 0 represents the sales module, adjust as necessary

A first version of the query didn't included the join on DataAreaId. Another problem is that compared with the previous versions, in D365 the ItemName is not available in the InventTable anymore while the ItemName was incorrectly considered as Description. The differences between the various versions of D365 are minimal, though the differences between D365 and AX 2009 are considerable with important changes due to redesign.

Prompt:
Rewrite the query to provide the other existing prices via a grouping.

Output:

SELECT 
    , IT.ItemId AS [Item Number]
    , IT.ItemName AS [Description]
    , STRING_AGG(CAST(ITM.Price AS NVARCHAR), ', ') AS [Item Prices]
FROM 
    dbo.InventTable IT
JOIN 
    dbo.InventTableModule ITM 
    ON IT.ItemId = ITM.ItemId 
    AND IT.DataAreaId = ITM.DataAreaId
WHERE 
    ITM.ModuleType = 0 -- Assuming 0 represents the sales module, adjust as necessary
GROUP BY 
    IT.ItemId, IT.ItemName

The generated query shows all the prices as a string, solution which might work if we change the delimiter, though is not an optimal solution at all.

Prompt:
Rewrite the query to provide the other existing individual prices via a grouping in separate fields.

Output:

SELECT 
    , IT.ItemId AS [Item Number]
    , IT.ItemName AS [Description]
    , MAX(CASE WHEN ITM.ModuleType = 0 THEN ITM.Price END) AS [Sales Price]
    , MAX(CASE WHEN ITM.ModuleType = 1 THEN ITM.Price END) AS [Purchase Price]
    , MAX(CASE WHEN ITM.ModuleType = 2 THEN ITM.Price END) AS [Cost Price]
FROM 
    dbo.InventTable IT
JOIN 
    dbo.InventTableModule ITM 
    ON IT.ItemId = ITM.ItemId 
    AND IT.DataAreaId = ITM.DataAreaId
GROUP BY 
    IT.ItemId, IT.ItemName

This version is closer to the original query I wrote some time ago:

-- Item prices via group by
SELECT ITD.DataAreaId 
, ITD.ItemId  
, ITD.InventPrice
, ITD.InventUnitId
, ITD.PurchPrice
, ITD.PurchUnitId
, ITD.SalesPrice
, ITD.SalesUnitId
FROM dbo.InventTable ITM
     LEFT JOIN (-- price details
     SELECT ITD.ItemId
     , ITD.DataAreaId 
     , Max(CASE ITD.ModuleType WHEN 0 THEN ITD.Price END) InventPrice
     , Max(CASE ITD.ModuleType WHEN 0 THEN ITD.UnitId END) InventUnitId
     , Max(CASE ITD.ModuleType WHEN 1 THEN ITD.Price END) PurchPrice
     , Max(CASE ITD.ModuleType WHEN 1 THEN ITD.UnitId END) PurchUnitId
     , Max(CASE ITD.ModuleType WHEN 2 THEN ITD.Price END) SalesPrice
     , Max(CASE ITD.ModuleType WHEN 2 THEN ITD.UnitId END) SalesUnitId
     FROM dbo.InventTableModule ITD
     GROUP BY ITD.ItemId
     , ITD.DataAreaId 
    ) ITD
       ON ITD.ItemId = ITM.ItemId
      AND ITD.DataAreaId = ITM.DataAreaId
WHERE ITD.DataAreaID = 'XYZ'
ORDER BY ITD.ItemId

Prompt:
Rewrite the query to provide the other existing individual prices via a grouping in separate fields.

Output:

SELECT 
    , IT.ItemId AS [Item Number]
    , IT.ItemName AS [Description]
    , Sales.Price AS [Sales Price]
    , Purchase.Price AS [Purchase Price]
    , Cost.Price AS [Cost Price]
FROM 
    dbo.InventTable IT
LEFT JOIN 
    dbo.InventTableModule Sales 
    ON IT.ItemId = Sales.ItemId 
    AND IT.DataAreaId = Sales.DataAreaId 
    AND Sales.ModuleType = 0 -- Assuming 0 represents the sales module
LEFT JOIN 
    dbo.InventTableModule Purchase 
    ON IT.ItemId = Purchase.ItemId 
    AND IT.DataAreaId = Purchase.DataAreaId 
    AND Purchase.ModuleType = 1 -- Assuming 1 represents the purchase module
LEFT JOIN 
    dbo.InventTableModule Cost 
    ON IT.ItemId = Cost.ItemId 
    AND IT.DataAreaId = Cost.DataAreaId 
    AND Cost.ModuleType = 2 -- Assuming 2 represents the cost module

The structure of the query looks promising, though same in the previous query Copilot got the uses for ModuleType wrong (see below)! And here's the query I wrote previously:

-- Item Prices without grouping
SELECT ITM.DataAreaId 
, ITM.ItemId 
, ILP.UnitId InventUnitId 
, ILP.Price InventPrice 
, IPP.UnitId PruchaseUnitId
, IPP.Price PurchasePrice
, ISP.UnitId SalesUnitId
, ISP.Price SalesPrice
FROM dbo.InventTable ITM
      LEFT JOIN dbo.InventTableModule ILP
        ON ITM.ItemId = ILP.ItemId
       AND ITM.DataAreaId = ILP.DataAreaId
       AND ILP.ModuleType = 0 -- Warehouse
      LEFT JOIN dbo.InventTableModule IPP
        ON ITM.ItemId = IPP.ItemId
       AND ITM.DataAreaId = IPP.DatareaId 
       AND IPP.ModuleType = 1 -- Purchases
      LEFT JOIN dbo.InventTableModule ISP
        ON ITM.ItemId = ISP.ItemId
       AND ITM.DataAreaId = ISP.DataAreaId 
       AND ISP.ModuleType = 2 -- Sales	
WHERE ITM.DataAreaId = 'XYZ'

Probably the Copilot for Dynamics 365 Finance and Operations [1] works much better than the one from M365. Unfortunately, I don't have access to it yet! Also, if I would invest more time in the prompts the results would be closer to the queries I wrote. It depends also on the goal(s) considered - build a skeleton on which to build the logic, respectively generate the final query via the prompts. Probably the 80-20 rule applies here as well. 

Frankly, for a person not knowing the D365 data model, the final queries generated are a good point to start (at least for searching for more information on the web) as long Copilot got the prompts right. It will be also interesting to see how business rules related to specific business processes (including customizations) will be handled. The future looks bright (at least for the ones still having a job in the field)!

Happy coding!

Previous Post <<||>> Next Post

References:
[1] Microsoft Learn (2024) Microsoft Learn: Overview of Copilot capabilities in finance and operations apps [link]