Exercise 2.3 – Building the Card Extraction Map

Modified on Fri, 13 Oct 2023 at 06:23 PM

In order to extract the data from the Source System for the Business Object Card, you will connect the Business Object to metadata structures in the Source System. You do this by building an Extraction Map on the Business Object Card.


Remember, that Hopp will load the data from the Source System into a staging database. By building the Extraction Map on the Business Object, you are instructing Hopp exactly how to extract the data you need from the staging database in order to assign the values to the Interface Fields exposed by the Target map.


You will also be using the View Customer. In a later exercise, you will create a View. For now it is enough to know that a View is simply an extract from one or more staging tables that is placed in yet another staging table. You can use the View just like you use any metadata structure (with Usage Active).


Let's give it a go.


Note: We are starting to leave out simple commands like Save (ctrl-s) in the hope that you now remember to save your work before moving on or closing an Item or Tab.


Add the Source metadata structure Src.DebitCard to the Business Object Card


Open the Business Object Card on the Document canvas. On the Export tab, you can see the Interface fields published by the Target Map for the Card Business Object. 


They were all born with a value of None. You must now assign a value to each of these fields.



To get data from the Source System to work with, you create an Extraction Map for the Card Business Object on the Source tab.


A sneak peek at what you will end up with when all is done:



This is how to go about it:

  • Select the Source tab and in the Extraction Map click the Click to add... moniker
  • In the dialog
    • In the Type to map drop down, select Metadata
    • In the Item drop down, select  SourceStructure [Src]
    • Select the structure DebitCard and click the Ok buttonIf you don't see DebitCard in the list you probably did not mark it Active in the previous exercise


Define the Relationship for Source Object Src.DebitCard


You have now added the Source metadata structure Src.DebitCard as the root Source Object in the Extraction Map for the Card Business Object. 


So you have told Hopp that you want to create Card Business Objects based on the data in the DebitCard table in the staging database. Now you need to tell a little more about just how that will take place. 


To do that, select the root node Src.DebitCard in the Extraction Map and then the Selection tab

  • In Discriminator you point out the fields on the DebitCard structure that identifies a Card. In other words, for which you want a new Card Business Object.

    By selecting the 2 Source Fields BankId and CardNumber, you instruct Hopp to create a new Card Business Object for each different value of BankId and CardNumber in the DebitCard table in the staging database:
  • In the Predicate, you can filter the data. For this exercise enter:
    ExpireDate is null
    or
    ExpireDate >= dateadd(year, 1, @FirstBankDay)

    As you can see, the syntax of the Predicate is SQL - more precisely the SQL Server variant called Transact-SQL (or T-SQL). You only want rows from the DebitCard staging table without any ExpireDate or with an ExpireDate more than 1 year from @FirstBankDay

  • The @FirstBankDay in the Predicate is actually a parameter to be given when the migration actually runs. You must instruct the Source Map what to assign to this parameter. You do this by creating a Predicate Parameter with the same name without the leading @ (FirstBankDay) and then telling what value you want passed for this parameter in the Content dropdown. In this case select the Constant FirstBankDay:


  • Finally, you can specify a Selector. It is not needed in this case, but would be necessary if there could be several rows in the staging table for each value of the Discriminator. If this was the case, Hopp would only create a Card Business Object for the first of these rows. By specifying a Selector you tell Hopp which of the several rows to pick = the first of the rows when sorted by the order given:

Add Vw.Customer to the Source Map of Business Object Card


To assign values to some of the Export Fields on Card, you need more than what you can get from the Src.DebitCard. You also need something from the View Vw.Customer (you will be creating a View in a later exercise).


You get hold of View Vw.Customer by adding it as a Source Object to the Extraction Map of Card as a child to the Source Object Src.DebitCard.

  • Right/click the Source Object Src.DebitCard in the Source Map tree view and select Add child...
  • In the dialog
    • In the Type to map drop down, select View
    • Select the view Customer and click the Ok button



Define the relationship between Source Object Src.DebitCard and Source Object Vw.Customer


So far so good. As you can see, you have added Vw.Customer as a child of Src.DebitCard in the Extraction Map:

Now you need to define how to find the row in the staging table Vw.Customer for the row in Src.DebitCard

  • Select the Name & Description tab and provide the name CardHolder. A bit later you will add another Source Object pointing to Vw.Customer, the name allows you to distinguish between the two:
  • Select the Selection tab
    Note that it looks a bit different from before, when you worked on the root object Src.DebitCard. Now - instead of the Discriminator, you have a Lookuplist. In the Lookup list you match Source Fields in Vw.Customer (this) to Source Fields in Src.DebitCard (parent) making up the Relationship 
    • Vw.Customer.BankId to Src.DebitCard.BankId
    • Vw.Customer.CustomerNumber to Src.DebitCard.Customer
  • Predicate, Predicate Parameters and Selector are all used for the same purposes as above. You do not need to specify anything here, because the Lookup suffices to pick the correct row from Vw.Customer
  • You want to react, if no row on the staging table Vw.Customer is found then
    • Select the Flags tab
    • On the Not Foundflag, fire the Event 'E0011 Customer ({0}) is missing'
      • Parameter CustomerNumber
        • Select the Value Source and the Content as Source Field Customer on Source Object Src.DebitCard
        • Use the Source Field Customer on the parent Source Object Src.DebitCard for the parameter. Of course there is also the equivalent Source Field Customer on the child Vw.Customer. But since the Event is only fired when nothing was found in Vw.Customer, it would not make sense to use anything from Vw.Customer as a parameter to the Event


You have now added the Source Object as a child under the Source Object and will use it to find the data related to the CardHolder. 


Next, you will add another Source Object pointing to the same Vw.Customer, this time to find information related to the Company owning the Card - if any.


Note that there can only be one root Source Object in the Extraction map (Src.DebitCard).


Add Vw.Customer to the Source Map of Business Object Card for the CardHolderCompany


Select the root Source Object Src.DebitCard and add the Source Object Vw.Customer once more, only this time

  • Name it CardHolderCompany
  • Specify the Lookup as
    • Vw.Customer.BankId to Src.DebitCard.BankId
    • Vw.Customer.CustomerNumber to Src.DebitCard.Company
  • Do not react to the Not found Flag. It is not required for the Card to have an owning company


Now the Extraction Map for the Card Business Object should look like this


An important detail: You have added the View Customer twice to the Extraction Map. Due to the inner workings of Hopp, it is in that case important to to name the 2 instances, otherwise, Studio will flag an error. This is why the instances are given the names CardHolder and CardHolderCompany.


What happened here?

You have instructed the Source Map to create one Card Business Object for each row in the staging table with a unique combination of BankId and CardNumber. And for each Card Business Object you have furthermore specified how to find a row in the staging table Vw.Customer for the user of the Card and another row in the same staging table for the company owning the Card.


You now have access to all the Source Fields on these 3 Source Objects and can start to use them in the following exercises when you will be assigning the values to the Export Fields expected by the Target interface. 


In fact, you have already used the value type Source when assigning the Event parameter above. So combined with the general value type, you are now starting to build up your arsenal of value types to use in the Source Map:

  • Literal
  • Constant
  • Source, to use a value from on of the Source Objects in the Source Map of the current Business Object - or any ancestor Business Object


Furthermore, you have used a Metadata structure and a View in your Source Map. In fact, it is also possible to use Valuesets in the Source Map. You will get a chance to do this in the next exercise.


You can read more about the Extraction Map here.

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article