Forms: Database Collector
A Database Collector reads data from a PhixFlow Datasource. It holds the SQL that will be sent to the datasource to retrieve data.
The query columns must match up to any Streams saving the data.
Form: Database Collector Details
The following fields are configured on the Details tab:
| Field | Description |
|---|---|
| Name | Name of the database collector. |
| Datasource | The datasource that this database collector will collect from. |
| Enabled | Tick when the configuration is complete and the database collector is ready to be used. |
| Allow Non-Scheduled Collection | If this is turned on, then the database collector will run as part of any ad-hoc Analysis Engine run which requires this data. If not, it will only run as part of a scheduled Task Plan under the Analysis Engine. |
| Datasource Instance Expression | The datasource to which this database collector is connected may list multiple database instances from which the data may be read. Each Datasource Instance is identified by a unique string. This expression should evaluate to a string which allows the database collector to determine the specific instance to use. If the expression is blank then the database collector will assume that there is only one instance and will use that one by default. If there is more than one instance and no expression is provided here then an error will be thrown during analysis since the database collector will be unable to determine which instance to use. |
The following fields are configured on the Query String tab:
| Field | Description |
|---|---|
| Query String | Query to be processed by the database collector to retrieve data from the database. |
The query string must bring back data in fields with the same name as the stream attributes you wish to populate. So to collect data into a stream with attributes account_number, cust_ref and eventsource from a table with fields account_number, customer_ref and eventsource use the query string:
select account_number, customer_ref as cust_ref, eventsource from billing_products where from_dat >= to_date({_fromDate}, 'yyyymmdd') and to_dat <= to_date({_toDate}, 'yyyymmdd')
Note the use of directly entering a PhixFlow Expression into SQL to calculate values. Expressions must be surrounded by curly braces i.e. start with a '{' and end with '}' and take the form of a standard PhixFlow Expression . In the example above the from and to dates of the stream period are referenced through the special Internal Variables .
If the dates include a time element, use the query string:
select account_number, customer_ref as cust_ref, eventsource from billing_products where from_dat >= to_date({_fromDate}, 'yyyymmdd.hh24miss') and to_dat <= to_date({_toDate}, 'yyyymmdd.hh24miss').
Make sure you do not include the standard sqlplus terminator (in Oracle, this is a semi-colon) as this is not needed.
If you are collecting data directly into a merge, you must match in your query the ordering which is represented by the grouping in the input pipe from the collector into the merge. For example, if you have specified Customer and Product as the grouping attributes on the pipe connecting the collector to the stream then you must include the appropriate order by expression in the sql to ensure the data is sorted by customer and then product.
Once you have entered a query you can click on the button (
) above the top right corner of the query dialog box. This button will use the information provided in the query to automatically create a new stream connected to the collector. The stream will be created with attributes named to match those that will be returned by the query. The attributes will have the appropriate types and lengths set and will have appropriate attribute expressions to map the incoming data to the stream attributes.
The following fields are configured on the Description tab:
| Field | Description |
|---|---|
| Description | A description of the database collector. |
Form Icons
The form provides the standard form icons as well as the following:
Creates a new Stream with the attributes implied by the query. The new Stream will be shown on the modelling pane connected to this Database Collector. To use this feature, the supplied query must be pure SQL i.e. should not contain embedded expressions (as described above). PhixFlow wraps the query with a "where 1= 0" to avoid a long running statement. | |
Clicking this icon runs the SQL and pops up a Data Grid showing the first page of returned data. |