Building your data model
Use Astrato's smart joins features to connect your tables and build your data model effectively.
Our efficient Joins features aren't just about the ease of drag and drop to create joins but also include the initial Joins suggestions and Astrato's recommended joins when manually connecting tables.
Creating Joins using Joins Suggestions
After selecting your tables in the Data View, no joins are defined. There are three options to create a new join:
Join Suggestions: Accept the join suggestions in the Joins dialog that displays. Our “suggested joins” process is based on “string similarity.” This works well in some cases but not all.
User-Selected Pair: Drag and Drop one table on top of another. Astrato will show a list of options to Join the tables.
Click on your selected option to apply the join.
Manual Drag and Drop- Select a field from one table and drag and drop it on a field in another table.
Astrato will check your join direction and might offer to switch the direction to optimize the join between the tables.
Manual Join: Click on the + sign in the left panel, and use the modal to define the Join. Manual join is needed when the join includes multiple fields.
User-selected pair joins - 16704
Astrato’s data engine displays Joins suggestions based on checks for content pairs. If tables have been added, or table names and contents haven’t been named as well as they should, our Join quality checks now provides more detailed feedback on pairs you’ve selected to use as joins.
Drag one table on top of the other to initiate the detailed search. We search all the fields to offer you joins suggestions. The checks provide feedback with amber or green circles beside the potential joins. You can then choose the suggestion that best matches your needs.
Please note: this feature doesn’t cover situations where you want to use multiple fields to connect tables
Manually pairing joins
Joining multiple fields - 15535
From the Joins page - add the additional join from the list of options.
Error displays - To add multiple fields joins, please use the Joins screen to select them manually.
Currently, when users try to create a complex join (a multiple-field join) in the table display
We show an error indicating that he is going to create a loop.
We do support complex joins in the manual joins creation screen.
The expected behavior is that we’ll show a message reading:
The tables are already connected. If you want to add a field to the join edit the join in the joins screens to go to the joins screens click here
Example - Duplicating tables
It’s only in Astrato, and our engine ensures it only connects once to the source table.
Generic dimension table (date, geography, … but might need the table twice, can use the same source table twice) To reduce the number of loops in the drawing, and reduces ambiguity when there are multiple keys that connect to a table.
Financial dates, quarters etc - you can reference the same table, and filter the dates, you could connect it three times to create calendars linked to Sales, Complaints, and Marketing. And you can adjust filters in a table, as a view, but in the backend it’s only connected to the source table once.
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.