Lookup transformation using effective dates

Hi,

I need to perform a lookup based on a business key and a date in the source that must be between an effective from and effective to date in the reference table. I've been able to achieve this by updating the SQL caching command on the advanced tab but the performance is very slow. I have 6 lookups of this type in the data flow with a source SQL statement that returns approx 1 million rows and this package takes over 90 minutes to run.

The caching SQL command now looks like this

select * from
(select * from [ReferenceTable]) as refTable
where [refTable].[Key] = ? and ? BETWEEN [refTable].[StartDate] AND [refTable].[EndDate]

and I've set up the parameters so that the business key is the first parameter and the source date is the second.

I have another lookup in the flow that does a straight equality comparison using 2 columns and the Progress tab shows that this lookup is cached (even though I haven't enabled it on the Advanced tab of the transformation editor) but none of the other lookups (using the date) appear to be cached, even though I have enabled them to be.

Can anyone suggest how I can improve the performance?

Thanks.

Hi,

When u use 'caching SQL command', caching can be either partial or none. In the 'none' mode each time it will execute the sql command for input. In the 'partial' mode, it will only cache the previously executed sql command results, so it won't cache any data at the outset.

Other alternative approach is, join ur input with the [reference table] using the key (don't use date). U will give multiple records. Use a conditional split to compare the date with start and end date. The output will be what u want.

|||

Thanks for the tip. Initially, the caching was partial so I would have expected the lookup speed to increase as the process went on ,as more and more of the target reference records were loaded, but this didn't seem to be the case.

I've now changed the package so that it joins directly onto the reference table and the speed has increased dramatically.

Thanks.

Lookup including looking up on null values possible?

In order to insert datekey values in I lookup datekey in the datedimension table. I join on the 'Date' column that contains dates. The datedimension contains one record for 'unknown date' for which the value of the 'Date' column is null.

The behavior that I desire from my lookup transformation is that for input records with a date the corresponding datekey from the datedimension is looked up and for records with date = null, the datekey for 'unknown date' is looked up.

The first part works well but the looking up on null fails, apparently because you can not say null == null. Does anyone know whether there is a setting in the lookup transformation to include null lookups?

Thnx,
HenkThe lookup transform can not do this. You would need to put a derived column in the flow and if the value is NULL then set it to the appropriate 'unknown date' value.

Thanks,|||Thanks Matt.|||In fact it can and it is quite easy! I found out in the documentation:

"A Lookup transformation that has been configured to use partial or no caching will fail if a lookup operation matches columns that contain null values, unless you manually update the SQL statement to include an OR ISNULL(ColumnName) condition. If full precaching is used, the lookup operation succeeds."

|||So by selecting the full precaching option for the lookup, you eliminate the need to modify the SQL with the ISNULL function?|||While this can work as described I would recommend against it and is, therefore, why I didn't mention it. You need to be careful if you do lookups in this way because unless you guarrantee that there is only one such value you will get the first one lookup happens to find with no warning.

Full precaching will not work because the cache is fully charged and doesn't issue the SQL statement again. The reason why partial or no cache works is because the SQL statement is issued if a match isn't found and will return success due to the ISNULL statement as long as there is a NULL in the table.

There are too many ifs and caveats to make this a good solution, IMHO.

Thanks,

Looking up Dates

Hi, I have another problem with my MS SQL Database 7.0. I am connecting to it from Delphi 7.0 with an ADO connection, and I have a database table of the call details that come into the call center. When I try and run the following SQL query, the first column, which is CHAR Size 10, physical Length 11 comes with with garbage, instead of a number. Here is the SQL query:

select * from CallDetail where DATENAME(weekday, InitiatedDateTimeGmt) = 'sunday' and InitiatedDateTimeGmt between '10/10/2003 00:00' and '10/16/2003 00:00'

The interesting part of it is, when I delete the where part of the query, the table comes up fine, with the right values in the first column.

Does anyone have any ideas what could be wrong with it?When I try and run the following SQL query, the first column, which is CHAR Size 10, physical Length 11 comes with with garbage...

what do you mean by that?|||Originally posted by ms_sql_dba
what do you mean by that?

For the first record that came up, in the first column the value was aBN0I7NwVX even though I know that the value is not supposed to be that, because it is a number. When I open up a BDE connection to the database, and browse there in data tab, they all come up fine.|||Originally posted by aimtech
For the first record that came up, in the first column the value was aBN0I7NwVX even though I know that the value is not supposed to be that, because it is a number. When I open up a BDE connection to the database, and browse there in data tab, they all come up fine.

What happens when you do DBCC CHECKDB?|||Originally posted by Brett Kaiser
What happens when you do DBCC CHECKDB?

ummm how do I do that?