SQL Server Performance

Drillthrough Performance with Dimension Attribute IsAggregatable =false

Discussion in 'SQL Server 2008 Analysis Services' started by yger, Feb 9, 2011.

  1. yger New Member

    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
  2. satya Moderator

    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.
  3. yger New Member

    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
  4. satya Moderator

  5. yger New Member

    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
  6. satya Moderator

    Excellent to hear, apperciate your feedback too ...

Share This Page