Manchmal, aus welch guten oder schlechten Gründen auch immer, muss man in berechneten Measures eines SSAS 2005 Cubes mehrere Zustände eines Members unterschiedlich behandeln. Dies erreicht man durch die Verwendung von IIF Statements, oder, ein wenig bequemer, durch die Verwendung von CASE WHEN Statements.
Das CASE WHEN Statement wird in zwei Typen unterteilt:
A) ”Simple” CASE:
CASE [MyValue]
WHEN 100 THEN „Small”
WHEN 1000 THEN ”Middle”
WHEN 10000 THEN ”Large”
ELSE ”Micro” END
B) ”Searched” CASE:
CASE
WHEN [MyValue] = 100 THEN „Small”
WHEN [MyValue] = 1000 THEN ”Middle”
WHEN [MyValue] = 10000 THEN ”Large”
ELSE ”Micro”
END
Wie Chris Webb in seinem BI-Blog mitteilt, ist die Verwendung von Simple CASE performanter als Searched CASE, allerdings kann in der SQL Server Version 2005 die Verwendung von Simple CASE unter gewissen Umständen fehlerhafte Ergebnisse im ELSE Zweig erzeugen.
Voraussetzung dafür ist, dass in irgendeinem der Zweige ein anderes, berechnetes Measure benutzt wird, welches wiederum aus einem Tupel mit mindestens einer Dimensionsreferenz bestehen muss. Bsp:
CREATE MEMBER CURRENTCUBE.[Measures].TestCalc AS
([Date].[Calendar Year].&[2003],[Measures].[Sales Amount]),
Visible=1,FORMAT_STRING=„#,###0.00“;
Folgend die Definition eines berechneten Elements mit einer SIMPLE CASE Entscheidung auf Basis der AdventureWorks DW 2005 OLAP Datenbank:
(Ich bitte den Sinn der angeführten Berechnungen in den Hintergrund zu stellen 😉
CREATE MEMBER CURRENTCUBE.[Measures].SimpleCase AS
CASE [Date].[Calendar Year].CURRENTMEMBER
WHEN [Date].[Calendar Year].&[2002] THEN
([Product].[Product Line].&[S],[Measures].TestCalc)
+ ([Product].[Product Line].&[],[Measures].TestCalc)
WHEN [Date].[Calendar Year].&[2003] THEN
([Product].[Product Line].&[],[Measures].[Sales Amount])
+ ([Product].[Product Line].&[S],[Measures].[Sales Amount])
ELSE — „Any other year – Product line MOUNTAIN
([Product].[Product Line].&[M],[Measures].[Sales Amount])
END,
Visible=1,FORMAT_STRING=„#,###0.00“;
Das Measure summiert die Produktlinien „Accessorys” und „Components” in den Pfaden 2002 und 2003, benutzt im Pfad für das Jahr 2002 das Measure „TestCalc” und zeigt im ELSE Zweig den Sales Amount der Produktinie „Mountain” an.
Ein berechnetes Element mit Verwendung der Searched CASE Variante würde dann wie folgt definiert werden müssen:
CREATE MEMBER CURRENTCUBE.[Measures].SearchedCase AS
CASE
WHEN [Date].[Calendar Year].CURRENTMEMBER
IS [Date].[Calendar Year].&[2002] THEN
([Product].[Product Line].&[S],[Measures].TestCalc)
+ ([Product].[Product Line].&[],[Measures].TestCalc)
WHEN [Date].[Calendar Year].CURRENTMEMBER
IS [Date].[Calendar Year].&[2003] THEN
([Product].[Product Line].&[S],[Measures].[Sales Amount])
+ ([Product].[Product Line].&[],[Measures].[Sales Amount])
ELSE — „Any other year – Product line MOUNTAIN
([Product].[Product Line].&[M],[Measures].[Sales Amount])
END,
Visible=1,FORMAT_STRING=„#,###0.00“;
Das Ergebnis ist verblüffend, zieht man beide berechneten Member in die Abfrage eines Cubebrowsers:
Zeilen : Dimension Promotion, Attribut Promotion
Filter : Date.Calendar Year = 2004; Product.Product Line = Mountain
Wie man sieht, wird die Gesamtsumme in beiden Membern korrekt angezeigt, im Simple CASE Measure werden jedoch die Ergebnisse für die unteren beiden Zeilen NICHT angezeigt. Das Phänomen / Bug tritt NUR im SQL Server 2005 auf und NUR bei Verwendung eines berechneten Measures mit o.g. Bedingungen in irgendeinem der CASE Zweige.
Gefährlich bleibt es trotzdem, kontrolliert man nämlich nur die Gesamtsummen (was der eine oder andere Developer aus Zeitgründen sicher schon mal getan hat), fällt der Fehler eben NICHT auf.
Deswegen:
Checkt Eure CASE WHEN Member! Vor allem darauf ob Ihr Euch die Verwendung nicht ganz sparen wollt/könnt 😉