Hierarchical joins using tMap

The previous recipe covered the basics of tMap joining, but tMap has another level of joining capability, in that it can join together data in a hierarchical fashion. This simple example shows how easily this can be achieved using tMap.

Getting ready

Open the job jo_cook_ch04_0080_hierarchicaltMapJoin.

How to do it...

  1. Open the tMap component. You will see three input tables.
  2. Select customerId from the customer table and drag it into Expr. key of the customerId in the order table.
  3. You will see that a join link, a purple key symbol has been added to the column.
  4. Change the tMap settings for the order table to Inner Join and All Matches (see previous recipe if you are not sure how to do this)
  5. Now, select orderId from the order table and drag it to orderId in the orderItem table.
  6. Change the tMap settings for the orderItem table to Inner Join and All Matches. Exit tMap and run the job.
  7. You should see a printed table containing denormalized customer/order/orderitem rows.

How it works…

This job works on the hierarchy that exists between customer, order, and order item. A customer has many orders and an order has many order items.

The key for orders is customer, and the key for order items is order. Thus, to get all the order items for a customer, it is necessary to first find the keys for all the orders, and then find all the order items that match the order keys.

As you can see. tMap allows this relationship to be defined easily simply by dragging the relevant parent key to the child structure.

上一章目录下一章