Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 SSAS - How do I handle dimensions with valid duplicates? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, September 19, 2013 6:07 AM
 Grasshopper Group: General Forum Members Last Login: Wednesday, December 17, 2014 3:45 AM Points: 12, Visits: 72
 Remember the original fact table:- Account 1: DatePK 20130901, AccountPK 1, Value 10- Account 2: DatePK 20130901, AccountPK 2, Value 20- Account 3: DatePK 20130901, AccountPK 3, Value 30When slicing by date, the total for 20130901 would be 60. Now the new fact table in the case I described:- Account 1: DatePK 20130901, AccountPK 1, Value 10- Account 2 +: DatePK 20130901, AccountPK 21, Value 20- Account 2 +: DatePK 20130901, AccountPK 22, Value 20- Account 2 +: DatePK 20130901, AccountPK 23, Value 20- Account 2 -: DatePK 20130901, AccountPK 24, Value -20- Account 3: DatePK 20130901, AccountPK 3, Value 30With this fact table, if we view the cube from the Groups-Accounts dimension, it would be somewhat correct (new names for the Accounts, but that's acceptable as they're intuitive). But if you slice by date, now the total would be 80.I don't see this working if we modify the fact table, because that directly affects the other dimensions.
Post #1496344
 Posted Thursday, September 19, 2013 6:47 AM
 Ten Centuries Group: General Forum Members Last Login: Yesterday @ 1:57 PM Points: 1,193, Visits: 3,283
 - Account 2 +: DatePK 20130901, AccountPK 22, Value 20- Account 2 +: DatePK 20130901, AccountPK 23, Value 20Why doesn't this account have the same PK? This adds an extra 20, which would account for the difference between 60 and 80.
Post #1496364
 Posted Thursday, September 19, 2013 9:45 AM
 Grasshopper Group: General Forum Members Last Login: Wednesday, December 17, 2014 3:45 AM Points: 12, Visits: 72
 Because if you don't use separate keys, only one of the three occurrences of the positive account (21, 22 or 23, the one you choose) in the Groups-Accounts dimension would have a value. Remember each occurrence in the dimension has its own unique key. This is the problem of using the regular relationship.
Post #1496470
 Posted Thursday, September 19, 2013 11:30 AM
 Ten Centuries Group: General Forum Members Last Login: Yesterday @ 1:57 PM Points: 1,193, Visits: 3,283
 Sorry, but I'm not following that at all. As I've said, we might be at the limits of being able to help without actually seeing the item and there could still be something I'm missing. But I have never seen in an SCD1 dimension the same dimension name having multiple surrogate keys. I'm assuming it's an SCD1 as you haven't mentioned anything about the need to provide history. It this is a fact table keying issue, you will need to have the fact table produce its own otherwise meaningless key.
Post #1496512
 Posted Tuesday, October 22, 2013 8:13 PM
 Forum Newbie Group: General Forum Members Last Login: Thursday, November 6, 2014 1:17 PM Points: 6, Visits: 174
 strangely, I think I am having the same issue. situation is with financial GL cube. There are accounts that roll up nicely into their income statement buckets. Then, there is a section called EBITDA, which includes many of those accounts again, or a second time. In order to put these in some sort of hierarchy in SSAS is brutal. I'm still not sure the best way to proceed.
Post #1507446

 Permissions