Search code examples
ssisetlbusiness-intelligence

SSIS transaction from retional table to the fact_buy

Goal:
Fill data in the table Fact_buy.

Problem:

How should I make a data transaction based on the sql code below to the table Fact_buy in SSIS?

The sourcecode below is indirectly that you cannot make a transaction direcly in the table Fact_buy for instance you have the data of store_id that is located in the table Dim_store.

You need to retrieve data of id_dimstore and id_diproduct in order to make transaction to the table Fact_buy

What I believe is that you need to make more complementory by using additional component, sql etc in order to complete the transaction to the Fact_buy.

enter image description here

SELECT store_id, product_id, buy Id, date
FROM   Store, Buy, Product
Solution

  • The component you are looking for is a Look Up found inside a Data Flow Task. This will allow you to look into the Dim_store, match your data to a record in the dim, and return the id_dimstore.

    Your data flow task could possibly look like this:

    1. ADO .Net/OLE DB Source
    2. Look Up for Store (gets store_id)
    3. Look Up for Products (gets product_id)
    4. Derived Column (add date to each row -- could also be done in source)
    5. ADO .Net/OLE DB Destination

    There are alternatives to what I have suggested, however, this is a really simple solution.