Versions Compared

Version Old Version 69 New Version 70
Changes made by

Fiona Sargeant (Unlicensed)

Fiona Sargeant (Unlicensed)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Overview

A pipe is a connector that links two elements in a PhixFlow model and sends data from the input to the output. Pipes allows you to control which attributes and which records from the input are delivered by to the output, although in most cases - with minimal configuration - you will get all columns and the records from the current run.

...

Country Is in ["England","France","Germany"] fx

Sort/Group

Use this section to group and sort data as it comes through the pipe. for lookup pipes, this section is called Order/Index. This section has:

  • a toolbar with standard buttons
  • a grid that lists the attributes that you want to sort or use to group
  • below the grid are the following options:
FieldDescription
Maximum Number of records per Group

Enter an upper limit for grouped records.

When collating the input records into groups, PhixFlow uses the specified sort order. When it has added the maximum number of records, any more records for the group are ignored.

This can be useful if you want the most recent record for an attribute that has many records. 

  1. Tick the Group checkbox for the attribute you want to use for grouping.
  2. On an appropriate date attribute, apply a (Z-A) sort order.
  3. Set the Maximum Number of Records to 1. 
Index Type

This field is available for pipes with the Type= Look-up.

Look-up pipes can be configured for fast "indexed" access to cached data collected from external tables, files or from other streams. Indexed access is controlled through configuring a pipe with an index and setting index expressions on grouping attributes. If the Type field on the Pipe is set to 'Look-up' then the field "Index Type" becomes available. This can have the value "None" meaning that there are no index keys or "Exact Match", "Best Match" or "Near Match" as described below:

  • Exact Match: The pipe retrieves data from its cache based on an exact match look-up with the values provided after evaluating the index expressions on the "Group By" attributes.

  • Best Match: The pipe retrieves data from its cache based on a "Best Match" look-up after evaluating the index expressions on the "Group By" attributes.
    Note: The last Group By Attribute with a key expression is used for the best match lookup. The index keys on any Group By attributes with a lower sequence number are used as an initial "Exact Match" to find the set of data on which to do the "Best Match". The "Best Match" is defined as the longest key value which matches the evaluated index expression.

  • Near Match: The pipe will retrieve data from its cache based on a "Near Match" look-up after evaluating the index expressions on the "Group By" attributes.
    Note: the last Group By Attribute with a key expression is used for the near match lookup. The index keys on any Group By attributes with a lower sequence number are used as an initial "Exact Match" to find the set of data on which to do the "Best Match". When "Near Match" is selected, an additional field appears where you can enter an expression which should evaluate to a number representing the allowed number of edits (e.g. deletions, insertions, substitutions and transpositions) which can be made when comparing the result of the index expression to the index key in order to achieve a match. For example if the index key is "Smyhte" and the result of the index expression is "Smith" this would still be a match providing that the allowed number of edits is 3 or more (i.e. substitute the 'i' for a 'y', transpose the 't' and the 'h' and then insert an 'e' at the end).

Using the Sort/Group Grid

To add a stream attributes attribute to the list:

  • click 
    Insert excerpt
    _attributes_show
    _attributes_show
    nopaneltrue
     to open the list of attributes in the input stream
  • drag a stream attribute into the grid.

To remove an attribute, click 

Insert excerpt
_delete
_delete
nopaneltrue
 in the toolbar.

To set the sort or group properties for an attribute, double-click its name in the grid. If you want to create a new attribute that is not present in the input stream, in the section toolbar, click

Insert excerpt
_add
_add
nopaneltrue
. PhixFlow opens the attribute's sort properties:

FieldDescription
Attribute

For input stream attributes, PhixFlow displays the attribute name. (Read-only)

For a new attribute, enter a name.

OrderEnter the number for the order the attribute appears in the grid and the order in which it is processed. Other attributes are renumbered.
Direction

Select the sort order

  • (A-Z) to sort data records in ascending order, e.g. A to Z, 1 to 9, earliest to latest date.
  • (Z-A) to sort data records in descending alpha-numeric order, e.g. Z to A, 9 to 1, latest to earliest date.
Group

Insert excerpt
_check_box_untick
_check_box_untick
nopaneltrue
 by default, data is not grouped.

Insert excerpt
_check_box_ticked
_check_box_ticked
nopaneltrue
 to group data records by the value in this attribute.

If this attribute is part of the candidate key set, you must tick the Group checkbox. Otherwise, the attributes will be used only to sort the data in the candidate set.

Index Expression

This field is available for lookup pipes with an Index Type option selected.

Look-up pipes can be configured for fast "indexed" access to cached data. This data is collected from external tables, files or from other streams. Indexed access is controlled through configuring a pipe with an index and setting index expressions on "Group By" attributes here.

Audit SummarySee Common Properties.


Tip

In some cases, you may have a pipe connected to a database collector, which pulls data from an external database table. In these cases, the fields in the database must have matching attribute names in the output stream. You can refer to it using the format _out.AttributeName

Aggregate Attributes

Aggregate Attributes Use this section to define the aggregated properties that are available when data is read from an aggregating Pipe. Note that Aggregate Attributes are not available on Pipes from Database Collectors (any aggregation can be performed in the query SQL), nor are they available on Pipes from File Collectors.

Possible aggregate values are counts, summations, averages and maximum or minimum values of Stream Items grouped in the Group/Order tab of the Pipe.

Use this section to combine data from different attributes it comes through the pipe. of data that you want to combine as it comes through the pipe.

Note

You cannot aggregate data from attributes if the pipe's input is from:

If you need to aggregate data from a database collector, you can use an SQL query. 

This section has:

  • a toolbar with standard buttons
  • a grid that lists the attributes that you want to aggregate.

.

Aggregate Attributes define the aggregated properties that are available when data is read from an aggregating Pipe. Note that Aggregate Attributes are not available on Pipes from Database Collectors (any aggregation can be performed in the query SQL), nor are they available on Pipes from File Collectors.

Possible aggregate values are counts, summations, averages and maximum or minimum values of Stream Items grouped in the Group/Order tab of the Pipe.

...

Only aggregate attributes which can be aggregated. For example, do not try to sum an attribute which contains text.

Custom Data to Read

Data to Read = Custom

The following fields are available in the Basic Settings section if you set Data To Read = Custom:

...

Use this option to exclude or include input streams sets that have future dates relative to the stream set you are generating. For details about how future stream sets occur, see Managing Future Stream Sets, above.

...

Using the Aggregate Attributes Grid

To add a stream attribute to the list:

  • click 
    Insert excerpt
    _attributes_show
    _attributes_show
    nopaneltrue
     to open the list of attributes in the input stream
  • drag a stream attribute into the grid.

To remove an attribute, click 

Insert excerpt
_delete
_delete
nopaneltrue
 in the toolbar.

To set the properties for an aggregate attribute, double-click its name in the grid. PhixFlow opens the attribute's sort properties:

FieldDescription
Stream Function

Select an function to combine stream data records.

  • Average
  • Count
  • Distinct
  • Maximum
  • Minimum
  • Sum

See Aggregate Function for details. Make sure the function matches the data in the attribute. For example, you cannot Sum text.

Attribute

Select the stream attribute aggregated from the list of attributes in the input stream.

PhixFlow does not use the value in this field if the Aggregate Function is Count.

NameEnter the name for the aggregated attribute. This can be the same as the original attribute.
OrderThe order of the aggregate attribute in the output stream.

Advanced

 to include future this analysis run. For example, for a stream with Period: Transactional , you will want to include new streams sets that are being added to the input stream after your analysis run starts.
FieldDescription
Data Expected

This checkbox is available when the pipe Type is Push or Pull.

Insert excerpt
_check_box_untick
_check_box_untick
nopaneltrue
 means the pipe may receive no data from its input during an analysis run.

Insert excerpt
_check_box_ticked
_check_box_ticked
nopaneltrue
  means PhixFlow reports an error if the pipe receives no data from the input datasource, collector or stream during an analysis run.

Allow Incomplete Stream Sets

Insert excerpt
_check_box_untick
_check_box_untick
nopaneltrue
 to complete a stream set before passing the data to the output stream. During the analysis run, PhixFlow pulls data into the input stream until the stream set is complete. If it cannot complete the stream set, PhixFlow reports an error message.

PhixFlow cannot complete a stream set if:

  • either the input stream is set to be  
    Insert excerpt
    _static
    _static
    nopaneltrue
  • or all of the pipes reading from the stream are static.

Insert excerpt
_check_box_ticked
_check_box_ticked
nopaneltrue

  PhixFlow ignores incomplete stream sets in

Advanced section

The following fields are configured in the Advanced section:

Cache Size

The cache is used when carrying out lookups from streams or database collectors. When doing a lookup, there are two common scenarios:

  1. The pipe does a single lookup onto a stream or database table to get a large number of records in one go (e.g. 10,000 records)
  2. The pipe does many lookups, getting a small number of records for each lookup (e.g. 10 records at a time).

In case 2, the results returned are typically based on a key value, e.g. an account number. This will be used in the filter of the pipe, if you are reading from a stream, or in the query, if you are reading from a database collector. For example, the query in a database collector will include the conditiona static input stream and does not report an error.

FieldDescription
Data ExpectedThis field is available when the Pipe Type is Push or Pull. This flag allows the user to specify that the pipe is expecting to receive data. If ticked but no data is received this is treated as an error.
Allow Incomplete Stream Sets

Normally, when a pipe tries to read from an input stream that contains an incomplete stream set, PhixFlow will attempt to complete the stream set before passing data down the pipe. However if the stream is static (i.e. the stream has its 'static' flag ticked) or is effectively static (i.e. all of the pipes reading from it in this analysis run are static) then, instead of completing the stream set, an error message is produced indicating that you cannot read from this stream because it contains an incomplete stream set.

If you do not want this error message to be produced when reading from static (or effectively static) streams, but would instead prefer PhixFlow to ignore the incomplete stream sets, then you must tick this box on all pipes that will read from the input stream in this analysis run. If there are multiple pipes that read from the input stream during this analysis run and even one of the pipes does not have this box ticked then you will not be allowed to read from the stream and the error message will be produced.

Pipes which are not used in the current analysis run (for example where they lead to streams on branches of the model which are not run by the current task plan) have no effect on whether or not the error message is produced.

Anchor
pipeCacheSizepipeCacheSize
Tip

You must tick this checkbox on all the pipes that will read from a static (or effectively static) input stream in the analysis run. PhixFlow will report an error if there is any pipe trying to complete the stream set during the analysis run.

Pipes that are not used in the analysis run do not try to complete a stream set, so will not report an error. (Unused pipes can occur if they lead to streams on branches of the model that are not being run.)

Cache Size

Anchor
pipeCacheSize
pipeCacheSize
 PhixFlow uses the pipe cache when it looks-up data from streams or database collectors. For efficiency, the records are cached (stored temporarily in memory) so that if the same set of records need to be looked up again they are readily available without going back to the database.

Enter a number to set a limit on the data cache size available for the pipe. You need to estimate the largest number of records that the lookup pipe will return on a single read.


If you do not set a limit for the cache, PhixFlow uses the system default set in System Configuration →  System Tuning → Maximum Pipe Cache Size. You may want to set a cache size on individual pipes if: .......PhixFlow may need to: 

  • either lookup many records: the pipe does a single lookup onto a stream or database table to get a large number of records in one go (e.g. 10,000 records)
  • or lookup few records may times: the pipe does many lookups, getting a small number of records for each lookup (e.g. 10 records at a time).
  • either lookup many records: the pipe does a single lookup onto a stream or database table to get a large number of records in one go (e.g. 10,000 records)
  • or lookup few records may times: the pipe does many lookups, getting a small number of records for each lookup (e.g. 10 records at a time).

Lookup for a few records may times

In this case, PhixFlow is usually using a key value, such as an account number,  to get the data. The key value is

  • for a stream - the attribute used to filter the pipe
  • for a database collector - a condition in the database query. For example:
Code Block
WHERE AccountNumber = _out.AccountNum

For efficiency, the records are cached (stored temporarily in memory) so that if the same set of records need to be looked up again they are readily available without going back to the database.


This field allows you to set a limit on the size of the cache. Setting a limit is important because if you do not, the cache can become very large and consume a lot of memory, which can lead to a slow down in both your tasks and those of other users of PhixFlow.

To set the cache size, try to estimate the largest number of records that the lookup pipe will return on a single read.

If you do not set a limit, it will default to the system-wide default, specified in the Maximum Pipe Cache Size in the System Tuning tab of the System Configuration.

Panel
bgColor#e6f0ff
titleBGColor#99c2ff
titleCache warnings and errors

If a single read brings back over 90% of the specified cache size, a warning message will be logged to the console.

If a single read brings back 100% or more of the cache size, a second warning message will be generated. If the Enforce Cache Size limit flag is ticked in System Configuration, instead of a warning an error will be generated, and the analysis run will stop completely.

Code Block
titleError Message: Cache Size Limit Exceeded
The Pipe "stream_name.lookup_pipe_name" cache is 100% full (the cache size is 10).



Panel
bgColor#e6f0ff
titleBGColor#99c2ff
titleTechnical breakout
Every time the lookup pipe is referenced, PhixFlow calculates the values of all of the variable elements of the query or pipe filter, and checks if it already has a set of data in the cache retrieved using this set of variable values. If so the data is immediately returned from the cache. Otherwise, a new set of data is read from the stream of collector. If adding the new records to the cache would cause it to exceed the maximum cache size, previously cached results are removed to make enough room for the new results.
Buffer SizeThe buffer size used to perform the stream calculation. If a large amount of data is being processed, then setting a large buffer size will give better performance.






Pipe ViewThe pipe view is used to limit which fields are retrieved down the pipe and in what order, and in some circumstances how each field is to be formatted. You can select from any of the views that have been configured on the source stream. Please note that any sorting or filtering of records will have to be applied directly on the pipe, and will not be inherited from the pipe view.
The pipe view is used in two contexts.

During lookups

Pipe views can also be used on lookup pipes to limit the fields that are returned by the lookup request. This is most useful in the scenario where the you want to read and cache data on a lookup pipe from a stream that has lots of attributes but where only a small number of attributes are actually required. You can simply create a new view on the source stream listing only the attributes needed, then specify it as the pipe view on the lookup pipe. Only those attributes specified on the view will then be loaded.
Without a pipe view, the pipe will load and cache all of the attributes from the stream which may consume a significant amount of free memory if there are a large number of records.

During File Export

When sending data to a file exporter only those fields specified on the pipe view will be exported. If no pipe view is supplied then all fields will be exported.
If the file exporter is configured to export to Excel or to HTML, and no Excel template is specified on the exporter, then the pipe view will be checked to see if an Excel template has been specified there. This template will then be used as part of the export. See the description of file exporters for further details. If an Excel template has been specified on the exporter then this will override any template specified on the pipe viewIf there are a large number of records to cache, it can use a lot of memory.  This means PhixFlow will run more slowly for all users.

To set the cache size, try to estimate the largest number of records that the lookup pipe will return on a single read.

If you do not set a limit, it will default to the system-wide default, specified in the Maximum Pipe Cache Size in the System Tuning tab of the System Configuration.

PhixFlow may need to: 

Buffer SizeThe buffer size used to perform the stream calculation. If a large amount of data is being processed, then setting a large buffer size will give better performance.Pipe ViewThe pipe view is used to limit which fields are retrieved down the pipe and in what order, and in some circumstances how each field is to be formatted. You can select from any of the views that have been configured on the source stream. Please note that any sorting or filtering of records will have to be applied directly on the pipe, and will not be inherited from the pipe view.

The pipe view is used in two contexts.

During lookups

Pipe views can also be used on lookup pipes to limit the fields that are returned by the lookup request. This is most useful in the scenario where the you want to read and cache data on a lookup pipe from a stream that has lots of attributes but where only a small number of attributes are actually required. You can simply create a new view on the source stream listing only the attributes needed, then specify it as the pipe view on the lookup pipe. Only those attributes specified on the view will then be loaded.

Without a pipe view, the pipe will load and cache all of the attributes from the stream which may consume a significant amount of free memory if there are a large number of records.

During File Export

When sending data to a file exporter only those fields specified on the pipe view will be exported. If no pipe view is supplied then all fields will be exported.

If the file exporter is configured to export to Excel or to HTML and the pipe view is a chart view then the output will be a picture of the chart in PNG format.

Max Records To Read

The maximum number of records that should be read down this pipe. The

, and no Excel template is specified on the exporter, then the pipe view will be checked to see if an Excel template has been specified there. This template will then be used as part of the export. See the description of file exporters for further details. If an Excel template has been specified on the exporter then this will override any template specified on the pipe view.

If the file exporter is configured to export to HTML and the pipe view is a chart view then the output will be a picture of the chart in PNG format.

Max Records To Read


The maximum number of records that should be read down this pipe. The pipe may read more than this number of records if it is configured to carry out multiple reads simultaneously e.g. if it is connected to a File Collector which has been configured to read multiple files simultaneously or if this pipe strategy is "Directed" with multiple workers.Strategy

The Execution Strategy determines how this pipe should be implemented.

Strategy

The Execution Strategy determines how this pipe should be implemented. See the section on Directed Merge Strategy

Max Workers

This field is only available if Strategy = Directed

The maximum number of concurrent worker tasks.

If blank, this defaults to 1.

Worker Size

This field is only available if Strategy = Directed

The number of key values to read for a single worker task (which runs a single select statement).

If blank, this defaults to 1000. This is the maximum value that can be used when reading from an Oracle databaseSee the section on Directed Merge Strategy

Max Workers

This field is available when Strategy is Directed

The maximum number of concurrent worker tasks.

If blank, this defaults to 1.

Worker Size

This field is available when Strategy is Directed

The number of key values to read for a single worker task (which runs a single select statement).

If blank, this defaults to 1000. This is the maximum value that can be used when reading from an Oracle database.




Custom Data to Read

Data to Read = Custom

The following fields are available in the Basic Settings section if you set Data To Read = Custom:

FieldDescription
From OffsetThe offset applied to the start of the collection period, relative to the period in the output stream that requires populating.
To OffsetThe offset applied to the end of the collection period, relative to the period in the output stream that requires populating.
Max Stream SetsIn almost all cases this specifies the number of stream sets to be retrieved from the input stream. However, if this is a push pipe with positive offsets this value indicates the maximum number of stream sets that can be created i.e. the maximum number of cycles this pipe can initiate.
Only collect from same runEvery time the analysis engine runs, all of the stream sets that are created by all of the streams affected by that analysis run are given the same Run ID. If this flag is ticked then the pipe will only collect stream sets from the input stream that have the same Run ID as the stream set currently being created by the output stream. You should only use this flag is both the input and output streams are transactional.
HistoriedIf ticked, the pipe will collect data from the input stream by period. So if the from and to offsets are both 0.0, and the output stream requires stream generation for the period 17/10/07 - 18/10/07, data will be collected from the input stream for the period 17/10/07 - 18/10/07. If not ticked, all data will be collected from the input stream, regardless of period. In this case, the offsets are still used to determine whether the required data periods in the input stream exist before the stream calculation can be carried out.
Read Future Data

Use this option to exclude or include input streams sets that have future dates relative to the stream set you are generating. For details about how future stream sets occur, see Managing Future Stream Sets, above.

Insert excerpt
_check_box_untick
_check_box_untick
nopaneltrue
 to exclude future stream sets from this analysis run. This is the default.

Insert excerpt
_check_box_ticked
_check_box_ticked
nopaneltrue
 to include future stream sets in this analysis run. For example, for a stream with Period: Transactional , you will want to include new streams sets that are being added to the input stream after your analysis run starts.