[resolved] tMap needs to support multiple lookups to the same table (alias)

One Star

[resolved] tMap needs to support multiple lookups to the same table (alias)

In a Data Warehouse Star schema scenario it is a common requirement to be able to lookup multiple surrogate keys based on the same physical lookup table. Example: A (main) order record may contain the following columns: OrderDate, ProcessedDate, ShippedDate, etc - all needing separate surrogates from a date dimension in the data pipeline. Therefore the Date Dimension needs to be used as a lookup multiple times. In OLAP terms this is called a role-playing dimension. In database terms this is just an alias.
So, rather than duplicating the Date dimension table multiple times as mutiple tInput lookup components, and using up memory, it would be great to use a single tInput lookup component, and in the tMap, be able to alias that single input lookup multiple times, joined from the desired main input. That makes for a simpler design, and should save valuable memory.
Dave

Accepted Solutions
Employee

Re: [resolved] tMap needs to support multiple lookups to the same table (alias)


tHashOuput and tHashInput components exist but they are not official, they don't take account StoreOnDisk mode, and they don't work well when they are directly connected to a tMap.
Yet, I will speak about this subject at our next meeting.

All Replies
One Star

Re: [resolved] tMap needs to support multiple lookups to the same table (alias)

+1
Thank you for saying out loud !
Employee

Re: [resolved] tMap needs to support multiple lookups to the same table (alias)

Hi,
It's already exist into our tELTMap you can refer multiple INPUT TABLE for the same using Alias.
Best regards;
One Star

Re: [resolved] tMap needs to support multiple lookups to the same table (alias)

Hi,
It's already exist into our tELTMap you can refer multiple INPUT TABLE for the same using Alias.
Best regards;

Any way to include that functionality in the non ELT (regular) tMap?
Dave
One Star

Re: [resolved] tMap needs to support multiple lookups to the same table (alias)

Hi,
It's already exist into our tELTMap you can refer multiple INPUT TABLE for the same using Alias.
Best regards;

Wanted to ask again:
Any way to include that functionality in the non ELT (regular) tMap? We are not using ELT.
Dave
Employee

Re: [resolved] tMap needs to support multiple lookups to the same table (alias)


tHashOuput and tHashInput components exist but they are not official, they don't take account StoreOnDisk mode, and they don't work well when they are directly connected to a tMap.
Yet, I will speak about this subject at our next meeting.
One Star

Re: [resolved] tMap needs to support multiple lookups to the same table (alias)

tHashOuput and tHashInput components exist but they are not official, they don't take account StoreOnDisk mode, and they don't work well when they are directly connected to a tMap.
Yet, I will speak about this subject at our next meeting.
That would be great. It is extremely common to have a date dimension aliased many times for lookups.
One Star

Re: [resolved] tMap needs to support multiple lookups to the same table (alias)

Is there any solution for this? I have to load 3 times the date and hour tables !!!
One Star

Re: [resolved] tMap needs to support multiple lookups to the same table (alias)

Is there any solution for this? I have to load 3 times the date and hour tables !!!

Im tMap, you can create a lookup based on the same table more than once, but, essentially, you are incurring the extra memory for each duplicated lookup table, nit to mention cluttering up the job display . Also, if the solution adds inferred dimension members on the fly, and that dimension is on eof the dups, then I would doubt that the others would get get updated. - not sure if Talend dynamically updates lookup cache as records are added.
I am used to SSIS (I like Talend better), where the same lookup table can be aliased several times. Also, added inferred dimension members are dynamically added to the lookup cache as well.
Hopefully talend will jump on this one.
Dave
One Star

Re: [resolved] tMap needs to support multiple lookups to the same table (alias)

And could you explain to me how can I do that on Tmap component ? Thanks in advance
One Star

Re: [resolved] tMap needs to support multiple lookups to the same table (alias)

And could you explain to me how can I do that on Tmap component ? Thanks in advance

Anyone ?
One Star

Re: [resolved] tMap needs to support multiple lookups to the same table (alias)

Hi,
It's already exist into our tELTMap you can refer multiple INPUT TABLE for the same using Alias.
Best regards;

Cantoine,
This actually already works using a regular tMap, I successfully used the same table multiple times in a single tmap. Since Talend automatically aliases each lookup, it just treats each instance like another table - works great.
This would make a great webinar.
Dave
One Star

Re: [resolved] tMap needs to support multiple lookups to the same table (alias)

Maths Games
and for example, one subject from the list:
interactive Times Tables Games
One Star

Re: [resolved] tMap needs to support multiple lookups to the same table (alias)

I am migrating from datastage to talend. I have a requirement where i am performing 200 reference lookups in 3 lookup stages in a set of 70-70-60 per lookup stage. All the reference lookups for each lookup stage are coming from one source, How should this be implemented in Talend as tmap maps one to one amongst its components.