D365 CRM
For CRM the files start with tables’ names and have the EntityMetadata.json postfix, the files being stored in the Microsoft.Athena.TrickleFeedService folder which lies with the other folders containing the data. Given that all files are in the same folder, the following query can be used to export the metadata for all or some of the tables. Just replace "(container name)" and "(data source)" with the corresponding values for your environment.
-- export definition for D365 CRM' CDM (all attributes, all tables) SELECT DAT.EntityName , DAT.AttributeName , DAT.Timestamp , DAT.AttributeType , DAT.MetadataId , DAT.Precision , DAT.MaxLength FROM openrowset( bulk '/(container name)/Microsoft.Athena.TrickleFeedService/*-EntityMetadata.json', data_source = '(data source)', format = 'csv', fieldterminator ='0x0b', fieldquote = '0x0b', rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON ) with (doc nvarchar(max)) as rows CROSS APPLY OPENJSON(doc, '$.AttributeMetadata')--definitions[0].hasAttributes with ( EntityName nvarchar(255) '$.EntityName' , AttributeName nvarchar(255) '$.AttributeName' , Timestamp nvarchar(50) '$.Timestamp' , AttributeType nvarchar(50) '$.AttributeType' , MetadataId nvarchar(100) '$.MetadataId' , Precision int '$.Precision' , MaxLength int '$.MaxLength' ) as DAT WHERE DAT.EntityName IN ('lead', 'opportunity', 'product')
For Finance & Operations folders’ structure is more complex, a whole hierarchy of folders being built based on a set of predefined categories. One would need to traverse the hierarchy structure to find the files. Thus, it might be easier to generate the metadata for each table. Files’ names start with tables’ names and have the .cdm.json postfix. The below query generates the metadata for the InventTable table. Just replace the "(container name)", "(instance name)" and "(data source)" with the values for your environment.
-- export definition for D365 FO's CDM (all attributes, specific table)
SELECT DAT.name , DAT.dataFormat FROM openrowset( bulk '/(container name)/(instance name)/Tables/SupplyChain/ProductInformationManagement/Main/InventTable.cdm.json', data_source = '(data source)', format = 'csv', fieldterminator ='0x0b', fieldquote = '0x0b', rowterminator = '0x0b' --> You need to override rowterminator to read classic JSON ) with (doc nvarchar(max)) as rows CROSS APPLY OPENJSON(doc, '$.definitions[0].hasAttributes') with ( name nvarchar(255) '$.name' , dataFormat nvarchar(50) '$.dataFormat' ) as DAT
Further Steps
No comments:
Post a Comment