Data Members
In Analysis Services, an assumption is usually made about the content of members.
In a parent-child dimension, however, some nonleaf members may also have data derived from underlying data sources, in addition to data aggregated from child members. For these nonleaf members in a parent-child dimension, special system-generated child members can be created that contain the underlying fact table data. Referred to as
Data members are available only to parent-child dimensions, and only if the parent-child dimension allows nonleaf members with data. This feature can be enabled with Dimension Editor by setting the Members with Data property to Nonleaf data visible or Nonleaf data hidden, or with the Decision Support Objects (DSO) library by using the MembersWithData property of a Dimension object with a SubClassType of sbclsParentChild. If the parent-child dimension has data in fact tables associated with nonleaf members and the Members with Data property is set to Leaf members only, a processing error will occur.
Setting the Members with Data property in Dimension Editor to Nonleaf data hidden allows nonleaf members to have data, but this setting overrides the normal aggregation behavior for nonleaf members. This can cause confusion, because the values for nonleaf members are not derived from the aggregation of child members but from underlying fact table data for the nonleaf member.
Setting the Members with Data property in Dimension Editor to Nonleaf data visible also allows nonleaf members to have data, but this setting creates an additional system-generated child member that contains the underlying fact table data for the nonleaf member. This setting does not override the normal aggregation behavior for nonleaf members; the data member is treated as a child member for the purposes of aggregation. Although a custom rollup formula can be used to override this behavior, the
The benefit of this functionality is not readily apparent for most client applications. However, in certain specific situations data members are indeed very beneficial. For example, the following diagram shows a dimension, representing gross sales volume of products, with three levels. The first level shows the gross sales volume for all salespersons. The second level contains the gross sales volume for all sales staff by sales manager, and the third level contains the gross sales volume for all sales staff by salesperson.
In the case of the Sales Manager 1 member, aggregating the values of the Salesperson 1 and Salesperson 2 members would ordinarily derive the value of the member. However, because Sales Manager 1 also can sell products, that member may also contain data derived from the fact table because there may be gross sales associated with Sales Manager 1.
The individual commissions for each sales staff member can vary. For sales managers, two different scales are used to compute commissions for their individual gross sales, as opposed to the total of gross sales generated by their salespersons. In this case, the ability to access the underlying fact table data for nonleaf members becomes important. The MDX DataMember function can be used to retrieve the individual gross sales volume of the Sales Manager 1 member, while a custom rollup expression can be used to exclude the data member from the aggregated value of the Sales Manager 1 member, providing the gross sales volume of the salespersons associated with that member.