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

• 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 your Introduction to Modelling analysis model that will use a query that has been supplied to you. This will retrieve the last bill for each customer:

1. Hover your mouse over  Databases in the toolbar to show the list of options.
2. Drag  Database Collector from the toolbar into the model.
3. In the New Collector settings, enter:
   1. Name: Last bill amount
   2. Datasource: CRM

   3. Statement Expression:

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

   4. Allow Non-Scheduled Collection: .
   5. Enabled: .
4. Press  Apply.

5. At the top of the Last bill amount database collector settings, press  More Options and press  Run query and see result.

6. In a view 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.

7. Close the view window.

8. Press  Apply and Close in the Last bill amount database collector settings.

9. Now will you create a table based on the structure of the query results:

   1. Hover over the Last bill amount database collector on your model.

   2. In the pop up menu press  Create Table Using Attributes

A new table will appear on your model to store the results of the query. Run analysis on the new table and check that the data has been loaded from the database, like the image below:

Enrich customer data with last billed amount

You will now add a lookup pipe from the table Last Bill Amount into the table Customer Details with Region, linking the data across the two tables using the attribute CustomerRef:

1. Hover over the table Last Bill Amount and press  Connector.
2. Move the pointer over the top of the table Customer Details with Region and select – this will attach the end of the pipe to the selected table.
3. In the pipe details form that pops up enter:
   1. Name: lba
   2. Type: Look-up
   3. Data To Read: leave as the default – Latest
   4. Press  Apply.
   5. In the Order/Index section:
      • Press  Show Attributes.
      • Drag the attribute CUSTOMERREF from the left hand list into the list of Order/Index attributes.
      • Drag the attribute CustomerRef from the right hand list on top of the Order/Index attribute CUSTOMERREF.
      • 
        • This means that the lookup will match records in the reference data (Last Bill Amount) where CUSTOMERREF = CustomerRef in the output data (Customer Details with Region).
   6. Press  Apply and Close on the pipe configuration form.
4. A lookup pipe will appear linking Last Bill Amount to Customer Details with Region.
5. Drag the attribute BILLAMOUNT from the table Last Bill Amount onto Customer Details with Region.
6. Run analysis on Customer Details with Region.
7. View the data in the Customer Details with Region table to check that the last billed amount has been retrieved for all records, like the image below:
   1. 
8. In the model window, click  Save.

Check Point

At this point your screen should look similar to this: