Data Migration Functionality Guide

This article is the guide on how to use Data Migration (Data ingestion) functionality in Trellispark. Data Migration allows developers to import data to the platform by simply inserting it into the concept dedicated SQL tables. This simplifies the process since migrated data integration (shaping and preparing data to be consumable in our platform) is already taken care of.

Plan of Action

Data Migration functionality is designed to be run on a sequence of concepts. This allows developers to import data in hierarchical levels. Where the current level is the concepts that are grouped together by its dependencies (parent-child and peer relationships). One level can have single or multiple concepts.

Before starting the migration, you need to decide on the particular order in which concepts will follow each other in the migration. Sequence is defined and run by you manually based on the concept’s dependencies. Note that circular dependencies are not supported!

To figure out the sequence for the specific data model you need to start at the top of the hierarchy since every concept does depend on its parent. Continue going down the hierarchy level by level. The only exception to the rule is if a concept references another one by it’s GUID.

See an example below on how to decide on the sequence based on the data model.

After you have decided on a sequence, these are the steps you need to take to run a migration:

Step 1: Prepare concepts for migration and generate migration scripts.

• For every single concept involved in the migration you need to generate a migration script. The migration script creates a migration table where you can insert data that you want to migrate for a specific concept. The script will also create a stored procedure that will shape and insert data from the migration table into the Instances table.

Step 2: Review migration script

• At this step you can also add additional business logic for the migration.

Step 3: Run script

Step 4: Insert data inside the migration table

• After inserting data into migration table, check that all values have been inserted to the correct columns before executing a migration. If the concept has Subforms, ensure that every record only uses fields dedicated to one corresponding Subform.

Step 5: Create Migration Run

Step 6: Run migration

Step 1.1: Concept Migration Requirements

Before starting the process, ensure that the concept definition satisfies the requirements below to avoid data loss, corruption, or issues with generating the script.

Requirements for the concept:

• Concept must have Form Name, Form Type and Build Instance Name
• Concept must have Default State
• Concept must have Needs migration table fieldset to Yes
• Concept must have Default Status set to the state that will be used for all migrated instances
• Concept must have Migration Sequence set to its corresponding number in the sequence

You can migrate records under:

A) Concept inside same Functionality

Concept must have the Parent Concept set to the future parent concept. Later when inserting records into migration table OldParentKey of new record needs to be set to OldPrimaryKey of an already existing instance type of Parent Concept.

Example:

To migrate Client under Customer you need to set the Parent Concept of Client to Customer:

Before inserting a new client record to the migration table, fetch the OldParentKey of desired parent record type of Parent Concept

Use found OldPrimaryKey as OldParentKey for the new client record:

B) Workspace

If you leave the Parent Concept field empty and do not provide the OldParentKey the record will be created under Workspace.

IMPORTANT NOTES:

• If you need to migrate some records under Workspace and some under a Concept, then you need to run two separate migrations
• If you need to migrate records under the same parent concept but parent records are spread around different hierarchies, then you can run it all under one migration run
• Records that cannot be mapped to their parent will not be migrated

You can find migration related fields in the Data Migration tab for every concept.

Step 1.2: Generate migration script

Select the Generate Data Migration Script command in the command bar to run it.

After a few seconds you should see a new attached file under Migration Scripts childlist. Select one from the bottom, download and open it.

The command will fail if any of the requirements found in the section are not satisfied.

Step 2: How to review the script

Every auto-generated script has three parts:

Part 1: Create migration table statement that was generated based on the fields of the concept.

Part 2: Create stored procedure statement that was generated based on the field definitions. This stored procedure will be executed when the Execute Migration Command is called.

The script can be run without any modifications. It also can be modified to add additional logic to the migration process. Everything that requires attention is tagged in 2 categories:

• [ToDo] to see optional individual fixes.
• [ToDos] to see summary of optional individual fixes.

All fields will be included in the script. There is only one exception to that statement:

1. Duplicate fields will be removed from the script.

The table below specifies field types that are not supported or require attention:

How to deal with duplications of XMLElement across different fields

If multiple Fields that have the same XMLElement name are added in, they will be kept and stored as a simple string instead of as a more complicated field type.

Next, you can search XMLName to find the Pre-Processing section for this field. Pre-Processing is responsible for setting the GUID attribute for value provided in migration. It is recommended to run in a cursor if there are possible repetitions of the selected value. You can also disregard the auto-generated cursor and insert your own logic.

If your field is dependent on a stored procedure, you need to replicate its logic (e.g., select statement) in between [ToDo]. The select statement should return two columns:

• GUID
• Name

Step 3: Run script

Simply run the entire script

Step 4: Insert Data into Migration Table

After the script has been run, refresh your existing tables and search amongst them for the table that was just created (such as dbo.M_ZCM_Customer In the example above). Right click on the table and begin modifying it with the relevant data you require. You will see all columns that have been added based on the information during creation that can then be filled out.

Step 5: Create Migration Run

To create a Migration Run, navigate to the functionality that the concepts are part of, navigate to the Data Migration tab, and select the Add new Migration Run button.

Remember that the Migration Run is responsible for running a migration across all concepts with same sequence number. Therefore, you need to set Sequence field to the one you will be running.

For example, if we wanted to create a Migration Run for the Customer hierarchy example mentioned previously these are the required steps.

Step one is to know the hierarchy and the sequence for all concepts. Looking at the diagram below we can see that the sequence for each concept is as follows:

• 1 Customer
• 2 Product
• 2 Client
• 3 Contract

On the Migration Run page specify the Sequence, for this example 3 Migration Runs need to be created so sequences 1-3 can be targeted.

Example of how your Migration Run should look like:

Step 6: Execute Data Migration

To run the Data Migration, select Execute Migration from the drop down menu on the top right hand side:

As soon as the migration has finished you will see the status on the Migration Run has been updated to the “execution” status. Fields like CycleGUID, User information, and Date of execution should be updated as well. CycleGUID can be used to query all records migrated in this migration run.

In case an error occurred and the migration has failed you may see the status of the Migration Run to be changed to Error Occurred or Command send to the queue [Executing]

If the error message could be captured, you can find it under Error field. See example below: