0

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?

Improve this question
2
  • 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, 2012 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, 2012 at 22:46

2 Answers 2

Reset to default
1

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

Improve this answer
4
  • 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, 2012 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, 2012 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, 2012 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, 2012 at 20:07
-1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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