BI empresarial automatizada

Este exemplo é sobre como executar o carregamento incremental em um pipeline de extração, carga e transformação (ELT). Ele usa o Azure Data Factory para automatizar o pipeline ELT. O pipeline move incrementalmente os dados OLTP mais recentes de um banco de dados SQL Server local para o Azure Synapse. Os dados transacionais são transformados em um modelo tabular para análise.

Arquitetura

Transfira um ficheiro do Visio desta arquitetura.

Essa arquitetura se baseia na mostrada no Enterprise BI com o Azure Synapse, mas adiciona alguns recursos que são importantes para cenários de armazenamento de dados corporativos.

• Automação do pipeline utilizando Data Factory.
• Carregamento incremental.
• Integração de várias fontes de dados.
• Carregamento de dados binários, como dados geoespaciais e imagens.

Fluxo de Trabalho

A arquitetura consiste nos seguintes serviços e componentes.

Origens de dados

SQL Server local. Os dados de origem estão localizados em um banco de dados do SQL Server local. Para simular o ambiente local. O banco de dados de exemplo OLTP da Wide World Importers é usado como o banco de dados de origem.

Dados externos. Um cenário comum para armazéns de dados é integrar várias fontes de dados. Essa arquitetura de referência carrega um conjunto de dados externos que contém as populações das cidades por ano e o integra aos dados do banco de dados OLTP. Você pode usar esses dados para informações como: "O crescimento das vendas em cada região corresponde ou excede o crescimento da população?"

Ingestão e armazenamento de dados

Armazenamento de Blob. O armazenamento de Blob é usado como uma área de preparo para os dados de origem antes de carregá-los no Azure Synapse.

Sinapse do Azure. O Azure Synapse é um sistema distribuído projetado para executar análises em dados grandes. Suporta o processamento paralelo em massa (MPP), que faz com que este serviço seja adequado para executar análises de elevado desempenho.

Azure Data Factory. O Data Factory é um serviço gerenciado que orquestra e automatiza a movimentação e a transformação de dados. Nesta arquitetura, coordena as várias etapas do processo ELT.

Análise e relatórios

Azure Analysis Services. O Analysis Services é um serviço totalmente gerenciado que fornece recursos de modelagem de dados. O modelo semântico é carregado no Analysis Services.

Power BI. O Power BI é um conjunto de ferramentas de análise de negócios para analisar dados para insights de negócios. Nessa arquitetura, ele consulta o modelo semântico armazenado no Analysis Services.

Autenticação

O Microsoft Entra ID autentica os usuários que se conectam ao servidor do Analysis Services por meio do Power BI.

O Data Factory também pode usar a ID do Microsoft Entra para autenticar no Azure Synapse, usando uma entidade de serviço ou uma Identidade de Serviço Gerenciado (MSI).

Componentes

• Armazenamento de Blobs do Azure
• Azure Synapse Analytics
• Fábrica de Dados do Azure
• Azure Analysis Services
• Power BI
• Microsoft Entra ID

Detalhes do cenário

Pipeline de dados

No Azure Data Factory, um pipeline é um agrupamento lógico de atividades usado para coordenar uma tarefa — neste caso, carregando e transformando dados no Azure Synapse.

Essa arquitetura de referência define um pipeline pai que executa uma sequência de pipelines filho. Cada pipeline filho carrega dados em uma ou mais tabelas de data warehouse.

Recomendações

Carregamento incremental

Quando você executa um processo automatizado de ETL ou ELT, é mais eficiente carregar apenas os dados que foram alterados desde a execução anterior. Isso é chamado de carga incremental, em oposição a uma carga completa que carrega todos os dados. Para executar uma carga incremental, você precisa de uma maneira de identificar quais dados foram alterados. A abordagem mais comum é usar um valor de marca d'água alto, o que significa acompanhar o valor mais recente de alguma coluna na tabela de origem, seja uma coluna datetime ou uma coluna inteira exclusiva.

A partir do SQL Server 2016, você pode usar tabelas temporais. Estas são tabelas versionadas pelo sistema que mantêm um histórico completo de alterações de dados. O mecanismo de banco de dados registra automaticamente o histórico de cada alteração em uma tabela de histórico separada. Você pode consultar os dados históricos adicionando uma cláusula FOR SYSTEM_TIME a uma consulta. Internamente, o mecanismo de banco de dados consulta a tabela de histórico, mas isso é transparente para o aplicativo.

As tabelas temporais são úteis para dados de dimensão, que podem mudar ao longo do tempo. As tabelas de fatos geralmente representam uma transação imutável, como uma venda, caso em que manter o histórico de versões do sistema não faz sentido. Em vez disso, as transações geralmente têm uma coluna que representa a data da transação, que pode ser usada como o valor da marca d'água. Por exemplo, no banco de dados OLTP da Wide World Importers, as tabelas Sales.Invoices e Sales.InvoiceLines têm um LastEditedWhen campo cujo padrão é sysdatetime().

Aqui está o fluxo geral para o gasoduto ELT:

1. Para cada tabela no banco de dados de origem, controle o tempo de corte quando o último trabalho ELT foi executado. Armazene essas informações no data warehouse. (Na configuração inicial, todos os tempos são definidos como '1-1-1900'.)

2. Durante a etapa de exportação de dados, o tempo de corte é passado como um parâmetro para um conjunto de procedimentos armazenados no banco de dados de origem. Esses procedimentos armazenados consultam todos os registros que foram alterados ou criados após o tempo de corte. Para a tabela de fatos de vendas, a LastEditedWhen coluna é usada. Para os dados de dimensão, são usadas tabelas temporais versionadas pelo sistema.

3. Quando a migração de dados estiver concluída, atualize a tabela que armazena os tempos de corte.

Também é útil registrar uma linhagem para cada corrida ELT. Para um determinado registro, a linhagem associa esse registro à execução ELT que produziu os dados. Para cada execução ETL, um novo registro de linhagem é criado para cada tabela, mostrando os tempos de carregamento iniciais e finais. As chaves de linhagem para cada registro são armazenadas nas tabelas de dimensões e fatos.

Depois que um novo lote de dados for carregado no depósito, atualize o modelo de tabela do Analysis Services. Consulte Atualização assíncrona com a API REST.

Limpeza de dados

A limpeza de dados deve fazer parte do processo ELT. Nesta arquitetura de referência, uma fonte de dados ruins é a tabela populacional da cidade, onde algumas cidades têm população zero, talvez porque não havia dados disponíveis. Durante o processamento, o gasoduto ELT remove essas cidades da tabela de população da cidade. Execute a limpeza de dados em tabelas de preparo, em vez de tabelas externas.

Origens de dados externas

Os armazéns de dados geralmente consolidam dados de várias fontes. Por exemplo, uma fonte de dados externa que contém dados demográficos. Este conjunto de dados está disponível no armazenamento de blob do Azure como parte do exemplo WorldWideImportersDW .

O Azure Data Factory pode copiar diretamente do armazenamento de blob, usando o conector de armazenamento de blob. No entanto, o conector requer uma cadeia de conexão ou uma assinatura de acesso compartilhado, portanto, não pode ser usado para copiar um blob com acesso de leitura pública. Como solução alternativa, você pode usar o PolyBase para criar uma tabela externa no armazenamento de Blob e, em seguida, copiar as tabelas externas para o Azure Synapse.

Tratamento de grandes dados binários

Por exemplo, no banco de dados de origem, uma tabela Cidade tem uma coluna Localização que contém um tipo de dados geográficos geográficos. O Azure Synapse não dá suporte ao tipo de geografia nativamente, portanto, esse campo é convertido em um tipo varbinary durante o carregamento. (Ver Soluções alternativas para tipos de dados sem suporte.)

No entanto, o PolyBase suporta um tamanho máximo de coluna de , o varbinary(8000)que significa que alguns dados podem ser truncados. Uma solução alternativa para esse problema é dividir os dados em partes durante a exportação e, em seguida, remontar os blocos, da seguinte maneira:

1. Crie uma tabela de preparo temporária para a coluna Local.

2. Para cada cidade, divida os dados de localização em blocos de 8000 bytes, resultando em 1 – N linhas para cada cidade.

3. Para remontar os blocos, use o operador T-SQL PIVOT para converter linhas em colunas e, em seguida, concatenar os valores de coluna para cada cidade.

O desafio é que cada cidade será dividida em um número diferente de linhas, dependendo do tamanho dos dados geográficos. Para que o operador PIVOT funcione, cada cidade deve ter o mesmo número de linhas. Para que isso funcione, a consulta T-SQL faz alguns truques para preencher as linhas com valores em branco, para que cada cidade tenha o mesmo número de colunas após o pivô. A consulta resultante acaba por ser muito mais rápida do que percorrer as linhas uma de cada vez.

A mesma abordagem é usada para dados de imagem.

Dimensões em mudança lenta

Os dados de dimensão são relativamente estáticos, mas podem mudar. Por exemplo, um produto pode ser reatribuído a uma categoria de produto diferente. Existem várias abordagens para lidar com dimensões que mudam lentamente. Uma técnica comum, chamada Tipo 2, é adicionar um novo registro sempre que uma dimensão muda.

Para implementar a abordagem Tipo 2, as tabelas de dimensão precisam de colunas adicionais que especifiquem o intervalo de datas efetivo para um determinado registro. Além disso, as chaves primárias do banco de dados de origem serão duplicadas, portanto, a tabela de dimensões deve ter uma chave primária artificial.

Por exemplo, a imagem a seguir mostra a tabela Dimension.City. A WWI City ID coluna é a chave primária do banco de dados de origem. A City Key coluna é uma chave artificial gerada durante o pipeline ETL. Observe também que a tabela tem Valid From e Valid To colunas, que definem o intervalo quando cada linha era válida. Os valores atuais têm um Valid To igual a '9999-12-31'.

A vantagem desta abordagem é que preserva dados históricos, que podem ser valiosos para análise. No entanto, isso também significa que haverá várias linhas para a mesma entidade. Por exemplo, aqui estão os registros que correspondem = WWI City ID 28561:

Para cada fato de venda, você deseja associar esse fato a uma única linha na tabela de dimensões Cidade, correspondente à data da fatura.

Considerações

Essas considerações implementam os pilares do Azure Well-Architected Framework, que é um conjunto de princípios orientadores que podem ser usados para melhorar a qualidade de uma carga de trabalho. Para obter mais informações, consulte Microsoft Azure Well-Architected Framework.

Segurança

A segurança oferece garantias contra ataques deliberados e o abuso de seus valiosos dados e sistemas. Para obter mais informações, consulte Visão geral do pilar de segurança.

Para segurança adicional, você pode usar pontos de extremidade de serviço de Rede Virtual para proteger os recursos de serviço do Azure somente em sua rede virtual. Isso remove totalmente o acesso público à Internet a esses recursos, permitindo o tráfego apenas da sua rede virtual.

Com essa abordagem, você cria uma VNet no Azure e, em seguida, cria pontos de extremidade de serviço privados para serviços do Azure. Esses serviços ficam então restritos ao tráfego dessa rede virtual. Você também pode alcançá-los de sua rede local por meio de um gateway.

Esteja ciente das seguintes limitações:

• Se os pontos de extremidade de serviço estiverem habilitados para o Armazenamento do Azure, o PolyBase não poderá copiar dados do Armazenamento para o Azure Synapse. Há uma atenuação para este problema. Para obter mais informações, consulte Impacto do uso de pontos de extremidade de serviço VNet com o armazenamento do Azure.

• Para mover dados do local para o Armazenamento do Azure, você precisará permitir endereços IP públicos do local ou da Rota Expressa. Para obter detalhes, consulte Protegendo serviços do Azure para redes virtuais.

• Para permitir que o Analysis Services leia dados do Azure Synapse, implante uma VM do Windows na rede virtual que contém o ponto de extremidade do serviço Azure Synapse. Instale o Gateway de Dados Local do Azure nesta VM. Em seguida, conecte seu serviço de Análise do Azure ao gateway de dados.

DevOps

• Crie grupos de recursos separados para ambientes de produção, desenvolvimento e teste. A utilização de grupos de recursos separados torna mais fácil gerir as implementações, eliminar as implementações de teste e atribuir direitos de acesso.

• Coloque cada carga de trabalho em um modelo de implantação separado e armazene os recursos em sistemas de controle do código-fonte. Você pode implantar os modelos juntos ou individualmente como parte de um processo de CI/CD, facilitando o processo de automação.

  Nessa arquitetura, há três cargas de trabalho principais:

  • O servidor de data warehouse, o Analysis Services e os recursos relacionados.
  • Azure Data Factory.
  • Um cenário simulado local para nuvem.

  Cada carga de trabalho tem seu próprio modelo de implantação.

  O servidor de data warehouse é configurado usando comandos da CLI do Azure, que segue a abordagem imperativa da prática do IAC. Considere o uso de scripts de implantação e integre-os no processo de automação.

• Considere preparar suas cargas de trabalho. Implante em vários estágios e execute verificações de validação em cada estágio antes de passar para o próximo estágio. Dessa forma, você pode enviar atualizações para seus ambientes de produção de forma altamente controlada e minimizar problemas de implantação imprevistos. Use estratégias de implantação azul-verde e lançamentos Canary para atualizar ambientes de produção ao vivo.

  Tenha uma boa estratégia de reversão para lidar com implantações com falha. Por exemplo, você pode reimplantar automaticamente uma implantação anterior bem-sucedida a partir do seu histórico de implantação. Consulte o parâmetro --rollback-on-error flag na CLI do Azure.

• O Azure Monitor é a opção recomendada para analisar o desempenho do seu armazém de dados e de toda a plataforma de análise do Azure para uma experiência de monitorização integrada. O Azure Synapse Analytics fornece uma experiência de monitoramento no portal do Azure para mostrar informações sobre sua carga de trabalho de data warehouse. O portal do Azure é a ferramenta recomendada ao monitorar seu data warehouse porque fornece períodos de retenção configuráveis, alertas, recomendações e gráficos e painéis personalizáveis para métricas e logs.

Para obter mais informações, consulte a seção DevOps no Microsoft Azure Well-Architected Framework.

Otimização de custos

A otimização de custos consiste em procurar formas de reduzir despesas desnecessárias e melhorar a eficiência operacional. Para obter mais informações, consulte Visão geral do pilar de otimização de custos.

Utilize a calculadora de preços do Azure para prever os custos. Aqui estão algumas considerações para serviços usados nessa arquitetura de referência.

Azure Data Factory

O Azure Data Factory automatiza o pipeline do ELT. O pipeline move os dados de um banco de dados SQL Server local para o Azure Synapse. Os dados são então transformados em um modelo tabular para análise. Para esse cenário, o preço começa a partir de $ 0,001 execução de atividade por mês, que inclui execuções de atividade, gatilho e depuração. Esse preço é a taxa base apenas para orquestração. Você também é cobrado por atividades de execução, como copiar dados, pesquisas e atividades externas. Cada atividade tem um preço individual. Você também é cobrado por pipelines sem gatilhos associados ou execuções dentro do mês. Todas as atividades são rateadas por minuto e arredondadas.

Exemplo de análise de custos

Considere um caso de uso em que há duas atividades de pesquisa de duas fontes diferentes. Um leva 1 minuto e 2 segundos (arredondado para 2 minutos) e o outro leva 1 minuto, resultando em tempo total de 3 minutos. Uma atividade de cópia de dados leva 10 minutos. Uma atividade de procedimento armazenado leva 2 minutos. A atividade total é executada por 4 minutos. O custo é calculado da seguinte forma:

Execuções de atividade: 4 * $ 0.001 = $0.004

Pesquisas: 3 * ($0.005 / 60) = $0.00025

Procedimento armazenado: 2 * ($0.00025 / 60) = $0.000008

Cópia de dados: 10 * ($0.25 / 60) * 4 unidades de integração de dados (DIU) = $0.167

• Custo total por execução de pipeline: US$ 0,17.
• Corra uma vez por dia durante 30 dias: $5.1 mês.
• Executado uma vez por dia por 100 mesas durante 30 dias: $ 510

Cada atividade tem um custo associado. Entenda o modelo de preços e use a calculadora de preços ADF para obter uma solução otimizada não apenas para desempenho, mas também para custo. Gerencie seus custos iniciando, parando, pausando e dimensionando seus serviços.

Azure Synapse

O Azure Synapse é ideal para cargas de trabalho intensivas com maior desempenho de consulta e necessidades de escalabilidade de computação. Pode escolher o modelo pré-pago ou utilizar planos reservados de um ano (37% de poupança) ou 3 anos (65% de poupança).

O armazenamento de dados é cobrado separadamente. Outros serviços, como recuperação de desastres e deteção de ameaças, também são cobrados separadamente.

Para obter mais informações, consulte Preços do Azure Synapse.

Analysis Services

O preço do Azure Analysis Services depende da camada. A implementação de referência dessa arquitetura usa a camada Desenvolvedor , que é recomendada para cenários de avaliação, desenvolvimento e teste. Outros níveis incluem, o nível Básico , que é recomendado para ambientes de produção pequenos, e o nível Standard para aplicativos de produção de missão crítica. Para obter mais informações, consulte A camada certa quando você precisar.

Não são aplicáveis custos quando coloca a instância em pausa.

Para obter mais informações, consulte Preços do Azure Analysis Services.

Armazenamento de Blobs

Considere usar o recurso de capacidade reservada do Armazenamento do Azure para reduzir o custo de armazenamento. Com este modelo, você obtém um desconto se puder se comprometer a reservar capacidade de armazenamento fixa por um ou três anos. Para obter mais informações, consulte Otimizar custos para armazenamento de Blob com capacidade reservada.

Para obter mais informações, consulte a seção Custo no Microsoft Azure Well-Architected Framework.

Próximos passos

• Introdução ao Azure Synapse Analytics
• Introdução ao Azure Synapse Analytics
• Introdução ao Azure Data Factory
• O que é o Azure Data Factory?
• Tutoriais do Azure Data Factory

Recursos relacionados

Talvez você queira revisar os seguintes cenários de exemplo do Azure que demonstram soluções específicas usando algumas das mesmas tecnologias:

• Data warehousing e análise para vendas e marketing
• BI Empresarial no Azure com Sinapse do Azure.