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 25 Next »

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.

For information about the properties toolbar, and about the sections Parent Details, Analysis Models, Description and Audit Summary, see Common Properties.  We recommend you always add a Description that explains the purpose of the item you are creating. 

For a full list of all the PhixFlow property tabs and windows, see Properties, Windows, Menus and Toolbars.

The following fields are configured on the Details tab:

FieldDescription
NameName of the database collector.
DatasourceThe datasource that this database collector will collect from.
Statement ExpressionDatabase Collector#dbCollectorQueryString to be processed by the database collector to retrieve data from the database.
Allow Non-Scheduled CollectionIf 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.
EnabledTick when the configuration is complete and the database collector is ready to be used.
Advanced
TimeoutA timeout for the query.
Datasource Instance ExpressionThe 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 plain text 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.
Log Traffic

 Untick means logging will be turned off for this Database Collector. This will still be logged if Log Database Collector Statements is set in System Logging.

Error rendering macro 'excerpt-include' : No link could be created for '_check_box_ticked'.
 means the statement will be logged for this Database Collector. Details will not be logged if Allow Logging is not ticked in System Logging.

Altering the logging setting requires the Modify System Logging Configuration privilege.

Description
DescriptionOptional: A description of the database collector.

Query String

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

Complex replacement expressions

You can use more complex PhixFlow expressions in queries where necessary. To do this, precede the expression (inside curly braces) with an = sign. For example

select account_number, customer_ref from account{=accBlockString(_out.BlockID)}

There are some disadvantages when using = to precede an expression, as noted below, and therefore you should only use this when really necessary. For example, where the table or column names can vary, and are controlled by input data to the query - as in the above example, where part of the name of the table being queried is returned by a Macro "accBlockString", based on an input value "_out.BlockID".

Note: when you use the = sign to precede an expression, this may result in slower performance. If you are using this to apply a data transformation within the query, it is better to apply this transformation to the input data before you pass it into the query. This is because without the = sign PhixFlow can make use of more efficient query techniques (such as using bind variables) to optimise performance of the queries.

Note: when you use the = sign to precede an expression, PhixFlow does not automatically map PhixFlow types to database types. This means, for example, that if you pass a String PhixFlow type into a query without = you need only

{_out.CustName}

but with = you need to format the string in the query as required by the syntax of the source database, for example

{="'" + _out.CustName + "'"}

Similarly dates in expressions preceded by = must be formatted as required by the syntax of the source database.

Merging directly from a database collector

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.

Reviewing query results

Clicking  runs the SQL and pops up a Data Grid showing the first page of returned data. It is not necessary to save the query to use this option.

Generate stream from query

From the hover menu from a database collector on a Using the Model Window, clicking  creates a new Stream with the attributes implied by the query. If the Datasource Instance Expression has been entered then the expression will be used to select the datasource, otherwise the first Datasource Instance alphabetically will be used. The new Stream will be shown on the model, 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.

Time Data

PhixFlow recognises date and date-time formats, but does not recognise time-only formats, for example HH:mm:ss. When a database collector loads time data, it applies the string data-type. This applies to data from MariaDB and SQL Server databases as there is no TIME data-type in Oracle DB.

  • No labels