When Each Column Needs a Record

A spreadsheet like a financial statement (income statement, etc) contains columns for each fiscal year.  When loading these into a data warehouse, you may want to turn each column into an individual record.  For example, store "Total Revenues" for 2009 as one record, and Total Revenues for 2010 as another.  To do this, use a Pervasive Map Designer Transformation with multiple MapPut records for each source record column.

 Take the following spreadsheet

 Load it into an Access database like so

To accomplish this, you'll need to write out several records for each input record.  This requires changing the default behavior which is a ClearMapPut Record Action after each AfterEveryRecord.  Change this to multiple actions, a starting Clear followed by several MapPut Record Actions.

 The key to this transformation is to use an index variable (called "counterColumn") that will move as each record is mapped.  Then, using a bit of logic in the target mapping, do something like


If columnCounter = 1 Then
   Fields("FY2006")
 Else
   If columnCounter = 2 Then
     Fields("FY2007")
   Else
     If columnCounter = 3 Then
       Fields("FY2008")
     Else
       Fields("FY2009")
     End If
   End If
End If


this is the mapping for target field "RevenueItemValue". RevenueItemName is mapped the same for each MapPut Record. FiscalYear is taken from an array that is also advanced with the index (counterColumn).

To control the iteration, reset counterColumn in the BeforeEveryRecord event (columnCounter = 1) and advance the variable in AfterMap (columnCounter = columnCounter + 1).

When you're getting your data from a report-like spreadsheet, it may take some additional setup to render this into something nice for an RDBMS. Use multiple MapPut Record Actions to write out records for each column representing a new set of values.