Home Forums SQL Server 2005 Business Intelligence SSAS - How do I handle dimensions with valid duplicates? RE: SSAS - How do I handle dimensions with valid duplicates?

  • September 19, 2013 at 6:07 am

    #1651469

    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.