Sistema de apoio à decisão suportado por armazém de dados ... · recorrer a processos de Extract, Transform And Load (ETL) que extraem dados das respetivas fontes e procedem ao

FACULDADE DE ENGENHARIA DA UNIVERSIDADE DO PORTO

Sistema de apoio à decisão suportadopor armazém de dados na logística da

cadeia de abastecimento

Pedro Emanuel Caldas Ferreira

Mestrado Integrado em Engenharia Eletrotécnica e de Computadores

Orientador: Professor João Correira Lopes

Co-orientador: Rui Machado

27 de Julho de 2017

Resumo

Hoje em dia a gestão de uma cadeia de abastecimento é um processo bastante complexo.Desde o envio de mercadorias ao seu rastreamento existem vários processos a serem realizados.Para manter tudo de uma forma controlada é necessário um sistema que agregue clientes, produtos,ordens de compra e ordens de venda. Tipicamente é um sistema que usa uma base de dadosorientada a transações (CRUD - create, read, update e delete). Logo não será uma base de dadosotimizada para grandes análises de dados, uma vez que para a construção de um simples relatórioserão necessários várias junções de tabelas, o que torna o processo bastante fraco em termos dedesempenho.

É nesta área de negócio, gestão da cadeia de abastecimento, que a HUUB se posiciona. Destaforma, a HUUB fornece serviços de logística, tratando de todas as receções e envios de produtosdos seus clientes. Para fornecer um serviço diferenciado aos seus clientes, houve a necessidadeda construção de um sistema de análise de dados, capaz de dar resposta a todos os relatóriosnecessários para perceber tudo o que se passa na cadeia de abastecimento. Para isto é necessário odesenho de um Data Warehouse (DW) capaz de armazenar toda a informação produzida, seja elapelo sistema de informação usado pela HUUB, ou outra fonte de dados (Email, Google Analitycs,etc).

Para carregar toda a informação referente ao negócio para o Data Warehouse é necessáriorecorrer a processos de Extract, Transform And Load (ETL) que extraem dados das respetivasfontes e procedem ao seu armazenamento no Data Warehouse. Numa última fase, é necessária aconfiguração de ferramentas de visualização de dados capazes de produzir os relatórios e dashbo-ards necessários. Todo este processo se desenrola tendo por base os Key Performance Indicators(KPI) definidos. São estes KPI que nos dizem o que analisar e que informação armazenar no DataWarehouse.

Finalmente, depois de construído o sistema de apoio à decisão, foram disponibilizados pelaempresa os ficheiros que contém os relatórios produzidos.

Keywords: Base de dados, Indicadores de Desempenho, Armazém de Dados.

i

ii

Abstract

Nowadays all the supply chain management process it’s too complex. From merchandise sentto it’s tracking, there are several process to be made. To keep this in a manageable way it isnecessary a system able to store clients, products, purchase orders and sale orders. Typically thisis a system that uses a transaction oriented database (CRUD - create, read, update and delete). So,this transactional database, isn’t optimized to analyze large amounts of data because, to a smallreport, it’s necessary to join various tables, which turns all the process slow.

This is the HUUB business area: supply chain management. This way, the HUUB providelogistic services, dealing with all the product receptions and shipments from it’s clients. To providea differentiated service to it’s clients, there was the need to build a data analysis system, able toanswer to all the reports needed to understand whats going on all the supply chain. In order to dothis, it’s necessary to build a Data Warehouse cappable of store all the informations produced bythe company, coming from the information system used by HUUB, or other data source (Email,Google Analitycs, etc).

In order to load into the Data Warehouse all the information related to the business, it’s neces-sary to resort ETL processes that extract data from sources and proceed to it’s storage in the DataWarehouse. In a final phase, it’s necessary to configure data visualization tools capable of produceall the reports and dashboards needed. All this process develops based on all the Key PerformanceIndicators (KPI) defined. This KPI are the ones telling us what analyse and which information tostore in the Data Warehouse.

Finally, all the files containing the reports were made available on the company.

Keywords: Data Warehouse, Database, Key Performance Indicator

iii

iv

Agradecimentos

Queria, em primeiro lugar, agradecer ao Professor João Correia Lopes pela orientação e ajudaprestada.

A todos os meus colegas da HUUB por toda a ajuda e ambiente de trabalho, em especialao Eng. Rui Machado pelo acompanhamento, disponibilidade de esclarecimento de dúvidas eamizade.

À HUUB pela oportunidade de desenvolver este trabalho e pelas excelentes condições criadas.Aos meus amigos, não pela ajuda técnica, mas pelo suporte dado durante todo este trabalho.Por último, quero agradecer aos meus pais por tudo, pois sem eles esta dissertação não seria

possível.

Obrigado,Pedro Emanuel Caldas Ferreira

v

vi

Conteúdo

1 Introdução 11.1 Contexto e Motivação . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11.2 Enquadramento . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21.3 Objetivos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31.4 Estrutura da Dissertação . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4

2 Estado da Arte 52.1 Business Intelligence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52.2 ETL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62.3 Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

2.3.1 Normalização . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72.3.2 Modelo Dimensional . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

2.4 Diferença entre Métrica e KPI . . . . . . . . . . . . . . . . . . . . . . . . . . . 152.5 Resumo e Conclusões . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

3 Definição do Problema e Arquitetura da Solução 173.1 Definição do Problema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173.2 Proposta de Solução . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

3.2.1 Arquitetura Física . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193.2.2 Arquitetura Tecnológica . . . . . . . . . . . . . . . . . . . . . . . . . . 19

3.3 KPI e Métricas a Analisar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213.3.1 Operações . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213.3.2 Vendas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223.3.3 Compras . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233.3.4 Bus Matrix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

3.4 Resumo e Conclusões . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

4 Implementação 254.1 Modelo Base de Dados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254.2 Extração, Transformação e Carregamento de dados no DW . . . . . . . . . . . . 27

4.2.1 Área de Staging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284.2.2 Processo ETL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294.2.3 Data Marts com Modelo Star . . . . . . . . . . . . . . . . . . . . . . . 384.2.4 Visualização de Dados . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

4.3 Real Time Schema – OpsTV . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394.4 Resumo e Conclusões . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

vii

viii CONTEÚDO

5 Modelo de Visualização 435.1 Operações . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435.2 Vendas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485.3 Compras . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 525.4 OpsTV . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 545.5 Análise Quantitativa . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555.6 Resumo e Conclusões . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

6 Conclusões e Trabalho Futuro 576.1 Satisfação de Resultados . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 576.2 Trabalho Futuro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58

A Anexos 59A.1 Criação das tabelas do DV . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59

A.1.1 Código usado na criação da tabela hub_orderdrop . . . . . . . . . . . . . 59A.1.2 Código usado na criação da tabela link_orderdrop . . . . . . . . . . . . . 59A.1.3 Código usado na criação da tabela sat_orderdrop . . . . . . . . . . . . . 59

A.2 Criação das tabelas do schema control DV . . . . . . . . . . . . . . . . . . . . . 60A.2.1 Código usado na criação da tabela hub_orderdrop_error . . . . . . . . . 60A.2.2 Código usado na criação da tabela link_orderdrop_error . . . . . . . . . 60A.2.3 Código usado na criação da tabela sat_orderdrop_error . . . . . . . . . . 60

A.3 Vista da área de staging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61A.4 Vista representativa de uma tabela de facto (v_fact_orderdrop) . . . . . . . . . . 62A.5 Vista representativa de uma tabela de dimensão (dim_orderdrop) . . . . . . . . . 62A.6 Vista exemplo do real time schema . . . . . . . . . . . . . . . . . . . . . . . . . 63

Referências 65

Lista de Figuras

1.1 Posicionamento estratégico da HUUB . . . . . . . . . . . . . . . . . . . . . . . 2

2.1 Componentes de um ambiente Business Intelligence . . . . . . . . . . . . . . . . 62.2 Modelo Star . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82.3 Modelo Flat . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92.4 Modelo Terraced . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102.5 Modelo Snowflake . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112.6 Modelo Data Vault . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122.7 Exemplo de um modelo relacional . . . . . . . . . . . . . . . . . . . . . . . . . 122.8 Exemplo de um modelo DV . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132.9 Exemplo de um modelo star . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142.10 Modelo de um sistema híbrido . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

3.1 Arquitetura Física . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

4.1 Diagrama do sistema híbrido a ser implementado . . . . . . . . . . . . . . . . . 274.2 Carregamento de uma tabela hub . . . . . . . . . . . . . . . . . . . . . . . . . . 304.3 Logs de uma tabela hub . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314.4 Carregamento de uma tabela link . . . . . . . . . . . . . . . . . . . . . . . . . . 324.5 Logs de uma tabela link . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334.6 Carregamento de uma tabela satellite . . . . . . . . . . . . . . . . . . . . . . . . 344.7 Logs de uma tabela satellite . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344.8 Orquestrador das tabelas hub . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354.9 Orquestrador das tabelas link . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364.10 Orquestrador das tabelas satellite . . . . . . . . . . . . . . . . . . . . . . . . . . 364.11 Orquestrador de todo o processo ETL . . . . . . . . . . . . . . . . . . . . . . . 37

5.1 Taxa de preenchimento de ordens de venda . . . . . . . . . . . . . . . . . . . . 445.2 Taxa de preenchimento de ordens de compra . . . . . . . . . . . . . . . . . . . . 455.3 Picks efetuados durante o dia . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465.4 Informação de packs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 475.5 Localização de produtos em stock . . . . . . . . . . . . . . . . . . . . . . . . . 485.6 Associação entre produtos e ordens de venda . . . . . . . . . . . . . . . . . . . 495.7 Ordens de venda e drops . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505.8 Shipping Cost . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 515.9 Hierarquia da tabela de dimensão de ordem de venda . . . . . . . . . . . . . . . 525.10 Ordens de venda recebidas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 535.11 Items associados a ordens de venda . . . . . . . . . . . . . . . . . . . . . . . . . 535.12 Hierarquia entre ordem de compra e receção . . . . . . . . . . . . . . . . . . . . 54

ix

x LISTA DE FIGURAS

5.13 OpsTV . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555.14 Desempenho processo ETL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

Lista de Tabelas

3.1 Bus Matrix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

xi

xii LISTA DE TABELAS

Abreviaturas e Símbolos

ETL Extract Transform and LoadBI Business IntelligenceAPI Application Programming InterfaceOLTP Online Transaction ProcessingOLAP Online Analytical ProcessingDW Data WarehouseKPI Key Performance IndicatorBD Base de DadosDM Data MartDV Data VaultSLA Service Level Agreement

xiii

Capítulo 1

Introdução

Este documento detalha todo o trabalho desenvolvido durante a elaboração desta dissertação.

Para isso são apresentados, numa primeira fase, todos os aspetos relacionados com o tema desen-

volvido: o enquadramento, a motivação e os objetivos do trabalho. Nesta dissertação está também

presente uma análise feita ao estado da arte, de modo a contextualizar o leitor para os conceitos

aqui abordados. É também apresentada a arquitetura da solução proposta, bem como a sua imple-

mentação. Por fim são apresentados os resultados obtidos com o trabalho desenvolvido, que neste

caso serão relatórios (reports) expondo vários Key Performance Indicators (KPI) definidos para

análise.

1.1 Contexto e Motivação

Num mundo cada vez mais globalizado, uma empresa que queira expandir de forma sustentá-

vel o seu negócio, necessita, cada vez mais, de um bom sistema de apoio à decisão que permita a

monitorização de todo o funcionamento do negócio. Para tal, existe a necessidade da criação de

indicadores que permitam o entendimento de todos os dados guardados.

Pretende-se com este trabalho o desenvolvimento de um sistema que, recorrendo a uma base

de dados, concretize em gráficos todos os dados operacionais de movimentação de stock, para um

melhor entendimento do que se está a passar em toda a cadeia de distribuição. Para a realização

desta plataforma é necessária uma base de dados capaz de tratar todos os dados operacionais

gerados ao longo do tempo, para que posteriormente seja mais rápida a sua obtenção por parte do

sistema de apoio à decisão.

Este projeto foi proposto pela HUUB, uma empresa relativamente recente (fundada em 2015),

que possui já na sua carteira de clientes um vasto leque de empresas, maioritariamente marcas

de roupa. Todos os seu clientes são bons profissionais no que toca ao desenho da roupa e sabem

onde se colocar para fazer chegar o seu produto ao maior número de pessoas. No entanto, não

possuem competências a nível tecnológico para desenvolverem uma plataforma que permita a

gestão e monitorização de toda a cadeia de distribuição.

1

2 Introdução

A HUUB disponibiliza aos seus clientes um serviço completo de recolha dos produtos junto

dos fornecedores dos seus clientes e distribui os produtos pelas lojas. Para que todo este processo

decorra sem problemas, é necessário um sistema que monitorize e registe todas as alterações de

estado de um produto ao longo de toda a cadeia de distribuição. Desta forma, irá ser desenvolvida

uma plataforma capaz de agregar todos os dados operacionais gerados sobre todos os produtos,

com o intuito da criação de indicadores que ajudem na perceção de tudo o que se está a passar na

cadeia de distribuição.

Num mundo cada vez mais competitivo, a rapidez do acesso à informação é fulcral na tomada

de decisões sobre o negócio. O sistema de apoio à decisão a desenvolver, permitindo uma fácil e

rápida perceção do estado do fluxo de produtos, dá uma vantagem competitiva à HUUB relativa-

mente aos seus concorrentes. É assim fornecida toda a informação de uma forma “legível” para

os seus clientes, permitindo-lhes tomadas de decisão baseadas no que se está a passar em toda a

cadeia de distribuição.

1.2 Enquadramento

Todo este projeto foi desenvolvido na HUUB, que é uma empresa de gestão da cadeia de

abastecimento dos seus clientes. De forma a esclarecer os conceitos de cliente da HUUB, cliente

final e fornecedor é apresentado um esquema alusivo ao processo de negócio da HUUB.

Figura 1.1: Posicionamento estratégico da HUUB

Na Figura 1.1 está posicionamento estratégico da HUUB. A HUUB está posicionada entre

as marcas (clientes da HUUB) e os clientes das marcas (customers). As marcas têm dois canais

de venda: Wholesale (B2B) e Webshop (B2C). Assim, a HUUB funciona como um intermediário

entre os seus clientes e os clientes dos seus clientes. Desta feita, as marcas que trabalhem com a

HUUB não terão de se preocupar com toda a cadeia de distribuição, sabendo que têm um parceiro

que realiza todas as tarefas de recolha de mercadoria e o seu envio para o destino.

A HUUB recebe as ordens de compra dos seus clientes e posteriormente recebe a mercadoria

nos seus armazéns. A mercadoria recebida pode ser rececionada para stock, ficando armazenada,

ou então ser direcionada para ordens de venda, sendo este processo apelidado de crossdocking.

Quando um cliente vende um dos produtos, comunica à HUUB a ordem de venda e é preparada a

1.3 Objetivos 3

saída de mercadoria do armazém para o destino indicado pelo cliente. Todo este processo pode pa-

recer simples, quando explicado desta forma, mas possui uma complexidade considerável, devido

ao elevado número de casos distintos que podem ocorrer.

Os clientes da HUUB têm maioritariamente dois canais de venda: venda a retalhistas ou ven-

das online diretamente ao consumidor final. Em ambos os casos é comunicado para a HUUB a

ocorrência da venda e esta procede ao envio das peças, caso as tenha em stock. Caso contrário, é

necessário esperar pela chegada do(s) produto(s) para se cumprir a ordem de venda.

1.3 Objetivos

O objetivo deste trabalho é o de desenvolver um sistema de apoio à decisão capaz de recolher

toda a informação proveniente da base de dados transacional em uso, com o intuito de formar

indicadores para futuramente serem analisados.

Sendo a HUUB uma empresa de gestão da cadeia de abastecimento dos seus clientes, existem

dois grandes tipos de indicadores que podem ser consultados: indicadores a nível da operação in-

terna da empresa e indicadores de negócio. Indicadores internos da empresa dizem respeito a tudo

o que é realizado internamente, como tempos de resposta, tempos de preparação de encomendas,

entre outros que estejam ligados ao funcionamento interno. Já indicadores de negócio dizem res-

peito a número de vendas de um determinado cliente, qual o país para onde determinado cliente

mais vende, entre outros.

De forma a poder apresentar em relatórios estes indicadores, é necessária a modelação de

uma base de dados capaz de armazenar toda a informação operacional da empresa e, quando

solicitada, devolver esta informação. Este pedido de informação poderia ser feito à base de dados

transacional, mas seria necessário efetuar uma série de operações internas da base de dados, o

que levaria a tempos de resposta bastante altos e incomportáveis. Isto deve-se ao facto de uma

base de dados transacional estar orientada a transações, isto é, a ações de inserção, atualização,

eliminação e leitura. Assim, ações de consulta de informação são bastante demoradas. No caso de

ser necessário a criação de um relatório com determinado indicador, são necessárias várias junções

de tabelas, o que leva a tempos de resposta bastante altos.

A criação de um Data Warehouse (DW) procura eliminar este problema, uma vez que a mode-

lação de dados é já orientada à análise de dados. É aqui que se debruça este projeto: a criação de

um sistema de apoio à decisão capaz de dar resposta a quem necessita de tomar decisões dentro

da empresa. Para isto é necessária a criação e modelação de um DW capaz de armazenar toda a

informação de forma a que sejam facilitadas as consultas destes dados.

Para o desenvolvimento do DW, será consultada a base de dados já implementada pela HUUB

(base de dados transacional), que contém toda a informação sobre os produtos, desde dados de

vendas ou compras a dados inerentes ao próprio produto. No entanto, é necessário o tratamento

de todos estes dados operacionais e posterior carregamento dos mesmos num DW para permitir

uma rápida consulta de dados. O processo de extração dos dados da base de dados original será

realizado através de um processo de Extract Transform And Load (ETL), que irá extrair os dados

4 Introdução

da base de dados original e os irá carregar, depois de um processo de transformação, no DW. Este

processo de extração e transformação dos dados, é o processo mais custoso a nível de Business

Intelligence (BI) uma vez que exige entre 60% a 80% de todo o trabalho, como é dito no relatório

publicado pelo The Data Warehousing Institute [1].

Este sistema de apoio à decisão tem como objetivo proporcionar um repositório único e centra-

lizado na empresa, dando apenas uma versão da verdade a todos os utilizadores. Permite também

um rápido acesso à informação, uma vez que apenas com uma instrução de consulta, são evi-

tadas várias ações de junção de tabelas na base de dados original, devido à informação ter sido

previamente tratada e trabalhada no sentido de fornecer um rápido acesso.

1.4 Estrutura da Dissertação

Para além da introdução, esta dissertação apresenta mais cinco capítulos.

No Capítulo 2 é apresentado o estado da arte do projeto, nomeadamente os conceitos de BI,

ETL e DW. Também neste capítulo é apresentada a diferença entre métrica e KPI.

No Capítulo 3 é detalhado o problema, bem como a arquitetura de solução pensada para a sua

resolução. Neste caso é apresentada a arquitetura tecnológica e quais os KPI ou métricas a ser

analisadas.

No Capítulo 4 é detalhada a implementação do DW. Aqui são apresentados todos os processos

ETL e como foram desenhados, bem como a estrutura interna do DW, como a apresentação do mo-

delo de dados e a forma como foram montados os DM com o modelo star. Por fim, é apresentado

o schema real time criado para dar resposta ao dashboard de operações internas do armazém.

No Capítulo 5 são apresentados todos os reports desenvolvidos ao longo do projeto, com a

ilustração dos indicadores para apoio à decisão.

Por fim, no Capítulo 6 são apresentadas as conclusões gerais do projeto. Também neste capí-

tulo é apresentado o trabalho futuro, de forma a dar continuidade ao projeto.

Capítulo 2

Estado da Arte

Neste capítulo serão apresentados todos os conceitos e técnicas relacionados com o trabalho.

Serão abordados os conceitos de BI, DW, ETL. No caso de DW serão abordadas várias técnicas de

modelação, de forma a expor alternativas à modelação de dados. Quanto ao processo ETL, será

explicado o que é este processo e qual o seu objetivo dentro deste projeto. O conceito de BI é

abordado, não por este projeto ser um projeto típico de BI, mas por ser importante entender o seu

significado, uma vez que está muito ligado ao uso de DW.

Por último, serão retiradas conclusões do trabalho de investigação realizado para a concretiza-

ção deste projeto, bem como às escolhas feitas para a modelação do DW.

2.1 Business Intelligence

Com o elevado ritmo de mudança observável nos dias de hoje, é importante para todas as

empresas o uso de técnicas de Business Intelligence (BI) para uma escolha fundamentada na hora

da tomada de decisões. É cada vez mais relevante o uso de técnicas que permitam uma rápida

observação de todos os dados referentes ao funcionamento de cada empresa. Esta melhoria de

performance está documentada e estudada e, segundo Wayne Eckerson, cerca de 61% das empresas

(de uma amostra de 510) conseguiram poupar tempo quando começaram a usar técnicas de BI.

Destas 510, cerca de 57% conseguiram melhores decisões estratégicas e 56% foram capazes de

tomar melhores decisões táticas. Por fim, cerca de 39% destas 510 empresas foram capazes de

poupar [1].

Segundo Negash [2], os sistemas de BI “combinam recolha de dados, armazenamento de da-

dos e gestão de informação, através de ferramentas analíticas, de modo a apresentar informação

complexa e competitiva aos responsáveis pelas decisões”. No caso da HUUB, é necessária a reco-

lha de dados em bruto que se encontram numa base de dados transacional, transformar estes dados

e, por fim, armazená-los num DW, para, posteriormente, serem consultados de uma forma rápida

e simples. Isto permite tomadas de decisão mais rápidas e fundamentadas em factos reais.

Na Figura 2.1 são apresentados os componentes de um sistema de BI. Conforme apresentado

na figura, um sistema BI é composto por um processo de ETL e um DW. O processo ETL é

5

6 Estado da Arte

responsável pela recolha de dados provenientes de uma base de dados transacional ou qualquer

outra fonte de dados, transformação dos dados e armazenamento no DW. Os dados provenientes da

base de dados transacional, são dados em bruto e portanto necessitam de ser combinados seguindo

regras de negócio, para serem posteriormente guardados. O DW guarda todos os dados já tratados,

para serem consultados de uma forma simples e rápida.

Figura 2.1: Componentes de um ambiente Business Intelligence

2.2 ETL

A principal função de um processo ETL é a recolha de dados (Extract), a sua transformação

(Transform) e carregamento no DW (Load). Como referido por Cristiano Xavier e Fernando Mo-

reira [3], um processo ETL deixa de ser apenas um processo de recolha de informação, uma vez

que possui a capacidade de correção de erros e normalização de dados, caso estes sejam proveni-

entes de fontes distintas. Desta feita, informação em bruto passa a ter um significado, permitindo

assim ser analisada posteriormente.

Contextualizando o problema, a HUUB possui uma base de dados transacional que armazena

toda a informação referente a todos os produtos dos seus clientes, como por exemplo, informação

sobre vendas, compras e detalhes. Todos estes dados são guardados em bruto, o que obriga a

combinar tuplos (join)1 para realização de relatórios ou representação de Key Performance Indi-

cator (KPI)2. De forma a obter a informação necessária, em tempo “útil”, é necessário o uso de

processos ETL de forma a realizar previamente todas as junções de tabelas necessárias.

Um processo ETL é constituído por várias etapas:

• Extract — nesta etapa são extraídos os dados necessários para o DW. Nesta fase é dado

ênfase à performance da pesquisa e extração dos dados, logo não são retiradas informações

desnecessárias ou redundantes, apenas informação necessária ao projeto em curso.

• Transform — esta fase pode ser considerada a etapa mais importante, uma vez que é res-

ponsável pela validação, consolidação e aplicação das regras de negócio sobre os dados.

1Função de junção de tabelas inerente à linguagem SQL que permite combinar linhas de 2 ou mais tabelas2Medida quantificável que permite inferir se os objetivos propostos estão ou não a ser cumpridos

2.3 Data Warehouse 7

• Load — uma vez realizadas as operações de transformação, os dados são carregados no

DW. Os dados a serem guardados podem substituir os já existentes ou então acrescentar os

novos dados. Esta última alternativa permite-nos uma visão global ao longo do tempo.

2.3 Data Warehouse

O conceito de Data Warehouse (DW) é, segundo Inmon [4], “um repositório de dados orga-

nizados por assunto, integrados, variáveis no tempo, e não voláteis, que suportam a tomada de

decisões dos gestores”. Desta forma, os principais objetivos do DW, em BI, são:

• guardar informação facilmente consultada — aqui são guardados os resultados do processo,

já tratados para uma rápida representação

• guardar informação consistente — todos os dados a guardar necessitam de serem previa-

mente validados

• guardar informação histórica — todos os dados aqui guardados permitem um rastreamento

histórico de todos os KPI analisados

2.3.1 Normalização

O conceito de normalização, inerente ao Modelo Relacional de uma base de dados, foi apre-

sentado por Edgar Codd. No seu livro “The Relational Model Database Management” [5], o

autor refere que a normalização de uma base de dados aumenta a integridade dos dados. No

entanto, este aumento de integridade é afetado por uma perda de performance nas operações de

consulta. Esta perda de performance nas operações de consulta deve-se ao facto de uma base de

dados transacional ser otimizada para operações CRUD. Estas operações são create (C), aquando

da criação de novos tuplos, read (R), para leitura dos diversos tuplos, update (U), para atualização

de informação e delete (D), para eliminação de tuplos. Neste caso a operação read apenas pode

ser considerada aquando de uma leitura simples da BD, isto é, não possuindo grande complexi-

dade. Desta forma operações de consulta em que é necessário o cruzamento de várias dimensões,

tornam-se impraticáveis em cima de uma BD transacional, quando esta possui uma dimensão

considerável de dados.

De acordo com os objetivos do projeto, pretende-se um sistema que possua um rápido acesso

a dados, logo é necessário retirar complexidade nas pesquisas a dados. Para tal, não é considerada

normalização nesta base de dados. Por norma, apenas as bases de dados transacionais (OLTP)

são normalizadas, enquanto que as bases de dados OLAP são não normalizadas, uma vez que são

bases de dados apenas de consulta, não havendo inserções ou modificações que, por isso, permitem

análises de grandes volumes de informação.

8 Estado da Arte

2.3.2 Modelo Dimensional

O paradigma de uma base de dados transacional é completamente diferente do ambiente de

uma base de dados multidimensional. Segundo Kimball [6], as técnicas usadas para desenhar uma

base de dados transacional não podem ser aplicadas ao desenho de um DW. É então que Kimball

propõe o uso de uma técnica a que chamou dimensional modelling. Este é um método baseado na

prática e foi sendo aperfeiçoado através de observações e da experiência de pessoas que trabalham

em apresentação de dados de uma forma “user-friendly”. É, portanto, um método não verificado

teoricamente, mas é bastante útil no desenho de um DW.

Existem vários tipos de modelações possíveis: star schema, flat schema, terraced schema,

snowflake schema e data vault. Cada um destes tipos de modelação é apresentado e detalhado de

seguida.

2.3.2.1 Modelo Star

Para Kimball [6], fazer a modelização dimensional é uma técnica “antiga usada para simpli-

ficar bases de dados”. Este mesmo autor apresenta uma abordagem, o modelo Star represntado

na Figura 2.2 3. Este modelo consiste em dois tipos de tabelas: as fact tables e as dimension

tables. As fact tables guardam informação acerca de um evento ocorrido (o facto). No caso de

uma venda, na sua fact table, irá constar informação sobre a data da venda, o identificador do

produto, o identificador do vendedor e todas as métricas associadas. Já nas dimension tables está

presente a informação detalhada sobre determinado produto, como por exemplo, a descrição e as

suas caraterísticas. Desta forma, as fact tables têm apenas chaves estrangeiras, que apontam para

o respetivo tuplo nas dimension tables, e as métricas associadas ao facto em questão.

Figura 2.2: Modelo Star

As tabelas de factos são compostas apenas por elementos transacionais e não possuem chave

primária. A sua chave primária é composta pela conjunção das chaves estrangeiras de todas as3Figura retirada de https://social.technet.microsoft.com/wiki/contents/articles/1238.star-schema.aspx


entidades componentes. As tabelas de dimensão são formadas por cada entidade componente,

contextualizando assim cada entidade presente nas tabelas de facto. Por último, todos os atributos

das tabelas de facto devem ser numéricos e métricos. Caso contrário há a necessidade da criação

de uma nova tabela para agregar estes atributos e atribuir um identificador numérico e métrico,

para ser utilizado na tabela de facto.

2.3.2.2 Modelo Flat

Como referido por Moody [7] este tipo de modelação é o mais simples. Numa modelação

flat todas as entidades do modelo de dados são colapsadas em entidades mínimas. Desta forma é

reduzido o número de tabelas. É um tipo de modelação que não leva a perda de informação, uma

vez que possui redundância. No entanto, quando tentamos agregar valores numéricos de relações

hierárquicas com relações transacionais, podemos ter erros de agregação. Outro problema do uso

deste tipo de modelação é o facto de termos baixa complexidade relacional, devido ao reduzido

número de tabelas, mas temos uma elevada complexidade elementar, uma vez que cada tabela

possui bastantes atributos.

A Figura 2.3 apresenta um exemplo deste tipo de modelação.

Figura 2.3: Exemplo do modelo Flat

2.3.2.3 Modelo Terraced

Neste tipo de modelação, obtém-se uma arquitetura composta apenas pelas entidades transaci-

onais, sendo criada uma tabela por cada uma destas entidades, como é possível verificar na Figura

10 Estado da Arte

2.4. Neste caso, cada tabela é composta pela mesma chave primária da base de dados relacional e

não são possíveis operações de agregação [7].

Figura 2.4: Exemplo do modelo Terraced

2.3.2.4 Modelo Snowflake

No modelo Star, as hierarquias dos dados originais são colapsadas ou desnormalizadas em ta-

belas de dimensão. Cada tabela de dimensão poderá possuir múltiplas dependências hierárquicas.

Um modelo snowflake é como um modelo Star, mas com todas as dependências explicitamente

representadas no modelo, como mostra a Figura 2.5. Para se chegar a este modelo, é possível

seguir o procedimento apresentado por Moody [7] partindo do modelo Star e desnormalizar todas

as tabelas, retirando todas as dependências hierárquicas. Podemos também partir do modelo de

dados relacional:

• cada fact table é composta por cada entidade transacional e a sua chave primária é a combi-

nação das chaves das entidades que a compõe.

• cada entidade é transformada numa tabela de dimensão.

• onde existam dependências hierárquicas, é criada uma nova tabela (child) que irá agregar

toda a informação da relação.

• os atributos numéricos das entidades transacionais devem ser transformados em chaves.

2.3.2.5 Data Vault

O modelo Data Vault (DV) foi desenvolvido por Dan Linstedt durante 10 anos de trabalho.

Este modelo começou por ser desenvolvido em 1990 e só foi lançada a sua primeira versão no ano


Figura 2.5: Exemplo do modelo Snowflake

2000. A definição dada por Linstedt [8] é: “Data Vault é um modelo orientado ao detalhe, que

permite um rastreamento histórico através de ligações entre tabelas normalizadas dando suporte a

uma ou várias áreas de negócio. É um modelo híbrido, uma vez que tira partido do melhor que

a 3FN dá e das vantagens do star schema. Possui um desenho flexível, escalável, consistente e

adaptável às variações do negócio”.

Neste tipo de modelação existem 3 tipos de tabelas: as tabelas hub, as tabelas link e as tabelas

satellite. As tabelas hub são constituídas pelas business keys4 provenientes da base de dados

transacional. Neste tipo de tabelas está também presente uma chave primária (que pode ser um

identificador incremental ou então o hash da business key), a data em que o DW vê pela primeira

vez a informação e, ainda, um identificador do sistema que originou a informação. Desta forma é

possível associar cada tuplo de uma tabela hub a um tuplo da tabela original presente na base de

dados transacional. As tabelas link são responsáveis pela representação das ligações entre dois ou

mais hub. Neste tipo de tabelas estará presente uma chave externa que aponta para cada um dos

hub associados. Nas tabelas link haverá também uma coluna responsável por guardar a data do

momento em que os dados foram carregados no DW e um identificador do sistema de origem da

informação. Por último, nas tabelas link também está presente uma chave primária incremental, ou

o resultado de hash das chaves estrangeiras. Por fim, temos as tabelas satellite que dão o contexto

do negócio. Desta forma estas tabelas terão uma chave primária composta pela chave estrangeira

do hub ou link que carateriza e ainda a data a que a informação foi carregada. Estas tabelas são

ainda compostas por todos os atributos necessários para caraterizar o hub ou link associados. Na

Figura 2.65 está representada uma arquitetura genérica do modelo Data Vault.

Para uma melhor perceção do modelo DV, na Figura 2.7 é mostrado um esquema exemplo de

um modelo relacional de uma base de dados que guarda informação referente a clientes, produtos,

ordens de compra e todas as relações para compor informação referente a uma ordem de compra,

dada por um determinado cliente e com todos os items associados. De relembrar que o termo

4Chave que identifica o tuplo no sistema de origem, de acordo com regras de negócio5Figura retirada de https://www.talend.com/blog/2015/03/27/what-is-the-data-vault-and-why-do-we-need-it/

12 Estado da Arte

Figura 2.6: Modelo Data Vault

cliente, no contexto deste trabalho, não significa cliente final, mas sim, cliente de uma empresa de

logística que dá ordens de compra e venda de produtos.

Figura 2.7: Exemplo de um modelo relacional

Como podemos verificar este é um modelo típico de uma base de dados transacional em que

temos informação referente a clientes (tabela client), produtos (tabela product) e ordens de compra

(tabela purchaseorder). Temos ainda uma tabela purchase_item que tem como função relacionar

uma ordem de compra com os diversos items que a compõe. Esta relação necessita de ser repre-

sentada por uma nova tabela, uma vez que corresponde a uma relação n:n, logo seria impraticável

adicionar colunas extra, quer do lado da tabela purchaseorder, quer do lado da tabela product, para

representar esta ligação.

Na Figura 2.8 é apresentado o modelo DV retirado deste modelo relacional.

Neste modelo é possível verificar a existência de tabelas hub, link e satellite. Assim temos

um hub por entidade do modelo relacional (hub_product, hub_client e hub_purchaseorder). Por

cada uma destas tabelas existe uma tabela satellite associada, que dá todo o contexto do negócio

associado ao hub. São as tabelas sat_client, sat_product e sat_purchaseorder. Para associar uma

purchaseorder ao cliente é criada a tabela link_purchaseorder_client que possui “foreign keys”6

6campo ou conjunto de campos que identificam única e exclusivamente um tuplo de uma tabela numa outra tabela


Figura 2.8: Exemplo de um modelo DV

que apontam para os hubs correspondentes à relação. Também a relação entre purchaseorder e

products é dado por uma tabela link, neste caso a tabela link_purchaseorder_items. Nesta tabela

também estão presentes “foreign keys” que apontam para os hub correspondentes. Todos os res-

tantes campos foram já mencionados anteriormente e são transversais a todas as tabelas link. Para

representar a quantidade de cada produto numa determinada ordem de compra é criado um sa-

tellite, sat_purchaseorder_items que contém o identificador da linha do link que possui a relação

entre determinado produto e uma ordem de compra e uma coluna qnt que nos dá a quantidade.

Todas as restantes colunas são colunas de auditoria e são transversais a todos os satellites.

Como referido anteriormente, para o criador deste modelo, Dan Linstedt, DV pressupõe o

uso de um sistema híbrido. Com esta definição, o autor afirma que para o uso do modelo DV

é necessário o uso do modelo star em conjunto. Desta feita, toda a informação proveniente de

qualquer sistema de informação é carregada para o DW, modelado com DV. De seguida toda esta

informação é passada a Data Marts (DM). Cada DM representa uma porção do DW e é a camada

de acesso aos dados guardados no DW para o utilizador. É através de cada um dos DM que serão

construídos os relatórios pretendidos. Cada um dos DM são modelados com o modelo star, sendo

compostos por tabelas de factos e tabelas de dimensões. O recurso a este sistema híbrido requer

uma complexidade superior quando comparado ao uso isolado do modelo star.

Na Figura 2.9 é representado o modelo em estrela (star) correspondente ao modelo anterior.

Neste caso é acrescentada uma dimensão que não constava no modelo relacional apresentado

anteriormente. Isto deve-se ao facto de, como o modelo star se designar a análise de dados, haver

a necessidade de possuir informação relativa à data que um simples timestamp não garante, como

por exemplo, possuir um campo para dia, mês e ano separadamente para permitir futuras análises

com diferentes granularidades temporais.

14 Estado da Arte

Figura 2.9: Exemplo de um modelo star

No entanto o modelo star não oferece flexibilidade para possíveis alterações no decorrer do

projeto. Um dos problemas decorrente desta falta de flexibilidade do modelo star é a alteração

da granularidade. Com o uso deste modelo, uma mudança de granularidade requer uma alteração

profunda ao modelo de dados, sendo portanto um modelo inflexível a alterações. Por outro lado,

o modelo star permite uma melhor performance no que a queries para obtenção de dados diz

respeito. O modelo DV destaca-se do modelo star no que a flexibilidade diz respeito, uma vez que

é um modelo mais tolerante a pequenas alterações, tanto aos dados como à forma como estes têm

de ser organizados. No caso de uma alteração de granularidade, é mais simples quando comparado

ao modelo star. Desta feita, o modelo star permite uma melhor performance na obtenção de dados,

enquanto que o modelo DV permite uma maior flexibilidade na organização dos dados. Assim,

com o uso de um sistema híbrido, conseguimos retirar vantagens dos dois modelos. Na Figura

2.10 está representada a arquitetura de um sistema híbrido, com os sistemas fonte de informação,

o DW e por fim os DM.

Figura 2.10: Modelo de um sistema híbrido

2.4 Diferença entre Métrica e KPI 15

Como referido por Krneta, Jovanovic e Marjanovic [9], recorrer a um sistema híbrido traz di-

versas vantagens. Estes autores referem mesmo que um modelo DV é “uma solução para integrar,

armazenar e proteger dados”. No entanto, estes mesmos autores referem que este modelo “não é

aconselhado para queries ou relatórios muito extensos”. Desta forma, recomendam o uso de um

sistema híbrido, com uma camada de armazenamento com o modelo DV e uma camada de acesso

a dados, DM, modelados com o modelo star. Para fazer a ponte entre os dois modelos, as tabelas

de dimensão do modelo star são obtidas através das tabelas hub e das suas tabelas satellite. As

tabelas de facto são construídas através das tabelas de link e seus satellites.

Ao modelo star, montado com vistas à frente do DV, é possível fazer queries de consulta de

dados. Os resultados destas queries podem ser usados para funções de reporting e dashboarding.

Fica aqui a definição de reporting e dashboarding:

• reporting : como é dito por Gregory Hills [10] reporting ou management reporting é "o

fornecimento de informação a quem toma decisões dentro de uma empresa, sob a forma de

gráficos, textos ou tabelas". O autor refere ainda que este tipo de documentos é, tipicamente,

distribuído numa intranet 7, através de uma aplicação web, ou de emails.

• dashboarding : este tipo de visualização de dados não possui uma definição clara. Como

referido por João Assunção Duarte [11], dashboarding, genericamente, pode ser definido

como uma "interface gráfica que contém medidores de performance de um determinado ne-

gócio que facilita a tarefa de gestão". Considerando apenas esta definição não há grande

diferença para reporting. No entanto, o mesmo autor refere que, após desenvolvimentos

de design, dashboarding é visto como uma ferramenta de visualização e gestão de perfor-

mance, apresentando num único ecrã todos os indicadores mais importantes, alertando o

utilizador para situações anormais. Como referido por Pauwels [12] um dashboard ajuda

gestores a perceber tendências, padrões e anomalias existentes na área de negócio onde está

inserido.

2.4 Diferença entre Métrica e KPI

Estes dois conceitos estão relacionados e tendem a ser confundidos. Uma métrica serve de

base para a formação dos KPI e apenas representa dados brutos sobre a operação. Não é possível

através de uma métrica perceber a evolução do trabalho efetuado por forma a atingir determinados

objetivos traçados. As métricas dividem-se em três tipos: aditivas, semi-aditivas e não aditivas.

• Métricas Aditivas: as métricas aditivas são aquelas que podem ser agrupadas independente-

mente da dimensão escolhida. Por exemplo, o número de items vendidos pode ser agrupado

por data (dia, mês, ano), por cliente, canal de venda, sem haver uma perda de consistência

da informação. Este é o tipo de métrica mais flexível e sem quase nenhuma restrição.

7Rede privada de computadores, que apenas pode ser acedida por máquinas internas à rede

16 Estado da Arte

• Métricas Semi-Aditivas: este tipo de métrica representa as métricas que podem ser agru-

padas ou sumarizadas, dependendo das dimensões. Isto é, apenas podem ser agrupadas em

alguns casos. Por exemplo, o saldo de uma conta bancária não pode ser somado tendo em

conta a dimensão data. Se numa conta bancária temos 1000 C num dia e os mesmos 1000

C no dia seguinte, ao fazermos a soma para estes dois dias, temos um resultado de 2000 C,

resultado este que não traduz qualquer informação. No entanto esta mesma métrica poderá

fazer sentido agrupar para um dia, de todas as contas bancárias, dando assim informação

sobre a quantidade total ao final do dia.

• Métricas Não Aditivas: estas métricas são aquelas que não podem ser sumarizadas de

forma alguma. Exemplo deste tipo de métricas são taxas (valores relativos). No caso da

taxa de fulfillment de uma ordem, não faz sentido somar taxas de fulfillment de duas ordens

distintas.

Já KPI (do inglês “Key Performance Indicator”) é um indicador construído com base em

métricas e tem como objetivo a perceção do alcance das metas estabelecidas. No caso do marketing

digital, uma métrica poderá ser o número de visitantes de uma página, enquanto que um KPI

poderá ser a taxa de conversão de visitantes em vendas. Em suma, um KPI é uma medida de

performance usada para avaliar o sucesso de uma atividade.

Desta forma, para uma boa escolha de que KPI e métricas queremos medir, temos de ter

uma boa perceção do negócio ou área de negócio que queremos monitorizar. Para este projeto,

posteriormente a uma análise da atividade da empresa e reuniões com os futuros utilizadores deste

sistema, foi definido um conjunto de KPI e métricas que faz sentido analisar no contexto do

negócio da HUUB.

2.5 Resumo e Conclusões

No final da leitura deste capítulo espera-se que o leitor fique contextualizado acerca dos di-

versos conceitos e técnicas que serão abordados durante esta dissertação. Numa primeira fase foi

abordado o tema de BI e como o uso de técnicas de BI podem ajudar ao desenvolvimento de uma

empresa. De seguida, foi explicado em que consiste um processo ETL e quais as suas fases. Por

fim, foi abordado o tema de DW e quais os possíveis modelos de dados a usar. Chegou-se à con-

clusão que a melhor opção seria a de optar pelo modelo híbrido, que apesar de mais complexo,

traz diversas vantagens, sobretudo a nível de flexibilidade, que é um aspeto a ter em conta no

desenvolvimento deste trabalho. Por fim, são apresentados os conceitos de métrica e KPI e qual a

sua diferença.

Capítulo 3

Definição do Problema e Arquitetura daSolução

Neste capítulo é apresentada a definição do problema, bem como a arquitetura do sistema

pensada para a sua resolução.

Nas duas primeiras secções deste capítulo é apresentado o problema e qual a perspetiva de

solução. De seguida é apresentada a arquitetura de solução pensada. É apresentado o modelo de

dados do DW escolhido, como foram desenhados os processos ETL e a passagem dos dados do

modelo DV para o modelo star, para depois serem construidos todos os reports e dashboards.

Neste capítulo é também apresentado o schema real time que tem como função alimentar um

dashboard com informação sobre a evolução do trabalho efetuado ao longo do dia na HUUB.

Por fim, são retiradas todas as conclusões acerca da arquitetura, a sua implementação e os

resultados obtidos.

3.1 Definição do Problema

Hoje em dia, com as rápidas mudanças no mercado, as empresas necessitam de se adaptar

com um ritmo elevado. Para poder proceder a estas rápidas alterações, do lado da gestão, têm de

ser tomadas decisões no menor espaço de tempo. Para que estas decisões sejam tomadas rapida-

mente, os gestores necessitam também de ter acesso a relatórios e representações dos diferentes

indicadores (KPI) no menor tempo possível.

Como toda a informação necessária é guardada em base de dados, torna-se imperativa uma

consulta à base de dados para obter informação necessária à representação dos KPI. Ora, estes

KPI não estão guardados de forma a serem diretamente retirados da base de dados. São neces-

sárias várias funções de junção de tabelas para obter o KPI desejado. No caso de termos poucas

tabelas e poucos tuplos em cada tabela, estas operações de junção são relativamente rápidas, sendo

insignificante a demora na sua obtenção. No entanto, quando temos uma base de dados com um

número considerável de tabelas e informação referente a um grande número de transações de pro-

dutos diárias, a demora na execução de junção de tabelas, torna-se significativa. Para combater

17

18 Definição do Problema e Arquitetura da Solução

este atraso, é necessário o uso de ferramentas de ETL. Durante o processo de ETL é necessário

retirar a informação necessária aos diferentes KPI desejados, que será proveniente de várias ta-

belas da base de dados transacional ou qualquer outra fonte de informação, fazer o tratamento da

informação e, por fim, carregar estes dados já tratados no DW.

Como é dito por Miguel Paulo [13], nos tempos correntes, cerca de 90% dos utilizadores de

uma empresa fazem uso de técnicas de BI para tomar as suas decisões. Para tal, necessitam de

constante contacto com o DW. Como foi dito anteriormente, são estes processos de ETL que irão

extrair os dados necessários da base de dados transacional, transformá-los de forma a que seja fácil

e rápida a representação dos KPI e, por fim, carregar estes dados no DW, que será depois acedido

por uma aplicação de visualização de dados.

Posteriormente ao desenho de todas as operações de ETL, é necessária a configuração de uma

aplicação de visualização de dados capaz de contactar com o DW para retirar a informação pre-

viamente transformada. Esta aplicação necessita de ser capaz de retirar a informação e construir

gráficos ou tabelas, para uma representação visual de toda a informação guardada em base de

dados.

3.2 Proposta de Solução

O desenvolvimento de um sistema que resolva o problema aqui exposto não é trivial. São

necessários vários processos, desde processos ETL, o desenho de um DW e, por fim, a configuração

de uma aplicação capaz de retirar toda a informação do DW e representar esta informação sob a

forma “visível” e perfeitamente entendida por parte de quem necessita de tomar decisões com base

na informação guardada.

A solução proposta para este projeto passa, numa primeira fase, pela definição dos processos

ETL para retirar e agregar toda a informação necessária da base de dados operacional. Para realizar

esta operação, será usado o software Pentaho Data Integration (mais conhecido como Kettle).

Para a definição do DW é necessário a escolha de um modelo dimensional a ser utilizado

no desenho do DW. Como foi apresentado anteriormente, foram estudados 5 tipos de modelos: o

modelo star, o modelo flat, o modelo terraced, o modelo snowflake e, por fim, o modelo DV. Pelos

motivos apresentados na Secção 2.3.2 não foi decidido nenhum destes modelos isoladamente. Foi

escolhido seguir o caminho do modelo híbrido, que tira proveito das vantagens do modelo star e

do modelo DV. Assim, é possível tirar vantagem da flexibilidade do modelo DV e da performance

a nível de queries do modelo star. Desta forma foram descartados os modelos flat, terraced e

snowflake, uma vez que não cumprem os requisitos necessários à realização do projeto.

Por último, é necessária a configuração de ferramentas de visualização de dados, de forma a

ser possível consumir dados provenientes do DW montado. Dentro destas ferramentas é neces-

sário montar reports e/ou dashboards com a informação pretendida por quem necessita de tomar

decisões baseadas nos KPI definidos.

3.2 Proposta de Solução 19

3.2.1 Arquitetura Física

Figura 3.1: Arquitetura Física

Na Figura 3.1 está presente o diagrama de componentes do sistema. O primeiro componente

é o servidor onde está alojada a base de dados transacional e funciona como fonte de dados. O

segundo componente é o servidor onde são executadas as rondas ETL. Neste servidor é onde é

atualizada a área de staging com informação relevante à ronda ETL em execução. Assim, este

servidor começa por carregar toda a informação a ser processada da fonte de dados para a área de

staging presente no servidor onde está alojado o DW. Neste componente estão também presentes

todos os scripts Kettle responsáveis pela ronda ETL, isto é, a extração de informação da área de

staging, a sua transformação e carregamento no DW. O último componente é o precisamente o

servidor que alberga do DW, bem como a área de staging e os DM. Estes DM são apenas vistas em

cima do modelo DV. É aos DM que os utilizadores terão acesso e onde irão consultar informação.

3.2.2 Arquitetura Tecnológica

Dada a complexidade do projeto, é necessária a escolha de determinadas tecnologias. A pri-

meira escolha a ser feita é a escolha do software a utilizar para realizar o processo de ETL. Aqui

a escolha recaiu sobre o software Pentaho Data Integration. Outra decisão a ser tomada é a tec-

nologia de gestão de base de dados. Neste caso foi escolhido o software PostgreSQL. Para a


visualização de dados foi escolhido o Microsoft Power BI. De seguida são apresentadas as razões

para a escolha de cada uma destas tecnologias.

3.2.2.1 Pentaho Data Integration

O software Pentaho Data Integration é o componente do Pentaho responsável por processos

ETL. Esta é uma ferramenta simples de ser utilizada e gratuita. O seu ambiente gráfico permite a

definição de todos os processos ETL, sem que seja necessário a escrita de código. É assim, uma

ferramenta orientada a metadados1. Suporta vários tipos de ficheiro de entrada, no entanto, numa

fase inicial apenas temos dados provenientes de uma base de dados transacional [14].

3.2.2.2 PostgreSQL

Para todo o armazenamento de dados será usado o software de gestão (DBMS) PostgreSQL. É

uma distribuição gratuita e desenvolvida por voluntários que dão o seu contributo para o projeto.

Desta forma, não é um software controlado por qualquer corporação e o seu código fonte é livre

para quem quiser utilizar e alterar.

O facto de ser um sistema livre é um fator a seu favor, uma vez que é um dos requisitos

da empresa para a realização deste projeto de reporting e dashboarding. Uma outra possível

solução que cumpre este requisito seria MySQL. No entanto, como a base de dados operacional

é desenvolvida em PostgreSQL, apenas é possível o uso de dblink (usados para a construção das

views da área de staging) com o uso de PostgreSQL uma vez que esta ferramenta dblink apenas

está disponível em PostgreSQL. Por fim, o PostgreSQL é indicado para data warehousing, uma vez

que possui um bom conjunto de rotinas e funções analíticas que permitem manipulação de dados,

que no caso de MySQL seriam processos mais complexos e demorosos.

Neste projeto será usado o PostgreSQL, na sua versão 9.5.

3.2.2.3 Microsoft Power BI

O Microsoft Power BI é um software que agrega um conjunto de ferramentas que permite a

análise de dados provenientes de várias fontes. Com esta ferramenta é possível a ligação a diversas

fontes de informação, entre elas uma base de dados PostgreSQL, que é a tecnologia usada para a

concretização do DW [15].

Para além disto o Power BI permite a publicação em cloud 2 de todos os reports feitos. Assim,

é simplificada a partilha de reports por quem os faz e por quem os consome. Outra vantagem

deste software é o facto de possuir também aplicação para Android e para IOS, permitindo assim

a fácil visualização de todos os reports em ambiente móvel. Este software permite também a

criação de métricas através dos dados importados. É uma funcionalidade que pode ser útil quando

é necessária a visualização de uma métrica que é a diferença entre duas colunas da mesma tabela

ou tabelas diferentes.1Metadados são dados sobre dados, dando informação sobre o que trata cada dado guardado2Modelo de armazenamento online

3.3 KPI e Métricas a Analisar 21

A única limitação que esta ferramenta possui é o facto de a frequência mais alta de refres-

camento de dados ser de 1 hora. No caso de funções reporting esta limitação não é muito sig-

nificativa, uma vez que um report não terá um tempo de vida inferior a 1 hora. Já no caso de

dashboarding esta limitação é importante, uma vez que os dados que são apresentados num dash-

board devem ser em tempo real, ou com um período de atualização o mais baixo possível.

No entanto, esta ferramenta mostra-se bastante capaz de cumprir com as funções de reporting.

3.2.2.4 Microsoft Excel

Também para a produção de reports pode ser usado o Microsoft Excel. Este é um software que

permite a visualização e edição de folhas de cálculo. Como fonte de dados é possivel configurar

uma base de dados. No contexto deste projeto, o Excel é usado para construir alguns dos reports

necessários, uma vez que tem a capacidade de extrair dados de uma base de dados PostgreSQL,

neste caso o DW.

Para ter um poder de manipulação de dados ainda maior é possível o uso do plugin Power

Pivot [16]. Com este plugin é possível a modelação de modelos de dados sofisticados. O Power

Pivot permite a criação de relações entre tabelas de forma a produzir tabelas com informação

cruzada entre tabelas.

3.3 KPI e Métricas a Analisar

Depois de percebido todo o processo de negócio da empresa, foram definidos vários KPI e

métricas a analisar. São estes KPI que vão alimentar os reports necessários. Esta lista é apenas

um ponto de partida para todo este sistema de reporting e dashboarding. Estas métricas e KPI

podem ser divididos por categorias, sendo elas operações, vendas e compras. Cada uma destas

categorias representa um departamento da empresa e representam, dentro do DW, cada um dos

DM e configurar.

3.3.1 Operações

Nesta secção são apresentados os KPI e métricas dentro da categoria de operações.

• so_fulfillment_tax: dentro do contexto de negócio da HUUB faz todo o sentido o cálculo

da taxa de preenchimento de todas as ordens de venda. Assim foi definida esta métrica para

mostrar qual a taxa de preenchimento das ordens de venda. De uma forma muito simples,

esta métrica é calculada através do quociente entre a quantidade de produtos efetivamente

enviados e a quantidade de produtos estimada. Esta é o exemplo de uma métrica não aditiva,

já que não faz sentido adicionar taxas de preenchimento de diferentes ordens de venda.

• po_fulfillment_tax: dentro da mesma lógica do item anterior, po_fulfillment_tax representa

a taxa de preenchimento de todas as ordens de compra. Neste caso, este resultado é obtido

através do quociente entre a quantidade de produtos que foi rececionada e a quantidade de


produtos que seria estimado receber. Assim como a métrica anterior, esta é também uma

métrica não aditiva.

• picks: para o controlo de toda a operação interna da HUUB é necessária a contabilização

da quantidade de picks de produtos a partir do stock para ordens de venda. Esta quantidade

de picks pode ainda ser partida em picks para ordens de Webshop e ordens de Wholesale.

Isto pode ser feito apenas com um simples filtro aplicado no report. É ainda necessária a

contabilização do número de items recebidos, isto é, picks associados a receções. Esta é uma

métrica aditiva, já que podem ser sumariados os valores de picks diários independentemente

da dimensão (data, cliente, tipo de ordem, canal de venda).

• number_packs_dispatched: número de packs expedidos. É uma métrica aditiva, já que

pode ser agrupada pelas diferentes dimensões que a compõe.

• number_items_pack: número de items presentes em cada pack. É uma métrica aditiva.

• stock_location: número de items presentes em cada posição de stock ao final de cada dia.

É uma métrica semi-aditiva uma vez que apenas pode ser agrupada segundo algumas das

dimensões que a compõe. Não faz sentido somar o stock de uma localização de um dia com

o stock da mesma localização do dia seguinte. No entanto, faz sentido somar a quantidade

de produtos de todas as localizações para um dado dia, de forma a obter o número total de

produtos em stock nesse dia.

3.3.2 Vendas

Nesta secção são apresentados os KPI e métricas associados a vendas.

• number_received_so_orders: representa o número de ordens de venda recebidas. Assim

como a anterior, esta é uma métrica aditiva.

• number_cancelled_so_orders: esta métrica apresenta o número de ordens de venda cance-

ladas. É uma métrica aditiva, uma vez que pode ser agrupada por data, cliente e canal de

venda.

• number_items_sold: outra métrica definida é o número de items transacionados. Esta é

uma métrica aditiva, isto é, pode ser agrupada independentemente da dimensão. Pode ser

calculado o número de items vendidos por cliente, país, canal de venda e data.

• quantity_items_cancelled_so: representa a quantidade de items de cancelados numa dada

ordem de venda. Também esta é uma métrica aditiva.

• shiping_cost_by: cada ordem de venda (SO) é partida em várias parcelas (drops) e cada

uma destas drops está associada a um envio. Assim, esta métrica representa o valor total de

todos os envios associados a cada ordem de venda. É também uma métrica aditiva, podendo

ser agrupada por várias dimensões (data, canal de venda, cliente, destino).

3.3 KPI e Métricas a Analisar 23

3.3.3 Compras

Nesta secção são apresentados os KPI e métricas associados a compras.

• number_received_po_orders: representa o número de ordens de compra recebidas. É tam-

bém uma métrica aditiva.

• number_cancelled_po_orders: esta métrica apresenta o número de ordens de compra can-

celadas. É uma métrica aditiva, uma vez que pode ser agrupada por data, cliente e fornece-

dor.

• quantity_items_ordered_po: representa a quantidade de items de numa dada ordem de com-

pra. Também esta é uma métrica aditiva.

• quantity_items_cancelled_po: representa a quantidade de items de cancelados numa dada

ordem de compra. Também esta é uma métrica aditiva.

3.3.4 Bus Matrix

Tendo estes KPI, o próximo passo é o desenho da Bus Matrix. Segundo Kimball [6], as

dimensões criadas são usadas por diversas tabelas de facto. As dimensões que são partilhadas em

várias tabelas de facto são apelidadas de dimensões conformes. São estas dimensões que permitem

a visualização conjunta de métricas de tabelas de facto distintas. Este uso de dimensões conformes

permite ainda um menor tempo de desenvolvimento do sistema e ainda diminui a redundância, já

que caraterísticas de uma dimensão podem ser usadas como filtros em diversas tabelas de facto,

onde a dimensão está associada.

Dimensões Conformes

Processos de negócio Date Product Huub ClientSalesorder-Orderdrop

Shipment Customer Sales Channel Purchaseorder Location Supplier Pack Status Warehouse

SO Fulfillement x x x x xPO Fulfillement x x x xPicks x x x x x xShipping Cost x x x x x xPacks Dispached x x x x x x xPack Fulfillment x x x x x x xStock Location x x x x x xSales x x x x x x xSales Items x x x x x x x xPurchase x x x x xPurchase Items x x x x x x

Tabela 3.1: Bus Matrix

Na Tabela 3.1 está representada a Bus Matrix do sistema, com os processos de negócio asso-

ciados e as dimensões. Como é possível verificar, existem diversas dimensões conformes a vários

procesoss de negócio. Uma delas é a data (Date) bem como o HUUB Client. De resto, é possível

verificar que dimensões se cruzam, e em que tabelas de facto.



No final deste capítulo o leitor fica a conhecer o problema e a forma como a solução foi dese-

nhada. Dentro do problema foi exposto qual o sistema a desenvolver e a complexidade associada

ao mesmo.

Na solução proposta são apresentadas as tecnologias necessárias à sua implementação e quais

as escolhas a serem feitas numa fase inicial para obter a solução apresentada. Como tinha sido

anteriormente referido, a solução proposta passa pelo uso de um sistema híbrido, isto é, o uso

conjunto do modelo DV e do modelo star. Para a representação do modelo star foi escolhido o

uso de vistas em cima do DV, por forma a permitir flexibilidade. Com o uso de vistas, todos os

DM passam a ser virtuais e, portanto, bastante flexíveis a possíveis alterações que possam surgir.

Na fase final do capítulo são apresentados os KPI a analisar, detalhando o seu significado. É

também aqui apresentada a bus matrix do sistema, que tem por objetivo relacionar dimensões e

processos de negócio, permitindo a reutilização de trabalho desenvolvido na definição das várias

dimensões.

Capítulo 4

Implementação

Neste capítulo da dissertação é apresentada a implementação da solução proposta deste pro-

jeto. Aqui é explicado como foi montado o DW, desde o modelo escolhido, bem como exemplos de

scripts usados. Posteriormente, é apresentado o template usado no sistema, expondo como foram

desenhados todos os processos ETL, de forma a serem o mais uniformes possíveis entre tabelas.

Será também abordada a forma como se desenharam todos os DM modelados com o modelo star.

Por fim, é exposta a forma como foi utilizado o Power BI para visualização de dados.

Também neste capítulo é explicado o schema real time. Este foi um schema criado na base

de dados onde está alojado o DW, separado do schema do DV, de forma a possuir informação em

tempo real necessária para determinados dashboards.

4.1 Modelo Base de Dados

De forma a criar o DW contendo todas as tabelas e schemas necessários, foram criados vários

scripts SQL. Caso haja algum problema no futuro e seja necessário a re-criação do DW, basta a

execução destes scripts e o DW fica montado e pronto a receber dados.

Também aqui foi dado ênfase à estruturação dos ficheiros. Foi criado um script por cada tipo

de tabela. Assim sendo, foi criado um script responsável pela criação de todas as tabelas hub,

bem como as suas limitações de chaves primárias e estrangeiras. De seguida, foi criado um script

responsável pela criação das tabelas link. Também este script cria todos os constrangimentos de

chaves primárias e estrangeiras de cada link. Por último, foi desenvolvido um script para a criação

das tabelas satellite assente no mesmo princípio dos anteriores.

Como é possível verificar no Anexo A.1.1, na criação da tabela hub é necessário um campo

incremental que nos dá o id referente a cada orderdrop do lado do DW, bem como uma coluna

que guarda a informação referente ao identificador no lado do sistema fonte da informação. Este

campo necessita de uma restrição, sendo considerado um campo com valores distintos, ou seja,

não é possível a criação de dois ou mais tuplos com o mesmo orderdrop_id. No caso da tabela

link (Anexo A.1.2), esta possui o seu id auto-incremental, bem como todos os id dos hubs que

25

26 Implementação

o compõe, neste caso, hub_orderdrop_id e hub_saleorder_id, significando assim que este link

relaciona uma orderdrop a uma saleorder.

Por fim temos, o exemplo de uma tabela satellite no Anexo A.1.3. Neste caso temos o iden-

tificador do hub referente à orderdrop que está a ser contextualizada. Neste tipo de tabelas temos

um número variável de colunas, que dão informação acerca da orderdrop que está a ser analisada

(created_by, orderdrop_date, etc.).

De notar que todas estas tabelas possuem um campo load_date, que regista o selo temporal

aquando do registo da informação pela primeira vez, que está definido por omissão como sendo o

valor atual do relógio do sistema. Outro campo transversal a todas as tabelas é o campo rec_SRC

que indica qual o sistema fonte de informação. Está definido o valor 1 uma vez que para este

projeto, apenas existe uma fonte de informação.

Foi também necessário o desenvolvimento de scripts para a criação do schema control, bem

como todas as suas tabelas. Neste caso, foram criados scripts para a criação das tabelas de erro

dos hub, link e saltellites. Foi também necessária a criação de uma tabela audit execution que

guarda informação sobre o início e término de cada ronda ETL para cada tabela, seja ela hub, link

ou satellite. Por fim, foi necessário o desenvolvimento de um script responsável pela criação de

todas as materialized views, no schema staging, desenvolvido na Secção 4.2.1.

No Anexo A.2.1 está presente excerto do script SQL para a criação das tabelas de erro re-

ferentes aos hub. Neste caso temos o exemplo do hub_orderdrop_error que possui um campo

orderdrop_id que indica a identificação da orderdrop no sistema fonte, bem como três colunas

adicionais referentes ao erro obtido na inserção de uma orderdrop (error_desc, error_field e er-

ror_code).

No Anexo A.2.2 está presente um exemplo da criação de uma tabela de link de erro, neste caso

link_orderdrop_error. Neste caso temos uma coluna referente ao hub_orderdrop (hub_orderdrop_id),

que nos diz qual o identificador do hub que deu origem ao erro na inserção. A mesma lógica é

aplicada à coluna hub_saleorder_id, mas neste caso é um identificador da tabela hub_saleorder.

Nesta tabela, ao contrário da link_orderdrop, temos ainda duas colunas adicionais: orderdrop_id e

saleorder_num. Estas colunas permitem perceber qual a orderdrop ou saleorder que deram origem

ao problema na inserção, já que estes dois identificadores representam a orderdrop ou saleorder

no sistema fonte. Neste tipo de tabelas (link_error), temos também os 3 campos que nos dão

informação adicional sobre o erro gerado (error_desc, error_field e error_code).

Por fim, no Anexo A.2.3, é apresentado o código SQL necessário para a criação de uma ta-

bela de erro referente a um satellite. Neste caso a tabela sat_orderdrop_error possui um campo

hub_orderdrop_id que tem como função a mesma que tem na tabela link_orderdrop_id, isto é,

a identificação do tuplo na tabela hub_orderdrop. Também nesta tabela está presente um campo

orderdrop_id que contém o identificador do sistema fonte da orderdrop que deu origem ao erro.

Desta forma, na ronda seguinte, basta fazer uma pesquisa para encontrar o identificador do hub da

orderdrop por forma a inserir os dados no satellite. Como acontece nas tabelas de erro de hubs e

links, também nas tabelas de erro dos satellites estão presentes 3 campos com informação sobre o

erro gerado (error_desc, error_field e error_code).

4.2 Extração, Transformação e Carregamento de dados no DW 27

Com o uso destas tabelas de erro, podemos, a cada ronda ETL, verificar quais as linhas que

não foram inseridas corretamente no DW, e perceber o que originou o erro. No caso de haver

algum problema na inserção de uma linha na tabela hub_orderdrop, haverá também um erro na

inserção da tabela link_orderdrop, uma vez que o identificador da orderdrop no sistema de origem

não foi encontrado na tabela hub_orderdrop. O mesmo também se aplica à tabela satellite. No

caso de tentarmos inserir informações na tabela sat_orderdrop sem primeiro preencher a tabela

hub_orderdrop, vamos-nos deparar com um erro de inserção no satellite. Neste caso, como a chave

primária do satellite possui o campo hub_orderdrop_id, este não pode ser nulo. Ora, como a tabela

hub_orderdrop não possui qualquer tuplo referente à orderdrop que estamos a tentar atualizar no

lado do satellite, este campo (hub_orderdrop_id) será nulo e, portanto, dará origem a um erro de

inserção.

Desta feita, com todos os scripts separados por tipo de tabela, é possível a criação fácil e rápida

do DW, apenas sendo necessário cuidado na ordem de execução. Como é óbvio não é possível a

criação de uma tabela link, sem primeiro proceder à criação dos hubs que fazem parte do link. Isto

deve-se ao facto de cada tabela link possuir chaves estrangeiras que apontam para hubs. O mesmo

acontece para as tabelas satellite.

4.2 Extração, Transformação e Carregamento de dados no DW

De modo a implementar com sucesso o modelo híbrido de dados, definido na Secção 4.1, é

necessário um cuidado especial na modelação do DW. Para obter uma boa performance é neces-

sário ter em atenção vários fatores que contribuem para a velocidade na obtenção de dados. Todo

este projeto é desenvolvido tendo em conta possíveis mudanças, portanto toda a sua organização

é pensada para que alterações nos sistemas fonte não reproduzam grandes alterações no lado do

DW.

Tendo em conta as questões de performance e flexibilidade, foi pensada a arquitetura presente

na Figura 4.1, bastante semelhante à apresentada na Figura 2.10. No entanto, é acrescentada uma

área de staging. Este componente adicional permite uma maior rapidez na obtenção de dados dos

sistemas fontes.

Figura 4.1: Diagrama do sistema híbrido a ser implementado

28 Implementação

4.2.1 Área de Staging

Esta é a primeira fase de todo o processo ETL. Como referido anteriormente, é nesta etapa que

são carregados todos os dados necessários a qualquer ronda ETL. É uma fase crítica do processo,

uma vez que se não for bem planeada e otimizada, irá afetar a performance de todo o sistema de

forma significativa.

De modo a atenuar o efeito desta fase, é feito um pré-carregamento de todos os dados ne-

cessários a uma dada ronda ETL para uma área de staging. Desta forma, quando a ronda ETL é

iniciada, irá carregar todos os dados necessários provenientes da área de staging, evitando assim

ligações persistentes com os sistemas fonte da informação. Esta mudança traz várias vantagens a

nível de performance, uma vez que qualquer processo ETL necessita de realizar diversas junções

de informação ou pesquisas extensivas de vários tuplos. Sem esta área de staging implementada o

que iria acontecer, no caso de uma junção de um tuplo ou tabela com outra tabela do sistema fonte,

seriam pesquisas ou junções caso a caso, tuplo a tuplo. Assim, por cada tuplo, seria necessária

uma ligação ao sistema fonte, o que nos traz bastante latência no processamento dos dados.

Para montar esta área de staging é criada um schema1 nomeado staging que não irá conter

qualquer tabela, apenas irá conter vistas materializadas (materialized views) 2. Fazendo uso deste

tipo de vistas, com a informação guardada em memória, são obtidos tempos de resposta mais

baixos quando comparado ao uso de vistas guardadas em disco. Esta diferença de tempos deve-se

ao facto de o acesso a memória ser bastante mais rápido que o acesso ao disco.

Como a definição de vista materializada nos diz, este tipo de vista é apenas o resultado de uma

query guardada em memória. Como o DW é guardado numa máquina diferente da base de dados

operacional, esta query necessita de ser executada remotamente. Como a tecnologia usada para o

armazenamento de dados é PostgreSQL, podem ser usados dblinks. Um dblink é uma ferramenta

do PostgreSQL que nos permite solucionar o problema desta fase do processo: a execução remota

de queries à base de dados de origem.

Assim, na definição das vistas do schema staging, todas as queries são executadas recorrendo

a dblinks. Desta forma é como se o DW estivesse armazenado na mesma máquina que a base de

dados transacional. Isto permite “puxar” todas as alterações posteriores à ultima ronda de ETL para

o lado do DW, sendo depois iniciado o processo ETL propriamente dito, não sofrendo latências na

fase de transformação dos dados.

De modo a garantir que a informação presente nesta área de staging é informação correta e

posterior à ultima ronda de ETL, é necessária uma atualização de todas as vistas do schema staging.

Para uma correta atualização deste schema é utilizada uma tabela audit execution. Nesta tabela

apenas é guardada informação referente à data e hora do início e fim de cada ronda ETL, bem

como que tabela foi atualizada. Desta forma, qualquer query presente na construção das vistas da

1Coleção de objetos, dentro de uma base de dados, que permite uma maior segurança, organização e administraçãodos objetos de dados

2Vista é um objeto de dados que é nada mais nada menos que o resultado de uma query SQL. O facto de ser umavista materializada apenas nos diz que é uma vista em que os dados são guardados em memória e não em disco


staging area, apenas “puxam” informação que foi criada ou atualizada no sistema fonte, posterior

à data de conclusão do processo ETL anterior.

Um exemplo do código necessário à criação de uma tabela de staging é apresentado no Anexo

A.3.

No caso aqui apresentado, são puxadas para staging todas as alterações necessárias à atuali-

zação da informação referente a orderdrops. Numa primeira fase temos a criação da vista com

o CREATE MATERIALIZED VIEW. Aqui podemos também constatar o uso de dblinks para con-

sultar a informação do sistema fonte. De modo a realizar remotamente a query pretendida, é

necessário recorrer ao objeto public.dblink. Neste objeto é necessário passarmos o host que possui

a base de dados onde queremos executar a query, a porta de ligação à base de dados, os dados de

autenticação (user e password) e, por último, o nome da base de dados a que nos queremos ligar.

Posto isto, temos de indicar a query a ser executada. Neste caso, a query lê informação de todas

as orderdrops existentes em sistema, e realiza uma série de joins para obter toda a informação

pretendida. São exemplo disto o join utilizado para se obter o utilizador que a criou (join com a

tabela auth_user), o join usado para obter a salesorder referente a cada orderdrop e ainda mais

dois joins usados para obter informação referente ao status da orderdrop e a sua localização dentro

do armazém.

Por último, é aplicado um filtro à informação recebida, para retirar todos os valores que não

interessam à ronda ETL a ser iniciada. Para isto é feito o WHERE link.created_at > . . . que irá ve-

rificar quando foi realizada a última ronda de ETL em que foi atualizada a tabela hub_orderdrop ou

sat_orderdrop. Após ter a informação de quando é que foi realizada a última ronda de ETL, toda

a informação anterior a esta data é ignorada, uma vez que foi já carregada para o DW. A decisão

de quando foi a última ronda de ETL é realizada através do resultado da query que aparece depois

de WHERE link.created_at. Esta query consulta a tabela audit_execution e procura pelos tuplos

que tenham informação referente a hub_orderdrop ou sat_orderdop. Do valor da data máxima

referente a hub_orderdrop e a sat_orderdop, é escolhido o valor mínimo entre estes dois valores

máximos. Caso a tabela audit_execution não contenha informação referente a nenhuma das tabe-

las referidas anteriormente, é utilizado um valor por omissão, definido como sendo “2000-01-01

01:00:00”, permitindo assim ao sistema carregar todas as linhas da tabela referente às orderdrops,

uma vez que no sistema fonte apenas temos informação posterior ao início do ano de 2016, logo

ao puxarmos todas as alterações posteriores a 2000, garantimos que conseguimos obter toda a in-

formação presente no sistema fonte. Esta decisão permite um primeiro carregamento do DV sem

alteração posterior necessária para realizarmos um carregamento incremental.

4.2.2 Processo ETL

Na execução de todo o processo ETL, como referido na Secção 3.2.2.1, é utilizado o Pentaho

Kettle. Nesta fase do processo é necessário um cuidado especial a organizar todos os ficheiros de

forma a que possíveis alterações que ocorram do lado da base de dados operacional não resultem

em grandes alterações do lado do DW.

30 Implementação

Assim, e tendo em conta esta flexibilidade necessária ao projeto, serão criados três tipos de

ficheiros: um para o carregamento das tabelas hub, um para o carregamento das tabelas link e,

por último, um para o carregamento das tabelas satellite. Todos estes ficheiros serão executados

a partir de um outro, apelidado de orchestrator (do inglês orquestrador), que terá a função de,

precisamente, orquestrar a execução de todos os scripts do Kettle.

4.2.2.1 Carregamento das tabelas Hub

Neste tipo de ficheiro apenas são criados os blocos necessários à leitura de dados provenientes

da área de staging, verificar se a chave ainda não foi inserida na hub e, se ainda não tiver sido

inserida, a criação de um novo tuplo com a informação deste novo elemento da tabela hub. Neste

script é adicionado também um bloco que irá guardar todos os tuplos que tenham dado origem

a erros, numa tabela de controlo. As tabelas de controlo são guardadas num schema chamado

control e terão os mesmos campos da tabela que lhe diz respeito. Isto é, por cada tabela hub,

é criada uma tabela hub_error no schema control. Nestas tabelas de erro são ainda adicionados

três colunas contendo informação do erro. Com o uso destas tabelas de erro, é possível verificar

se algum tuplo não foi corretamente inserido no DW e ainda perceber qual o erro que originou,

dando a oportunidade para a correção do erro e inserção da informação na ronda ETL seguinte.

Este tipo de tabelas, como é referido na Secção 2.3.2.5, apenas guardam uma business key

que identifica exclusivamente a informação do lado do sistema fonte. Em alguns casos, esta key

poderá ser mesmo uma chave primária no sistema fonte e, portanto, um identificador numérico,

ou um qualquer outro campo não numérico. A única caraterística obrigatória para esta business

key é identificar única e exclusivamente o tuplo em questão, do lado do sistema fonte.

Para o carregamento deste tipo de tabelas é necessário, então, consultar todas as chaves pro-

venientes do sistema fonte que estão presentes na área de staging. Como sabemos que na área de

staging apenas estão as alterações desde a última ronda de ETL, basta inserir todas as chaves na

tabela hub. A cada business key carregada para o DW é-lhe associada uma chave primária (inteiro

auto-incremental), um load_timestamp que regista a data e hora que a business key foi carregada e

um identificador do sistema fonte que deu origem à informação.

Figura 4.2: Exemplo de um script de carregamento de uma tabela hub


Na Figura 4.2 é apresentado um exemplo do script necessário ao carregamento de um hub.

Neste caso é apresentado o exemplo referente à tabela hub_orderdrop. Como é possível ob-

servar, temos um primeiro bloco responsável pela extração da informação da área de stagging

(get_orderdrop). Aqui são retirados todos os identificadores de orderdrop guardados na área de

staging, excluindo aqueles identificadores que tenham já sido carregados previamente. Em para-

lelo são também carregados todos os tuplos provenientes da tabela de erro referente à hub_orderdrop

(hub_orderdrop_error). Posteriormente, são carregados todos os identificadores lidos da área de

staging para a tabela hub_orderdrop, através do bloco hub_orderdrop. O bloco error recebe to-

das as linhas que originaram erros no bloco anterior e procede ao seu carregamento na tabela

hub_orderdrop_error. Por fim, temos o bloco Audit Step que regista, se bem sucedido, o início e

fim do processo ETL, bem como que tabela foi afetada (neste caso hub_orderdrop). A função do

bloco Blocking Success é a de bloquear a execução do script até todas as linhas serem carregadas

para o DW. Caso contrário, e como o Kettle executa todos os blocos em paralelo, teríamos uma

escrita na tabela audit_execution errada.

De forma a ser possível o armazenamento de informação do carregamento de todas as tabelas

hub foi configurado cada script Kettle de forma a guardar os seus ficheiros log numa tabela da

base de dados, hub_logs. Esta é uma tabela pertencente ao schema audit. Assim, se ocorrer

algum problema durante a execução de cada um dos scripts de carregamento das tabelas hub, essa

informação estará guardada na tabela hub_logs, permitindo assim a sua deteção e correção. Estes

erros serão guardados na tabela de erro correspondente ao hub em questão. Para configurar este

armazenamento de logs é necessária a configuração presente na Figura 4.3.

Figura 4.3: Configuração do Kettle para armazenamento de logs de uma tabela hub

32 Implementação

4.2.2.2 Carregamento das tabelas Link

Para este carregamento é usada a mesma lógica que é usada para as tabelas hub. Assim, em

cada ronda ETL é executado um ficheiro por tabela link que será responsável por verificar o que

alterou no sistema fonte através da área de staging.

Após consultar que ligações são necessárias criar desde a ultima ronda de ETL, o script faz

uma pesquisa ao DW pelas chaves dos hub presentes na ligação, através da business key que foi

carregada para a área de staging. Após encontrar as chaves primárias dos hub necessárias, cria um

novo tuplo na tabela link correspondente, atribui-lhe um identificador numérico auto-incremental

e um load_timestamp. Também aqui é necessário dar a informação de qual o sistema fonte que

originou a informação.

Seguindo a mesmo princípio do carregamento das tabelas hub, também para cada tabela link

é criada a respetiva tabela link_error no schema control. Estas tabelas têm, também, as mesmas

colunas da tabela link correspondente, bem como a informação do erro que originou a impossibi-

lidade da sua correta inserção no DW. Da mesma forma que as hub_error, nas tabelas de erro dos

link também não há restrições de chaves primárias e/ou estrangeiras.

Figura 4.4: Exemplo de um script de carregamento de uma tabela link

Como é possível analisar na Figura 4.4, temos um primeiro bloco (get_orderdrop_info) res-

ponsável pela leitura de todas as novas business keys a inserir na ronda ETL correspondente. Os

blocos seguintes (get_hub_saleorder_id e get_hub_orderdrop_id) apenas fazem uma pesquisa ao

DV em busca das chaves primárias das tabelas hub saleorder e hub orderdrop, respetivamente,

para depois criar um link com esta informação. Ter em atenção que nestes dois blocos foram ati-

vadas as opções “Habilita Cache” e “Load all data from table”. Desta forma o Kettle, aquando

da primeira pesquisa, carrega toda a tabela da base de dados onde terá de pesquisar. Assim são

evitados tempos de ligação e comunicação por cada tuplo de entrada.

Por fim, o bloco get_last_round_erros é responsável por extrair toda a informação presente na

tabela de erro associada a este link. Neste caso, a tabela link_orderdrop_error possui informação

sobre todos os tuplos que não foram previamente inseridos na tabela link_orderdrop. Assim, em

cada ronda ETL estes tuplos são carregados novamente e inseridos na tabela link_orderdrop. Um

dos problemas que pode levar ao aparecimento de um erro é carregar a tabela link_orderdrop sem

primeiro carregar a tabela hub_orderdrop. Neste caso não vai ser encontrada a chave primária

correspondente à business key, uma vez que esta ainda não foi carregada. Na ronda ETL seguinte,


se o problema estiver corrigido, a business key já terá sido carregada e portanto o link poderá ser

criado.

Por forma a serem guardadas todas as informações do carregamento de informação de cada

uma das tabelas link são também configurados todos os script das tabelas link da mesma forma que

as tabelas hub. Aqui a única diferença é a tabela de destino da informação, link_logs. Na Figura

4.5 está representada a configuração de um script de carregamento da tabela link como exemplo.

Figura 4.5: Configuração do Kettle para armazenamento de logs de uma tabela link

4.2.2.3 Carregamento das tabelas Satellite

Também para este tipo de tabelas é seguida a mesma arquitetura das tabelas anteriores. Assim,

é criado um script diferente para cada tabela. Em cada um destes scripts é feita inicialmente

uma pesquisa à área de staging por alterações que tenham ocorrido no sistema fonte. Como estas

tabelas guardam o rastreamento histórico do contexto das hub e link a que dizem respeito, no seu

carregamento é necessário verificar se se trata de uma atualização ou criação de um novo contexto.

Seguindo a mesma lógica das anteriores, também aqui são criadas tabelas de erro.

Na Figura 4.6 está apresentado o script usado no carregamento do sat_orderdrop. Neste caso,

são carregadas todas as linhas da view pre_orderdrop, que possui todas as atualizações de infor-

mação desde a última ronda de ETL. Como se trata de um satellite associado a um hub, temos

um bloco responsável pela pesquisa do hub_orderdrop_id. Este bloco é responsável por procurar

o identificador da tabela hub para cada linha carregada da área de staging. Se se tratasse de um

satellite associado a um link, teria de fazer uma pesquisa pelo identificador do link correspondente

a cada linha de entrada. Também aqui temos um bloco get_last_round_errors responsável pelo

levantamento de todas as linhas que deram origem a erros na ronda ETL anterior.

34 Implementação

Figura 4.6: Exemplo de um script de carregamento de uma tabela satellite

Como acontece com as tabelas hub e link, também no caso das tabelas satellite é configurado

o armazenamento da informação de logs produzida pelo Kettle. Na Figura 4.7 está representada a

configuração para o caso de uma tabela satellite.

Figura 4.7: Configuração do Kettle para armazenamento de logs de uma tabela sat

4.2.2.4 Automatização do Processo ETL

Através desta organização, é possível a execução em paralelo do carregamento de tabelas hub,

link e satellite. Assim garantimos que não estamos a carregar um satellite sem primeiro carregar o

hub que lhe diz respeito, originando erros de verificação de chaves estrangeiras. Para a execução

é usado então um orquestrador que executa todos os scripts das tabelas hub de uma só vez, um

outro orquestrador para as tabelas link e por fim um outro para a execução das tabelas satellite.

Desta forma é possível a execução em separado do carregamento de tabelas hub, link e satellite.

Para fazer o papel de orquestrador, foram usados jobs do Kettle. Um job em Kettle é um script de


Figura 4.8: Print screen do orquestrador que executa o processo ETL referente a todas as tabelashub

mais alto nível que uma transformação e permite o envio de emails, a execução de transformações,

entre outras funcionalidades.

Neste caso da Figura 4.8, todas as transformações são executadas em paralelo, uma vez que o

carregamento dos hub é independente entre tabelas.

No caso do carregamento das tabelas link, como é possível verificar na Figura 4.9, este também

é realizada em paralelo, uma vez que não há qualquer dependência entre links.

Na Figura 4.10 temos parte do orquestrador usado no carregamento de todos os satellites. Não

foi possível colocar o script na íntegra, uma vez que não é possível visualizá-lo de uma só vez no

Kettle. Ao contrário dos outros orquestradores, a execução de cada transformação correspondente

a um satellite é feito em série. Isto deve-se ao facto de ser um elevado número de tabelas satellite

e por cada satellite é criada uma transformação. Ora cada transformação necessita de fazer uma

ligação à base de dados e, por ser um número elevado de ligações em simultâneo (no caso de serem

executadas em paralelo), a base de dados rejeita algumas delas, o que leva à falha no carregamento

de alguns satellites. A solução encontrada foi a de todas as transformações serem executadas em

série, embora se perca a nível de performance.

Na Figura 4.11 está representado o job de Kettle criado para despoletar a execução completa de

uma ronda ETL. Como se pode verificar na figura, cada ronda ETL é iniciada com o refrescamento

das vistas materializadas da área de staging. Isto é feito através do bloco Execute SQL script que

executa um script SQL criado apenas para fazer refresh a todas as tabelas da área de staging. Esta

atualização de informação presente nas tabelas da área dee staging é essencial, uma vez que é

nesta fase que são carregados todos os dados que ainda não forma carregados para o DW. Uma vez

36 Implementação

Figura 4.9: Print screen do orquestrador que executa o processo ETL referente a todas as tabelaslink

Figura 4.10: Print screen do orquestrador que executa o processo ETL referente a todas as tabelassatellite


Figura 4.11: Print screen do orquestrador que executa o processo ETL

concluída a atualização da área de staging é iniciado o carregamento das tabeals hub. Esta ação é

levada a cabo pelo bloco hub_load. Esta etapa apenas executa o job representado na Figura 4.8.

De seguida temos os blocos link_load e sat_load que executam os jobs representados na Figura

4.9 e na Figura 4.10, respetivamente.

Os blocos pre_views_error, hub_load_error, link_load_error e sat_load_error são blocos do

Kettle que tem como função o envio de email. Neste caso, aquando da execução de cada um

dos blocos (Refresh_pre_views, hub_load, link_load ou sat_load), for detetado algum erro de

execução, é enviado um email para o administrador do sistema, para este ser notificado que ocorreu

algum problema na execução de uma ronda ETL. No início de cada ronda é registado o valor

do relógio do sistema (set_start_time) e no final de cada ronda é registado o tempo do sistema

(audit_insert). Tanto o bloco set_start_time como o bloco audit_insert são blocos que executam

uma transformation do Kettle. Assim, o primeiro diz respeito a uma transformação que apenas

retira o valor do relógio do sistema e a guarda como uma variável global. No final de cada ronda

a transformação audit_insert recupera o tempo de início da ronda ETL e regista esta informação,

juntamente com o tempo de final da ronda, numa tabela de auditoria, etl_rounds, que regista todos

os tempos de execução de todas as rondas ETL, para futuras análises de performance.

Para tornar todo este processo automático, foi ainda criado um bash script3 que tem como

responsabilidade a execução da Figura 4.11, ou seja, é responsável pela execução de uma ronda

ETL. Este bash script executa o master_orchestrator em ciclo, isto é, quando uma ronda ETL

termina, outra é iniciada de seguida. Assim, os dados guardados no DW têm um mínimo de

latência entre o sistema fonte de informação.

3script escrito em formato plaintext que é responsável pela execução de comandos a serem executados na linha decomandos

38 Implementação

4.2.3 Data Marts com Modelo Star

Como foi explicado anteriormente, foi escolhido o modelo híbrido, conjugando o modelo DV

com o modelo star. Com esta arquitetura, temos um DW modelado com DV e, para se efetu-

arem ações de consulta, diversos DM funcionando como vistas do DW. Estas vistas são criadas

recorrendo ao modelo star.

Uma vez tendo o DW criado com todas as tabelas e o processo ETL configurado, a próxima

fase do projeto é a criação de vistas no DW de forma a construir todos os DM recorrendo ao

modelo star.

Já abordado na Secção 2.3.2.1, o modelo star é composto por tabelas de facto (fact tables) e

tabelas de dimensão (dimension tables). As tabelas de dimensão representam todas as entidades

do sistema fonte e as tabelas de facto as métricas associadas a dimensões, ou relação entre estas.

Desta forma, no caso de uma orderdrop, esta representa uma dimensão. Assim, há a necessidade

da criação de uma tabela de dimensão associada a orderdrop, contendo todas as caraterísticas não

métricas de determinada orderdrop. Como uma orderdrop representa uma saída de produtos da

HUUB, está associada a uma saleorder (ordem de venda, que pode ser “partida” em várias or-

derdrop’s) e a todos os produtos que a compõe. Esta associação entre uma orderdrop, saleorder

e produtos é concretizada através de uma tabela de factos. Nesta tabela (v_fact_orderdrop, ver

Anexo A.4) está presente o identificador da orderdrop no DW (hub_orderdrop_id), o identificador

da saleorder associada (hub_saleorder_id) e o produto (hub_product_id). De forma a relacionar

toda esta informação é necessário associar a tabela hub_orderdrop e sat_orderdrop, para a obter

toda a informação da orderdrop. Para obter qual a saleorder associada à orderdrop em questão, é

necessário relacionar a informação obtida anteriormente com a tabela link_orderdrop, que contém

o identificador da saleorder relacionada com a orderdrop. Por fim, para se obter qual/quais os pro-

dutos que estão associados a determinada orderdrop, é necessário relacionar o hub_orderdrop_id

da orderdrop em questão, com a tabela sat_orderdrop_items. Nesta última tabela estão guardados

todos os detalhes sobre determinado produto que faz parte da orderdrop. São estes valores (quan-

tidade, estado) que compõe as métricas associadas a cada tuplo da tabela v_fact_orderdrop. Desta

forma estão relacionadas na mesma tabela a orderdrop, a saleorder e o produto, caraterizados por

todas as métricas associadas.

Para a construção da tabela de dimensão da orderdrop (ver Anexo A.5) apenas é necessária

a relação entre a tabela hub_orderdrop e a tabela sat_orderdrop. A vista dim_orderdrop apenas

necessita de possuir informação referente a uma orderdrop, tal como: o email de quem a criou

(created_by), qual a sua localização (location) ou qual o seu número de ordem (order_num).

São estas vistas que serão consumidas pelo software de visualização de dados (Power BI).

Desta forma, fica garantido que o DV nunca é consultado diretamente por ferramentas externas.

Apenas é consultado através de vistas, que dão informação resultante da query que a compõe.

Desta forma, todos os DM criados serão virtuais, uma vez que apenas são o resultado de uma

query executada sobre o DW que está modelado com o modelo DV. Esta solução permite uma

grande flexibilidade, já que havendo a necessidade de alguma alteração em alguma tabela de fac-

4.3 Real Time Schema – OpsTV 39

tos ou dimensão (alteração de granularidade ou que campos apresentar numa dimensão) apenas é

necessário reescrever a query que resulta na vista a alterar. Tendo os dados corretamente arma-

zenados no DW com o modelo DV, não há a necessidade de nenhum reprocessamento de dados,

apenas atualização de uma vista.

4.2.4 Visualização de Dados

Um dos extremos do sistema é o Microsoft Power BI. É esta camada que funciona como

interface com o utilizador. Assim é nesta camada que são produzidos todos os reports a partir da

informação guardada no DW.

O que é feito na construção de um report é uma ligação ao DW através do conetor para Post-

greSQL do Power BI. Uma vez efetuada a ligação, é necessária a escolha de qual/quais tabelas

se quer importar para determinado report. No caso de querermos analisar informação acerca

de ordens de venda (saleorder) é necessário carregar as tabelas dim_saleorder, dim_product,

dim_huubclient, dim_customer e dim_date que é uma tabela com toda a informação sobre a data,

por forma a permitir ao Power BI ações de roll up e drill down na data. Por fim, é necessário a

importação da tabela v_fact_saleorder que possui todas as métricas associadas a determinada sale-

order. Todas as relações entre tabelas são reconhecidas pelo Power BI. Caso contrário, é bastante

simples a definição de relações entre tabelas, sendo apenas necessário indicar quais os campos que

se relacionam. De notar que todos os dados são carregados em memória e, portanto, havendo uma

alteração no lado do DW, com criação de novos tuplos, estas alterações não terão efeito no Power

BI até que a informação seja atualizada. No entanto, como exposto na Secção 3.2.2.3 é possível

a configuração de uma atualização automática de dados a cada hora. Desta forma é garantido que

os reports estão, no máximo, 1 hora desatualizados.

Tendo os dados do lado do Power BI é bastante simples a construção de reports com o tipo

de gráfico mais adequado à visualização dos dados (gráfico de barras, gráfico de colunas, circular,

mapa entre outros). Em todos os reports realizados há também a possibilidade da aplicação de

filtros dinâmicos. No caso do report das saleorders, há a função de filtro por nome de HUUB

Client ou por canal de venda (Wholesale ou Webshop).

4.3 Real Time Schema – OpsTV

Dentro do mesmo problema, a criação de um sistema de apoio à decisão, surgiu a necessidade

da monitorização da quantidade de picks4 realizados ao longo do dia. Para tal é necessária a criação

de um dashboard que contenha informação relevante a picks de receção e picks para ordens de

venda.

Nesta secção será abordada uma solução pensada para responder a esta necessidade. Para a

realização deste dashboard, nestas condições, o Power BI não é o software indicado, uma vez que

4Processo interno ao armazém da HUUB

40 Implementação

apenas permite atualizações de dados de hora a hora. Esta janela temporal de atualização não é

suficientemente pequena (período bastante alargado) para visualização de dados em tempo real.

O que foi feito, então, para contornar esta limitação foi o uso da aplicação web Metabase que

permite a fácil e rápida configuração de dashboards. Esta aplicação permite a recolha de dados

através de diversas fontes, entre elas a base de dados PostgreSQL. Uma vez configurada a conexão

com a base de dados, é possível a construção de várias perguntas (Questions) à mesma. Tendo

as perguntas feitas, é possível apresentar os seus resultados de diversas formas: gráfico, tabela,

cartão (quando o resultado é apenas um valor único, ou então a soma/média/máximo/mínimo de

determinado conjunto de valores). Tendo a visualização pretendida de dados, basta associar estas

mesmas visualizações num dashboard.

Neste caso em concreto, OpsTV, pretende-se a visualização da evolução de três tarefas do

armazém: ordens preparadas de Wholesale, ordens preparadas de Webshop e receções de produ-

tos. Para responder a todas estas perguntas, que serão necessárias no Metabase para a constru-

ção destes gráficos, foi construído o schema real time que apenas é composto por vistas. Assim

cada vista procura responder a uma pergunta do Metabase. Foram então construídas as vistas:

rt_real_orders_done, rt_real_receptions_picked. Ambas as vistas são contruídas a partir de uma

query realizada remotamente à base de dados transacional. Para conseguir executar remotamente

as queries é necessário recorrer novamente à funcionalidade do PostgreSQL dblinks.

No caso concreto da vista rt_real_orders_done, esta guarda o número de ordens fulfilled (com-

pletas a 100% com produtos que tinham sido previamente encomendados) realizadas até ao mo-

mento. Assim, nesta vista teremos a informação de qual o HUUB Client em questão, o número

de ordens completas e qual o canal de venda a que se refere. Esta informação, assim como a in-

formação necessária à construção da vista rt_real_receptions_picked, é obtida através da consulta

de uma tabela de logs do sistema fonte, que regista todas as alterações, inserções e eliminações na

base de dados transacional. Assim, e sabendo que campos em que tabelas são alterados aquando

de um pick de uma peça para uma orderdrop, é possível saber quando uma ordem está completa

para sair. No caso de receções é consultada a mesma tabela para se obter o número de picks de re-

ceção realizados durante o dia. De notar que 1 pick representa a receção de 1 peça de determinado

modelo. Consultar Anexo A.6.

Tendo estas vistas montadas desta forma, para se obter os gráficos desejados do lado do Meta-

base temos de consultar a informação presente nas vistas. Apenas é necessário aplicar um filtro no

caso das ordens de saída, uma vez que na vista rt_real_orders_done temos informação referente a

Webshop e Wholesale.

Toda esta informação é volátil, com validade de um dia, uma vez que estas vistas apenas

apresentam informação referente ao dia em questão. Por esta razão foi criado este schema real

time separado virtualmente do DW. No entanto, toda esta informação é guardada no DW de forma

a permitir o rastreamento histórico de toda a informação referente a ordens realizadas e picks

de receção por dia, marca e canal de venda. Assim, é possível a construção de reports com a

informação produzida todos os dias por este schema.

4.4 Resumo e Conclusões 41


Neste capítulo foi descrita a implementação do sistema de apoio à decisão desenvolvido. Numa

primeira fase foi descrita a modelação de dados do DW, descrevendo a criação de tabelas hub,

link e satellite. Foram também apresentadas as tabelas de erro, que possuem toda a informação

referente a erros de inserção em cada ronda ETL.

De seguida foi apresentada a template do sistema. Foi descrita a área de staging e como a

sua utilização melhora a performance do processo ETL, uma vez que faz um pré-carregamento de

dados para o lado do DW no início de cada ronda ETL. O carregamento de cada um dos tipos de

tabelas (hubs, links, satellites) é o mais homogéneo possível para a possuir a mesma lógica entre

tabelas. Por forma a retirar informação sobre cada ronda ETL, cada um dos scripts foi configurado

para guardar informação nas tabelas de logs (hub_logs, link_logs, sat_logs). Para controlar toda a

ronda ETL foram usados jobs do Kettle que funcionam como orquestradores de toda a ronda ETL.

Assim, é possível o controlo da execução e sequência de todas as rondas ETL.

Em cima do modelo DV foram configuradas vistas para representar todos os DM com o modelo

star. Desta forma todos os DM são virtuais, permitindo alterações na implementação de forma

simples.

Por último, é descrito o schema real time configurado ao lado do DV. Este schema foi cons-

truído para dar resposta à necessidade da monitorização em tempo real do trabalho realizado no

armazém ao longo do dia.

42 Implementação

Capítulo 5

Modelo de Visualização

Neste capítulo são apresentados os reports e dashboards elaborados para representar os KPI.

Na Secção 3.3 foram definidos os KPI e métricas a serem consideradas na realização do projeto.

Por último é feita uma análise ao desempenho do processo ETL.

Todos os relatórios aqui expostos têm a sua informação desfocada ou em falta, uma vez que o

sistema de apoio à decisão foi desenvolvido recorrendo a dados reais e, portanto, não podem ser

mostrados.

5.1 Operações

Uma das categorias são as operações internas da empresa. Este DM tem como objetivo con-

ter todas as tabelas de facto e dimensão necessárias ao cálculo de todas as métricas ou KPI do

departamento de operações.

Nesta secção serão apresentados todos os reports que contenham KPI ou métricas relacionados

com a operação interna da HUUB, definidos na Secção 3.3.1.

so_fulfillement

Como explicado na Secção 3.3.1 um dos KPI a ser analisado é a taxa de preenchimento de to-

das as ordens de venda. Para concretizar este KPI foi criado um report chamado so_fulfillement_tax.

Para alimentar este report foi criada uma tabela de factos v_fact_so_fulfillement_tax. Nesta tabela

estão presentes os identificadores de ordem de venda (hub_saleorder_id), do produto (hub_product_id),

do cliente (hub_huubclient_id) e do customer (hub_customer_id). Cada um destes identificadores

representam um tuplo presente na correspondente tabela de dimensão. Nesta tabela de factos está

ainda presente qual o canal de venda associado a cada ordem de venda (sales_channel) e qual a

season associada (season_name). Assim sendo, nesta tabela de factos estão presentes 4 dimen-

sões: a dimensão da ordem de venda (dim_saleorder), a dimensão do produto (dim_product), a

dimensão do cliente da HUUB (dim_huubclient) e a dimensão do customer (dim_customer). Fo-

ram criadas todas estas 4 dimensões contendo todas as caraterísticas associadas a cada uma delas.

No caso da dimensão da ordem de venda temos caraterísticas como o número interno da ordem

43

44 Modelo de Visualização

de venda (chave externa que aponta para o tuplo na base de dados operacional). Na dimensão

do produto temos todas as caraterísticas do produto, como por exemplo, o nome, a descrição, o

género (masculino ou feminino), qual o grupo (adulto, criança) e preços genéricos do produto.

Para a dimensão do cliente da HUUB são carregadas todas as informações como o nome, email e

telefone. Por fim, para a dimensão do customer são carregadas todas as informações referentes ao

nome, cidade e país.

Tendo a tabela de factos e respetivas dimensões criadas, basta fazer o seu carregamento para o

Power BI e concretizar o report.

Figura 5.1: Print screen do report da taxa de preenchimento de ordens de venda

Como é possível verificar na Figura 5.1 temos um gráfico que nos mostra a taxa de preenchi-

mento das ordens de venda, agrupada ao canal de venda. Temos ainda o número total de items

enviados e o número total de items estimados para envio. Por fim, temos um gráfico semicircular

que nos mostra a taxa de preenchimento global das ordens de venda. Do lado esquerdo do report

temos a área de filtragem de informação. Nesta secção o utilizador pode filtrar a informação pelo

cliente da HUUB, pelo canal de venda (Sale Channel), pelo número interno da ordem de venda

(Internal Saleorder Num), pela season, por país e ainda pelo nome do customer. Todos estes filtros

foram introduzidos de forma a produzir um report o mais flexível e dinâmico possível.

po_fulfillement

Do mesmo modo que foi construído o report anterior, foi feita uma análise semelhante, mas

desta vez ao nível das ordens de compra.

Para a concretização deste report foi necessária a criação de uma tabela de factos v_fact_po_fulfillement_tax

que, do mesmo modo da tabela de factos anterior, possui os identificadores para as dimensões de

5.1 Operações 45

ordem de compra, de cliente da HUUB e de fornecedor. Neste caso não faz sentido o canal de

venda, uma vez que estamos a falar de uma ordem de compra e, portanto, não tem nenhum canal

de venda associado. Uma vez criadas as dimensões e a tabela de factos, foram inseridas no Power

BI.

Figura 5.2: Print screen do report da taxa de preenchimento de ordens de compra

Na Figura 5.2 está presente o relatório reference à taxa de preenchimento de ordens de compra.

Do mesmo modo que o relatório anterior, temos um gráfico que mostra a taxa de preenchimento

por fornecedor. Neste caso apenas são apresentados os 5 fornecedores com maior número de

items encomendados. Na parte superior deste gráfico temos o número total de items recebidos e o

número total de items encomendados. Neste report também está presente um gráfico semicircular

que apresenta a taxa de preenchimento global. Do mesmo modo que são aplicados filtros ao report

da Figura 5.1, também neste report é possível filtrar a informação por cliente da HUUB (HUUB

Client Name), por fornecedor (Supplier Name) e ainda por número interno da ordem de compra

(Purchaseorder Internal Num).

picks

Como foi definido na Secção 3.3 há a necessidade de um report que mostre a evolução ao

longo do tempo de todos os picks realizados na HUUB, quer sejam picks para ordens de compra

ou picks de receções. Para isso, foi necessária a criação de duas tabelas de facto distintas. Um

delas com a informação referente a picks para ordens de venda e uma outra referente a picks de

receções. No caso de picks para ordens de venda é necessário ainda a distinção entre ordens de

Webshop e ordens de Wholesale. Esta distinção é feita através de um campo sale_channel_code

que nos diz se a ordem é referente a webshop ou wholesale. De notar que esta análise é feita ao

nível de uma orderdrop, que é uma divisão da ordem de venda. Isto é, uma ordem de venda pode


ser partida em várias orderdrops. Assim, como todas as orderdrops têm associada uma ordem de

venda, basta cruzar a informação referente a orderdrop e ordens de venda e é obtido o canal de

venda. Para obter informação referente a picks de receções, foi criada uma tabela de factos que

possui informação referente à receção, ao cliente da HUUB, a que purchaseorder diz respeito (da

mesma forma que uma ordem de venda pode ser partida em várias orderdrops, cada ordem de

compra (purchaseorder) pode ser partida em várias receções).

Figura 5.3: Print screen do report do número de peças e ordens preparadas por dia

Na Figura 5.3 é apresentado o report elaborado para dar resposta a este KPI. Aqui é possível

verificar o número de ordens de compra e venda, bem como a quantidade de items processados.

Neste report está presente a divisão entre ordens de Wholesale e ordens de Webshop. No topo

do report são visíveis dois filtros: um filtro da data e um outro para filtrar ordens por cliente da

HUUB. Em qualquer um dos três gráficos é possível visualizar o número de ordens de compra ou

venda processadas e completas.

number_packs_dispatched

Para representação desta métrica foi utilizada a mesma tabela de factos descrita na Secção 5.2,

uma vez que, cada linha desta tabela de facto (v_fact_shipment_pack) representa um pack inserido

num shipment. Para a contabilização do número de packs, é inserido, em cada linha desta tabela

um campo com o valor 1. Para saber o número total de packs expedidos, apenas é necessário somar

todos os “1’s” presentes nesta tabela. Nesta tabela de facto estão presentes todas os identificadores

das dimensões de ordem de venda (com a hierarquia explicada também na Secção 5.2), shipment,

cliente da HUUB, transportador, canal de venda, pack e cliente da marca.

5.1 Operações 47

Apesar da tabela de facto que alimenta este report e o report do shipping cost ser a mesma,

neste caso é dada visibilidade sobre campos distintos. Assim, está presente na Figura 5.4 o report

desenvolvido em Excel, alimentado por dados provenientes da tabela de facto v_fact_shipment_pack.

Figura 5.4: Report desenvolvido com informação acerca de packs

Neste report temos presente o identificador da ordem de venda em questão, o número de ordem

da drop, a data em que a mercadoria saiu do armazém (Shipment Date), qual o cliente da HUUB,

qual a transportadora e, por fim informação relativa ao pack. As duas últimas colunas representam

o número de items do pack e o número de packs.

number_items_pack

Esta métrica está representada na coluna de número de items por pack da Figura 5.4. Este valor

está também representado na mesma tabela de facto da métrica anterior (v_fact_shipment_pack),

em que para cada pack está presente a quantidade de items.

stock_location

De forma a representar esta métrica, foi relacionada uma posição de stock com um produto,

canal de venda, cliente da HUUB e armazém. Assim foi montada uma tabela de facto contendo

hub_location_id, hub_product_id, hub_saleschannel_id, hub_huubclient_id, hub_warehouse_id

e date_dim_id. Cada um destes identificadores apontam para a respetiva dimensão. Nesta tabela

de facto está também presente a métrica propriamente dita: a quantidade do produto numa dada

localização de stock.

Toda a informação necessária para esta tabela de factos e respetivas dimensões foi previamente

carregada para o DV. Neste caso, como apenas é necessária a quantidade de produtos ao final do

dia, a informação referente à quantidade de produtos em determinada localização apenas é atuali-

zada ao final do dia. Já a informação de cada uma das dimensões é carregada incrementalmente

ao longo do dia.

O identificador hub_location_id aponta para a dimensão localização e contém informação de

cada uma das localizações de stock do armazém. Aqui está presente o nome da localização, qual


o corredor, a prateleira e qual a estante a que pertence. A dimensão produto, representada pelo

hub_product_id, possui toda a informação referente ao produto, desde a sua descrição, tipo, qual

a season a que pertence e qual o seu EAN1. O hub_saleschannel_id identifica a dimensão sales

channel e contém informação referente ao canal de venda (Webshop ou Wholesale). A dimensão

do cliente da HUUB, representada pelo seu hub_huubclient_id, como explicado anteriormente,

possui informação do cliente da HUUB. A dimensão warehouse (hub_warehouse_id) contém in-

formação sobre o armazém onde o stock está alojado. Por último, temos o identificador da data

(date_dim_id), que aponta para a respetiva dimensão, onde está guardada informação detalhada

sobre a data, como por exemplo, o ano, mês, dia, dia da semana, trimestre.

Para visualizar toda esta informação, foram importadas para uma folha de cálculo (Excel) todas

estas tabelas virtuais (uma vez que são construídas com vistas em cima do modelo DV). Depois

de carregadas as tabelas, criadas as relações entre elas e mudados os nomes das próprias tabelas

e colunas (de modo a terem nomes mais direcionados ao negócio) foi montada a tabela dinâmica

presente na Figura 5.5. Todo este processo do primeiro carregamento de dados e alterações de

nomes de campos é manual. No entanto, toda a atualização de dados posterior é feita de forma

automática, basta para isso clicar no botão de atualização de dados.

Figura 5.5: Folha de cálculo criada para representar quantidade de um produto numa localizaçãode stock

5.2 Vendas

Este DM tem como objetivo conter todas as métricas e KPI relacionados com vendas.

items_sold

Para representar esta métrica, foi montada uma tabela de facto (v_fact_so_items) que relaci-

ona ordens de venda e produtos. Nesta tabela estão ainda presentes identificadores do cliente da

1Standard numérico usado globalmente para identificar um produto

5.2 Vendas 49

HUUB, do cliente final (customer), o canal de venda e qual o estado do produto na ordem de venda.

Quanto a métricas presentes nesta tabela, estão presentes os preços associados à ordem de venda e

ainda a quantidade de produtos. Para o cálculo desta métrica, é necessário filtrar informação para

apenas serem mostrados os items que não tenham sido cancelados.

Figura 5.6: Folha de cálculo criada para representar quantidade de um produto associado a umaordem de venda

Na Figura 5.6 está representado o report elaborado para representar esta métrica. Aqui é

possível observar o número interno da ordem de venda, o EAN dos produtos associados, qual o seu

estado, cliente da HUUB, canal de venda, país do cliente (customer country) e qual a quantidade

do produto associado à ordem de venda. Para obter o valor total, basta somar estas quantidades.

Neste report cada linha representa um produto dentro de uma ordem de venda. Para obter

a quantidade de produtos total numa dada ordem, basta agregar toda a informação da ordem de

venda e o Excel soma as quantidades de todos os produtos.

items_cancelled

Como a tabela de facto utilizada para o cálculo da métrica anterior possui o estado do item

dentro da ordem de venda, basta fazer um filtro no report anterior para serem mostrados apenas os

items que tenham sido cancelados e somar as suas quantidades.

Através do report apresentado na Figura 5.6, é possível o cálculo desta métrica. Para tal basta

filtrar a coluna do estado para apresentar apenas os items que tenham o seu estado “Cancelled”.

received_orders

Neste caso foi montada uma tabela de facto que relaciona uma ordem de venda com todas as

suas drops. Esta relação é criada para fazer uso da hierarquia ordem de venda - drop (ver Secção

5.2). Esta tabela de facto (v_fact_so_drop) contém o identificador da drop que aponta para a tabela

que possui a hierarquia, o identificador do estado da drop, o identificador do estado da ordem de

venda (estes dois identificadores apontam para tabelas diferentes, mas com conteúdo semelhante,


para permitir filtrar o estado da ordem de venda ou da drop em separado) e ainda identificadores

para as datas da drop e ordem de venda. Por último estão ainda presentes os preços associados à

ordem de venda.

Para obter o número de ordens recebidas, apenas é necessário fazer uma contagem distinta dos

números internos da ordem de venda.

Figura 5.7: Report criado com ordens de venda e respetivas drops

Através da folha de cálculo ilustrada na Figura 5.7 é possível obter o número de ordens de

venda. Para tal basta fazer uma contagem da primeria coluna que diz respeito ao número interno

da ordem de venda. Neste report é também possível perceber qual o número de drops canceladas

dentro de uma ordem de venda. Para tal basta filtrar informação para uma ordem de venda e

verificar qual/quais as drops que têm o seu estado “Cancelled”.

cancelled_orders

Para o cálculo desta métrica apenas é necessário filtrar as ordens de venda que tenham o seu

estado em “Cancelled”, presentes na mesma tabela de facto usada na métrica anterior. Tendo o

filtro aplicado basta fazer uma contagem distinta do campo Sale Order Internal Num.

Esta métrica é obtida na folha de cálculo da Figura 5.7, sendo necessário filtrar a informação

para visualizar apenas as ordens de venda que tenham o seu estado diferente de “Cancelled”.

shipping_cost

Para este KPI é pretendido o valor gasto e cobrado ao cliente para cada shipping. Como men-

cionado anteriormente, cada ordem de venda é partida em várias fases (drops) e cada uma destas

5.2 Vendas 51

fases tem associado um shipment, que representa a saída do armazém de determinada drop. Cada

um destes shipments tem associados um ou mais packs que representam as caixas que transpor-

tam a mercadoria. Cada shipment tem associado um preço cobrado ao cliente e um custo para a

HUUB, que é o preço do transporte dos diversos packs que compõe o shipment.

Para dar resposta a este KPI foi criada uma tabela de facto v_fact_shipment_pack que relaciona

ordens de venda, drops, shipment’s, packs, cliente da HUUB, cliente final e transportador (carrier).

Para representar este KPI foi configurada uma tabela dinâmica no Excel de forma a relacionar

todos estes valores.

Figura 5.8: Report representativo do shipping cost

Na Figura 5.8 está representado um exemplo deste report criado. Neste report é possível

verificar a existência de uma coluna Saleorder Internal Num que contém o número interno da

ordem de venda, a data do shipment (Shipment Date), o país de destino do shipment, o canal de

venda, a quantidade de items presentes no shipment e, por fim, as colunas com o preço cobrado ao

cliente pelo transporte e o custo para a HUUB.

Neste caso, para a dimensão da ordem de venda foi montada uma hierarquia entre canal de

venda, ordem de venda e drop. Esta hierarquia só é possível uma vez que a relação entre canal de

venda e ordem de venda é de 1:n, isto é, um canal de venda pode estar associado a várias ordens

de venda e, cada ordem de venda, apenas pode estar associada a um canal de venda. O mesmo

acontece entre ordem de venda e drop, em que cada ordem de venda pode estar associada a várias

drops enquanto que cada drop apenas pode estar associada a uma ordem de venda.

Na Figura 5.9 está representada a hierarquia usada na tabela de dimensão de uma ordem de

venda. As restantes dimensões (shipment, pack e carrier) contêm o contexto de cada uma destas

entidades.


Figura 5.9: Hierarquia da tabela de dimensão de ordem de venda

5.3 Compras

Nesta secção são apresentadas algumas das métricas do DM de compras.

number_received_po_orders

Dentro da mesma lógica pensada para as ordens de venda, para as ordens de compra foi tam-

bém configurada uma tabela de facto (v_fact_po_reception) que relaciona ordem de compra, rece-

ção, cliente da HUUB, data da ordem de compra, fornecedor e estado da ordem. Assim é possível

chegar a esta métrica: o número total de ordens recebidas. Com todas estas dimensões é ainda

possível filtrar a informação para se obter qual o cliente com maior número de ordens de venda

num determinado período de tempo, e qual o seu principal fornecedor.

Uma vez modeladas as tabelas de dimensão e tabela de facto, estes dados são importados para

o Excel para serem trabalhados de forma a obter informação referente a ordens de compra.

Na Figura 5.10 está representado o report realizado. É possível verificar a existência do nú-

mero interno da ordem de compra, o estado da ordem de compra, o número de ordem da receção,

o estado da receção, qual o cliente da HUUB associado à ordem de compra e o fornecedor (sup-

plier). Para obter o número de ordens de compra total, basta fazer uma contagem dos elementos

da primeira coluna. Também neste caso, é possível filtrar esta informação pelo estado da ordem,

pelo estado da receção ou pelo cliente da HUUB.

number_cancelled_po_orders

Esta métrica está presente na mesma tabela de facto da métrica anterior. Para tal, basta filtrar

quais as ordens que tenham o seu estado “Cancelled”. Este filtro é possível através da dimensão

de estado (dim_status).

5.3 Compras 53

Figura 5.10: Report configurado para dar resposta ao número de ordens de compra recebidas

Com a informação presente nesta tabela de facto é ainda possível obter um KPI como sendo a

percentagem de ordens canceladas para um dado cliente, num dado período de tempo.

Este KPI pode ser retirado do report da Figura 5.10, sendo apenas necessário filtrar a coluna

do estado da ordem, para apenas mostrar ordens que estejam “Cancelled”.

quantity_items_ordered_po

Esta métrica é obtida através de uma tabela de facto denominada v_fact_purchaseorder_items.

Aqui estão relacionadas as dimensões de ordem de compra, produto, cliente da HUUB, fornecedor,

data e estado. Quanto a métricas, está presente a quantidade de determinado produto dentro de uma

ordem de compra. Assim para calcular a quantidade total de items encomendados, basta somar

esta coluna. Com a presença de todas as dimensões é ainda possível aplicar filtros na informação,

de forma a obter informação como qual o período no qual determinada marca (cliente da HUUB)

encomendou um maior número de produtos, ou até mesmo a média de produtos por ordem de

compra.

Figura 5.11: Report representativo do número de items de cada ordem de compra


Na Figura 5.11 está representado o report produzido com a informação referente à quantidade

de produtos de cada ordem de compra. Assim como para ordens de venda, também neste caso é

possível agregar a informação referente a uma ordem de compra e é apresentada na última coluna

a soma da quantidade de todos os produtos da ordem de compra em questão.

Também para esta tabela de facto foi criada uma tabela de dimensão que relaciona todas as

ordens de compra com todas as receções. Da mesma forma que uma ordem de venda pode ser

dividida em várias drops, também uma ordem de compra pode ser dividia em várias receções.

Como existe uma relação de 1:n entre ordens de compra e receções, pode ser criada uma tabela de

dimensão contendo esta hierarquia. Na Figura 5.12 está representada a hierarquia entre ordem de

compra e receções. Com esta hierarquia é possível visualizar não só a quantidade total de produtos

de uma ordem de compra, mas também a quantidade de produtos de cada uma das receções.

Figura 5.12: Hierarquia entre ordem de compra e receção

quantity_items_cancelled_po

Para obter esta métrica é usada a mesma tabela de facto da métrica anterior (v_fact_purchaseorder_items).

No entanto é necessário aplicar um filtro ao nível do estado do produto na ordem de compra, fil-

trando apenas informação referente a produtos que tenham o seu estado “Cancelled”.

5.4 OpsTV

Como abordado na Secção 4.3 foi construído um dashboard de forma a permitir a avaliação

da evolução de um dia, a nível de drops e receções processadas. Uma vez configurado o schema

real time e o Metabase foi possível obter o dashboard pretendido.

Na Figura 5.13 está um print screen do dashboard para um dia. Como é possível verificar,

está presente um gráfico com as ordens processadas para Webshop (primeiro gráfico à esquerda),

as ordens processadas para Wholesale (gráfico do meio) e à direita o gráfico correspondente a

receções. No topo de cada um destes gráficos temos informação referente à quantidade de ordens

e quantidade de produtos processados (picks). No caso das receções apenas temos a quantidade

total de produtos rececionados (Number of Picks).

5.5 Análise Quantitativa 55

Figura 5.13: Dashboard criado para concretização da OpsTV

5.5 Análise Quantitativa

De forma a analisar o desempenho de todo este processo, são analisadas todas as tabelas de

logs de cada uma das tabelas hub, link e satellites. É também consultada uma tabela de log criada

para registar o início e término de cada uma das rondas ETL (tabela etl_rounds).

Em cada uma das tabelas de log de hubs, links ou satellites, está presente informação sobre

que tabela foi atualizada, a quantidade de linhas criadas, atualizadas ou rejeitadas. Com esta

informação é possível obter o número de linhas que foram inseridas ou rejeitadas aquando da

atualização de cada uma das tabelas do DW. Através da tabela de log de cada ronda ETL é possível

analisar tempos médios de execução de cada uma das rondas ETL.

Tendo esta informação guardada é possível a construção de uma dashboard que mostra a

evolução do desempenho de todos os processos ETL.

Na Figura 5.14 está apresentado o dahsboard criado para representar o desempenho de todas as

rondas ETL. Este dashboard foi criado, recorrendo ao Power BI. Neste dashboard está informação

referente à duração em segundos e minutos de cada ronda ETL. Também neste dashboard está

presente informação referente ao número médio de linhas inseridas em cada uma das rondas, bem

como o número médio de linhas rejeitadas.

Como cada ronda ETL tem uma latência mínima relativamente à ronda anterior, devido a ser

executada imediatamente a seguir ao término da ronda anterior, terá também um número de linhas

a serem processadas inferior, quando comparado, por exemplo, ao facto de cada ronda ETL ser

executada apenas uma vez por dia. Assim terá também uma duração inferior, uma vez que tem de

processar menos dados.

Na Figura 5.14 é possível observar os tempos médios de execução de cada ronda ETL. No topo

do dashboard está representada a duração média como sendo de 4,65 minutos, o que corresponde


Figura 5.14: Desempenho processo ETL

a 3 minutos e 55 segundos. O número médio de linhas a serem carregadas em cada ronda ETL é,

sensivelmente, de 649 linhas por ronda.


Neste capítulo foram expostos todos os reports desenvolvidos de forma a dar resposta aos KPI

e métricas definidas na Secção 3.3. Por último, é apresentado o dashboard desenvolvido para dar

resposta à Secção 4.3.

O DW é bastante flexível à visualização de dados. Ou seja, independentemente da ferramenta

usada para consumir a informação guardada, é possível obter gráficos ou tabelas de forma a perce-

cionar cada um dos KPI, e, por conseguinte, o desenvolvimento do trabalho realizado na HUUB.

Neste caso foram apresentados reports realizados em Power Bi ou Excel, no entanto, isto não quer

dizer que apenas estas duas ferramentas possam ser usadas para consumir informação do DW. Ha-

vendo a necessidade de mudar uma destas ferramentas, não implica nenhuma alteração do lado do

DW.

Para o dashboard criado para dar resposta à evolução do trabalho desenvolvido ao longo do

dia no armazém, a informação que alimenta este dashboard está num schema externo ao DV. Esta

decisão foi tomada uma vez que para este dashboard é necessário informação em tempo real e

não implica grande manipulação de dados do sistema fonte, apenas é necessário uma consulta da

tabela de logs da base de dados de origem.

Capítulo 6

Conclusões e Trabalho Futuro

Neste capítulo são apresentadas as conclusões da elaboração deste trabalho, bem como um

resumo de tudo o que foi feito e o trabalho futuro a ser desenvolvido para dar continuidade ao

projeto.

6.1 Satisfação de Resultados

No mundo empresarial, mais do que fazer, o importante é fazer bem e dentro dos prazos

estipulados. Dependendo da dimensão da empresa, manter o nível de serviço pode ser uma tarefa

mais ou menos trivial. No caso da HUUB manter um “Service Level Agreement” (SLA) é uma

tarefa tudo menos trivial, uma vez que existem diversas variáveis em jogo que podem colocar em

causa o nível de serviço. De forma a puder controlar este SLA é necessário perceber o que deve

ser monitorizado e controlado. Esta perceção leva a que KPI devem ser definidos. Estes KPI não

estão de forma direta guardados na base de dados transacional e, portanto, é necessário o desenho

de processos ETL de forma a extrair a informação da base de dados transacional, transformar esta

informação e, por fim, o seu carregamento num repositório central de dados (DW).

De forma a ajudar na tarefa de decisão é necessário um entendimento sobre o que se passa na

empresa, quer a nível operacional, quer a nível de negócio. Para isso foi desenvolvido o sistema

de apoio à decisão de forma a fornecer aos decisores tomadas de decisão apoiadas em informação

gerada durante o funcionamento da empresa. Esta informação poderia ser retirada diretamente da

base de dados transacional, mas levaria a várias limitações. Uma delas seria a impossibilidade da

integração de dados de diversas fontes. Outra limitação seria o facto de a base de dados transacio-

nal ser orientada a transações e não otimizada para consultas de grandes volumes de dados.

A monitorização de diversos KPI ou métricas relacionados com a operação ou o negócio da

HUUB é fundamental para melhorias no seu funcionamento. Para conseguir quantificar a sua

operação interna ou o negócio dos seus clientes, a HUUB teve necessidade de construir um sistema

de análise de dados capaz de agregar toda a informação relacionada com a empresa e seus clientes.

Assim, foi desenvolvido este trabalho, com o intuito de concretizar um sistema de apoio à decisão.

57

58 Conclusões e Trabalho Futuro

Foi desenvolvido um DW modelado com o modelo híbrido, isto é, modelo DV juntamente com

o modelo star. O modelo DV foi construído fisicamente, isto é, foram configuradas tabelas físicas

num schema do DW. Já o modelo star é virtual, ou seja, funciona apenas como vistas em cima do

modelo DV. Desta forma obtém-se flexibilidade, já que uma mudança numa das tabelas do modelo

star não implica reprocessamento histórico de dados, apenas a reescrita de uma query.

Por fim, foram desenvolvidos relatórios capazes de dar resposta aos KPI ou métricas definidos

no início dos trabalhos. Também aqui é visível uma vantagem do DW: um repositório central e

único de dados. Assim, é possível analisar dados com a ferramenta mais adequada, não havendo a

necessidade de alterações no DW se por algum motivo um relatório for desenvolvido numa outra

ferramenta que não Power BI ou Excel.

Durante o desenvolvimento deste trabalho foram encontradas algumas limitações relacionadas

com a escolha das tecnologias utilizadas.

Uma das limitações prende-se com o facto de serem usados db links para o povoamento da

área de staging. Como o módulo db link é exclusivo do PostgreSQL apenas é possível o seu uso

entre bases de dados com este motor de gestão. Se for escolhido outro motor de bases de dados

para o sistema transacional, no futuro, todas as queries que compõe a área de staging terão de ser

repensadas, já que o DW está alojado em PostgreSQL. Desta forma, a tecnologia de motor de base

de dados usado no DW terá de estar em concordância com o motor de base de dados do sistema

transacional, para se continuar a usar a metodologia de execução de queries remotamente.

6.2 Trabalho Futuro

Todo este projeto foi desenvolvido, mas não concluído, à data da conclusão deste documento.

O desenho de um DW requer bastante tempo, uma vez que é bastante dependente da área de

negócio onde está inserido. Esta dependência deve-se ao facto de, dependendo do negócio em

questão, existirem necessidades distintas de KPI ou métricas que devem ser analisadas.

Durante o desenvolvimento do projeto foram definidos alguns KPI considerados prioritários.

Assim chegou-se aos 3 DM (operações, vendas e compras). Mas este projeto está em constante

desenvolvimento e como trabalho futuro é proposto o desenvolvimento destes 3 DM com mais

KPI ou métricas que façam sentido dentro do negócio da HUUB e o desenvolvimento de mais

DM para dar resposta a todos os departamentos da empresa. Um dos departamentos que não foi

analisado e que pode ganhar valor com o uso do DW é o departamento de marketing.

Assim como trabalho futuro é proposta a continuidade do projeto. Para dar resposta aos KPI

definidos apenas foi necessário extrair informação proveniente de uma base de dados transacional,

mas um DW tem capacidade de receber dados provenientes de várias fontes de dados. Desta

forma, para dar continuidade ao projeto é necessário a integração com diversas outras fontes de

informação, por exemplo as redes sociais da empresa e das marcas e Google Analitycs, entre

outras. Esta integração com outras fontes de dados permite uma tomada de decisão fundamentada

em dados reais do funcionamento da empresa.

Anexo A

Anexos

A.1 Criação das tabelas do DV

A.1.1 Código usado na criação da tabela hub_orderdrop

1 CREATE TABLE hub_orderdrop(

2 hub_orderdrop_id SERIAL PRIMARY KEY,

3 orderdrop_id INTEGER,

4 load_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

5 rec_SRC INTEGER DEFAULT 1,

6 UNIQUE (orderdrop_id)

7 );

A.1.2 Código usado na criação da tabela link_orderdrop

1 CREATE TABLE link_orderdrop(

2 link_orderdrop_id SERIAL PRIMARY KEY,

3 hub_orderdrop_id INTEGER NOT NULL,

4 hub_saleorder_id INTEGER NOT NULL,

5 hub_location_id INTEGER NOT NULL,



8 CONSTRAINT hub_orderdrop FOREIGN KEY (hub_orderdrop_id) REFERENCES hub_orderdrop(

hub_orderdrop_id) ON DELETE CASCADE ON UPDATE CASCADE,

9 CONSTRAINT hub_saleorder_drop FOREIGN KEY (hub_saleorder_id) REFERENCES

hub_saleorder(hub_saleorder_id) ON DELETE CASCADE ON UPDATE CASCADE

10 );

A.1.3 Código usado na criação da tabela sat_orderdrop

1 CREATE TABLE sat_orderdrop(

2 hub_orderdrop_id SERIAL,



5 created_by VARCHAR(512),

6 order_date TIMESTAMP,

59

60 Anexos

7 created_at TIMESTAMP,

8 order_num VARCHAR(512),

9 location VARCHAR(512),

10 estimated_drop_date TIMESTAMP,

11 PRIMARY KEY (hub_orderdrop_id, load_date),

12 CONSTRAINT orderdrop_id FOREIGN KEY (hub_orderdrop_id) REFERENCES hub_orderdrop

(hub_orderdrop_id) ON DELETE CASCADE ON UPDATE CASCADE);

A.2 Criação das tabelas do schema control DV

A.2.1 Código usado na criação da tabela hub_orderdrop_error

1 CREATE TABLE hub_orderdrop_error(




5 error_desc text,

6 error_field VARCHAR(767),

7 error_code VARCHAR(767));

A.2.2 Código usado na criação da tabela link_orderdrop_error

1 CREATE TABLE link_orderdrop_error(

2 hub_orderdrop_id INTEGER,

3 hub_saleorder_id INTEGER,



6 error_desc text,


8 error_code VARCHAR(767),

9 orderdrop_original_id INTEGER,

10 saleorder_num VARCHAR(512));

A.2.3 Código usado na criação da tabela sat_orderdrop_error

1 CREATE TABLE sat_orderdrop_error(

2 hub_orderdrop_id INTEGER,



5 created_by VARCHAR(512),

6 order_date TIMESTAMP,

7 created_at TIMESTAMP,

8 order_num VARCHAR(512),

9 location VARCHAR(512),

10 error_desc text,


12 error_code VARCHAR(767),


14 saleorder_num VARCHAR(512),

15 status_type VARCHAR(512),

A.3 Vista da área de staging 61

16 status_name VARCHAR(512),

17 estimated_drop_date TIMESTAMP);

A.3 Vista da área de staging

1 CREATE MATERIALIZED VIEW staging.pre_orderdrop AS

2 SELECT link.id AS orderdrop_original_id,

3 link.orderdrop_date,

4 link.created_at,

5 link.orderdrop_num,

6 link.email,

7 link.location_name,

8 link.saleorder_num,

9 link.status_type,

10 link.status_name

11 FROM public.dblink(’host=*********** port=***** user=***** password=*********

dbname=********’::text,

12 ’SELECT orderdrop.id,

13 orderdrop.orderdrop_date,

14 orderdrop.created_at,

15 orderdrop.orderdrop_num,

16 users.email,

17 location.name,

18 saleorder.internal_order_num,

19 status.status_type,

20 status.name

21 FROM app_orderdrop AS orderdrop

22 JOIN auth_user AS users ON orderdrop.created_by = users.id

23 JOIN app_locations AS location ON orderdrop.locations_id = location.id

24 JOIN app_salesorder AS saleorder ON orderdrop.salesorder_id = saleorder.id

25 JOIN app_status AS status ON orderdrop.status_id = status.id’::text)

26 link(id INTEGER, orderdrop_date TIMESTAMP, created_at TIMESTAMP, orderdrop_num

VARCHAR(512), email VARCHAR(512), location_name VARCHAR(512), saleorder_num

VARCHAR(512), status_type VARCHAR(512), status_name VARCHAR(512))

27 WHERE link.created_at > (SELECT DISTINCT

28 CASE WHEN (SELECT MIN(cenas.max)

29 FROM (SELECT MAX(start_date_etl)

30 FROM audit_execution

31 WHERE TABLE_NAME = ’hub_orderdrop’

32

33 UNION

34

35 SELECT MAX(start_date_etl)


37 WHERE TABLE_NAME = ’sat_orderdrop’) AS cenas)

38 IS NULL

39 THEN TO_TIMESTAMP(’2000-01-01 01:00:00’, ’\%Y \%m \%d \%

h24:\%mi:\%s’)::TIMESTAMP WITHOUT TIME ZONE

40 ELSE

62 Anexos

41 (SELECT MIN(cenas.max)

42 FROM (SELECT MAX(start_date_etl)


44 WHERE TABLE_NAME = ’hub_orderdrop’

45

46 UNION

47

48 SELECT MAX(start_date_etl)


50 WHERE table_name = ’sat_orderdrop’) AS cenas )

51 END

52 AS start_date_etl);

A.4 Vista representativa de uma tabela de facto (v_fact_orderdrop)

1 CREATE VIEW v_fact_orderdrop AS

2 SELECT items.hub_orderdrop_id,

3 items.hub_product_id,

4 items.hub_saleorder_id,

5 hub_saleorder.saleorder_num,

6 sat_orderdrop.created_at,

7 sat_orderdrop.order_date,

8 sat_orderdrop_items.qnt_estimated,

9 sat_orderdrop_items.qnt_actual,

10 sat_status_link_orderdrop_items.status,

11 sat_status_link_orderdrop_items.type

12 FROM public.link_orderdrop_items AS items

13 LEFT JOIN v_dim_product ON items.hub_product_id = v_dim_product.hub_product_id

14 LEFT JOIN public.sat_orderdrop_items ON items.link_orderdrop_items_id = public.

sat_orderdrop_items.link_orderdrop_items_id

15 LEFT JOIN public.sat_status_link_orderdrop_items ON items.link_orderdrop_items_id =

public.sat_status_link_orderdrop_items.link_orderdrop_items_id

16 LEFT JOIN public.hub_saleorder ON items.hub_saleorder_id = public.hub_saleorder.

hub_saleorder_id

17 LEFT JOIN public.sat_orderdrop ON items.hub_orderdrop_id = public.sat_orderdrop.

hub_orderdrop_id

18 ORDER BY items.hub_saleorder_id;

A.5 Vista representativa de uma tabela de dimensão (dim_orderdrop)

1 CREATE VIEW v_dim_orderdrop AS

2 SELECT sat.hub_orderdrop_id,

3 sat.load_date AS sattelite_load_date,

4 sat.location,

5 sat.created_by,

6 CAST(sat.order_num AS INT)

7 FROM public.sat_orderdrop AS sat

8 JOIN public.hub_orderdrop AS hub ON sat.hub_orderdrop_id = hub.hub_orderdrop_id;

A.6 Vista exemplo do real time schema 63

A.6 Vista exemplo do real time schema

1 CREATE VIEW rt_real_orders_done AS

2 SELECT link.huubclient_name,

3 link.sales_channel_name,

4 link.nb_orders

5 FROM dblink(’host=psqlhuub.ddns.net port=62345 user=openpg password=

AGT_pwd_05012015 dbname=huubapp’::text,

6 ’SELECT DISTINCT huubclient_name,

7 sales_channel_name,

8 COUNT(orderdrop_id) OVER(PARTITION BY huubclient_name, sales_channel_name)

AS nb_orders

9 FROM(

10 SELECT bb.*,

11 sch.name sales_channel_name,

12 hc.name huubclient_name

13 FROM(

14 SELECT DISTINCT orderdrop_id,

15 SUM(qnt_estimated_allocation) OVER(PARTITION BY

orderdrop_id) AS qnt_estimated_allocation,

16 SUM(qnt_actual_allocation) OVER(PARTITION BY orderdrop_id)

AS qnt_actual_allocation,

17 CAST(SUM(qnt_actual_allocation) OVER(PARTITION BY

orderdrop_id) AS FLOAT) / CAST(SUM(

qnt_estimated_allocation) OVER(PARTITION BY

orderdrop_id) AS FLOAT) AS percent_done

18 FROM(

19 SELECT DISTINCT CAST(row_data->’’orderdrop_id’’ AS INT) AS

orderdrop_id,

20 odi.product_model_id,

21 odi.qnt_estimated_allocation,

22 odi.qnt_actual_allocation

23 FROM audit.logged_actions

24 JOIN app_orderdrop_items odi ON CAST(row_data->’’

orderdrop_id’’ AS INT) = odi.orderdrop_id

25 WHERE table_name = ’’app_orderdrop_items’’ AND action = ’’U

’’ AND action_tstamp_tx::date = NOW()::date AND

changed_fields->’’qnt_actual_allocation’’ IS NOT NULL

26 ORDER BY 1

27 ) AS b

28 ORDER BY 1

29 ) AS bb

30 JOIN app_orderdrop od ON bb.orderdrop_id = od.id

31 JOIN app_salesorder so ON od.salesorder_id = so.id

32 JOIN app_sales_channel sch ON so.sales_channel_id = sch.id

33 JOIN app_huubclient hc ON so.huubclient_id = hc.id

34 WHERE percent_done >= 1

35 ) AS a

36 ORDER BY 1,2

64 Anexos

37 ’::text)

38 link(huubclient_name VARCHAR(512), sales_channel_name VARCHAR(512), nb_orders

INTEGER);

Referências

[1] Wayne Eckerson. The Secrets of Creating Successful Business Intelligence Solutions SmartCompanies in the 21st Century. Seattle: The Data Warehousing Institute, 2010.

[2] Solomon Negash. Communications of the Association for Information Systems BusinessIntelligence BUSINESS INTELLIGENCE. Communications of the Association for Infor-mation Systems, 13:177–195, 2004.

[3] Cristiano Xavier e Fernando Moreira. Agile ETL. Procedia Technology, 9:381–387, 2013.

[4] W. H. Inmon. Building the data warehouse. New York, terceira edição, 2002.

[5] E F Codd. The Relational Model for Database Management. Segunda edição, 1990.

[6] Ralph Kimball e Margy Ross. The Data Warehouse Toolkit. The Complete Guide to Dimen-sional Modelling. Indianapolis, terceira edição, 2013.

[7] Daniel Moody e Mark A.R Kortink. From Enterprise Models to Dimensional Models: AMethodology for Data Warehouse and Data Mart Design. Proceedings of the InternationalWorkshop on Design and Management of Data Warehouses (DMDW’2000), 2000:5–16.

[8] Dan E Linstedt. Data vault series 1 - data vault overview, janeiro 2017.DATAVERSITY Education, LLC, disponível em http://tdan.com/data-vault-series-1-data-vault-overview/5054, acedido em fevereirode 2017.

[9] Dragoljub Krneta, Vladan Jovanovic, e Zoran Marjanovic. An Approach to Data Mart Designfrom a Data Vault. INFOTEH-JAHORINA, 15:473–478, March 2016.

[10] Gregory Hills. Enterprise reporting - definition, maio 2008. Gregory Hills, disponí-vel em http://ghill.customer.netspace.net.au/reporting/definition.html, acedido em fevereiro de 2017.

[11] João Carlos Assunção Duarte. Dashboard Visual , Uma ferramenta de Business Intelligence.Relatório técnico, Faculdade de Engenharia da Universidade do Porto, Porto, 2012.

[12] K. Pauwels, T. Ambler, B. H. Clark, P. LaPointe, D. Reibstein, B. Skiera, B. Wierenga, eT. Wiesel. Dashboards as a Service: Why, What, How, and What Research Is Needed?Journal of Service Research, 12:175–189, Novembro 2009.

[13] Miguel Marques Nunes Soares Paulo. Implementação de um Modelo de Data Warehousepara o Serviço Nacional de Avisos Agrícolas. Relatório técnico, Universidade Nova de Lis-boa, Lisboa, 2011.

65

http://tdan.com/data-vault-series-1-data-vault-overview/5054

http://tdan.com/data-vault-series-1-data-vault-overview/5054

http://ghill.customer.netspace.net.au/reporting/definition.html

http://ghill.customer.netspace.net.au/reporting/definition.html

66 REFERÊNCIAS

[14] Hitachi Group. Pentaho data integration, janeiro 2015. Hitachi Group, disponível emhttp://community.pentaho.com/projects/data-integration/, acedido emfevereiro de 2017.

[15] Microsoft. What is Power BI?, maio 2017. Microsoft, disponível em https://powerbi.microsoft.com/en-us/what-is-power-bi/, acedido em março de 2017.

[16] Microsoft. Power pivot: Poderosa análise e modelação de dados no Excel, maio2017. Microsoft, disponível em https://support.office.com/pt-pt/article/Power-Pivot-Poderosa-an%C3%A1lise-e-modela%C3%A7%C3%A3o-de-dados-no-Excel-a9c2c6e2-cc49-4976-a7d7-40896795d045, ace-dido em março de 2017.

http://community.pentaho.com/projects/data-integration/

https://powerbi.microsoft.com/en-us/what-is-power-bi/

https://powerbi.microsoft.com/en-us/what-is-power-bi/

https://support.office.com/pt-pt/article/Power-Pivot-Poderosa-an%C3%A1lise-e-modela%C3%A7%C3%A3o-de-dados-no-Excel-a9c2c6e2-cc49-4976-a7d7-40896795d045



Documents

Sistema de apoio à decisão suportado por armazém de dados ... · recorrer a processos de Extract, Transform And Load (ETL) que extraem dados das respetivas fontes e procedem ao