Understanding Join direction and quality
The foundation of join quality is based on ensuring your joins always use the fields containing multiples as the foreign key, with the unique field in your master table as the primary key.
The join quality table includes stats to explain how join quality is assigned to the join you've created. The join quality table includes these stats:
Keys count - number of unique values in the keys used in the join.
Rows count - number of overall rows in the table.
Key quality - Keys count divided by Rows count. We expect to see 100% on the master table on a one-to-many join. This indicates a good quality for the join.
Orphans - number of values from the Keys count that don't have a corresponding value in the other table. This information can help to ensure you've connected the correct fields. Usually, you expect to see close to zero on the join quality table.
Figure 5: Join Quality statistics showing 100% for Key Quality and zero Orphans.
Joins Quality and business value
Checks the key and counts the records to ensure the relationship is one-to-many or many-to-one, as appropriate for the data.
We also ensure that the relationship for your keys are in the 'right direction.' It helps to reduce the likelihood of displaying incorrect data in your chart.
For example, a 'null' response indicates that your chart may be displaying information that doesn't match the data keys that you have.
Join suggestions displays
Previewing Join Quality suggestions
Mouse over each suggestion to preview the join quality suggestion
Applying join suggestions
If you're happy with all the suggestions, click Apply All.
The user can apply one specific suggestion by clicking on the check icon. The join will be added immediately.
By clicking on "Apply all" the user applies all the selected joins.
Defining and manually applying data joins
Define Data Joins >
Drag and drop > one connection per field.
Creating the joins (keys) between your tables
To add more joins > add from the Data Joins tab
Managing data joins
Number of suggestions
At the top, we always show the number of available suggestions we have for the user to choose from. This is visible even when the box is contracted.
Number of selected joins
All suggestions are preselected and are shown under the number of suggested joins. This is visible even when the box is contracted.
Dismiss a suggestion
The user can dismiss a specific suggestion by clicking the "X" icon. That join won't be included if the user clicks the "Apply all" button.
View full field/table name
In situations in which the field/table name is too long, we will display the full name in a tooltip. The maximum width of the tooltip is 230px **how many characters is that?**.
Hover
When hovering over an individual suggested join, the user should be able to identify that join in the table view (dotted spaghetti).
Expand / contract list
The user should always be able to contract and expand the suggestions list again by clicking on the arrow icon at the top right.
Join direction
To improve understanding and support troubleshooting your data model output, Astrato represents the join direction in your data view with an arrow that always starts from the ‘many’ side to the ‘one’ side.
Our data engine applies the UNION ALL operations by merging the two SELECT statements and then integrating the outcome by integrating a further SELECT statement with a GROUP BY aggregation to remove the duplicates.
Figure 3: Join direction showing the arrow always begins from many to one.
Please note: The engine's decision is uniquely based on the direction of the arrow and not on the data itself. That’s why the direction of the joins is so essential.