Im folgenden Blogbeitrag wird gezeigt, wie man DeltaMaster mit Azure Analysis Services als Quelle verwenden kann. Dabei wird auf den Aufbau der Architektur sowie die Verwaltung und Wartung des Systems eingegangen. Grundlegende Kenntnisse über Aufbau und Modellierung einer OLAP-Datenbank im Tabular-Modus (im Gegensatz zu multidimensionalen OLAP-Datenbanken) werden vorausgesetzt, da die Modellierung des Tabular-Modells nicht Bestandteil des Beitrags ist. Des Weiteren wird auch eine Lösung für eine kosteneffiziente Nutzung von Azure Analysis Services aufgezeigt.

Übersicht der verschiedenen Cloud Ansätze (PaaS vs. IaaS)

Azure Analysis Services ist ein von Microsoft Azure angebotener Service, der gemäß der PaaS-Struktur (Platform as a Service) die SQL Server Analysis Services (SSAS) ersetzt. Allerdings hat sich Microsoft dazu entschieden, in der Cloud lediglich Datenbanken im Tabular-Modus zu unterstützen. Für multidimensionale OLAP-Modelle in der Cloud wird eine Azure VM benötigt, auf der dann gemäß der IaaS-Struktur (Infrastructure as a Service) SSAS auf herkömmliche Weise installiert und verwendet werden kann. Vorteil des PaaS Ansatzes gegenüber dem IaaS-Ansatzes sind die integrierte Skalierbarkeit von Azure und die automatische Wartung des Services von Microsoft.
Ziel ist es im Folgenden, eine funktionierende, sich selbst aktualisierende Umgebung aufzubauen, um DeltaMaster Berichte auf Basis von Azure Analysis Services zu erstellen.

Analysis Services Instanz erstellen

Zum Erstellen von Azure Ressourcen ist eine Microsoft Azure Subscription notwendig. Microsoft bietet eine 30-tägige Testsubscription mit 170€ Guthaben an.
Die Verwaltung der Azure-Ressourcen findet im Azure Portal statt. Dort wird eine neue Ressource über den Button „Create a resource“ erzeugt:

Abbildung 1: “Create a resource” – Button

Unter allen Services, die im Azure Marketplace angeboten werden, kann nun nach Analysis Services gesucht werden:

Abbildung 2: Create Azure resource

Dieser Service erzeugt eine neue Azure-Analysis-Services-Instanz. Das ist ein Server, auf dem verschiedene Datenbanken im Tabular Mode gespeichert werden können.
Im Dialog zum Erstellen der Instanz können einige Einstellungen vorgenommen werden:

Abbildung 3: Create Azure Analysis Services instance

Server name:

• Es handelt sich um ein Freitextfeld.
• Der Name muss komplett kleingeschrieben, alphanumerisch und zwischen 3 und 63 Zeichen lang sein.
• Der Name bildet einen Teil des Connection Strings zum Server. Daher ist es zu empfehlen, diesen nicht zu lang zu wählen.

Subscription:

• Eintrag sollte standardmäßig ausgewählt sein.
• Bei mehreren unterschiedlichen Subscriptions (z.B. DEV/PROD) ist ggf. Änderung notwendig.

Resource Group:

• Resource groups sind Container, die mehrere Ressourcen enthalten können.
• Es ist sinnvoll, zusammengehörige Ressourcen in der gleichen Resource group zu erstellen.
• Entweder eine vorhandene Resource group aus Drop-down-Menü auswählen oder direkt über den Link „Create new“ eine neue Resource group erstellen:

Abbildung 4: Create new Resource group

Location :

• Für die beste Verbindung die für die Nutzung nächstgelegene Region wählen.
• Beispiele: West Europe ist in Amsterdam, North Europe ist in Dublin lokalisiert.
• Replizierbarkeit in andere Regionen (Scale out) ist auswählbar.

Pricing tier:

• Für Testzwecke wurde hier D1 für Developer als günstigste Option gewählt.
• Im Nachhinein kann dies noch den Ansprüchen angepasst werden (Scale up)

Administrator:

• Dies ist standardmäßig der angemeldete User.
• Jeder User des Azure Active Directory kann ausgewählt werden.

Storage key expiration:

• Hiermit kann gesteuert werden, in welchem Zeitraum der Speicherschlüssel wiederhergestellt werden muss.
• für Testzwecke ist „never“ ausreichend

„Create“ erstellt die definierte Analysis Services Server-Instanz. Der Aufruf im Azure Portal zeigt die wichtigsten Eckdaten des Servers inklusive der auf dem Server implementierten Modelle:

Abbildung 5: Azure Analysis Services im Azure Portal

Hierbei ist vor allem der „Server name“ wichtig. Dieser kann direkt aus dem Azure Portal kopiert werden und im SQL Server Management Studio getestet werden:

Abbildung 6: Verbindung zu Azure Analysis Services

Es ist zu beachten, dass die Anmeldung nicht mit der Standard-Windows-Authentifizierung funktioniert, da die Berechtigungen über das Azure Active Directory gesteuert werden. Daher ist die Anmeldung über das Microsoft-Konto notwendig.
Standardmäßig ist die Firewall der Analysis Services Instanz nicht aktiviert. Diese sollte als zusätzliche Sicherheit in den Einstellungen der Instanz aktiviert werden:

Abbildung 7: Aktivieren der Firewall

Erstellen einer Datenbank in Azure Analysis Services

Azure Analysis Services kann mit einer Vielzahl unterschiedlicher Datenquellen verbunden werden. Im Folgenden wird beispielhaft die Anbindung einer „On-premises“-Excel-Tabelle sowie einer Azure SQL-Datenbank gezeigt. „On-premises“ bedeutet in diesem Kontext lokal im Unternehmen gespeichert und nicht in der Cloud. Azure Analysis Services unterstützt auch eine Kombination verschiedener Datenquellen.

„On-premises“-Excel-Datenquelle

Für die Modellierung der Datenbanken werden die SQL Server Data Tools des Visual Studios benötigt. In der Community Version des Visual Studios ist ggf. die Nachinstallation der „Microsoft Analysis Services Projects“ notwendig. Mit der Hilfe dieser Erweiterungen kann entweder eine bestehende Datenbank importiert oder eine neue erstellt werden:

Abbildung 8: Erstellen einer neuen Datenbank für Azure Analysis Services

Beim Erstellen neuer Projekte muss sich mit der Analysis Services Instanz verbunden werden und ein Kompatibilitätsgrad gewählt werden. Derzeit verwendet Azure den Kompatibilitätsgrad 1400, der dem SQL Server 2017 entspricht:

Abbildung 9: Designer für tabellarische Modelle

Die Datenquellen können nun analog zu einem „On-premises“-Analysis Services Server hinzugefügt werden.
Rechtsklick auf Datenquellen -> Neue Datenquelle -> gewünschte Excel-Datei aus dem Dateisystem auswählen

Abbildung 10: Datenquellen im Visual Studio

Im Folgenden müssen die Credentials des Windows-Kontos hinterlegt werden, das die Daten laden soll.

Abbildung 11: Credentials für Datenquelle hinterlegen

Zu Testzwecken kann hier das aktuell verwendete Konto verwendet werden, in einem produktiven Betrieb bietet sich jedoch ein technischer User an, dessen Kennwort sich nicht ändert.
Für wiederhergestellte Datenbanken kann diese Einstellung auch problemlos in den Anmeldeinformationen der Verbindungseigenschaften der Datenquelle gesetzt werden:

Abbildung 12: Datenquellen im Management Studio

Abbildung 13: Datenquellen im Management Studio – Eigenschaften

Nach Anbindung der Datenquelle kann daraus eine Tabelle importiert werden. Hierzu genügt ein Rechtsklick auf die neu angelegte Datenquelle -> Neue Tabellen Importieren -> gewünschtes Tabellenblatt auswählen.
Das Verarbeiten dieser Tabelle funktioniert allerdings nur, wenn es ein lokales Data Gateway gibt. Dieses muss zunächst lokal installiert werden, im Kontext eines Users, der im Azure Active Directory registriert ist:

Abbildung 14: Installation lokales Data Gateway – Registrierung

Abbildung 15: Installation lokales Data Gateway – Anmeldung

Abbildung 16: Installation lokales Data Gateway – Konfiguration

Besonders zu beachten ist, dass das Gateway in der gleichen Region erstellt wird, wie die Ressource, mit der es verknüpft werden soll. Wird dies falsch eingestellt, muss entweder die Installation wiederholt werden oder das Gateway aus den „Power Apps“ entfernt werden:

Abbildung 17: Gateway aus Power Apps entfernen

Nach erfolgreicher lokaler Installation muss dieses Gateway in der Cloud registriert werden. Hierfür gibt es die Ressource „On-premises data gateway“:

Abbildung 18: Erstellung On-premises data gateway

Beim Erzeugen der Azure Ressource muss die lokale Installation ausgewählt werden. Diese kann nur gefunden werden, wenn die folgenden Bedingungen erfüllt sind:

• Die Gateway-Installation verwendet dieselbe Region wie die Gateway-Ressource, die Sie er-stellen möchten.
• Die Gateway-Installation ist nicht mit einer anderen Azure-Gateway-Ressource verknüpft.
• Die Gateway-Installation ist mit demselben Azure-Konto verknüpft, das Sie zum Erstellen der Gateway-Ressource verwenden.
• Ihr Azure-Konto gehört zu einem einzigen Azure Active Directory-Mandanten oder -Verzeichnis und ist das gleiche Konto, das für die Gateway-Installation verwendet wurde.

Der letzte notwendige Schritt ist, die Azure-Gateway-Ressource mit der Analysis Services Instanz zu verbinden. Dies ist eine Einstellung im Azure Portal, in der Analysis Services Instanz unter dem Punkt „On-Premises Data Gateway“:

Abbildung 19: On-premises Data Gateway im Azure Portal

Für Azure SQL-Datenbanken:

Für Datenquellen innerhalb Azure ist kein Data Gateway notwendig.
Zunächst wird beispielhaft eine SQL-Datenbank auf einem neuen Azure SQL Server erstellt:

Abbildung 20: Azure SQL-Datenbank erstellen

Abbildung 21: Azuer SQL-Datenbank Konfiguration

Es wird davon ausgegangen, dass diese Datenbank regelmäßig mit aktuellen Daten befüllt wird.

Firewall-Einstellungen

Azure-Ressourcen haben standardmäßig eine Firewall implementiert. Wenn diese aktiviert ist, blockt sie Zugriffe von außen, falls sie nicht autorisiert sind. Um durch die Firewall hindurch kommunizieren zu können, müssen Ausnahmeregeln definiert werden. Im Fall von Azure Analysis Services kann die fehlende Firewallregel bei entsprechender Berechtigung des ausführenden Kontos direkt hinzugefügt werden:

Abbildung 22: Neue Firewallregel für Azure Analysis Services-Server

Für den Azure SQL Server kann diese Einstellung über die Master-Datenbank gesetzt werden:

Abbildung 23: Firewallregel für Azure SQL-Server

In beiden Fällen ist es möglich, die Firewallregeln auch im Azure Portal in der Ressource zu pflegen.

Verbindung zur Datenbank mit DeltaMaster

Wenn die Datenbank erstellt und verarbeitet wurde sowie der Zugriff auf die Azure Analysis Services Instanz funktioniert, kann DeltaMaster sich mit der Datenbank verbinden. Hierfür kann die reguläre Schnittstelle für Microsoft SQL Server Analysis Services in Verbindung mit der Windows-Authentifizierung verwendet werden:

Abbildung 24: DeltaMaster Verbindung

Es kann vorkommen, dass folgende Fehlermeldung erscheint:

Abbildung 25: DeltaMaster Fehlermeldung

Das liegt daran, dass die Analysis Services-Bibliotheken nicht mit der Azure Analysis Services-Version kompatibel sind. Das kann behoben werden, indem der Inhalt des Unterordners „AS 13.0“ (alternativ auch eine höhere Version) in den DeltaMaster 6 Installationsordner kopiert wird.

Automatisierung

Nachdem es jetzt eine funktionierende Umgebung gibt, muss diese noch für den täglichen Gebrauch vorbereitet werden, damit die Daten in der Azure Analysis Services-Instanz immer aktuell sind und die Kosten so gering wie möglich gehalten werden.

Process

Der Process der Azure Analysis Services-Datenbank funktioniert genau wie der Process einer „On-premises“ Datenbank im Tabular Modus. Dieser kann zum Beispiel über das SQL Server Management Studio direkt über den Objekt-Explorer mit der Funktion Datenbank verarbeiten angestoßen werden:

Abbildung 26: Verarbeitung einer Datenbank im Tabular Modus

Dies kann auch direkt im Management Studio geskriptet werden. Das Ergebnis ist ein String im JSON-Format, der im SQL Server Management Studio als XMLA ausgeführt wird:

{
  "refresh": {
    "type": "full",
    "objects": [
      {
        "database": "Source_Excel"
      }
    ]
  }
}

Auch über Powershell kann der Process der Datenbank angestoßen werden:

Das ist eine sehr gute Methode, um den Process manuell anzustoßen. Der automatisierte Process mit Powershell, zum Beispiel über die Windows Aufgabenplanung, führt allerdings aufgrund der Multi-Faktor-Authentifizierung des Azure Active Directories zu Problemen, da jeder Aufruf sich zunächst mit Azure verbinden muss.
Es ist daher komfortabler, die Automatisierung direkt in Azure zu definieren und anzustoßen, um nicht von außerhalb der Cloud Umgebung eine Verbindung aufbauen zu müssen.
Microsoft schlägt drei unterschiedliche Umsetzungsmöglichkeiten vor:

• Aktualisieren mit Azure Automation
  – Dies verwendet den Powershell-Ansatz und umgeht das Authentifizierungsproblem mit einem eigens erstellten Dienstprinzipal, welches in ein Automation Runbook eingebettet wird.
• Aktualisieren mit Logic Apps
  – Ein REST-API-Aufruf wird in einer Logic App gespeichert, die dann von der Azure Data Factory getriggert wird.
• Aktualisieren mit REST-API
  – REST-API Aufruf wird direkt über Azure Data Factory getriggert.

Die Aktualisierung mit REST-API hat sich als sehr einfach zu definieren herausgestellt und funktioniert sehr robust. Es werden dafür weder Automation Accounts noch Client-Zertifikate gebraucht.
Zunächst wird eine neue Data Factory erstellt:

Abbildung 27: Erstellen Azure Data Factory

Über den Link „Author & Monitor“ in der Overview der neuen Ressource kann eine neue Pipeline definiert werden. In dieser kann in einer Web Activity der Process direkt über die Refresh Function der REST-API aufgerufen werden:

Abbildung 28: Azure Data Factory – Overview

Die Refresh Methode wird für die Datenbank über die URL aufgerufen.
Im Body steht der Aufruf des Processes, wie er auch aus dem Management Studio geskriptet werden kann. Zusätzliche Optionen sind möglich, wie zum Beispiel das Hinzufügen mancher Objekte, um einen inkrementellen Process zu ermöglichen.
Die Authentifizierungsmethode „MSI“ steht für Managed Identity Application und führt die Azure Factory Pipeline in einem eigenen User-Kontext aus. Dieser User setzt sich zusammen aus:

ApplicationID und TenantID können über die Properties der Data Factory abgerufen werden:

Abbildung 29: Azure Data Factory – Properties

Der resultierende User kann nun dem Azure Analysis Services Server als Administrator hinzugefügt werden:

Abbildung 30: Azure Analysis Services-Servereigenschaften

Die Data Factory kann nun über getestet oder über geplant werden.

Firewall Freischaltungen müssen für die Data Factory im Azure Analysis Services Server noch eingerichtet werden:

Abbildung 31: Firewallregel für Azure Data Factory

Jetzt kann der Process über die Data Factory beliebig gestartet werden.
Der Refresh Befehl der REST-API geschieht asynchron. Das bedeutet, die Data Factory Pipeline endet direkt nach erfolgreichem Anstoßen des Process. Die Mitteilung über Erfolg oder Scheitern des Processes kann hier nicht eingesehen werden.
Greg Galloway hat für dieses Problem eine Lösung bereitgestellt, die den gerade beschriebenen Ansatz um eine korrekte Fehler- bzw. Erfolgsmeldung erweitert. Dieser Ansatz ist komplett parametrisierbar und kann hier eingesehen und heruntergeladen werden.

Kostenoptimierung

Im Vergleich zu Azure SQL-Datenbanken und -Servern sind Azure Analysis Services-Instanzen sehr teuer. Es ist daher umso wichtiger, sich Gedanken um die effiziente Nutzung des Services zu machen. Im Gegensatz zu Azure SQL Servern lassen sich Azure Analysis Services Server komplett pausieren und verursachen in der Zeit, in der sie pausiert sind, auch keine Kosten.
Auch Pausieren und Starten der Instanz können mit REST API über die Data Factory automatisiert werden.
Der REST Befehl für Pausieren ist:

POST
{subscriptionId}/resourceGroups/{resourceGroupNa-me}/providers/Microsoft.AnalysisServices/servers/{serverName}/suspend?api-version=2017-08-01

Analog für das Fortsetzen:

POST
{subscriptionId}/resourceGroups/{resourceGroupNa-me}/providers/Microsoft.AnalysisServices/servers/{serverName}/resume?api-version=2017-08-01

Dies kann in der Data Factory wieder in einer Web Activity in der URL eingebettet werden:

Abbildung 32: Web Activity für Kostenoptimierung

Zu beachten ist hierbei, dass die Ressource nicht mehr der Region des Servers entspricht, sondern .
Außerdem gibt es für diesen Aufruf keinen Body mehr, daher wird als @null als dynamischer Inhalt hinterlegt.
Damit das Pausieren/Fortsetzen aus der Data Factory aufgerufen werden kann, muss für die MSI-Authentifizierung die Data Factory der Analysis Services-Instanz im Access control (IAM) als Contributor hinzugefügt werden:

Abbildung 33: Pipeline für Kostenoptimierung

Mit einer Pipeline für das Pausieren und einer für das Fortsetzen der Azure Analysis Services-Instanz kann nun genau gesteuert werden, wann die User Zugriff auf die Daten benötigen. So kann die Instanz zum Beispiel um 0:00 Uhr zum Process gestartet werden. Nach dem Process wird die Instanz wieder pausiert. Für die User könnte die Instanz dann um 8:00 Uhr wieder gestartet werden und um 20:00 Uhr wieder pausiert werden. Diese Implementierung würde bereits fast 50% der Kosten der Instanz einsparen.
Dazu kommt noch die typische Skalierbarkeit von Cloud Ressourcen. Microsoft hat hierfür eine automatisierte Lösung veröffentlicht, die es ermöglicht, abhängig vom Workload das passende Pricing Tier zu wählen.

Zusammenfassung

Durch die in diesem Blogbeitrag beschriebenen Schritte wurde eine komplette Cloud-Umgebung für den produktiven Betrieb geschaffen. Die Architektur dieser Umgebung lässt sich wie folgt beschreiben:

Abbildung 34: Architekturskizze

Azure wird stetig weiterentwickelt. Daher kann es sein, dass sich in Zukunft manche Ressourcen in ihrer Funktionalität oder ihrer Erscheinung im Azure Portal ändern. Außerdem kann es sein, dass weitere Ressourcen, Dienste oder Schnittstellen mit Azure verwendbar sein werden, die die initiale Bereitstellung sowie die Wartung im laufenden Betrieb vereinfachen werden.