Regardless of implementation, data in the Target System will inevitably be tied together with foreign key relationships. In other words, data instances have unique ids (the primary key) and other data instances relate by storing the key of the instance to relate to (the foreign key).
As is indeed best practice, the key values are normally internal to the Target System and do not carry any information. Furthermore, the responsibility for the generation and assignment of the primary keys resides inside the Target System, and it is the Target System that guarantees the necessary uniqueness of the primary keys.
This article will give you an impression of how the Identity Value types of migFx provide valuable assistance in handling the integrity of primary and foreign keys in data migration.
Loading Target System via API
Loading via an API typically means that the generation and assignment of the primary keys is done when the API is called for a data instance, and the API will return the value of the primary key assigned to this instance to be used as foreign key for all related data instances.
For instance, when calling the Target System API to load a Project, the API assigns and returns a primary key for the Project, and this key has to be included as a foreign key when calling the API for all the Tasks in the Project.
This is a very common use case, especially when migrating into commercially available 'off-the-shelf' products like Oracle EBS, SAP, Salesforce, Microsoft Dynamics etc.
One normal draw-back of loading via an API is that it is quite often not very performant and while the actual transformation part of the data migration may be fast, loading via the API may take a long time.
Direct load to Target System
For larger migrations into bespoke Target Systems under the control of the Target Business, it is not unusual to load the migrated data directly into the underlying database tables. Typically, this allows the load to utilize very performant bulk insert facilities of the underlying database management system.
However, for this at all to be possible, the mechanism used by the Target System to generate and assign keys must be externalized in a way that makes it possible for the correct primary keys to be generated and assigned by the data migration itself with zero risk of colliding with keys generated by the live Target System.
We have come across a few different ways to accomplish this (there are maybe more out there):
Identity Value Types
Whether you are loading via an API or loading directly, you must ensure complete referential integrity between Primary and Foreign Keys in the Target Data produced by migFx.
In migFx, the Value Types Identity and Guid (available from version 1.3) in Studio are specifically designed to help you accomplish this. The two are quite similar, the only difference is that the Value Type Identity returns a 64 bit integer while the Value Type Guid returns a 128 bit (36 character) GUID.
The Identity Value Types are only available in Studio when assigning values to Target Fields. At run time, the Target Engine has complete track of the key value of the current Business Object (as expressed by the Interface Fields marked as Key on the Business Object and its ancestors) plus the mapping id of the current Target Object and current Target Field. The Target Engine passes this combined migration key on to the Director Runtime and asks for a key value (either integer or GUID). If the Director has already stored a value for for this migration key, this value will be returned. If not, the Director will generate, store and return a new value.
This mechanism ensures that
In other words, migFx ensures that a given Identity Value
Once a Target Field has been assigned an Identity value, it is simply business as usual to use the assigned value of this field to assign values to other Target Fields that acting as Foreign Keys on their respective Target Objects:
As an example, consider a simple Business Object Account with a child Business Object Statement. The Business Object Statement has a relationship to a separate Business Object Customer, to identify the Customer that receives the Statement. For the sake of this example, in the Target System there are two Foreign Key relationships on the Statement:
In the figure above, the Business Object Statement is a child of the Account root Business Object. In addition, it has a relationship to the separate root Business Object Customer. The Target Fields AccountId on Account and the CustomerId on Customer are both mapped using an Identity Value. As described above, these 2 Target Fields will each be assigned a globally unique, repeatable value when the migration executes.
The foreign key relationships on Statement are established like so:
This is how the corresponding mapping looks in Studio:
Of course, migFx automatically resolves the resulting dependency between Customer and Account and ensures that the Customers are migrated (and the CustomerId assigned its value) before the dependent Accounts (using the value assigned to CustomerId).
But imagine a large migration, where the migration of all Accounts may take considerable time. Let's say that an iteration of all Customers is desired to solve some minor issue internal to the Customers (for instance that Surnames are not starting with an upper case letter). Indeed a minor correction, and it would be a wasteful to be forced to iterate all dependent Accounts.
Luckily, since the values assigned to the CustomerIds remain the same across all the iterations, the ReceiverId foreign keys on the Statements within the Accounts are still valid even though the Customers are iterated. So there is no need to iterate the Accounts.
In real life migrations, with multi-level dependencies (CreditCards depend on Accounts depend on Customers), this cross-iteration persistence of the assigned Identity values potentially saves time and avoids the risk of unnecessary iterations.
Load and Key translation
As described above, the Identity Value Types provide an easy to use and robust way of maintaining complete referential integrity in the migrated data.
If you are so lucky, that your migration loads by inserting directly in the Target System, and your Target System uses GUID type primary key values that will accept Guid Identity Values from migFx, you are home free. The Guid Identity values assigned by migFx can simply go directly into the Target System with no risk of collision on primary key values.
On the other hand, if the Target System is in control of the final assignment of primary key values via an API, the Identity Values produced by migFx are not directly usable and - as part of your delivery extension - it will be necessary to maintain a lookup between the Identity Values produced by migFx and the primary key values generated by the Target System.
However, due to the uniqueness of the Identity Values globally within migFx, such a lookup will always be quite simple, with the migFx Identity value on one side and the Target Primary Key Value on the other.
When a data instance is loaded via the API and a primary key value is returned, this primary key value must be stored in the lookup along with the corresponding migFx Identity value.
Whenever the same migFx Identity value is encountered as a foreign key in the migrated data, it is simple to look up the corresponding primary key value and send it on to the Target API.