Drillthrough Performance with Dimension Attribute IsAggregatable =false | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Drillthrough Performance with Dimension Attribute IsAggregatable =false

Environment: SQL2008R2 on Windows 2008 R2In our Testexample we have a cube with 3 Dimensions. 1 of them with about 4 mil rows. This is just for Drillthrough. The Fact Table has about 1.7 mil rows. Our dimension which causes the problem looks like this:Name: ZeitZeit_Jahr_Idx IsAggregatable=falseZeit_Tag_Idx(PK) IsAggregatable=trueThe Fact-Table has the Data on the Zeit_Jahr_Idx-Level.When I run a Drillthrough, which returns only Attributes of my Drillthrough Dimension, it takes about 1 minute and 40 seconds.If I change IsAggregatable=true on my Zeit_Jahr_Idx the query takes 1 second!!!!The Drillthrough looks like this:drillthrough select [Berichtsperiode].[Berichtsmonat].&[12] on 0 –Month from [VIS] where [Zeit].[Jahre].[Jahr].&[21]–Year return [$Bearbeitungen Detail].[VIS Bearbeitungen Detail Key Idx] as [Detail], [$Bearbeitungen Detail].[Branche] as[Branche]Can anyone explain why?? Thanx
Welcome to the forums.
It depends on the dimensions and condition value, as it is default to measure for the cube each dimension will have a default member used. Have you removed the reference for All-element and by design it may not be feasible to set aggregate the different memebrs where IsAggregatable will be FALSE.
SEe http://technet.microsoft.com/en-us/library/ms166717.aspx fyi.

Thanx, It seems that the problem is caused by the combination of IsAggregatable=false and that the Fact-Table is not related to Dimension on the Leaf-Level of the Dimension. If I have only a one-Level Hierarchydimension it works fine. But when I have a Multilevel-HierarchyDimension and the Fact-Table is related on a higher Level I’m getting poor performance
Ok, that explains the problem. Are there any repeatable values such as sequence number to identify the uniqueness of value that will help the performance of multi-level hierarcy issues.
Are there any calculated members in that cube?
See this related blog post on multi-level hierarchy https://community.altiusconsulting….lti-level-hierarchy-dimension-design-tip.aspx and tools to use for performance monitoring http://www.dbsophic.com/tip-multidimensional-analysis.

The installation of "SQL Server 2008 R2 Cumulative Update Package 6" solves this issue. Exactly: This Issue has been resolved by resolving another problem: FIX: The memory usage in SSAS 2008 or SSAS 2008 R2 may be larger than in SSAS 2005 when you create a local cubehttp://support.microsoft.com/kb/2422081
Excellent to hear, apperciate your feedback too …

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |