Dynamics 365 FO CDMUtilSolution no tables appear in synapse

Wayne Bartkowski 656 Reputation points
2023-02-28T03:21:06.72+00:00

I am deploying the components to read the csv and metadata files landing the azure datalake from the Dynamics 365 finance and Opertions system. I can see the folder and files in the blob storage container.

I deployed the CDM App in Azure, using the CDMUtil_AzureFunction app (not console) and configured everything as per the steps in https://github.com/microsoft/Dynamics-365-FastTrack-Implementation-Assets/blob/master/Analytics/CDMUtilSolution/deploycdmutil.md

Unfortunately I cannot see any tables being created or data moving into Synapse and can't see anyway to check that everything is connected and running as it should be. The event grid on the blob storage account should be triggering the CDMUtil_AzureFunction app when I add a file or blob but I can't see that it is. Then the app should be reading the manifest files etc in the datalake to create the tables in Synapse but can't see this happening.

Any suggestion on how to debug this would be handy or to check that the apps and events are talking as per the solution.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,362 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Wayne Bartkowski 656 Reputation points
    2023-04-28T01:13:06.5166667+00:00

    With Tom Luo assistance we got the cdmutil__azurefunction working. It is worth noting that I ran out of time a bit so I got the initial tables set up using the cdmutil__console app. Following the steps in the url https://github.com/microsoft/Dynamics-365-FastTrack-Implementation-Assets/blob/master/Analytics/CDMUtilSolution/deploycdmutil.md

    Once deployed go into functions for the cdmutilazurefuntion and disable the servicebus_cdmtosynpaseview. It appears that this is something that has been included in the new release in github but didn't work for me and just caused confusion.

    Ensure you enable MSI. Update your configuration following the table in the directions, the examples for DDLType are not real clear - I used just SynapseView and not including the words after the colon. For SQLEndPoint it looks ok but I used this which included a DB in Synapse: Server=mysynapswrkspc-ondemand (usingserverless).sql.azuresynapse.net;Database=mydb;Authentication=ActiveDirectoryMSI ( this is why msi in step 1 is important);

    You can also add in a different Schema name if you need eg I used Schema fo instead of dbo as we will be bringing in other data sources so this will identify the FO tables.

    Follow the steps to grant access to the storage account, I thought the util having contributor access was more powerful so should be fine but Computer said no, - make sure you use storage blob data contributor and storage blob data reader.

    Grant access to your synapse for your function using the scripts provided.

    The event grid set up seemed pretty straigthforward at https://github.com/microsoft/Dynamics-365-FastTrack-Implementation-Assets/tree/master/Analytics/CDMUtilSolution

    Probably make sure you understand your folder structure in your storage account and adjust the Subject begins with parameter to suit - /blobServices/default/containers/dynamics365-financeandoperations/blobs/environment.sandbox.operations.dynamics.com/

    these were about the main points of issue for me - hope this helps

    1 person found this answer helpful.