Microsoft Produkte im Datenmanagement: Der SQL Server 2017

Microsoft bietet eine breite Palette von Produkten für den Einsatz im Bereich Business Intelligence und Datenanalyse, die ich in mehreren Beiträgen gerne vorstellen möchten. Eines der mächtigsten Tools ist Microsoft SQL Server, den ich heute im Detail anschaue. Wir haben die 2017 eingeführte Version nun bereits seit einiger Zeit für verschiedene Projekte im Bereich Datenanalyse im Einsatz.

Einleitend sei gesagt, dass der SQL Server 2017 eine Weiterentwicklung des SQL Servers 2016 ist, mit dem sich Microsoft endlich von der Strategie einer einzigen Plattform (Windows) verabschiedet und den Weg zur Multiplattform nun auch für SQL Server eingeschlagen hat. Während die Verantwortlichen bei Microsoft Anfang des Jahr 2000 Linux noch für ein „Krebsgeschwür“ hielten, ist der SQL Server für Linux heutzutage das erfolgreichste Server Produkt.

Der SQL Server 2017 ermöglicht die Entwicklung in verschiedenen Entwicklungssprachen, was besonders für Machine Learning und Künstliche Intelligenz eine notwendige Voraussetzung ist.  Weiterhin ist es möglich, den SQL Server entweder lokal oder in der Cloud auszuführen. Als Betriebssysteme können Windows-basierte sowie Linux und Linux-basierte Docker-Container eingesetzt werden.

Nun werden wir uns die Neuigkeiten der einzelne Komponente des SQL Server ein bisschen näher anschauen, dabei legen wir den Fokus auf folgende Punkte:

  • Datenbank Engine
  • Integration Services
  • Analysis Services
  • Reporting Services
  • Maschine Learning Services

Datenbank Engine 

Die wichtigste und grundlegendste Änderung befindet sich in der Datenbank Engine und es handelt sich um einen Sicherheits-Feature. Dabei geht es um die Sicherheit der sogenannten „CLR Assemblies“. CLR (Common Language Runtime) Assemblies sind Programme, die der Benutzer in .NET schreibt und der SQL Server ausführt. Bevor andere Sprachen wie Python oder R direkt in SQL Server integriert wurden, war das die einzige Möglichkeit, um spezielle Berechnungen oder Aktionen durch SQL Server durchführen zu lassen. Jedes Programm das auf dem Computer ausgeführt wird, wird von einer Autorität überwacht und muss bestimmte Sicherheitsbedingungen erfüllen.

Als CLR in den SQL Server integriert wurde, hat diese Rolle die sogenannte CAS (Code Access Security) übernommen und war damit für die Sicherheit (Zugriffsrechte) der „CLR Assemblies“ verantwortlich.

Ab dem SQL Server 2017 wird die sogenannte „CLR strict security“ eingeführt und in Standard Einstellungen aktiviert. Die „strict security“ bedeutet, dass alle CLR Assemblies als UNSAFE behandelt werden und somit weniger Zugrifstrechte bekommen. Das erhöht die Sicherheit der Assemblies deutlich. Ohne aktive „strict security“ kann eine Assembly sogar sysadmin-Rechte bekommen, was Angreifer nutzen könnten, um über den SQL Server das komplette System zu kompromittieren.

Nachdem „strict security“ aktiviert wurde, können nur signierte Assemblies geladen werden. Signierte Assemblies sind deswegen wichtig, weil Sie mit einem Zertifikat geschützt sind und nur der Inhaber des Zertifikats sie ändern kann. So wird gewährleistet, dass der Angreifer sie nicht manipulieren oder austauschen kann.

Bei weiteren Verbesserungen der Engine geht es hauptsächlich um Performance Steigerung. Ein Beispiel ist die fortsetzbare Online-Index-Neuerstellung. Bei großen Tabellen kann eine Index -Erstellung (wird durchgeführt zur verbesserten Performance bei Suchanfragen) sehr lange dauern und wenn ein Fehler passiert und der Prozess abgebrochen wird, musste bis jetzt der ganze Prozess erneut durchgeführt werden. Ab dem SQL Server 2017 wird der Vorgang dort fortgesetzt, wo er nach dem Fehler aufgehört hat.

Weitere Verbesserungen wie adaptive Abfrageverarbeitung, Adaptive Joins und Feedback zur Speicherzuweisung im Batchmodus bringen Performance Steigerungen bei den Abfragen.

 

Machine Learning

Die automatische Datenbankoptimierung überwacht und analysiert fortlaufend die Datenbank, lernt (Maschinen Learning) und identifiziert potenzielle Verbesserungen oder künftige Probleme. Dieser Prozess sucht nach optimalen Ausführungsplänen und überwacht nach jeder Änderung fortlaufend die Leistung.

Weitere Verbesserungen erfolgten im Bereich Massenzugriff wie SELECT INTO und BULK INSERT. Bei SELECT INTO ist es jetzt möglich, eine explizite Dateigruppe als Standard Dateigruppe bei der Abfrage festzulegen.

Integration Services

Bei den SQL Server Integration Services sind die wichtigsten Neurungen – neben der bereits erwähnten Linux-Integration – die Scale-Out Funktion und die Unterstützung von OData-Datenquellen.

Die Scale-Out Funktion ermöglicht die Ausführung von SSIS Paketen über mehrere Knoten (Cluster). So ist es möglich, die Beladung parallel über mehrere Server zu verteilen. Die Ausführung wird von einem Master Server überwacht. Einen ähnlichen Ansatz findet man im Bereich Big Data bei Hadoop Clustern, wenn die Verarbeitung von einem Map-Reduce Job über mehrere (auch tausende) Cluster verteilt wird.

Außerdem ist es nun möglich, in SSIS einen OData Feed von Microsoft Dynamics AX und CRM als Konnektor zu verwenden.

Analysis Services 

Wie wir schon im Beitrag zu multidimensionalen vs tabellarische Datenbank-Modellen beschrieben, war das multidimensionale Modell bis zum SQL Server 2016 Standard. Ab dem SQL Server 2017 ist die Standardinstallationsoption für Analysis Services der tabellarische Modus. Das ist auch meiner Meinung nach die Zukunft der Datenanalyse. Künftig werden DAX und M-Abfragen, die zum tabellarischem Modus gehören, mehr unterstützt und der DAX-Editor ist jetzt in den SQL Server 2017 Data Tools fest integriert.

Außerdem unterstützen die Analysis Services jetzt den Kompatibilitätsgrad 1400 für tabellarische Modelle.

Reporting Services 

Reporting Services sind nicht mehr Bestandteil des SQL Server Installationspakets und müssen separat runtergeladen und installiert werden. Diese Änderung geht mit der Bereitstellung des Power BI Report Servers einher. Somit erhält der Anwender die Möglichkeit, sowohl den Report Server oder aber auch den Power BI Report Server losgelöst vom SQL Server und somit auch von der verwendeten SQL Server Version zu nutzen.

Machine Learning Services

Machine Learning gewinnt immer mehr an Bedeutung. Es hängt mit der Entwicklung von künstlicher Intelligenz (KI) zusammen. Wie bereits erwähnt ist es nun möglich, CLR (.NET) Assemblies im SQL Server auszuführen. Dies war der erste Schritt zur Ausführung komplizierter Berechnungen im SQL Server. Bei KI und Machine Learning haben sich aber R oder Python als Standard durchgesetzt.

Python ist eine universelle Programmiersprache und für die meisten Betriebssysteme (Linux, Windows, Mobile Applikationen …) frei einsetzbar. Sie findet Einsatz in Webanwendungen (z.B. Odoo), in Geräten wie 100 Dollar Laptop oder, wegen der großen Anzahl an guten Bibliotheken, auch in der Wissenschaft.

R ist ebenfalls eine freie Software und wurde von Anfang an für statistische Berechnungen konzipiert. Da Machine Learning hauptsächlich statistische Methoden wie Regression oder Klassifizierung anwendet, ist R bei Maschinen Learning ein wichtiger Bestandteil.

Außerdem hat Microsoft im Jahr 2015 die Firma Revolution Analytics gekauft, die Schöpfer der Analyse Plattform Revolution R. Neben Microsoft setzen auch weitere namhafte Hersteller wie Oracle, SAP, Hewlett-Packard und andere auf R.

Im SQL Server 2017 ist jetzt neben R (seit Version 2016) auch Python fest integriert. Beide Sprachen müssen aber separat als SQL Server – Maschine Learning Dienste installiert werden. Im Gegensatz zu den Reporting Services ist die Installationsroutine aber mit der des SQL Servers verknüpft. Daneben ist es auch möglich, einen eigenständigen Machine Learning-Server, ohne Datenbank Engine Nutzung von Python und R, zu nutzen. Mit den Machine Learning Diensten werden R- und Python-Bibliotheken installiert, mit denen die verschiedene Maschinen Learning Algorithmen wie Regressionen und Klassifizierungen durchgeführt werden können. Außerdem ist es möglich, auch direkt in T-SQL eine Predict Funktion auszuführen.

Fazit

Mit dem SQL Server 2017 zeigt Microsoft endgültig, dass nicht nur Produkte wie Office, sondern auch Produkte im Server-Umfeld zur Multiplattform dazugehören. Der Benutzer hat die Wahl zwischen verschiedenen Betriebssystemen, der Cloud oder der Ausführung in einem Docker Container.

Die zentralen Themen sind Sicherheit und Maschinen Learning, nicht nur in der Azure Cloud, sondern auch in der On Premise Lösungen. Machine Learning ist jetzt auch in der Datenbank Engine integriert und überwacht den Server im Hinblick auf optimale Leistung und Problem-Vorhersage oder -Vermeidung.

Das perfekte Datenbank-Modell – Multidimensional vs Tabellarisch

Heute werden wir uns in einige technische Details der Business Intelligence (BI) Infrastruktur stürzen. Was für den Endnutzer im Verborgenen bleibt, ist für den BI-Experten eine wichtige Frage auf der Suche nach der richtigen Lösung. Er muss sich auf der Datenbank-Ebene zwischen zwei komplett verschiedenen Ansätzen entscheiden: dem multidimensionalen und dem tabellarischen Modell. Diese Frage ist beim Start jedes BI Projektes essenziell, denn es ist später nicht möglich, auf ein anderes Modell umzusteigen.

Die Frage, welches Modell besser geeignet ist, ist nicht einfach zu beantworten, sondern hängt von verschiedenen Faktoren ab. Daher betrachten wir heute beide Modelle im Detail.

Entwicklung und Funktionsweise der Modelle

Das Multidimensionale Model OLAP (Online Analytical Processing) wurde schon im Jahr 1998 als ein Teil des SQL Server 7 eingeführt. Eine richtige Verbreitung fand es aber erst mit dem SQL Server 2000, als Microsoft Analysis Services 2000 einführte. Seitdem wurden Multidimensionale Analyse Services bis zur Version „Analysis Services 2016“ stets entwickelt.

Das Multidimensionale Model basiert auf Cubes, Dimensionen und Measures oder Fakten. Die Datenmodellierung erfolgt mit Stern- oder ggf. einem Schneeflocke-Schema. Für jemanden aus dem klassischen Datenbank Umfeld muss beim Einstieg in die multidimensionale Modellierung ein Umdenken stattfinden, was die Fakten und Dimensionen angeht. Während Daten in OLTP (Online-Transaction-Processing) Quellsystemen quer durch das System verteilt sind und das Datenmodell sehr oft einem Spinnennetz ähnelt, sollten Daten in Source Systemen, die als Data–Source für einen Cube dienen, in Fakten und Dimensionen aufgeteilt werden, optimalerweise in einem Stern-Schema.

Bei der Daten-Modellierung sollte man stets die Logik der multidimensionalen Modelle im Hinterkopf behalten. Fakten sind Fakten oder Zahlen und sie werden während des Cube-Processing voraggregiert. Dimensionen beinhalten meistens Attribute, die Fakten beschreiben.
Dimensionstabellen sollten klein gehalten werden – je größer eine Dimensionstabelle ist, umso schlechter ist die Performance beim Cube Processing.

Bevor die Daten aus einem Cube konsumiert werden können, muss der Cube aufbereitet -„geprocessed“ – werden. Bei dem Process wird „SELECT DISTINCT “ auf den Dimensionstabellen ausgeführt und Zahlen werden in Faktentabellen voraggregiert.  Aggregationen werden in der multidimensionalen Struktur in Zellen an Koordinaten gespeichert, die durch die Dimensionen angegeben sind. Bei Dimensionen ist bei ca. 2 Mrd. Elementen Schluss. Das heißt, ein Cube kann nicht mehr als 2 Mrd. Elemente in einem Dimensionsattribut beinhalten.

Trotz dieser Einschränkung ist das multidimensionale Modell sehr ausgereift und skalierbar. Das zeigt auch ein Beispiel der Firma Yahoo, bei der ein Cube von ca. 24 TB Größe aus einem 2 PB Hadoop Cluster mit täglicher Beladung von 135 GB zum Einsatz kommt.

Ab dem SQL Server 2012 wurde ein neues Modell eingeführt, das Tabular Model. Das tabellarische Modell basiert auf In-Memory oder xVelocity Technologie, die im SQL Server 2012 auch für relationale Datenspeicherung eingeführt wurde. Diese Technologie benutzt speicheroptimierte Datenspeicherung, den sogenannte Columnstore Index. Daten werden in der Datenbank nicht als Zeilen („Rows“) sondern als Spalten („Columns“) gespeichert. Bei der klassischen Datenbank werden alle Werte in Zeilen gespeichert. Wenn ein Attribut in eine Tabelle mehrmals vorkommt, wird es auch mehrmals gespeichert. Einzelne Zeilen werden dann in einer Datenkette gespeichert. Diese Datenspeicherung ist historisch bedingt – wenn Daten auf ein magnetisches Band oder einer Festplatte sequentiell gespeichert wurden, mussten Sie auch sequentiell gelesen werden also „row by row“.

Bei dem Columnstore Index werden die Daten in einzelne Spalten aufgeteilt und es werden nur eindeutige („distinct“) Werte einer Spalte in der Datenbank gespeichert. Einzelne Spalten werden dann im Arbeitsspeicher (RAM) miteinander verknüpft.

Daten müssen nicht sequentiell gelesen werden, sondern der Datenzugriff erfolgt direkt über die Adresse im Arbeitsspeicher (RAM). Deswegen heißt diese Technologie auch In-Memory Datenverwaltungstechnologie.

Das bringt bis zu hundertfache Performance Steigerung  und bis zu siebenfacher Datenkomprimierung gegenüber klassischer Datenbank Technologie.

Wie der Name schon sagt ist der Arbeitsspeicher der Hauptbestandteil der Technologie. Entsprechend ist zur Nutzung dieser Technologie genügend RAM erforderlich.

Soviel zur Entstehung und Funktionsweise der Technologien die hinter beiden Modellen steckt. Zeit, sich mit den unterschiedlichen Features zu beschäftigen.

Wie schon erwähnt ist das multidimensionale Modell sehr ausgereift und wird ab Version 2012/2014 eigentlich nicht mehr weiter entwickelt, da alle wichtigen Features schon enthalten sind. Deswegen gibt es auch nicht viele Unterschiede zwischen Versionen 2012/2014 oder 2016.

Ganz anders ist die Situation bei dem tabellarischen Modell. Das Tabular Model wurde erst mit dem SQL Server 2012 eingeführt und bis Version 2017 stets weiterentwickelt. Entsprechend gibt es große Unterschiede zwischen den Versionen. Daher spielt die genutzte Version des SSAS Server im Unternehmen eine große Rolle bei der Entscheidung, welches Modell für den Business Intelligence Einsatz besser geeignet ist.

Ein weiterer wichtiger Punkt bei der Entscheidung ist der Kompatibilitätsgrad eines Modells. Der Kompatibilitätsgrad bezieht sich auf release-spezifische Verhalten in der Analysis Services-Engine. Also beispielsweise, welche Datenquellen unterstützt werden, welche Funktionen in eingebauten Skript Sprachen zu Verfügung stehen und welche Berechnungen durchgeführt werden können.  Der Kompatibilitätsgrad hat auch Auswirkung auf die Leistung der Queries.

Grob verglichen können wir sagen, dass Tabular 2012 und 2014 eine relativ ähnliche Funktionalität anbieten. Dann gibt es einen großen technologischen Sprung zur Version 2016 und zwischen Version 2016 und 2017 gibt es wieder nur kleine Unterschiede. Daher vergleichen wir die Versionen 2012/2014 und 2016/2017 mit dem multidimensionalen Modell.

Hier sind die hauptsächlichen Unterschiede der Modelle im direkten Vergleich:

 

MultidimensionalTabellarisch
2012/2014
Tabellarisch
2016/2017
Many to Many Relationships JaNeinJa
Writeback
(Benutzer kann in die SSAS Database schreiben)
JaNeinNein
Translations
(Metadata und Attribute
können in verschiedenen
Sprachen ausgegeben werden)
JaNeinJa
Row Level (Cell) Security
(Zugriff zu Daten durch Object
Level Security gesteuert)
JaNeinJa
Unregelmäßige Hierarchien
(nicht dieselbe Anzahl von übergeordneten Elementen)
JaNeinJa
Parent / Child Hierarchien
(nicht dieselbe Anzahl von
übergeordneten Elementen)
JaDAX FunktionDAX Funktion
Role-Playing Dimensionen
(Dimension mit mehreren
Bedeutungen)
JaDAX FunktionDAX Funktion
Display Folders
(um die Measures
besser zu organisieren)
JaMit Add-inJa
Drillthrough
(um die Detail Data
zu sehen)
JaBIDS Helper Add-in2016: BIDS Helper Add-in
2017: Ja

 

Zuletzt schauen wir uns noch die Datenquellen für beide Modelle an. Bei Datenquellen gibt es keine großen Unterschiede zwischen dem Tabular und Multi-Dimensional Model.

Für beide Modelle können zwei verschiedene Modi ausgewählt werden. Im ersten Modus werden Daten aus der Quelle zwischengespeichert, „gecached“. Dieser Modus ist natürlich schneller.

Im Multi-Dimensional Model heißt dieser Modus MOLAP. Dies ist der Standard Modus, bei dem die Daten aus dem Quellsystem beladen, voraggregiert und in einer speziellen multidimensionalen Form im Analysis Services gespeichert werden.

Im zweiten Modus werden Daten aus dem Quellsystem nicht zwischengespeichert und das Modell und alle Berechnungen werden „on the fly“ direkt auf dem Quellsystem durchgeführt.

Im Multi-Dimensional Model heißt diese Modus ROLAP. Dabei werden keine Daten aus dem Quellsystem gezogen, sondern es werden lediglich zusätzliche Datenbankobjekte, wie Indexed Views, generiert, auf denen die Berechnungen im Cube basieren. Im Tabular Model heißt dieser Modus Direct Query Modus.

Das Multi-Dimensional Model unterstützt als Datenquelle fast alle relationalen Datenbanken plus MS Access.

Das Tabular Model unterstützt hingegen im Cached Modus deutlich mehr Datenquellen. Neben vieler relationaler Datenquellen auch Analysis Services Cube, Excel, csv, Text Files, Data Feeds und Office Database Connection Files.

Das Direct Query Model arbeitet nur mit relationalen Datenbanken ausfolgenden Systemen: SQL Server, Azure SQL Database, Oracle, und Teradata.

Abschließend können wir also festhalten, dass beide Systeme komplett unterschiedlich arbeiten, obwohl am Ende aus der Sicht des Endbenutzers die gleichen Daten rauskommen.

Das Tabular Model ist die neuste Entwicklung im BI-Bereich und kann mit seiner vielseitigen Performance durchaus überzeugen. Allerdings gibt es große Unterschiede zwischen den Versionen. Während das multidimensionale Modell dem tabellarischen Modell in der Version 2012/2014 noch deutlich überlegen ist, bietet das Tabular Model ab der Version 2016 gut Paroli.

Nachdem wir nun die technische Seite beleuchtet haben, werde ich mich in einem meiner nächsten Beiträge mehr mit den Unterschieden auf Benutzerseite auseinandersetzen und darauf eingehen, wie die Daten in beiden Systemen modelliert und wie Berechnungen implementiert werden.