Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Reverted from v. 45

Insert excerpt
_Banners
_Banners
nameanalysis
nopaneltrue


This page is for data modellers. It explains the properties for database collectors.

Overview

A database collector reads data from a Datasource. It holds the SQL that will be sent to the datasource to retrieve data. The query columns must match up to any streams tables saving the data.

To add a new database collector to an analysis model:

  1. Go to the model's toolbar → Create group.
  2. Click
    Insert excerpt
    _database
    _database
    nopaneltrue
     to expand the menu.
  3. Drag a 
    Insert excerpt
_standard_settings_standard_settingsnopaneltrue
Panel
borderColor#7da054
titleColorwhite
titleBGColor#7da054
borderStylesolid
titleSections on this page

Table of Contents
indent12px
stylenone

Database Collector Properties
  1. _database_collector
    _database_collector
    nopaneltrue
     onto the analysis model.

To add an existing database collector to an analysis model, in the model diagram toolbar:

  1. Go to the model toolbar → List group.
  2. Click
    Insert excerpt
    _database
    _database
    nopaneltrue
     to expand the menu.
  3. Click 
    Insert excerpt
    _database_collector
    _database_collector
    nopaneltrue
      to open the list of available database collectors.
  4. Drag a database collector into the analysis model.

Insert excerpt
_property_toolbar
_property_toolbar
nopaneltrue

Insert excerpt
_property_tabs
_property_tabs
namebasic-h
nopaneltrue

Insert excerpt
_parent
_parent
nopaneltrue

Basic Settings

FieldDescription
NameEnter the name of the database collector.
DatasourceSelect the datasource that this database collector will collect from. To select from a list, click 
Insert excerpt
_datasource
_datasource
namelist
nopaneltrue
.
Statement Expression

Enter

a query string that will be processed by the database collector

an SQL query string, which can include PhixFlow Expressions. The database collector processes the query to retrieve data from the database; see  Statement

Expression Query String

Expressions, below, for details.

Allow Non-Scheduled Collection

Insert excerpt
_check_box_

ticked

tick
_check_box_

ticked

tick
nopaneltrue
 to allow this database collector to be run as part of any ad-hoc analysis run that requires this data.

Insert excerpt
_check_box_untick
_check_box_untick
nopaneltrue
 to restrict this database collector to being run as part of a scheduled Task Plan.

Enabled
Tick when
Insert excerpt
_check_box_tick
_check_box_tick
nopaneltrue
 when the configuration is complete and the database collector is ready to be used.

Insert excerpt
_model_prop
_model_prop
nopaneltrue

Advanced

FieldDescription
TimeoutSpecify a time period in seconds, after which a query will timeout.
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. The database collector needs to know which specific datasource instance to use.

Enter an expression that evaluates to a plain text string that corresponds to one of the unique datasource instances.

If this expression is blank then the database collector assumes that there is only one instance to use. If there is more than one instance the database collector will be unable to determine which instance to use and PhixFlow will report an error.

Log Traffic

Insert excerpt
_log_traffic2
_log_traffic2
nopaneltrue

  • Log Database Collector Statements: when ticked, PhixFlow always logs details of data loaded by database collectors, whatever is set here.

Insert excerpt
_log_traffic1
_log_traffic1
nopaneltrue

Insert excerpt
_description
_description
nopaneltrue

Statement Expression Query Strings

Insert excerpt
_audit
_audit
nopaneltrue

Statement Expressions

Testing Expressions 
Anchor
reviewCollectorQueryResults
reviewCollectorQueryResults

When the database collector is enabled, the properties menu at has an option Image Added Run Query and See Result. If you cannot see the option, click on

Insert excerpt
_more_options
_more_options
nopaneltrue
 to expand the full menu. 

Use Image Added Run Query and See Result to test the SQL query string in the expression. PhixFlow runs the SQL and opens a table showing the first page of returned data. You can Image Added Run Query even if the database collector has not been saved.

Query Strings 
Anchor
dbCollectorQueryString
dbCollectorQueryString

The query string must

bring back

return data in fields with the same name as the

stream

attributes you wish to populate. So to collect data into a

stream

table with the attributes account_number, cust_ref and eventsource from a table with fields account_number, customer_ref and eventsource, use the query string:

Code Block
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

Notice that the query string directly includes PhixFlow Expressions that calculate values.

Expressions must be

surrounded by curly braces i.e. start

enclosed in curly brackets, starting with a

'

{

'

and

end

ending with

'

}

' and

 

take the form of a standard PhixFlow Expression. Expressions can include PhixFlow's Internal Variables. In the example above

the from and to dates of the stream period are referenced through the special Internal Variables

{_fromDate} and {_toDate} are expressions that use internal variables to reference the table's period.

If the dates include a time element, use the query string:

Code Block
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


Note

Do not include the standard sqlplus terminator(For example, in Oracle,

this

the terminator 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

Code Block
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

Code Block
{_out.CustName}

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

Code Block
{="'" + _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

your query 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

table then you must include the appropriate order by expression in the SQL to ensure the data is sorted by customer and then product

. AnchorreviewCollectorQueryResultsreviewCollectorQueryResultsReviewing Query ResultsClicking Image Removed 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.

Anchor
generateStreamFromCollectorQuery
generateStreamFromCollectorQuery
Generate a

Stream from

Tablefrom a Query

From the hover menu from a database collector on a

Using the Image Removed

Image Added creates a new

Stream

Table 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

datasource instance alphabetically will be used. The new

Stream

table will be shown on the model, connected to this

Database Collector

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

Excerpt

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.


Live Search
spaceKey@self
additionalnone
placeholderSearch all help pages
typepage

Panel
borderColor#00374F
titleColorwhite
titleBGColor#00374F
borderStylesolid
titleSections on this page

Table of Contents
maxLevel3
indent12px
stylenone

 Learn More

For links to all pages in this topic, see Analysis Models for Batch Processing Data.


Insert excerpt
_terms_changing
_terms_changing
nopaneltrue