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)

AjitPCResult

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

AjitPCTrace

Level based cube: (3 seconds)

AjitLevelResult

Level based cube trace file: (37 rows)

AjitLevelTrace

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

AjitPCResultAS2000

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

7 comments:

Anonymous said...

Pingback.
Link to this article was added on our website in the [Articles]/[Parent-child] section:
http://www.ssas-info.com/analysis-services-articles/52-parent-child/819-parent-child-attribute-performance-woes-in-sql-server-2005-sp2-a-case-study

Anonymous said...

Who knows where to download XRumer 5.0 Palladium?
Help, please. All recommend this program to effectively advertise on the Internet, this is the best program!

Anonymous said...

Can anyone recommend the best MSP program for a small IT service company like mine? Does anyone use Kaseya.com or GFI.com? How do they compare to these guys I found recently: [url=http://www.n-able.com] N-able N-central remote desktop
[/url] ? What is your best take in cost vs performance among those three? I need a good advice please... Thanks in advance!

Anonymous said...

Erectile Dysfunction online pharmacy guide helps you find the right place to buy meds such as cialis, levitra and overall safe viagra online or generic.

Cialis


Cialis


Cialis


Cialis


Cialis


Cialis


Cialis


Cialis


Cialis


Cialis is a ed medicine that is used to treat erectile dysfunction .Cialis is a magic erectyle dysfunction medication with perfect benefits.


Cialis


Cialis


Cialis


Cialis


The best offers among online pharmacies. The most reputable online drugstore. Buy Cialis online.


Cialis


Cialis


Cialis


Cialis


All about Cialis. View complete and up to date Cialis information - part of the Drugs.com trusted medication database.
Cialis


Cialis


Cialis


Cialis


Cialis


Generic Cialis Cheap . FDA approved Health product. Cheapest prices ever!
Cialis


Cialis


Cialis


Cialis


Cialis


Cialis


Cialis


Generic CialisGeneric Cialis SoftGeneric LevitraTrial PacksGeneric ViagraGeneric. Generic Cialis $1.61 $1.94, Generic Levitra $1.67. Generic Kamagra.
Cialis

Anonymous said...

[url=http://akreoplastoes.net/][img]http://akreoplastoes.net/img-add/euro2.jpg[/img][/url]
[b]cheap software for schools, [url=http://akreoplastoes.net/]sales purchase software[/url]
[url=http://akreoplastoes.net/][/url] autocad trial version 7 Mac Poser 7
download nero 6 0 for free [url=http://rastimores.net/]adobe creative suite 4 production premium full download[/url] coreldraw for mac
[url=http://rastimores.net/]convert images acdsee[/url] buy windows mobile software
[url=http://akreoplastoes.net/]educational software[/url] computer software discounts
fisher price software downloads [url=http://rastimores.net/]Software Corel[/url][/b]

Sam Kane said...

Here are this and some other articles on Parent-Child Dimension:

http://ssas-wiki.com/w/Articles#Parent-Child_Dimension

Frank said...

I'm so glad I found this post because I've been looking for some information.
Extenze Testimonials

My Articles

Design

Cube structure optimization for MDX query performance in Analysis Services 2005 SP2: Tips for Parent Child Hierarchies usage

Fact table design for “State Workflow Analysis”: Analysis Services Dimensional modeling

Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip

Identifying intra-dimensional members relationship and reducing cube sparsity in Analysis Services 2005 SP2 : Cube design tip

Leaves() : An example to understand it for both regular hierarchies as well as parent child hierarchies

Aggregation design: useful tips

Level based attribute hierarchy: MDX query performance woes in SQL Server 2005 SP2: Is it fixed in post SP2 hotfix?

Parent child hierarchy to level base hierarchy conversion: hiding placeholder dimension members in client application

Trouble / Troubleshooting

Aggregate(), Sum() functions using calculated members does not work in Analysis Services 2005 SP2 (9.00.3042.00 version) but works in Analysis Services 2000 SP4

Analysis Services 2005 migration tool: Custom member formula issues in migrated database

Cube Partitions: Fact table not listing in Business Intelligence Development Studio in partition wizard

Analysis Services 2005: Many-to-Many relationship does not support unary operators with parent-child dimension

MDX

NextAnalytics and MDX : Part 1 - Swap Cells with Row Labels

Selecting dimension's default member based on a member property

Sorting members on member codes / member properties

Time Dimension: How to set Default Member to Current Month

Setting dynamic default member in dimension X based on the current member of dimensions Y

ADOMD.NET

Code : utility code for converting cellset to a data table

Others

Google specialized search for Analysis Services and MDX web resources integrated in my blog

Art of reading MDX articles

MDX Expression Builder : Need for a tool making it easier for functional users to write MDX expressions, queries.

Blogroll