Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Cryogeneric
Starting Member
6 Posts |
 Posted - 2012-05-08 : 16:13:35
|
| Good day,I have a fact table (Cases) and dimension table (Dim_Time) with a regular relationship between the two on the Date_Time_ID column. Additionally, two fact more fact tables reference the Date_Time_ID in Cases using a referenced relationship.Slicing the dates in:FactTable2 (case_id)-->Cases (case_id)-->Case (Dim_Time_ID)-->Dim_Time (Dim_Time_ID)Recently, I added a new New_Time_ID to the Cases table and I created a new Time dimension from the Dim_Time table for the relationship...however, when I try create a reference relationship on the two other fact tables, SSAS won't allow me to select the New_Time_ID in the "intermediate dimension attribute". It will only Let me select the oringal Date_Time_ID. This would not be correctIt needs to be the New_Time_ID like this:FactTable2 (case_id)-->Cases (case_id)-->Case (New_Time_ID)-->Dim_Time (Dim_Time_ID)Any ideas?Thanks a million!! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-08 : 16:55:23
|
| why do you need to create a new dimension? shouldnt you be creating a role playing dimension to reuse existing dimension itselfhttp://msdn.microsoft.com/en-us/library/ms174487(v=sql.90).aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Cryogeneric
Starting Member
6 Posts |
Posted - 2012-05-08 : 17:12:37
|
| My terminology must be wrong. Sorry. I did add a Role-Playing Dimension and named it New Time.I can add the fact table measure (the table with the New_Time_ID column) to the new time "role playing" dimension just fine. The problem is that I can't add the referenced relationships for the other two measures. SSAS won't allow me to select the correct "intermediate dimension attribute". The drop down ONLY shows the original Date_Time_ID value. I can't select the New_Time_ID.Does that make sense? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-08 : 20:08:50
|
| ok i think i got you. The issue I feel is because you've only single attribute in your Time dimension on which you're trying to define relationship. I think you should be creating a separate attribute for second relationship in role playing dimension. For that, you might have to use a view or a named query in your DSV to make Date dimension appear twice in your DSV with separate date_time_Id attributes (use different name for second one) which you can use for different relationships------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Cryogeneric
Starting Member
6 Posts |
Posted - 2012-05-08 : 20:55:55
|
| Interesting. That seems like a strange limitation. I'll experiment with adding a second key in the Dim_Time table, but I just ended up adding the New_Time_ID to the two tables and using regular relationships instead of references as I needed to get it working quickly.Thanks for your help. I appreciate it. |
|
|
|
|
|
|
SSAS Referenced Dimension Issue