Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

SSAS - How do I handle dimensions with valid duplicates? Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 6:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:04 AM
Points: 12, Visits: 62
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 30

When 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 30

With 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
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:41 PM
Points: 815, Visits: 2,020
- Account 2 +: DatePK 20130901, AccountPK 22, Value 20
- Account 2 +: DatePK 20130901, AccountPK 23, Value 20


Why 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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:04 AM
Points: 12, Visits: 62
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
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:41 PM
Points: 815, Visits: 2,020
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 7:37 PM
Points: 6, Visits: 169
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
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse