Why is there a problem?

When a dimension is organized by defining a hierarchy, members are divided into those that have children (nonleaf members) and those that haven't (leaf members). 

In Books on Line you can find the following paragraph:

"In Analysis Services, an assumption is usually made about the content of members. Leaf members contain data derived from underlying data sources; nonleaf members contain data derived from aggregations performed on child members."

There are at least three situations in  which this assumption might not be valid. One of these is supported only in parent-child dimensions and the other two require you to customize your cube design. In this article I shall describe these situations and possible solutions.

The three cases

First, there is the case where the nonleaf members aggregate their children but also have their own source data. In a way those members have two values: their source value and their aggregate value calculated by aggregating their source value and those of their children. This case is supported by parent-child dimensions as discussed below. However, there are ways to implement it in regular dimensions too.

A much-used example of this case is a dimension that represents the members of a sales force. Supervisors (nonleaf members) would be responsible for the sales made by their subordinates but  they might make their own sales too.

A different situation arises if the dimension's hierarchy is intended to represent a non-formulaic relationship among its members. In this case, nonleaf members will have values that depend entirely on source data and have no mathematical relationship to the values of their children.

An example would be a structure of financial ratios, in which you define levels according to the way a ratio might be explained by lower-level indicators. If possible you would load the base data for the ratios and create calculated members to avoid this problem. But if you only have access to the ratios themselves, say, through some news service, then you have to load data into the nonleaf indicators some other way.

The third case occurs when there is a difference of grains. This can happen if, for one of the cube's measures, there is a dimension for which there is no source data at the leaf level.

This often happens when you have to compare budget and actual data in the same cube. For a product dimension, actual data would go down all the way to the individual products, while budget data might be generated at the Product line or family level.

1 - Members with data

I will describe two approaches: one appropriate for regular dimensions (star-schema and snowflake) and another for parent-child dimensions.

In the case of parent-child dimensions, Analysis Services explicitly allows nonleaf members to have associated fact-table data. To enable this support, you use the "Members with Data" property that can be accessed through the Advanced tab of the Dimension Properties Pane in Dimension Editor.

The default setting is "Leaf Members Only". Changing it to "Nonleaf Data Visible" creates system-generated child members to contain the associated source data. By default those members will be labeled ([Parent Name] data). However, you can modify this behavior by setting the "Data Member Caption Template" property.

This does not alter the default aggregation process, it simply adds an additional child for each nonleaf members and uses the aggregation function defined for each measure to calculate aggregated values. Since this is what we wanted in this case, the problem is solved.

This situation can also apply to a dimension that, for whatever reason, you cannot define as parent-child.  

In this case, you can manually or automatically (recommended, of course) modify the dimension source table to include a child for each nonleaf member that has associated fact-table data.  This works reasonably well for members one-level removed from the leaf level. If you have source data associated with members that are higher up the hierarchy, you would have to create intermediate members for each level. At that point I would definitely recommend using a parent-child dimension. 

To avoid confusion you have to be careful with the keys and names you assign to the leaf-level members. The settings for the Dimension properties "Member Keys Unique" and "Member Names Unique" might have to be modified as well.

2 - Members that do not aggregate their children

To solve this case is simply a matter of building upon the solutions devised for the "Members with data" scenario.

You have already defined the cells where you will store your data. What you need to do is:
1 - Modify the aggregation process so that the nonleaf level value is taken  only from the associated child and ignores the values of the other children.
2 - Since you will be displaying the value at the nonleaf level it is best to hide the auxiliary members to avoid confusion.

For parent-dimensions this can be accomplished by:

1 - Creating a Custom Rollup Formula like this:

     calculationpassvalue(<dimension name>.currentmember.datamember,0)

If you have more than one dimension in your cube where you are using this technique, the Custom Rollup Formulas should be:

     calculationpassvalue((<dimension 1>.currentmember.datamember,<dimension 2>.currentmember.datamember),0)

These formulas assign to the nonleaf members their system-generated children, using the datamember function. The calculationpassvalue function is used to ensure that the original fact table values are not replaced by aggregations involving other dimensions that are part of your cube.

2 - Setting the "Members with Data" property to "Nonleaf Data Hidden". This setting will hide the system-generated child members, which is just what you wanted. 

For star-schema or snowflake dimensions:

1 - For each non-level containing associated source data (remember I recommend this technique just for the next-to-last level) you should set a Custom Rollup Formula like this: 

strtomember("<Dimension Name>.<Next Level Name>." + <Dimension Name>.currentmember.name)

For example, if you were defining a Customer dimension, with levels [Family Group] and [Family Member] (leaf-level), the CRF for the [Family Group] level would be:

strtomember("[Customer].[Family Member]." + [Customer].currentmember.name)

2 -To hide the auxiliary members set the "Hide Member If " level property  to "Parent's name". This assumes you are using the same name for  the nonleaf members and their associated children.

3 - Different grains

You could try to adapt one of the methods described above to this case, but since there are usually multiple levels and multiple dimensions involved I can not recommend this.

In my opinion, the best approach is to build two separate cubes, one for actual data and the other for budget data. 

Of course you don't want to duplicate the dimensions, since their structures and members are similar and they differ only in the lower levels. Analysis Services allows you to do just that. You define a shared dimension, include it in a cube, and you disable the levels you don't need by using the Disabled property which you can find in the Advanced Properties pane.

Once the two cubes are build you can join them in virtual cube and enable comparative analysis.

While discussing budget allocation lies beyond the scope of this article, it is worth noting that you can use either calculated cells of calculated members to perform allocation in the virtual cube.

Please contact me with your comments and suggestions at : brianaltmann@yahoo.com