Einführung in SSAS

Im folgenden Artikel möchte ich die OLAP-Implementierung von Microsoft, die SQL Server Analysis Services (SSAS) näher beschreiben.

OLAP, ROLAP MOLAP, HOLAP, Cube was ist das?

OLAP steht für „online data processing“ und beschreibt eine Methode von analytischen Informationssystemen bzw. eine sog. hypothesengestützte Analysemethode und wird in Business-Intelligence-Anwendungen genutzt.
Es beruht darauf, dass der Datenanalyst vor einer Abfrage schon weiß, welche Abfragen er an das System stellen möchte. Die Abfrage dient dann dazu, das erwartete Ergebnis zu bestätigten oder zu widerlegen.
Dies kann beispielsweise in den Bereichen Controlling, Finanzabteilungen, Vertrieb, Produktion usw. genutzt werden.
Dem OLAP liegt ein sog. OLAP-Würfel (Cube) zugrunde, der aus einer relationalen Datenbank erstellt wurde.

ROLAP

Steht für “relational OLAP” und bedeutet, dass die operationale Datenbank relational ist.
Der Vorteil hiervon ist, dass flexiblere Abfragen möglich sind, dafür ist es allerdings langsamer als MOLAP.

MOLAP

Steht für “multidimensional OLAP” und bedeutet, dass die operationale Datenbank multidimensional ist.
Der Vorteil liegt in der Geschwindigkeit der Abfrage, zulasten der Flexibilität.
Jedes Datenattribut (z.b. Zeitpunkt, Filiale, Kostenstelle etc.) ist eine eigene Dimension.

HOLAP

Steht für “hybrid OLAP” und vereint Elemente von ROLAP und MOLAP in einem Modell.

In SSAS kann im Partitions-Tab der Speichertyp einer Partition eingestellt werden:

Cube

Beim Verarbeiten (processing) des OLAP werden die einzelnen Datensätze als Elemente eines Würfels dargestellt. So können Daten multidimensional betrachtet und verknüpft werden.

Slicing, Dicing, Drill-down, Rotating

Slicing

Beim sog. slicing  wird eine Scheibe des Würfels abgeschnitten und steht als zweidimensionale Tabelle zur Verfügung. Dies kann z.B. der Verkauf von bestimmten Produkten innerhalb eines Monats sein.
In SSAS kann innerhalb das Partition-Tabs ein Rechtsklick auf eine Measuregroup gemacht (1) und in den Properties (2) das Slicing (3) definiert werden:

Als MDX-Query ausgedrückt sieht das bspw. so aus:

Select {[measure].[abc]} on 0,  from cube  where [Customer].[Customer].&[250] --- this is slice  

Dicing

Beim dicing wird ein kleinerer Würfel mit Teildatensätzen aus dem großen Würfel herausgefiltert, der nur die relevanten Daten enthält.
Als MDX-Query ausgedrückt sieht dann folgendermaßen aus:

Select {[measure].[abc]} on 0,  from cube  where {[Customer].[Customer].&[250],[Product].[Product].&[100]} --- this is dice  

Drill-down / Drill-Up

Drill-down bzw. drill-up ist eine Kombination von slice und dice.
Dabei wird eine Dimension quasi heraus- oder herangezoomt, sodass mehr oder weniger detailliertere Werte betrachtet werden können.

Rotating

Beim rotating wird der Würfel gedreht, sodass eine andere Dimension betrachtet wird. Beispiel: bei einer Verknüpfung von Zeit, Region und verschiedenen Produktvarianten könnte man sich statt auf den zeitlichen Verlaufstrend ebenso auf die Entwicklungen der verschiedenen Regionen fokussieren.

SSAS-Abfragesprachen

MDX

Das Kürzel steht für „MultiDimensional eXpression Language”.
Es ähnelt SQL, unterscheidet sich aber dadurch, dass Mengen im n-dimensionalen Raum in Betracht gezogen werden.
MDX ist hauptsächlich für das Multidimensional Model und für OLAP- und Data-Spezialisten gedacht, sehr mächtig, erfordert aber gute Kenntnisse des Multidimensional Models.

Bei Abfragen werden die Daten nicht aus relationalen Tabellen wie bei SQL, sondern aus mehrdimensionalen Objekten (den Cubes) abgefragt.

Fakten und Dimensionen sind hier die beiden wichtigsten Entitäten. Fakten sind Zahlen (z.B. Umsatz, Menge, EBIT), Dimensionen sind Attribute die Zahlen beschreiben.
Das können z.B. Zeit, Geographie (Land, Stadt etc.) aber auch Personen (CEO, Teamleiter, Mitarbeiter etc.) sein.

Die Daten sind im mehrdimensionalen Modell als sog. Tupel organisiert (Tupel sind Adressen im Würfel).
Durch die Tupel werden Daten-Scheiben (slices) identifiziert.
Ebenso gibt es das sog. “Set” als Kollektion von Tupeln.

Beispiele:

// Tupel, Jahr 2017  Date.Calendar.[Calendar Year].&[2016]	      //	Tupel, Jahr 2016 in Deutschland  Date.Calendar.[Calendar Year].&[2017]	     // Tupel, Jahr 2016 in Deutschland  (Date.Calendar.[Calendar Year].&[2016], [Country].&[Germany])      // Set, Jahr 2016 und 2017 in Deutschland  ({Date.Calendar.[Calendar Year].&[2016], Date.Calendar.[Calendar Year].&[2017]}, [Country].&[Germany])	  

Bei Tupeln die aus verschiedenen Dimensionen bestehen, wird eine Klammer „()“ benutzt.
Sets sind durch geschweifte Klammern „{}“ gekennzeichnet.

MDX-Abfragen liefern automatisch ein Aggregat aus der Abfrage zurück.

Eine Hierarchie über Länder, die einen Tupel zurückliefert, der nur Daten aus München enthält wird in MDX bspw. folgendermaßen abgebildet:

[Cube].[Country].[All].[Germany].[München]

DAX

Das Kürzel steht für „Data Analysis eXpression Language.
Auch diese Sprache ähnelt SQL, ist allerdings keine Abfragesprache wie SQL oder MDX, sondern eher eine Analysesprache.
Sie ist primär beim Tabular Model als Analysesprache für den Endanwender im „Self-Service BI“ (Power-BI, Excel, Power-Pivot) geeignet.
Trotzdem können auch Daten aus tabellarischen Modellen abgerufen oder Measures in die Abfrage integriert werden.
Die aus einer DAX-Query resultierenden Daten werden als Tabelle zurückgeliefert.

Nachfolgendend zwei identische Abfragen, jeweils in SQL und in DAX.

SQL:

DAX:

Anhand dieses Beispiels sieht man, das die „WHERE“-Klausel aus dem SQL in DAX durch ein „FILTER()“ und das „GROUP BY“ durch ein „SUMMARIZE()“ abgebildet werden.

TMSL

Das Kürzel steht für „Tabular Model Scripting Language“.
Sie ist erst seit Erscheinen von SSAS 2016 verfügbar und kann sowohl in Tabular als auch Multidimensional Models verwendet werden.
Die Abfragen werden über das XLMA-Fenster im SQL Server Management Studio erzeugt und resultieren in einer JSON-Struktur.
TSML-Skripte können über drei Arten ausgeführt werden:

• XMLA-Fenster in SSMS
• Per AMO Powershell über das cmdlet „Invoke-ASCmd“
• Als „Analysis Services Execute DDL Task“ in SSIS

Die verschiedenen SSAS-Modelle

Tabular-Model

• Speichert Daten relational als Datensätze
• Dadurch besser Skalierung als Multidimensional-Model
• Daten müssen aus einer vielseitigen aber langsameren Datenbank geholt und aufbereitet werden, daher ist es langsamer als das Multidimensional-Model

Multidimensional-Model (Cube)

• Speichert Daten als Datenpunkte
• Mehr Speicherplatzverbrauch
• Schnellerer Zugriff auf Datenpunkte

Data source views

Ist eine einheitliche Sicht der Datenbank-Metadaten die in den Data-Sources definiert wurden. Sie ermöglichen das Arbeiten ohne eine offene Datenbankverbindung.
Sie können im Solution Explorer erstellt werden. Nach Auswahl der zugrundeliegenden Datenquelle können die in die Sicht eingeschlossenen Objekte ausgewählt werden.

Berechnete Spalten

Innerhalb einer Tabelle wird durch „Insert column“ eine neue Spalte angelegt, die eine Berechnungsformel im Format „Data Analysis Expressions (DAX)“ aufnehmen kann.

Measures

Measures sind Berechnungen mithilfe von DAX-Formeln, die im Gegensatz zu berechneten Spalten, anhand eines vom Benutzer ausgewählten Filters ausgewertet werden.
Dieser Filter kann z.B. eine bestimmte Spalte sein.
Anhand dieses Filters wird dann ein Wert für jede Zelle berechnet. Somit sind Measures leistungsstarke, flexible Berechnungen, die in fast allen tabular-models für dynamische Berechnungen von numerischen Daten verwendet werden.
Ein neues Measure wird erstellt, indem eine bestehende Tabelle im Model-Designer geöffnet wird.
In der Tabelle muss dann der Haken für die Anzeige des Measure-Grids gesetzt werden:

Das Measure-Grid befindet sich unterhalb der Tabelle:

Mit einem Klick auf der Zeile kann in der Formel-Zeile am oberen Bildschirmrand eine Formel eingegeben werden.

Leistungskennzahlen (Key Performance Indicator/KPIs)

KPIs werden zum Messen der Leistung eines Basiswerts eines Zielewerts genutzt.
Basiswert und Zielwert können sowohl in absoluten Werten als auch als Measures definiert werden.
In praktischen Anwendungen werden sie genutzt um einen schnellen Einblick in den Geschäftserfolg zu erhalten und um Trends schneller zu identifizieren.
KPIs werden durch einen Klick mit der rechten Maustaste auf eine Spalte und den Kontext-Menüpunkt „Create KPI“ erstellt.
Im nachfolgenden Dialog kann dann als Target entweder ein Measure oder ein absoluter Wert angegeben werden, der die verschiedenen Stufen voneinander abgrenzt.  Ein Icon-Style kann ebenfalls gesetzt werden. Im dargstellten Screenshot entspricht das den Einstellungen:
Wert 25.000
Unteren Schwelle: 10.000
Obere Schwelle: 20.000
Icon: Selection-Icon in Ampel-Darstellung:

In der eben selektierten Zelle im Measure-Grid wird jetzt ein Symbol angezeigt, das zeigt, dass die Zelle als Basiswert für eine KPI gewählt wurde.

Perspektiven

Perspektiven stellen, ähnlich wie Datenbank-Views für Tabellen, nur die für eine gewisse Zielgruppe relevanten Teile des SSAS-Modells dar.
Dies können z.B. in der Dimension der Zeitachse Wochen, Monate oder Jahre sein.
Dabei können Tabellen, Spalten, Measures, Hierarchien und KPIs dargestellt werden.
Erzeugt werden Perspektiven durch einen Rechtsklick auf den Punkt „Perspectives“ im Tabular Model Explorer“ und die anschließende Auswahl anzuzeigender Objekte im erscheinenden Fenster:

Hierarchien

Sind Gruppen von Spalten, die in verschiedenen Ebenen angeordnet werden.
Bsp: eine Geographie-Ebene mit Unterebenen für Land, Landkreis, und Stadt.
Zum Erstellen muss in die Diagramm-Ansicht des Model-Explorers gewechselt werden:

Als nächstes muss ein Rechtsklick auf die gewünschte Quell-Tabelle vorgenommen werden:

Es erscheint dann ein neuer Eintrag in der Spalten-Auslistung der Tabelle „DimProduct“. Dieser kann umbenannt werden und anschließend können einige Spalten durch Drag & Drop in die Category-Hierarchy hineingezogen und ebenfalls umbenannt werden werden:

Partitions

Unterteilt eine Tabelle in mehrere logische Teile, die unabhängig von anderen Partitionen verarbeitet werden können.
Bsp. Eine Unterteilung von Verkaufsdaten nach Jahren.
Partitions werden im Tabular Modul Explorer durch Erweitern der Tabellen und und einen Rechtsklick auf den Unterpunkt „Partitions“ angelegt:

Im neu erscheinenden Fenster kann jetzt die bestehende Partition „FactInternetSales“ kopiert werden (1) und die Ansicht kann von der Tabellen- auf die SQL-Ansicht umgeschaltet werden (2).
Im Editor kann das vorhandene passende SQL-Statement angepasst werden (in meinem Fall die Erweiterung um eine WHERE-Klausel, welche auf das Jahr 2018 filtert):

Um eine Überlappung der Partitionen beim Processing zu vermeiden, sollte die Standard-Partition gelöscht werden (diese würde die Daten aller Jahre zurückliefern).

Multidimensional Model

Das mehrdimensionale Modell arbeitet nicht 2-Dimensional wie das Tabular Model, sondern kann mehrere Dimensionen bereitstellen.

Dimensions

Eine neue Dimension kann durch einen Rechtsklick auf „Dimensions“ im Solution-Explorer erstellt werden:

Nachfolgend können die Datasource-View, die Tabelle und die Key-Column angepasst werden.
Anschließend werden die Dimensionsattribute und deren Datentypen festgelegt.

Cubes

Durch einen Rechtsklick auf den Punkt „Cubes“ im Solution-Explorer kann ein neuer Cube angelegt werden.
Über den Button „Suggest“ schlägt der Assistent geeignete Tabellen (sog. „Faktentabellen“) für den Cube vor.

Anschließend können die zu verwendenden Measures ausgewählt werden:

Die neu zu erstellenden Dimensionen müssen ausgewählt werden:

Attribute in Dimensionen

Durch einen Doppelklick auf die entsprechende Dimension müssen die verwendeten Attribute der Dimension eingestellt werden.
Dazu werden die Attribute aus den Tabellen in den Attribute-Bereich gezogen:

Projekt bereitstellen

Um die Cube- und Dimensionsdaten anzeigen zu können, muss das Projekt zunächst bereitgestellt werden.
Bei der Bereitstellung werden die im Projekt erstellten Objekte im Analysis-Service erstellt, ohne jedoch Daten zu beinhalten (dies entspricht der Datenbankstruktur einer Datenbank).
Die Bereitstellung wird durch einen Rechtsklick auf das Projekt konfiguriert:

„Processing Option“: es kann eingestellt werden, ob das Projekt nach der Bereitstellung processed werden soll oder nicht.
„Transaction Deployment“: legt fest, ob das Deployment transaktional erfolgt, (also entweder vollständig fehlerfrei oder gar kein Deployment)
„Server mode“: soll das gesamte Projekt neu deployt werden, oder nur Änderungen (schneller)
„Server“: der Server auf dem deployt wird
„Database“: Name der SSAS Datenbank (und nicht der SQL-Quell-Datenbank)

Projekt verarbeiten

Wenn der Cube auf den Analysis Server deployt wurde, ist eine Analysis-Datenbank sichtbar (nicht mit der Quell-SQL-Datenbank zu verwechseln).
Innerhalb dieser ist u.a. der erstellte Cube (oder mehrere) zu sehen. Durch einen Rechtsklick auf den Cube kann im Kontext-Menü „Process“ ausgewählt und die Verarbeitung des Cubes somit gestartet werden.
Im Anschluss enthält der Cube die gewünschten aufbereiteten Daten, welche nun durch MDX Queries, Excel-Pivot o.ä. weiter ausgewertet und verarbeitet werden können.

Fazit

Das Thema SSAS bzw. OLAP im Allgemeinen ist zu komplex um darauf in einem einzigen Blog-Artikel eingehen zu können, aber ich hoffe eine kleine Einführung zum grundlegenden Verständnis gegeben zu haben.

Links

[1] http://sqljason.com/2010/04/creating-solution-file-from-ssas.html
[2] https://docs.microsoft.com/de-de/sql/analysis-services/tabular-models/kpis-ssas-tabular?view=sql-server-2017
[3] https://github.com/just-bi/adventureworksupdater
[4] https://www.sqlchick.com/entries/2010/10/10/display-of-ssas-kpi-indicators-in-excel.html
[5] http://falconteksolutionscentral.com/?p=734
[6] https://bi2run.de/olap-quadratisch-praktisch-gut/
[7] https://plus-it.de/blog/mdx-dax-sql-die-richtige-sprache-fuer-jedes-datenmodell/