Versions Compared

Version Old Version 1 New Version 2
Changes made by

Anthony George

Zoe Baldwin

Saved on

Key

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

Overview

Tables are a key model component. They represent a structured store of data within PhixFlow receiving data from one or more components, processing it then storing it. When you make changes to tables and attributes, PhixFlow automatically publishes them to the database; see Publishing Tables.

Insert excerpt_property_toolbar_property_toolbarnopaneltrue The toolbar has the following additional buttons:  Insert excerpt_recordsets_show_recordsets_shownopaneltrue,  Insert excerpt_view_show_view_shownopaneltrue,  Insert excerpt_run_analysis_run_analysisnopaneltrue. Insert excerpt_property_tabs_property_tabsnamebasic-hnopaneltrue Insert excerpt_parent_parentnopaneltrue

Basic Settings

FieldDescriptionNameThe name of the table. Excerpt
nameFileTable
For Insert excerpt_tableFile_tableFilenopaneltrues, see File Display.

Attributes

The grid contains a list of the attributes in the table. This section has a toolbar with standard buttons, see Menu Options and Toolbar Buttons, and  Insert excerpt_table_show_table_shownopaneltrue and  Insert excerpt_file_collector_show_file_collector_shownopaneltrue.

The grid lists the attributes (data columns) in the table data. To edit the properties of an attribute, double-click the attribute name to open the Attribute properties.

To remove an attribute from the table, select the attribute and click  Insert excerpt_delete_deletenopaneltrue in the toolbar.To add a new attribute to the list, click   Insert excerpt_new_newnopaneltrue to open a new Attribute properties tab.

To add an existing attribute to the list:

Click  Insert excerpt_table_show_table_shownopaneltrue to list the tables available in the repository.
  • Expand a table to show its attributes.
  • Drag an attribute into this list to add it. 

    • To edit only the expression:

    1. Right-click an attribute name to display the context menu.
    2. Select Edit the expression field.
    3. PhixFlow opens a simple text editor box
    4. Make changes to the attributes expression.
    5. Click Image Removed to save your changes.

    Views

    This section has a toolbar with standard buttons, see Menu Options and Toolbar Buttons. The grid contains a list of the data views for this table. 

    To edit the properties of an view, double-click its name. To add a view to the list, click   Insert excerpt_new_newnopaneltrue. PhixFlow opens the View properties tab.To remove a view from the table, select the view and click  Insert excerpt_delete_deletenopaneltrue in the toolbar. AnchormultipliersmultipliersAnalysis OptionsFieldDescriptionEnabled Insert excerpt_check_box_tick_check_box_ticknopaneltrue to include the table during analysis runs.Static Data Insert excerpt_check_box_tick_check_box_ticknopaneltrue to set the table to hold static data. Static data is reference or "look up" data that is used as part of a table calculation.
    Updating a static table

    In general, static tables are not updated during an analysis run that is initiated on another table in the model. The exception is when an incoming push pipe is triggered, as this will force the table to update itself.

    Instead, to update the data in a static table you must run analysis directly on the table via:

    • the model
    • a task plan.
    Period

    Select the time period for the table:

    Excerpt
    namePeriod

    Transactional

    This allows multiple users to run independent analysis tasks at the same time.

    Daily

    This is a non-transactional table type, and generates or collects data every day.

    Monthly

    This is a non-transactional table type, and generates or collects data every month.

    Variable

    This is a non-transactional table type, and generates or collects data since the more recent run of the table to the current date.

    As transactional tables need a UID attribute, PhixFlow automatically creates this attribute if it does not already exist.

    Default Data Range

    This option affects the following items for which this is the backing table:

    • views
    • screens
    • card components.
    Insert excerpt_data_range_data_rangenopaneltrue

    If no option is is selected, PhixFlow assumes the default data range is All.

    You can override this setting using the options Use Custom Data Range and Data Range available:

    You cannot override this setting for a view.

    Table Type

    Select the table type; see Types of Table and When to Use Them. The type indicates the function that operates on the data.

    • Aggregate Table
    • Calculate Table
    • CalculateBySet Table
    • Cartesian Table
    • Merge Table
    Supersede Items on Pipe

    Select a loop pipe from the list of those available. 

    A loop pipe links the table back to itself. It compares new data records to the existing data records. If there is a repeated record PhixFlow uses the new one in the table and marks  the old one as superseded.

    Input MultiplierThe input multiplier expression should evaluate to a list of one or more values. For each value in the list, the internal variable _inputMultiplier will be set to that value and the whole table processing will be repeated i.e. the pull pipes will be read and the data from those pipes processed to generate output records to be added to the current recordset. For example :

    do ( $aRange = [], addElement($aRange, rng.RangeFrom), addElement($aRange, rng.RangeTo), $bRange = [], addElement($bRange, $aRange), $bRange )

    Where rng.RangeFrom = 500 and rng.RangeTo = 1000, the above example evaluates to [[500,1000]], which is a list containing 1 element, which is itself a list containing 2 elements. An input multiplier that evaluates to [3,4,7,8] would run the Table 4 times.
    Because Input Multipliers are evaluated first in the PhixFlow Timing Cycle they are often used to look up values that can be passed to Database or other Collectors.

    See Expressions and PhixScripts.

    Log Input Multiplier Messages Insert excerpt_check_box_tick_check_box_ticknopaneltrue to cause PhixFlow to write a message to the log every time an Input Multiplier value changes during an analysis run.Output MultiplierThis field is an expression which should evaluate to an array of values. A separate output record will be produced for each value in the array and this value is available as _outputMultiplier in each of the table output attribute expressions (each value in the array is also available through _type, although this is not recommended usage). In effect this will multiply each of the output records by the number of elements from the returned list.
    For example :

    ifNull(in.ASSET, [1,10,12] , // else do [5,7] )

    will create 3 records for every record in the table if in.ASSET contains a value (setting _type = 1, 10 and 12 in each case). Otherwise it will create 2 records for every record in the table (and set _type = 5 and 7). An output multiplier may also evaluate to a record, or a group of records. For example an Output Multiplier with the expression:

    do( lookup(lkin, $num = in.BNumber), lkin )

    will return a list of records which match the lookup on the lkin pipe. In this case the required data can be extracted from the Output Multiplier using the following expression :

    do ( $values = _type, $values.account_num )

    If the output multiplier expression evaluates to _NULL, an empty list of values or an empty list of records then a single output record will be produced with _type set to _NULL, _NULL or an empty record respectively.

    See Expressions and PhixScripts.

    Select Records to Output

    (Formerly Output Filter)

    You can select a subset of records to output to the next modelling object. You select records using the data in any attribute that is in the table output. Enter an expression that should evaluate to true or false (equivalently 1 or 0).  

    When the expression evaluates to true or 1, the records found by the expression are selected and output to the next modelling object.

    For example:

    • A table containing data about accounts has an attribute called "active".
    • You only want to include "active" accounts in the following tables. 
    • Active accounts have the value 1.

    To select active accounts, enter the expression  _out.active.
    All records that have the active attribute set to any other value are ignored.

    See Expressions and PhixScripts.

    Actions

    This section has a toolbar with standard buttons, see Menu Options and Toolbar Buttons. The grid contains a list of actions that operate on the table.

    To edit the properties of an action, double-click its name. To add an action to the list, click   Insert excerpt_new_newnopaneltrue. PhixFlow opens the Table-Action properties tab.To remove an action from the table, select the action and click  Insert excerpt_delete_deletenopaneltrue in the toolbar.

    Record-Actions

    This section has a toolbar with standard buttons, see Menu Options and Toolbar Buttons. The grid contains a list of record-actions that can update records in the table. 

    To edit the properties of a record-action, double-click its name. To add a record-action to the list, click   Insert excerpt_new_newnopaneltrue. PhixFlow opens the Record-Action properties tab.To remove a record-action from the table, select the record-action and click  Insert excerpt_delete_deletenopaneltrue in the toolbar.

    Sort Orders

    This section has a toolbar with standard buttons, see Menu Options and Toolbar Buttons. The grid contains a list of the sort orders for this table. 

    To edit the properties of a sort order, double-click its name. To add a sort order to the list, click   Insert excerpt_new_newnopaneltrue. PhixFlow opens the Sort Order properties.To remove a sort order from the table, select the sort order and click  Insert excerpt_delete_deletenopaneltrue in the toolbar.Errors  Anchorerrorerror Insert excerpt_validate_validatenameerrornopaneltrue

    ERDs

    The grid displays a read-only list of the Entity Relationship Diagrams (ERDs) that this table is part of. Double-click an ERD to open it. See Entity Relationship Diagrams.

    Relations

    This section has a toolbar with standard buttons, see Menu Options and Toolbar Buttons. The grid displays a read-only list of the relationships that this table has to attributes in other tables. Double-click a relationship to display it's properties; see Relationship and Entity Relationship Diagrams.

    Filters

    This section has a toolbar with standard buttons, see Menu Options and Toolbar Buttons. The grid contains a list of the filters that can be applied to this table. You can add filters to view based on this table. 

    All filters in this list are available in the default view for this table. For views that you create, in the View properties tab → Filters section, you can add filters. The list of available filters is populated by the filters defined for the table. 

    To edit the properties of a filter, double-click its name. To add a filter to the list, click   Insert excerpt_new_newnopaneltrue. PhixFlow opens the Filter properties tab.To remove a filter from the table, select the filter and click  Insert excerpt_delete_deletenopaneltrue in the toolbar.
    Tip

    In a view grid, the header row has a drop-down list of filters. The drop-down list is populated by the filters that are selected on the view. The filters must first be defined for the table here.

    This section lists all pipes that connect into the table. Double-click a pipe to view more details and make changes. See Pipe.

    This section has a toolbar with  Insert excerpt_detailed_list_detailed_listnopaneltrue and  Insert excerpt_refresh_refreshnopaneltrue buttons; see standard buttons, see Menu Options and Toolbar Buttons. 
    Note

    This list can include pipes that have no input. This occurs if the source table has been deleted or if a model has been moved to a different PhixFlow instance (export/import), leaving behind a referenced table. Any pipes with no input are highlighted in yellow. To resolve pipes with no input you can:

    • recreate the missing table 
    • import the missing table 
    • keep the connection, if it will be restored when the model is moved to a different PhixFlow instance
    • delete the pipe, if it is no longer required.
    Download Limits  Anchordownload_limitsdownload_limits

    The following properties affect the number of data records a user can download from a view, such as a grid, chart or card; see Download CSV or Excel Data.

    FieldDescriptionTable Download LimitEnter the maximum number of records that a user can download from a view on this table. If no value is set, PhixFlow uses the default limit set in System Configuration→ System Tuning → Default Download File Limit.Allow Unlimited Downloads Insert excerpt_check_box_untick_check_box_unticknopaneltrue to restrict all users to the Table Download Limit. Insert excerpt_check_box_tick_check_box_ticknopaneltrue to display a User Groups section where you can specify user groups whose members can download unlimited data from a view on this table. For example, members of the Administrators user group may need to download all data.User Groups

    Available when Allow Unlimited Downloads is ticked.

    Use this section to select the user groups whose members can download an unlimited number of records.

    This section has a toolbar with standard buttons, see Menu Options and Toolbar Buttons, and  Insert excerpt_user_group_user_groupnopaneltrue.

    The grid contains a list of user groups that are exempt from the Table Download Limit. To add a user group to the list:

    Click Insert excerpt_user_group_user_groupnopaneltrue to list all the user groups in the repository.
  • Drag user groups into this list to add them to the role. 
    • To remove user groups, use the Insert excerpt_delete_deletenopaneltrue in the toolbar.Data Retention Settings Anchordata-retaindata-retain

    Insert excerpt
    _Banners
    _Banners
    nameerd
    nopaneltrue

    Overview

    Insert excerpt
    _tableFile
    _tableFile
    nopaneltrue
    s are used to hold the information about uploaded files, such as images, PDFs and CSVs. They can be used for File Display components and when Adding a File Gallery Display.

    Insert excerpt
    _property_tabs
    _property_tabs
    namebasic-h
    nopaneltrue

    Insert excerpt
    _parent
    _parent
    nopaneltrue

    Basic Settings

    FieldDescription
    NameThe name of the file table.
    Enabled
    File Location
    Read OnlySignifies that the file table's attribute cannot be amended.
    DescriptionEnter a description to explain the purpose of this item

    Attributes

    Each attribute has a set of predefined settings, these are:

    FieldDescription
    UID
    Name
    Original File Name
    Mime Type
    Created DTM
    Created By
    File Size
    Path
    Url
    Extension
    Label
    Content Id
    Inline
    Folder Id


    Views

    The grid contains a list of the data views for this table. 

    Sort Orders

    This section has a toolbar with standard buttons, see Menu Options and Toolbar Buttons. The grid contains a list of the sort orders for this table. 

    To edit the properties of a sort order, double-click its name. To add a sort order to the list, click  

    Insert excerpt
    _new
    _new
    nopaneltrue
    . PhixFlow opens the Sort Order properties.

    To remove a sort order from the table, select the sort order and click 

    Insert excerpt
    _delete
    _delete
    nopaneltrue
     in the toolbar.

    Errors 

    Insert excerpt
    _validate
    _validate
    nameerror
    nopaneltrue

    ERDs

    The grid displays a read-only list of the Entity Relationship Diagrams (ERDs) that this table is part of. Double-click an ERD to open it. See Entity Relationship Diagrams.

    Relations

    This section has a toolbar with standard buttons, see Menu Options and Toolbar Buttons. The grid displays a read-only list of the relationships that this table has to attributes in other tables. Double-click a relationship to display it's properties; see Relationship and Entity Relationship Diagrams.

    Filters

    This section has a toolbar with standard buttons, see Menu Options and Toolbar Buttons. The grid contains a list of the filters that can be applied to this table. You can add filters to view based on this table. 

    All filters in this list are available in the default view for this table. For views that you create, in the View properties tab → Filters section, you can add filters. The list of available filters is populated by the filters defined for the table. 

    To edit the properties of a filter, double-click its name. To add a filter to the list, click  

    Insert excerpt
    _new
    _new
    nopaneltrue
    . PhixFlow opens the Filter properties tab.

    To remove a filter from the table, select the filter and click 

    Insert excerpt
    _delete
    _delete
    nopaneltrue
     in the toolbar.

    Tip

    In a view grid, the header row has a drop-down list of filters. The drop-down list is populated by the filters that are selected on the view. The filters must first be defined for the table here.

    This section lists all pipes that connect into the table. Double-click a pipe to view more details and make changes. See Pipe.

    This section has a toolbar with 

    Insert excerpt
    _detailed_list
    _detailed_list
    nopaneltrue
     and 
    Insert excerpt
    _refresh
    _refresh
    nopaneltrue
     buttons; see standard buttons, see Menu Options and Toolbar Buttons. 

    Data Retention Settings

    Use the following options to specify how PhixFlow manages old data when a table-data-delete task runs.

    Warning

    We recommend that you always specify the data retention values for every table you create.
    PhixFlow checks that a table has:

    • either data retention settings (recommended)
    • or Retain All Data selected.

    If no data retention option is set, PhixFlow displays an error in the Errors section, above.

    FieldDescriptionWhat Data is Deleted

    Retain All Data

    Insert excerpt
    _check_box_untick
    _check_box_untick
    nopaneltrue
     to specify the criteria for keeping data in the Keep... properties below  (recommended).

     

    Insert excerpt
    _check_box_tick
    _check_box_tick
    nopaneltrue
     to allow old data to accumulate in PhixFlow.

    Warning

    Tables with Retain All Data ticked never have their data deleted and the data will accumulate. This can lead to performance issues.


    Keep for N Days

    Anchor
    keepForXDays
    keepForXDays
    Enter the number of days to keep data in the table.

    The age of data in a recordset is its 'to' date, relative to the 'to' date of the newest valid recordset in the table .

    When a table-data-delete task runs for a table and:

    • Keep for X Days only is set, PhixFlow deletes table data that is older than N days.
    • Keep for Y Recordsets only is set, PhixFlow keeps the most recent X recordsets and deletes recordsets that exceed X.
    • both Keep for X Days and Keep for Y Recordsets are set, PhixFlow deletes the table data that meets both conditions.
    • neither Keep for N Days nor Keep for X Recordsets are set, PhixFlow keeps table data indefinitely. This can lead to performance issues.
    Keep for X Recordsets

    The number of recordsets to keep in the table.


    Keep Superseded for N Days

    Anchor
    keepSupersededForXDays
    keepSupersededForXDays
    Available when Track Superseded Data is selected.

    Enter the number of days to keep superseded records in the table data. 

    Tip

    Remember to keep superseded records for as long as you want the option to roll back the data.

    For example, when Keep Superseded for N Days is set to 4, you can run roll back on data that was superseded up to 4 days ago.

    A table-data-delete task removes superseded records older than 4 days, so PhixFlow no longer has the data to be able to roll it back.


    In a table where the superseded date is tracked, the table data contains a mixture of active records and records that have been superseded.

    When a table-data-delete task runs for a table and:

    • Keep Superseded for N Days only is set, PhixFlow deletes data records that were superseded more than N days ago.
    • Keep Superseded for X Recordsets only is set, PhixFlow  deletes superseded records from the recordsets that exceed X.
    • both Keep Superseded for N Days and Keep Superseded for X Recordsets are set, PhixFlow deletes the superseded records that meet both conditions.
    • neither Keep Superseded for N Days nor Keep Superseded for X Recordsets are set, PhixFlow keeps superseded records indefinitely. This can lead to performance issues.
    Keep Superseded for X Tablesets

    Available when Track Superseded Data is selected.

    Enter the number of recent recordsets that will retain their superseded data records.


    Apply Delete Filter

    : 

    Insert excerpt
    _check_box_tick
    _check_box_tick
    nopaneltrue
     to open a filter tab where you can set filter conditions; see Filter. PhixFlow applies this filter when it runs a table-data-delete task. Only the records which match the filter are deleted.

    Insert excerpt
    _model_prop
    _model_prop
    nopaneltrue

    Advanced 

    Only change the advanced properties with advice by PhixFlow support.

    FieldDescription
    Advanced PropertiesOnly use this field with advice from PhixFlow support. 
    Database View

    Use this property if your database administrator wants to query table data in the PhixFlow database.

    Enter a string in the format v_xxxx. When the table is published to the database, PhixFlow creates a  view. If the table is deleted, the next system task that runs will clear the associated view from the database.

    By default, this field is blank and no view is created in the database.

    See also Configuring Access to Database Views.

    Audit Manual Changes

    This toggle is available when the Analysis Options → Period is Transactional.

    Insert excerpt
    _toggle_on
    _toggle_on
    nopaneltrue
     toggle on to update and delete initiated by actions (not those carried out by analysis runs) will automatically mark the existing record as superseded and create a new recordset. The new versions of the updated records will be placed in the new recordset. Inserts will simply create a new recordset, and add the inserted record into that recordset.

    When Audit Manual Changes is first set, the attributes UpdateAction, UpdatedByName, UpdatedByID and UpdatedTime will be created if they do not already exist. If you do not require these attributes, delete them. For the UpdatedByName attribute, PhixFlow creates a field of 250 characters. 

    UpdateAction must be set to the type of action, such as INSERT, UPDATE or DELETE. The other attributes will be populated if they exist on the table:

    • UpdatedByName - the name of the user that performed the update, 
    • UpdatedByID - the internal id of the user that performed the update 
    • UpdatedTime - the date and time the update was made
    Storage Options
    Index Scheme 
    Anchor
    index_scheme
    index_scheme
    		Select an option to determine how indexes on the table are organised. 
    • All: indexes on the table are optimised for selecting from all recordsets (non-historied reads).
    • Latest: indexes on the table are optimised for selecting from the latest recordset (i.e. for historied reads).
    • Superseded: indexes on the table are optimised for self-updating tables which have a mostly superseded records.
    • None: no indexes are created on the table.
    • Partitioned: this index scheme is specific to partitioned tables; see Storage Type below. Partitioned tables must use this index scheme, and only partitioned tables can use it.
    Storage TypeSelect an option to specify how data for the table should be stored:
    • Database: Store the data in a regular table within the PhixFlow database. This is the most common option
    • Database (Partitioned): Store the data in a partitioned table within the PhixFlow database. This option provides improved performance for rollback and retaining very large recordsets. The option is only available if "partitioning" is available within your database installation.
    • In Memory: Data for the table will not be written to the database. This option can be used (for example) when you want to aggregate large amounts of unsorted data which can then be written to a stored table.
    Data Generation Options 
    Anchor
    data-gen
    data-gen
    Start DateEnter the date on which this table starts or click
    Insert excerpt
    _calendar
    _calendar
    nopaneltrue
     to select a date. Data will be populated into the table from this date onwards.
    Primary Key Generator

    This option relates to an actionflow with a save node that inserts a new record into a table. Each new record requires a unique value for the record's primary key. Use this option to specify how PhixFlow generates the primary key value.

    • Internal: PhixFlow generates unique primary key values automatically. This is equivalent to setting the attribute expression to _NULL. 
    • Sequence: display the Primary Key Generator Sequence option, in which you can select a sequence of values.
    Primary Key Generator Sequence

    Available when Primary Key Generator is Sequence.

    Select a sequence to assign a new value to the primary key attribute of a new record; see Sequence.

    The new record takes the next primary key value in a sequence. This is equivalent to setting the attribute expression to nextValue("sequenceName".

    In Memory Cache SizeEnter the size of the cache that will be maintained when you are using an in-memory table. See Manage Unsorted Data Using an In-Memory Table.
    Allow Partial Set Processing

    Insert excerpt
    _check_box_untick
    _check_box_untick
    nopaneltrue
     to restrict all users to the Table Download Limit.

    Insert excerpt
    _check_box_tick
    _check_box_tick
    nopaneltrue
     so that, when analysis reaches the end of a buffer block, it submits the candidate set for processing, even if the next buffer block has a different key.

    Prevent Parallel Processing

    Available when Period is Transactional.

    Insert excerpt
    _check_box_tick
    _check_box_tick
    nopaneltrue
     to ensure only a single recordset can be generated at a time even if the table receives several concurrent requests to generate data.

    This can be useful where you want to make sure that two analysis runs do not attempt to update the same records at the same time e.g. as a result of two people selecting the same records in a view and then hitting the same action button at the same time to process those records.

    Run Alone

    Insert excerpt
    _check_box_tick
    _check_box_tick
    nopaneltrue
     so that, whenever the analysis engine needs to generate data for this table, it will first wait for all running tasks to complete before it starts.

    Any additional analysis tasks submitted while this table is waiting to start, or while it is generating data, will wait until this table has completed its analysis before they start.

    Key TolerancesWhen building a candidate set, data can be grouped together using a specific key value, e.g. Account Number. If the key value is a number, enter a key tolerance to identify numbers within the given tolerance as the same key value.
    Default ViewSelect the default view for the table; see Views for details of creating views on tables.
    Last Run Date (Read only)PhixFlow displays the date and time that analysis was last run for this table. This date is taken from the "to date" of the most recent recordset for this table.
    Last Run ByPhixFlow displays the user that last ran analysis on this table.
    Insert excerpt_description_descriptionnopaneltrue Insert excerpt_access_accessnopaneltrueBy default, the permissions to access table data is set in System Configuration →  Allow access to data by default.  You can add specific user groups to restrict access to the table.
    PhixFlow displays the user that last ran analysis on this table.


    Insert excerpt
    _access
    _access
    nopaneltrue

    Insert excerpt
    _audit
    _audit
    nopaneltrue

    Live Search
    spaceKey@self
    additionalnone
    placeholderSearch all help pages
    typepage

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

    Table of Contents
    maxLevel3
    indent12px
    stylenone

       

    Learn More

    Insert excerpt_terms_changing_terms_changingnopaneltrue