Manchmal kommt es vor, dass eine bestimmte Berechnung in MDX nicht genau das berechnet, was man erwartet hätte. Häufig hilft dann aber ein bisschen Spielen mit der Solve_Order und die Berechnung stimmt. Dadurch hängt der Solve_Order ein wenig der Spirit von Magie an. Dem möchten wir in diesem Blogbeitrag entgegenwirken und die Solve_Order etwas entzaubern.
Solve_Order
In diesem Artikel wird die Datenbank waremart2005 aus dem Buch ‚MDX Solutions‘ von Spofford (Verlag Wiley) genutzt. Ausgangspunkt soll ein simples MDX sein, das 4 Zellen zurückliefert (2 Zeilen und 2 Spalten):
Select
{ [Measures].[Dollar Sales], [Measures].[Unit Sales]} on 0,
{ [Time].[Quarter].[Q1, 2005], [Time].[Quarter].[Q2, 2005]} on 1
from Sales
where ( [Customer].MA )
Um sich dem Thema Solve_Order zu nähern, verwenden wir einmal das simple Bild einer Excel – Tabelle. Die Beispiel-Excel-Tabelle enthält also 4 Zellen (2 Spalten und 2 Zeilen) wobei in den Zeilen die Quartale und in den Spalten die Werte Umsatz und Absatz stehen. Diese Zellen beinhalten auch nur die Werte, Berechnungen sind bis jetzt keine vorhanden.
Als nächstes wird der Time-Dimension eine Berechnung (Differenz der beiden Quartale) hinzugefügt:
With
Member [Time].Quarter.[Q1 to Q2 Growth] as
[Time].[Quarter].[Q2, 2005] - [Time].[Quarter].[Q1, 2005]
Select
{ [Measures].[Dollar Sales]
,[Measures].[Unit Sales]} on 0,
{ [Time].[Quarter].[Q1, 2005]
,[Time].[Quarter].[Q2, 2005]
,[Time].Quarter.[Q1 to Q2 Growth]} on 1
from Sales
where ( [Customer].MA )
Durch die Berechnung der Differenz zwischen den Quartalen entstehen 2 neue Zellen. Diese beiden Zellen enthalten aber keine Werte wie die anderen 4 Zellen, sondern Formeln. Soweit funktioniert alles tatsächlich so wie in Excel. Nun soll noch eine berechnete Measure in den Spalten hinzukommen. Die Berechnung ist im Gegensatz zu der Formel in der Time-Dimension keine Summe/Differenz, sondern ein Quotient: [Dollar Sales]/[Unit Sales]. Die neue Measure soll also einen Durchschnittspreis zurückgeben:
With
Member Measures.[Avg Sales Price] as
Measures.[Dollar Sales] / Measures.[Unit Sales]
Member [Time].Quarter.[Q1 to Q2 Growth] as
[Time].[Quarter].[Q2, 2005] - [Time].[Quarter].[Q1, 2005]
Select
{ [Measures].[Dollar Sales]
,[Measures].[Unit Sales]
,[Avg Sales Price]} on 0,
{ [Time].[Quarter].[Q1, 2005]
,[Time].[Quarter].[Q2, 2005]
,[Time].Quarter.[Q1 to Q2 Growth]} on 1
from Sales
where ( [Customer].MA )
Da zu dem Zeitpunkt unsere Excel-Tabelle 3 Zeilen hat (Quartal1, Quartal2 und Differenz) kommen nun 3 neue Zellen zu der Excel-Tabelle hinzu. In den ersten 2 Zeilen ist wiederum alles klar: das sind Zellen mit einer Formel hinterlegt (jetzt sozusagen A1/B1 bzw. A2/B2). Das Dilemma entsteht in der 3. Zeile. Hier müsste (aus der Zeile betrachtet) jetzt die Formel für Differenz verwendet werden (Das Ergebnis wäre dann 25,3358036363636 – 25,0773719606828 = 0,258431675680765). Aus der Spalte betrachtet muss an der Stelle aber der Quotient greifen (7.561,07 / 259 = 29,1933204633205). Es kann aber nur eine Formel in die Zelle geschrieben werden. Die Frage ist: welche? Diese Thematik wird auch ‚formula overlap‘ genannt, weil für bestimmte Zellen mehrere Formeln zur Verfügung stehen.
Um dieses Dilemma aufzulösen, wird nun jeder Formel (d.h. jedem berechneten Element) eine Priorität (‚formula precedence‘) zugeordnet. Die Formel mit der höchsten Priorität wird in die Zelle geschrieben. Diese Priorität wird jedem berechneten Element als Eigenschaft ‚Solve_Order‘ mitgegeben. Wenn keine Solve_Order explizit angegeben ist, hat das berechnete Element die Solve_Order 0.
Es gibt davon aber auch einige Ausnahmen (die meisten gelten im Cube-Scope (Cube-Scope wird weiter unten erläutert)). Z.B.:
- Measures, welche auf einer MeasureExpression beruhen, CustomRollup-Berechnungen und Unäre Operatoren haben die Solve_Order -5119
- Visual totals Berechnungen: -4096
- Berechnete Elemente, welche die Aggregate-Funktion verwenden, habe immer eine geringere Solve_Order als alle anderen Berechnungen
Wenn alle Berechnungen dieselbe Solve_Order haben, wird es dem Server überlassen, welche Formel ‚gewinnt‘. Der Server entscheidet das dann nach der Sortierung der Dimensionen. Diese Sortierung entsteht beim Aufbau der OLAP-Datenbank und kann sich möglicherweise ändern. Bei nicht angegebenen Prioritäten (Solve_Orders) kann das zu schwer nachvollziehbaren Ergebnisänderungen in den Berechnungen führen.
Wir möchten hier darauf hinweisen, dass es bei vielen Berechnungen egal ist, welche Formel genommen wird, da das Ergebnis immer das gleiche sein wird. Z.B. wenn alle Formeln ausschließlich aus Summen oder Differenzen bestehen. In solchen Fällen braucht man sich um die Solve_Order keine Gedanken machen.
Mit Hilfe von Solve_Order kann jetzt also festgelegt werden, welche Formel in der Zelle rechts unten angewendet wird.
- Die Differenz gewinnt:
With Member Measures.[Avg Sales Price] as Measures.[Dollar Sales] / Measures.[Unit Sales] ,SOLVE_Order = 0 Member [Time].Quarter.[Q1 to Q2 Growth] as [Time].[Quarter].[Q2, 2005] - [Time].[Quarter].[Q1, 2005 ,SOLVE_Order = 1 Select { [Measures].[Dollar Sales] ,[Measures].[Unit Sales ,[Avg Sales Price]} on 0, { [Time].[Quarter].[Q1, 2005] ,[Time].[Quarter].[Q2, 2005] ,[Time].Quarter.[Q1 to Q2 Growth]} on 1 from Sales where ( [Customer].MA )
2. Der Quotient gewinnt:
With
Member Measures.[Avg Sales Price] as
Measures.[Dollar Sales] / Measures.[Unit Sales]
,SOLVE_Order = 1
Member [Time].Quarter.[Q1 to Q2 Growth] as
[Time].[Quarter].[Q2, 2005] - [Time].[Quarter].[Q1, 2005]
,SOLVE_Order = 0
Select
{ [Measures].[Dollar Sales]
,[Measures].[Unit Sales]
,[Avg Sales Price]} on 0,
{ [Time].[Quarter].[Q1, 2005]
,[Time].[Quarter].[Q2, 2005]
,[Time].Quarter.[Q1 to Q2 Growth]} on 1
from Sales
where ( [Customer].MA )
Gültigkeitsbereiche von Solve_Orders
Berechnete Elemente können aber nicht nur im With-Teil einer MDX-Abfrage erstellt werden. Mit dem Create Member – Statement können berechnete Elemente auch im MDX-Script des Cubes oder in der Session vor einer Abfrage erstellt werden. Hinzu kommen noch weitere Stellen in den Dimensionen und Cubes, in denen Berechnungen hinterlegt werden können. Dazu gehören zum Beispiel Unäre Operatoren, MeasureExpressions oder CustomRollups.
Ab der Version SQL-Server 2005 werden drei Gültigkeitsbereiche definiert, die jeweils ihr eigenes Solve_Order-Universum bilden. Der erste Bereich ist dabei eine MDX-Abfrage, der Zweite die Session und der Dritte der Cube. In den Cube-Gültigkeitsbereich fallen alle Berechnungen des Cube-Scriptes aber auch die oben genannten weiteren Stellen für Berechnungen in Dimensionen und Cubes. Eine Solve_Order-Nummer ist nur innerhalb des entsprechenden Gültigkeitsbereiches gültig. Sollten Berechnungen aus verschiedenen Scopes sich überlappen, gewinnt die Berechnung des Abfrage-Bereiches vor der Berechnung des Session-Bereiches. Formeln im Cube-Bereich verlieren bei Überlappungen mit Berechnungen anderer Gültigkeitsbereiche immer.
Um diese Regeln nachzuvollziehen, wird im Server-Script eine neue Measure angelegt. Diese beinhaltet denselben Quotienten wie die MDX-Berechnung, um die Unterschiede schnell und deutlich erkennen zu können:
MDX-Script mit Verwendung des neuen Elementes:
With
Member Measures.[Avg Sales Price] as
Measures.[Dollar Sales] / Measures.[Unit Sales]
,SOLVE_Order = 1
Member [Time].Quarter.[Q1 to Q2 Growth] as
[Time].[Quarter].[Q2, 2005] - [Time].[Quarter].[Q1, 2005]
,SOLVE_Order = 0
Select
{ [Measures].[Dollar Sales]
,[Measures].[Unit Sales]
,[Measures].[Avg Sales Price]
,[Measures].[AvgSalesPrice_Cube]} on 0,
{ [Time].[Quarter].[Q1, 2005]
,[Time].[Quarter].[Q2, 2005]
,[Time].Quarter.[Q1 to Q2 Growth]} on 1
from Sales
where ( [Customer].MA )
Obwohl die Solve_Order von AvgSalesPrice_Cube (10) höher ist als die Solve_Order von der Differenz (0), wird die Differenz genommen, da diese im Abfrage-Gültigkeitsbereich ausgeführt wird und damit über die Berechnung des Cube-Gültigkeitsbereiches (AvgSalesPrice_Cube) gewinnt.
Es gibt jedoch die Möglichkeit, eine Berechnung, die in einer Abfrage definiert ist, trotzdem im Gültigkeitsbereichs des Cubes laufen zu lassen. Dafür wird nach der Berechnung ein SCOPE_ISOLATION = CUBE hinzugefügt. Das hat jedoch dann logischerweise wiederum Konsequenzen auf die Solve_Order innerhalb der Abfrage. Da eine so gekennzeichnete Berechnung nun im Cube-Gültigkeitsbereich läuft, gewinnen alle anderen Berechnungen der Abfrage, die weiterhin im Abfrage-Gültigkeitsbereich ausgeführt werden, automatisch. Da spielt die Solve_Order keine Rolle. Das ist im ersten Moment etwas verwirrend. Am besten lässt sich das wohl durch ein Beispiel verdeutlichen. Es soll folgendes MDX untersucht werden:
With
Member Measures.[Avg Sales Price] as
Measures.[Dollar Sales] / Measures.[Unit Sales]
,SOLVE_Order = 1
Member [Time].Quarter.[Q1 to Q2 Growth] as
[Time].[Quarter].[Q2, 2005] - [Time].[Quarter].[Q1, 2005]
,SCOPE_ISOLATION = CUBE
,SOLVE_Order = 2
Select
{ [Measures].[Dollar Sales]
,[Measures].[Unit Sales]
,[Measures].[Avg Sales Price]
,[Measures].[AvgSalesPrice_Cube]} on 0,
{ [Time].[Quarter].[Q1, 2005]
,[Time].[Quarter].[Q2, 2005]
,[Time].Quarter.[Q1 to Q2 Growth]} on 1
from Sales
where ( [Customer].MA )
Auf dem Server existiert immer noch die Berechnung der Measure ‚AvgSalesPrice_Cube‘. Im MDX-Script werden die beiden bereits bekannten Berechnungen erstellt. Diese Mal wird aber die Berechnung der Differenz in den Cube-Gültigkeitsbereich verschoben (SCOPE_ISOLATION = CUBE). Beim Untersuchen der Ergebnisse kann folgendes festgestellt werden:
Bei der Berechnung des [Avg Sales Price] (Spalte 3) in der 3. Zeile gewinnt die Formel des Durchschnittes. Grund dafür ist, dass die Berechnung dieses Durchschnittes im Abfrage-Gültigkeitsbereich stattfindet und deshalb über alle anderen Gültigkeitsbereiche gewinnt. In der Berechnung AvgSalesPrive_Cube (Spalte 4, Zeile 3) gewinnt die Berechnung des Durchschnittes, weil sie die höhere Solve_Order (10) hat und beide konkurrierenden Berechnungen im Cube-Gültigkeitsbereich laufen.
Scopes
Im Server-Script gibt es die Möglichkeit für bestimmte Teil-Cubes Berechnungen festzulegen. Damit ist das Konstrukt gemeint:
Scope(); Berechnung; End Scope;
Ausgedrückt im vereinfachten Modell einer Excel-Tabelle: Die Scope-Anweisung legt für bestimmte Zellen die Formel fest. Scope-Anweisungen gibt es per Definition nur im Server-Script. Deshalb laufen sie auch nur im Cube-Gültigkeitsbereich für Solve_Orders. In einem Scope festgelegte Formeln ‚gewinnen‘ immer gegen andere überlappende Berechnungen im Cube-Gültigkeitsbereich. Das bedeutet, Formeln in Create Member – Statements und Formeln in MDX-Abfragen mit dem Zusatz ‚SCOPE_ISOLATION = CUBE‘ werden überschrieben.
With – Member – Berechnungen OHNE ‚SCOPE_ISOLATION=CUBE‘ haben aber auch weiterhin die höhere Priorität.
Auch das soll in einem kleinen Beispiel verdeutlicht werden. Dazu wird im Server-Script nun noch ein Scope angelegt, der für den Bundesstaat Massachusetts ([Customer].MA) eine spezielle Berechnung anlegt. Diese Berechnung setzt die vorher erzeugte Measure AvgSalesPrice_Cube auf 1.
Die gleiche MDX-Abfrage wie vorher bringt jetzt folgendes Ergebnis:
Geändert hat sich nun die Spalte 4. Da im MDX-Query auf den US Bundesstaat Massachusatts gefiltert wird, wirkt hier die spezielle Berechnung und der Wert für beide Quartale ist eins. Da die Berechnung der Differenz der beiden Quartale immer noch im Cube-Gültigkeitsbereich läuft, wird sie von der Berechnung des Scopes (=1;) überschrieben.
Nun soll noch einmal getestet werden, was passiert, wenn die Berechnung der Differenz nicht mehr im Cube-Gültigkeitsbereich läuft, sondern wieder im Abfrage-Gültigkeitsbereich für Solve_Orders:
With
Member Measures.[Avg Sales Price] as
Measures.[Dollar Sales] / Measures.[Unit Sales]
,SOLVE_Order = 1
Member [Time].Quarter.[Q1 to Q2 Growth] as
[Time].[Quarter].[Q2, 2005] - [Time].[Quarter].[Q1, 2005]
,SOLVE_Order = 2
Select
{ [Measures].[Dollar Sales]
,[Measures].[Unit Sales]
,[Measures].[Avg Sales Price]
,[Measures].[AvgSalesPrice_Cube]} on 0,
{ [Time].[Quarter].[Q1, 2005]
,[Time].[Quarter].[Q2, 2005]
,[Time].Quarter.[Q1 to Q2 Growth]} on 1
from Sales
where ( [Customer].MA )
Wird die Differenz-Berechnung im Abfrage-Gültigkeitsbereich ausgeführt, siegt sie auch in der un-tersten, linken Spalte (1-1=0).
MDX bietet auch noch die Möglichkeit die Berechnung von bestimmten Zellen über Cell-Calculations zu verändern. Auch hier wird die Solve_Order ausgewertet. Da aber DeltaMaster nicht mit Cell-Calculations arbeitet, möchten wir hier auch nicht näher darauf eingehen.
DeltaMaster
In DeltaMaster wird die Solve_Order für jedes berechnete Element (berechnete Analyseelemente, aber auch berechnete Dimensionselemente und Zeitanalyselement) festgelegt. DeltaMaster-Grundeinstellung ist dabei: Solve_Order = 100. Die berechneten Elemente in DeltaMaster werden in den DAS-Dateien gespeichert und somit ist auch die SolveOrder DAS-File-weit gültig. Man muss sich also beim Einstellen der SolveOrder im DeltaMaster mehr Gedanken machen, also nur um die nächste MDX-Abfrage. In einigen Szenarien kann das dazu führen, dass die Solve_Orders ständig angepasst werden müssen. In solchen Fällen empfiehlt es sich, eine Übersicht aller eingestellten Sol-ve_Orders zu führen, um schneller die DAS-Datei-weite Solve-Order-Reihenfolge erkennen zu kön-nen.
Außerdem gibt es in den Optionen die Möglichkeit, alle berechneten Elemente im Cube-Gültigkeitsbereich laufen zu lassen (Extras/Optionen/System-> SCOPE_ISOLATION –Klausel ver-wenden). Diese globale Einstellung ist durchaus sinnvoll, da ein Mischen der Query- und Cube-Gültigkeitsbereiche eine zusätzliche Fehlerquelle darstellt.
Quellen:
MDX-Solutions (Spofford, Wiley)
http://msdn.microsoft.com/en-us/library/ms145539.aspx
http://msdn.microsoft.com/en-us/library/ms144787.aspx
