20 | 01 | 2019

Member vs. Scope

Bei Berechnungen stellt sich immer wieder die Frage wie die Berechnung implementiert werden soll. Je nach Entscheidung ergibt sich dann eine unterschiedliche Performance oder unterschiedliches Handling. Grundlegend Unterscheiden kann man zwischen drei verschiedenen Arten der Berechnung:

  • ein Standard berechnetes Element
  • ein berechnetes Element das per Scope überschrieben wird
  • oder ein physisches Element das per Scope überschrieben wird

Die klassische Anwendung dieser Möglichkeiten kann bei der Berechnung von YTD zum Tragen kommen. Für die Entscheidung für eine der Berechnungsarten sind meistens zwei Kriterien ausschlaggebend, welche sind

  • das Handling (die Handhabung im Client)
  • und die Performance

Unterschiedliche Clients reagieren unterschiedlich auf berechnete Elemente. Zum Beispiel ist es bei Excel 2007 nicht möglich berechnete Elemente (sofern sie in der Pivot Tabelle aktiviert wurden - relevant nur für berechnete Elemente außerhalb der Kennzahlendimension) von der Selektion auszunehmen - sie werden dann immer dargestellt. Auch gibt es in MDX selbst Einschränkungen für berechnete Elemente, sie dürfen nicht

  • in Subqueries enthalten sein
  • in einer Subcube Definition enthalten sein.

Nach der Entscheidung über das Handling fällt meistens nachranging nach dem Handling noch die Frage der Performance. Dazu habe ich wie schon in anderen Beispielen die Adventure Works Datenbank entsprechend aufbereitet.

  • reduzieren auf das Notwendigste (entfernen von Measure Groups, Dimensionen und nicht benötigten Attributen)
  • Erweiterung des Testdatenbestandes
  • Erstellung der Utiltity Dimension
  • Anlage der YTD Berechnungen

Die drei Varianten der YTD Berechnung sehen wie folgt aus:

  • YTD als berechnetes Element
    • create member currentcube.[Utility].[Utility].[create calculated Member] as Aggregate(Ytd(),([Utility].[Utility].[Current]));
  • YTD as berechnetes Hülle mit Scope überschrieben
    • create [Utility].[Utility].[Scope calculated Member];
    • ([Utility].[Utility].[Scope calculated Member])=Aggregate(Ytd(),([Utility].[Utility].[Current]));
  • YTD als physisches Element per Scope überschrieben
    • ([Utility].[Utility].[Scope physical Member])=Aggregate(Ytd(),([Utility].[Utility].[Current]));

Das Ergebnis

Da mit einer einfachen Query die Laufzeitunterschiede praktisch nicht darzustellen sind habe ich eine Dummy Berechnung erstellt, die jeweils den Median aus jedem einzelnen Tageswert berechnet. Somit hat der Server zumindest etwas zu tun, und wir können besser sehen welche Art der Berechnung besser ist.

  YTD als berechnetes Element YTD als berechnetes Element per Scope überschrieben YTD als physisches Element per Scope überschrieben
Kalter Cache 1042ms 1082ms 709ms
Warmer Cache 658ms 659ms 327ms

Auf Basis eines berechneten Elements scheint es keinen Unterschied zu machen ob ein Standard berechnetes Element oder eine leere Hülle per Scope überschrieben. Am schnellsten ist jedoch ein physisches Element das per Scope überschrieben wird - weil da die Aggregationsfunktion von Analysis Services einspringt und sich um die weitere Aggregation nach der Definition im Measure kümmert.

Nochmal der Hinweise, dass dies kein repräsentativer Vergleich war. Jeder sollte das für seine Situation angepasst Testen.

Testumgebung war

  • SQL Server 2008 Developer Edition SP1
  • Dell Latitude E6400

Um diesen Vergleich selbstständig nachvollziehen zu können sind hier alle Daten zum herunterladen.