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.

Test Approach:

Three OLAP databases were setup on the same relational database as below:

1. AjitLevel - Level based dimension hierarchies in Analysis Services 2005 SP2 (AS2005SP2)

2. AjitPC - Parent Child attribute hierarchy in AS2005SP2

3. AjitPC2000 - Parent Child dimension in AS2000SP4

The relational database was AjitDB in Sql Server 2005 SP2. The FACT table contained just 100 data rows.

The MDX query which just queries the 11 children of a parent were run on all the three OLAP databases and the run time were noted. The results of all the 3 queries were same.

The below MDX query was run:

select descendants([Account].&[110]) on rows,
{[Measures].[MTD]} on columns
from repcube3

Test Results:

OLAP database	Query Runtime	Trace Details
AjitLevel	Instantaneous	37 rows
AjitPC	15 minutes on server desktop, 40 minutes on my old homePC	3.8 million rows. The size of trace file was 870 mb!
AjitPC2000	8 seconds on server desktop	Trace not available for AS2000

Screenshots:

MDX query result of Parent Child attribute dimension (note the time as 38 min 50 seconds)

Profiler Trace on Parent Child cube: (3.8 million rows, 870 mb trace file size)

Level based cube: (3 seconds)

Level based cube trace file: (37 rows)

Parent child dimension cube in Analysis Services 2000: (8 seconds query time, same result)

Files needed to recreate the cubes and database:

CreateTables.sql : Script to create the dimension and fact tables

AjitDBData.rar : Script to populate the data in dimension and fact tables

AjitPC.xmla : Script to create AjitPC cube

AjitLevel.xmla : Script to create AjitLevel cube

AjitPC2000.CAB : Archive of AjitPC2000 cube to be restored in Analysis Services 2000

TestQuery.mdx : Simple MDX query used in testing

AjitPCTrace.rar : zipped trace file of AijtPC cube trace recorded via SQL Server 2005 profiler. Its 3 mb and upon unzipping becomes 870 mb.

AjitLevelTrace.trc : trace file of AjitLevel cube query execution