INF: How to Work with More Than 64,000 Children Per Parent

SQL Server 2000 Books Online specifies that the maximum number of child members a parent can have is 64,000. What does this mean? What can you do if you have a dimension that violates this rule? What if a parent has more than 64,000 children?

CONSIDERATION

An Analysis Services database designer must do something special to support dimensions that have more than 64,000 members to a parent. Before you start implementing a dimension with that many members per parent, consider the user experience associated with drilling down a dimension hierarchy, and stumbling across a branch with more than 64,000 members. Any member that has more than 1,000 children is too much for a human to comprehend. Most OLAP practitioners agree, for usability reasons, that you do not want more than 1,000-3,000 children per parent. Some client tools handle this scenario more gracefully than others.

SOLUTIONS

The first solution is to manage the data on the data warehouse design side. Do not build a dimension with too many children per parent. Figure out a mechanism to group members between the parent and the child in your original design. Here is an example. Imagine that your original design drilled from State to Business Customer. An easy solution is to insert City, or City and Zip, into the dimension. If your dimension is a parent-child dimension, this is the only way you can solve the problem.

If your dimension is a standard dimension (not a parent-child dimension), you can use a new feature in SQL Server 2000 Analysis Services called Member Groups. SQL Server 2000 Books Online does have a section about Member Groups. Analysis Services automatically inserts a directory level for you. To create automatic grouping:

• Identify the level that has too many members per parent.
  
  -and-
  
  
• Introduce an additional level that precedes the level and change the Grouping property to Automatic.

You can even hide the automatic grouping level, although Microsoft does not recommend doing so because of browsing usability. SQL Server Books Online does have a Creating Member Groups topic. The Creating Member Groups topic contains step-by-step information on how to create member groups.

A drawback of the automatic grouping is that it applies throughout the level of the dimension. Perhaps you have a very unbalanced hierarchy, and only one parent (for example the ubiquitous "All Other") has a lot of children. You will see the automatic grouping even for reasonably-sized parts of the hierarchy.

CONCLUSION

The best practice is to solve the problem in the design phase, by inserting a meaningful level to support a graceful drilldown. The automatic Member Groups are there to help you if you just cannot change the dimensional design, or if your dimension grows beyond the cardinality expected during the design phase.