Lookup in SS|S

[walidwalid]

I have dimension called Dim_Customer:

CustomerUID CustomerID CustomerName
1 1 Joe
2 2 James
3 3 Micheal

and i have Fact called SalesFact:
CustomerUID CustomerID Sales
Null 1 12
Null 2 13
Null 3 23


i used lookup task to get the CustomerUID from Customers dimension and put it into the CustomerUID in the Fact table. Then i used destination to load the data again into the Fact table i found:

CustomerUID CustomerID Sales
Null 1 12
Null 2 13
Null 3 23
1 1 12
2 2 13
3 3 23

I found that the data has been duplicated.

I don not want that i want to insert the updated data only
The same i found in the Derived column task.

how can i solve that problem ?

Thanks
regards

 

[MDXer]

You design performed as expected. If you used a destination and had your fact table be the destination then you will add the rows from your dataflow to you destination table.

If you are trying to update existing records there are 2 possible ways to accomplish this.

1) Use and OLE DB Command to update you existing records. you can have the task call a stored procedure or you can have the update statement in task. This is not the ideal solution as the OLE DB Command task will execute once for every row. Pretty much the same as if you used a cursor to update a table in SQL.

2) Set the destination table to be a staging table where you dump the records from your data flow. Then in your control flow add a sql task which calls a stored procedure that will do a set based update or place the update query into the SQL Task. This method will perform a set based update.

 

[walidwalid]

OK MDXer,
But in my business i have more than 12 lookup for UIDs and more than 40 script update for massaging. I do use the second way of your suggestions but i found that the staging tables reach to more than 50 tables.

I can not imaging that the number of staging tables reach to that number i feel something wrong in my work.

Is that right or what?

Thanks for your reply.

 

[MDXer]

There is no need to output the results of each lookup or transform to a seperate table. Process the data and preform your lookups on a single data flow and output the results to a single Table that mirrors your fact then you can do and update on records where the data you are trying to update doesn't match. It will be faster to update multiple columns on 1 record than it will be to update 1 record multiple times.