Der DeltaMaster-SQL-Durchgriff ist eine komfortable Möglichkeit, in BI-Anwendungen eine nahtlose Integration der meist vorkonsolidierten dimensionalen Daten im OLAP-Cube zu den relationalen Rohdaten im Data Warehouse zu gewährleisten und damit Medienbrüche für den Anwender zu vermeiden.
Wie so oft sind jedoch bei der Modellierung nichttrivialer Szenarios einige Details zu beachten, um „böse Fallen“ in Form von unerwünschten Ergebnissen oder gar Fehlern zu vermeiden. Hierzu liefert dieser Blogartikel einen Überblick.

So funktioniert der SQL-Durchgriff in DeltaMaster

Die meisten unserer Anwendungen verwenden die OLAP-Technologie Microsoft Analysis Services als Datenquelle. Die DeltaMaster-Anwendung ist also mit einem Cube verbunden, dessen Daten nach der Grundregel „Analysieren heißt Vergleichen“ in aller Regel vorkonsolidiert sind. Die relationalen Rohdaten im Data Warehouse, auf deren Grundlage das OLAP-Modell erstellt wird (Star- oder Snowflake-Schema) sind dagegen oft detaillierter.

Ein Beispiel: Die Faktentabelle enthält noch einzelne Rechnungsbelege, die bei der Verarbeitung des OLAP-Cubes dann in dessen Dimensionalität und Granularität aggregiert werden. Kauft ein Kunde in „seinem“ Supermarkt morgens und abends auf dem Weg zur und von der Arbeit jeweils eine Dose Cola, sind die in solchen Fällen typischen Attribute Tag, Filiale, Kunde und Produkt identisch. Aus zwei verschiedenen Transaktionen mit zwei Belegpositionen wird also ein einziger Wert für Kennzahlen Absatz und Umsatz in nur einer Cube-Zelle. Die beiden ursprünglichen Vorgänge sind demnach im Cube nicht mehr einzeln zu identifizieren. Dies ist aus analytischer Sicht auch meist nicht relevant. Im Falle von Auffälligkeiten während der Analyse ist es jedoch wünschenswert, den Bezug doch wieder herstellen zu können – möglichst dynamisch, automatisch und ohne manuellen Aufwand.

Genau hier kommt der SQL-Durchgriff ins Spiel: DeltaMaster ist mit der Anbindung des relationalen Modells (Modus Modellieren, rechtes Menü) in der Lage, die Datenquelle (DataSource, DS) des Würfels und anschließend dessen Datenquellensicht (DataSourceView, DSV) auszulesen. Letztere ist quasi der Bauplan aller Einzelteile des Würfels und erlaubt ein vollautomatisches Mapping des dimensionalen Modells zum relationalen Modell. Daraufhin verhält sich DeltaMaster wie ein Simultandolmetscher und übersetzt bei Bedarf MDX in SQL, denn jedes Objekt im Cube hat seine exakte Herkunft im Data Warehouse.

Genau das sollte man sich stets in Erinnerung rufen – denn wenn diese Voraussetzung nicht zu 100% erfüllt ist, erlebt der DeltaMaster-Anwender später mitunter böse Überraschungen.

Böse Fallen

Virtuelle Objekte in der Datenquellensicht

Microsoft erlaubt die Erstellung von benannten Berechnungen (Named Calculations) und benannten Abfragen (Named Queries) innerhalb der Datenquellensicht. Dieses Konzept richtet sich vermutlich an weniger professionelle OLAP-Entwickler ohne Zugang zur Datenquelle. Wer keine DDL-Rechte im Data Warehouse hat, kann hier dennoch einzelne Ausdrücke oder ganze Abfragen definieren.

Neben diversen anderen negativen Effekten führt dies bei der Nutzung des SQL-Durchgriffs in DeltaMaster zu einer Syntaxfehlermeldung, da beim Mapping des betroffenen MDX-Objekts in SQL Server ja keine tatsächliche Quelle existiert.

Der Analysis-Services-Experte Thomas Ivarsson strafte dieses Konzept schon 2011 in seinem wunderbar launischen Blog „20 signs you have hired the wrong BI consultant“ mit der Formulierung „Damit zieht der ETL-Prozess in den Cube ein“ ab:

https://thomasivarssonmalmo.wordpress.com/2011/06/15/top-20-signs-that-you-have-hired-the-wrong-ssas-consultant/

Dieses Risiko besteht glücklicherweise nur bei Datenmodellen, die von Kunden oder Modellierern manuell mit Microsoft-Bordmitteln, d.h. SQL Server Data Tools (SSDT) und nicht automatisiert mit DeltaMaster ETL erstellt wurden.

Unverbundene Dimensionen

Die Verwendung einer oder mehrerer Hilfsdimensionen (Periodenansicht, Kumulation) für Zeitberechnungen gilt in der professionellen BI-Szene aus gutem Grund seit langer Zeit als Best Practice: Die Abstraktion der Bildung von Zeitreihen (Kumulation, rollierende Durchschnitte etc.) und Zeit-sprüngen (Vorperiode/Vorjahr mit absoluter und prozentualer Abweichung) und deren Verlagerung in separate Achsen schafft bestmögliche Flexibilität und vermeidet Mehrfachaufwand durch Redundanz.

Darüber, ob diese Hilfsdimensionen jedoch mit allen MeasureGroups verbunden sein und deshalb sämtliche Faktentabellen um Dummy-Spalten erweitert werden müssen, wird immer wieder diskutiert.

Die MDX-Berechnungen im Cube oder in DeltaMaster (Zeitanalyseelemente) funktionieren auch ohne Verbindung zwischen den Hilfsdimensionen und den MeasureGroups tadellos – und dies nachweislich unabhängig von MSAS-Flags wie IgnoreUnrelatedDimensions.

DeltaMaster ETL bietet zu diesem Zweck gar ein eigenes Konzept: die „Unlinked Cube Dimensions“.

Was spricht also doch dafür, die (dank DeltaMaster ETL geringe) Fleißarbeit auf sich und den (eben-falls eher zu vernachlässigenden) zusätzlichen Speicherplatz in der SQL-Datenbank in Kauf zu nehmen und die Hilfsdimensionen mit allen MeasureGroups zu verbinden?

Nach meiner Praxiserfahrung gibt es nur einen Grund, dieser ist jedoch gravierend: Der DeltaMaster-SQL-Durchgriff liefert nur dann die korrekte Anzahl Datensätze, wenn die betroffenen Fakten- und Dimensionstabellen in der DSV verbunden sind. Ansonsten wird ein Join zu wenig generiert, und es entsteht ein kartesisches Produkt.

Ob es sich hierbei um einen Defect handelt, ist Ansichtssache: Nach exakter Auslegung der in der Einleitung beschriebenen Definition verhält sich DeltaMaster konsequent und konsistent – wenn der Würfel nicht dem Bauplan entspricht, unterscheiden sich die Ergebnisse…

Collections als KeyColumns

Schon Ralph Kimball, der Papst der OLAP-Modellierung, empfahl u.a zur Performanceoptimierung die durchgängige Verwendung eindeutiger Dimensionsschlüssel, im Zweifelsfall maschinell erstellter, nicht sprechender „Surrogate Keys“ ohne jede inhaltliche Bedeutung.

Wenn Elementschlüssel nur in Kombination mit weiteren Merkmalen fachlich und technisch eindeutig sind (z.B. Kundennummern nur innerhalb einzelner Firmen/Mandanten), ist es ein Leichtes, im ETL-Prozess eindeutige zusammengesetzte Schlüssel zu erzeugen. In unserer Projektpraxis erfolgt dies meist per Konkatenation in den betroffen Dimensions- und Fakten-Views.

Dennoch erlaubt Analysis Services ganz pragmatisch die Verwendung von Collections, d.h. die Angabe von mehr als einer Spalte als KeyColumns für Dimensionsattribute.

Abgesehen vom Widerspruch zur Lehrmeinung und dem Mehraufwand an diversen Stellen des Modellierungsprozesses kommt auch in diesem Falle das böse Erwachen sehr spät: Wiederum generiert DeltaMaster aktuell mitunter ein falsches SQL-Statement, das nach Projektbeobachtung bei der Filterung auf Elemente der untersten Ebene der betroffenen Dimension zu einem leeren Ergebnis führt.

Dieses Detail ist zweifelsfrei ein Bug. Ein entsprechender CR ist erstellt. Bisweilen ist von der Verwendung von Collections als KeyColumns unbedingt abzuraten.

Auch für diesen Fall ist das Risiko bei der Verwendung von DeltaMaster ETL ausgeschlossen.

Berechnete Elemente

Wie verhält sich der DeltaMaster-SQL-Durchgriff im Kontext berechneter Elemente?

Der erfahrene BI-Berater antwortet auf Fragen grundsätzlich mit „Es kommt darauf an…“
Das ist nur selten eine Floskel. Auch hier sind verschiedene Fälle zu unterscheiden:

1. Berechnungen in DeltaMaster (WITH MEMBER)
   • Measures
     Grundsätzlich stellen im Frontend berechnete Kennzahlen unabhängig vom Definitionstyp (Quotient, Filter, benutzerdefiniert etc.) kein Problem dar.
     Allerdings relativiert sich hier der eigentliche Zweck des SQL-Durchgriffs, nämlich der Einzelnachweis der den aggregierten Werten des OLAP-Cubes zugrundeliegenden Faktentabelle. Letztere enthält nur die Basis-Measures, so dass auch nur die-se gezeigt werden können. Der Anwender muss die Berechnung daher quasi im Kopf nachvollziehen. Beispiel aus unserer Chair-Anwendung: Der berechnete Nettoumsatz ergibt sich aus der Formel „Umsatz -./. Rabatte- ./. Erlösschmälerungen“. Diese drei Spalten sind in der SQL-Tabelle enthalten, nicht aber das Ergebnis der Subtraktion.
   • Dimensionselemente
     Benutzerdefinierte berechnete Elemente führen beim SQL-Durchgriff zu einer Fehlermeldung: „Der Bericht kann nicht berechnet werden, da im Filter ein berechnetes Element ausgewählt ist.“
     Diese Einschränkung ist aus Anwendersicht unschön, aus technischer Sicht jedoch verständlich: Die Semantik des berechneten Elements kann beliebig komplex sein (Arithmetik, MDX-/VBA-Funktionen etc.) und kann maschinell nicht gemäß der definierten Logik in SQL-Syntax übersetzt werden.
2. Berechnungen im Cube (MDX-Skript)
   • CREATE MEMBER
     Sowohl für Measures als auch für Dimensionselemente gelten die obigen Angaben.
   • SCOPE
     Bei der Filterung von mit SCOPE überschriebenen Dimensionselementen liefert der SQL-Durchgriff die korrekte Anzahl Datensätze aus der zugrundeliegenden Faktenta-belle. Es entsteht jedoch je nach Semantik höchstwahrscheinlich eine Wertdiskrepanz, denn die definierte Formel ist nur dem OLAP-Cube bekannt, nicht aber der relationa-len Datenquelle.
     Es ist also zu empfehlen, in derartigen Szenarios zumindest unbedarften Anwendern im Präsentationsmodus keinen SQL-Durchgriff anzubieten.

Bestandswerte im Kontext (Zeit-)Granularität

Was ist beim DeltaMaster-SQL-Durchgriff im Kontext zu Bestandsgrößen (Measures mit semiadditi-ven Aggregatoren wie z.B. LastNonEmpty) zu beachten?

Nun wird es spannend, denn abhängig von der aktuellen Filtereinstellung in der Periodendimension liefert die normale Logik (aus inhaltlicher Sicht) zu viele Daten: Filtert man auf einen Monat, die Fakten liegen jedoch täglich vor, zeigt der SQL-Durchgriff alle Zeilen aller Tage des betroffenen Monats. Das ist logisch zweifellos korrekt, fachlich jedoch nicht gewünscht – im OLAP-Cube sogt der LNE-Operator ja auch dafür, dass der letzte vorhandene Tageswert auf den Monat übernommen wird.

Für diesen Fall muss also eine zusätzliche Logik implementiert werden, die dasselbe Verhalten beim SQL-Durchgriff erzwingt. Hier ein Lösungsvorschlag, so im Einsatz bei einem unserer Großkunden:

1. Im PostProcess wird pro Monat der letzte Tag mit Bestandswerten in einer T_S-Tabelle gespeichert.
   
2. In der V_SEC-View wird die Faktentabelle mit der T_S-Tabelle verknüpft. Mittels CASE wird pro Datensatz ermittelt, ob der jeweilige Tag der letzte Tag des Monats ist.
   

Im Quellbericht werden mit Hilfe von Bedingungen zwei Verknüpfungen zu SQL-Durchgriffs-Berichten definiert.

Wenn im Filter ein Tag ausgewählt ist, kann die Faktentabelle (bzw. V_SEC-View) ungefiltert abgefragt werden. Ist ein Monat ausgewählt, ist in den Berichtseigenschaften des zweiten SQL-Durchgriffs-Berichts eine zusätzliche Bedingung erforderlich, die nur die Datensätze des letzten Tages des gefilterten Monats mit Daten liefert.

Als Alternative zu diesem doch recht aufwändigen Ansatz bietet sich die Möglichkeit, den neuen komplett Stored-Procedure-basierten DeltaMaster-SQL-Durchgriff 2.0 zu verwenden.

Wir hoffen mit diesem Blogbeitrag für die jeweiligen – gar nicht so exotischen – Szenarios sensibilisiert und damit zur Vermeidung potentieller Risiken beigetragen zu haben.

Happy Modeling!