13 January 2021

SQL Reloaded: Useful Queries for D365 FO (and not only)

Microsoft Dynamics 365 for Finance and Operations (D365 FO or simply D365) has currently more than 12000 tables, therefore attempting to understand the data model behind might seem at first sight to be a foolhardy effort. Fortunately, only a small percentage from all the tables contain meaningful master or transactional data, fact that makes easier the task. 

Moreover, the tables have meaningful prefixes (e.g. 'Invent' for Inventory, 'Purch' for Purchae Orders, 'Sales' for Sales Orders, etc.) and postfixes (e.g. 'Table for header data, 'Line' for position data, 'Trans' for transaction data) while their names are in many cases self-explanatory if one knows the corresponding business terms.

For those coming from earlier versions of D365 (e.g. Microsoft Dynamics AX 2009), with a few exceptions in the inventory transactions, product master data, addresses and finance dimensions, the data structures remained almost the same, if one considers the main functional area of the ERP system. However as new modules and/or functionality were added, the number of new tables increased considerably between the versions. 

On the other side in D365 the data are exposed through data entities, abstractions from the physical implementation of the database tables [1], implemented as views typically having the 'Entity' postfix. In many scenarios the data entities more likely will prove to be the best way of accessing the data, as they include an important part of the logic one will need anyway to reengineer, however there are maybe also exceptions in which is needed to work with the base tables. In the end it will be needed to know the main data entities as well the important tables related to them, being needed to check which views reference a given table, respectively which tables are references by a view. This can be easily achieved via the sys.sql_expression_dependencies system catalog view. 

The following queries can be run in non-production D365 environments:

-- retrieving the views where an object was used 
SELECT SCHEMA_NAME(obj.SCHEMA_ID) + '.'+ obj.name AS ReferencingObject
, obj.type_desc
, SCHEMA_NAME(obj.SCHEMA_ID) + '.'+ sed.referenced_entity_name AS ReferencedObject
, obr.type_desc ReferencedObjectType
FROM sys.all_objects obj
     JOIN sys.sql_expression_dependencies sed 
       ON obj.OBJECT_ID = sed.REFERENCING_ID 
	      JOIN sys.all_objects obr
		    ON sed.referenced_id = obr.object_id
WHERE sed.referenced_entity_name = 'PurchTable'
  AND obj.Type = 'V'
  AND obj.name LIKE 'Purch%'
ORDER BY ReferencingObject

Output (12 records):
ReferencingObjecttype_descReferencedObjectReferencedObjectType
dbo.PURCHASEORDERCUBEVIEWdbo.PURCHTABLEUSER_TABLE
dbo.PURCHASEORDERRESPONSEHEADERSUMMARYVIEWdbo.PURCHTABLEUSER_TABLE
dbo.PURCHLINEBACKORDERVIEWdbo.PURCHTABLEUSER_TABLE
dbo.PURCHLINEBACKORDERWORKSPACEVIEWdbo.PURCHTABLEUSER_TABLE
dbo.PURCHLINEEXPANDEDVIEWdbo.PURCHTABLEUSER_TABLE
dbo.PURCHORDERAPPROVEDVIEWVIEWdbo.PURCHTABLEUSER_TABLE
dbo.PURCHPREPAYOPENBASEVIEWdbo.PURCHTABLEUSER_TABLE
dbo.PURCHPURCHASEORDERHEADERCHARGEENTITYVIEWdbo.PURCHTABLEUSER_TABLE
dbo.PURCHPURCHASEORDERHEADERENTITYVIEWdbo.PURCHTABLEUSER_TABLE
dbo.PURCHPURCHASEORDERHEADERV2ENTITYVIEWdbo.PURCHTABLEUSER_TABLE
dbo.PURCHPURCHASEORDERLINECDSENTITYVIEWdbo.PURCHTABLEUSER_TABLE
dbo.PURCHTABLENOTARCHIVEDVERSIONSVIEWdbo.PURCHTABLEUSER_TABLE

The inverse search: 

-- retrieving the objects referenced in a view
SELECT SCHEMA_NAME(obj.SCHEMA_ID) + '.'+ obj.name AS ReferencingObject
, obj.type_desc ReferncingObjectType
, SCHEMA_NAME(obj.SCHEMA_ID) + '.'+ sed.referenced_entity_name AS ReferencedObject
, obr.type_desc ReferencedObjectType
FROM sys.all_objects obj
     JOIN sys.sql_expression_dependencies sed 
       ON obj.OBJECT_ID = sed.referencing_id 
	      JOIN sys.all_objects obr
		    ON sed.referenced_id = obr.object_id
WHERE obj.name = 'PURCHPURCHASEORDERHEADERV2ENTITY'
ORDER BY ReferencedObject

Output:
ReferencingObjectReferncingObjectTypeReferencedObjectReferencedObjectType
dbo.PURCHPURCHASEORDERHEADERV2ENTITYVIEWdbo.ACCOUNTINGDISTRIBUTIONTEMPLATEUSER_TABLE
dbo.PURCHPURCHASEORDERHEADERV2ENTITYVIEWdbo.CONFIRMINGPOUSER_TABLE
dbo.PURCHPURCHASEORDERHEADERV2ENTITYVIEWdbo.DIMENSIONSETENTITYVIEW
dbo.PURCHPURCHASEORDERHEADERV2ENTITYVIEWdbo.GetValidFromInContextInfoSQL_SCALAR_FUNCTION
dbo.PURCHPURCHASEORDERHEADERV2ENTITYVIEWdbo.GetValidToInContextInfoSQL_SCALAR_FUNCTION
dbo.PURCHPURCHASEORDERHEADERV2ENTITYVIEWdbo.HCMWORKERUSER_TABLE
dbo.PURCHPURCHASEORDERHEADERV2ENTITYVIEWdbo.LOGISTICSPOSTALADDRESSUSER_TABLE
dbo.PURCHPURCHASEORDERHEADERV2ENTITYVIEWdbo.LOGISTICSPOSTALADDRESSBASEENTITYVIEW
dbo.PURCHPURCHASEORDERHEADERV2ENTITYVIEWdbo.PURCHTABLEUSER_TABLE
dbo.PURCHPURCHASEORDERHEADERV2ENTITYVIEWdbo.PURCHTABLE_WUSER_TABLE
dbo.PURCHPURCHASEORDERHEADERV2ENTITYVIEWdbo.REASONTABLEREFUSER_TABLE
dbo.PURCHPURCHASEORDERHEADERV2ENTITYVIEWdbo.TMSPURCHTABLEUSER_TABLE
dbo.PURCHPURCHASEORDERHEADERV2ENTITYVIEWdbo.TRANSPORTATIONDOCUMENTUSER_TABLE
dbo.PURCHPURCHASEORDERHEADERV2ENTITYVIEWdbo.VENDINVOICEDECLARATION_ISUSER_TABLE

Notes:
1. There will be cases in which is needed to check views' definition. To understand the logic it will be needed to format the queries in a more readable form. This can take time, given that the joins and the further constraints are not always straightforward. Therefore, it might be a good idea to export a formatted copy of the view for later use.
2.  Unfortunately, the data model for D365 is not publicly available. One can use however the data model for AX 2012 (see [2] [3]) as basis and fill the gaps when needed. 
3. The important changes between the various data models versions are partially documented in several documents available via the Microsoft Dynamics CustomerSource. 
4. For non-production environments the data from views, tables or other database objects can be exported directly from the database. Unfortunately, Microsoft blocked the direct access to production environments. The data from tables can be accessed and exported via the table browser, while the data from entities can be exported to Excel typically from the display forms or via the Data Management Framework.
5. The queries can be used to explore any SQL Server database. The logic can be easily encapsulated in a view to simplify the calls. 

References:
[1] Microsoft Dynamics 365 (2020) Data entities overview [source]
[2] Microsoft Docs (2014) Application tables [source]
[3] Microsoft Docs (2014) Base Enums AX 2012 [source]

No comments:

Related Posts Plugin for WordPress, Blogger...

About Me

My photo
Koeln, NRW, Germany
IT Professional with more than 24 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.