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 a Source Map on the Business Object Card.


Remember, that migFx will load the data from the Source System into a staging database. By building the Source Map on the Business Object, you are instructing migFx 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).


Lets give it a go.


Note: We are starting to leave out simple commands like Save (ctrl-s) in the hope that you now remenber 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 fields published by the Target Map for the Card Business Object. You must assign a value to each of these Export Fields. To get data from the Source System to work with, you create a Source Map for the Card Business Object on the Source tab:

  • Select the Source tab and click the Add button
  • 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 Source Map for the Card Business Object. So you have told migFx 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 Source Map and then the Relationship tab

  • In Break Fields you point out the fields on the DebitCard structure, for which you want a new Card Business Object. By selecting the 2 Source Fields BankId and CardNumber, you instruct migFx 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 Where Clause, you can filter the the data. For this exercise enter

ExpireDate is null
or
ExpireDate >= dateadd(year, 1, @FirstBankDay)

  • As you can see, the syntax of the Where Clause 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 Where Clause 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 Named Value with the same name without the leading @ (FirstBankDay) and then tell what value you want passed for this parameter in the Content dropdown. In this case select the Constant FirstBankDay
  • Finally you can specify Order by. 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 Break Fields. If this was the case, migFx would only create a Card Business Object for the first of these rows. By specifying Order by you tell migFx which of the several rows should be the first one to pick



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 Source Map of Card as a child to the Source Object Src.DebitCard.

  • Select the Source Object Src.DebitCard in the Source Map tree view and click the Add button
  • 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 Source 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 tag 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 Relationship tab. Note that now - instead of the Break Fields list, you have a Relationship list. In the Relationship 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
  • Where Clause, Named Values and Order By are used for the same purposes as above. You do not need to specify anything here, because the Relationship 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 None Found flag, 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 Source 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 Reationship 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 Source Map for the Card Business Object should look like this



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 use 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.