Page tree
Skip to end of metadata
Go to start of metadata
Overview

(Note: This section relates uses the MyETLExample sample project as a reference.)

Mapping Business Objects (BOs)

The map associated with a BO provides a template for how to format the data associated with this BO.  Multiple maps can refer to the same business object: in this way, it is possible to use a single BO to extract data from one data sources (for example worksheets in an Excel spreadsheet) and load (write) it to one or more destinations (for example, one or more database tables).  

Designing with diagrams

A simple diagram can help us visualize our solution design:

In this example, data is read from a worksheet, Keywords, in an Excel spreadsheet, keywords.xlsx, into a business object (extract), ReadAndWrite, using the map InputDataFromExcel. (As you see, naming these components in the context of their roles helps understand the processing taking place.)  It is then written out to a relational database table, READ_AND_WRITE, using the OutputToTable map.

Cantor code sample:

 

' extractDataTranformAndLoad.cantor
' Read data records from Excel spreadsheet worksheet, write records to internal DB
MappingHelper.setMapping(ReadAndWrite,"InputDataFromExcel")  ' load from Excel spreadsheet sheet
Dim dataIn As BusinessObjectList = Query From ReadAndWrite
MappingHelper.setMapping(ReadAndWrite, "OutputToTable")  ' write to database table
Dim dataOut As BusinessObjectList = Query From ReadAndWrite Writable
Dim count = 0
Try
      dataOut.clear()
      dataOut.store()
Catch ex As Exception
      notice (ex.getMessage() + " - cannot clear dataOut - likely cause: mappings not established.")
End Try
For Each boIn In dataIn
      Dim recordExists As Boolean = false
      Try
            If dataOut.isEmpty() = false then
                   For Each existingEntry In dataOut
                         If existingEntry.keyword = boIn.keyword Then
                               recordExists = true
                               notice ("Keyword: " + boIn.keyword + " exists!")
                               Exit for
                         End If
                   Next
            End If
      Catch ex As Exception
            notice (ex.getMessage() + ": dataIn.isEmpty() failed.  Likely cause: BOs not mapped.")
            Return
      End Try
    If recordExists = false Then
        count = count + 1
        Dim boOut = ReadAndWrite.createInstance()
        boOut.keyword = boIn.keyword
        boOut.index = count
      dataOut.addNew(boOut)
    End if
Next
dataOut = SortListOfBOs.sortBOs (dataOut)
Dim counter = 1
For Each bo In dataOut
      bo.index = counter
      notice ("Index " + bo.index + ": " + bo.keyword)
      counter++
Next
dataOut.store()
notice ("--- End of account extract, transform, and load --")
  • No labels