Mapping surrogate keys of level 2 dimensions to fact table in SSIS data flow

Question

Mapping surrogate keys of level 2 dimensions to fact table in SSIS data flow

Stefan K

SSCommitted

Points: 1786
More actions
August 16, 2007 at 6:14 am

#117873

Hi,
I use lookups to map surrogate of level 1 dimensions to my fact tables in SSIS.
But how to handle a level 2 dimension with a ValidFrom and a ValidUntil date field?
I do not use an IsCurrent column, because this could problem with late arriving facts.
- In dts I used an SQL statement like this:
update SA
SET SA.DimProdRef = Dim.RecordID
FROM SAWarenEingang SA, DimProd Dim
where SA.ProduktNumber = Dim.ProduktNumber
and SA.ArtikelkontoBewegungsdatum between Dim.ValidFrom and Dim.ValidUntil
Now in SSIS I want to handle the whole thing in the data flow without using a staging table:
- Using Lookups: I would have to pass the date column for each inside the fact table into the lookup. That does not work.
- Using Execute SQL in the data flow: would be very slow, because the statement will be executed for any line in the dataflow
Any ideas?
Best regards,
Stefan
Kindest Regards,
SK

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

There are a lot of ways to do this, but using a lookup is what you were already thinking, so let's go with that.

Create your lookup as you would normall and jut se the GUI to join by ProduktNumber (you really should spell things better in your database). Select the fields you want to return as you would normally.

Now, click on the advanced tab. Select Enable Memory Restriction and then select Modify the SQL Statement. Add to the end of the SQL statement your additional criteria "MyDateField BETWEEN ? AND ?".

Finally, click that Parameters button below the SQL statement you just modified and map your new parameters.

Stefan K SSCommitted Points: 1786 More actions · Answer 2

Hi Michael,

thanks for your post. I already tried this. It works but the problem is, that it is too slow. For every row in the data flow a seperate SQL statement is sent to the DB Engine.

Best regards,

Stefan

Kindest Regards,

SK

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

To get around that, you are probably going to have to use a script component. With a script component, you can get all of the records into a recordset in memory in the pre-execute, and then find the rows in the recordset rather than sending the query back to the server.

I used to have a sample of this, but I cannot seem to find it. I don't know if you are going to see a huge performance improvement.