Due to the nature of the analytics data source, loops can be found in some join paths which may result in unwanted data duplication.
Consider this basic example, the green path represents the logical join path from the base transaction record to the account record through the transaction line and transaction accounting line records. The red path represents the loop and the possibility of data duplication. Be aware of this when defining filter criteria or when adding fields to the data grid from multiple joined records.
Another thing to consider when joining multiple records is the two types of joined relationships, one-to-many and many-to-many
- One-to-many
It represents a relationship where a single source record can be associated with multiple iterations of a target record. For example, consider the relationship between a base transaction record and each transaction line. For each transaction, there may be many transaction lines.
- Many-to-many
It represents a relationship where there are multiple iterations of the source record associated with multiple iterations of a target record. For example, consider the relationship between contact records and customer records.
In this case, there could be multiple contacts associated with multiple store locations.
When records with one-to-many relationships are joined in a data set, the cardinality of the data from the source record type is duplicated for each instance of the target record type. This duplication can result in inaccurate data aggregations and summaries in your workbooks. This data duplication can be further compounded when working with records with many-to-many relationships.
