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
Source Click Here.
Post a Comment