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 data set.
The pipe must be enabled to make it active.
For advanced configuration, see Advanced Pipe Configuration.
| Panel | ||||
|---|---|---|---|---|
| ||||
A pipe joining a datasource to a data collector has no editable details. All the output data set configuration occurs in the SQL query on the collector. |
Name
The name is used to refer to the pipe in other model elements.
| Panel | ||||
|---|---|---|---|---|
| ||||
The name can have no special characters except the underscore character '_' and it has to start with a letter and cannot be an Attribute Function name. |
Type
There are 3 options available:
...
Data To Read
Specify what input data to use. There are 4 options available:
...
| Panel | ||||
|---|---|---|---|---|
| ||||
If the Only collect from same run flag is ticked, the pipe will only collect data from inputs from the same analysis run that is generating the output data. This is only used when building a transactional model. |
| Panel | ||||||
|---|---|---|---|---|---|---|
| ||||||
In some circumstances the input Stream may have Stream Sets that have dates in the future relative to the Stream Set being generated for the output Stream. This may happen, for example, if you have rolled back a number of Stream Sets on the output Stream but have not rolled back the corresponding Stream Sets on the input Stream, and have then requested that the output Stream is brought up to date. Some of the Stream Sets on the input Stream will have dates in the future relative to the some of the Stream Sets you are rebuilding. By default, pipes will ignore any Stream Sets with dates in the future relative to the Stream Set you are generating. This is so that if you are rebuilding an old Stream Set the pipe will retrieve the same data on the rerun as it retrieved when the Stream Set was first built. Similarly, if you are running a Transactional Stream, it is possible that while your analysis run is taking place, other analysis runs which started after yours may have completed before yours. These will have generated 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 it is possible to tell the pipe not to ignore these future Stream Sets by ticking the Read Future Data tick box on the Advanced tab. |
Static
Normally when a Pipe requests data from a non-static input Stream then that Stream will first attempt to bring itself up to date, generating new Streamsets as necessary, before supplying the data requests. However, if this field is ticked, the input Stream will not attempt to do this.
Multiplier
This causes the pipe to present each candidate set to the output stream in a different way than usual. The multiplier flag is on the Advanced tab of the form.
When processing data, a Stream will usually take one candidate set from each of its input pipes. If the multiplier flag is ticked on one of these, then the stream changes its behaviour to take / instead takes one record from the 'multiplied'/multiplier pipe, and all the records on the other pipes contribute to the pool of candidate sets available to the stream in the usual way. The process then repeats with the next record in the multiplier pipe record set.
Filters, sorting and grouping, aggregating
Filters, sorting and grouping, and aggregating are configured through their own tabs on the form:
...
Pipe Form Reference
The following fields are configured on the Details tab:
...
This field is used to determine which Streamsets to read from the input Stream.
...
Normally when a Pipe requests data from a non-static input Stream then that Stream will first attempt to bring itself up to date, generating new Streamsets as necessary, before supplying the data requests. However, if this field is ticked, the input Stream will not attempt to do this.
...
If this flag is not ticked then it is an indication to PhixFlow that the Stream is not ready to be used during any analysis runs and should be therefore be ignored.
The following fields are available on the Details tab if you set Date To Read = Custom:
...
- Only collect from same run is not ticked
- Max Stream Sets is blank or zero
- Historied is not ticked
The following fields are configured on the Advanced tab:
...
Mandatory
...
If ticked, when multiple Streams are being merged then there must be an input record from this Pipe for an output record to be generated by the output Stream.
If this is a push pipe with positive offsets and this flag is ticked then the notification to create another stream set will only be pushed along the pipe if the last stream set created contains at least one record.
...
The Execution Strategy determines how this pipe should be implemented. See the section on Directed Merge Strategy
...
The maximum number of concurrent worker tasks.
If blank, this defaults to 1.
...
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.
...
The cache is used when carrying out lookups from streams or database collectors. When doing a lookup, there are two common scenarios:
- 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)
- 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 condition:
| 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 | ||||||
|---|---|---|---|---|---|---|
| ||||||
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.
|
| Panel | ||||||
|---|---|---|---|---|---|---|
| ||||||
| 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. |
...
During Look Ups
...
During File Export
...
During Drill Down
...
Where to look next
...
| Insert excerpt | ||||||||
|---|---|---|---|---|---|---|---|---|
|
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.
| Tip |
|---|
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:
|
| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|
| Insert excerpt | ||||||||
|---|---|---|---|---|---|---|---|---|
|
| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|
Basic Settings
| Field | Description | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Name | Enter a name. The name is used to refer to the pipe in other model elements. Pipe names default to The name:
| ||||||||||||||||
| Enabled |
| ||||||||||||||||
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.
Pipes from collectors cannot be marked as static. In the model, hover over a pipe to display an icon that shows | ||||||||||||||||
| Mandatory |
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 |
| ||||||||||||||||
| Type | Select:
| ||||||||||||||||
| Data to Read | Select the type of input data to use.
| ||||||||||||||||
| 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.
| ||||||||||||||||
| 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. |
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Tip |
|---|
When the pipe Type is Lookup, the filter controls which data will be cached in memory. |
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:
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 view on the input table. Select a view from the list. If the input table has no views, the list will be empty.
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:
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 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:
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:
|
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 |
|---|---|
| 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.
|
| 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:
|
Using the Sort/Group Grid
| Anchor | ||||
|---|---|---|---|---|
|
To add an attribute to the list:
- click
to open the list of attributes in the input tableInsert excerpt _attributes_show _attributes_show nopanel true - drag an 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 table, in the section toolbar, click
| Insert excerpt | ||||||
|---|---|---|---|---|---|---|
|
| 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
| ||||||||||||||||
| 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 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
|
| 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 table. You can refer to it using the format |
Aggregate Attributes Anchor aggregate aggregate
| aggregate | |
| aggregate |
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.
To add an attribute to the list:
- click
to open the list of attributes in the input tableInsert excerpt _attributes_show _attributes_show nopanel true - drag an 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 |
|---|---|
| A Function | Select an function to combine 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 attribute aggregated from the list of attributes in the input table. 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 table. |
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 Streamsets | This field is available when the input is not Transactional. Where the input table is transactional PhixFlow will behave as though the box is ticked.
PhixFlow cannot complete a recordset if:
Pipes that are not used in the analysis run do not try to complete a recordset, so will not report an error. (Unused pipes can occur if they lead to tables on branches of the model that are not being run.) | ||||||||||||||||||||||||||
| Buffer Size | Enter a number for the buffer size used to perform the table calculation. If a large amount of data is being processed, then setting a large buffer size will give better performance. |
...