SQL Server Analysis Services & Excel – “Bewegliche Ziele” oder wie wichtig ist eigentlich ein deterministischer Attribute Key?

Ein Thema kam beim letzten Treffen der SQL Server Community (PASS) in Hamburg auf und zwar die Frage: Wie wichtig ist die deterministische Vergabe des Attribute Keys in Analysis Services?”. Nun ja, und die Antwort darauf ist wie so oft: Kommt ganz drauf an!

Was ist überhaupt gemeint mit der deterministische Vergabe von Attribute Keys?

Am Beispiel von Excel 2010 als Frontend möchte ich kurz die Herausforderung skizzieren.

In Excel 2010 können wir ohne weitere Plug-Ins mittels einer Pivot-Tabelle und/oder den Excel Cube Funktionen auf Analysis Services Datenbanken (aka “Cubes”) zurückgreifen. Und Excel gibt es mittlerweile sowohl als Tool für den Desktop als auch als Excel Services im SharePoint.

Für unser kleines Beispiel habe ich hier eine Tabelle DimKunde mit folgenden Einträgen:

Wichtig dabei ist, dass die Dimensionszeile einen eindeutigen Schlüssel mit der Spalte ID hat. Die Spalte OrtId wird in diesem Fall automatisch erzeugt und enthält damit einen eindeutigen Key für jedes Member des Attributes. Das automatische Erzeugen machen wir zum Beispiel mit dem Ansatz aus meinem Blogpost über DENSE_RANK(). Die Vergabe der OrtId erfolgt immer direkt bei der Aufbereitung der Analysis Services Datenbank. Vielleicht ist da ja ein View oder ähnliches zwischengeschaltet.

Wir setzen die “KeyColumns” Eigenschaft des Attributes in Analysis Services (also im BIDS) auf OrtID und die “NameColumn” auf Ort.

Damit nun die Member nicht in der Reihenfolge der Schlüssel angezeigt werden, setzen wir noch die Eigenschaft “OrderBy" auf “Name”.

Und eine kleine Hierarchie darf auch nicht fehlen.

Damit bekommen wir folgende Dimension:

 

Natürlich gibt es noch Fakten dazu, aber darauf gehen wir hier mal nicht im Detail ein. :-)

Und schon können wir uns unsere multidimensionale Datenbank in Excel darstellen lassen. Nicht viel dran mit einer Dimension und einer Faktentabelle, aber es reicht für den Moment.

Nun filtern wir in Excel die Darstellung so, dass nur noch Umsätze für Hamburg angezeigt werden sollen:

Noch schnell eine Überschrift drüber und schon ist ein einfacher “Report” fertig, wie er wahrscheinlich in etwas komplexerer Form in zahllosen Unternehmen tagtäglich erstellt wird. Den könnten wir nun gut abspeichern und später wieder nutzen, wir haben ja nun unsere Umsätze für Hamburg “herausgefiltert”. Nützlich so etwas und kann auch gerne dem Management zur Verfügung gestellt werden.

 

Nun kommt der Tag, an wir einen weiteren Kunden dazu bekommen. Kein Problem, der bekommt ja sofort eine eindeutige ID zugeordnet. Aber, bei der täglichen Vergabe der OrtId ist nun auf einmal das Member “Essen” zwischen “Berlin” und “Hamburg” gerückt und bekommt damit die automatische OrtId “2”. 

Wenn wir uns nun die erweiterte Dimension anschauen, dann ist die korrekt ergänzt worden. Sieht alles unverdächtig aus!

Aber wenn wir nun unser gespeichertes Excel Dokument anschauen und mittels Aktualisieren die Darstellung aktualisieren, dann haben wir auf einmal folgendes in unserem “Report” stehen:

Was ist denn jetzt passiert? Ganz offensichtlich speichert Excel den Key des Members ab. Und sobald wir aktualisieren, wird aus Hamburg Essen, da nun Essen die OrtId “2” hat. Ein solches Verhalten von auf Excel basierenden Reports wird sehr wahrscheinlich das Vertrauen der Endanwender in die Business Intelligence Lösung nicht unbedingt festigen!

Wie schaust mit den Excel Cube Funktionen aus? Hierzu legen wir in Excel ein Beispielszenario an:

  Dieses kleine Beispiel basiert auf folgenden Formeln:

Der obere Wert verwendet den Key “&[2]”. Der untere Wert bezieht sich direkt auf den Namen des Members “Hamburg”. Nach der Erweiterung um unseren Kunden in Essen, sieht unser Excel Dokument nun so aus:

Der obere Wert hat sich geändert auf den Wert von Essen, der untere ist erhalten geblieben. Wobei nun gesagt werden muss, dass die Funktion in C7 mit dem Standardverhalten von Excel erstellt wurde.

Fragestellung ist nun: Wie kommen wir da jetzt nur raus?

Es bleiben uns drei Varianten:

1.) Wir verwenden keine extra Key Columns und nutzen den hoffentlich einmaligen Namen des Attributes. Ok, dann geht die Performance sehr wahrscheinlich recht bald den Bach runter, aber irgendwas ist ja immer… (mal im Ernst, damit kommen wir nicht weiter..)

2.) Wir sagen den Anwendern, dass sich unser BI auf magische Weise über Nacht verändern kann und sie bitte auf den einen oder anderen Client verzichten sollten. (ok, ist auch nix… sehe ich ja ein…).

3.) Wir investieren wesentlich mehr Zeit in die Datenbewirtschaftung und sorgen dafür, dass die Schlüssel der Attribute deterministisch sind! (bin offen für Punkt 4, 5 etc…)

Also, sobald unser SSAS Projekt den Status Proof-of-Concept oder Prototyp verlässt und tatsächlich von den Anwendern produktiv eingesetzt wird, müssen wir uns mit dieser Thematik auseinandersetzen. Einen Ansatz dazu hatte ich schon mal kurz angerissen und zwar die Verwaltung der Dimensionen in Key-Value-Tabellen erfolgen zu lassen. Auf den ersten Blick scheint dieser Ansatz unnötig kompliziert, aber er hat einige Vorteile. Einen zeigt dieser Artikel, da in den Key-Value Tabellen die Schlüssel für die Attribute bereits gepflegt werden. Damit umgehen wir die Herausforderung der “spontanen deterministischen” Vergabe! :-)