Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Having the foresight to begin collecting data from our production environment several years ago, I have a table with a following structure-

Part Status Startdate Enddate

I can write a relational join, but how do I then go about including this in my project? What should I do in the DSV and in the Dimension relationships?

share|improve this question
Jeff, I can't remove this since it has an upvoted answer. It would probably add some value if you clarified the question based on your new/current understanding of the issue. If it was based on a misunderstanding, you are probably not the first or last person to have that issue! – JNK Jun 21 '12 at 16:02
In my solution, I currently have a non-changing dimension where I join on the natural key - part number. In the DSV, I link the two tables with a relationship where they are equal. If I want that to be a SCD, I can't specify a complex relationship criteria like part = part and date > startdate and date < enddate. The correct procedure is to use surrogate keys maintained in the ETL process to link the fact table to the appropriate row in the dimension table. When you create the DSV, you simply use the equal relationship like any other dimension. – Jeff Sacksteder Jun 21 '12 at 22:46

in the BIDS, open your project and double click on your DSV file, you can right click any where and add a table and then do the rest of steps like adding a relation

look at images

Step 1 Add Table in DSV Step 2 Add Relation

Is that what you meant

share|improve this answer
But then how do I create a relationship where the date in the fact table is between two values in the dimension table? – Jeff Sacksteder Jun 20 '12 at 19:58
in your time dimension table, keep a record for every day, from as much years as you need, and have two foreign keys in your fact table, a startdate key and enddate key – AmmarR Jun 20 '12 at 20:06
I have that already. My point is, how do you relate the fact saledate to the correct dimension member? If the saledate is 2012-6-21, it needs to relate to the scd dimension where startdate is 6-1-2012 and enddate is 6-30-2012. – Jeff Sacksteder Jun 21 '12 at 13:40
jeff i kept thinking about your issue, but i still don't understand what you want exactly, is it related to Time dimension or how to update an SCD, or something else – AmmarR Jun 21 '12 at 20:07

The proper technique is to relate the records in your relational tables. If done correctly, you do not need to to treat fact-dimension relationships any differently than non-SCD ones.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.