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 Views for you (View1 using View2 using View1). At runtime, migFx 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 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 multible 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.

Test View Query

After all, this training is not about Sql, so we have prepared the view query for you. In order to test it in Sql Server Management Studio, you can open the query provided (button Openin the Toolbar) and open the file Documents/MigFx/SQL/View_CardStatus.sql.


After the query opens, please ensure the correct active database migFx_2_Staging (MigFx_2_Generated in earlier setups) is selected in the drop down just above the Object Explorer tree view. 


As you can see, the file contains more Sql statements than actually constitutes the query itself. These extra lines are inserted by Studio when copying the query to the clipboard to afterwards paste it into Sql Server Management Studio to test. These extra lines are suffixed by the comment "-- [Generated by migFx]" causing them to be removed if you paste the query back into Studio. 


Now click thebutton in the Toolbar to test the query.

Create View CardStatus

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
  • Copy the query from Sql Server Management Studio (ctrl-a, ctrl-c) and paste it into the Sql text box (ctrl-v)
    • 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
    OBS: Note that the Exposed Field CardStatusEndDate is marked as nullable (check mark 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.2 – Load Source Data and Views)
  • 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. And finally, you have told Studio what you want 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.