Relations

Relations define intra-source connections and enable users to configure lookups and cross-source aggregates.

Creating Relations

To create a Relation, select a Source from the Sources screen, select the Relations tab, and click the "New Relation" button in the top-right corner of the screen. Once the button has been clicked, the create Relation Modal will open. Fill out all the required parameters and all desired optional parameters (more info below) and press the save button to create your new relation. The save button will be disabled until all required parameters are filled in, and a valid expression has been entered.
Self Relations: The user can define a relation between the current source and itself by selecting the current source in the related source modal. Self relations can never be primary.
New Relation Button
 
Create Relation Modal
 

Relation Properties

  • Relation Name: The name of the Relation must be globally unique. 
  • Relation Description: Optional description for the relation between sources.
  • [This] Source: Visual indicator of the source you are currently selected on/creating the relation for.
  • Related Source: Specify the source for which a relationship with the current source is being defined.
  • Cardinality: Shown for both [This] Source and [Related] Source.  Indicates whether the data is unique on each side of the relation.  If unique, the cardinality is 1.  Otherwise, cardinality is Many.
  • Relation Expression: This is a boolean expression written in SQL that "joins" the current Source (denoted by "[This]") to the related Source (denoted by "[Related]"). The Relation will return 0, 1, or many records depending on the result of the expression.
  • Primary Flag: Useful when there is only one valid relation between two sources, or a set of traversals between sources. 
  • Active Flag: Indicates if the Relation is currently available to Rule expressions and can be accessed during data processing.
Across the DataForge platform, a grey (un-clickable) "Save" button indicates there is an error with the parameters. Typically this error is within an expression field. Double check errors and expressions if you are unable to "Save" your work.

Graph View vs Table View

The Relations tab has two main views: the graph view and the table view.
 

Graph View

The graph view allows users to see a visual representation of the current source, all sources related to the current source either directly or through a primary relation chain, and all the relations that connect these sources.
 
Graph View
In the graph view, solid lines represent Primary Relations and dotted lines represent non-primary relations.
 

Table View

The table view allows users to quickly view, filter, search, and sort all relations that involve the current source.
 
Table View

 

Primary Relations

Primary relations are relations that are designated by the user to be the main relation between a pair of sources. The first relation between a pair of sources will be automatically set as the primary relation, this can be changed by toggling the Primary Flag on the create/edit Relation Modal.
 
Primary Flog Toggle circled at the bottom right

Relation Expressions

The relation expression defines the SQL applied to the ON condition of a JOIN statement. See the Relation Example below for further details. Expressions must resolve to a boolean value, and every relation expression must contain an instance of each of the source containers [This] and [Related] . In essence, each row in [This] source (in other words, the current source) is related to every row in the [Related] source in which the relation expression is true. See the Rules page for more details and examples on using Relations in Rule Expressions.
 

Example Relation Expressions

For relating a pair of tables by a foreign key
[This].ProductID = [Related].ProductID
For relating a table by finding a quantity within a range
[This].Subtotal BETWEEN ([Related].Subtotal - 10) AND ([Related].Subtotal + 10)
For relating a table by using an inequality
[This].Subtotal <= [Related].Subtotal

Creating 1:1 or 1:Many Relation Cardinality

When Key Data Refresh Type is used for a source, the Key Column that is identified from the raw schema will be flagged as a unique field.  Any relations built with this field will result in 1:1 or 1:Many cardinality.

For sources with other Data Refresh Types like Full, the fields brought in from the raw schema are not automatically identified as unique.  By default, any relation using a non-unique field in the relation expression will result in Many:1 or Many:Many relations.

To create a 1:1 or 1:Many relation using one of these non-unique fields, a new Rule needs to be created that references the raw attribute directly and the Unique Value toggle should be turned on for the rule.

Example Unique Rule created from raw attribute

When this unique rule is used in the Relation Expression, the cardinality will result in 1:1 or 1:Many.

Sub-Source Relations

Each sub-source has an implicit relation with M:1 cardinality to its parent source. Additional relations can be added to the sub-source similar to working with relations in a regular source.

For full documentation, visit Sub-Sources. 

Updated

Was this article helpful?

0 out of 0 found this helpful