Data Duplication Based on Record Joins
The analytics data source contains different record types with predefined relationships to one another. In the SuiteAnalytics Workbook user interface, you can see which record types are related to one another in the Records list on the Dataset Builder.
The record types listed on the Dataset Builder are based on the root record type of the dataset, the features enabled in your account, and the permissions assigned to the role you use to log in to NetSuite. If you do not see a record type that you think you should have access to, contact your NetSuite administrator.
There are two types of relationships that you should be cautious of when you join record types in a dataset: those with a one-to-many relationship with the source record type, and those with a many-to-many relationship with the source record type.
One-to-many relationships between record types are those in which the source record type can be associated to multiple iterations of the target record type. For example, consider the relationship between the transaction and transaction line record types. For each transaction record there can be multiple transaction line records. When records with a one-to-many relationship are joined in a dataset, 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 in your datasets and workbooks.
To better understand the implications of joining record types with a one-to-many relationship, consider the total amount field on the transaction record type.
In a transaction dataset, for each transaction record listed there is a single value for the total amount field.
However, if you join the transaction line record type to the dataset by adding a transaction line field to the Data Grid, notice that the number of values listed for the total amount field is multiplied by the number of associated transaction line records.
The result is that aggregations based on the duplicated source record fields are inaccurate. This includes the summaries displayed at the bottom of the Data Grid, and the totals and grand totals that you set up in any pivot tables based on the dataset.
This data duplication can also occur when you create criteria filters using fields from record types that have a one-to-many relationship, even if you do not explicitly add fields from the joined record type to the Data Grid.
For example, assume you are working on a transaction dataset with only one transaction record that matches your selected criteria.
If you create an additional criteria filter based on the transaction line record type, the results are again multiplied based on the number of associated transaction line records.
In record types with a many-to-many relationship, the same duplication can occur. Many-to-many relationships between record types exist when multiple iterations of the source record type are associated to multiple iterations of the target record type. For example, in a multi-subsidiary account, each account record can be assigned to multiple subsidiaries and each subsidiary record can be associated with multiple accounts. Consequently, the account and subsidiary record types share a many-to-many relationship, and joining these record types in a dataset can cause data duplication.
You should always be cautious when you create joins between record types that have a one-to-many or many-to-many relationship. If you do perform these types of joins, be aware of the impact they will have on your datasets and the aggregations throughout your workbooks.