Open peterloudon opened 1 month ago
@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"}]}]
Hi
These are questions more than issues, but I don't know where else to ask them:
Thank you Peter