This page is for data modellers who need to edit the properties of a pipe.

Overview

A pipe is a connector that links two objects in a PhixFlow model and sends data from the input object to the output object. Pipes allows you to control which attributes and which records from the input are delivered by to the output. With default configuration the pipe pass all attributes and records from the current run.

The pipe must be enabled to make it active.

For advanced configuration, see Advanced Pipe Configuration.

A pipe joining a datasource to a data collector has no details to edit. All the configuration for the output data set occurs in the collector:

either in a database collector for a database datasource
or in an HTTP collector for an HTTP datasource.

Basic Settings

Field	Description

Field	Description
Name	Enter a name. The name is used to refer to the pipe in other model elements. Pipe names default to `in`. The name: must start with a letter must not be an Attribute Function name. must not include special characters, except underscore _
Enabled	to prevent the pipe from being used during an analysis run. to indicate the pipe properties are complete and the pipe is ready to be used.
Static	Normally when a pipe requests data from a non-static input table, that table will first attempt to bring itself up to date, generating new recordsets as necessary, before supplying the data requested. However, if this field is ticked, the input table will not run. Pipes from collectors cannot be marked as static. when the pipe requests data from a non-static input table, that table will first attempt to bring itself up to date, generating new recordsets as necessary, before supplying the data requested. to prevent the input table from updating itself. The pipe will pull the existing data from the input table. Pipes from collectors cannot be marked as static. On the model, any pipes set as Static will display the static icon, .
Mandatory	to indicate that, when multiple tables are being merged, there must be an input record from this pipe for an output record to be generated by the output table. If this is a push pipe with positive offsets and this option is ticked then the notification to create another recordset will only be pushed along the pipe if the last recordset created contains at least one record. This causes the pipe to present each candidate set to the output table in a different way than usual.
Multiplier	is the default. so that, for each output record generated by a table, the table will get a set of records from each of its input pipes. If the multiplier flag is ticked on one of these, then the table will generate an output record for each record from the set of records provided by the multiplier pipe. For each output record, each of the other input pipes will provide the same set of records as normal.
Action Pipe	Available when Type is Look-up. Flag a pipe as an Action Pipe if it is used in a table-action(s). The flag is used to show or hide action pipes on the analysis model canvas. See Analysis Model Window and Toolbars.
Type	Select: Pull: pull pipes are the most common type in PhixFlow - they "pull" data from the input to the output. Pull pipes are shown as solid arrows on models. Look-up: look-up pipes are used to enrich data. Typically, you will have one or more pull pipes to supply the base data for an output, and if needed, one or more look-up pipes to enrich the base data with values from additional inputs. Look-up pipes are shown as dashed lines on models. Push: data is "pushed" rather than "pulled" into the output table. Push pipes are most commonly used when sending data from tables to exporters (File Exporters, Database Exporters, HTTP Exporters). Push pipes are shown as dotted lines on models.
Data to Read	Select the type of input data to use. Latest: supply data from the current run (the latest recordset). This is the mostly commonly used option. Previous: supply data from the previous run (the previous recordset). This is used when you are comparing data for the current run with data from the previous run, for example, today's data with yesterday's. All: supply data from all runs (all recordsets). All Previous: supply data from all runs except the current run (all recordsets except the latest recordset). Same Run: this option should only be used where the input and output tables are set to Period: Transactional. The pipe will only collect data from inputs in the same analysis run. This configuration support several analysis runs going on at the same time without interfering with each other. Custom: select this option to display additional settings, described in the Custom Data to Read section, below. We recommend that you only use the custom settings when directed to by PhixFlow consultants or support.
Read Future Data	Use this option to exclude or include input tables sets that have future dates relative to the recordset you are generating. For details about how future recordsets occur, see Managing Future Recordsets, below. to exclude future recordsets from this analysis run. This is the default. to include future recordsets in this analysis run. For example, for a table with Period: Transactional , you will want to include new recordsets that are being added to the input table after your analysis run starts.
Input	Enter the name of the object at the start of the pipe.
Output	The name of the table at the end of the pipe.

Managing Future Recordsets

In some circumstances the input table may have recordsets that have dates in the future relative to the recordset being generated for the output table. This may happen, for example, if:

you roll-back some recordsets on the output table
but do not roll-back the corresponding recordsets on the input table
and then request that the output table is brought up to date.

Some of the recordsets on the input table will have dates in the future relative to some of the recordsets you are rebuilding.

By default, the Read Future Data check box is not ticked. This means pipes ignore any recordsets with dates in the future relative to the recordset you are generating. You want to ignore future recordsets when you rebuild an old recordset, because you want the pipe to retrieve the same data on the rerun as it retrieved when the recordset was first built.

When you run analysis on a table 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 recordsets on the input table with a future data relative to the date of the recordset you are generating. For transactional input tables, you want the pipe to use these future tables. 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.

When the pipe Type is Lookup, the filter controls which data will be cached in memory.

Field	Description

Field	Description
Cache Size	Available when Type is Lookup. The default value is set in System Configuration. For lookup pipes, PhixFlow uses the pipe cache when it looks-up data from tables 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 table 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 table - the attribute used to filter the pipe for a database collector - a condition in the database query. For example: `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. 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: a single read returns 100% or more of the cache size and the System Configuration → Enforce Cache Size limit flag is ticked. Error Message: Cache Size Limit Exceeded `The Pipe "table_name.lookup_pipe_name" cache is 100% full (the cache size is 10).` 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 table 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.
Pipe View	Use this option to look up data from attributes that are present in a view on the input table. Select a view from the list. If the input table has no views, the list will be empty. 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 table 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 view are sent to the output table. Pipe views are very useful: during lookups a pipe loads and caches all of the attributes from the table. 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 table. 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. If a File Exporter is configured to export to Excel or to HTML: and it has an Excel template, PhixFlow uses this template and does not use any template specified in the pipe view. but it does not have an Excel template, PhixFlow will use the Excel template specified in the pipe view (if there is one) and the pipe view is a chart view, PhixFlow will export a PNG picture of the chart.
Include History Records	to automatically filter out superseded records. to include superseded records.
Condition	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.
Clause	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.
Filter Icons	Hover your mouse pointer over conditions or clauses to display: another condition or clause to the filter the selected clause or condition from the filter. indicates the value entered is a literal value. Click this icon to treat the value as an expression. indicates the value entered is an expression. Click this icon to treat the value to a literal string. Note: ["123", "234", "345"] looks like a literal value but it can be evaluated as an expression. Open the expression in a larger editor.
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: 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 table) 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: `StartDate >= _out.TransDate && (EndDate <= _out.TransDate \|\| EndDate == _NULL)` Cache extraction filters are entered free hand. The attribute names referenced must exist in a table. This means that the each attribute must be one of: an attribute in a source table, if you are reading from a table if you are reading from an external database table, one of the fields returned by the database collector AND an attribute in the output table. This means to use an attribute with the source as a database collector, there must be an attribute of matching name in the output table an attribute in the destination table, 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 or Order/Index

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; see Using the Sort/Group Grid, below
the following below the grid, when Basic Settings → Type is Look-up:

Field	Description

Field

Description

Maximum Number of records per Group

Available when Type is Look-up, except where the pipe is connected to a calculate table.

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.

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 tables. 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 an attribute to the list:

click
to open the list of attributes in the input table
drag an attribute into the grid.

To remove an attribute, click

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 table, in the section toolbar, click

. PhixFlow opens the attribute's sort properties:

Field	Description

Field	Description
Attribute	For input 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 (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	by default, data is not grouped. 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 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 tables. Indexed access is controlled through configuring a pipe with an index and setting index expressions on "Group By" attributes here.
Audit Summary	This option is on the ; see Common Properties.

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 table. You can refer to it using the format _out.AttributeName

Aggregate Attributes

Use this section to define the properties of data that you want to combine as it comes through the pipe.

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.