Exercise 2.6 – Create View CardStatus

As outlined in the introduction to this section of exercises on the Source Map in Studio, the Source System metadata imported into the Source Map, eventually will result in corresponding tables being created in the Staging database created and maintained by the generated Source Engine. The Source Engine will then load the data received from the Source System into these tables.

A View is simply an Sql query that you can define on top of these staging tables. The Source Engine will then execute this query and store the result in yet another table in the Staging database. In addition to the staging tables corresponding to the Source metadata, the view query can also use the staging tables for other views and indeed also for Valuesets. 

The figure below illustrates how Views:

1. query Source-, Valueset- and View tables in the Staging Database and 
2. store the result in View staging tables.

The Studio validation will discover any circular view dependencies for you (View1 using View2 using View1). At runtime, Hopp will make sure that the Views are loaded in the correct sequence.

Views are very powerful in the Source Map. They can be used to clean, transform and aggregate data received from the Source System. A View consists of 3 main parts:

• A list of columns (name and data type) exposed by the View, usable elsewhere in the Source Map
• An SQL query that results in these columns - in the correct order and matching data types
• Possibly some Named Values to pass parameters to the View

As you can see, the SQL query is really the heart and soul of the View. When defining the View in Studio, it is a good idea to develop and test this query against the actual data in the Staging database. In the exercise below you will use the Microsoft Sql Server Management Studio application to connect to the Staging database and do just that.

Looking in the Staging database

To have a look at the Staging database and later to develop and test the View, you need Sql Server Management Studio. It should be visible in the Windows Taskbar:If not, you can easily find it in the Windows Start menu:

When you start Sql Server Management Studio you must initially connect to the correct Sql Server instance. Provide these values in the dialog (they are most probably already correct)

• Server type: Database engine
• Server name: (localdb)\mssqllocaldb
• Authentication: Windows Authentication

and click the Connect button.

In the Object Explorer tree view, expand the folder node Databases and locate and expand the database MigFx_02_Staging (this is in fact the database from the additional WorkshopDemo project, that already has all the Card related staging tables present). 

Expand the Tables folder node and the Synonyms folder node to see the generated staging tables for the Source metadata, Views and Valuesets from the Source Map:

Notice that 

• the staging tables corresponding to the Source System metadata are prefixed with the Alias (Src) provided in Studio
• the Views are prefixed with Vw
• the Valuesets are prefixed with Vs 

Actually, the Valuesets from both the Source Map and the Target Map are present as tables with the prefixes mig_vs_src and mig_vs_tgt respectively. But only the Valuesets from the Source Map are exposed with the prefix Vs (as so-called Synonyms).

Potential issue

Sometimes for obscure reasons deep in the Microsoft universe, Sql Server Management Studio may complain and show this (somewhat misleading) error message:

If you experience this error please close down Sql Server Management Studio and start it again. This will resolve the problem.

The task of View CardStatus

In the TargetSystem, the metadata structure CardStatus must contain the status for the lifetime of a Card, but in the correct chronological sequence. The valid status' are:

1. Ordered
2. Active (maybe)
3. Blocked (maybe)
4. Expired (maybe)

The Source System is just different. It stores similar information in a single row in the Src.DebitCard metadata structure. This row contains these relevant columns:

• ÓrderDate
• ActiveDate
• Blocked (X or blank)
• Expired (X or blank)

The task of the View CardStatus is to translate this single row for each Card in the Source System into multiple rows, one per status of the Card with a sequence number for the chronological order. You will then be using this View when you map the Target Interface Fields of the Business Object Card.CardStatus in the next exercise.

Now you are ready to create the actual View in Studio

• Create a new View
  • Name: CardStatus
  • Description: List CardStatus on all Debit cards
    
    
• Select the Definition tab
  • In Referenced Objects, click the Add button on the right and add the Source metadata structure Src.DebitCard
  • In Exposed Fields provide:
    • BankId, Num(5), uncheck Nulls
    • CardNumber, Char(16), uncheck Nulls
    • CardStatusSeqNo, Num (5), uncheck Nulls
    • CardStatus, Char (10), uncheck Nulls
    • CardStatusStartDate, Date, uncheck Nulls
    • CardStatusEndDate, Date, check Nulls
      
      
• After all, this training is not about writing SQL, so we have prepared the view query for you in the file Documents/MigFx/SQL/View_CardStatus.sql
  • You can just copy/paste the entire query text. When pasting, Studio will automatically remove all the unwanted lines (suffixed with the comment "-- [Generated by migFx]")
    
    
• Add a Named Value
  • Name: FirstBankDay
  • Value: Constant.FirstBankDay
    
    
• Your View should look like this
  
  
  
• Now it is a very good idea to test the view and verify that you got it right. Especially that the result set returned by the Sql Statement is compatible with the Exposed Fields of the view
  • Click the Copy button below the Sql Statement text box to copy the view to the clipboard
    
  • Open a new query in Sql Server Management Studio and paste the view in there
    • As you can see, the paste query contains more statements than actually constitutes the query itself. 
    • These lines actually creates a temporary table corresponding to the Exposed Fields of the view and inserts the result of the view query into it.
    • This validates that the view Sql Statement result is compatible with the Exposed Fields of the view
  • Be sure to select the correct database. For the sake of testing the view at this point in the training, select the database migFx_02_Staging
  • Now click thebutton in the Toolbar to test the query
    
    
• OBS: Note that the Exposed Field CardStatusEndDate is marked as nullable (checkmark in the Nulls column) in the screenshot above. This is important. The view Sql returns a null CardStatusEndDate for some rows, and if the corresponding Exposed Field is not nullable, it will cause problems later on, when the data for this view is loaded in the Portal Operations (Exercise 4.3 – Load Source Data and Views)
  • If you tested the query as described above, you should be fine :-)
    
    
• Save and commit

What happened here?

You have created your first-ever View in Studio. By defining the Exposed Fields you have let Studio know which columns the view contains - these columns can now be used as Source values elsewhere in Studio. You have also provided the Sql query for the view - well tested against actual data in the Staging database. Finally, you have told Studio what you want to be passed as the parameter @FirstBankDay when the view loads its staging table at runtime.

When the Validation runs in Studio, your query will be tested against a real SQL Server revealing any syntax errors. It is also validated that your query returns columns corresponding to the Exposed Fields - in the correct order and with compatible data types. 

You can click the Validate button to run the validation for just this one View.