slowly-changing-dimensionssas
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?
I've often found errors reported from SSAS indicating a duplicate key value in the dimension. When I've reviewed the source data, the duplicates aren't found. While I can't shed light on why this error is popping up in the dimension, I can offer a solution that bypasses the error and allows the cube to finish deploying and processing.
If you open the dimension object and look at the properties, you'll see a section for ErrorConfiguration. If you change this to "custom", you can tell SSAS what to do when it encounters errors in processing the object. If you want to log the error and allow the processing to continue, use "ReportAndContinue" as the value for properties like KeyDuplicate, KeyNotFound, NullKeyConvertedToUnknown, NullKeyNotAllowed - whatever is appropriate for your design. I set the KeyErrorLimit to 1 and set the KeyErrorLimitAction to StopLogging so your log files don't get overloaded with the same or similar errors.
As I said, this doesn't SOLVE the issue, but it does allow the cube to continue processing and sometimes, I've found, that's enough.
Making SSAS hierarchies on slowly changing dimensions is a bit of a fiddle. You need to make surrogate keys for each historical version at each level of the hierarchy. Then the key has the actual business facing name, which the user selects or reports by.
As an example, imagine worker BloggsJ in Division1, which is in LineOfBusiness1. Now Division1 gets moved to LineOfBusiness2. Logically you have the Division entity with two rows now:
DivisionKey Division LineOfBusinessKey
1 Division1 11
2 Division2 12
3 Division1 12
and
LineOfBusinessKey LineOfBusiness
11 LineOfBusiness1
12 LineOfBusiness2
Now, we have worker BloggsJ, who is assigned to Division 1, which is subsequently moved
WorkerKey WorkerName DivisionKey Division LineOfBusinessKey LineOfBusiness
101 BloggsJ 1 Division1 11 LineOfBusiness1
102 BloggsJ 3 Division1 12 LineOfBusiness2
103 SmithF 2 Division2 12 LineOfBusiness2
In this case the keys remain in a strictly hierachical order:
LineOfBusiness2 (Key=12) has two children: Division2 (Key=2) and Division1 (Key=3). Division1 (Key=3) has one child: BloggsJ (Key=102) and Division2 (Key=2) has one child: SmithF (Key=103).
LineOfBusiness1 (Key=11) has one child: Division1 (Key=1), which has one Child: BloggsJ (Key=101)
Displaying the name in the cube allows you to build a hierarchy that can support drill-down operations. You will also probably want to hide the base attributes for this hierarchy and display another set with just the names, so there is something in the dimension that will produce a clean, unique list of members at each level without opaque, confusing repeated names.
*****Update 05012012 3:22 PM CST
Here is an image of my data example.
Best Answer
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
Step 2 
Is that what you meant