The Extraction Map

Modified on Mon, 13 May at 1:46 PM

This is a detailed topic in our support portal in the Using Hopp series and assumes that you have some prior knowledge or experience using Hopp.

Many find their first encounter with the Extraction Map on a Business Object in the Studio Source Map a wee bit daunting.

This article is here to help you out and get these pesky notions of lookups, discriminators, selectors and what-not put in their proper place once and for all.

Basically, when you work on a Business Object in the Source Map, what you are doing is instructing Hopp how to extract data from the Tables in the Staging Database and how to use the extracted data to assign values for the Interface Fields to send on to the Target Map.

This is what the Extraction Map does.

In the following, the Customer Business Object and its child Business Object PhysicalAddress are used as a Sample:

So Customer is the root Business Object and it may have multiple PhysicalAddress children.

The Extraction Map shows up on a Business Object as a tree view showing the Source Objects in the Staging Database data are extracted from. Here's the Extraction Map of the Customer root Business Object in the Workshop sample project used in the Training Exercises:

The Extraction Map shows that the Customer Business Object is extracting data from 3 Source Objects in the Staging Database: View Customer, Source Table ClientAdditional and Valueset TranslateSegments.

When a Source Object is selected in the tree view, the Selection tab to the right lets you specify how data should be extracted from the selected Source Object.

The Selection tab looks a bit different depending on these 3 possible contexts:

Root Source Object in the Extraction Map of a root Business Object
Root Source Object in the Extraction Map of a child Business Object
Child Source Object in any Extraction Map

Root Source Object in the Extraction Map of a root Business Object

This is what sets off the creation of every Business Object. It is here the Source Map is instructed how to create new instances of the root Business Object.

Below the Selection for the root Source Object Vw.Customer on the Customer root Business Object that tells the Source Map how to create Customer Business Object based on the rows in the Vw.Customer table in the Staging Database:

Not so much here, but let's go through the 4 elements one by one anyway:

Predicate	An Sql WHERE clause that can be used to filter which rows in the Vw.Customer staging table should be used.
Predicate Parameters	It is possible to pass parameter values to the Predicate. For instance, a Run parameter holding a migration date in the Portal Operations.
Discriminator	Determines which distinct values in the Vw.Customer should trigger the creation of a new Customer Business Object. Here a new Customer Business Object will be created for each distinct value of BankId and CustomerNumber in the Vw.Customer Staging table.
Selector	Not in this case - but in some cases, there may be multiple rows for each distinct value of the Discriminator. A typical example is when the Staging table holds different versions with a Start and End date. If this is the case, the Selector determines which of these rows should be the basis for the Business Object. The rows are sorted as specified by the Selector, and the first row is then picked. Studio will validate that the Selector does not contain any fields that are also part of the Discriminator.

Root Source Object in the Extraction Map of a child Business Object

This Selection contains the same 4 elements as the Extraction Map above. But it has an extra Lookup element that is used to specify how the child Business Objects are looked up.

In the Workshop Source Map, the Business Object PhysicalAddress is a child of Customer, and child PhysicalAddress children are created based on the staging table Vw.Address.

This means that the root Source Object Vw.Address of PhysicalAddress is linked to the Source Object Vw.Customer of Customer as illustrated above.

Here's the Selection for the root Source Object Vw.Address:

As you can see, the bottom part of the Selection tab contains the same 4 elements as before, but above you have the Lookup table, that defines how to use information from the parent Source Object Vw.Customer to look up rows in the Source Object Vw.Address to create PhysicalAddress child Business Objects.

This lookup may well produce multiple rows from Vw.Address, and - as before - the Discriminator says for which distinct values in these rows a new PhysicalAddress child should be created.

And if there are multiple rows with the same Discriminator values, the Selector decides which one to pick.

Another way to look at this is to picture what the steps of the resulting, generated Source Engine looks like when processing a Customer Business Object with a given BankId and CustomerNumber:

Look up the distinct AddressNumber values in the Vw.Address Staging table with this BankId and CustomerNumber
For each distinct AddressNumber, look up the rows in the Vw.Address staging table with the BankId, CustomerNumber and AddressNumber
Sort this (potential) set of row as defined in the Selector
Pick the first in the set row after this sort

Studio will validate that the Discriminator does not contain any fields that are also part of the Lookup.

Studio will validate that no fields of the Selector are also part of either the Discriminator or the Lookup.

Finally, an added bonus in this context is the ability to set a sort order on the Discriminator fields - thus determining the order in which the child Business Objects are created.

Child Source Object in any Extraction Map

The last context for the Selection tab is for any Source Object that is added as a child of any other Source Object in an Extraction Map.

Below, in the Customer Business Object, the Source Object Src.ClientAdditional is added as a child of the root Source Object Vw.Customer.

This last context is fundamentally different from the two described above. Whereas the two preceding contexts both dealt with the selection of a collection of rows from a Staging table, this context will always select exactly 0 or 1 row from the Staging table concerned.

Look at it this way: When dealing with root Source Objects it's all about defining a collection. Either the collection of root Business Objects or the collection of a given child Business Object within its parent.

This last context is concerned with extracting the data for each individual instance of these collections and will only return a maximum of 1 row for each of the Staging tables in the Extraction Map.

Here's the Selection tab for the child Source Object Src.ClientAdditional:

This is indeed the third variant: It is like the last one (root Source Object on a child Business Object), just without the Discriminator. This stands to reason, as this context will always only return a maximum of 1 row, so there really is nothing to discriminate.

Here - finally - you see the Selector in use. Apparently, there may be multiple rows in the Src.ClientAdditional staging table for each BankId and ClientId.

Using the Selector, it is assumed that the newest row (as determined by the When date) is used. The Selector says: Take the possibly multiple rows, sort them in descending order, and then give me the first one - which will be the newest.