PhixFlow Help

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Current »

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
  • No labels