Import Configuration Overview

The CSV Import Integration allows importing data from a flat CSV file into the hierarchical structure of connected Targetprocess entities. Therefore, a deep analysis of the current state of the data and data-model in ATP and the expected state of the data after the import should precede every Import configuration.

Executed Imports cannot be rolled back automatically, so conducting some initial imports using a small subset of the data is recommended.

There are three main factors that affect the results of the Import operation:

1. The initial state of Targetprocess:
   Targetprocess defines a set of connected entities with their fields, connections, and validation rules. All the settings as defined in Targetprocess affect the way new data can be added.
2. The content of CSV file:
   A CSV file is a file contains a flat data set consisting of rows of data. Every row is divided into columns by a separator. The CSV Import integration requires the first row to be a header row defining the column names.
3. Mapping:
   The Targetprocess state and CSV files are predefined and are relatively unchangeable. Mapping is the main configuration point that impacts the Import process. The Mapping is in JSON format that describes the conversion process of flat CSV data into the hierarchical Targetproces-connected entities structure.

Mapping definition

The mapping is a JSON document with a standard structure:

{ "entitiesMappings": [] "relations": [ ], }

Mapping items definitions:

• entitiesMappings – This section describes how the CSV records should be reflected in Targetprocess. Every Mapping can have a unique name that can be used as a reference. Thanks to that we can create multiple Mappings for the same entity type.
• relations – This section allows defining relations/connections between Targetprocess entities. The relations section describes all transformations required to convert flat CSV data rows into hierarchically connected Targetprocess entities.

Every Mapping definition is based on the columns in the CSV file. The internal definition of entities in Targetprocess is found in metadata: your-account-name.tpondemand.com/api/v1/index/meta

The following use cases can be reflected in the Mapping definition.

Import of single entity type

Import of a single entity is the simplest use case for a CSV import. The CSV file defines a set of columns that can be treated as fields of a single Targetprocess entity.

The column names in the CSV file may differ from the names of fields in the Targetprocess entity. The association between the CSV columns and the Targetprocess fields must be defined in entity mapping:

{ 
  "entitiesMappings": [
    {
      "name": "UserStory",
      "tpEntityTypeName": "UserStory",
      "fieldsMappings": [
        {
          "isLookup": true,
          "tpFieldName": "Name",
          "csvColumnName": "UserStory.Name"
        },
        {
          "tpFieldName": "Description",
          "csvColumnName": "UserStory.Description"
        }
      ]
    }
  ]
}

Definitions of entity mapping properties:

• Name: It should be set as the unique name of the Mapping. It can have any value. It is used to identify the mapping definition.
• tpEntityTypeName: It is the name of the Targetprocess entity.
• fieldsMapping: It is a list of mappings between CSV columns and Targetprocess fields. Every field mapping consists of at least two main properties:
  • csvColumnName – The name of the column defined in the header row of the source CSV file.
  • tpFieldName – The name of the Targetprocess field. The Import process will read the value from the corresponding CSV column and persist the data in the specified Targetprocess field

Lookups

Every Import of CSV records results in the update or creation of Targetprocess entities. The Import mechanism will create duplicated entities if the user reruns the import using the same source data set. The mapping definition can specify the Lookup key, which prevents the creation of duplicated records by checking if such a record already exists and can/must be updated instead.

The Import process will search for an existing record if the field is marked as a Lookup. A new entity is created if the record is not found; otherwise, an update is applied.

Complex Lookups can be specified by setting „isLookup” to true for multiple fields.

{
  "entitiesMappings": [
    {
      "name": "UserStoryMappingName",
      "tpEntityTypeName": "UserStory",
      "fieldsMappings": [
        {
          "isLookup": true,
          "tpFieldName": "Name",
          "csvColumnName": "UserStory.Name"
        },
        {
          "tpFieldName": "Description",
          "csvColumnName": "UserStory.Description"
        },
      ]
    }
  ]
}

Lookup key behavior based on the setup:

• At least one pair: TP field-CSV column must have Lookup key set as "true".
• If the Lookup key is set as "true" and there is more than one record in the CSV file with the same key (e.g. feature.name), then only the data from the first row will be imported. The remaining rows will be ignored.
• IsLookup can be also set for relations. More information with examples may be found in "Relations" section.
• By default every Lookup is case sensitive. To change field (e.g. user.email) to be case insensitive you need to add: "isCaseSensitiveLookup": false, to the field mapping.
• Exception: the behavior of 'isLookup' is different when it is set on the entity ID. The entities that already exist in the Targetprocess will be updated, but the new entities will not be created. This is because the ID field is automatically set by the system during entity creation and cannot be manually set up or changed.

Multiple entities in one CSV row

A single CSV row can contain data for multiple entities. The following mapping defines the mechanism of importing a single CSV row into multiple Targetprocess entities:

{
  "entitiesMappings": [
    {
      "name": "UserStoryMapping",
      "tpEntityTypeName": "UserStory",
      "fieldsMappings": [
        {
          "isLookup": true,
          "tpFieldName": "Name",
          "csvColumnName": "UserStory.Name"
        }
      ]
    },
    {
      "name": "TaskMapping",
      "tpEntityTypeName": "Task",
      "fieldsMappings": [
        {
          "isLookup": true,
          "tpFieldName": "Name",
          "csvColumnName": "Task.Email"
        }
      ]
    }
  ]
}

Relations

Entities in Targetprocess can refer to other entities and the CSV file structure is flat. The concept of relations is introduced to define such transformation.

{
  "relations": [...],
  "entitiesMappings": [...]
}

Every relation describes the way entity mappings are associated with each other. There is always „join” entity mapping and edges. At least one edge is required:

{
  "relations": [
    {
      "root": {
        "mappingName": "UserStoryMapping"
      },
      "edges": [
        {
          "field": "Epic",
          "mappingName": "EpicMapping"
        },
        {
          "field": "Project",
          "mappingName": "ProjectMapping"
        }
      ]
    }
  ],
  "entitiesMappings": [
    {
      "name": "UserStoryMapping",
      "tpEntityTypeName": "UserStory",
      "fieldsMappings": [...]
    },
    {
      "name": "EpicMapping",
      "tpEntityTypeName": "Epic",
      "fieldsMappings": [...]
    },
    {
      "name": "ProjectMapping",
      "tpEntityTypeName": "Project",
      "fieldsMappings": [...]
    }

  ]
}

Constant reference

Constant reference is used to assign the same entity to every CSV file record. It requires knowledge of the identifier of the entity which we want to assign. Let's assume that we want to import some UserStories which will be assigned to Project called "Awesome Project". First, we check project id - 1008.Then we can create a CSV file with our UserStories

In Mapping, we need to specify the field which will be populated with constant reference - Project.

{
  "entitiesMappings": [
    {
      "name": "UserStoryMapping",
      "tpEntityTypeName": "UserStory",
      "fieldsMappings": [
        {
          "isLookup": true,
          "tpFieldName": "Name",
          "csvColumnName": "UserStory.Name"
        },
        {
          "kind": "ConstantReference",
          "constValue": {
            "id": 1008
          },
          "tpFieldName": "Project"
        }
      ]
    }
  ] 
}

Reference to other entities (1-M)

One-to-many relations mean that one entity (root) can be associated with one or more entities (edges). For example, in Targetprocess, a project can be associated with one or more user stories, so there is a one-to-many relationship here. Let's assume that we want to import the following CSV file:

Every UserStory should be connected with the project in the same row.To do so, we need to add entity mappings for UserStory (UserStoryMapping) and Project (ProjectMapping) with appropriate field mapping. As a next step, we will add root and edge entities.

We want to add Project to the UserStory. Therefore, we put its mappingName into the edge. We also need to specify which field in UserStory will be used to assign Project and add it to edge entity. In this case, it's Project.

As the last step, we will add UserStory as root, because other entities will be assigned to it. Full mapping will be as follows:

{
  "relations": [
    {
      "root": {
        "mappingName": "UserStoryMapping"
      },
      "edges": [
        {
          "mappingName": "ProjectMapping",  
          "field": "Project"
        }
      ]
    }
  ],
  "entitiesMappings": [
    {
      "name": "UserStoryMapping",
      "tpEntityTypeName": "UserStory",
      "fieldsMappings": [
        {
          "isLookup": true,
          "tpFieldName": "Name",
          "csvColumnName": "UserStory_Name"
        }
      ]
    },
    {
      "name": "ProjectMapping",
      "tpEntityTypeName": "Project",
      "fieldsMappings": [
        {
          "isLookup": true,
          "tpFieldName": "Name",
          "csvColumnName": "Project_Name"
        }
      ]
    }
  ]
}

Define relations between entities (M-M)

Many-to-many relations mean that one or more entities (edge) can be associated with one or more entities (edges). The association of those entities is done with the use of a root entity. Such entity contains information on which edge of the relation connects to which one. As an example, let's take a simplified version of assigning User to UserStory. In Targetprocess, we are able to assign multiple users to multiple user stories, so those entities will be our edges. To connect those edges we need the root entity which is in this case Assignment.  

{
  "relations": [
    {
      "root": {
        "mappingName": "AssignmentMapping"
      },
      "edges": [
        {
          "field": "Assignable",
          "mappingName": "UserStoryMapping"
        },
        {
          "field": "GeneralUser",
          "mappingName": "UserMapping"
        }
      ]
    }
  ]
}

Assignments (M-M and M-M-M)

Assignments can be imported in two different ways. With many-to-many and many-to-many-to-many mapping. Let's start with a simpler one.

Many-to-many

Suppose we want to assign users to a user story in one project. All those users will be assigned with the default roles. To do so, we create the following CSV file:

Next, we need to add mapping for User, UserStory and Assignment. UserStory has required field Project. As we will operate only on one Project, we can use ConstantReference for the Project field. Finally, we can add relations. An entity that contains information about which User is connected to which UserStory is Assignment. Because of that, we put its mapping into the root. Our edges are UserStory and GeneralUser. If we examine Assignment definition, we will see that it contains two fields Assignable for UserStory and GeneralUser for User. We need to use those fields in relation definition.

Full mapping will be as follows:

{
  "relations": [
    {
      "root": {
        "mappingName": "AssignmentMapping"
      },
      "edges": [
        {
          "field": "Assignable",
          "mappingName": "UserStoryMapping"
        },
        {
          "field": "GeneralUser",
          "mappingName": "UserMapping"
        }
      ]
    }
  ],
  "entitiesMappings": [
    {
      "name": "UserStoryMapping",
      "tpEntityTypeName": "UserStory",
      "fieldsMappings": [
        {
          "isLookup": true,
          "tpFieldName": "Name",
          "csvColumnName": "UserStory.Name"
        },
        {
          "kind": "ConstantReference",
          "constValue": {
            "id": 1008
          },
          "tpFieldName": "Project"
        }
      ]
    },
    {
      "name": "UserMapping",
      "tpEntityTypeName": "User",
      "fieldsMappings": [
        {
          "isLookup": true,
          "tpFieldName": "Email",
          "csvColumnName": "User.Email"
        }
      ]
    },
    {
      "name": "AssignmentMapping",
      "tpEntityTypeName": "Assignment",
      "fieldsMappings": []
    }
  ] 
}

Many-to-many-to-many

The previous example has quite a few limitations. We cannot dynamically set the role of the assigned user. To get around this constraint, we need to add another edge to our relationship. The third edge will be Role.Let's modify the previous CSV file and add a user role.

Next, we need to add mapping for Role and add an edge for this entity in relations. Assignment stores relation to Role in field Role. Full mapping will be as follows:

{
  "relations": [
    {
      "root": {
        "mappingName": "AssignmentMapping"
      },
      "edges": [
        {
          "field": "Assignable",
          "mappingName": "UserStoryMapping"
        },
        {
          "field": "GeneralUser",
          "mappingName": "UserMapping"
        },
        {
          "field": "Role",
          "mappingName": "RoleMapping"
        }
      ]
    }
  ],
  "entitiesMappings": [
    {
      "name": "UserStoryMapping",
      "tpEntityTypeName": "UserStory",
      "fieldsMappings": [
        {
          "isLookup": true,
          "tpFieldName": "Name",
          "csvColumnName": "UserStory.Name"
        },
        {
          "kind": "ConstantReference",
          "constValue": {
            "id": 1008
          },
          "tpFieldName": "Project"
        }
      ]
    },
    {
      "name": "UserMapping",
      "tpEntityTypeName": "User",
      "fieldsMappings": [
        {
          "isLookup": true,
          "tpFieldName": "Email",
          "csvColumnName": "User.Email"
        }
      ]
    },
    {
      "name": "RoleMapping",
      "tpEntityTypeName": "Role",
      "fieldsMappings": [
        {
          "isLookup": true,
          "tpFieldName": "Name",
          "csvColumnName": "Role.Name"
        }
      ]
    },
    {
      "name": "AssignmentMappingName",
      "tpEntityTypeName": "Assignment",
      "fieldsMappings": []
    }
  ]
}

Lookups for relations

Lookups can also be defined for relations. The entity that should be created or updated can be chosen based on the names of related entities.

Suppose we want to update the data in the field Internal Cost (IT) in the entities PeriodBudget, but only if they are related to the Portfolio Epics listed in the first column (PE1,PE2,PE3) and Periods listed in the second column (January). To do so, we create the following CSV file:

And the full mapping will be as follows:

{
  "relations": [
    {
      "root": {
        "mappingName": "PeriodBudgetMappingName"
      },
      "edges": [
        {
          "field": "PortfolioEpic",
          "isLookup": true,
          "mappingName": "PortfolioEpicReferenceMapping"
        },
        {
          "field": "Period",
          "isLookup": true,
          "mappingName": "PeriodReferenceMapping"
        }
      ]
    }
  ],
  "entitiesMappings": [
    {
      "name": "PeriodBudgetMappingName",
      "fieldsMappings": [
        {
          "isLookup": true,
          "tpFieldName": "Name",
          "csvColumnName": "PeriodBudget "
        },
        {
          "tpFieldName": "Internal Cost (IT)",
          "csvColumnName": "InternalCost(IT)"
        },
        {
          "kind": "ConstantReference",
          "constValue": {
            "id": 1234
          },
          "tpFieldName": "Project"
        }
      ],
      "tpEntityTypeName": "PeriodBudget"
    },
    {
      "name": "PortfolioEpicReferenceMapping",
      "fieldsMappings": [
        {
          "isLookup": true,
          "tpFieldName": "Name",
          "csvColumnName": "PortfolioEpic.Name"
        }
      ],
      "tpEntityTypeName": "PortfolioEpic"
    },
    {
      "name": "PeriodReferenceMapping",
      "fieldsMappings": [
        {
          "isLookup": true,
          "tpFieldName": "Name",
          "csvColumnName": " Period.Name"
        }
      ],
      "tpEntityTypeName": "Period"
    }
  ]
}