Geautomatiseerde Enterprise BI

Dit voorbeeld gaat over het uitvoeren van incrementeel laden in een ELT-pijplijn (extract, load en transform). Azure Data Factory wordt gebruikt om de ELT-pijplijn te automatiseren. De pijplijn verplaatst incrementeel de meest recente OLTP-gegevens van een on-premises SQL Server-database naar Azure Synapse. Transactionele gegevens worden omgezet in een tabellair model voor analyse.

Architectuur

Een Visio-bestand van deze architectuur downloaden.

Deze architectuur bouwt voort op de architectuur die wordt weergegeven in Enterprise BI met Azure Synapse, maar voegt enkele functies toe die belangrijk zijn voor bedrijfsdatawarehousingscenario's.

• Automatisering van de pijplijn met Behulp van Data Factory.
• Incrementeel laden.
• Meerdere gegevensbronnen integreren.
• Binaire gegevens laden, zoals georuimtelijke gegevens en afbeeldingen.

Workflow

De architectuur bestaat uit de volgende services en onderdelen.

Gegevensbronnen

On-premises SQL Server. De brongegevens bevinden zich on-premises in een SQL Server-database. De on-premises omgeving simuleren. De OLTP-voorbeelddatabase Wide World Importers wordt gebruikt als de brondatabase.

Externe gegevens. Een veelvoorkomend scenario voor datawarehouses is het integreren van meerdere gegevensbronnen. Deze referentiearchitectuur laadt een externe gegevensset die stadspopulaties per jaar bevat en integreert deze met de gegevens uit de OLTP-database. U kunt deze gegevens gebruiken voor inzichten zoals: 'Komt de verkoopgroei in elke regio overeen met of overschrijdt de bevolkingsgroei?'

Opname en gegevensopslag

Blob Storage. Blob Storage wordt gebruikt als faseringsgebied voor de brongegevens voordat deze in Azure Synapse worden geladen.

Azure Synapse. Azure Synapse is een gedistribueerd systeem dat is ontworpen om analyses uit te voeren op grote gegevens. Het biedt ondersteuning voor massive parallel processing (MPP), waardoor het geschikt is voor het uitvoeren van analyses met hoge prestaties.

Azure Data Factory. Data Factory is een beheerde service waarmee gegevensverplaatsing en gegevenstransformatie worden georganiseerd en geautomatiseerd. In deze architectuur coördineert het de verschillende fasen van het ELT-proces.

Analyse en rapportage

Azure Analysis Services. Analysis Services is een volledig beheerde service die mogelijkheden voor gegevensmodellering biedt. Het semantische model wordt geladen in Analysis Services.

Power BI. Power BI is een suite met hulpprogramma's voor bedrijfsanalyse voor het analyseren van gegevens voor zakelijke inzichten. In deze architectuur wordt een query uitgevoerd op het semantische model dat is opgeslagen in Analysis Services.

Verificatie

Microsoft Entra ID verifieert gebruikers die verbinding maken met de Analysis Services-server via Power BI.

Data Factory kan ook Microsoft Entra-id gebruiken om te verifiëren bij Azure Synapse, met behulp van een service-principal of Managed Service Identity (MSI).

Onderdelen

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

Scenariodetails

Gegevenspijplijn

In Azure Data Factory is een pijplijn een logische groepering van activiteiten die worden gebruikt om een taak te coördineren, in dit geval het laden en transformeren van gegevens in Azure Synapse.

Deze referentiearchitectuur definieert een bovenliggende pijplijn die een reeks onderliggende pijplijnen uitvoert. Elke onderliggende pijplijn laadt gegevens in een of meer datawarehouse-tabellen.

Aanbevelingen

Incrementeel laden

Wanneer u een geautomatiseerd ETL- of ELT-proces uitvoert, is het meest efficiënt om alleen de gegevens te laden die zijn gewijzigd sinds de vorige uitvoering. Dit wordt een incrementele belasting genoemd, in plaats van een volledige belasting waarmee alle gegevens worden geladen. Als u een incrementele belasting wilt uitvoeren, hebt u een manier nodig om te bepalen welke gegevens zijn gewijzigd. De meest voorkomende benadering is het gebruik van een hoge watermarkeringswaarde , wat betekent dat de meest recente waarde van een kolom in de brontabel wordt bijgehouden, ofwel een datum/tijd-kolom of een unieke geheel getalkolom.

Vanaf SQL Server 2016 kunt u tijdelijke tabellen gebruiken. Dit zijn tabellen met systeemversies die een volledige geschiedenis van gegevenswijzigingen behouden. De database-engine registreert automatisch de geschiedenis van elke wijziging in een afzonderlijke geschiedenistabel. U kunt een query uitvoeren op de historische gegevens door een FOR SYSTEM_TIME-component toe te voegen aan een query. Intern voert de database-engine een query uit op de geschiedenistabel, maar dit is transparant voor de toepassing.

Tijdelijke tabellen zijn handig voor dimensiegegevens, die na verloop van tijd kunnen worden gewijzigd. Feitentabellen vertegenwoordigen meestal een onveranderbare transactie, zoals een verkoop, waarbij het bewaren van de systeemversiegeschiedenis niet zinvol is. In plaats daarvan hebben transacties meestal een kolom die de transactiedatum vertegenwoordigt, die kan worden gebruikt als de grenswaarde. In de OLTP-database Wide World Importers hebben de tabellen Sales.Invoices en Sales.InvoiceLines bijvoorbeeld een LastEditedWhen veld dat standaard is ingesteld sysdatetime()op .

Dit is de algemene stroom voor de ELT-pijplijn:

1. Houd voor elke tabel in de brondatabase de cutoff-tijd bij waarop de laatste ELT-taak is uitgevoerd. Sla deze informatie op in het datawarehouse. (Bij de eerste installatie worden alle tijden ingesteld op '1-1-1900'.)

2. Tijdens de gegevensexportstap wordt de cutoff-tijd doorgegeven als een parameter aan een set opgeslagen procedures in de brondatabase. Deze opgeslagen proceduresquery's voor records die zijn gewijzigd of gemaakt na de cutoff-tijd. Voor de feitentabel Sales wordt de LastEditedWhen kolom gebruikt. Voor de dimensiegegevens worden tijdelijke tabellen met systeemversies gebruikt.

3. Wanneer de gegevensmigratie is voltooid, werkt u de tabel bij waarin de afsluittijden worden opgeslagen.

Het is ook handig om een herkomst vast te leggen voor elke ELT-uitvoering. Voor een bepaalde record koppelt de herkomst die record aan de ELT-uitvoering die de gegevens heeft geproduceerd. Voor elke ETL-uitvoering wordt er een nieuwe herkomstrecord gemaakt voor elke tabel, waarin de begin- en eindbelastingstijden worden weergegeven. De herkomstsleutels voor elke record worden opgeslagen in de dimensie- en feitentabellen.

Nadat een nieuwe batch met gegevens in het magazijn is geladen, vernieuwt u het tabellaire Analysis Services-model. Zie Asynchroon vernieuwen met de REST API.

Gegevensopschoning

Gegevensopschoning moet deel uitmaken van het ELT-proces. In deze referentiearchitectuur is één bron van slechte gegevens de tabel stadspopulatie, waarbij sommige steden nul populatie hebben, misschien omdat er geen gegevens beschikbaar waren. Tijdens de verwerking verwijdert de ELT-pijplijn deze steden uit de tabel stadspopulatie. Gegevensopschoning uitvoeren voor faseringstabellen in plaats van externe tabellen.

Externe gegevensbronnen

Datawarehouses consolideren vaak gegevens uit meerdere bronnen. Bijvoorbeeld een externe gegevensbron die demografische gegevens bevat. Deze gegevensset is beschikbaar in Azure Blob Storage als onderdeel van het WorldWideImportersDW-voorbeeld .

Azure Data Factory kan rechtstreeks vanuit blobopslag worden gekopieerd met behulp van de blobopslagconnector. De connector vereist echter een verbindingsreeks of een handtekening voor gedeelde toegang, zodat deze niet kan worden gebruikt om een blob met openbare leestoegang te kopiëren. Als tijdelijke oplossing kunt u PolyBase gebruiken om een externe tabel te maken via Blob Storage en vervolgens de externe tabellen naar Azure Synapse te kopiëren.

Grote binaire gegevens verwerken

In de brondatabase heeft een tabel Plaats bijvoorbeeld een locatiekolom met een geografisch gegevenstype voor ruimtelijke gegevens. Azure Synapse biedt geen ondersteuning voor het geografietype, dus dit veld wordt tijdens het laden geconverteerd naar een varbinair type. (Zie Tijdelijke oplossingen voor niet-ondersteunde gegevenstypen.)

PolyBase ondersteunt echter een maximale kolomgrootte, varbinary(8000)wat betekent dat sommige gegevens kunnen worden afgekapt. Een tijdelijke oplossing voor dit probleem is het opsplitsen van de gegevens in segmenten tijdens het exporteren en vervolgens opnieuw samenvoegen van de segmenten, als volgt:

1. Maak een tijdelijke faseringstabel voor de kolom Locatie.

2. Splits voor elke stad de locatiegegevens in segmenten van 8000 bytes, wat resulteert in 1 – N rijen voor elke stad.

3. Als u de segmenten opnieuw wilt samenvoegen, gebruikt u de T-SQL PIVOT-operator om rijen te converteren naar kolommen en voegt u vervolgens de kolomwaarden voor elke plaats samen.

De uitdaging is dat elke stad wordt gesplitst in een ander aantal rijen, afhankelijk van de grootte van geografische gegevens. De PIVOT-operator werkt alleen als elke plaats hetzelfde aantal rijen heeft. Om dit werk te maken, voert de T-SQL-query enkele trucs uit om de rijen met lege waarden op te heffen, zodat elke plaats hetzelfde aantal kolommen na het draaipunt heeft. De resulterende query blijkt veel sneller te zijn dan door de rijen één voor één te doorlopen.

Dezelfde methode wordt gebruikt voor afbeeldingsgegevens.

Langzaam veranderende dimensies

Dimensiegegevens zijn relatief statisch, maar kunnen worden gewijzigd. Een product kan bijvoorbeeld opnieuw worden toegewezen aan een andere productcategorie. Er zijn verschillende benaderingen voor het verwerken van langzaam veranderende dimensies. Een veelgebruikte techniek, type 2, is het toevoegen van een nieuwe record wanneer een dimensie wordt gewijzigd.

Als u de methode Type 2 wilt implementeren, hebben dimensietabellen aanvullende kolommen nodig die het ingangsdatumbereik voor een bepaalde record opgeven. Bovendien worden primaire sleutels uit de brondatabase gedupliceerd, zodat de dimensietabel een kunstmatige primaire sleutel moet hebben.

In de volgende afbeelding ziet u bijvoorbeeld de tabel Dimension.City. De WWI City ID kolom is de primaire sleutel uit de brondatabase. De City Key kolom is een kunstmatige sleutel die wordt gegenereerd tijdens de ETL-pijplijn. U ziet ook dat de tabel Valid From en Valid To kolommen het bereik definiëren wanneer elke rij geldig was. De huidige waarden zijn Valid To gelijk aan '9999-12-31'.

Het voordeel van deze aanpak is dat historische gegevens behouden blijven, wat waardevol kan zijn voor analyse. Dit betekent echter ook dat er meerdere rijen voor dezelfde entiteit zijn. Dit zijn bijvoorbeeld de records die overeenkomen WWI City ID met = 28561:

Voor elk verkoopfeit wilt u dat feit koppelen aan één rij in de dimensietabel Plaats, die overeenkomt met de factuurdatum.

Overwegingen

Met deze overwegingen worden de pijlers van het Azure Well-Architected Framework geïmplementeerd. Dit is een set richtlijnen die kunnen worden gebruikt om de kwaliteit van een workload te verbeteren. Zie Microsoft Azure Well-Architected Framework voor meer informatie.

Beveiliging

Beveiliging biedt garanties tegen opzettelijke aanvallen en misbruik van uw waardevolle gegevens en systemen. Zie Overzicht van de beveiligingspijler voor meer informatie.

Voor extra beveiliging kunt u service-eindpunten van Virtual Network gebruiken om Azure-servicebronnen alleen in uw virtuele netwerk te beveiligen. Hierdoor wordt openbare internettoegang voor deze resources volledig verwijderd, zodat alleen verkeer van uw virtuele netwerk mogelijk is.

Met deze methode maakt u een VNet in Azure en maakt u vervolgens privéservice-eindpunten voor Azure-services. Deze services worden vervolgens beperkt tot verkeer van dat virtuele netwerk. U kunt ze ook bereiken vanuit uw on-premises netwerk via een gateway.

Houd rekening met de volgende beperkingen:

• Als service-eindpunten zijn ingeschakeld voor Azure Storage, kan PolyBase geen gegevens kopiëren van Storage naar Azure Synapse. Er is een oplossing voor dit probleem. Zie Impact van het gebruik van VNet-service-eindpunten met Azure Storage voor meer informatie.

• Als u gegevens wilt verplaatsen van on-premises naar Azure Storage, moet u openbare IP-adressen van uw on-premises of ExpressRoute toestaan. Zie Azure-services beveiligen naar virtuele netwerken voor meer informatie.

• Als u wilt dat Analysis Services gegevens kan lezen uit Azure Synapse, implementeert u een Virtuele Windows-machine in het virtuele netwerk dat het Azure Synapse-service-eindpunt bevat. Installeer Azure On-premises Gegevensgateway op deze VM. Verbind vervolgens uw Azure Analysis-service met de gegevensgateway.

DevOps

• Maak afzonderlijke resourcegroepen voor productie-, ontwikkelings- en testomgevingen. Met afzonderlijke resourcegroepen kunt u eenvoudiger implementaties beheren, testimplementaties verwijderen en toegangsrechten verlenen.

• Plaats elke workload in een afzonderlijke implementatiesjabloon en sla de resources op in broncodebeheersystemen. U kunt de sjablonen samen of afzonderlijk implementeren als onderdeel van een CI/CD-proces, waardoor het automatiseringsproces eenvoudiger wordt.

  In deze architectuur zijn er drie hoofdworkloads:

  • De datawarehouse-server, Analysis Services en gerelateerde resources.
  • Azure Data Factory.
  • Een on-premises naar cloud gesimuleerd scenario.

  Elke workload heeft een eigen implementatiesjabloon.

  De datawarehouse-server wordt ingesteld en geconfigureerd met behulp van Azure CLI-opdrachten die de imperatieve benadering van de IaC-praktijk volgen. Overweeg implementatiescripts te gebruiken en te integreren in het automatiseringsproces.

• Overweeg om uw workloads te faseren. Implementeer in verschillende fasen en voer validatiecontroles uit in elke fase voordat u naar de volgende fase gaat. Op die manier kunt u op een zeer gecontroleerde manier updates naar uw productieomgevingen pushen en onverwachte implementatieproblemen minimaliseren. Gebruik blauwgroene implementatie - en Canary-releasesstrategieën voor het bijwerken van live productieomgevingen.

  Zorg voor een goede terugdraaistrategie voor het afhandelen van mislukte implementaties. U kunt bijvoorbeeld automatisch een eerdere, geslaagde implementatie opnieuw implementeren vanuit uw implementatiegeschiedenis. Zie de vlagparameter --rollback-on-error in Azure CLI.

• Azure Monitor is de aanbevolen optie voor het analyseren van de prestaties van uw datawarehouse en het volledige Azure Analytics-platform voor een geïntegreerde bewakingservaring. Azure Synapse Analytics biedt een bewakingservaring in Azure Portal om inzicht te geven in uw datawarehouse-workload. Azure Portal is het aanbevolen hulpprogramma bij het bewaken van uw datawarehouse, omdat het configureerbare bewaarperioden, waarschuwingen, aanbevelingen en aanpasbare grafieken en dashboards voor metrische gegevens en logboeken biedt.

Zie de sectie DevOps in Microsoft Azure Well-Architected Framework voor meer informatie.

Kostenoptimalisatie

Kostenoptimalisatie gaat over manieren om onnodige uitgaven te verminderen en operationele efficiëntie te verbeteren. Zie Overzicht van de pijler kostenoptimalisatie voor meer informatie.

Gebruik de Azure-prijscalculator om een schatting van de kosten te maken. Hier volgen enkele overwegingen voor services die in deze referentiearchitectuur worden gebruikt.

Azure Data Factory

Azure Data Factory automatiseert de ELT-pijplijn. De pijplijn verplaatst de gegevens van een on-premises SQL Server-database naar Azure Synapse. De gegevens worden vervolgens omgezet in een tabellair model voor analyse. Voor dit scenario beginnen de prijzen vanaf $ 0,001-uitvoeringen per maand met uitvoeringen van activiteiten, triggers en foutopsporing. Die prijs is alleen de basiskosten voor indeling. Er worden ook kosten in rekening gebracht voor uitvoeringsactiviteiten, zoals het kopiëren van gegevens, zoekopdrachten en externe activiteiten. Elke activiteit is afzonderlijk geprijsd. Er worden ook kosten in rekening gebracht voor pijplijnen zonder gekoppelde triggers of uitvoeringen binnen de maand. Alle activiteiten worden naar rato per minuut afgerond.

Voorbeeld van kostenanalyse

Overweeg een use-case waarbij er twee opzoekactiviteiten uit twee verschillende bronnen zijn. Een duurt 1 minuut en 2 seconden (afgerond tot 2 minuten) en de andere duurt 1 minuut, wat resulteert in de totale tijd van 3 minuten. Eén activiteit voor het kopiëren van gegevens duurt 10 minuten. Eén opgeslagen procedureactiviteit duurt 2 minuten. De totale activiteit wordt gedurende 4 minuten uitgevoerd. Kosten worden als volgt berekend:

Uitvoeringen van activiteit: 4 * $ 0,001 = $ 0,004

Zoekacties: 3 * ($ 0,005 / 60) = $ 0,00025

Opgeslagen procedure: 2 * ($ 0,00025 / 60) = $ 0,000008

Gegevenskopie: 10 * ($ 0,25 / 60) * 4 gegevensintegratie-eenheid (DIU) = $ 0,167

• Totale kosten per pijplijnuitvoering: $ 0,17.
• Eenmaal per dag uitvoeren voor 30 dagen: $ 5,1 maand.
• Eenmaal per dag uitvoeren per 100 tabellen gedurende 30 dagen: $ 510

Elke activiteit heeft een bijbehorende kosten. Krijg inzicht in het prijsmodel en gebruik de ADF-prijscalculator om niet alleen een oplossing te krijgen die is geoptimaliseerd voor prestaties, maar ook voor kosten. Beheer uw kosten door uw services te starten, stoppen, onderbreken en schalen.

Azure Synapse

Azure Synapse is ideaal voor intensieve workloads met hogere queryprestaties en schaalbaarheidsbehoeften. U kunt het model voor betalen per gebruik kiezen of gereserveerde abonnementen van één jaar gebruiken (37% besparingen) of drie jaar (65% besparingen).

Gegevensopslag wordt afzonderlijk in rekening gebracht. Andere services, zoals herstel na noodgevallen en detectie van bedreigingen, worden ook afzonderlijk in rekening gebracht.

Zie Prijzen voor Azure Synapse voor meer informatie.

Analysis Services

Prijzen voor Azure Analysis Services zijn afhankelijk van de categorie. De referentie-implementatie van deze architectuur maakt gebruik van de developer-laag , die wordt aanbevolen voor evaluatie-, ontwikkelings- en testscenario's. Andere lagen zijn onder andere de Basic-laag , die wordt aanbevolen voor een kleine productieomgeving; de Standard-laag voor bedrijfskritieke productietoepassingen. Zie de juiste laag wanneer u deze nodig hebt voor meer informatie.

Er worden geen kosten in rekening gebracht wanneer u uw exemplaar pauzeert.

Zie prijzen voor Azure Analysis Services voor meer informatie.

Blob Storage

Overweeg om de gereserveerde capaciteitsfunctie van Azure Storage te gebruiken om de kosten voor opslag te verlagen. Met dit model krijgt u korting als u een reservering kunt doorvoeren voor vaste opslagcapaciteit voor een of drie jaar. Zie Kosten optimaliseren voor Blob Storage met gereserveerde capaciteit voor meer informatie.

Zie de sectie Kosten in Microsoft Azure Well-Architected Framework voor meer informatie.

Volgende stappen

• Inleiding tot Azure Synapse Analytics
• Aan de slag met Azure Synapse Analytics
• Inleiding tot Azure Data Factory
• Wat is Azure Data Factory?
• Zelfstudies voor Azure Data Factory

Verwante resources

U kunt de volgende Azure-voorbeeldscenario's bekijken die specifieke oplossingen demonstreren met behulp van een aantal van dezelfde technologieën:

• Datawarehousing en analyses voor verkoop en marketing
• Enterprise BI in Azure met Azure Synapse.