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

 

The other day, I was working on a level based dimension hierarchy and the fact table with rather very few records.

I had applied all the dimensional modeling recommendations such as,

1. for each attribute,

  • disable unary operator and customrullupformula if not needed.
  • remove unwanted attributes
  • isaggregatable property = true
  • attributehierarchyenabled = false if not needed
  • attributehierarchyvisible = false if you need to access the attribute but not visible in dimension browser
  • define attribute relationship

2. In cube, all dimension to cube relationship is "Regular"

3. In cube partition, updated all the statistics (which is evident in above screenshot)

Testing process:

1. I took a set of leaf fact table level data set of five rows. In the profiler , the "Query Dimension" went on and on

image

Finally the query aborted and a "fatal error" message came:

Analysis Services had taken entire available RAM for this small query:

image

It seems there is a post SP2 hotfix which sounds like to adddress the above problem. A similar issue was reported in Analyiss Services 2005 forum from where I got the below information.

938077 (http://support.microsoft.com/kb/938077/)
FIX: The client application stops responding, and the Msmdsrv.exe process uses all the available memory after you perform the filtering operation and the browsing operation against an instance of SQL Server 2005 Analysis Services

In fact, there are a bunch of problem addressed in post SP2 hotfix.

Since, these hotfixes are not fully regression tested by Microsoft as yet, it is available through request only here.

Once, I apply the hotfix, would check if the above get resoled. I would update this blog then.

2 comments:

Anonymous said...

Hello Ajit,

We have just launched the Most Helpful Professional Award (MHP) program and would like to invite all contributors in the currently active categories (SQL Server, .NET Development, My SQL and Oracle) to register for this award.

Only the contributors who will compete for the award need register whereas the users who will vote do not need to register.

You can read more about the program at: http://www.mhpaward.com and http://www.mhpaward.com/About.aspx

- sorry I posted it as comment here but I could not find a better way to contact you.

Anonymous said...

Can anyone recommend the top performing Managed Service software 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 performance reporting
[/url] ? What is your best take in cost vs performance among those three? I need a good advice please... Thanks in advance!

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