Automatizada enterprise BI com o SQL Data Warehouse e o Azure Data FactoryAutomated enterprise BI with SQL Data Warehouse and Azure Data Factory

Esta arquitetura de referência mostra como efetuar o carregamento incremental de uma extração, carregamento e transformação (ELT) pipeline.This reference architecture shows how to perform incremental loading in an extract, load, and transform (ELT) pipeline. Ele usa o Azure Data Factory para automatizar o pipeline ELT.It uses Azure Data Factory to automate the ELT pipeline. O pipeline incrementalmente move os dados mais recentes do OLTP de uma base de dados do SQL Server no local para o SQL Data Warehouse.The pipeline incrementally moves the latest OLTP data from an on-premises SQL Server database into SQL Data Warehouse. Dados transacionais é transformada num modelo de tabela para análise.Transactional data is transformed into a tabular model for analysis.

Logótipo do GitHub uma implementação de referência para esta arquitetura está disponível no GitHub.GitHub logo A reference implementation for this architecture is available on GitHub.

Diagrama de arquitetura para automatizada enterprise BI com o SQL Data Warehouse e o Azure Data Factory

Esta arquitetura baseia-se na mostrada na Enterprise BI com o SQL Data Warehouse, mas adiciona algumas funcionalidades que são importantes para cenários de armazenamento de dados da empresa.This architecture builds on the one shown in Enterprise BI with SQL Data Warehouse, but adds some features that are important for enterprise data warehousing scenarios.

  • Automação do pipeline com o Data Factory.Automation of the pipeline using Data Factory.
  • Carregamento incremental.Incremental loading.
  • Integração de várias origens de dados.Integrating multiple data sources.
  • A carregar dados binários, como imagens e dados geoespaciais.Loading binary data such as geospatial data and images.

ArquiteturaArchitecture

A arquitetura é composta pelos seguintes componentes.The architecture consists of the following components.

Origens de dadosData sources

SQL Server no local.On-premises SQL Server. A origem de dados está localizada numa base de dados do SQL Server no local.The source data is located in a SQL Server database on premises. Para simular o ambiente no local, os scripts de implementação para esta disposição de arquitetura uma máquina virtual no Azure com o SQL Server instalada.To simulate the on-premises environment, the deployment scripts for this architecture provision a virtual machine in Azure with SQL Server installed. O [Wide World Importers exemplo base de dados OLTP] wwi é utilizado como a base de dados de origem.The Wide World Importers OLTP sample database is used as the source database.

Dados externos.External data. Um cenário comum dos armazéns de dados é integrar várias origens de dados.A common scenario for data warehouses is to integrate multiple data sources. Esta arquitetura de referência carrega um conjunto de dados externo que contém populações de cidades por ano e integra-a com os dados da base de dados OLTP.This reference architecture loads an external data set that contains city populations by year, and integrates it with the data from the OLTP database. Pode utilizar estes dados para as informações como: "Crescimento de vendas em cada região corresponder ou exceder o crescimento de população?"You can use this data for insights such as: "Does sales growth in each region match or exceed population growth?"

Ingestão e armazenamento de dadosIngestion and data storage

Armazenamento de BLOBs.Blob Storage. Armazenamento de BLOBs é utilizado como uma área de transição para a origem de dados antes de os carregar para o SQL Data Warehouse.Blob storage is used as a staging area for the source data before loading it into SQL Data Warehouse.

Azure SQL Data Warehouse.Azure SQL Data Warehouse. O SQL Data Warehouse é um sistema distribuído concebido para efetuar análises em dados de grande dimensão.SQL Data Warehouse is a distributed system designed to perform analytics on large data. Ele suporta processamento paralelo em grande escala (MPP), que faz com que seja adequado para a execução de análises de elevado desempenho.It supports massive parallel processing (MPP), which makes it suitable for running high-performance analytics.

Azure Data Factory.Azure Data Factory. [Data Factory] adf é um serviço gerido que orquestra e automatiza o movimento de dados e transformação de dados.Data Factory is a managed service that orchestrates and automates data movement and data transformation. Nesta arquitetura, ela coordena os diversos estágios do processo de ELT.In this architecture, it coordinates the various stages of the ELT process.

Análises e relatóriosAnalysis and reporting

Azure Analysis Services.Azure Analysis Services. Analysis Services é um serviço totalmente gerido que fornece capacidades de modelação de dados.Analysis Services is a fully managed service that provides data modeling capabilities. O modelo semântico é carregado no Analysis Services.The semantic model is loaded into Analysis Services.

Power BI.Power BI. O Power BI é um conjunto de ferramentas de análise de negócio para analisar dados de informações empresariais.Power BI is a suite of business analytics tools to analyze data for business insights. Nesta arquitetura, ele consulta o modelo semântico armazenado no Analysis Services.In this architecture, it queries the semantic model stored in Analysis Services.

AutenticaçãoAuthentication

O Azure Active Directory (Azure AD) autentica os utilizadores que se conectam ao servidor do Analysis Services através do Power BI.Azure Active Directory (Azure AD) authenticates users who connect to the Analysis Services server through Power BI.

Fábrica de dados pode utilizar também utilizar o Azure AD para autenticar para o SQL Data Warehouse, utilizando um principal de serviço ou a identidade de serviço gerida (MSI).Data Factory can use also use Azure AD to authenticate to SQL Data Warehouse, by using a service principal or Managed Service Identity (MSI). Para simplificar, a implementação de exemplo utiliza a autenticação do SQL Server.For simplicity, the example deployment uses SQL Server authentication.

Pipeline de dadosData pipeline

Na do Azure Data Factory, os pipelines são agrupamentos lógicos de atividades usado para coordenar uma tarefa — neste caso, carregar e transformar dados para o SQL Data Warehouse.In Azure Data Factory, a pipeline is a logical grouping of activities used to coordinate a task — in this case, loading and transforming data into SQL Data Warehouse.

Esta arquitetura de referência define um pipeline principal que executa uma sequência de pipelines de subordinados.This reference architecture defines a master pipeline that runs a sequence of child pipelines. Pipeline cada filho carrega dados para uma ou mais tabelas do armazém de dados.Each child pipeline loads data into one or more data warehouse tables.

Captura de ecrã do pipeline na fábrica de dados do Azure

Carregamento incrementalIncremental loading

Quando executa um processo ETL ou ELT automatizado, é mais eficiente para carregar apenas os dados que foram alterados, uma vez que executar o anterior.When you run an automated ETL or ELT process, it's most efficient to load only the data that changed since the previous run. Isso é chamado de um carga incremental, em vez de uma carga completa que carrega todos os dados.This is called an incremental load, as opposed to a full load that loads all of the data. Para executar uma carga incremental, precisa de uma forma para identificar quais dados foram alterados.To perform an incremental load, you need a way to identify which data has changed. A abordagem mais comum é usar um máximo valor, o que significa que o valor mais recente de algumas colunas na tabela de origem, uma coluna de datetime ou uma coluna de número inteiro exclusivo de controlo.The most common approach is to use a high water mark value, which means tracking the latest value of some column in the source table, either a datetime column or a unique integer column.

A partir do SQL Server 2016, pode utilizar tabelas temporais.Starting with SQL Server 2016, you can use temporal tables. Estas são as tabelas com versão do sistema que mantenha um histórico completo das alterações de dados.These are system-versioned tables that keep a full history of data changes. O motor de base de dados regista automaticamente o histórico de todas as alterações numa tabela de histórico separado.The database engine automatically records the history of every change in a separate history table. Pode consultar os dados históricos, adicionando uma cláusula FOR SYSTEM_TIME a uma consulta.You can query the historical data by adding a FOR SYSTEM_TIME clause to a query. Internamente, o motor de base de dados consulta a tabela de histórico, mas isso é transparente para a aplicação.Internally, the database engine queries the history table, but this is transparent to the application.

Nota

Para versões anteriores do SQL Server, pode usar captura de dados alterados (CDC).For earlier versions of SQL Server, you can use Change Data Capture (CDC). Essa abordagem é menos conveniente para tabelas temporais, porque tem que consultar uma tabela de alterações separado e alterações são controladas por um número de sequência de registo, em vez de um carimbo.This approach is less convenient than temporal tables, because you have to query a separate change table, and changes are tracked by a log sequence number, rather than a timestamp.

Tabelas temporais são úteis para dados de dimensão, que podem alterar ao longo do tempo.Temporal tables are useful for dimension data, which can change over time. As tabelas de factos representam normalmente uma imutável transação como uma venda, caso em que mantém o histórico de versões de sistema não faz sentido.Fact tables usually represent an immutable transaction such as a sale, in which case keeping the system version history doesn't make sense. Em vez disso, transações normalmente têm uma coluna que representa a data da transação, que pode ser utilizada como o valor de marca d'água.Instead, transactions usually have a column that represents the transaction date, which can be used as the watermark value. Por exemplo, na base de dados OLTP de Importers ampla do mundo, as tabelas Sales.Invoices e Sales.InvoiceLines tem uma LastEditedWhen campo que assume a predefinição sysdatetime().For example, in the Wide World Importers OLTP database, the Sales.Invoices and Sales.InvoiceLines tables have a LastEditedWhen field that defaults to sysdatetime().

Eis o fluxo geral para o pipeline ELT:Here is the general flow for the ELT pipeline:

  1. Para cada tabela na base de dados de origem, controle o tempo limite quando a última tarefa ELT foi executada.For each table in the source database, track the cutoff time when the last ELT job ran. Store estas informações no armazém de dados.Store this information in the data warehouse. (Durante a instalação inicial, todas as horas são definidas como "1-1-1900".)(On initial setup, all times are set to '1-1-1900'.)

  2. Durante os dados de exportação passo, o tempo limite é transmitido como um parâmetro para um conjunto de procedimentos armazenados na base de dados de origem.During the data export step, the cutoff time is passed as a parameter to a set of stored procedures in the source database. Estes armazenados a consulta de procedimentos para quaisquer registos que foram alteradas ou criados após o tempo limite.These stored procedures query for any records that were changed or created after the cutoff time. Para a tabela de fatos de vendas, o LastEditedWhen coluna é utilizada.For the Sales fact table, the LastEditedWhen column is used. Para os dados de dimensão, são utilizadas as tabelas temporais com versão do sistema.For the dimension data, system-versioned temporal tables are used.

  3. Quando a migração de dados estiver concluída, atualize a tabela que armazena os tempos limite.When the data migration is complete, update the table that stores the cutoff times.

Também é útil registrar um linhagem para cada ELT executar.It's also useful to record a lineage for each ELT run. Para um determinado registo, a linhagem associa esse registo com o ELT executar que produziu os dados.For a given record, the lineage associates that record with the ELT run that produced the data. Para cada execução ETL, é criado um novo registo de linhagem para cada tabela, que mostra o início e a terminar em tempos de carregamento.For each ETL run, a new lineage record is created for every table, showing the starting and ending load times. As chaves de linhagem para cada registo são armazenadas nas tabelas de dimensões e fatos.The lineage keys for each record are stored in the dimension and fact tables.

Captura de ecrã da tabela de dimensão de cidade

Depois de um novo lote de dados é carregado para o armazém, atualize o modelo de tabela do Analysis Services.After a new batch of data is loaded into the warehouse, refresh the Analysis Services tabular model. Ver assíncrona atualizar com a API REST.See Asynchronous refresh with the REST API.

Limpeza de dadosData cleansing

Limpeza de dados deve ser parte do processo de ELT.Data cleansing should be part of the ELT process. Nesta arquitetura de referência, uma fonte de dados incorretos é a tabela de população da cidade, onde algumas cidades têm zero população, talvez porque não existem dados estavam disponíveis.In this reference architecture, one source of bad data is the city population table, where some cities have zero population, perhaps because no data was available. Durante o processamento, o pipeline ELT remove as cidades da tabela de população de cidade.During processing, the ELT pipeline removes those cities from the city population table. Execute limpeza em tabelas de testes, em vez de tabelas externas de dados.Perform data cleansing on staging tables, rather than external tables.

Eis o procedimento armazenado que remove as cidades com zero população da tabela Population de cidade.Here is the stored procedure that removes the cities with zero population from the City Population table. (Pode encontrar o ficheiro de origem aqui.)(You can find the source file here.)

DELETE FROM [Integration].[CityPopulation_Staging]
WHERE RowNumber in (SELECT DISTINCT RowNumber
FROM [Integration].[CityPopulation_Staging]
WHERE POPULATION = 0
GROUP BY RowNumber
HAVING COUNT(RowNumber) = 4)

Origens de dados externasExternal data sources

Armazéns de dados, muitas vezes, consolidar dados de várias origens.Data warehouses often consolidate data from multiple sources. Esta arquitetura de referência carrega uma origem de dados externa que contém dados de dados demográficos.This reference architecture loads an external data source that contains demographics data. Este conjunto de dados está disponível no armazenamento de Blobs do Azure como parte da WorldWideImportersDW exemplo.This dataset is available in Azure blob storage as part of the WorldWideImportersDW sample.

O Azure Data Factory pode copiar diretamente a partir do armazenamento de BLOBs, utilizando o conector de armazenamento de BLOBs.Azure Data Factory can copy directly from blob storage, using the blob storage connector. No entanto, o conector requer uma cadeia de ligação ou uma assinatura de acesso partilhado, pelo que não pode ser utilizada para copiar um blob com acesso de leitura público.However, the connector requires a connection string or a shared access signature, so it can't be used to copy a blob with public read access. Como solução, pode utilizar o PolyBase para criar uma tabela externa ao longo do armazenamento de BLOBs e, em seguida, copie as tabelas externas para o SQL Data Warehouse.As a workaround, you can use PolyBase to create an external table over Blob storage and then copy the external tables into SQL Data Warehouse.

Manipulação de dados binários grandesHandling large binary data

A base de dados, a tabela de cidades tem uma coluna de localização que contém um geografia tipo de dados geográficos.In the source database, the Cities table has a Location column that holds a geography spatial data type. O SQL Data Warehouse não suporta o geografia escreva nativamente, para que este campo é convertido num varbinary tipo durante o carregamento.SQL Data Warehouse doesn't support the geography type natively, so this field is converted to a varbinary type during loading. (Consulte soluções alternativas para tipos de dados não suportado.)(See Workarounds for unsupported data types.)

No entanto, o PolyBase suporta um tamanho máximo da coluna de varbinary(8000), que significa que alguns dados poderia ser truncado.However, PolyBase supports a maximum column size of varbinary(8000), which means some data could be truncated. Uma solução alternativa para esse problema é dividir os dados em segmentos durante a exportação e, então, remontá os segmentos, da seguinte forma:A workaround for this problem is to break the data up into chunks during export, and then reassemble the chunks, as follows:

  1. Crie uma tabela de testes temporária para a coluna de localização.Create a temporary staging table for the Location column.

  2. Para cada cidade, dividir os dados de localização em segmentos de 8000 bytes, resultando em 1 – N linhas para cada cidade.For each city, split the location data into 8000-byte chunks, resulting in 1 – N rows for each city.

  3. Para a remontagem os segmentos, utilize o T-SQL PIVOT operador para converter linhas em colunas e, em seguida, concatenar os valores da coluna para cada cidade.To reassemble the chunks, use the T-SQL PIVOT operator to convert rows into columns and then concatenate the column values for each city.

O desafio é que cada cidade será dividida num número diferente de linhas, dependendo do tamanho dos dados de geografia.The challenge is that each city will be split into a different number of rows, depending on the size of geography data. Para o operador PIVOT funcione, cada cidade tem de ter o mesmo número de linhas.For the PIVOT operator to work, every city must have the same number of rows. Para que isso funcione, a consulta de T-SQL (que pode ver aqui) faz alguns truques para preencher as linhas com valores em branco, para que cada cidade tem o mesmo número de colunas, após o pivô.To make this work, the T-SQL query (which you can view here) does some tricks to pad out the rows with blank values, so that every city has the same number of columns after the pivot. A consulta resultante é muito mais rápido do que o loop nas linhas uma por vez.The resulting query turns out to be much faster than looping through the rows one at a time.

A mesma abordagem é utilizada para dados de imagem.The same approach is used for image data.

Dimensões de mudança lentaSlowly changing dimensions

Dados de dimensão forem relativamente estáticos, mas pode alterar.Dimension data is relatively static, but it can change. Por exemplo, um produto pode obter reatribuído a uma categoria de produto diferente.For example, a product might get reassigned to a different product category. Existem várias abordagens para processamento de dimensões de mudança lenta.There are several approaches to handling slowly changing dimensions. Uma técnica comum, chamada tipo 2, é adicionar um novo registo sempre que um alterações de dimensão.A common technique, called Type 2, is to add a new record whenever a dimension changes.

Para implementar a abordagem de tipo 2, tabelas de dimensões tem colunas adicionais que especifique o intervalo de datas em vigor a partir de um determinado registo.In order to implement the Type 2 approach, dimension tables need additional columns that specify the effective date range for a given record. Além disso, as chaves primárias da base de dados de origem irão ser duplicadas, para que a tabela de dimensão tem de ter uma chave primária artificial.Also, primary keys from the source database will be duplicated, so the dimension table must have an artificial primary key.

A imagem seguinte mostra a tabela de Dimension.City.The following image shows the Dimension.City table. O WWI City ID coluna é a chave primária da base de dados de origem.The WWI City ID column is the primary key from the source database. O City Key coluna é uma chave artificial gerada durante o pipeline ETL.The City Key column is an artificial key generated during the ETL pipeline. Além disso, observe que a tabela tem Valid From e Valid To colunas, que definem o intervalo de quando cada linha era válida.Also notice that the table has Valid From and Valid To columns, which define the range when each row was valid. Valores atuais têm um Valid To igual a "9999-12-31'.Current values have a Valid To equal to '9999-12-31'.

Captura de ecrã da tabela de dimensão de cidade

A vantagem dessa abordagem é que ela vai preservando dados históricos, que podem ser útil para análise.The advantage of this approach is that it preserves historical data, which can be valuable for analysis. No entanto, isso também significa que haverá várias linhas para a mesma entidade.However, it also means there will be multiple rows for the same entity. Por exemplo, aqui estão os registos que correspondem a WWI City ID = 28561:For example, here are the records that match WWI City ID = 28561:

Segunda captura de ecrã da tabela de dimensão de cidade

Para cada fatos de vendas, que pretende associar esse fato com uma única linha na tabela de dimensão de cidade, correspondente à data da nota fiscal.For each Sales fact, you want to associate that fact with a single row in City dimension table, corresponding to the invoice date. Como parte do processo de ETL, criar uma coluna adicional queAs part of the ETL process, create an additional column that

A seguinte consulta de T-SQL cria uma tabela temporária que associa cada nota fiscal a chave de cidade correta da tabela de dimensão de cidade.The following T-SQL query creates a temporary table that associates each invoice with the correct City Key from the City dimension table.

CREATE TABLE CityHolder
WITH (HEAP , DISTRIBUTION = HASH([WWI Invoice ID]))
AS
SELECT DISTINCT s1.[WWI Invoice ID] AS [WWI Invoice ID],
                c.[City Key] AS [City Key]
    FROM [Integration].[Sale_Staging] s1
    CROSS APPLY (
                SELECT TOP 1 [City Key]
                    FROM [Dimension].[City]
                WHERE [WWI City ID] = s1.[WWI City ID]
                    AND s1.[Last Modified When] > [Valid From]
                    AND s1.[Last Modified When] <= [Valid To]
                ORDER BY [Valid From], [City Key] DESC
                ) c

Esta tabela é usada para preencher uma coluna na tabela de fatos de vendas:This table is used to populate a column in the Sales fact table:

UPDATE [Integration].[Sale_Staging]
SET [Integration].[Sale_Staging].[WWI Customer ID] =  CustomerHolder.[WWI Customer ID]

Esta coluna permite que uma consulta do Power BI para encontrar o registo de cidade correto para uma determinado fatura de venda.This column enables a Power BI query to find the correct City record for a given sales invoice.

Considerações de segurançaSecurity considerations

Para segurança adicional, pode usar pontos finais de serviço de rede Virtual para proteger os recursos de serviço do Azure para apenas a rede virtual.For additional security, you can use Virtual Network service endpoints to secure Azure service resources to only your virtual network. Esta ação remove totalmente acesso de Internet público a esses recursos, a permitir o tráfego apenas a partir da sua rede virtual.This fully removes public Internet access to those resources, allowing traffic only from your virtual network.

Com esta abordagem, criar uma VNet no Azure e, em seguida, criar pontos finais de serviço privada para serviços do Azure.With this approach, you create a VNet in Azure and then create private service endpoints for Azure services. Esses serviços são, em seguida, restrito ao tráfego de rede virtual.Those services are then restricted to traffic from that virtual network. Também pode acedê-los a partir da rede no local através de um gateway.You can also reach them from your on-premises network through a gateway.

Tenha em atenção as seguintes limitações:Be aware of the following limitations:

  • No momento desta arquitetura de referência foi criado, VNet pontos finais de serviço são suportados para o armazenamento do Azure e Azure SQL Data Warehouse, mas não para o serviço de análise do Azure.At the time this reference architecture was created, VNet service endpoints are supported for Azure Storage and Azure SQL Data Warehouse, but not for Azure Analysis Service. Verificar o estado mais recente aqui.Check the latest status here.

  • Se a pontos finais de serviço estiverem ativados para armazenamento do Azure, o PolyBase não é possível copiar dados de armazenamento para o SQL Data Warehouse.If service endpoints are enabled for Azure Storage, PolyBase cannot copy data from Storage into SQL Data Warehouse. Há uma atenuação para este problema.There is a mitigation for this issue. Para obter mais informações, consulte impacto de utilizar pontos finais de serviço de VNet com o armazenamento do Azure.For more information, see Impact of using VNet Service Endpoints with Azure storage.

  • Para mover dados do local para o armazenamento do Azure, precisará para endereços IP públicos de lista de permissões do seu no local ou ExpressRoute.To move data from on-premises into Azure Storage, you will need to whitelist public IP addresses from your on-premises or ExpressRoute. Para obter detalhes, consulte serviços de proteção do Azure para redes virtuais.For details, see Securing Azure services to virtual networks.

  • Para ativar os serviços de análise ler dados do SQL Data Warehouse, implemente uma VM do Windows para a rede virtual que contém o ponto de extremidade de serviço do SQL Data Warehouse.To enable Analysis Services to read data from SQL Data Warehouse, deploy a Windows VM to the virtual network that contains the SQL Data Warehouse service endpoint. Instale do Azure no local Gateway de dados nesta VM.Install Azure On-premises Data Gateway on this VM. Em seguida, ligar o seu serviço de análise do Azure para o gateway de dados.Then connect your Azure Analysis service to the data gateway.

Implementar a soluçãoDeploy the solution

Para a implementar e executar a implementação de referência, siga os passos a Leiame do GitHub.To the deploy and run the reference implementation, follow the steps in the GitHub readme. Implementa o seguinte:It deploys the following:

  • Uma VM do Windows para simular um servidor de base de dados no local.A Windows VM to simulate an on-premises database server. Ele inclui o SQL Server 2017 e ferramentas relacionadas, juntamente com o Power BI Desktop.It includes SQL Server 2017 and related tools, along with Power BI Desktop.
  • Uma conta de armazenamento do Azure que fornece armazenamento de BLOBs para armazenar os dados exportados a partir da base de dados do SQL Server.An Azure storage account that provides Blob storage to hold data exported from the SQL Server database.
  • Uma instância de armazém de dados SQL do Azure.An Azure SQL Data Warehouse instance.
  • Uma instância do Azure Analysis Services.An Azure Analysis Services instance.
  • O Azure Data Factory e o pipeline da fábrica de dados para a tarefa ELT.Azure Data Factory and the Data Factory pipeline for the ELT job.

Pode querer rever o seguinte procedimento cenários de exemplo do Azure que demonstram a soluções específicas com algumas das mesmas tecnologias:You may want to review the following Azure example scenarios that demonstrate specific solutions using some of the same technologies: