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.
...
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.
The pipe must be enabled to make it active.
For advanced configuration, see Advanced Pipe Configuration.
...
By default, the Read Future Data check box is not ticked. This means pipes ignore any stream sets with dates in the future relative to the stream set you are generating. You want to ignore future stream sets when you rebuild an old stream set, because you want the pipe to retrieve the same data on the rerun as it retrieved when the stream set was first built.
When you run analysis on a stream with a transactional period, it is possible that as your analysis is still running, a different run can start and complete. This run can generate additional stream sets on the input stream with a future data relative to the date of the stream set you are generating. For transactional input streams, you want the pipe to use these future streams. To do this, tick the Read Future Data check box.
Filter
Filters are made up of a set of clauses; each clause in turn contains a number of conditions. These conditions must be satisfied for data to be passed through the pipe.
...
...
...
Available when Type is Lookup.
For lookup pipes, 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. Check whether PhixFlow is looking up:
- many records
The pipe does a single lookup onto a stream or database table to get a large number of records in one go, for example 10,000 records. - few records may times
The pipe does many lookups, getting a small number of records for each lookup, for example, 10 records at a time. 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 |
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.
...
| title | Warnings and Errors |
|---|
In the log for an analysis run, which is available in the system console, PhixFlow reports warnings when a single read returns:
- over 90% of the specified cache size
- 100% or more of the cache size.
PhixFlow reports an error and stops the analysis run when:
...
| Code Block | ||
|---|---|---|
| ||
The Pipe "stream_name.lookup_pipe_name" cache is 100% full (the cache size is 10). |
| Expand | ||
|---|---|---|
| ||
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. |
...
Use this option to look up data from attributes that are present in a stream view on the input stream.
Select a stream view from the list. If the input stream has no views, the list will be empty.
| Note |
|---|
Sorting or filtering of records must be set directly on the pipe. It is not inherited from the pipe view. |
Use the pipe view to limit the attributes that the pipe reads when:
- a stream has lots of attributes containing many data records
- but you only need data from a few attributes.
Only the data for the attributes in the stream view are sent to the output stream.
Pipe views are very useful:
- during lookups a pipe loads and caches all of the attributes from the stream. This can use a lot of memory, especially when there are many records.
- during file export all data records from all attributes are exported.
To set up a pipe view:
- Create a new view on the source stream. In the view, only add the attributes you need.
- In the pipe Pipe View option, select the pipe view.
- Run analysis. PhixFlow only looks-up or exports data from the attributes specified on the view.
| Expand | ||
|---|---|---|
| ||
If a File Exporter is configured to export to Excel or to HTML:
|
...
Select one of the options
- Where ALL...
- Where ANY...
To add more conditions, hover your mouse pointer over this field to display the button and add another condition to your filter.
...
Select an option from the list. PhixFlow adds more fields where you can:
- select how the filter matches (for example,
equals,contains,is null) - enter a string that the filter uses to match the data. The string can be an expression or a literal string.
...
Hover your mouse pointer over conditions or clauses to display:
...
Open the expression in a larger editor.
...
A cache extraction filter allows you to further filter the data retrieved by a pipe. These are not commonly used, but are sometimes helpful when either:
- Optimising performance on a lookup pipe when for a set of records, the record you require from the lookup depends on non-key data, e.g. the date
- When getting data from a pull pipe when the filter requires that you compare one value in each record with another; this is not possible within a standard filter.
For case 1, when using a lookup pipe, data retrieved is stored in a cache. See cache size for details. The cache extraction filter allows you, as you are processing a set of output records, to use different cached entries from the lookup for each of the records are you are processing. This is very fast compared to looking up from the source (i.e. going back to an external DB table or even another PhixFlow stream) for each output record.
E.g. you want to look up the credit rating for a customer for a set of transactions - in the output, each transaction is represented by a single output record. You create an indexed lookup pipe using CustNo as the key for the index. This means that for each new CustNo you encounter in the data, all the credit rating entries for that CustNo would be retrieved by the pipe and placed into the cache. The credit rating for each customer is fully historied, so you get a number of entries for each CustNo. To get the relevant lookup entry for each output report (each transaction), you need to compare the transaction date of the output record to the dates of credit rating entries in the cache. So to extract the relevant record, you include a cache extraction filter in the form:
| Code Block |
|---|
StartDate >= _out.TransDate && (EndDate <= _out.TransDate || EndDate == _NULL) |
Cache extraction filters are entered free hand.
The attribute names referenced must exist in a stream. This means that the each attribute must be one of:
- an attribute in a source stream, if you are reading from a stream
- if you are reading from an external database table, one of the fields returned by the database collector AND an attribute in the output stream. This means to use an attribute with the source as a database collector, there must be an attribute of matching name in the output stream
- an attribute in the destination stream, in which case you will refer to it using the format
_out.AttributeName
Filter Examples
Filter on Current User
Sometimes when running analysis you want to select, from the source, only records belonging to the currently logged in user. To set a filter where, say, an attribute in the source Owner equals the current logged in user, add a condition to the filter like this:
Owner Equals _user.name fx
Enter a list of values for an "Is In" or "Is Not In" filter
If you want to based on a list of values, use the Is in or Is not in comparators, then type the list of values into the comparison field as a comma separated list like this:
Country Is in England, France, Germany ABC
In this case you must NOT click the ABC icon to convert the value to an fx, because this will indicate that the value is a formula; it must be left as a literal value. If you do click the ABC icon, then the value must be entered like this:
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:
...
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.
- Tick the Group check box for the attribute you want to use for grouping.
- On an appropriate date attribute, apply a (Z-A) sort order.
- Set the Maximum Number of Records to 1.
...
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:
...
Using the Sort/Group Grid
To add a stream attribute to the list:
...
For input stream attributes, PhixFlow displays the attribute name. (Read-only)
For a new attribute, enter a name.
...
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.
...
If this attribute is part of the candidate key set, you must tick the Group check box. Otherwise, the attributes will be used only to sort the data in the candidate set.
...
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.
...
| 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 |
Aggregate Attributes
Use this section to define the properties 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.
Using the Aggregate Attributes Grid
To add a stream attribute to the list:
...
To set the properties for an aggregate attribute, double-click its name in the grid. PhixFlow opens the attribute's sort properties:
...
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.
...
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.
...
Advanced
...
This check box is available when the pipe Type is Push or Pull.
...
PhixFlow cannot complete a stream set if:
...
| Tip |
|---|
You must tick this check box 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
...
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. Check whether PhixFlow is looking up:
- many records
The pipe does a single lookup onto a stream or database table to get a large number of records in one go, for example 10,000 records. - few records may times
The pipe does many lookups, getting a small number of records for each lookup, for example, 10 records at a time. 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 |
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.
...
| title | Warnings and Errors |
|---|
In the log for an analysis run, which is available in the system console, PhixFlow reports warnings when a single read returns:
- over 90% of the specified cache size
- 100% or more of the cache size.
PhixFlow reports an error and stops the analysis run when:
...
| Code Block | ||
|---|---|---|
| ||
The Pipe "stream_name.lookup_pipe_name" cache is 100% full (the cache size is 10). |
| Expand | ||
|---|---|---|
| ||
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. |
zz
...
Use this option to look up data from attributes that are present in a stream view on the input stream
Select a stream view from the list. If the input stream has no views, the list will be empty.
| Note |
|---|
Sorting or filtering of records must be set directly on the pipe. It is not inherited from the pipe view. |
Use the pipe view to limit the attributes that the pipe reads when:
- a stream has lots of attributes containing many data records
- but you only need data from a few attributes.
Only the data for the attributes in the stream view are sent to the output stream.
Pipe views are very useful:
- during lookups a pipe loads and caches all of the attributes from the stream. This can use a lot of memory, especially when there are many records.
- during file export all data records from all attributes are exported.
To set up a pipe view:
- Create a new view on the source stream. In the view, only add the attributes you need.
- In the pipe Pipe View option, select the pipe view.
- Run analysis. PhixFlow only looks-up or exports data from the attributes specified on the view.
| Expand | ||
|---|---|---|
| ||
If a File Exporter is configured to export to Excel or to HTML:
|
Max Records To Read
...
Enter a number for 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. For example:
- a pipe can be connected to a File Collector that reads multiple files simultaneously
- the pipe strategy is Directed with multiple workers.
...
Select an option to specify how this pipe should be implemented. See the section on Directed Merge Strategy
- Default: this is the way pipes usually works
- Directed: when reading from a large data set; see Using Directed Pipes to Read from Large Data Sets
...
Max Workers
...
This field is available when Strategy is Directed
Enter the maximum number of concurrent worker tasks. When no value is specified, this defaults to 1.
...
This field is available when Strategy is Directed
Enter the number of key values to read for a single worker task, which runs a single select statement.
When no value is specified, this defaults to 1000. This is the maximum value that can be used when reading from an Oracle database.
...
The following properties are available in Basic Settings when you set Data To Read to Custom.
...
Enter the number of stream sets to be retrieved from the input stream.
...
old stream set, because you want the pipe to retrieve the same data on the rerun as it retrieved when the stream set was first built.
When you run analysis on a stream with a transactional period, it is possible that as your analysis is still running, a different run can start and complete. This run can generate additional stream sets on the input stream with a future data relative to the date of the stream set you are generating. For transactional input streams, you want the pipe to use these future streams. To do this, tick the Read Future Data check box.
Filter
Filters are made up of a set of clauses; each clause in turn contains a number of conditions. These conditions must be satisfied for data to be passed through the pipe.
Field | Description | ||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Cache Size
| Available when Type is Lookup. For lookup pipes, 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. Check whether PhixFlow is looking up:
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.
| ||||||||||||||||||||||||||||||||
| Pipe View | Use this option to look up data from attributes that are present in a stream view on the input stream. Select a stream view from the list. If the input stream has no views, the list will be empty.
Use the pipe view to limit the attributes that the pipe reads when:
Only the data for the attributes in the stream view are sent to the output stream. Pipe views are very useful:
To set up a pipe view:
| ||||||||||||||||||||||||||||||||
| Include History Records |
| ||||||||||||||||||||||||||||||||
| Condition | Select one of the options
To add more conditions, hover your mouse pointer over this field to display the button and add another condition to your filter. | ||||||||||||||||||||||||||||||||
| Clause | Select an option from the list. PhixFlow adds more fields where you can:
| ||||||||||||||||||||||||||||||||
| Filter Icons | Hover your mouse pointer over conditions or clauses to display:
| ||||||||||||||||||||||||||||||||
| Cache Extraction Filter | A cache extraction filter allows you to further filter the data retrieved by a pipe. These are not commonly used, but are sometimes helpful when either:
For case 1, when using a lookup pipe, data retrieved is stored in a cache. See cache size for details. The cache extraction filter allows you, as you are processing a set of output records, to use different cached entries from the lookup for each of the records are you are processing. This is very fast compared to looking up from the source (i.e. going back to an external DB table or even another PhixFlow stream) for each output record. E.g. you want to look up the credit rating for a customer for a set of transactions - in the output, each transaction is represented by a single output record. You create an indexed lookup pipe using CustNo as the key for the index. This means that for each new CustNo you encounter in the data, all the credit rating entries for that CustNo would be retrieved by the pipe and placed into the cache. The credit rating for each customer is fully historied, so you get a number of entries for each CustNo. To get the relevant lookup entry for each output report (each transaction), you need to compare the transaction date of the output record to the dates of credit rating entries in the cache. So to extract the relevant record, you include a cache extraction filter in the form:
Cache extraction filters are entered free hand. The attribute names referenced must exist in a stream. This means that the each attribute must be one of:
|
Filter Examples
Filter on Current User
Sometimes when running analysis you want to select, from the source, only records belonging to the currently logged in user. To set a filter where, say, an attribute in the source Owner equals the current logged in user, add a condition to the filter like this:
Owner Equals _user.name fx
Enter a list of values for an "Is In" or "Is Not In" filter
If you want to based on a list of values, use the Is in or Is not in comparators, then type the list of values into the comparison field as a comma separated list like this:
Country Is in England, France, Germany ABC
In this case you must NOT click the ABC icon to convert the value to an fx, because this will indicate that the value is a formula; it must be left as a literal value. If you do click the ABC icon, then the value must be entered like this:
Country Is in ["England","France","Germany"] fx
Sort/Group
For lookup pipes this section is called Order/Index.
Use this section to group and sort data as it comes through the pipe. 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:
| Field | Description |
|---|---|
| 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.
|
| Index Type | Available when Type is 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:
|
Using the Sort/Group Grid
To add a stream attribute to the list:
- click
to open the list of attributes in the input streamInsert excerpt _attributes_show _attributes_show nopanel true - drag a stream attribute into the grid.
To remove an attribute, click
| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|
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 | ||||||
|---|---|---|---|---|---|---|
|
| Field | Description | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Attribute | For input stream attributes, PhixFlow displays the attribute name. (Read-only) For a new attribute, enter a name. | ||||||||||||||||
| Order | Enter 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
| ||||||||||||||||
| Group |
If this attribute is part of the candidate key set, you must tick the Group check box. 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 Summary | See 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 |
Aggregate Attributes
Use this section to define the properties 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.
Using the Aggregate Attributes Grid
To add a stream attribute to the list:
- click
to open the list of attributes in the input streamInsert excerpt _attributes_show _attributes_show nopanel true - drag a stream attribute into the grid.
To remove an attribute, click
| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|
To set the properties for an aggregate attribute, double-click its name in the grid. PhixFlow opens the attribute's sort properties:
| Field | Description |
|---|---|
| Stream Function | Select an function to combine stream data records.
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. |
| Name | Enter the name for the aggregated attribute. This can be the same as the original attribute. |
| Order | The order of the aggregate attribute in the output stream. |
Advanced
The following options are available when the pipe Type is Push or Pull. Allow Incomplete Stream Sets is also available when the pipe Type is Lookup.
| Field | Description | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Data Expected |
| ||||||||||||||||||||||||||
| Allow Incomplete Stream Sets |
PhixFlow cannot complete a stream set if:
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.) | ||||||||||||||||||||||||||
| Buffer Size | Enter a number for the 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. | ||||||||||||||||||||||||||
Max Records To Read | Enter a number for 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. For example:
| ||||||||||||||||||||||||||
| Strategy | Select an option to specify how this pipe should be implemented. See the section on Directed Merge Strategy
| ||||||||||||||||||||||||||
Max Workers | This field is available when Strategy is Directed Enter the maximum number of concurrent worker tasks. When no value is specified, this defaults to 1. | ||||||||||||||||||||||||||
| Worker Size | This field is available when Strategy is Directed Enter the number of key values to read for a single worker task, which runs a single select statement. When no value is specified, this defaults to 1000. This is the maximum value that can be used when reading from an Oracle database. |
Custom Data to Read
| Anchor | ||||
|---|---|---|---|---|
|
The following properties are available in Basic Settings when you set Data To Read to Custom.
| Field | Description | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Only collect from same run | Every 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 IDof the stream sets that are created by all of the streams affected by that analysis run are given the same Run ID.
| untick_check_box_untick | ||||||||||||||
| nopanel | true | ||||||||||||||||
| Insert excerpt | _check_box_ticked | _check_box_ticked | |||||||||||||||
| nopanel | true |
| |||||||||||||||
| From Offset | Enter the offset applied to the start of the collection period, relative to the period in the output stream that requires populating. | ||||||||||||||||
| To Offset | Enter the offset applied to the end of the collection period, relative to the period in the output stream that requires populating. | ||||||||||||||||
| Max Stream Sets | Enter the number of stream sets to be retrieved from the input stream. For a push pipe with positive offsets. enter the maximum number of stream sets that can be created i.e. the maximum number of cycles this pipe can initiate. | ||||||||||||||||
| Historied |
For example, if:
the pipe reads data from the input stream for the period 17/10/07 - 18/10/07. |
...