Measures and Measure Groups - Expert Cube Development with SSAS Multidimensional Models - page 103

A non-aggregatable measure will have, by its very nature, data stored for many
different granularities of a dimension. Rather than storing all of these different
granularities of values in the same fact table (which is something Ralph Kimball would
frown on anyway), we could create multiple fact tables for each granularity of value.
Having built measure groups from these fact tables, we would then be able to join our
dimension to each of them with a regular relationship but at different granularities.
We'd then be in the position of having multiple measures representing the
different granularities of a single, logical measure. What we actually want is
a single non-aggregatable measure, and we can get this by using MDX Script
assignments to combine different granularities. Let's say we have a regular
(non-parent/child) dimension called Employee with three attributes including
Manager , Team Leader , and Sales Person , and a logical non-aggregatable
measure called Sales Quota appearing in three measure groups as three measures
called Sales Amount Quota_Manager , Sales Amount Quota_TeamLead , and Sales
Amount Quota for each of these three granularities. The following screenshot shows
us what a query against this cube would show at this stage:
We can combine the three measures into one, as shown in the following code:
SCOPE([Measures].[Sales Amount Quota]);
SCOPE([Employee].[Salesperson].[All]);
THIS=[Measures].[Sales Amount Quota_TeamLead];
END SCOPE;
SCOPE([Employee].[Team Lead].[All]);
THIS=[Measures].[Sales Amount Quota_Manager];
END SCOPE;
END SCOPE;