Parent Child Attribute performance woes in SQL Server 2005 SP2: A case study

Objective:

To understand why Parent Child attribute perform so bad vis-a-vis level based hierarchies. Also, the parent child attribute in SQL server 2005 SP2 performs much worse than SQL server 2000 SP4. The total number of fact table rows are very small (100 to be precise).

Note: the needed files to recreate the cubes and database are provided at the end of the blog.