The join feature enables you to join columns from multiple tables into one row.
Joins may be used to standardise codes through lookup, to flatten parent/child relationships into facts, and for many other use cases. They are the most common form of transformation in a data warehouse.
In this tutorial, we will walk through the creation of a table that is created by joining two tables into one.
Load Invoice Table
In this step we load the Invoice table into the data warehouse.
The sample data source Chinook OLTP contains the data for this tutorial.
Take a shortcut to the data source using the Load navigation menu:
Load the metadata for the Invoice table:
Complete the metadata information for the Invoice table:
Edit the columns for the Invoice table:
Set a business key for the table by editing the invoice_id column and settings its role to BK. You should also complete the descriptions and documentation for all columns.
Run the Create and Load scripts for the load_invoice table:
View the table data to be sure it has loaded correctly:
Load Invoice Line
Now we’re going to load the InvoiceLine table from our ChinookOLTP database.
Because the process is the same as loading the Invoice table, we’ll leave out all the screen shots this time. As a reminder, here are the steps you need to follow:
- Open the ChinookOLTP data source
- Load Metadata for the InvoiceLine table
- Edit Columns, and set the invoice_line_id and invoice_id columns as the business key
- Run the Create and Load scripts to move the data into your warehouse
- View the table data to verify that it loaded correctly
Create Stage Sale
If you were writing a Join in SQL, you’d start with one table as the FROM table, usually the most detailed table in the join:
select t1a,t2b FROM t
and then you’d join another table to it:
select t1a,t2b FROM t1 join t2 on t2a = t1a
We’re going to do something similar in Ajilius.
We start by creating a Select/Join table from the most detailed table, in this case load_invoice_line. This is because there are many load_invoice_lines for each load_invoice.
To create the table, select the Create Stage option from the load_invoice_line context menu:
When filling in the Stage table details, choose the Select / Join transform type:
Next, we’re going to join our Invoice table to the Sales Transaction. To do this, select the Join option from the stage_sale context menu:
We haven’t joined any tables yet, so the list of joins will be empty. Press the Add link to join a new table to stage_sale:
First, select the table that you want to join to stage_sale. In this case it will be load_invoice, and press the Next button when you have selected this table:
Finally, we fill in the details of the join.
There are two entries to be made here. The first entry is to select a matching column for each business key column in the table being joined. The second entry is to select the column/s from the joined table that you want to appear in the result, and press the Change button to update the join details.
Now we’ve successfully joined the tables, and added the Invoice Date to the results:
And when we view the scripts generated after this operation, we can see the join implemented in the code: