Dynamics 365 Connector

Summary: Explore the main features and benefits of the Dynamics 365 Connector for Snowflake.

• Seamless Data Integration: The connector simplifies importing data from Azure Synapse Link into Snowflake, providing support for the incremental timestamp-based folder structure used in Azure Synapse Link.
• Near-Real-Time Data Updates: It allows organisations to work with near-real-time data from Dynamics 365 applications, ensuring timely insights and analytics.
• Advanced Analytics Capabilities: Snowflake's platform allows businesses to use their Dynamics 365 data for advanced analytics, reporting, and business intelligence (BI) with tools such as dbt, Tableau, or Power BI.
• Scalable and Secure: As a native Snowflake application, the Dynamics 365 Connector is designed to handle large data volumes in a scalable manner, ensuring data integrity and security.
• Automated Data Refresh: The connector automates the process of ingesting new or updated data, eliminating manual data transfers or custom-built data pipelines.

Summary: Review the requirements you need in both Snowflake and Azure before installing the connector.

Before installing the Dynamics 365 Connector, ensure you have the following:

Snowflake Requirements

• Snowflake Account: Active Snowflake account with ACCOUNTADMIN privileges
• Account Privileges: EXECUTE TASK and EXECUTE MANAGED TASK privileges
• Role: A role with permissions to install and manage Native Apps

Azure Requirements

• Dynamics 365 Environment: Active Dynamics 365 Finance, Supply Chain, CE, Commerce, or Dataverse environment
• Azure Subscription: Active Azure subscription with permissions to create resources
• Azure Data Lake Storage Gen2: Storage account configured for Azure Synapse Link
• Azure Event Grid: Configured for storage event notifications

Permissions

• Azure: Contributor or Owner role on the Azure subscription
• Dynamics 365: System Administrator role to configure Azure Synapse Link

Summary: Follow these steps to get the Dynamics 365 Connector up and running quickly.

Get up and running with Dynamics 365 Connector in under 15 minutes:

1. Install from Marketplace Navigate to Snowflake Marketplace Search "Dynamics 365 Connector" Click "Get"
2. Grant Privileges Accept and grant required account privileges (EXECUTE TASK, EXECUTE MANAGED TASK)
3. Create Integrations Set up Storage Integration and Notification Integration (see detailed guides below)
4. Create Environment Open the app Create new environment Enter Azure storage details
5. Start Sync Enable data synchronisation View status in the app UI

For detailed step-by-step instructions, see the Setup & Configuration section below.

Summary: Detailed instructions for configuring the connector and all required integrations.

1. Install the Connector The Dynamics 365 Connector is available via Snowflake's Native Application Marketplace. Install the connector directly within Snowflake.
2. Grant Required App Privilege Grant the required privileges, events and logs from UI:
   • Account level privileges: EXECUTE TASK and EXECUTE MANAGED TASK
   • This app collects ERROR-level logs and records TRACES ON EVENT. Enable these logs and event sharing.
   • The information collected includes the timestamp folder names in the Azure Datalake and entity names within each folder.
3. Configure Azure Synapse Link Ensure your Dynamics 365 application is set up to use Azure Synapse Link for data export.
4. Create Storage Integration Please read Guide: Set up Storage Integration in Snowflake.
5. Create Notification Integration Please read Guide: Set up Notification Integration in Snowflake.
6. Grant USAGE Privilege After you create the Snowflake Storage Integration and Notification Integration, grant USAGE privilege to the app using the following commands:

   grant usage on integration <your_storage_integration> to application dynamics_365_connector;
   grant usage on integration <your_notification_integration> to application dynamics_365_connector;

7. Create New D365 Environment Set up a new environment in the app connected to a Dynamics 365 Azure Data Lake container.
8. Set Data Synchronisation Scheduled data synchronisation depending on business requirements and Azure Synapse Export frequencies.
9. Start Querying Data Once the data is ingested, users can start querying the data in Snowflake using standard SQL and integrating it with analytics tools.

Summary: Understand which Snowflake objects you need to create and configure for the connector to work.

You are required to create the following Snowflake integration objects in your Snowflake account and grant the necessary usage privileges to this application. Furthermore, when setting up a new environment, you will need to provide these objects in the Create Environment form.

Should you require assistance in creating these objects, please do not hesitate to reach out to us. We are here to support you.

Monitor the synchronisation status of your Dynamics 365 tables. View last sync times, record counts, and identify any sync errors or warnings.

Configure new connections to Dynamics 365 environments. Specify Azure Storage account details, event grid settings, and target Snowflake objects.

Update existing connection settings, pause/resume synchronisation, and manage table selections for sync operations.

A dedicated UI tab provides users with detailed insights into the status and task process history for each created environment. This tab is vital for effectively monitoring and managing the specific environment.

In the screenshot above, four environments are created, namely "ce_prd", "ce_tst", "fo_prd" and "fo_tst".

Key Features of the Dedicated Environment UI Tab

• Status Overview: Users can quickly view the current status of the environment, including whether it has been started, suspended, or is experiencing any issues.
• Task Process History: The tab maintains a comprehensive log of all task executions associated with the environment, including execution dates/times, outcomes, and duration.
• Actionable Insights: The dedicated tab highlights any warnings or errors during task execution and suggests actions for resolution.

Database Schema Overview

Table Structure

View Definitions

1. Log into the Azure Portal.
2. Follow the Microsoft documentation to create a storage account.
   • Create a storage account to use with Azure Data Lake Storage
   • Choose a Standard general-purpose v2 account type.
   • In the Advanced settings, make sure to enable the hierarchical namespace.

1. Enable Azure Synapse Link in Dynamics 365
   1. Sign in to Power App Open your Power Apps and log in to the Dataverse environment linked with D365FO environment.
   2. Create Azure Synapse Link
      1. On the left navigation pane, select Azure Synapse Link.
      2. On the Azure Synapse Link for Dataverse page, select + New link on the command bar.
   3. Select Azure Storage Account
      1. Select Subscription, Resource group and a Storage account. You don't need to provide a Synapse workspace or a Spark pool.
      2. Select Next. The option to choose tables appears.
   4. Enable Incremental Update Folder Structure
      1. Select Advanced, select Show advanced configuration settings, and then enable the option Enable incremental update folder structure
      2. In the Time interval field, choose the desired frequency for reading incremental data. Using this frequency, the system partitions data into time stamped folders such that you can read the data without being impacted by ongoing write operations.
      3. Select the Dataverse tables you want. You can also select finance and operations tables. The options Append only and Partition available at a table level are ignored. Data files are always appended and data is partitioned yearly.
      4. Select Save. Tables selected are initialised and you see incremental data in the storage account.
         

Please see Microsoft documentation for more detail:

• Access incremental data changes from finance and operations

1. Log in to Snowflake Access your Snowflake account through your Snowflake web interface.
2. Create Storage Integration You can create a storage integration using the Snowflake SQL command. Here's a template command:

   create storage integration <your_storage_integration>
     type = external_stage
     storage_provider = 'azure'
     enabled = true
     azure_tenant_id = '<your_azure_tenant_id>'
     storage_allowed_locations = '<your_container_url>'
   ;

   • <storage_integration_name> is the name of the storage integration object.
   • <azure_tenant_id> is your Azure tenant ID.
   • <azure_container_location> is the location of the Azure storage containers where the incremental folders reside.
3. Run the code below to get the Azure Consent URL and the integration name.

   describe integration <storage_integration_name>;

   Copy the azure_consent_url and paste it into a browser. Follow the on-screen prompt to give authorisation. Note down the azure_multi_tenant_app_name; this will be used to access the Azure storage container.

4. In Azure Portal, Navigate to the Azure Data Lake container. Under Access Control (IAM), assign the Storage Blob Data Reader role to the newly created azure_multi_tenant_app_name.

Please follow the detailed instructions from Snowflake Documentation:

• Step 1: Create a Cloud Storage Integration in Snowflake
• Step 2: Grant Snowflake Access to the Storage Locations

1. Create an Azure Storage Queue
   1. Open Azure Portal Navigate to your Azure Portal: Azure Portal.
   2. Create a Storage Queue
      1. Go to your Storage Account.
      2. Under Data Storage, select Queues.
      3. Click on + Queue to create a new queue.
      4. Enter a name for your queue and click OK.
2. Create an Event Grid Subscription
   1. Navigate to Event Grid In the Azure Portal, search for Event Grid and select Event Subscriptions.
   2. Create a New Event Subscription Click on + Event Subscription.
   3. Configure the Event Subscription
      1. Name: Provide a name for your event subscription.
      2. Event Schema: Select Event Grid Schema.
      3. Event Type: Choose the types of events you want to listen to (e.g., Blob Created, Blob Deleted, etc.).
   4. Endpoint Type
      1. Choose Azure Storage Queue as the endpoint type.
      2. Under Endpoint, select your storage account and then select the queue you created earlier.
   5. Filter Options Add filters sent to the queue. When setting up an Azure Event Grid Subscription, utilising subject filters to refine the events that trigger the subscription is beneficial. Specifically, you can configure the filters to capture only those events where the subject ends with /model.json. This approach is particularly advantageous for applications designed to synchronise model.json files into Snowflake.

      By focusing solely on the model.json files, the system can efficiently monitor for changes and automatically sync data when new folders are added. This targeted filtering not only streamlines the data ingestion process but also reduces the volume of irrelevant events that need to be processed.

      Implementing subject filters enhances performance by minimizing the load on the event processing system and ensuring that Snowflake remains updated with the most pertinent information. This method promotes an efficient workflow, enabling timely access to critical data for analytics and reporting purposes.

   6. Create Subscription Click Create to finalise the event subscription.

Summary: This guide walks you through creating a notification integration in Snowflake to receive Azure Event Grid events via an Azure Storage Queue, enabling automated and efficient data ingestion.

1. Log into Snowflake
   1. Access your Snowflake account.
   2. Create the Notification Integration
      • Use the following SQL command to create a notification integration for Azure Storage Queue:

        create notification integration <your_notification_integration>
          type = queue
          notification_provider = azure_storage_queue
          enabled = true
          azure_tenant_id = '<azure_tenant_id>'
          azure_storage_queue_primary_uri = '<azure_storage_queue_primary_uri>'
        ;

      • where:
        • <notification_integration_name> is the name of the notification integration object.
        • <azure_tenant_id> is your Azure tenant ID.
        • <azure_storage_queue_primary_uri> is a queue created for receiving data lake blob create events.
   3. Run the code below to get the Azure Consent URL and the integration name.

      describe integration <notification_integration_name>;

      Copy the azure_consent_url, paste it into a browser, and follow the on-screen prompt to give authorisation. Note down the azure_multi_tenant_app_name; this will be used to access the Azure Storage Queue.

2. In Azure Portal
   1. Navigate to the Azure Data Lake queue.
   2. Under Access Control (IAM), assign the Storage Queue Data Contributor role to the newly created azure_multi_tenant_app_name.
3. Verify the notification integration
   1. Each created environment will have an external stage mapped to the Data Lake container. This stage mirrors the full container folder and file structure of the container at the time of environment initialisation.
   2. After initialisation, any newly created folders in the Data Lake should be reflected in the stage through the notification integration. If the latest folders aren't showing up, verify that the notification integration is correctly set up — it's responsible for triggering the stage refresh when new folders or model.json files are added.

Please follow the detailed instructions from Snowflake Documentation:

• Step 2: Creating the Notification Integration

Symptom

New data from Dynamics 365 is not appearing in Snowflake tables.

Possible Causes & Solutions

• Azure Synapse Link Not Configured: Verify Azure Synapse Link is enabled and exporting data to Azure Data Lake. Learn more
• Notification Integration Issue: Check that the notification integration is properly configured and receiving events. Snowflake Docs
• Task Suspended: Review task history in the Environment tab to check for errors or suspended tasks.
• Storage Integration Permissions: Verify the storage integration has access to the Azure Data Lake container.

Symptom

Error messages related to insufficient privileges or access denied.

Solutions

• Verify Account Privileges: Ensure EXECUTE TASK and EXECUTE MANAGED TASK are granted.
• Check Integration Usage: Confirm USAGE privilege is granted on both storage and notification integrations.
• Azure IAM Roles: Verify the Snowflake service principal has Storage Blob Data Reader and Storage Queue Data Contributor roles.

Symptom

Expected tables or columns from Dynamics 365 are missing in Snowflake.

Solutions

• Azure Synapse Link Export: Check which entities are configured for export in Azure Synapse Link.
• Data Lake Folder Structure: Verify the folder structure matches the expected timestamp-based format.
• Manual Refresh: Trigger a manual refresh from the Environment tab to force a sync.

Summary: Find answers to common questions about data sync, historical data, deletions, and multi-environment support.

How often does data sync?

Data syncs based on the schedule configured in your environment settings and Azure Synapse Link export frequency. Typical intervals range from 15 minutes to hourly.

Can I sync historical data?

Yes, the connector will ingest all existing data in the Azure Data Lake at the time of environment creation, then continue with incremental updates.

What happens if I delete data in Dynamics 365?

Deletions are reflected in Snowflake based on Azure Synapse Link's change tracking. The connector processes incremental updates including deletes.

Can I use multiple environments?

Yes, you can create multiple environments for different Dynamics 365 instances, such as production and testing environments.

Summary: Get help, access documentation, and learn about support options for the Dynamics 365 Connector.

Get Started

• Installation Support: Contact JayConsulting for guided installation assistance
• Licensing & Pricing: Inquire about enterprise licenses and volume pricing
• Custom Configuration: Professional services for complex integration scenarios

Additional Resources

• Microsoft Azure Synapse Link Documentation

Enterprise Support

• Priority technical support with fast response times
• Custom feature development