"Joining" Text Files in a TM1 Turbo Integrator Process

I was faced with an interesting situation yesterday. I needed to load information from a header and a detail table into TM1. So, just do a join in the TI, I said to myself. And then in the middle of the night I realised that just isn’t possible to do a join in a TI when the source data is text or csv files. This blog post deals with how we overcame the problem of how to join text files in a Turbo Integrator process.

If we were using a relational database as the source to load data from multiple tables in a single query into TM1 we would have a query on our TI that includes a join in something like this:

select header.invoice_number, header.customer_id, header.invoice_date, detail.sku, detail.invoice_line, detail.quantity, detail.sales, detail.cost 

from header, detail 

where header.invoice_number = detail.invoice_number

Note this has a join on the invoice_number field. With a text or csv file as the source, we just can’t do joins like this.

Solution to “Creating a Join” to Load from Multiple Text Files

So how did we load load data into TM1 from multiple text files as the source as if we were creating a join? It was a two step process:

1. In the TI reading the header file we created a temporary dimension using invoice number as the element. Then we assigned attributes for customer ID and invoice date.
2. Then in the TI that reads the detail table we simply read each record, getting the invoice number, invoice line, quantity, sales and cost and then use ATTRS to look up our temporary dimension to get the customer ID and invoice date for the current record’s invoice number. Then, we deleted the temporary dimension created in the first step.

Snappo! We were able to join text files via a Turbo Integrator process.