Join Order in SuiteAnalytics Workbook
As specified in the Data Duplication Based on Record Joins topic, data duplication can occur when you join record types with a one-to-many or many-to-many relationship in a dataset. We can also demonstrate this duplication by examining the order in which record types are added to a dataset.
Consider the relationship between the entity and transaction record types: for each transaction record, there can only be one associated entity or customer record. Each customer record however, can be associated with multiple transaction records. This means that summary information such as field counts can vary from dataset to dataset even if they contain the same fields and record types, depending on the order that the record types are joined in a dataset.
For example, assume you want to see a list of customers and their associated transactions. If you create a new dataset beginning with the entity record type, you can then join the transaction record type to view each associated transaction. If you select the entity field to view a count of the total number of distinct customers, it is displayed at the bottom of the Data Grid.
If you create your dataset beginning with the transaction record type and join the entity record type however, notice that the number of distinct customers decreases.
This discrepancy is another implication of the one-to-many relationship between specific record types. To avoid confusion over these discrepancies, make sure you join related record types in the correct order according to the type of datasets and results you want to generate.