CODEWITHSUNDEEP
sqlssisdata-warehousedimensionslookup-tables
user1709091
user1709091

Reputation:

SSIS Lookup multiple dates in my fact table, how to pull this off using one date dimension?

In my SSIS/DW project, I have a DIM.DATE dimension which is linked to my FACT table by a surrogate key as follows:

ALTER TABLE FACT.SALES ADD date_id INT NOT NULL
ALTER TABLE FACT.SALES WITH CHECK ADD CONSTRAINT FK_dim_date FOREIGN KEY (date_id) REFERENCES DIM.DATE(date_id)

This creates a "date_id" in my fact table, now during my SSIS import process I have a date column being passed (shipped_date), I use this to look up the DIM.DATE table and pass in the surrogate key in my dimension.

This works great, but now I need to have a few different date dimensions for invoice date, received date, etc.

I am confused as to how to make use of the existing DIM.DATE to do this?

I could then add more columns into my fact table..

-- add column into fact table
ALTER TABLE FACT.SALES ADD shipped_date_id INT NOT NULL
ALTER TABLE FACT.SALES ADD invoice_date_id INT NOT NULL

-- add foreign key
ALTER TABLE FACT.SALES WITH CHECK ADD CONSTRAINT FK_shipped_date FOREIGN KEY (shipped_date_id) REFERENCES DIM.DATE(date_id)
ALTER TABLE FACT.SALES WITH CHECK ADD CONSTRAINT FK_invoice_date FOREIGN KEY (invoice_date_id) REFERENCES DIM.DATE(date_id)

But when I do my lookup, I can only pass in the "date_id" column.. I am confused how to make this work all together.

Anyone able to clear this up for me?enter image description here

Upvotes: 0

Views: 2402

Answers (1)

Bill Anton
Bill Anton

Reputation: 2970

You have to use multiple LookUp transforms... 1 for each DateKey field in the fact table.

Upvotes: 1

Related Questions