Primeiras aulas do curso ETL com Integration Services: modelo de dados

ETL com Integration Services: modelo de dados

Entendendo o nosso problema - Introdução ao curso

Olá, gente. Bem-vindos ao treinamento de Criando um Data Warehouse. Neste treinamento, nós vamos criar e carregar um Data Warehouse baseado numa Matriz Dimensão Indicador.

É importante que para fazer esse curso, vocês possam fazer antes o curso de Conceitos de Business Intelligence.

Isso porque no curso Conceitos de Business Intelligence nós apresentamos uma série de conceitos que usaremos nesse treinamento.

Apesar desse treinamento ser extremamente prático, e vamos aprender muita coisa manipulando as ferramentas do Microsoft SQL Server, nós iremos falar de termos e de conceitos que foram definidos no curso anterior.

Por isso é muito importante que vocês façam antes desse treinamento o curso de Conceitos de Business Intelligence.

Mesmo assim, nós vamos fazer uma pequena revisão da conclusão do treinamento.

Quais são os passos principais para a construção de um bom sistema de Business Intelligence?

Então, o primeiro passo para a construção desse sistema de Business Intelligence é a apresentação da Matriz Dimensão Indicador.

A Matriz Dimensão Indicador é o resultado das análises que os consultores de Business Intelligence fizeram na empresa que está sendo modelada.

Foram feitas várias entrevistas e aí se constrói a matriz onde nós cruzamos as dimensões de pesquisa com os indicadores.

Vemos toda a relação entre as dimensões, para saber se criamos ou não novas hierarquias, novos níveis, novos atributos.

E aí, a partir do momento que a matriz está construída, nós podemos definir o nosso Data Warehouse. Como ele será construído?

Quais serão as tabelas do Data Warehouse? Quais serão as suas chaves primárias? As chaves estrangeiras? As tabelas de dimensão?

Com o Data Warehouse definido, nós passamos a definir as fontes dos dados. Dentro dessa empresa existem vários sistemas transacionais que controlam o dia a dia da empresa.

Precisamos identificar e saber aonde vamos pegar os dados que serão fontes do nosso Data Warehouse.

Depois iremos determinar quais serão os processos de ETL. O ETL vem de Extract, Transact e Load, ou, traduzindo para o português, extrair, transformar e carregar. [2:54] É no momento do ETL que nós definimos as regras de negócio da empresa. Iremos definir, por exemplo, a unificação de conceitos.

E evitar que um indicador que no banco de dados operacional possui uma regra de negócios muito ligada àquela operação possa ter, dentro do Data Warehouse.

Um conceito único para que possamos nos dados calculados e nos relatórios a serem exibidos aos usuários finais, que possamos apresentar resultados corretos.

Pode ser que durante a carga do Data Warehouse nós podemos ter que precisar usar tabelas auxiliares, que nós chamamos de ODS, Operational Data Storage.

Bem, com os meus ODSs, os meus Data Warehouses construídos e com as minhas cargas, eu posso agora me preocupar com o meu banco de dados de consulta.

Que normalmente possuem o formato de OLAPs, que do inglês vem de Online Analytical Processing.

Os OLAPs são bancos que são desenhados para agilizar a consulta da informação, a consulta do dado.

Com os meus OLAPs construídos e carregados, nós agora vamos nos dedicar a entregar aos usuários finais as aplicações para que eles possam visualizar os resultados gerenciais da empresa.

É importante que nesse ponto você consiga identificar o que cada tipo de usuário espera do seu relatório.

Identificar a correta expectativa do usuário e fornecer a ele relatórios que satisfaçam essa expectativa é o fator chave de sucesso do projeto de BI.

Entendendo o nosso problema - Onde este curso se aplica

Então vemos aqui todo o esquema completo da construção de um sistema de Business Intelligence.

Esse curso não vai englobar nesse momento todas essas fases.

Nós iremos aprender a utilizar as ferramentas do Microsoft SQL Server em dois momentos desse processo de construção de uma base de Business Intelligence.

O primeiro momento será na construção dos Data Warehouse e eventualmente, se necessário, na construção dos ODSs.

Para fazer isso, iremos aprender e utilizar o banco Microsoft SQL Server. O Microsoft SQL Server é um banco de dados relacional, e é nele que iremos construir o nosso Data Warehouse.

Porém, não iremos apenas nos limitar ao Microsoft SQL Server, iremos ver uma outra ferramenta da Microsoft que, justamente, vai fazer todo o processo de ETL.

De buscar os dados das fontes dos sistemas transacionais, estar colocando dentro do Data Warehouse, ou do ODS se necessário.

A ferramenta que iremos aprender é a Microsoft Integration Services.

Então iremos nos limitar, neste treinamento, dentro do processo de Business Intelligence a apenas criar o Data Warehouse, criar o ODS e fazer a carga dos dados dos modelos transacionais para esses dois bancos de dados. Ok?

Entendendo o nosso problema - Matriz Dimensão Indicador da Sucos

Vamos agora conhecer a nossa empresa modelo, a empresa de suco de frutas. Nós iremos agora determinar a Matriz Dimensão Indicador da empresa.

Essa Matriz Dimensão Indicador será usada para todos os projetos práticos do curso de Business Intelligence.

Então eu, como consultor da empresa, junto na mesma sala todos os seus executivos, desde o jovem presidente da empresa de suco de frutas até os responsáveis pela área de vendas, financeiro, contabilidade, produção e até mesmo o pessoal da TI.

Depois de conversas, de entrevistas, de tentar entender como funciona a empresa, chegamos aos seguintes indicadores importantes para a empresa de suco de frutas.

Basicamente, a empresa quer ver: faturamento, em reais; o custo de frete; impostos; faturamento líquido; custo fixo; custo variável; a margem, que é o lucro da empresa;

As unidades vendidas; a quantidade vendida em litros, já que a empresa vende sucos; o preço médio.

As metas de faturamento e de custos, aquilo que eles esperam gastar ou esperam faturar no ano seguinte; e a variação dessas metas, em relação ao que realmente aconteceu.

Depois que eu determinei todos esses indicadores, ou seja, o que eles desejam ver, nós agora vamos descobrir como eles querem ver isso.

Então, após novas entrevistas, chegamos a seguinte conclusão. Eles querem ver aqueles indicadores por: data; cliente; segmento; estado; cidade; região.

Fábrica que fabricou o produto que está sendo entregue ao cliente; o produto, que também se organiza em sabor, marca, categoria.

E existe uma estrutura interna de vendas, que vai desde o atendente até o gerente de vendas e o diretor de vendas. Então é isso que a empresa de suco de frutas deseja ver.

Mais uma vez, após outras conversas, vamos tentar, antes de montar a nossa matriz, determinar se as quantidades tem uma relação de um para N ou de M para N.

E aí chegamos as seguintes relações: Cliente com segmento, cidade, estado e região; Produto com tamanho, sabor e marca, marca se divido em categorias.

Fábrica, uma entidade isolada. E nós temos o atendente, que se agrupa em regionais e que se agrupa em diretoria de vendas.

E aí, claro, depois de grupamento chegamos à conclusão que temos aqui quatro dimensões de pesquisa.

Claro, na verdade o nosso modelo vai ter cinco dimensões de pesquisa, porque o tempo é sempre obrigatório.

Dividi os indicadores em dois grandes grupos: os indicadores que eu chamaria de primários e os calculados.

Os primários vão estar nas tabelas de fato do Data Warehouse. E aí, discutindo com os meus usuários, chegamos a essa matriz.

Nós temos, cruzando com Tempo, Cliente, Produto, Fábrica e a estrutura de vendas, o Faturamento, o Imposto, o Custo Variável, Unidades Vendidas e Quantidades Vendidas em Litros diárias.

Lógico que esses indicadores que eu acabei de falar fazem parte da primeira tabela de fato, e esses dados serão diários.

Agrupando, novamente, cruzamentos iguais dentro da matriz, chegamos a uma segunda tabela de fato que só vai ter um indicador, o Custo de Frete, que também serão dados diários.

Por quê? Porque o Custo de Frete independe do atendente ou da área de vendas que efetuou a venda para o cliente.

Temos também o Custo Fixo, que é também uma outra tabela de fato, porque o Custo Fixo está só relacionado com a fábrica.

Ele não depende de que produto eu fabriquei, para que cliente eu vendi e qual foi o atendente ou vendedor que atendeu aquele cliente.

Mais uma tabela de fato seria a parte de Meta de Faturamento. A Meta de Faturamento é especificada por Cliente, Produto e área de Vendas, já que é esta que estipula essa meta.

Qual fabrica que vai atender? Só depois a logística vai determinar isso. Já a meta de custos quem determina é a área de logística e Produção, então é por Produto, por Fábrica e pelo Tempo.

Então temos aí cinco tabelas de fato e temos cinco dimensões de pesquisa: o Tempo, Cliente, Produto, Fábrica e a estrutura de Vendas.

Os indicadores calculados eu obtenho através dos indicadores primários, que vão estar lá na tabela de fato.

Então nós vamos ter aqui, o Faturamento Líquido, que vai ser o Faturamento em reais menos o Custo de Frete menos Impostos.

A margem, que é o lucro, que vai ser o Faturamento Líquido menos o Custo Fixo menos o Custo Variável; o Preço Médio, que vai ser o Faturamento divido pela Quantidade de Vendas em litros.

A Variação de Meta de Faturamento, que vai ser uma relação entre o faturamento real e o orçado; a Variação da Meta de Custo igual só que para o custo, uma variação entre o custo real e o custo orçado.

E, finalmente, a Variação do Lucro. Com essa matriz, com a definição das tabelas de fato e com os indicadores calculados, estamos prontos para implementar o nosso Data Warehouse, os nossos OLAPs e as nossas telas de consulta.

Agora, a partir daqui, é colocar a mão na massa e trabalhar para que o seu Data Warehouse, os OLAPs e as telas de consulta estejam o mais rápido possível disponíveis para os nossos usuários da empresa do suco de frutas.

A forma com que os OLAPs serão construídos é muito parecida com a carga do Data Warehouse. Primeiro nós iremos criar as dimensões.

Essas dimensões serão únicas dentro do meu servidor OLAP. Depois eu vou construir um OLAP para cada departamento.

Ou seja, vou construir o meu OLAP para o departamento de vendas, o de custos e o da presidência.

Um ponto importante para a construção dos nossos OLAPs é conhecer muito bem o nosso Data Warehouse, já que ele será fonte única dos dados.

Então no nosso Data Warehouse nós temos aqui a nossa dimensão Produto, que ela está num esquema Snowflake, com tabelas normalizadas, uma tabela para cada nível.

Nós temos a dimensão Cliente, que ela está num modelo estrela, tabelas desnormalizadas, todos os campos que representam todos os níveis na mesma tabela; a mesma coisa acontece na dimensão Fábrica.

A dimensão Organizacional é aquela dimensão pai e filho, onde você tem um alto relacionamento dentro da própria dimensão, que é usada para modelar dimensões irregulares.

Temos a dimensão Tempo, com uma série de pré-agregadores do tempo calculados a partir da data encontrada dentro da tabela de fato.

E nós temos cinco tabelas de fato: a Tabela de Fato 001, que cruza com todas as dimensões; a tabela 002, que cruza com todas menos a dimensão Organizacional.

A Fato_003, que cruza com Fábrica e o Tempo; a 004 vê Produto, Organizacional, Cliente e Tempo; e a 005 apenas Fábrica, Tempo e Produto.

O desenho dessas cinco tabelas de fato veio da Matriz Dimensão Indicador, que foi usado para a construção do Data Warehouse, que nós fizemos em cursos que antecederam a este treinamento.

Então, de posse dos OLAPs departamentais que nós queremos construir e conhecendo bem o leiaute das tabelas do Data Warehouse, nós podemos já inicializar a construção dos bancos de dados de consulta, ok?

Então, vamos seguindo.

Sobre o curso ETL com Integration Services: modelo de dados

O curso ETL com Integration Services: modelo de dados possui 148 minutos de vídeos, em um total de 51 atividades. Gostou? Conheça nossos outros cursos de Business Intelligence em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

Aprenda Business Intelligence acessando integralmente esse e outros cursos, comece hoje!

Plus

  • Acesso a TODOS os cursos da plataforma

    Mais de 1200 cursos completamente atualizados, com novos lançamentos todas as semanas, em Programação, Front-end, UX & Design, Data Science, Mobile, DevOps e Inovação & Gestão.

  • Alura Challenges

    Desafios temáticos para você turbinar seu portfólio. Você aprende na prática, com exercícios e projetos que simulam o dia a dia profissional.

  • Alura Cases

    Webséries exclusivas com discussões avançadas sobre arquitetura de sistemas com profissionais de grandes corporações e startups.

  • Certificado

    Emitimos certificados para atestar que você finalizou nossos cursos e formações.

  • Alura Língua (incluindo curso Inglês para Devs)

    Estude a língua inglesa com um curso 100% focado em tecnologia e expanda seus horizontes profissionais.

12X
R$85
à vista R$1.020
Matricule-se

Pro

  • Acesso a TODOS os cursos da plataforma

    Mais de 1200 cursos completamente atualizados, com novos lançamentos todas as semanas, em Programação, Front-end, UX & Design, Data Science, Mobile, DevOps e Inovação & Gestão.

  • Alura Challenges

    Desafios temáticos para você turbinar seu portfólio. Você aprende na prática, com exercícios e projetos que simulam o dia a dia profissional.

  • Alura Cases

    Webséries exclusivas com discussões avançadas sobre arquitetura de sistemas com profissionais de grandes corporações e startups.

  • Certificado

    Emitimos certificados para atestar que você finalizou nossos cursos e formações.

  • Alura Língua (incluindo curso Inglês para Devs)

    Estude a língua inglesa com um curso 100% focado em tecnologia e expanda seus horizontes profissionais.

12X
R$120
à vista R$1.440
Matricule-se
Conheça os Planos para Empresas

Acesso completo
durante 1 ano

Estude 24h/dia
onde e quando quiser

Novos cursos
todas as semanas