Inherited Tables when using EntityUtil to generate data entities as views in Fabric

[peterloudon]

Hi

These are questions more than issues, but I don't know where else to ask them:

1. Given a list of data entities, how do I know which inherited tables are required?
2. Is it ever necessary to regenerate the tableinheritance.json file, and, if yes, how?
3. Does the answer to point 2 above change if there is an ISV product which has it's own data entities which also need to be created as views in Fabric?

Thank you
Peter

[swood-nz]

@peterloudon

Points 1 & 2 are not related as 1 regards data entities & 2 is regarding 'derived' tables (e.g. DirPartyTable) - the entity DirParty can be thought of as a view over other views and tables:

From AxDB
CREATE VIEW [dbo].[DIRPARTYENTITY] AS SELECT T1.PARTYNUMBER ....

FROM DIRPARTYBASEENTITY T1
LEFT OUTER JOIN LOGISTICSPOSTALADDRESSBASEENTITY T2 ON ....

CREATE VIEW [dbo].[DIRPARTYBASEENTITY] AS SELECT T1.PARTYNUMBER AS PARTYNUMBER ....
FROM DIRPARTYTABLE T1
LEFT OUTER JOIN LOGISTICSELECTRONICADDRESS T2
.....
LEFT OUTER JOIN DIRDUNSNUMBER T16

So, entities can be recreated in Fabric by replicating the AxDB views.

For Derived tables, start with getting the list of derived tables from AxDB

Use the script in Analytics/DataverseLink/DataIntegration/AdditionalScripts/get_derivetables.sql to determine which derived tables exist
This part of the script returns all Bast Tables and the 'derived' tables that are linked

SELECT
      DerivedTable.Name as derivedTable,
	  DerivedTable.ID as derivedTableId,
      BaseTable.NAME as BaseTable,
	  BaseTable.ID as BaseTableId
FROM dbo.TableIdTable DerivedTable
 JOIN dbo.SYSANCESTORSTABLE TableInheritance on TableInheritance.TableId = DerivedTable.ID
LEFT JOIN dbo.TableIdTable BaseTable on BaseTable.ID = TableInheritance.ParentId
where TableInheritance.ParentId != TableInheritance.TableId

From the BaserTable column we can see all tables that are derived from joins to other tables and will need to be created as views in Fabric.

Executing the script with DirPartyTable in the parenttable CTE
We see that DirPartyTable is a derived table consisting of childtables:
CompanyInfo,DirOrganization,DirOrganizationBase,DirPerson,OMInternalOrganization,OMOperatingUnit,OMTeam

This list would then need to be converted into the json format in file tableinheritance.json

[{"parenttable":"DirPartyTable","childtables":[{"childtable":"CompanyInfo"},{"childtable":"DirOrganization",{"childtable":"DirOrganizationBase"},{"childtable":"DirPerson"},{"childtable":"OMInternalOrganization"},{"childtable":"OMOperatingUnit"},{"childtable":"OMTeam"}]}]