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

• Create a Database Collector directly in a model
• Collect data from a database using a supplied query

Collect data from a database using a supplied query

You will add a Database Collector to use a query that has been supplied to you, which will retrieve the last bill for each customer:

• Drag - Add a new Database Collector onto the modelling pane
• In the new Database Collector form that pops up, enter the Name: Last bill amount
• Press OK

Double-click on the Database Collector to open the details form and complete the configuration:

• In the tab Query String enter:

select
CustomerRef, BillAmount
from
SOURCE_CUST_BILLS SCB1
where
BillDate = (select max(BillDate) from SOURCE_CUST_BILLS SCB2
where SCB2.CustomerRef = SCB1.CustomerRef)

• Go to the Details tab
  • Select the Datasource CRM
  • Tick the flag Enabled
  • Tick the flag Allow Non-Scheduled Collection
• Press Apply
• Return to the Query String tab, and press - Run query and see result
• In a window that pops up you can see the data that will be returned by the query; this feature is a useful way to check that your query works
• Close the data window
• Press the button  - Create a new Stream using the attributes returned from the query

A new Stream will appear on your model to store the results of the query.

• Run Analysis on the new Stream and check that the data has been loaded from the database

Enrich customer data with last billed amount

You will now add a Lookup Pipe from the Stream LastBillAmount into Customer Details with Region, linking the data across the 2 Streams using the Attribute CustomerRef:

• Hover over LastBillAmount and press  - Add a new Connector
• Move the pointer over the top of Customer Details with Region and select – this will attach the end of the pipe to the selected Stream
• In the Pipe Setup form that pops up:
  • Enter the Name: lba
  • Type: Look-up
  • Data To Read: leave as the default – Latest
  • Press 
  • In the matching attributes form that pops up create a match:
    • Drag the attribute CUSTOMERREF from the left hand box into the list of matching attributes
    • Drag the attribute CustomerRef from the right hand box into the list of matching attributes – on top of the entry for CustomerRef you created above

This means that the lookup will match records in the reference data (LastBillAmount) where CUSTOMERREF = CustomerRef in the output data (Customer Details with Region)

• • Press  on the pipe configuration form
• A lookup pipe will appear linking LastBillAmount to Customer Details with Region
• Drag the Attribute BILLAMOUNT from the Stream LastBillAmount onto Customer Details with Region
• Run Analysis on Customer Details with Region
• Check in the data in Customer Details with Region that the last billed amount has been retrieved for all records