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.
Previous Post <<||>> Next Post
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