Replicate Dynamics365 In Snowflake
appRules features an unmatched number of options for migrating, replicating and synchronizing data.
The "Replicate Dynamics365 In Snowflake" sample project shows how to utilize the appRules Data Migration Engine for enterprise-scale data replication. As you review the project, note the following:
- The entire project uses a single workflow activity - the MigrateData activity.
- Configurations are used for all aspects of the project instead of scripts or code.
- Easily configurable options for optimizing processing including Source and Target workers.
- The process is the same not only for Dynamics365 but for hundreds of sources, such as Dynamics CRM, Salesforce, SharePoint, etc.
- The process is the same not only for Snowflake but for other supported targets of appRules Data Replication, such as ADLS, AmazonS3, Azure Synapse, Cassandra, DB2, Databricks, BigQuery, MySQL, Oracle, PostgreSQL, Redshift, SQL Azure, SQL Server, etc.
The workflow activities and other modules used in this sample project are available in the following appRules Platform products:
appRules Data Migration Engine
appRules Integration & Automation Engine
appRules Enterprise 360
If you are running the On-Premise, Azure Marketplace or AWS Marketplace editions, the following hosting options supported by appRules are recommended for running this sample project:
Recommended for Project
appRules Host Service
Since the appRules Host Service is automatically installed and ready to go, it is recommended that you use it for evaluating this sample project. Just point your browser to the appRules URL
The completed project is available in the appRules Samples project database. Click the "Open" toolbar button on the appRules home page to select the "Replicate Dynamics365 In Snowflake" project.
First, we will create a new project for the data replication job. Click the New button on the Project toolbar of the appRules Home Page. Simply enter the name of the project - (Replicate Dynamics365 In Snowflake) and click the Save button.
Creating a New Project
We will configure the entire job using the appRules MigrateData activity. Drag the MigrateData activity from the Migrate-Replicate-Synchronize module of the toolbox on to the designer and configure it as described in the following sections.
The MigrateData Activity
appRules uses data source metadata extensively for all processing. Before configuring the migration project we must first connect to Dynamics365 to generate the metadata of the data source. Once generated, the data source metadata can be used in all your appRules projects including Business Rules, Integration and Automation. There is no limit on the number of data sources and related metadata.
To generate metadata and create the Dynamics365 data source, click the "New DataSource" button on the toolbar. Select the Connector (from CRM or ERP categories) and configure as shown in figures below. Follow subsequent prompts to generate the metadata.
Dynamics365 connector selection from the CRM or ERP data source categories
Configure Dynamics365 data source and Connection Properties
Now that we have created the Dynamics365 data source, select Dynamics365 as the default source, Data Replication as the data migration type, and Snowflake as the target. On subsequent modals, click the OK toolbar button to continue.
Initializing the Data Replication Process
Configure the connection properties and schema options for Snowflake:
Snowflake Connection Properties
Select the Dynamics365 entities to be replicated in Snowflake:
Dynamics365 Source Entities Selection
The entities to be replicated are shown in the appRules Data Replication Target Entities collection:
appRules Data Replication Target Entities
Snowflake Table Definitions
The definitions of the target tables are automatically generated by appRules and can be modified to suit your needs:
Snowflake Table Definitions
Snowflake Table Column Definitions
The definitions of the target table columns are automatically generated by appRules and can be modified to suit your needs:
Snowflake Table Column Definitions
The definitions of the Dynamics365 to Snowflake field maps are automatically generated by appRules and can optionally be modified to suit your needs. For example, you can use appRules field mapping to transform Dynamic365 data before saving in Snowflake.
The MapRecordFieds Activity
You can also optionally define Mapping Conditions to be utilized in the mapping process using the appRules Condition Editor:
Control Mapping Using appRules Condition Editor
To configure a Condition to be applied to a row in the MapRecordFields modal:
- Configure the Condition by navigating the Condition tree on the appRules Condition Editor (Condition-->Category-->Type).
- Configure the properties of the selected condition type.
- Name and save the condition.
- Apply the condition by selecting it on the Mapping Condition column of a specific row of the table in the MapRecordFields modal.
You can quickly transform values in the mapping activity modal by configuring Action Fields without writing code using the appRules Action Editor:
Transform Values Using appRules Action Editor
To transform a value using the ActionField option:
- Configure the Action by navigating the Action tree on the appRules Action Editor (Field-->Field-->Module-->Type).
- Configure the properties of the selected action type.
- Name and save the action.
- On a specific row of the MapRecordFields modal, select ActionField as the Source of the value to be mapped to a target field. Then select the Action that you have created above.
The appRules Data Replication Target Entities collection is automatically generated for you and is ready to run the replication jobs for the selected entities. As an option, you can make changes to the entities to suit your needs:
- Job Execution Mode - Select Default, Sequential or Parallel execution
- Target Loader Settings including batch size, and error tracking
- Source records query options - Property Configuration or Custom Query
- Optional Source Workers to split the source records and handing off to additional sessions
- Optional Target Workers to optimize processing by configuring additional sessions and threads.
- Audit Logging Settings to capture details for replication detail record reports and statistics.
- More ... Explore the other options of the collection entries for the entities to be replicated
As you configure your project, appRules automatically organizes and stores the project settings. Before running the project, you can review the project settings from a central location by clicking on the Project Details button:
Review and make any changes in the following tabs:
- Run Options: Use this tab to make changes related to the defaults for running the job.
- Status: View the current status of the project
- Schedule: Use this tab to optionally schedule the job to be run
- Notifications: Define optional notifications
- Arguments: Define optional project variables
- Activities: View the activities that have been defined for the project by dragging and dropping from the toolbox on the designer.
- Notes: View or edit notes associated with the project
- Json: View project JSON
- Projects: View optional child projects associated with this project
Validations, error handling, and notifications are critical components for executing successful data migration, data replication and data synchronization projects.
appRules includes the following options for error handling and notifications:
- Automatic and on-demand validation is available for all workflow activities.
- Automatic and on-demand validation is available for all target entity definitions.
- Incomplete, Terminated, and errored job instances can be rerun to process only the incomplete or errored records. This saves a great deal of time as successfully transferred records are skipped - greatly reducing the load trips to the source.
- Edit the LoaderSettings property of any Target Entity definition to optionally configure error tolerance threshold and the action to perform when the error limit is exceeded:
- Edit the Notifications tab of the Project Settings to define project-wide notifications:
Configure each entity replication entry to suit your needs. Then the job can be run directly, scheduled to run, or run via the appRules Web API.
- Click to Run toolbar button to run the project directly. Select the server and other settings for the job run.
- To schedule the job, edit the Schedule tab of the Project Details:
If you are running a long running job, you can monitor it by using the appRules Job Monitor:
- View job details
- Set a job control value
- Pause job
- Cancel job
Monitor Jobs Using appRules Job Monitor
During and after the run, you can utilize the many facilities available in appRules to view results and statistics.
You can browse the the source and newly replicated data records using the appRules Data Source Browser as shown in the figure below.
You can define queries to load records and optionally even edit data records using the appRules Data Source Browser:
appRules Data Source Browser
To view "Data Migration Logs and Statistics", click the "Logs/Statistics" button on the "appRules Data Migration: Target Entities" toolbar. The following modal will be displayed:
Data Migration Logs and Statistics
appRules Data Migration Logs and Statistics offer a rich set of options to give you insight into running or completed jobs as show in the sections below. Simply configure the selection criteria that meets your requirements and click on the Summary, Statistics, Project Run Details or Audit Log Details toolbar button.
At any point, you can access the Data Migration Job Summary to view the results of running or completed instances:
- Job Status
- Completed Record Counts
- Skipped Record Counts
- Error Record Counts
Data Migration Job Summary
Gain insight on the state of running or completed job instances by viewing job statistics.
Data Migration Job Statistics
Gain insight on the state of running or completed job instances by viewing Project Run Details:
- Job Instance detail including reference, start, end, duration, current state, etc.
- Snapshot View of Audit Log Details including Success, Failed, and Skipped Record counts.
- Workflow Activity Statistics
- Data Source Statistics
- Condition Execution Results
- Action Execution Results
- Arguments & Variables
- Runtime Logs & Errors
Project Run Details
You can configure your appRules data migration, replication and synchronization jobs to capture all pertinent information regarding every source and target record involved in the process. This level of detail is especially useful in heavily validated organizations and offers the best tool for reconciling the results of the process.
Audit Log Details can be viewed by accessing the Data Migration Logs and Statistics modal and selecting the range of log entries to view.
Audit Log Details
Powered by the all-in-one appRules Platform, the appRules Data Migration Engine offers the most powerful and flexible end-to-end solution for enterprise-scale data migration, data replication and data synchronization for IT Departments, Solution Providers, and Consultants.
This sample project has been used to showcase the powerful, easy-to-use, and fully configurable features of appRules including:
- No-code environment
- Replicates data from hundreds of sources including SaaS platforms into popular targets including SQL databases and data warehouses such as ADLS, AmazonS3, Azure Synapse, Cassandra, DB2, Databricks, BigQuery, MySQL, Oracle, PostgreSQL, Redshift, SQL Azure, SQL Server, etc.
- Integrated support for hundreds of data source connectors.
- Metadata-driven solution
- All settings including source entities, target entities collection, target tables, and field mappings are automatically generated and can optionally be modified for customized implementations without writing code.
- Conditioned field mappings using the powerful appRules Condition Editor.
- Field transformations using Action Fields configured using the powerful appRules Action Editor.
- Integrated Job Monitors, Logs, Statistics and the appRules Data Source Browser.
- You can continue exploring the many features available for data migration, data replication and data synchronization in the appRules Data Migration engine. The completed project is available in the appRules Samples project database. Click the "Open" toolbar button on the appRules home page to select the "Replicate Dynamics365 In Snowflake" project.