Inequality joins, Asynchronous transformations and Lookups : SSIS

re: Inequality joins, Asynchronous transformations and Lookups : SSIS

Matt Masson — Fri, 14 May 2010 22:51:21 GMT

You do need to be careful about relative dataset sizes when considering using this approach. If you have a very large reference table (your OLE_SRC), and not many change rows (SCR_SRC), you'll end up spending a lot of time waiting for the OLE_SRC to finish reading, even though there are no more matches coming in from SCR_SRC.

re: Inequality joins, Asynchronous transformations and Lookups : SSIS

jamiet — Sat, 15 May 2010 07:16:47 GMT

Matt,

Yes indeed. Others' mileage may vary; as always you must test and measure to see what works best for you.

-Jamie

re: Inequality joins, Asynchronous transformations and Lookups : SSIS

Marco Russo (SQLBI) — Sat, 15 May 2010 16:22:51 GMT

Matt, I agree with you but Jamie is right - each data warehouse (and each dimension) has specific characteristics that doesn't change so much over time - thus, I think that there are many scenarios where Jamie's approach does worth the effort (and the risk, which is calculated if you know what if you're doing!)

Great idea, Jamie!

Marco

re: Inequality joins, Asynchronous transformations and Lookups : SSIS

AlbertoFerrari — Sat, 15 May 2010 17:00:10 GMT

Jamie,

The major drawback of this approach is, IMHO, the fact that the inputs of the merge need to be sorted. If one of the input is the fact table and you have, for example, three SCD lookups, then you will end un paying three in-memory blocking sorts of the fact table, something that is seldom acceptable.

When I face these situations, I normally end up with a script component that caches the full SCD, in a dictionary where the key is the customer code and each element contains the sorted list of <date, id>. It runs at a speed very close to the normal equi-join non blocking lookup, even if it needs some coding, of course.

I wish MS will sometime create an SCD lookup... and I think I'm not the only one (Matt... got the point? :))

re: Inequality joins, Asynchronous transformations and Lookups : SSIS

Nitin — Mon, 17 May 2010 04:47:22 GMT

I've incorporated this suggestion in our test env and it works like a charm.

Thanks for the Tip Jamie.

@nitinsalgar

re: Inequality joins, Asynchronous transformations and Lookups : SSIS

JoshuaDD — Wed, 19 May 2010 13:40:00 GMT

Hi Jamie,

On an unrelated topic, I left you a question in this forum thread.

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/53e3f59d-9442-43f9-99c5-37dd964408fc

It's on an example you posted a couple years ago so I'm sorry if this isn't fresh in your mind. Thanks in advance for any help you can give.

Josh

re: Inequality joins, Asynchronous transformations and Lookups : SSIS

François JEHL — Thu, 29 Jul 2010 12:49:45 GMT

Really great post Jamie. Even if I agree with Matt on the potential risks, it's the best solution I know to handle theta-joins. Even if a specific SCD lookup would be great, I think a Lookup with the ability to write the comparison expression (an IComparer thing I dunno) without losing the cache functionnality would be even better. Anyway thanks for this one.

Don’t turn the dataflow into a cursor [SSIS]

SSIS Junkie — Tue, 31 Aug 2010 21:22:57 GMT

I saw a thread on the SSIS forum today that went something like this: I have the following dataset: AccountNo

re: Inequality joins, Asynchronous transformations and Lookups : SSIS

MartinIsti — Wed, 01 Dec 2010 04:06:34 GMT

What I haven't been able to figure out how to produce the same in SSIS like the following simple T-SQL JOIN statement:

SELECT ...

FROM same_table t1 JOIN same_table t2

ON t1.column1 = t2.column1

AND t1.column2 >= t2.column2

The tables are the same (it's a self join) and e.g. consists of 3 rows. The end result can be more than 3 rows.

My problem is the following:

- I've tried the Lookup task's advanced tab's custom query feature to create inequal JOINs and that's working fine apart from the thing that a Lookup returns with a maximum of 1 row (per match)

- I've tried using the MERGE JOIN but it does NOT allow inequal JOINing conditions.

I think it would be a quite basic requirement against SSIS that it can provide a feature that covers this type of T-SQL JOINing. I hope there is and it's just me who hasn't found it (out) yet. If not... well it should provide a solution for this I think.

If anyone has an idea or just knows that it's already been discussed and solved please let me know, I've been desperately seeking the answer in the last 7 days.

Thanks!

re: Inequality joins, Asynchronous transformations and Lookups : SSIS

jamiet — Wed, 01 Dec 2010 08:34:36 GMT

MartinIsti,

Use the same technique that I used in this blog post. Put the "greater than or equal to" condition into a Conditional Split immediately after the Merge Join.

Regards

Jamie

re: Inequality joins, Asynchronous transformations and Lookups : SSIS

MartinIsti — Mon, 06 Dec 2010 01:50:07 GMT

Thanks Jamie, in the end I managed to solve the issue by modifying sg in the source query (but I purely used SSIS tricks :))

I'd like to have another question (not totally related to here but it's also about JOINs and LOOKUPs and I haven't found useful info on the net about it - or might have used the wrong keywords in the search engine):

I have a fact table with 4 date columns. For each of those I would like to get the surrogate key and putting only those into the final fact table. Is there a way of doing it without 4 lookups (since the same table is used it would negatively affect performance, I presume?) /apart from doing it with T-SQL JOINs in the source task/?

It just does not feel elegant and efficient to use that many lookups. What's your opinion?

Thanks for every idea!

re: Inequality joins, Asynchronous transformations and Lookups : SSIS

jamiet — Mon, 06 Dec 2010 13:59:12 GMT

Martin,

Unfortunately its pretty much unavoidable - if you want to use the Lookup component then you will need four of them. However, if the lookup set is the same for each one then you could alleviate some of the impact on your lookup source by using a Cache Connection (http://consultingblogs.emc.com/jamiethomson/archive/2007/11/16/Katmai_3A00_-SSIS_3A00_-Lookup-component-gets-a-makeover.aspx)

-Jamie

re: Inequality joins, Asynchronous transformations and Lookups : SSIS

MartinIsti — Mon, 06 Dec 2010 21:16:49 GMT

Thanks for the tip Jamie. Though this time I handled it in the source SQL query. I think it's not really elegant (not to mention the effectiveness) to use that many lookups, but it seems we have to live with it.

re: Inequality joins, Asynchronous transformations and Lookups : SSIS

Debo — Tue, 14 Dec 2010 04:14:01 GMT

Hi Jamie,

I appreciate the way you done the video.I am new to ssis and it is quite useful to see the experts.

Please post few more videos on different topics.That will be helpful .

Investigation: Can different combinations of components effect Dataflow performance?

SSIS Junkie — Thu, 13 Jan 2011 10:04:34 GMT

Introduction The Dataflow task is one of the core components (if not the core component) of SQL Server