English
Deutsch
ixto-Blog RSS Feed
SSAS 2005 MDX Tuning – unnötige MEMBER und fast noch schlimmer: STRTOMEMBER
“Das beste MDX ist das, das man nicht schreibt” (über den Buschfunk: Mosha Pasumansky)
Aber wer kommt denn auf die Idee, dass dieses Zitat wortwörtlich zu nehmen ist?
Ich sah mich mit einer Kundendimension von 172.000 Kunden konfrontiert, die etwa 200 Vertretern zugeordnet waren. Ergebnis der Abfrage sollten die Kunden sein, die in einem gewissen Monat keine Umsätze, dafür aber im Rest des Jahres Umsätze erzielt hatten. Übersetzt in den AdventureWorks Cube, wobei der Vertreter hier durch die Dimension “Produkt” ersetzt wird, sah die Abfrage zuerst so aus:
WITH
MEMBER
UmsatzDezember03
AS
(
[Measures].[Internet Sales Amount]
,STRTOMEMBER(‘[Date].[Calendar].[Month].&[2003]&[12]‘, CONSTRAINED)
)
SET Kunden AS
EXCEPT(EXISTS
(NONEMPTY([Customer].[Customer].[Customer].MEMBERS
,[Measures].[Internet Sales Amount])
,[Product].[Product Categories].[Category].&[1])
,NONEMPTY([Customer].[Customer].[Customer].MEMBERS
,UmsatzDezember03))
SELECT
{
[Measures].[Internet Sales Amount]
} ON 0,
{
Kunden
} ON 1 FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2003]
Um der besseren Lesbarkeit willen erstellte ich das berechnete MEMBER UmsatzDezember03 um das – zugegeben etwas komplizierte – Kundenset zu filtern.
Die ursprüngliche Abfrage (nicht das Beispiel hier) benötigte 45 Sekunden, was bei einer so kleinen Dimension von 172.000 Membern nicht akzeptabel war. Query-Tuning war also unumgänglich.
Nehmen wir uns des Beispiels oben an: Diese Abfrage – mit “MDX Studio” auf dem AdventureWorks Cube ausgeführt – berechnete 44.240 Zellen und benötigte 2,6 Sekunden für die Ausführung.
Mosha’s Befehl zur Sparsamkeit gehorchend, verzichtete ich auf das Hilfsmember und brachte den Ausdruck für UmsatzDezember03 direkt in das Kundenset.
WITH
SET Customers AS
EXCEPT(
EXISTS(NONEMPTY([Customer].[Customer].[Customer].MEMBERS
,[Measures].[Internet Sales Amount])
,[Product].[Product Categories].[Category].&[1])
,NONEMPTY([Customer].[Customer].[Customer].MEMBERS
,(
[Measures].[Internet Sales Amount]
,STRTOMEMBER(‘[Date].[Calendar].[Month].&[2003]&[12]‘, CONSTRAINED)
)))
SELECT
{
[Measures].[Internet Sales Amount]
} ON 0,
{
Customers
} ON 1 FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].&[2003]
Dies führte zu einer Berechnung von nur 7.272 Zellen, benötigte aber immer noch 2,6 Sekunden für die Ausführung. Nachdem ich mir auch das CONSTRAINED Flag in der STRTOMEMBER Funktion erspart hatte, lag die Ausführungszeit bei 0,6 Sekunden.
Was war passiert? Offensichtlich führte das Einfügen des berechneten Members UmsatzDezember03 dazu, das die Berechnung für den Term NONEMPTY([Customer].[Customer].[Customer].MEMBERS,UmsatzDezember03) für jedes einzelne Member der Kundendimension ausgeführt wurde – d.h. je mehr Member in der Dimension enthalten sind, desto langsamer wird die Abfrage.
Darüber hinaus ist festzustellen, dass das CONSTRAINED Flag die Query ebenfalls verlangsamt – was uns zu dem Schluss führt, das Mosha’s Zitat tatsächlich wortwörtlich zu nehmen ist. Allein das Einsparen der Worte “MEMBER, AS, STRTOMEMBER und CONSTRAINED” führt in obigem Beispiel zu einer Beschleunigung der Abfrage von 500% und zu einer 86%-igen Reduktion der berechneten Zellen.
In den Analysis Services 2008 tritt dieses Problem nicht mehr auf. “Hilfsmember” können hier ohne Scham verwendet werden. Beide auf einem SQL Server 2008 ausgeführten Abfragen berechneten nur die nötigen 7.272 Zellen.
