Parent-Child dimensions is an important feature of Analysis Services. Parent-Child dimension allow flexibility in the dimension modeling for scenarios such as Bill Of Materials, Chart of Accounts, Employee Organization Structure and others. However, this flexibility comes with the cost. Overuse of parent-child can cause performance problems. Additionally, there are some semantic quirks which can cause problems with calculations – such as parent-child not decoding related attributes like the normal dimensions do. So in some cases it is advisable to convert parent-child hierarchy to the regular multi-level hierarchy. This process of conversion is not a trivial one and it includes several steps.

Jon Burchel, Senior Support Escalation Engineer in Microsoft, looked deeper into this problem, and came up with the tool called “Analysis Services Dimension Table Naturalizer” (or PCDimNaturalizer), which automates conversion of parent-child dimensions to regular ones. He put the project on www.codeplex.com – meaning that all the source code is available as well.

Unlike most open source projects (at least on codeplex), this one is extensively documented (I guess Jon, being a support engineer, really understands the value of good documentation).

The PCDimNaturalizer can be used from command line, with UI and it also exposes object model allowing it to be embedded in the .NET application or SSIS package. I am sure this tool will be useful for many Analysis Services practicioners.

PCDimNaturalizer project at codeplex: http://www.codeplex.com/PCDimNaturalize