In einem meiner letzten Blog Einträge habe ich versprochen zu prüfen, welche Performance meine Version von “Ever Last NonEmpty” mit großen Dimensionen  erzielt.

Um es gleich vorwegzunehmen: Wird für jeden Artikel in einer Produktdimension mit über 700.000 Artikeln der erste und der letzte Verkaufstag über 5 Jahr berechnet, so brauchen sie sehr sehr viel Zeit. Nach mehr als 20 Minuten habe ich die Abfrage abgebrochen. Verärgertes Smiley Die CPU war bereits rot glühend. In der Praxis ist dieses Laufzeitverhalten  nicht mehr akzeptabel. Smiley mit herausgestreckter Zunge

Muss so eine Anforderung umgesetzt werden, hilft bei dieser Anzahl von Artikeln nur, die entsprechenden Werte in den Cube physikalisch zu laden. Da kommt natürlich ein ganz schön großes Volumen zustande. Eventuell müssen die “LastNonEmpty” Informationen auch nicht historisch abgelegt werden und es genügt die aktuelle Sicht – das würde wiederum einiges an Platz sparen.

Sie wollen es trotzdem wissen

Aber nicht jeder verwaltet so große Dimensionen die die semiadditive Berechnung von “LastNonEmpty” benötigt. Bei meinen Versuchen habe ich festgestellt, dass bis ungefähr 10.000 Member in der Produkt Dimension die folgende MDX Script Lösung noch eine halbwegs brauchbare Performance liefert. Der erste, der noch nicht auf gespeicherte Ergebnisse zugreifen muss wartet bis knapp an die Schmerzgrenze. Die weiteren Abfragen sind dann absolut kein Problem mehr. Das Ergebnis wird dann erstaunlich schnell angezeigt.

Es gibt einen ausgezeichneten Blog Eintrag von Chris Webb zum Thema “Ever Last Non Empty”. Mein Ansatz basiert auf diesen Vorschlag, allerdings habe ich eine kleine Modifikation vorgenommen. Mir ist sehr wohl bewusst, dass Chris Webb auch meinen Ansatz hätte wählen können. Aber die Beispiele von Chris Webb sind in den meisten Fällen so aufgebaut, dass nichts am bestehenden Cube verändert werden muss. Ich habe aber Modifikationen am Cube durchgeführt.

Meine Lösung bestimmt den ersten und letzten Verkaufstag eines Produkts aus dem Adventure Works Cube. Ein nur ganz bedingt wiederverwendbares Beispiel – aber Sie können es ja auf Ihre Anforderungen anpassen.Strebersmiley

Bevor ich Ihnen die Lösung im Detail vorstelle erst mal ein abstraktes Kochbuch:

  1. Für jeden Tag wird ein Offset zum ersten Tag in der Zeitdimension als Attribute in der Zeitdimension gespeichert. Dazu erstelle ich eine neue berechnete Spalte in der DataSource View.
  2. Zur Tabelle FactInternetSales wird in der DataSource View eine neue berechnete Spalte hinzugefügt.
  3. Es werden zwei neue reale Measures zur MeasureGroup InternetSales hinzugefügt. Die Aggregatsfunktionen werden bei dem Measure auf  MIN und beim anderen auf MAX gestellt.
  4. Bei der Dimension Date wird bei dem Attribute Date das Offset  zum ersten Tag der Dimension gespeichert.
  5. Jetzt brauchen wir noch etwas MDX Script und fertig ist die Lösung.

Schauen wir uns die einzelnen Schritte genauer an:

Schritt 1

In der DataSource View wird in der Tabelle Date eine neue berechnete Spalte mit dem Namen DateIndex erstellt. Für die Berechnung des Wertes verwende ich folgenden SQL Ausdruck: DateDiff(day,'20050101', FullDateAlternateKey).

Dieser Index liefert für den ersten Tag in der Dimension einen Wert von 0 und erhöht sich für jeden Tag um den Wert 1. Mit Hilfe dieses Indexes werden wir später quasi die Zeitdimension als Array betrachten und damit einzelne Members direkt adressieren. Wichtig ist, dass das Offset immer zum ersten Eintrag in der lückenlosen Zeitdimension bestimmt wird.

Schritt 2

In der DataSource View wird in die Tabelle FactInterNetSales eine neue berechnete Spalte mit dem Namen NullWert hinzugefügt. Der Wert dieser Spalte bekommt den SQL Ausdruck NULL. Der Wert NULL ist in der Tat wichtig. Wir legen im Schritt 3 zwei Measures in der MeasureGroup InterNetSales an und referenzieren auf dieses “reale Measure”. Wenn Sie unbedingt wollen können Sie auch jeden anderen Wert nehmen – später wird dieser Wert aber mit Hilfe von MDX Script überschrieben.

Schritt 3

In der MeasureGroup InterNetSales legen wir das Measure [Order Date Index MIN] basierend auf der Spalte NullWert und der Aggregatesfunktion MIN statt SUM. Zusätzlich legen wir ein weiteres Measure [Order Date Index MAX] an und basieren diese Spalte ebenfalls auf die Spalte NullWert. Als Aggregatesfunktion verwenden wir jetzt aber MAX. 
Diesen beiden realen Measures werden mit Hilfe von MDX Script überschrieben. Wenn wir mit der Lösung fertig sind können diese beiden Measures auf unsichtbar geschaltet werden.

Schritt 4

In diesem Schritt verändern wir die Zeitdimension (Date). Allerdings gibt es hier eine Einschränkung. Ich gehe davon aus, dass Sie nicht bereits mit dem MemberValue des Attributes Date arbeiten. Ist dies der Fall, dann brauchen wir eine andere Lösung.

Bei der Eigenschaft ValueColumn wird die Spalte DateIndex – im Schritt 1 in der DataSource View angelegt – Bild1eingetragen.

Falls Sie bereits mit dem MemberValue arbeiten, dann fügen Sie DateIndex als Attribute zur Dimension hinzu. Wenn alles funktioniert wie es soll, können Sie dieses Attribut unsichtbar schalten.

Schritt 5

Jetzt benutzen wir die Zutaten innerhalb des MDX Scripts:

Zuerst wird ein unsichtbares statisches Set erzeugt

/* bei mir bringt das leichte Performance Verbesserungen */
CREATE HIDDEN STATIC SET __Date AS [Date].[Date].[Date].Members;

Alle Tage ermitteln, bei denen ein Verkauf stattgefunden hat

/* Tage mit Verkauf raussuchen und Index setzten */
Create Member CurrentCube.[Measures].[VerkaufstagIndex]
AS IIF(IsEmpty([Measures].[Internet Sales Amount]), NULL, [Date].[Date].CurrentMember.MEMBERVALUE), VISIBLE = False;

Den Index für das letzte Verkaufsdatum setzten

/* Index für letztes Verkaufsdatum */
/* [Measures].[Order Date Index MAX] : reales Measure mit Aggregatsfunktion MAX */
SCOPE({[Measures].[Order Date Index MAX]}, [Date].[Date].[Date].MEMBERS);
THIS = MAX({Null : [Date].[Date].CurrentMember}, [Measures].[VerkaufstagIndex]);
END SCOPE;

Den Index für das erste Verkaufsdatum setzen

/* Index für ersten Verkaufsdatum */
/* [Measures].[Order Date Index MIN] : reales Measure mit Aggregatsfunktion MIN */
SCOPE({[Measures].[Order Date Index MIN]}, [Date].[Date].[Date].MEMBERS);
THIS = MIN({Null : [Date].[Date].CurrentMember}, [Measures].[VerkaufstagIndex]);
END SCOPE;

Das erste Verkaufsdatum dem Measure [LetztesVerkaufsdatum] zuweisen

/* erstes Verkaufsdatum */
CREATE Member CurrentCube.[Measures].[LetztesVerkaufdatum]
AS IIF(IsEmpty([Measures].[Order Date Index MAX]), NULL,
[__Date].Item([Measures].[Order Date Index MAX]).Member_Name)
,Visible = TRUE, DISPLAY_FOLDER = 'Verkaufstage';

Das erste Verkaufsdatum dem Measure [ErstesVerkaufsdatum] zuweisen

/* letztes Verkaufsdatum */
CREATE MEMBER CURRENTCUBE.[Measures].[ErstesVerkaufdatum]
AS IIF(IsEmpty([Measures].[Order Date Index MIN]), NULL,
[__Date].Item([Measures].[Order Date Index MIN]).Member_Name),
VISIBLE = TRUE, DISPLAY_FOLDER = 'Verkaufstage';

Zum Testen in Adventure Works können Sie diese MDX Abfrage verwenden:

SELECT
{[Measures].[Internet Sales Amount]
,[Measures].[ErstesVerkaufdatum],[Measures].[LetztesVerkaufdatum]} on 0,
NonEmpty(
[Product].[Product].[Product].members
/*
* {[Date].[Calendar].[All Periods]}
*/
,{[Measures].[Internet Sales Amount]})
on 1
FROM [Adventure Works]


Falls Sie mit einen Attribut arbeiten müssen, dann wird beim zweiten Schritt nur Date.Date.CurrentMember.MemberValue durch Date.DateIndex.CurrentMember ersetzt. Das sollte genügen.

In der Praxis werden Sie diese beiden Kennzahlen vermutlich nie so benutzen. Allerdings gibt es durchaus reale Anwendungsfälle, wo diese Technik hilfreich sein kann. Bitte testen Sie aber unbedingt die Lösung mit Ihren Daten.

Vielen Dank, wenn Sie diese Zeilen noch lesen. Vielleicht erinnern Sie sich bei Bedarf an diesen Artikel. Smiley mit herausgestreckter Zunge

Damit Sie nicht alles abtippen müssen, können Sie sich das MDX Script hier herunter laden.