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
Happy coding!
Previous Post <<||>> Next Post
No comments:
Post a Comment