Spotfire: How to avoid cyclic dependencies between data tables

This article shows various ways to solve a cyclic dependency between data tables in Spotfire

A cyclical dependency is a relation (data table relationship, insert rows, insert columns, etc) between multiple data tables that which are defined based on each other in such a way to create a loop. When attempting to add a relationship between tables in the TIBCO Spotfire installed client which would lead to this cyclical dependency, you will be prompted with a warning "The relation could not be created since it leads to cyclic dependencies between data table." 

An example scenario:
Suppose you have two data sets - Cust1 and Cust2. Both tables have CustomerID and City columns. There are two more tables with only one column each, CustIDFilter (CustomerID column) and CityFilter (City column), and you want to use both of these tables to filter on Cust1 and Cust2 at the same time.

To achieve this, you create a relation between (Cust1.CustomerID and CustIDFilter.CustomerID) and then between (Cust1.City and CityFilter.City). And similarly you try to create a relation between (Cust2.CustomerID and CustIDFilter.CustomerID ) and then between (Cust2.City and CityFilter.City) but as soon as you are attempt to make the relation between (Cust2.City and CityFilter.City), Spotfire will throw the cyclic dependency error as it forms closed loop between the tables:

The above design will cause a cyclic dependency because you are joining CustIDFIlter to Cust1 and Cust2 and also joining CityFilter to Cust1 and Cust2, resulting in the relations: Cust1 > CustIDFilter > Cust2 > CityFilter > Cust1, etc See KB 000022763 as a reference. 

Resolution:

The following options are available to avoid this issue:

Option 1) The easiest option is to merge Cust1 and Cust2 tables to a single table, for example "CustMerged", so that the data relations can be easily applied from the CustIDFilter and CityFilter tables.

Option 2) Create another copy of CityFilter table by inserting it from source again, for example "CityFilterCopy" and then create the data relations between (Cust1.CustomerID and CustIDFilter.CustomerID), (Cust1.City and CityFilter.City), (Cust2.CustomerID and CustIDFilter.CustomerID) and (Cust2.City and CityFilterCopy.City)

Option 3) Merge the CustIDFilter and CityFilter tables to one data table, for example "CustCity" instead of having two separate tables with only one column in each table. If CustIDFilter data table has 70 records and CityFilter has 30 records, the newly merged data table will be the cartesian product of two tables with 2100 records, for each customer ID there will be 30 city records. As seen in the attached Merge.dxp, if there are two filters of City and Customer ID in a Text Area for filtering records in Cust1 and Cust2 table, you can set up a data relation between the data table CustCity and both Cust1 and Cust2 tables and select "Include filtered rows" from CustCity in Cust1 and Cust2 tables from te Filter Panel. See the reference Filtering in Related Data Tables for more details. This way the filters will have unique values of both the CustomerID and City columns.

Warning: This approach will increase the records in CustCity dramatically so only use this option if the tables being joined have a small number of rows.

Option 4) If your data set is small, for example less than 1000 unique values, then you can use CustomerID and City columns as a List Box document property controls in a Text Area instead of using the default Filters. To create the List Box property control see reference Details on Property Control. When creating the property controls, create new document properties, for example called "CustIDProp" and "CityProp", and for "Set property value through" choose "Unique values in column" and select the CustIDFilter.CustomerID and CityFilter.City columns respectively. Then create calculated columns in both Cust1 and Cust2 tables based on those document properties, like:  

Find([CustomerID],"${CustIDProp}")<>0 and Find([City],"${CityProp}")<>0 

and always keep the filter of that calculated column selected to only "True".

Note: To ensure that column filter is always set to "True" and not modified, you can select it to True and then hide the filter from filter panel so no one can change it manually. A more complex but more robust solution would be to trigger an Iron Python script to execute when either of the "CustIDProp" or "CityProp" document properties change value (see reference Details on Script – Act on Property Change) which will set the filter value of the calculated column to only "True" on both the Cust1 and Cust2 data tables (see reference Example IronPython Filtering Scripts). This way you will not require any data table relations.

Note: The List box and Dropdown document property controls can handle only 1000 values and do not have (All) values and search value option like other filters. 

Option 5) Create a custom data function (see reference Details on Register Data Functions) which will set a document property value upon change of filter values for CustIDFilter.CustomerID and CityFilter.City. This document property value change will then trigger Iron Python script to execute which will apply the filters on Cust1 and Cust2 tables. For example: 

1. Optional: Insert filters for CustIDFilter.CustomerID and CityFilter.City in a Text Area if you would like the filters available in a single location

2. Create a data function which takes input from the CustIDFilter.CustomerID and CityFilter.City columns using an expression such as UniqueConcatenate(CustomerID) limit by filtering (see reference Details on Data Function – Select Input) and set the output parameters to document properties, for example "CustIDProp" and "CityProp".

3. If this filter needs to be applied only to specific visualizations, then you can just use custom expression in data limit like: 

Find([CustomerID],"${CustIDProp}")<>0 and Find([City],"${CityProp}")<>0 

If you want to apply the filter across the analysis, then create calculated columns based on those document properties like:        

    Find([CustomerID],"${CustIDProp}")<>0 and Find([City],"${CityProp}")<>0 

and always keep the filter of that calculated column selected to "True". See the Note in Option 4 above for more details. 

References:

KB: 000022763 Warning message: "The relation could not be created since it leads to cyclic dependencies between data table." https://support.tibco.com/s/article/Warning-message-The-relation-could-not-be-created-since-it-leads-to-cyclic-dependencies-between-data-table 

Doc: Filtering in Related Data Tables https://docs.tibco.com/pub/sfire-analyst/7.11.1/doc/html/en-US/TIB_sfire-analyst_UsersGuide/filter/filter_filtering_in_related_data_tables.htm 

Doc: Details on Property Control https://docs.tibco.com/pub/sfire-analyst/7.11.1/doc/html/en-US/TIB_sfire-analyst_UsersGuide/text/text_details_on_property_control.htm 

Doc: Details on Script – Act on Property Change https://docs.tibco.com/pub/sfire-analyst/7.11.1/doc/html/en-US/TIB_sfire-analyst_UsersGuide/other/other_details_on_script_act_on_property_change.htm 

Wiki: Example IronPython Filtering Scripts https://community.tibco.com/wiki/ironpython-scripting-tibco-spotfire#toc-8 

KB: 000020939 How to execute an IronPython script on initial load of an analysis using Data Functions https://support.tibco.com/s/article/Tibco-KnowledgeArticle-Article-44163 

Doc: Details on Register Data Functions https://docs.tibco.com/pub/sfire-analyst/7.11.1/doc/html/en-US/TIB_sfire-analyst_UsersGuide/df/df_details_on_register_data_functions.htm 

Doc: Details on Data Function – Select Input https://docs.tibco.com/pub/sfire-analyst/7.11.1/doc/html/en-US/TIB_sfire-analyst_UsersGuide/df/df_details_on_data_function_select_input.htm

Attachments:  How+to+avoid+cyclic+dependencies+between+data+tables+in+Spotfire.dxp