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 

   s in the toolbar to show the list of options.

2. Drag 

    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 

   .

5. At the top of the Last bill amount database collector settings, press 

    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 

    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 

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:

Open

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 

   .

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

      .

   5. In the Order/Index section:

      • Press 

        .

      • 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.

      • Open

        

        • 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 

       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. Open

      

8. In the model window, click 

   .

Check Point

At this point your screen should look similar to this:

Open