Automatisierte Enterprise BI-Instanz

Dieses Beispiel befasst sich mit der Vorgehensweise für inkrementelles Laden in einer ELT-Pipeline (Extrahieren, Laden und Transformieren). Sie verwendet Azure Data Factory, um die ELT-Pipeline zu automatisieren. Die Pipeline verschiebt die neuesten OLTP-Daten inkrementell aus einer lokalen SQL Server-Datenbank in Azure Synapse. Transaktionsdaten werden in ein tabellarisches Modell für die Analyse transformiert.

Aufbau

Laden Sie eine Visio-Datei dieser Architektur herunter.

Diese Architektur basiert auf der Architektur unter Enterprise BI mit Azure Synapse, aber sie weist einige zusätzliche Funktionen auf, die für Data Warehousing-Szenarien für Unternehmen wichtig sind.

• Automatisierung der Pipeline mithilfe von Data Factory.
• Inkrementelles Laden.
• Integration mehrerer Datenquellen.
• Laden von binären Daten wie räumliche Daten und Bilder.

Workflow

Die Architektur umfasst folgende Dienste und Komponenten.

Datenquellen

Lokaler SQL Server. Die Quelldaten befinden sich in einer lokalen SQL Server-Datenbank. So simulieren Sie die lokale Umgebung. Die OLTP-Beispieldatenbank von Wide World Importers wird als Quelldatenbank verwendet.

Externe Daten. Ein gängiges Szenario für Data Warehouses ist die Integration mehrerer Datenquellen. Diese Referenzarchitektur lädt ein externes Dataset, das die Stadtbevölkerung nach Jahr enthält, und integriert es in die Daten aus der OLTP-Datenbank. Auf der Grundlage dieser Daten können Sie verschiedene Fragen beantworten. Beispiel: „Entspricht die Umsatzsteigerung in jeder Region dem Bevölkerungswachstum, oder ist sie unverhältnismäßig höher?“

Erfassung und Datenspeicherung

Blobspeicher. Blobspeicher wird als Stagingbereich für die Quelldaten vor dem Laden in Azure Synapse verwendet.

Azure Synapse: Azure Synapse ist ein verteiltes System für die Analyse großer Datenmengen. Es unterstützt massive Parallelverarbeitung (Massive Parallel Processing, MPP), die die Ausführung von Hochleistungsanalysen ermöglicht.

Azure Data Factory Data Factory ist ein verwalteter Dienst, der Datenverschiebung und Datentransformation orchestriert und automatisiert. In dieser Architektur koordiniert er die verschiedenen Phasen des ELT-Prozesses.

Analysen und Berichte

Azure Analysis Services: Analysis Services ist ein vollständig verwalteter Dienst, der Datenmodellierungsfunktionen ermöglicht. Das semantische Modell wird in Analysis Services geladen.

Power BI: Power BI ist eine Suite aus Business Analytics-Tools zum Analysieren von Daten für Einblicke in Geschäftsvorgänge. In dieser Architektur dient sie zum Abfragen des in Analysis Services gespeicherten semantischen Modells.

Authentifizierung

Microsoft Entra ID authentifiziert Benutzer*innen, die über Power BI eine Verbindung mit dem Analysis Services-Server herstellen.

Data Factory kann ebenso Microsoft Entra ID für die Authentifizierung bei Azure Synapse nutzen, indem ein Dienstprinzipal oder eine verwaltete Dienstidentität (Managed Service Identity, MSI) verwendet wird.

Komponenten

• Azure Blob Storage
• Azure Synapse Analytics
• Azure Data Factory
• Azure Analysis Services
• Power BI
• Microsoft Entra ID

Szenariodetails

Datenpipeline

In Azure Data Factory ist eine Pipeline eine logische Gruppierung von Aktivitäten zum Koordinieren einer Aufgabe – in diesem Fall das Laden und Transformieren von Daten in Azure Synapse.

Diese Referenzarchitektur definiert eine übergeordnete Pipeline, die eine Folge von untergeordneten Pipelines ausführt. Jede untergeordnete Pipeline lädt Daten in eine oder mehrere Data Warehouse-Tabellen.

Empfehlungen

Inkrementelles Laden

Beim Ausführen eines automatisierten ETL-oder ELT-Prozesses ist es am effizientesten, nur die Daten zu laden, die sich seit der vorhergehenden Ausführung geändert haben. Dies wird als inkrementeller Ladevorgang bezeichnet, im Gegensatz zu einem vollständigen Ladevorgang, bei dem alle Daten geladen werden. Zum Ausführen eines inkrementellen Ladevorgangs benötigen Sie eine Möglichkeit zum Identifizieren, welche Daten sich geändert haben. Der gängigste Ansatz ist die Verwendung eines Werts zum Erkennen von Änderungen mit oberem Grenzwert, d.h. der aktuelle Wert einer Spalte in der Quelltabelle – entweder einer datetime-Spalte oder einer eindeutigen Integer-Spalte – wird nachverfolgt.

Ab SQL Server 2016 können Sie temporale Tabellen verwenden. Hierbei handelt es sich um Tabellen mit Systemversionsverwaltung, die einen vollständigen Verlauf aller Datenänderungen beibehalten. Die Datenbank-Engine zeichnet den Verlauf aller Änderungen automatisch in einer separaten Verlaufstabelle auf. Sie können die Verlaufsdaten abfragen, indem Sie eine FOR SYSTEM_TIME-Klausel an eine Abfrage anhängen. Intern fragt die Datenbank-Engine die Verlaufstabelle ab, dies ist für die Anwendung jedoch transparent.

Temporale Tabellen sind hilfreich für Dimensionsdaten, die sich im Laufe der Zeit ändern können. Faktentabellen stellen in der Regel eine unveränderliche Transaktion wie einen Verkauf dar, und in diesem Fall ist das Beibehalten des Systemversionsverlaufs nicht sinnvoll. Stattdessen weisen Transaktionen normalerweise eine Spalte auf, die das Transaktionsdatum darstellt, das als Wasserzeichenwert verwendet werden kann. Beispielsweise enthalten in der OLTP-Datenbank von Wide World Importers die Tabellen „Sales.Invoices“ und „Sales.InvoiceLines“ das Feld LastEditedWhen, dessen Standardwert sysdatetime() ist.

Hier ist der allgemeine Ablauf für die ELT-Pipeline:

1. Verfolgen Sie für jede Tabelle in der Quelldatenbank den Trennzeitpunkt der Ausführung des letzten ELT-Auftrags nach. Speichern Sie diese Informationen im Data Warehouse. (Bei der Ersteinrichtung sind alle Zeiten auf „1.1.1900“ festgelegt.)

2. Während des Datenexportschritts wird der Trennzeitpunkt als Parameter an einen Satz von gespeicherten Prozeduren in der Quelldatenbank übergeben. Diese gespeicherten Prozeduren fragen alle Datensätze ab, die nach dem Trennzeitpunkt geändert oder erstellt wurden. Für die Sales-Faktentabelle wird die Spalte LastEditedWhen verwendet. Für die Dimensionsdaten werden temporale Tabellen mit Systemversionsverwaltung verwendet.

3. Wenn die Datenmigration abgeschlossen ist, aktualisieren Sie die Tabelle, in der die Trennzeitpunkte gespeichert werden.

Es ist auch hilfreich, eine Herkunft für jede ELT-Ausführung aufzuzeichnen. Für einen bestimmten Datensatz ordnet die Herkunft diesen Datensatz der ELT-Ausführung zu, bei der die Daten erzeugt wurden. Bei jeder ETL-Ausführung wird für jede Tabelle ein neuer Herkunftsdatensatz erstellt, der die Start- und Endladezeiten anzeigt. Die Herkunftsschlüssel für die einzelnen Datensätze werden in den Dimensions- und Faktentabellen gespeichert.

Aktualisieren Sie das Analysis Services-Tabellenmodell, nachdem ein neuer Datenbatch in das Warehouse geladen wurde. Siehe Asynchrones Aktualisieren mit der REST-API.

Datenbereinigung

Die Datenbereinigung sollte Teil des ELT-Prozesses sein. In dieser Referenzarchitektur ist die Tabelle mit der Stadtbevölkerung eine Quelle ungültiger Daten, in der einige Städte keine Bevölkerung aufweisen, da möglicherweise keine Daten verfügbar waren. Während der Verarbeitung entfernt die ELT-Pipeline diese Städte aus der Tabelle mit der Stadtbevölkerung. Führen Sie die Datenbereinigung in Stagingtabellen statt in externen Tabellen durch.

Externe Datenquellen

Data Warehouses fassen häufig Daten aus mehreren Quellen zusammen. Beispielsweise eine externe Datenquelle, die demografische Daten enthält. Dieses Dataset in Azure Blob Storage als Teil des Beispiels WorldWideImportersDW verfügbar.

Azure Data Factory kann mithilfe des Blob Storage-Connectors direkt aus Blob Storage kopieren. Der Connector erfordert jedoch eine Verbindungszeichenfolge oder eine Shared Access Signature, damit er nicht zum Kopieren eines Blobs mit öffentlichem Lesezugriff verwendet werden kann. Um dieses Problem zu umgehen, können Sie PolyBase zum Erstellen einer externen Tabelle über Blob Storage verwenden und die externen Tabellen dann in Azure Synapse kopieren.

Verarbeiten von umfangreichen Binärdaten

Beispielsweise weist in der Quelldatenbank die Tabelle mit Städten die Spalte „Ort“ auf, die den räumlichen Datentyp Geografie enthält. Da Azure Synapse den Typ Geografie nicht nativ unterstützt, wird dieses Feld während des Ladens in einen varbinary-Typ konvertiert. (Siehe Verwenden von Problemumgehungen für nicht unterstützte Datentypen.)

PolyBase unterstützt jedoch eine maximale Spaltengröße von varbinary(8000), was bedeutet, dass einige Daten möglicherweise abgeschnitten werden. Dieses Problem lässt sich umgehen, indem die Daten während des Exports wie folgt in Blöcke aufgeteilt und dann wieder zusammengefügt werden:

1. Erstellen Sie eine temporäre Stagingtabelle für die Standort-Spalte.

2. Teilen Sie die Standortdaten für jede Stadt in Blöcke von 8.000 Bytes auf. Dies ergibt 1 bis N Zeilen für jede Stadt.

3. Um die Blöcke wieder zusammenzusetzen, verwenden Sie den T-SQL-Operator PIVOT, um Zeilen in Spalten umwandeln und dann die Spaltenwerte für jede Stadt zu verketten.

Die Herausforderung besteht darin, dass jede Stadt je nach Größe der geografischen Daten in eine unterschiedliche Anzahl von Zeilen aufgeteilt wird. Damit der PIVOT-Operator funktioniert, muss jede Stadt die gleiche Anzahl von Zeilen aufweisen. Dazu wendet die T-SQL-Abfrage einige Tricks an, um die Zeilen mit leeren Werten aufzufüllen, sodass jede Stadt nach dem Pivot-Vorgang die gleiche Anzahl von Spalten aufweist. Die resultierende Abfrage erweist sich als wesentlich schneller als Durchlaufen der einzelnen Zeilen.

Der gleiche Ansatz wird für Bilddaten verwendet.

Langsam veränderliche Dimensionen

Dimensionsdaten sind relativ statisch, können sich jedoch ändern. Beispielsweise kann ein Produkt einer anderen Produktkategorie zugewiesen werden. Es gibt verschiedene Ansätze zur Handhabung von langsam veränderlichen Dimensionen. Ein gängiges Verfahren mit der Bezeichnung Typ 2 ist das Hinzufügen eines neuen Datensatzes bei jeder Dimensionsänderung.

Zur Umsetzung des Typ 2-Ansatzes erfordern Dimensionstabellen zusätzliche Spalten, die den effektiven Datumsbereich für einen bestimmten Datensatz angeben. Außerdem werden Primärschlüssel aus der Quelldatenbank dupliziert, daher muss die Dimensionstabelle über einen künstlichen Primärschlüssel verfügen.

Beispielsweise zeigt die folgende Abbildung die Tabelle Dimension.City. Die Spalte WWI City ID ist der Primärschlüssel aus der Quelldatenbank. Die Spalte City Key ist ein künstlicher Schlüssel, der während der Verarbeitung der ETL-Pipeline generiert wurde. Beachten Sie auch, dass in der Tabelle die Spalten Valid From und Valid To vorhanden sind, die den Bereich definieren, in dem die einzelnen Zeilen gültig waren. Das Valid To-Element aktueller Werte entspricht „9999-12-31“.

Der Vorteil dieses Ansatzes ist, dass historische Daten beibehalten werden, die für die Analyse nützlich sein können. Allerdings sind dabei auch mehrere Zeilen für dieselbe Entität vorhanden. Hier sehen Sie beispielsweise Datensätze, die WWI City ID = 28561 entsprechen:

Ordnen Sie jeden Sales-Fakt einer einzelnen Zeile in der Dimensionstabelle „City“ zu, die dem Rechnungsdatum entspricht.

Überlegungen

Diese Überlegungen beruhen auf den Säulen des Azure Well-Architected Frameworks, d. h. einer Reihe von Grundsätzen, mit denen die Qualität von Workloads verbessert werden kann. Weitere Informationen finden Sie unter Microsoft Azure Well-Architected Framework.

Sicherheit

Sicherheit bietet Schutz vor vorsätzlichen Angriffen und dem Missbrauch Ihrer wertvollen Daten und Systeme. Weitere Informationen finden Sie unter Übersicht über die Säule „Sicherheit“.

Für höhere Sicherheit können Sie Virtual Network-Dienstendpunkte zum Schützen von Azure-Dienstressourcen verwenden, indem diese ausschließlich auf Ihr virtuelles Netzwerk beschränkt sind. Der öffentliche Internetzugriff auf diese Ressourcen wird dadurch vollständig entfernt, sodass nur Datenverkehr aus Ihrem virtuellen Netzwerk zulässig ist.

Mit diesem Ansatz erstellen Sie ein VNET in Azure und dann private Dienstendpunkte für Azure-Dienste. Diese Dienste sind dann auf Datenverkehr aus diesem virtuellen Netzwerk beschränkt. Sie können auch über ein Gateway aus Ihrem lokalen Netzwerk darauf zugreifen.

Bedenken Sie dabei folgende Einschränkungen:

• Wenn Dienstendpunkte für Azure Storage aktiviert sind, kann PolyBase keine Daten aus Storage in Azure Synapse kopieren. Dieses Problem kann entschärft werden. Weitere Informationen finden Sie unter Auswirkungen der Verwendung von VNET-Dienstendpunkten mit Azure Storage.

• Zum Verschieben von Daten aus einem lokalen Speicher in Azure Storage müssen Sie öffentliche IP-Adressen in Ihrem lokalen Speicher oder ExpressRoute erlauben. Details finden Sie unter Schützen von Azure-Diensten in virtuellen Netzwerken.

• Stellen Sie im virtuellen Netzwerk einen virtuellen Windows-Computer bereit, der den Azure Synapse-Dienstendpunkt enthält, damit Analysis Services Daten aus Azure Synapse lesen kann. Installieren Sie auf diesem virtuellen Computer ein lokales Azure-Datengateway. Verbinden Sie dann Ihren Azure Analysis-Dienst, mit dem Datengateway.

DevOps

• Erstellen Sie separate Ressourcengruppen für Produktions-, Entwicklungs- und Testumgebungen. Separate Ressourcengruppen erleichtern das Verwalten von Bereitstellungen, das Löschen von Testbereitstellungen und das Zuweisen von Zugriffsrechten.

• Platzieren Sie jede Workload in einer separaten Bereitstellungsvorlage, und speichern Sie die Ressourcen in Quellcodeverwaltungssystemen. Sie können die Vorlagen zusammen oder einzeln im Rahmen eines CI/CD-Prozesses bereitstellen. Dies erleichtert den Automatisierungsprozess.

  In dieser Architektur sind drei grundlegende Workloads vorhanden:

  • der Data Warehouse Server, Analysis Services und zugehörige Ressourcen.
  • Azure Data Factory
  • Ein Szenario mit Lokal-zu-Cloud-Simulation.

  Jede Workload verfügt über eine eigene Bereitstellungsvorlage.

  Der Data Warehouse-Server wird mit Befehlen der Azure-Befehlszeilenschnittstelle eingerichtet und konfiguriert, wobei der imperative Ansatz der IaC-Praktiken verfolgt wird. Erwägen Sie, Bereitstellungsskripts zu verwenden und diese in den Automatisierungsprozess zu integrieren.

• Erwägen Sie ein Staging Ihrer Workloads. Nehmen Sie die Bereitstellung in verschiedenen Stages vor, und führen Sie auf jeder Stage Überprüfungen durch, bevor Sie zur nächsten Stage wechseln. Auf diese Weise können Sie mit umfassender Kontrolle Aktualisierungen in Ihre Produktionsumgebungen pushen, und Sie minimieren unerwartete Bereitstellungsprobleme. Verwenden Sie die Strategien der Blau-Grün-Bereitstellung und Canary-Releases, um Liveproduktionsumgebungen zu aktualisieren.

  Sorgen Sie für eine gute Rollbackstrategie für die Behandlung fehlerhafter Bereitstellungen. Sie können beispielsweise automatisch eine frühere, erfolgreiche Bereitstellung aus Ihrem Bereitstellungsverlauf bereitstellen. Siehe --rollback-on-error-Flag-Parameter in der Azure-Befehlszeilenschnittstelle.

• Azure Monitor ist die empfohlene Option zum Analysieren der Leistung Ihres Data Warehouse und der gesamten Azure Analytics-Plattform; hiermit verfügen Sie über eine integrierte Überwachungsumgebung. Azure Synapse Analytics bietet Überwachungsfunktionen im Azure-Portal, mit denen Sie Erkenntnisse zu Ihrer Data Warehouse-Workload gewinnen können. Das Azure-Portal ist das empfohlene Tool zum Überwachen Ihrer Data Warehouse-Instanz, weil es eine konfigurierbare Aufbewahrungsdauer, Warnungen, Empfehlungen und anpassbare Diagramme und Dashboards für Metriken und Protokolle bietet.

Weitere Informationen finden Sie im Microsoft Azure Well-Architected Framework unter Übersicht über die DevOps-Säule.

Kostenoptimierung

Bei der Kostenoptimierung geht es um die Suche nach Möglichkeiten, unnötige Ausgaben zu reduzieren und die Betriebseffizienz zu verbessern. Weitere Informationen finden Sie unter Übersicht über die Säule „Kostenoptimierung“.

Verwenden Sie den Azure-Preisrechner, um die voraussichtlichen Kosten zu ermitteln. Im Anschluss finden Sie einige Aspekte, die im Zusammenhang mit in dieser Referenzarchitektur verwendeten Diensten berücksichtigt werden müssen.

Azure Data Factory

Azure Data Factory automatisiert die ELT-Pipeline. Die Pipeline verschiebt die Daten aus einer lokalen SQL Server-Datenbank in Azure Synapse. Anschließend werden die Daten zur Analyse in ein tabellarisches Modell transformiert. Bei diesem Szenario beginnt der Preis bei 0,001 USD für Aktivitätsausführungen pro Monat, Aktivitäts-, Auslöser- und Debugausführungen eingeschlossen. Dieser Preis ist ausschließlich die Grundgebühr für die Orchestrierung. Ihnen werden auch Ausführungsaktivitäten in Rechnung gestellt, z. B. das Kopieren von Daten, Suchvorgänge und externe Aktivitäten. Jede Aktivität wird einzeln abgerechnet. Ihnen werden auch Pipelines ohne zugeordnete Auslöser oder Ausführungen innerhalb des Monats in Rechnung gestellt. Alle Aktivitäten werden anteilmäßig auf Minutenbasis berechnet und aufgerundet.

Beispielkostenanalyse

Bedenken Sie einen Anwendungsfall, bei dem zwei Suchaktivitäten aus zwei unterschiedlichen Quellen vorhanden sind. Ein Vorgang dauert eine Minute und zwei Sekunden (aufgerundet auf zwei Minuten) und der andere eine Minute, was zu einer Gesamtzeit von drei Minuten führt. Eine Datenkopieraktivität dauert zehn Minuten. Eine Aktion vom Typ „Gespeicherte Prozedur“ dauert zwei Minuten. Gesamtaktivitätsausführungen für vier Minuten. Die Kosten werden wie folgt berechnet:

Aktivitätsausführungen: 4 * 0,001 USD = 0,004 USD

Suchvorgänge: 3 * (0,005 USD/60) = 0,00025 USD

Gespeicherte Prozedur: 2 * (0,00025 USD/60) = 0,000008 USD

Datenkopie: 10 * (0,25 USD/60) * 4 Datenintegrationseinheiten (Data Integration Unit, DIU) = 0,167 USD

• Gesamtkosten pro Pipelineausführung: 0,17 USD
• Ausführung täglich über 30 Tage: 5,1 USD im Monat
• Ausführung täglich pro 100 Tabellen über 30 Tage: 510 USD

Jeder Aktivität sind Kosten zugeordnet. Verschaffen Sie sich einen Überblick über das Preismodell, und nutzen Sie den ADF-Preisrechner, um eine leistungs- und kostenoptimierte Lösung zu erhalten. Verwalten Sie Ihre Kosten, indem Sie Ihre Dienste starten, beenden, anhalten und skalieren.

Azure Synapse

Azure Synapse eignet sich bestens für intensive Workloads, die eine höhere Abfrageleistung und Computeskalierbarkeit erfordern. Sie können das Modell mit nutzungsbasierter Bezahlung wählen oder reservierte Pläne mit einer Laufzeit von einem Jahr (37 Prozent Ersparnis) oder drei Jahren (65 Prozent Ersparnis) nutzen.

Datenspeicher wird separat abgerechnet. Andere Dienste wie die Notfallwiederherstellung und die Bedrohungserkennung werden ebenfalls separat abgerechnet.

Weitere Informationen finden Sie unter Preise für Azure Synapse.

Analysis Services

Die Preise für Azure Analysis Services sind abhängig von der Dienstebene. In der Referenzimplementierung dieser Architektur wird die Dienstebene Entwickler verwendet, die für Auswertungs-, Entwicklungs- und Testszenarien empfohlen wird. Zu den anderen Dienstebenen gehören die Dienstebene Basic, die für kleine Produktionsumgebungen empfohlen wird, und die Dienstebene Standard für unternehmenskritische Produktionsanwendungen. Weitere Informationen finden Sie unter Immer der richtige Tarif.

Wenn Sie Ihre Instanz anhalten, fallen keine Gebühren an.

Weitere Informationen finden Sie unter Azure Analysis Services – Preise.

Blob Storage

Verwenden Sie ggf. die Funktion für reservierte Kapazität von Azure Storage, um die Speicherkosten zu senken. Mit diesem Modell erhalten Sie einen Rabatt, wenn Sie für ein oder drei Jahre eine Reservierung für eine feste Speicherkapazität vornehmen. Weitere Informationen finden Sie unter Optimieren der Kosten für Blobspeicher mit reservierter Kapazität.

Weitere Informationen finden Sie im Microsoft Azure Well-Architected Framework unter Grundsätze der Kostenoptimierung.

Nächste Schritte

• Einführung in Azure Synapse Analytics
• Erste Schritte mit Azure Synapse Analytics
• Einführung in den Azure Data Factory-Dienst
• Was ist Azure Data Factory?
• Azure Data Factory-Tutorials

Zugehörige Ressourcen

Es wird empfohlen, sich das folgende Azure-Beispielszenario anzusehen. Darin wird veranschaulicht, wie einige dieser Technologien in spezifischen Lösungen verwendet werden:

• Data Warehousing und Analysen für Vertrieb und Marketing
• Enterprise BI in Azure mit Azure Synapse