Database Reference
In-Depth Information
Non-aggregatable values
In some rare cases, you may encounter non-aggregatable measures; that's to say,
measures whose aggregated values have to be supplied from the data warehouse
and cannot be derived from lower granularity values. It's always worth asking
why measures are non-aggregatable; in many cases, it's the result of some kind of
pre-aggregation or calculation taking place during the ETL phase. While Analysis
Services can handle non-aggregatable measures, it's much better at handling additive
data, so if you can build your cube from the additive data, we recommend you do so,
even if the data volumes end up being much larger.
However, if you do have to work with non-aggregatable measures, one way of
handling them is by using parent/child hierarchies (we'll talk about another approach
later on in this chapter). Every non-leaf member on a parent/child hierarchy has an
extra, system-generated child called a data member, and you can control the visibility
of these data members by setting the MembersWithData property on the parent/child
hierarchy. If your fact table contains values for a non-leaf member on a parent/child
hierarchy, then these values will in fact be assigned to the member's data member; by
default, the real non-leaf member's values will then be aggregated from its children
and its data member. This can be seen in the following screenshot from Excel:
In order to display non-aggregatable values, all you need to do is to use an MDX
Script assignment to make each non-leaf member on your parent/child hierarchy
display just the value of its data member, as in the following code:
SCOPE([Measures].[Sales Amount Quota]);
THIS=[Employee].[Employees].CURRENTMEMBER.DATAMEMBER;
END SCOPE;
 
Search WWH ::




Custom Search