20 | 01 | 2019

Measures oder Measure Dimension, Rechenregeln oder Stuktur

Ich stehe aktuell in der Umstrukturierungsphase von einem Großprojekt. Bisher haben wir dort Kennzahlen im regulären Sinn verwendet. Jetzt bekommen wir aber immer mehr Kennzahlen mit Ableitung von anderen Kennzahlen in System, sowie auch Vorsysteme die Kennzahlen dynamisch liefert, und wo alle paar Tage auch eine neue Kennzahl dazukommen kann.

Wir stehen aktuell vor der Entscheidung von regulären Kennzahlen auf einen Kennzahlendimension umzustellen. Grundsätzlich habe ich im Measures vs. Dimensionen Beitrag ja schon einmal beläuchtet, das Kennzahlen als Dimension nicht schneller sein muss. Da es mehr Speicherplatz benötigt sogar langsamer sein kann. In diesem Modell haben wir aber auch sehr viele abhängige Kennzahlen, die derzeit als Rechenregeln definiert sind. Diese können zum Beispiel auch über einen Struktur abgebildet werden - zum Beispiel einen Parent Child Dimension für die Deckungsbeitragsrechnung. Dann stellt sich die Frage ob die Parent Child mit physischen Elementen nicht vielleicht doch schneller ist als die Rechenreglen, vorallem wenn man eine längere Kette davon hat.

Nun will ich folgende weitere Fragen klären:

  • Wieviel wird der Cube größer, wenn man von ca. 50 bis 100 Kennzahlen ausgeht die in die Zeilen gedreht werden müssen
  • Wieviel verlängert sich dadurch die Abfragezeit einer einzelnen Kennzal / aller Kennzahlen
  • Ist die Berechnung weitere Kennzahlen über MDX Script schneller als über eine Dimension und Sturkturknoten (sicher nicht),
    • aber ist ein Knoten in einer Parent Child Dimension schneller als eine MDX Berechnung
    • bzw. eine Knoten in einer Parent Child Dimension mit Unären Operatoren schneller als eine MDX Berechnung
  • MDX Script über physische Elemente in einer Parent Child schneller / langsame als berechnete Element in den Measures

Als Ausgangsbasis verwende ich das Measures vs. Dimension Bespiel, nur werden die Kennzahlen auf 50 Stück erweitert.

 

Das Ergebnis

  Measures Measures als Dimension
Datenbankgröße als Backup 195.145.728 bytes 355.975.168 bytes
Partitionsdatendatei Größe 211.936.846 bytes 450.913.614 bytes
Processing Dauer 30.979 ms 114.666 ms

Auch unterschieden sich die Abfragen entsprechend

  Measures Measures als Dimension
Abfrage select {[Measures].[M9]} on columns, {[Dim2].[Id].AllMembers} on rows from [Cube] select {[Dim4].[Id].&[9]} on columns, {[Dim2].[Id].AllMembers} on rows from [Cube]
Laufzeit kalter Cache 12.000 ms 20.736 ms
Laufzeit warmer Cache 4 ms 4 ms
Abfrage (andere Dimension auf den Zeilen) select {[Measures].[M9]} on columns, {[Dim3].[Id].AllMembers} on rows from [Cube] select {[Dim4].[Id].&[9]} on columns, {[Dim3].[Id].AllMembers} on rows from [Cube]
Laufzeit kalter Cache (aber Windows Filesystem Cache) 650 ms 854 ms
Laufzeit warmer Cache 8 ms 8 ms
Abfrage (alle Measures) select {[Measures].Members} on columns, {[Dim1].[Id].AllMembers} on rows from [Cube] select {[Dim4].[Id].[Id].Members} on columns, {[Dim1].[Id].AllMembers} on rows from [Cube]
Laufzeit kalter Cache (aber Windows Filesystem Cache) 590 ms 3.690 ms
Laufzeit warmer Cache 45 ms 36 ms

Danach habe ich jetzt in der einen Datenbank die Dimension auf eine Parent Child Dimension umgestellt, und zwischenknoten definiert. Als ersten Schritt habe ich Knoten angelegt, die jeweils fünf Kennzahlen zu einer Summe zusammengeführt haben. Danach habe ich Konten angelegt, die die ersten zwei Fünfersummen zu einer neuen Summe zusammengefasst habe. Dann habe ich vier neue Knoten angelegt, die jweils die Summe von vorher mit zwei weiteren Fünferknoten zusammenfasst - und zum Schluss noch zwei Knoten mit jeweils einen Fünferknoten. In der einen Datenbank habe ich das per Parent Child Dimension gemacht, in der zweiten Datenbank über berechnete Elemente. Die Struktur in Parent Child sieht danach wie folgt aus:

Struktur der Kennzahlen in der Parent Child Dimension

Zusätzlich musste ich noch die Datentypen ändern, da die Summenwerte nicht korrekt waren - ein Overflow bei allen int Größen und auch bei Currency in den Summen war aufgetreten. Daher habe ich alle Kennzahlen die int waren auf Double geändert, und die eine Kennzahl von Currency auf Double. Auch wenn das wie ich aus Erfahrung kenne andere Probleme bringt. Zum Beispiel wenn man Aus- und Einbuchungen hat, und diese sich aufheben, dann stimmt das nur bei Currency (deshalb verwendete ich es) und bei allen anderen exakten Datentypen, aber bei Double kann da schon mal ein kleiner Wert überbleiben und dann arbeitet die 0 Wert Unterdrückung nicht mehr korrekt. Auf die Datenbankgrößen hatte das keinen nennenswerten Einfluß.

Die diverse Traces zeigen leider sehr deutlich, dass eine Parent Child Dimension in so einfachen Szenarien wie ich sie gestetet habe wesentlich langsamer ist als Kennzahlen und MDX Rechenregeln. Leider ist meine reale Umgebung nicht so einfach gestrickt, daher bin ich mir diesbezüglich noch immer nicht sicher. In dem simplen Fall wie hier kann man nur sagen, sollten Rechenregeln einer Kennzahlendimension als Parent Child nicht gewählt werden. Nicht nur da Abfragen von einem Measure bis für alle Measures in Kennzahlen schneller sind, auch sind die Rechenregln sofort fertig wenn eine Kennzahl einmal abgefragt wurde, während bei jeder Ebene bzw. bei jedem Element im Parent Child Baum neuerlich von der Festplatte gelesen wurde. Das liegt daran, dass der Cache nur verwendet wird, wenn alle darunterliegenden Elemente in einer gemeinsamen Abfrage enthalten waren und sich noch im Cache befinden. Wurde die Werte hingegen einzeln abgefragt kann sie Analysis Services nicht aus dem Cache abfragen. Es scheint da so, als würde maximal ein Cache für die Beantwortung verwendet werden. Da ich aber die Elemente einzeln abgefragt hatte, war kein einzelner gecachter Wert zur Beantwortung der Knotensumme zulässig. Erst mann man alle Kinder des Knotens - oder die ganze Leaf Ebene - abgefragt hatte konnte der Cache zur Beantwortung von Abfragen verwendet werden.

Zusätzlich stellte sich bei mir jetzt noch heraus, dass ich die Parent Child gegenüber Measures nicht direkt verwenden kann, weil meine Sicherheitsanforderungen nicht mehr erfüllbar sind. Daher muss ich per Many To Many eine Virtuelle Parent Child Dimension verwenden, wo ich einzelne Knoten mehrfach verwende - um dann die Security unterschiedlich anwenden zu können. Many to Many fügt dann nochmal einen kleinen Overhead auf die Abfragen, da ja mindestens zwei Abfragen auf die Storage Engine gemacht werden müssen.

Daher kann für mich nur folgende Schlussfolgerung gelten - falls nicht unbedingt erforderlich immer Measures zu verwenden.

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.