Exercise 4.3 - Load Source Data and Views

Modified on Mon, 11 Dec, 2023 at 11:51 AM

In this and the following exercise, you will be loading the data necessary to migrate the new Card Business Object. First, in this exercise, you will load the data received from the Source System into the staging table Src.DebitCard and then load the View CardStatus into the corresponding staging table for this view. 

Next, in the following exercise, you will load data into the staging tables for the new Valuesets TranslateCardTypes and CardTypes.

In this training setup, data is received from the Source System as comma-separated files. These files are placed in a folder on the migration server dedicated to the actual track. In your training setup, this folder is Documents\MigFx\Runtime\Track\01 and the data from the Source System resides in the sub-folder SourceData\Src. In this training setup, we have placed the data files in the correct folder for you. In a real-life migration, you would have to place the files in the correct folder yourself. 

Note the sub-folder Src corresponding to the alias of the metadata in Studio. It is possible to create and import multiple metadata collections into Studio, and they will end up in a sub-folder, thus avoiding problems in case of name collisions.

Data from the Source System for Src.DebitCard has been delivered in two files, one for each bank being migrated.

Relating back to the Metadata in Studio

In the Studio Source Map, you imported the metadata describing the source tables, and the entire mapping done in Studio is based on this metadata. Now you are further along and about to load the actual data. It is of course crucial that the actual data conforms to the metadata. If this is not the case, the Portal Operations and Runtime will be unable to load the data. To recap the process:

  1. The metadata is imported into Studio and serves as the basis for the Source Map
  2. When running the Setup of the resulting Source Engine in Portal Operations, the Source Engine will ensure that the Staging database contains a corresponding table for each structure in the metadata.
  3. Loading data in the Portal Operations is done in 2 steps
    • The data are reformatted from one of the known formats to a common load format
    • The reformatted data are loaded into the corresponding table in the Staging database

If the actual data for some reason does not conform to the metadata, there is a good chance that either the reformat step or the load step above will fail. In this training setup, we have ensured that the data conforms. But in a real-life project, this is not an unusual problem.

In case of problems

From now on, we assume that the preceding exercises in Studio were completed correctly. If at any point you realize that you need to go back to a previous exercise to correct something in Studio, please (please!) remember the complete path any modification in Studio needs to go through in order to be operational in the Portal Operations.

  1. Studio: Validate and Publish
  2. Visual Studio: Generate and Deploy
  3. Portal Operations: Restart Track and run Setup

If you find yourself scratching your head over something that is not how you expect it to be, a good first thing to make absolutely sure is that the above has been done in the correct order. 

Load Source Data

In order to load the data for Src.DebitCard, select Source/Data in the Portal Operations menu. The Portal Operations will show a list of all the tables in the Staging Database. The Src.DebitCard is there as well because it was part of the new Source Engine that you have deployed in the last exercise.

Most tables are marked green, as they have already been loaded as part of earlier iterations of Business Objects Customer and Account. But Src.DebitCard is red - it has not been loaded yet. 

Apart from the obvious like Alias and Name, the meaning of the columns in the list is:

The number of files to load. For instance, for DebitCard there are 2 files
The reformatter to run on the received file to reformat it to the common format used to load data into the Staging database
Received file date
The date of the received file. If this file is newer than the Formatted file, it will be marked orange
Formatted file date
The date of the formatted file. If this file is newer than the loaded file, it will be marked orange
Date of loaded file
The date of the formatted file that was loaded into the Staging database. If this file is newer than the date the file was loaded, it will be marked orange
Loaded date
The date of the formatted file was loaded into the Staging database.
A saved baseline of rows loaded into the Staging table
The actual number of rows loaded into the Staging table

Now, load the data in the 2 files into the staging table Src.DebitCard

  • Click the Context Menu button on the Src.DebitCard row
  • Select Truncate & Load...
  • Ensure Src.DebitCard is selected in the dialog
  • Click the Confirm button in the confirmation dialog to submit a job to load the 2 files into the table

You can go to the Job List to see the progress of the job. 

When the job finishes you can go back to the Tables list and click the Refresh button to update the Src.DebitCard row.

Load View CardStatus

Select Source/Views in the Portal Operations menu to see the list of Views:

CardStatus is in the list as expected but not loaded yet. It has a green dot on the left to show it is ready to load. In other words that the items it depends on (in this case Src.DebitCard as you can see by clicking Dependencies... in the context menu) are all loaded.

The meaning of the columns is:

Hopp analyzes the view query and builds indexes to help it run. The number indicates how many iterations Hopp did before it decided that the query could not be optimized any further. If interested, you can click the blue link to see the query plan (takes a little while as it launches Sql Server Management Studio to visualize the query plan).
Loaded Date
The exact time the data for the view was loaded into the staging table.
The time it took to load the view.
A baseline for the row count of the view. You can update the baseline by clicking the Baseline button.
Row count
The number of rows loaded into the staging table
  • Green if equal to Baseline
  • Red if blank (not loaded)
  • Yellow if different from baseline

Now load the staging table of the View CardStatus by selecting it in the list:

  • Select Load... in the context menu
  • In the dialog, ensure there is a check in Include Descendants. As you remember, in Studio it is possible to use Views from other Views. By checking Include Descendant, you instruct the Portal Operations and runtime to resolve these dependencies in a recursive manner. So if any views are using the View CardStatus, they get loaded too. Now, no other views are actually using the View CardStatus, but it is a good idea to leave this checkmark be
  • Click the Confirm button to submit a job to load the view
  • Check the job in the job list
    • A note: If the job fails, be sure to check the Job Instance Log to see the error
      If it is complaining about inserting nulls into the CardStatusEndDate column, you probably didn't make that column nullable in the view when it was created in Studio. See Exercise 2.6 – Create View CardStatus
  • When finished, refresh the View list to update the CardStatus row

What happened here?

Getting ever closer to actually migrating the Cards. In this exercise, you have loaded the staging table for the data received from the Source System for Src.DebitCard as well as the staging table for the View CardStatus.

Next up is to load the staging tables for the Dynamic and Translation Valuesets.

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 at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article