to "convert" the source ID to a dimension surrogate key whe you load your
fact table, simply use a view.
select Dim1.Key1, Dim2.Key2, ..., Fact.Sum1...
from Fact
inner join DWDatabase.dbo.Dim1 Dim1
on Dim1.ID = Fact.DimID1
....
use the bulk insert task to use the fast load options.
you can load thousands of rows / sec.
in my case I reach 100 000rows/sec on a small server
doing a row by row loading is the slower option.
SSIS can do a lookup in memory, so there is an advantage:
* the staging database and the DW database can be on 2 different servers
(while a view cause some restriction or performance issues)
* you can identify missing codes during the loading process more easely (the
view will load only matching keys in dimensions using an inner join clause)
[quoted text, click to view] "Ray" <raymondc@symmetrics.net> wrote in message
news:uavdR9kPGHA.3944@tk2msftngp13.phx.gbl...
> We are still fairly new to data-warehousing. We have a fact table that has
> a fk to the dimension table... and the lookup translation is too SLOW...
>
> We are currently using t-sql and stored procedures for our ETL work. We
> open a cursor to read the source (stage table) and do basic insert
> operations into the datamart fact table. That means for each source row,
> we have to translate the source ID to a destination ID.
>
> For example:
> Fact Product Sales has fk to Dimension Product.
> source product Id = 99. datamart destination product Id = 1099.
>
> We have a lookup tables that manages the mapping of source & destination
> for our dimension tables. We call a stored procedure to retrieve the
> destination key from the lookup table during the Fact Table ETL. The
> problem, of course, is that this stored procedure is called once per FK
> column and per record.
>
> Can someone please recommend options to make this faster? Does SSIS have
> in-memory data structures to support this? Or are there other tricks I can
> employ such as indexes, turning off FKs, using 'NOLOCK' and 'ROWCOUNT'
> off, reducing the number of columns retrieved in the cursor, etc. --- I've
> done all of these, but it still takes approximately 2500 rows/minute.
>
> Thank you!
>
> Ray
>
