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):
ReferencingObject | type_desc | ReferencedObject | ReferencedObjectType |
dbo.PURCHASEORDERCUBE | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHASEORDERRESPONSEHEADERSUMMARY | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHLINEBACKORDER | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHLINEBACKORDERWORKSPACE | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHLINEEXPANDED | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHORDERAPPROVEDVIEW | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHPREPAYOPENBASE | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERCHARGEENTITY | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERENTITY | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHPURCHASEORDERLINECDSENTITY | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHTABLENOTARCHIVEDVERSIONS | VIEW | dbo.PURCHTABLE | USER_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:
ReferencingObject | ReferncingObjectType | ReferencedObject | ReferencedObjectType |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.ACCOUNTINGDISTRIBUTIONTEMPLATE | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.CONFIRMINGPO | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.DIMENSIONSETENTITY | VIEW |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.GetValidFromInContextInfo | SQL_SCALAR_FUNCTION |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.GetValidToInContextInfo | SQL_SCALAR_FUNCTION |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.HCMWORKER | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.LOGISTICSPOSTALADDRESS | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.LOGISTICSPOSTALADDRESSBASEENTITY | VIEW |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.PURCHTABLE | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.PURCHTABLE_W | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.REASONTABLEREF | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.TMSPURCHTABLE | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.TRANSPORTATIONDOCUMENT | USER_TABLE |
dbo.PURCHPURCHASEORDERHEADERV2ENTITY | VIEW | dbo.VENDINVOICEDECLARATION_IS | USER_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]
[1] Microsoft Dynamics 365 (2020) Data entities overview [source]
[2] Microsoft Docs (2014) Application tables [source]
No comments:
Post a Comment