Non-Additive Fact

Question

I have a Fact Table with 10 measures. out of 10 measures 9 are aggregated at Sum level.But I have Unit Price which is a Non-Additive Fact Measure.When I go to the property of Aggregation and make it none it does not work in SSAS and gives NULL values in the Cube once the cube is processed. So is there any way where I can deal with my Non-additive measure called Unit_Price.SO created a MDX. I don't know if this is the right method to do that or not. Here is my MDX.Please suggest a method to do the above.

with member [Measures].[Non Additive Unit Sales] as
  case when [Time].Level is [Time].[Month]
     and [Gender].Level is [Gender].[Gender]
     and [Customers].Level is [Customers].[Name]
     and [Product].Level is [Product].[Product Name]
  then [Measures].[Unit Sales]
  else null
  endselect
   {[Measures].[Unit Sales],
     [Measures].[Non Additive Unit Sales],
     [Measures].[Semi Additive Unit Sales]}
   * [Gender].Members ON COLUMNS,
  [Time].Members ON ROWS
from [Sales]

ConcernedOfTunbridgeWells ConcernedOfTunbridgeWells · Accepted Answer · 2011-12-21T01:38:15

It's not the right method. If a measure is set to 'Do Not Aggregate' it will not be shown at any cardinality where it would have to be aggregated.

Essentually, unit cost is an attribute of your Product, not a measure as such. There are a couple of ways to deal with it:

Set it up as a member property of the product. You can query this through MDX, and some query tools (e.g. ProClarity) will show member properties as a tooltip.
Make another fact table with the unit cost. Have one row per product, and don't connect the measure group to any dimension but product. You can set this to 'Do Not Aggregate' and the measure will be blank when you are not drilled down to product.

Non-Additive Fact

1 Answers