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
7 comments:
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
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!
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!
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
[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]
Here are this and some other articles on Parent-Child Dimension:
http://ssas-wiki.com/w/Articles#Parent-Child_Dimension
I'm so glad I found this post because I've been looking for some information.
Extenze Testimonials
Post a Comment