SSAS Aggregation on Distinct ID

Question

I wish to change the default aggregation from SUM to SUM on Distinct ID Values. This is the current behaviour

     ID    Amount
      1      $10
      1      $10
      2      $20
      3      $30
      3      $30

Sum Total = $90

By default, I am getting a sum of $90. I wish to do the sum on distinct ids and get a value of $60. How would I modify the default Aggregation Behavior to achieve this result?

Answer 1

Design your data as a many-to-many relationship: create one table/view having one record per ID and the amount column from the data shown in your question (the main fact table), and one table/view having one record per record of your data as shown in your question, presumably having another column, as otherwise it would not make any sense to have the data as shown in your question). This will be the m2m dimension table. Then, create a bridge table/view having the id of the m2m dimension table and your ID column.

Then create the following AS objects: A measure group from the main fact table, a dimension on column ID of the same table (in case there is no other column making a dimension table meaningful, in that case, you would better have a separate dimension table having ID as the primary key). Create a dimension from the m2m dimension table, and a measure group having only the invisible measure "count" from the bridge table. Finally, on the "Dimension Usage" tab of Cube Designer, set the relationship between the m2m dimension and the main measure group to be many to many via the bridge measure group.

See http://technet.microsoft.com/en-us/library/ms170463.aspx for a tutorial on many-to-many relationships.