14 Enriching data directly from database tables

By the end of this chapter you will be able to:

• Enrich a data set using a lookup pipe directly from a database collector
• Use the attribute function countElements()

In this exercise you will compare the Stream you created in the previous exercise, listing all channels for each customer, with a list of assigned channels stored in an inventory database. To do this, you will create a lookup directly from a Database Collector:

• Add a new Stream to your model from Latest Package All Channels, called Channel List Reconciliation
• Drag all the attributes from Latest Package All Channels into Channel List Reconciliation
• Create a Database Collector, using the Datasource Inventory DB, to read all records from the table SOURCE_ALL_CHANNELS_LIST
• Add a lookup pipe from the Database Collector SOURCE_ALL_CHANNELS_LIST to Channel List Reconciliation
  • Call the pipe inv
  • In the matching attributes form create a match:
    • Drag the attribute CustomerRef from the right hand box into the list of matching attributes
    • Drag the attribute CustomerRef from the right hand box into the list of matching attributes again – on top of the matching attribute CustomerRef you created above – this will populate the right hand part of the matching attribute
  • Create another match
    • Drag Channel from the right-hand box into the list of matching attributes
    • Drag Channel from the right-hand box into the list of matching attributes again - on top of the matching attribute for Channel that you created above

This lookup will find values in the external database table which match the output records on CustomerRef and Channel

• Press  in the main pipe configuration form to save your changes
• Open the configuration form for the Stream Channel List Reconciliation
• Add an attribute with Name InvoiceCheck
• In this Attribute, you will record whether you returned the channel from the inventory table - to do this, you will count the records returned from the lookup; if this is 1, you will record that the reconciliation check was successful:
  • Set the Expression of the Attribute to:

if (countElements(inv) == 1,
 "YES"
 ,
 "NO"
 )

• Save your changes to the Stream
• Run Analysis on Channel List Reconciliation
• Review the results, and see which channels reconciled against the list from the inventory database, and which did not
  • Remember that you can open the data in the external inventory table using the PhixFlow schema browser – if you want to verify the results