Hi All,
I am wondering if anybody can help with the below question/query to do with building Foreign Key Relationships within a database table generated by CloverETL.
I have a dimension table (customer) which has a unique id (customer_id). The customer ID is a surrogate key generated when written to the database and there is 20,000 rows (approx).
When I load the orders table, I need to reference back to the customer via customer_id. To do this I use a merge join to merge orders & Order_Lines together.
I do not have the customer_id when merging the data but do have customer_account_code – which is unique but is a legacy data attribute.
In the customer dimension I have customer_id, customer_account_code: A direct one to one mapping.
My Question is; what would be the simplest, efficient method to resolve customer_id from customer_account_code?
I tried to use DBLookupTable with no success – would anyone have an example? The issue I had was ArrayOutOfBounds on DBLookupTable:153.
Is DBLookupTable the most efficient method as I know it implements caching, however, would you have 20,000 calls to the database if all customer had ordered?
I don’t really want to start writing dbinput<–>temp file and reading it into to a simpleLookup object so Ideally, I require a lookuptable that is stored in memory and is populated by a DBInput Table. I can then interrogate the memory for the customer_id from the mergeJoin Component? Would one exist/simple to develop?
Any help would be greatly appreciated.
Many Regards,
Ian.