Solução de ETL desenvolvida para o cliente CETIC.br –
Centro de Estudos sobre as Tecnologias da Informação e da
Comunicação
Rick Miranda Ferreira
DRE: 108038503
Projeto de Graduação apresentado ao Curso de
Engenharia Eletrônica e de Computação da Escola
Politécnica, Universidade Federal do Rio de
Janeiro, como parte dos requisitos necessários à
obtenção do título de Engenheiro.
Orientador: Aloysio de Castro Pinto Pedroza
Orientador: Diego da Silva Rodrigues
Rio de Janeiro
Julho de 2014
Solução de ETL desenvolvida para o cliente CETIC.br Centro de Estudos sobre as Tecnologias da Informação e da
Comunicação
Rick Miranda Ferreira
PROJETO DE GRADUAÇÃO SUBMETIDO AO CORPO DOCENTE DO CURSO
DE ENGENHARIA ELETRÔNICA E DE COMPUTAÇÃO DA ESCOLA
POLITÉCNICA DA UNIVERSIDADE FEDERAL DO RIO DE JANEIRO COMO
PARTE DOS REQUISITOS NECESSÁRIOS PARA A OBTENÇÃO DO GRAU DE
ENGENHEIRO ELETRÔNICO E DE COMPUTAÇÃO
Autor:
_________________________________________________
Rick Miranda Ferreira
Orientador:
_________________________________________________
Prof. Aloysio de Castro Pinto Pedroza, Dr.
Orientador:
_________________________________________________
Diego da Silva Rodrigues, M. Sc.
Examinador:
_________________________________________________
Prof. Flávio Luis de Mello, D.Sc.
Examinador:
_________________________________________________
Prof Antônio Cláudio Gómez de Sousa, Dr.
Rio de Janeiro – RJ, Brasil
Julho de 2014
UNIVERSIDADE FEDERAL DO RIO DE JANEIRO
Escola Politécnica – Departamento de Eletrônica e de Computação
Centro de Tecnologia, bloco H, sala H-217, Cidade Universitária
Rio de Janeiro – RJ
CEP 21949-900
Este exemplar é de propriedade da Universidade Federal do Rio de Janeiro, que
poderá incluí-lo em base de dados, armazenar em computador, microfilmar ou adotar
qualquer forma de arquivamento.
É permitida a menção, reprodução parcial ou integral e a transmissão entre
bibliotecas deste trabalho, sem modificação de seu texto, em qualquer meio que esteja
ou venha a ser fixado, para pesquisa acadêmica, comentários e citações, desde que sem
finalidade comercial e que seja feita a referência bibliográfica completa.
Os conceitos expressos neste trabalho são de responsabilidade do(s) autor(es).
iii
AGRADECIMENTO
Dedico primeiramente este trabalho aos meus pais que contribuíram diretamente
em minha formação como cidadão e profissional. Dedico também a todos os
profissionais da empresa Calandra Soluções que me ensinaram e ajudaram sempre que
precisei.
iv
RESUMO
No mercado econômico atual, onde as empresas precisam se destacar entre seus
concorrentes, algum método diferencial deve ser implantado. Hoje em dia, a informação
é a ferramenta mais poderosa e importante que uma organização pode ter, portanto,
deve-se saber explorá-la da forma mais inteligente possível para obter conhecimento
sobre a própria empresa e tomar decisões estratégicas, visando a melhoria da
organização. Nesse contexto, a qualidade dos dados é de suma importância para que
toda essa informação possa se tornar conhecimento, destacando então a grande
importância do processo de ETL num modelo de Data Warehouse. Tendo em vista essa
importância, o tema desse trabalho é a solução de ETL desenvolvida conforme as regras
de negócio do cliente CETIC.br. Neste sentido, pretende-se extrair os dados das
pesquisas do Brasil sobre o uso das tecnologias da informação e da comunicação, fazer
as transformações necessárias para que o portal, também desenvolvido pela empresa
Calandra, possa consumir os dados da maneira adequada e apresenta-los de maneira
mais dinâmica auxiliando no melhor entendimento dos resultados das pesquisas
coletadas. Os resultados obtidos foram satisfatórios, cumprindo com todos os requisitos
e funcionalidades contratadas pelo CETIC.br. Para obter um entendimento tácito das
regras de negócio, foi necessária uma imersão no negócio e foi preciso realizar diversos
testes durante o desenvolvimento para que o sistema cobrisse todo o escopo previsto.
Palavras-Chave: Extração, Tranformação, Carga, Dados, Armazém de dados, Talend,
CETIC.
v
ABSTRACT
In the current market, where companies need to stand out amongst competitors, a
unique method must be thought of and put into practice. Nowadays, information is the
most powerful and influential tool that an organization can have, therefore, it is a tool
that needs to be explored cleverly to be able to gain knowledge about the company itself
and to make strategic decisions with the company’s goals in mind, which eventually
will gain the company prominence in the market. In this context, data quality is
extremely important so that all this information can be turned into knowledge;
highlighting the importance of an (ETL) process in a Data Warehouse model. Keeping
this in mind, the topic of this project entails the solution of ETL developed according to
the business conventions of the client CETIC.br. Accordingly, the goal is to review data
from research in Brazil about the use of information technology and communication,
making the necessary changes so that the portal, also developed by the company
Calandra, may use the data in the best manner and present the company in a more
dynamic way, assisting from the best understanding of the results from the collected
research. The results were satisfactory, complying with all the requirements and
functionalities contracted by CETIC.br. To acquire a better understanding of the
business conventions, an immersion in the business was necessary, which also included
the performance of several tests during the development of the system so that it would
cover the results anticipated.
Key-words: Extract, Tranform, Load, Data, Data Warehouse, Talend, CETIC.
vi
SIGLAS
BI – Business Intelligence
CETIC – Centro de Estudos sobre as Tecnologias da Informação e da Comunicação
CGI – Comitê Gestor da Internet
CSV – Comma-separated Values
DW – Data Warehouse
ETL – Extract Tranform Load
MD – Modelo Dimensional
NIC – Núcleo de Informação e Coordenação
OLAP – On-line Analytical Processing
OLTP – On-line Transaction Processing
TIC – Tecnologia da informação e da comunicação
UFRJ – Universidade Federal do Rio de Janeiro
XML – eXtensible Markup Language
vii
Sumário
Lista de figuras ....................................................................................................................X
Lista de tabelas ................................................................................................................. XI
1 Introdução ........................................................................................................................ 1
1.1 Tema ............................................................................................................................ 1
1.2 Delimitação .............................................................................................................. 1
1.3 Justificativa ............................................................................................................. 1
1.4 Objetivos ................................................................................................................... 2
1.5 Metodologia ............................................................................................................. 2
1.6 Descrição .................................................................................................................. 3
2 Apresentação do problema ..................................................................................... 4
2.1 Sobre o cliente ....................................................................................................... 4
2.2 Justificativa do projeto..................................................................................... 5
2.3 Objetivo do projeto ............................................................................................. 5
2.4 Atual ferramenta web do cliente ................................................................ 5
3 Conceitos de BI .............................................................................................................. 7
3.1 OLTP............................................................................................................................. 7
3.2 Data Marts ................................................................................................................ 7
3.3 Data Warehouse ................................................................................................... 8
3.4 Modelagem multidimensional ...................................................................... 8
3.5 Metadado ................................................................................................................ 10
3.6 ETL – Extração, transformação e carga de dados .......................... 10
3.7 OLAP .......................................................................................................................... 11
4 Modelagem de dados dos dicionários ............................................................. 13
4.1 Modelo de Entidade-Relacionamento .................................................... 13
4.2 Modelagem multidimensional – Template .......................................... 17
4.3 Dicionários ............................................................................................................. 19
4.4 Layout dos arquivos de dicionários ........................................................ 21
4.4.1
4.4.2
4.4.3
Layout do dicionário de perguntas .............................................. 21
Layout do dicionário de respostas ............................................... 22
Layout do dicionário de indicadores ........................................... 23
5 Solução de ETL desenvolvida ............................................................................... 28
5.1 Funcionalidades .................................................................................................. 28
5.2 Ferramentas utilizadas ................................................................................... 28
viii
5.3 Padrões .................................................................................................................... 28
5.4 Funcionamento .................................................................................................... 29
5.4.1
5.4.2
Carga dos dados ................................................................................. 30
Exclusão dos dados ........................................................................... 31
5.5 Processo de ETL .................................................................................................. 33
5.5.1
5.5.2
5.5.3
5.5.4
5.5.5
5.5.6
5.5.7
5.5.8
5.5.9
ETL – Job Start ................................................................................... 34
ETL – Job orquestrador ................................................................... 34
ETL – Job deleta ................................................................................. 35
ETL - Job carrega stage ................................................................... 36
ETL - Job carrega dw dicionários ................................................. 37
ETL – Job gera arquivo dicionário ............................................... 38
ETL – Job microdado ........................................................................ 39
ETL – Job send logs .......................................................................... 40
Execução do ETL ................................................................................ 41
5.6 Interface do portal ao usuário final ........................................................ 41
6 Conclusão ........................................................................................................................ 44
6.1 Resultados alcançados ................................................................................... 44
6.2 Trabalhos futuros e possíveis extensões ............................................ 45
Bibliografia ........................................................................................................................... 46
ix
Lista de figuras
Figura 1 - Escopo macro do projeto ................................................................... 5
Figura 2 - Atual apresentação de indicadores de pesquisas Fonte:
www.cetic.br ................................................................................................................ 6
Figura 3 - Modelo Estrela e Floco de Neve – Fonte: Kimball 2002. ....... 9
Figura 4 - Diagrama de ETL Fonte:
http://www.imc.com/services/enterprise-data-warehousing/etlprocess-management na data 13/08/2014................................................... 10
Figura 5 - Modelo de dados dos dicionários .................................................. 13
Figura 6 - Modelo multidimensional ................................................................. 18
Figura 7 - Layout do dicionario de perguntas - parte 1 ........................... 21
Figura 8 - Layout do dicionário de pergunta - parte 2 ............................. 22
Figura 9 - Layout do dicionário de respostas ............................................... 23
Figura 10 - Layout do dicionário de indicadores - parte 1 ...................... 24
Figura 11 - Layout do dicionário de indicadores - parte 2 ...................... 25
Figura 12 - Layout do dicionário de indicadores - parte 3 ...................... 25
Figura 13 - Layout do dicionário de indicadores - parte 4 ...................... 26
Figura 14 - Layout do dicionário de indicadores - parte 5 ...................... 26
Figura 15 - Layout do dicionário de indicadores - parte 6 ...................... 27
Figura 16 - Arquivo de configuração "database.xml" ................................ 29
Figura 17 - Arquivo de configuração "email_log.xml"............................... 30
Figura 18 - Arquivo de configuração de carga de dados
"estrutura_generico.xml"...................................................................................... 30
Figura 19 - Exemplo de configuração de carga de dados ........................ 31
Figura 20 – Arquivo de configuração “estrutura_generico.xml”
configurado para excluir dados .......................................................................... 32
Figura 21 – Exemplo de configuração do XML para exclusão de dados
........................................................................................................................................ 32
Figura 22 - Fluxograma de funcionamento do ETL .................................... 33
Figura 23 – Job Start .............................................................................................. 34
Figura 24 - Job orquestrador_generico ........................................................... 34
Figura 25 - Job Deleta ........................................................................................... 36
Figura 26 - Job carrega stage ............................................................................. 36
Figura 27 – Job carrega dw dicionários .......................................................... 37
Figura 28 - Job gera arquivo dicionário – parte 1 ...................................... 38
Figura 29 - Job gera arquivo dicionário - parte 2 ....................................... 39
Figura 30 - Job microdado – parte 1 ............................................................... 39
Figura 31 - Job microdado - parte 2 ................................................................ 40
Figura 32 - Job send logs ..................................................................................... 40
Figura 33 - Indicadores da pesquisa de Domicílios 2012 ........................ 41
Figura 34 - Série histórica Domicílios por computador (2008 - 2012)42
Figura 35 - Perfil de usuários com acesso as TIC's .................................... 42
Figura 36 – Proporção de Domicílios 2012 que possuem
equipamentos TIC.................................................................................................... 43
x
Lista de tabelas
Tabela
Tabela
Tabela
Tabela
Tabela
Tabela
Tabela
Tabela
Tabela
Tabela
Tabela
1 - Tabela dc_pesquisa ........................................................................... 14
2 - Tabela dc_unidade_analise ............................................................ 15
3 - Tabela dc_grupo_indicador ............................................................ 15
4 - Tabela dc_indicador .......................................................................... 15
5 - Tabela dc_pergunta .......................................................................... 16
6 - Tabela dc_resposta ........................................................................... 17
7 - Tabela dm_regiao .............................................................................. 19
8 - Tabela dm_<var_agrupamento> ................................................ 19
9 - Tabela ft_pesquisa_<ano>_<ua>_<idioma> ........................ 19
10 - Padrão de códigos dos idiomas .................................................. 28
11 - Padrão de códigos das pesquisas e unidades de análise . 29
xi
1 Introdução
1.1 Tema
O tema desse trabalho é a solução de ETL desenvolvida conforme as regras de
negócio do cliente CETIC.br. Neste sentido, o ETL deve extrair os dados das pesquisas
do Brasil sobre o uso das tecnologias da informação e da comunicação, fazer as
transformações necessárias para que o portal, também desenvolvido pela empresa
Calandra, possa consumir os dados da maneira adequada.
1.2 Delimitação
A delimitação desse projeto é desenvolver apenas a solução de ETL para que o
portal desenvolvido possa consumir as informações. Não faz parte do escopo desse
trabalho apresentar o desenvolvimento do site. A solução foi específica para o cliente
tendo em vista a complexidade das regras do negócio.
1.3 Justificativa
Dado a importância do processo ETL na arquitetura de um Data Warehouse, esse
tema tornou-se muito interessante. É um tema que precisa de mais ênfase tendo em
conta sua importância. ETL é uma das soluções de BI que vem ganhando terreno nas
organizações no que toca o processamento dos dados.
Esse processo é muito utilizado na empresa Calandra Soluções para integração de
dados. Também ele é utilizado para auxiliar na realização dos relatórios e entre outros.
O processamento correto desses dados é crítico para que se possa transformar essa
informação em conhecimento e utilizar essas informações como um diferencial no
mercado.
Neste sentido, pretende-se analisar o uso dessa tecnologia na empresa Calandra,
aplicado ao projeto CETIC.br, como sendo uma oportunidade de conhecimento e de
novos desafios.
1
1.4 Objetivos
O objetivo geral é propor um modelo de dados de dicionários que traduzam para
o sistema as perguntas e respostas das pesquisas e estruture as informações de forma
adequada. Desta forma, tem-se como objetivos específicos:

Propor um modelo de dados para os dicionários de perguntas, respostas e
indicadores;

Implementar um ETL para extrair as informações;

Transformar os dados de acordo com as regras de negócio;

Criar um armazém de dados das pesquisas;

Carregar os dados nos repositórios para que o portal possa consumi-los.
1.5 Metodologia
Foram realizadas reuniões com o cliente para que se pudesse entender o
problema e então estudar e discutir uma possível solução. Em seguida, foram
desenvolvidos os dicionários com o intuito de traduzir as perguntas, respostas e os
indicadores para o sistema. A partir disso, foi desenvolvido o modelo lógico de dados
respeitando as regras de negócio. Após esta etapa, foi implementado o ETL para fazer a
carga dos dados no DW para então carregar a ferramenta OLAP Calandra BI e verificar
se o sistema respondia da maneira desejada reproduzindo o resultado das consultas do
antigo site do cliente. Foram realizadas inúmeras interações com o cliente durante todo
o processo de desenvolvimento para que todos os ajustes necessários para mapear as
inconsistências, até que o sistema desenvolvido fosse confiável e representasse as regras
de negócio fielmente. Por fim, carregou-se os repositórios definitivos para que os dados
fossem indexados e consultados pelo novo portal.
A modelagem utilizada para os dicionários na arquitetura de Data Warehouse foi
a Entidade-Relacionamento. Já a modelagem utilizada no solr, repositório final de
dados, foi a modelagem multidimensional. No desenvolvimento do sistema foi adotada
a abordagem estruturada.
2
1.6 Descrição
No capítulo 2 será apresentado o cliente assim como a especificação do
problema. O capítulo 3 apresenta os conceitos básicos para entendimento do tema
abordado no projeto. A modelagem de dados e os layouts dos dicionários desenvolvidos
serão apresentados no capítulo 4. No capítulo 5 será explicitada a solução de ETL
desenvolvida para o projeto CETIC, assim como os procedimentos para utilizar o
sistema. No capítulo 6 serão apresentados os resultados e possíveis extensões do
projeto.
3
2 Apresentação do problema
Neste capítulo, será apresentado o cliente como também a motivação por parte
deste para o projeto e seus objetivos específicos.
2.1 Sobre o cliente
O Comitê Gestor da Internet no Brasil – CGI.br, criado pela Portaria
Interministerial nº 147 em maio/1995 e alterada pelo Decreto Presidencial nº 4.829 em
setembro/2003, tem como missão coordenar e integrar todas as iniciativas de serviços
Internet no Brasil, promovendo a qualidade técnica, a inovação e a disseminação dos
serviços ofertados. Dentre as suas atribuições e responsabilidades, o CGI.br coleta,
organiza e dissemina informações, indicadores e estatísticas sobre o uso das tecnologias
de informação e comunicação no Brasil.
O Núcleo de Informação e Coordenação do Ponto BR – NIC.br é uma entidade
civil privada e sem fins lucrativos, criado para implementar as decisões e projetos do
CGI.br. O Centro de Estudos sobre as Tecnologias da Informação e da Comunicação
(CETIC.br) é um departamento do NIC.br e tem como objetivo conduzir pesquisas
especializadas, produzir e divulgar indicadores, estatísticas e informações estratégicas
sobre o desenvolvimento da Internet no Brasil. O CETIC.br vem concentrando esforços
para a ampliação e melhoria da qualidade dos indicadores e das estatísticas produzidas
anualmente em suas pesquisas, com o objetivo de garantir a confiabilidade dos dados, a
geração de melhores informações e, sobretudo, melhor nível de comparabilidade
internacional [3].
Atualmente o CETIC.br realiza as seguintes pesquisas sobre o acesso e uso das
TIC no Brasil: TIC Domicílios, TIC Empresas, TIC Microempresas, TIC Crianças, TIC
Kids Online, TIC Educação, TIC Saúde, TIC Centros de Acesso Público (Tele centros e
Lanhouses), TIC Governo Eletrônico, TIC Organizações Sem Fins Lucrativos e TIC
Provedores.
4
2.2 Justificativa do projeto
O antigo site do CETIC.br apresentava os dados das pesquisas em tabelas
estáticas e de difícil interpretação para o usuário. O cliente também tinha a necessidade
de poder comparar, ao longo dos anos, dados que eram relevantes para a sociedade,
academia e pesquisadores além de deixar a visualização destes mais atraentes.
2.3 Objetivo do projeto
O principal objetivo do projeto era construir uma ferramenta de visualização
web, responsável por disponibilizar relatórios, gráficos e mapas por meio de indicadores
do armazém de dados do CETIC.br até abril de 2014. Na Figura 1 foi apresentado o
escopo macro do projeto [4]. Com isso, espera-se:

Melhor padronização e estruturação dos dados gerados pelas pesquisas;

Melhor transferência de conhecimento para a sociedade, academia e
pesquisadores;

Facilidade na consulta e análise das pesquisas geradas através de técnicas de
visualizações modernas.
Figura 1 - Escopo macro do projeto
2.4 Atual ferramenta web do cliente
No atual site do cliente os indicadores são exibidos conforme a Figura 2 com os
conceitos necessários para melhor entendimento da estrutura das tabelas.
5
Figura 2 - Atual apresentação de indicadores de pesquisas
Fonte: www.cetic.br
6
3 Conceitos de BI
Nesse capítulo serão apresentados os conceitos de Business Intelligence que são
fundamentais para o bom entendimento do projeto.
3.1 OLTP
Sistemas OLTP, do inglês on-line transaction processing: são sistemas que têm a
tarefa de monitorar e processar as funções básicas e rotineiras de uma organização, tais
como processamento da folha de pagamento, faturamento, estoque, etc. Os fatores
críticos de sucesso para este tipo de sistema são: alto grau de precisão, integridade a
nível transacional e produção de documentos em tempo hábil.
Os dados transacionais OLTP são usados pelos usuários em geral no dia-a-dia
em seus processos e transações, gravação e leitura. Ex.: consulta de estoque, registro de
vendas.
O principal objetivo da modelagem relacional em um sistema OLTP é eliminar
ao máximo a redundância, de tal forma que uma transação que promova mudanças no
estado do banco de dados, atue o mais pontualmente possível. Com isso, nas
metodologias de projeto usuais, os dados são fragmentados por diversas tabelas
(normalizados), o que traz uma considerável complexidade à formulação de uma
consulta por um usuário final. Por isso, esta abordagem não parece ser a mais adequada
para o projeto de um Data Warehouse, onde estruturas mais simples, com menor grau de
normalização devem ser buscadas.
3.2 Data Marts
O Data Warehouse é normalmente acedido através de Data Marts, que são
pontos específicos de acesso a subconjuntos do Data Warehouse. Os Data Marts são
construídos para responder prováveis perguntas de um tipo específico de usuário. Por
exemplo: um Data Mart financeiro poderia armazenar informações consolidadas dia a
dia para um usuário gerencial e em periodicidades maiores (semana, mês, ano) para um
usuário no nível da diretoria. Um Data Mart pode ser composto por um ou mais cubos
de dados.
7
3.3 Data Warehouse
Um Data Warehouse, ou ainda armazém de dados, é utilizado para armazenar
informações relativas às atividades de uma organização em bancos de dados, de forma
consolidada. A estrutura da base de dados favorece os relatórios, a análise de grandes
volumes de dados e a obtenção de informações estratégicas que podem facilitar
a tomada de decisão.
O Data Warehouse possibilita a análise de grandes volumes de dados, coletados
dos sistemas transacionais (OLTP). São as chamadas séries históricas que possibilitam
uma melhor análise de eventos passados, oferecendo suporte às tomadas de decisões
presentes e a previsão de eventos futuros. Por definição, os dados em um Data
Warehouse não são voláteis, ou seja, eles não mudam, salvo quando é necessário fazer
correções de dados previamente carregados. Os dados estão disponíveis somente para
leitura e não podem ser alterados.
Os Data Warehouse surgiram como conceito acadêmico na década de 80. Com o
amadurecimento dos sistemas de informação empresariais, as necessidades de análise
dos dados cresceram paralelamente. Os sistemas OLTP não conseguiam cumprir a
tarefa de análise com a simples geração de relatórios. Nesse contexto, a implementação
do Data Warehouse passou a se tornar realidade nas grandes corporações. O mercado de
ferramentas de Data Warehouse, que faz parte do mercado de Business Intelligence,
cresceu então, e ferramentas melhores e mais sofisticadas foram desenvolvidas para
apoiar a estrutura do Data Warehouse e sua utilização.
Atualmente, por sua capacidade de sumarizar e analisar grandes volumes de
dados, o Data Warehouse é o núcleo dos sistemas de informações gerenciais e apoio à
decisão das principais soluções de Business Intelligence do mercado.
3.4 Modelagem multidimensional
Os sistemas de base de dados tradicionais armazenam os dados em formato
normalizado para garantir consistência dos dados, minimização do espaço de
armazenamento necessário e diminuição de redundâncias, que devem ser verificadas
antes da conclusão do modelo de dados. Entretanto, algumas transações e consultas
costumam ser mais complexas devido ao número de tabelas envolvidas.
8
Os elementos que compõem um modelo dimensional são: Tabela Fato e Tabela
Dimensão. A tabela de fatos é a principal tabela de um modelo dimensional, onde as
medições numéricas de interesse da empresa estão armazenadas [2]. A palavra "fato"
representa uma medida dos processos modelados, como quantidades, valores e
indicadores. A tabela de fatos registra os fatos que serão analisados. É composta por
uma chave primária (formada por uma combinação única de valores de chaves de
dimensão) e pelas métricas de interesse para o negócio. A tabela de dimensão é
composta de atributos e contém a descrição do negócio. Seus atributos são fontes das
restrições de consultas, agrupamento dos resultados e cabeçalhos para relatórios. Ela
possui aspectos pelos quais se pretende observar as métricas relativas ao processo
modelado. A tabela de dimensão costuma ser bem menor do que a tabela fato.
Um Data Warehouse restringe-se a normalizar as dimensões e usar tabelas fatos
com granularidade única. Isto aumenta o desempenho das consultas e como benefício
adicional, o processo torna-se mais intuitivo para os utilizadores leigos. Essa maneira de
reordenar os dados chama-se Modelagem Dimensional, e o resultado da modelagem é o
Modelo Dimensional, ou MD.
Esse tipo de modelagem tem dois modelos: Modelo Estrela (Star Schema e
Modelo Floco de Neve (Snow Flake) [4]. O nome “estrela” se dá devido à disposição
em que se encontram as tabelas, sendo a fato centralizada relacionando-se com diversas
outras tabelas de dimensão. Já o modelo “floco de neve” consiste em uma extensão do
modelo estrela onde cada uma das "pontas da estrela" passa a ser o centro de outras
estrelas. Isto porque cada tabela de dimensão seria normalizada, "quebrando-se" a tabela
original ao longo de hierarquias existentes em seus atributos. Podemos visualizar os
dois modelos na Figura 3.
Figura 3 - Modelo Estrela e Floco de Neve – Fonte: Kimball 2002.
9
3.5 Metadado
O conceito metadado é considerado como sendo os "dados sobre dados", isto é,
os dados sobre os sistemas que operam com estes dados. Um repositório de metadados é
uma ferramenta essencial para o gerenciamento de um Data Warehouse no momento de
converter dados em informações para o negócio. Entre outras coisas, um repositório de
metadados bem construído deve conter informações sobre a origem dos dados, regras de
transformação, nomes e alias, formatos de dados, etc. Ou seja, esse "dicionário" deve
conter muito mais do que as descrições de colunas e tabelas: deve conter informações
que adicionem valor aos dados.
3.6 ETL – Extração, transformação e carga de dados
A etapa de ETL é uma das mais críticas de um projeto de DW, pois uma
informação carregada erroneamente trará consequências imprevisíveis nas fases
posteriores. O objetivo desta fase é fazer a integração de informações de fontes
múltiplas e complexas. Basicamente, divide-se esta etapa em três passos: extração,
transformação e carga dos dados. Embora tenhamos hoje em dia ferramentas que
auxiliam na execução do trabalho, ainda assim é um processo trabalhoso, complexo e
também muito detalhado. A Figura 4 apresenta as etapas do ETL que serão descritas a
seguir.
Figura 4 - Diagrama de ETL
Fonte: http://www.imc.com/services/enterprise-data-warehousing/etl-process-management na data 13/08/2014
10
Carga: Num processo de ETL, primeiramente, há a necessidade de definir as origens
das fontes de dados e fazer a extração deles. As origens deles podem ser várias e
também em diferentes formatos, onde poderemos encontrar desde os sistemas
transacionais das empresas (por exemplo: SAP, BSCS, etc.) até planilhas, arquivos
textos e também arquivos DBF (dBase) ou do Microsoft Access.
Limpeza: Definidas as fontes de dados, é necessário o processo de transformação e
limpeza dos dados. A limpeza é necessária porque os dados normalmente vêm de uma
fonte muitas vezes desconhecida, concebida há bastante tempo pelo cliente, contendo
inconsistências e dados sem significado para o negócio.
Transformação. Uma vez que a origem dos dados pode ser de sistemas diferentes, às
vezes é necessário padronizar os diferentes formatos. Quando levamos esses dados para
o DW, deve-se ter uma padronização deles, ou seja, deve ser colocado sob uma mesma
estrutura.
3.7 OLAP
OLAP, ou On-line Analytical Processing é a capacidade para manipular e
analisar um grande volume de dados sob múltiplas perspectivas. As aplicações OLAP
são usadas pelos gestores em qualquer nível da organização para lhes permitir análises
comparativas que facilitem a sua tomada de decisões diárias.
Em um modelo de dados OLAP, a informação é conceitualmente organizada em
cubos que armazenam valores quantitativos ou medidas. As medidas são identificadas
por duas ou mais categorias descritivas denominadas dimensões que formam a estrutura
de um cubo. Uma dimensão pode ser qualquer visão do negócio que faça sentido para
sua análise, como produto, departamento ou tempo. Este modelo de dados
multidimensional simplifica para os usuários o processo de formular pesquisas ou
"queries" complexas, criar relatórios, efetuar análises comparativas, e visualizar
subconjuntos (slice) de maior interesse. Por exemplo, um cubo contendo informações de
vendas poderá ser composto pelas dimensões tempo, região, produto, cliente, cenário
11
(orçado ou real) e medidas. Medidas típicas seriam valor de venda, unidades vendidas,
custos, margem, etc.
Dentro de cada dimensão de um modelo OLAP, os dados podem ser organizados
em uma hierarquia que define diferentes níveis de detalhe. Por exemplo, dentro da
dimensão tempo, você poderá ter uma hierarquia representando os níveis anos, meses, e
dias. Da mesma forma, a dimensão região poderá ter os níveis país, região, estado e
cidade. Assim, um usuário visualizando dados em um modelo OLAP irá navegar para
cima (drill up) ou para baixo (drill down) entre níveis para visualizar informação com
maior ou menor nível de detalhe.
12
4 Modelagem de dados dos dicionários
O objetivo desse capítulo é apresentar o modelo de dados e os dicionários
criados para o projeto CETIC. A primeira seção descreve o modelo EntidadeRelacionamento dos dicionários, a segunda seção descreve genericamente o modelo
multidimensional, a terceira apresenta os dicionários desenvolvidos e a quarta seção
mostra o layout dos dicionários.
4.1 Modelo de Entidade-Relacionamento
O modelo de dados abaixo foi criado para solucionar a parte de armazenamento
dos dados dos dicionários e harmonização. A harmonização dos dados é de grande
importância, pois esta permite a comparação de indicadores ao longo do tempo que
muitas vezes os valores de referência são diferentes. Com isso, foi categorizado as
respostas nos campos harmonizados tornando-os assim equiparáveis.
Podemos visualizar o modelo na Figura 5 com suas as tabelas e seus respectivos
relacionamentos:
Figura 5 - Modelo de dados dos dicionários
13
Segue abaixo o padrão utilizado para nomear com prefixos as tabelas e campos
do modelo:

dc – abreviação de dicionário

id – abreviação de identificador

co – abreviação de código

no – abreviação de nome

ing_no – abreviação de inglês e nome

esp_no – abreviação de espanhol e nome

ds – abreviação de descrição

ing_ds – abreviação de inglês e descrição

esp_ds – abreviação de espanhol e descrição

tp – abreviação de tipo

tx – texto
O formato padrão adotado para todos os arquivos de dados foi CSV. Já os
arquivos de configuração do ETL, o padrão adotado foi XML.
Seguem abaixo as tabelas de atributos que compõem o modelo desenvolvido.
Tabela 1 - Tabela dc_pesquisa
Tabela
Descrição
dc_pesquisa
Tabela que armazena os dados de código e descrição de pesquisas existentes.
Ex: TIC_DOM, Pesquisa de Domicílios.
Campos
Tipo
Descrição
id_pesquisa
Int
Chave primária da tabela dc_pesquisa. Identificador da pesquisa no
modelo de dados. Ex: 1.
ano
Int
Ano em que ocorreu a pesquisa.
co_pesquisa
Varchar Código da Pesquisa. Ex: Tic_Dom.
no_pesquisa
Varchar
ing_no_pesquisa
Varchar Nome da Pesquisa em inglês (Descrição). Ex: Household Survey.
esp_no_pesquisa
Varchar Nome da Pesquisa em espanhol (Descrição). Ex: Encuesta de Hogares.
Nome da Pesquisa em português (Descrição). Ex: Pesquisa de
Domicílios.
14
Tabela 2 - Tabela dc_unidade_analise
Tabela
Descrição
dc_unidade_analise
Tabela que armazena os dados de código e texto das unidades de análise de
uma pesquisa. Ex:Alunos.
Campos
Tipo
id_unidade_analise
Int
id_pesquisa
Int
co_unidade_analise
Varchar Código da unidade de análise.
no_unidade_analise
Varchar Nome da unidade de análise. Ex: Alunos.
Descrição
Chave primária da tabela dc_unidade_analise. Identificador da
unidade de análise no modelo de dados. Ex: 1.
Chave estrangeira da tabela dc_pesquisa. Identificador da pesquisa
no nosso modelo de dados. Ex: 1.
ing_no_unidade_analise Varchar Nome da unidade de análise em Inglês. Ex: Students.
esp_no_unidade_analise Varchar Nome da unidade de análise em Espanhol. Ex: Estudiantes.
Tabela 3 - Tabela dc_grupo_indicador
Tabela
dc_grupo_indicador
Campos
id_grupo_indicador
id_unidade_analise
Descrição
Tabela que armazena os dados de código e texto dos grupos de indicadores de
uma pesquisa. Ex: B, Uso da Internet.
Tipo
Descrição
Chave primária da tabela dc_grupo_indicador. Identificador do
Int
grupo de indicadores no modelo de dados. Ex:1.
Chave estrangeira da tabela dc_unidade_analise. Identificador da
Int
unidade de análise no modelo de dados. Ex: 1.
Campos
Tipo
co_grupo_indicador
Varchar Código do grupo de indicadores. Ex: B.
Descrição
no_grupo_indicador
Varchar Nome do grupo de indicadores. Ex: Uso da Internet.
ing_no_grupo_indicador Varchar Nome do grupo de indicadores em inglês. Ex: Uso da Internet.
esp_no_grupo_indicador Varchar Nome do grupo de indicadores em Espanhol. Ex: Uso da Internet.
Tabela 4 - Tabela dc_indicador
Tabela
Descrição
dc_indicador
Tabela que armazena os dados de código e descrições dos indicadores.
Ex: B6, PROPORÇÃO DE EMPRESAS QUE POSSUEM WEBSITE.
Campos
Tipo
id_indicador
Int
id_grupo_indicador
Int
co_indicador
Varchar
no_indicador
Varchar
no_indicador_harmonizado
Varchar
tp_indicador
Varchar
ds_indicador
Varchar
Descrição
Chave primária da tabela dc_indicador. Identificador do
indicador no modelo de dados. Ex: 1
Chave estrangeira da tabela dc_grupo_indicador.
Identificador do grupo de indicadores no modelo de dados.
Ex:1
Código do indicador. Ex: B6
Nome do indicador. Ex: PROPORÇÃO DE EMPRESAS
QUE POSSUEM WEBSITE.
Nome do indicador harmonizado. Ex: PROPORÇÃO DE
EMPRESAS QUE USAM WEBSITE
Tipo do indicador. Pode ser D- dicotômico, DD- dicotômico
derivado, N- normal
Descrição do indicador. Ex: Percentual sobre o total de
empresas com acesso à Internet¹.
15
 Descrição da base de dados do indicador. Ex: ¹Base: 6 231
Varchar empresas que declararam ter acesso à Internet, com 10 ou
mais pessoas ocupadas e etc.
 Descrição das notas relativas ao indicador. Ex:
ds_nota_indicador
Varchar Considerando-se o computador de uso principal no
domicílio.
Nome do indicador em inglês. Ex: PROPORTION OF
Ing_no_indicador
Varchar
ENTERPRISES OWN WEBSITE.
Descrição do indicador em Inglês. Ex: Percentage of total
Ing_ds_indicador
Varchar
number of enterprises with Internet access ¹.
Descrição da base de dados do indicador em inglês. Ex:
Ing_ds_base_indicador
Varchar ¹Base: 6231 companies that reported having Internet access,
with 10 or more employees e etc.
 Descrição das notas relativas ao indicador em inglês. Ex:
ing_ds_nota_indicador
Varchar
Considering the main computer use at home.
Nome do indicador em espanhol. Ex: PROPORCIÓN DE
Esp_no_indicador
Varchar
EMPRESAS SITIO WEB PROPIA.
Descrição do indicador em espanhol. Ex: Porcentaje del
Esp_ds_indicador
Varchar
número total de empresas con acceso a Internet ¹.
Descrição base de dados do indicador em espanhol. Ex:
Esp_ds_base_indicador
Varchar ¹Base: 6.231 empresas que reportaron tener acceso a
Internet, em 10 o más empleados e etc.
 Descrição das notas relativas ao indicador em espanhol. Ex:
esp_ds_nota_indicador
Varchar Teniendo en cuenta que el principal uso del ordenador en
casa.
 Nome do indicador harmonizado em inglês. Ex:
ing_no_indicador_harmonizado Varchar
PROPORTION OF ENTERPRISES OWN WEBSITE.
 Nome do indicador harmonizado em espanhol. Ex:
esp_no_indicador_harmonizado Varchar
PROPORCIÓN DE EMPRESAS SITIO WEB PROPIA.
 Código do peso a ser utilizado no cálculo da proporção. Ex:
co_peso
Varchar
PesoBase.
 Texto do filtro a ser utilizado no cálculo da proporção. Ex:
tx_filtro
Varchar
Se P26 = 1.
Ds_base_indicador
Tabela 5 - Tabela dc_pergunta
Tabela
Descrição
dc_pergunta
Tabela que armazena os dados de código e texto das perguntas de uma
pesquisa. Ex: est_civ, Estado civil do entrevistado.
Campos
Tipo
id_pergunta
Int
id_unidade_analise
co_pergunta
co_pergunta_harmonizada
no_pergunta
uso_analise
Descrição
Chave primária da tabela dc_pergunta. Identificador da
pergunta no modelo de dados. Ex: 1
Chave estrangeira da tabela dc_unidade_analise.
Int
Identificador da unidade de análise no modelo de dados. Ex:
1
Varchar Código da pergunta. Ex: sexo.
Varchar Código da pergunta harmonizada. Ex: Sexo.
Nome da pergunta em português (Descrição). Ex: Qual o
Varchar
sexo do respondente?
Indica se a pergunta é de agrupamento ou dimensão. Ex: A –
Varchar
Agrupamento e D – dimensão.
no_categoria
Varchar Nome da categoria em português. Ex: Tecnologia de rede.
uso_resposta
Varchar
ing_no_pergunta
Indica se o texto a ser exibido é o da pergunta ou da resposta.
Ex: S.
Nome da pergunta em inglês (Descrição). Ex: What is the
Varchar
16el16 f the respondent?
16
esp_no_pergunta
Varchar
ing_no_categoria
Varchar
esp_no_categoria
Varchar
no_categoria_harmonizada
varchar
ing_no_categoria_harmonizada Varchar
esp_no_categoria_harmonizada Varchar
co_unidade_analise
Nome da pergunta em espanhol (Descrição). Ex: ¿Qué e sexo
el encuestado?
Nome da categoria em inglês (Descrição). Ex: Network
technology.
Nome da categoria em espanhol (Descrição). Ex: Tecnología
de red.
Nome da categoria harmonizada em português. Ex:
Tecnologia de rede.
Nome da categoria harmonizada em inglês. Ex: Network
technology.
Nome da categoria harmonizada em espanhol. Ex:
Tecnología de red.
Varchar Código da unidade de análise. Ex: Aluno.
Tabela 6 - Tabela dc_resposta
Tabela
Descrição
dc_resposta
Tabela que armazena os dados de código e texto das respostas de uma pesquisa.
Ex: 2, Centro.
Campos
Tipo
id_resposta
Int
id_pergunta
Int
co_resposta
Int
no_resposta
Varchar Nome da resposta em português (Descrição). Ex: Sim.
no_harmonizado
Varchar
ing_no_resposta
Varchar Nome da resposta em inglês (Descrição). Ex: Yes.
esp_no_resposta
Varchar Nome da resposta em espanhol (Descrição). Ex: Sí.
Descrição
Chave primária da tabela dc_resposta. Identificador da resposta no
modelo de dados. Ex:1
Chave estrangeira da tabela dc_pergunta. Identificador da pergunta no
nosso modelo de dados. Ex: 1
Código da resposta. Ex: 1.
Nome harmonizado designado para a resposta em português
(Descrição). Ex: 1 salário mínimo.
Nome harmonizado designado para a resposta em inglês (Descrição).
Ex: 1 brazilian minimum wage.
Nome harmonizado designado para a resposta em espanhol
esp_no_harmonizado Varchar
(Descrição). Ex: 1 salario mínimo brasileño.
ing_no_harmonizado Varchar
4.2 Modelagem multidimensional – Template
O modelo de dados abaixo foi criado como um template genérico que representa o
modelo multidimensional do projeto. Esta modelagem é importante para que se possa
fazer análises em tempo real (em ferramenta OLAP) fazendo combinações de
agrupamentos diversos com dimensões. As várias tabelas fatos serão geradas para cada
pesquisa/ano/unidade de análise/idioma. Apesar de não estarem representados nesse
modelo, terão fatos multilíngues.
Cada pesquisa tem no banco, suas dimensões de variáveis de agrupamento
específicas (representadas por dm_regiao e dm_<var_agrupamento>). Tem também
suas várias dimensões de perguntas, para as usadas na contagem dos indicadores
(representadas por co e no <pergunta>).
17
O conteúdo do campo genérico co_<pergunta>, é o código da resposta dada para a
pergunta que aquele campo representa. Da mesma forma o conteúdo do campo genérico
no_<pergunta>, é o nome da resposta dada para pergunta que aquele campo representa.
Figura 6 - Modelo multidimensional
Segue abaixo o padrão utilizado para nomear com prefixos as tabelas e campos
do modelo:

dm – abreviação de dimensão

ft – abreviação de fato

id – abreviação de identificador

co – abreviação de código

no – abreviação de nome

<> – representa que os nomes são genéricos

ano – representa o ano da pesquisa

UA – representa a unidade de análise

var_agrupamento – representa variáveis de agrupamento
18
Seguem abaixo as tabelas da modelagem multidimensional.
Tabela 7 - Tabela dm_regiao
Tabela
Descrição
dm_regiao
Tabela da dimensão região. Escolhida para estar no template por constar em
todas as pesquisas.
Campos
Tipo
Descrição
co_regiao
Int
Chave primária da dimensão dm_regiao. Código da região. Ex: reg_tab
co_resposta
Varchar
Código da resposta. Ex: 1. (se referencia a “Norte” pelo dicionário de
respostas)
Tabela 8 - Tabela dm_<var_agrupamento>
Tabela
Descrição
dm_<var_agrupamento>
Tabela que representa as demais dimensões necessárias para as variáveis de
agrupamento de cada pesquisa. Ex: Porte.
Campos
Tipo
co_<var_agrupamento>
Int
co_resposta
Descrição
Na dimensão vai existir um código da variável de agrupamento
referente à pesquisa. Ex: zona_tab.
Na dimensão vai existir um código que se refere a resposta da
Varchar pergunta referente ao agrupamento. Ex: 2 (se referencia a “Rural”
pelo dicionário de respostas)
Tabela 9 - Tabela ft_pesquisa_<ano>_<ua>_<idioma>
Tabela
Descrição
ft_pesquisa_<ano>_<ua>
Tabela que representa a fato de cada pesquisa no ano e na unidade de analise
específica.
Campos
Tipo
id_quest
Int
co_regiao
co_<var_agrupamento>
Descrição
Chave primária da fato que contem o identificador do questionário.
Ex: 100
Chave estrangeira da dimensão região. Código da região. Ex:
Varchar
reg_tab
Representa a chave estrangeira das demais dimensões de variáveis
Int
de agrupamento. Ex: zona_tab.
co_<var_pergunta>
Varchar Representa os códigos das perguntas daquela pesquisa. Ex: P1.
co_resposta
Varchar
peso
Varchar Campo de peso da resposta. Ex: 0.285432.
O conteúdo dele será o código da resposta para aquela pergunta. Ex:
1.
4.3 Dicionários
Os dicionários consistem em arquivos gerados no formato adequado para a carga
dos dados no sistema. Foram estruturados baseados no modelo de dados dos dicionários
e harmonização do projeto CETIC. Devem ser preenchidos nos padrões observados
nesta seção para que a inserção dos dados seja bem sucedida. O objetivo dos arquivos
de dicionários é carregar as informações para o banco de dados do projeto com todas as
descrições e associações entre indicadores, perguntas e respostas.
19
Os nomes dos arquivos de dicionários devem obedecer o formato abaixo:
<Código da pesquisa>_<ano>_dicionario_perguntas.csv
<Código da pesquisa>_<ano>_dicionario_respostas.csv
<Código da pesquisa>_<ano>_<unidade_análise>_dicionario_indicadores.csv
<Código indicativo da Pesquisa>_<ano>_erro.csv
Dados_<Código indicativo da Pesquisa>_<ano>.csv
Onde:
Código da pesquisa é a abreviação TIC_ somada a abreviação (3 letras) designada a um
dos tipos de pesquisa existentes. Ex: TIC_DOM para Pesquisa de Domicílios.
Exemplo de nome de arquivo: TIC_DOM_2012_dicionario_perguntas.csv.
O projeto adotou como padrão que os arquivos fontes dos dados são de formato
CSV (comma-separated values). O sistema não será capaz de carregar arquivos em
outras extensões.
Exemplo de nome completo de arquivo: TIC_DOM_2012_dicionario_perguntas.csv
O padrão utilizado para nomear os campos dos arquivos de um dicionário segue
a mesma forma do modelo de dados, onde:

dc – abreviação de dicionário

id – abreviação de identificador

co – abreviação de código

no – abreviação de nome

ing_no – abreviação de inglês e nome

esp_no – abreviação de espanhol e nome

ds – abreviação de descrição

ing_ds – abreviação de inglês e descrição

esp_ds – abreviação de espanhol e descrição

tp – abreviação de tipo

tx – texto
20
4.4 Layout dos arquivos de dicionários
A estrutura de layout dos arquivos de dicionário é essencial para a parte de carga
dos dados do projeto CETIC. É necessário respeitar o nome, o conteúdo e a posição dos
campos para o processo de inserção dos dados seja bem sucedido.
4.4.1 Layout do dicionário de perguntas
O Layout do dicionário de perguntas é formado por dois campos essenciais
(co_pergunta e no_pergunta) a serem preenchidos para a carga dos dados e outros
opcionais.

tipo_var: Contém o tipo da pergunta (D – dimensão ou A – Agrupamento). Pode
ser observado na Figura 7 coluna A. Ex: D.

co_pergunta: Contém o código associado ao texto da pergunta de uma pesquisa.
Pode ser observado na Figura 7 coluna B. Ex: QUEST.

co_pergunta_harmonizada: Contém o código da pergunta harmonizada. Não foi
necessária a utilização da mesma no decorrer do projeto, logo ficará em branco.
Coluna C da Figura 7.

no_pergunta: Contém o valor do texto da pergunta de uma pesquisa. Pode ser
observado na coluna D da Figura 7. Ex: Número do Questionário.

no_categoria: Contém o valor do texto referente a uma categoria na dimensão, se
existir. Ex: Banda Larga. Pode ser observado na coluna E da Figura 7.

uso_resposta: Variável de controle para utilizar ou não a descrição da pergunta
no lugar da descrição da resposta. Se uso_resposta = S, o no_pergunta será
exibido no lugar da descrição da resposta dessa pergunta (Utilizado sempre
quando o indicador relacionado é do tipo Dicotômico Derivado – DD). Na
Figura 7 pode ser observado na coluna F.

ing_no_pergunta: Contém o valor do texto da pergunta de uma pesquisa em
inglês. É opcional. Na Figura 7 pode ser observado na coluna G.
Figura 7 - Layout do dicionario de perguntas - parte 1
21

esp_no_pergunta: Contém o valor do texto da pergunta de uma pesquisa em
espanhol. É opcional. Na Figura 8 pode ser observado na coluna H.

ing_categoria: Contém o valor do texto da categoria de uma pesquisa em inglês.
É opcional. Na Figura 8 pode ser observado na coluna I.

esp_categoria: Contém o valor do texto da categoria de uma pesquisa em
espanhol. É opcional. Na Figura 8 pode ser observado na coluna J.

no_categoria_harmonizada: Contém o valor do texto da categoria harmonizada.
Não foi necessária a utilização da mesma no decorrer do projeto, logo ficará em
branco. Coluna K da Figura 8.

ing_no_categoria_harmonizada: Contém o valor do texto da categoria
harmonizada em inglês. Não foi necessária a utilização da mesma no decorrer do
projeto, logo ficará em branco. Coluna L da Figura 8.

esp_no_categoria_harmonizada: Contém o valor do texto da categoria
harmonizada em espanhol. Não foi necessária a utilização da mesma no decorrer
do projeto, logo ficará em branco. Coluna M da Figura 8.
Figura 8 - Layout do dicionário de pergunta - parte 2
4.4.2 Layout do dicionário de respostas
O Layout do dicionário de respostas é formado por três campos essenciais a
serem preenchidos para a carga dos dados, outros quatro campos opcionais de idioma e
um opcional de harmonização. Segue abaixo a descrição de cada campo que compõe o
dicionário:

co_pergunta: Contém o valor da variável associada ao texto da pergunta nos
microdados de uma pesquisa. Na Figura 9 pode ser observado na coluna A. Ex:
SEXO.
22

co_resposta: Contém o valor da variável associada ao texto da resposta nos
microdados de uma pesquisa. Na Figura 9 abaixo pode ser observado na coluna
B. Ex: 1.

no_resposta: Contém o valor do texto da resposta de uma pesquisa. Na Figura 9
pode ser observado na coluna C. Ex: Masc.

ing_no_resposta: Contém o valor do texto da resposta de uma pesquisa em
inglês. É opcional. Na Figura 9 pode ser observado na coluna D.

esp_no_resposta: Contém o valor do texto da resposta de uma pesquisa em
espanhol. É opcional. Na Figura 9 pode ser observado na coluna E.

no_harmonizado: Contém o valor do texto da resposta harmonizada de uma
pesquisa. Será usado nas respostas de indicadores com evolução histórica. É
opcional. Na Figura 9 pode ser observado na coluna F.

ing_no_harmonizado: Contém o valor do texto da resposta harmonizada de uma
pesquisa em inglês. É opcional. Na Figura 9 pode ser observado na coluna G.

esp_no_harmonizado: Contém o valor do texto da resposta harmonizada de uma
pesquisa em espanhol. É opcional. Na Figura 9 pode ser observado a coluna H.
Figura 9 - Layout do dicionário de respostas
4.4.3 Layout do dicionário de indicadores
O Layout do dicionário de indicadores é formado por treze campos essenciais
(pesquisas com micro dados) a serem preenchidos para a carga dos dados e outros
campos opcionais de idiomas. Segue abaixo a descrição de cada campo que compõe o
dicionário:

no_pesquisa: Contém o nome da pesquisa padronizado por “TIC” + o nome da
pesquisa em letras maiúsculas. Ex: TIC DOMICÍLIOS. Na Figura 10 pode ser
observado na coluna A.

no_unidade_analise: Contém o nome da unidade de análise associada a pesquisa,
por convenção será também todas as letras em maiúsculo. Ex: USUÁRIOS. No
23
caso de pesquisas que não tenham unidade de análise deverá constar nesse
campo o nome da pesquisa. Observar o exemplo da Figura 10 abaixo na célula
B.

co_pergunta: Contém o valor da variável associada ao texto da pergunta nos
microdados de uma pesquisa. Na Figura 10 abaixo pode ser observado na coluna
C. Ex: b1.

co_indicador: Contém o valor do código do indicador associado a uma pergunta.
Ex: B1. Na Figura 10 abaixo pode ser observado na coluna D.

no_indicador: Contém o nome de um indicador. Ex: Proporção de indivíduos
que já utilizaram um computador. Na Figura 10 abaixo pode ser observado na
coluna E.
Figura 10 - Layout do dicionário de indicadores - parte 1

no_indicador_harmonizado: Contém o nome de um indicador harmonizado ao
longo dos anos. Quando este campo está preenchido, significa que este indicador
deverá ser exibido no gráfico da série histórica do portal. Ex. Proporção de
indivíduos que já utilizaram um computador. No exemplo da coluna F, ele está
em branco, logo não será harmonizado.

tp_indicador: Coluna que identifica o tipo do indicador. Código D – Dicotômico,
DD – Dicotômico Derivado e N – Não dicotômico. Ex: D exibido na coluna G.

co_grupo_indicador: Contém o valor do código do grupo de um indicador. Ex:
B. Na Figura 11 pode ser observado na coluna H.

no_grupo_indicador: Contém o nome do grupo de um indicador. Ex: Uso do
Computador. Na Figura 11 abaixo pode ser observado na coluna I.
24
Figura 11 - Layout do dicionário de indicadores - parte 2

ds_indicador: Contém a descrição do universo dos indivíduos entrevistados no
indicador. Ex: Percentual sobre o total da população. Na Figura 11 pode ser
observado na coluna J.

ds_base_indicador: Contém a descrição da base de dados do indicador. Ex:
Base: 6 271 empresas que declararam utilizar computador, com 10 ou mais
pessoas...”. Na Figura 12 pode ser observado na coluna K.

ds_nota_indicador: Contém a descrição do universo de entrevistados a ser
considerado no indicador. Na Figura 12 pode ser observado na coluna L.

ing_no_indicador: Contém o nome de um indicador em inglês. É opcional. Na
figura abaixo pode ser observado na coluna M.

ing_no_grupo_indicador: Contém o nome do grupo de um indicador em inglês.
É opcional. Na Figura 12 pode ser observado na coluna N.

ing_ds_indicador: Contém a descrição do indicador em inglês. É opcional. Na
Figura 12 pode ser observado na coluna O.
Figura 12 - Layout do dicionário de indicadores - parte 3

ing_ds_base_indicador: Contém a descrição da base de dados do indicador em
inglês. É opcional. Na Figura 13 pode ser observado na coluna P.

ing_ds_nota_indicador: Contém a descrição do universo de entrevistados a ser
considerado no indicado em inglês. É opcional. Na Figura 13 pode ser observado
na coluna Q.

esp_no_indicador: Contém o nome de um indicador em espanhol. É opcional.
Na Figura 13 pode ser observado na coluna R.
25

esp_no_grupo_indicador: Contém o nome do grupo de um indicador em
espanhol. É opcional. Na Figura 13 pode ser observado na coluna S.

esp_ds_indicador: Contém a descrição do indicador em espanhol. É opcional. Na
Figura 13 pode ser observado na coluna T.

esp_ds_base_indicador: Contém a descrição da base de dados do indicador em
espanhol. É opcional. Na Figura 13 pode ser observado na coluna U.

esp_ds_nota_indicador: Contém a descrição do universo de entrevistados a ser
considerado no indicado em espanhol. É opcional. Na Figura 13 pode ser
observado na coluna V.
Figura 13 - Layout do dicionário de indicadores - parte 4

ing_no_unidade_analise: Contém o nome da unidade de análise associada a
pesquisa em inglês. É opcional. Observar o exemplo da Figura 14 na célula W.

esp_no_unidade_analise: Contém o nome da unidade de análise associada a
pesquisa em espanhol. É opcional. Observar o exemplo da Figura 14 na célula
X.

ing_no_pesquisa: Contém o nome da pesquisa em inglês. É opcional. Observar o
exemplo da Figura 14 na célula Y.

esp_no_pesquisa: Contém o nome da pesquisa em espanhol. É opcional.
Observar o exemplo da Figura 14 na célula Z.

ing_no_indicador_harmonizado: Contém a descrição do indicador harmonizado
em inglês. É opcional. Observar o exemplo da Figura 14 na célula AA.
Figura 14 - Layout do dicionário de indicadores - parte 5
26

esp_no_indicador_harmonizado: Contém a descrição do indicador harmonizado
em espanhol. É opcional. Observar o exemplo da Figura 15 na célula AB.

co_peso: Contém o código do peso a ser calculado (somente quando há
microdados). Pode ser observado na coluna AC.

tx_filtro: Contém o texto da expressão do universo que será calculado (somente
quando há microdados). Pode ser observado na coluna AD.
Figura 15 - Layout do dicionário de indicadores - parte 6
27
5 Solução de ETL desenvolvida
O objetivo dessa seção é apresentar as funcionalidades do sistema, os parâmetros
de configurações, ferramentas utilizadas e o funcionamento de cada etapa.
5.1 Funcionalidades
As principais funcionalidades do sistema são:

Carregar os arquivos de microdados de determinadas pesquisas;

Carregar os arquivos de dicionários de perguntas, respostas e indicadores de
determinadas pesquisas;

Carregar apenas os pares de agrupamento x dimensão considerados válidos através da
tabela de erro;

Carregar as pesquisas pré-calculadas e estruturar os dados no mesmo modelo das
pesquisas calculadas;

Apagar o repositório de dados dos microdados de determinadas pesquisas;

Apagar o repositório de dados dos dicionários de determinadas pesquisas;
5.2 Ferramentas utilizadas
Para realizar a solução de ETL foi utilizada a ferramenta Talend Open Studio
versão 5.3.1. A Ferramenta de busca e indexação foi o Apache Solr versão 4.0. Foi
utilizada também a ferramenta OLAP Calandra BI versão 3.0.3.
5.3 Padrões
O padrão adotado de códigos de pesquisa, unidade de análise e idiomas do
projeto CETIC podem ser encontrados na Tabela 11 e na Tabela 10.
Tabela 10 - Padrão de códigos dos idiomas
Código do idioma
Idioma
pt
Português
en
Inglês
es
Espanhol
28
Tabela 11 - Padrão de códigos das pesquisas e unidades de análise
Código da unidade de análise (sem
Código da pesquisa
acento)
TIC_EMP
Empresas
TIC_DOM
Domicilios
TIC_DOM
Usuarios
TIC_EDU
Aluno
TIC_EDU
Coordenador
TIC_EDU
Diretor
TIC_EDU
Escola
TIC_EDU
Professor
5.4 Funcionamento
Foram desenvolvidas estruturas de arquivos XML que configuram o ETL. São
eles: “database.xml” e “email_log.xml”.

“database.xml”: Arquivo que possui as conexões com o banco de dados
MYSQL, endereço de e-mail e senha de autenticação para envio de log,
configurações de FTP e do solr. As configurações são preenchidas conforme o
exemplo da Figura 16:
Figura 16 - Arquivo de configuração "database.xml"
29

“email_log.xml”: Arquivo que possui as configurações de e-mail do destinatário
dos logs de execução do ETL. Será preenchido o “id”, nome do destinatário e
endereço de e-mail conforme Figura 17.
Figura 17 - Arquivo de configuração "email_log.xml"
Após a configuração dos arquivos XML é necessária a criação das tabelas do
banco de dados que serão utilizadas. Para tal foi gerado o script de criação utilizando o
programa Workbench.
5.4.1 Carga dos dados
Para
realizar
a
carga
dos
dados,
devemos
configurar
o
arquivo
“estrutura_generico.xml”. As duas primeiras linhas do código e a última são
obrigatórias para que o XML seja lido corretamente. Os blocos do meio vão se repetir
sempre no padrão ilustrado na Figura 18:
Figura 18 - Arquivo de configuração de carga de dados "estrutura_generico.xml"
5.4.1.1 Exemplo de carga de dados
No primeiro bloco da Figura 19, o arquivo XML está configurado para carregar
a pesquisa de Domicílios, do ano de 2010, unidade de análise de Domicílios e idioma
português. Nas marcações de dicionários e diretórios foram preenchidos o diretório de
entrada, nomes dos arquivos a serem carregados, diretório de saída e arquivo de
proporção. A pesquisa de domicílios do ano de 2010 é considerada como pré-calculada
e por isso a marcação com o nome do arquivo de microdados deverá estar vazia.
30
No segundo bloco da Figura 19, o arquivo XML está configurado para carregar a
pesquisa de Domicílios, do ano de 2011, unidade de análise de Domicílios e idioma
português. Nas marcações de dicionários e diretórios foram preenchidos o diretório de
entrada, nomes dos arquivos a serem carregados, diretório de saída e arquivo de erro.
Na pesquisa de domicílios do ano de 2011 existe o arquivo de microdados e por isso a
marcação <arq_pesquisa> está preenchida com o nome do arquivo.
Figura 19 - Exemplo de configuração de carga de dados
Nas pesquisas com microdados deverá conter um bloco preenchido para cada
pesquisa/ano/unidade_analise/idioma. Somente no caso das pesquisas pré-calculadas
deverá conter um bloco para cada pesquisa/ano/unidade_analise. Logo para fazer uma
carga com todas as pesquisas do ano de 2012, deve-se copiar o bloco com as marcações
de dicionários e diretórios para cada pesquisa/unidade_analise/idioma mantendo o ano
como 2012. Do mesmo modo, para realizar uma carga de uma determinada pesquisa ao
longo dos anos, deve-se ter um bloco por ano/unidade_analise/idioma mantendo o
mesmo código da pesquisa.
5.4.2 Exclusão dos dados
Para
realizar
a
exclusão
dos
“estrutura_generico.xml” da seguinte forma:
31
dados
basta
configurar
o
arquivo
Figura 20 – Arquivo de configuração “estrutura_generico.xml” configurado para excluir dados
Como mostrado na Figura 20, as marcações de diretórios e dicionários deverão
ficar vazias e preencher apenas o código da pesquisa, ano, unidade de análise e idioma a
ser apagado. No caso das pesquisas pré-calculadas, o idioma é desconsiderado tendo em
vista que não existe microdados. Desse modo, para apagar as pesquisas de um
determinado ano basta repetir o bloco com o código da pesquisa/unidade_analise/idioma
mantendo o ano igual para todos.
5.4.2.1 Exemplo de exclusão de dados
O comando de excluir dados pode ser realizado em conjunto com alguma carga,
pois o ETL é executado por bloco de estrutura sequencialmente. No exemplo da Figura
21, serão excluídos os dados da pesquisa de domicílios, ano de 2011, unidade de análise
domicílios e idioma português. No segundo bloco do XML, será realizada a carga dos
dados da mesma pesquisa.
Figura 21 – Exemplo de configuração do XML para exclusão de dados
32
5.5 Processo de ETL
A Figura 22 apresenta em alto nível o comportamento do sistema de ETL. Nas
subseções será mostrada cada etapa com maiores detalhes. Na ferramenta Talend Open
Studio essas etapas são chamadas de “job’s”.
Figura 22 - Fluxograma de funcionamento do ETL
33
5.5.1 ETL – Job Start
O job “Start” é responsável por ler o XML que contém as configurações do
banco de dados, configurações da autenticação de remetente de e-mail e carrega-las
como variáveis de contexto para que possam ser utilizadas no job filho
“job_orquestrador_generico”. Na Figura 23 podemos visualizar o “job Start”.
Figura 23 – Job Start
5.5.2 ETL – Job orquestrador
O “job_orquestrador_generico” foi dividido em sete partes e pode ser
visualizado na Figura 24:
Figura 24 - Job orquestrador_generico
34

A primeira parte é responsável por carregar as configurações do arquivo XML
(diretórios, nome dos arquivos, código da pesquisa, ano, unidade de análise)
identificar se será realizada a opção de apagar alguma base de dados (core de
microdados ou dicionários) e chamar o “job_deleta”;

A segunda parte apenas limpa as tabelas “stage”;

A terceira parte lê o arquivo XML, verifica se deve carregar alguma base de
dados e, se solicitado, chama o “job_carrega_stage”;

A quarta parte do ETL é a chamada DW, onde os dados carregados nas tabelas
stage
são
colocados
em
tabelas
no
banco
MYSQL
arquivo
de
metadados
pelo
“job_carrega_dw_dicionarios”;

A
quinta
parte
gera
“job_gera_arquivo_dicionario”
o
que
será
carregado
no
através
core
do
chamado
“cetic_dicionario”.

Na sexta etapa, são identificadas as pesquias que contém microdados através do
arquivo XML de configuração e é chamado o “job_microdado”.

O “job_orquestrador_generico” contém mais dois blocos isolados que capturam
informações de erros de execução do ETL, estatísticas e os carrega nas tabelas
“tos_log” e “tos_stats”. Na última etapa, é chamado o “job_send_logs”.
5.5.3 ETL – Job deleta
O “job_deleta”, exibido na Figura 25, será chamado quantas vezes forem
necessárias para apagar os core’s que foram configurados no XML. No primeiro bloco,
é apagado o core dos microdados relacionado à pesquisa/ano/unidade_análise/idioma
solicitado. Em seguida, apagam-se os dados relacionados no core de dicionários e
também no dicionário_resposta.
35
Figura 25 - Job Deleta
5.5.4 ETL - Job carrega stage
O “job_carrega_stage”, Figura 26, será executado da mesma forma que o
job_deleta, porém ele carregará as tabelas stage de acordo com as planilhas de
indicadores, perguntas, respostas e erro/proporção. Nessa etapa os arquivos são lidos e
carregados diretamente em tabelas no banco de dados.
Figura 26 - Job carrega stage
36
5.5.5 ETL - Job carrega dw dicionários
O “job_carrega_dw_dicionarios”, Figura 27, será executado uma única vez
independentemente da quantidade de pesquisas que foram carregadas. Nessa rotina são
realizadas as consultas nas tabelas stage e montadas as tabelas com as chaves
identificadoras de acordo com o modelo de dados do negócio.
Figura 27 – Job carrega dw dicionários
37
5.5.6 ETL – Job gera arquivo dicionário
No “job_gera_arquivo_dicionario”, Figura 28, são feitos todos os cruzamentos
de agrupamentos com dimensões e estrutura dos metadados que serão consumidos pelo
web service.
Figura 28 - Job gera arquivo dicionário – parte 1
Já na segunda parte do job, Figura 29, é feita uma comparação com a tabela de
erro/proporção para validar os cruzamentos considerados válidos. Em seguida é
carregado o arquivo “cetic_dicionario” no core de metadados com o mesmo nome do
arquivo. Além deste, o core “cetic_dicionario_resposta” também é carregado para que
seja traduzido para o sistema o texto que define o filtro a ser calculado em cada
indicador.
38
Figura 29 - Job gera arquivo dicionário - parte 2
5.5.7 ETL – Job microdado
O
“job_microdado”,
Figura
30,
é
executado
toda
vez
que
uma
pesquisa/ano/unidade_análise/idioma é solicitada para fazer a carga e tenha o arquivo de
microdados. Nesse job são feitas as manipulações dos microdados para trazer a
descrição no lugar do código da resposta. Na segunda parte do job, Figura 31, o core
solicitado é carregado e é criado o esquema de variáveis para ser importado no Calandra
BI.
Figura 30 - Job microdado – parte 1
39
Figura 31 - Job microdado - parte 2
5.5.8 ETL – Job send logs
O job_send_logs, Figura 32, será executado uma vez para enviar o log por email para o analista indicado no XML “email_log”. Além disso, duas tabelas de log são
alimentadas com os erros e estatísticas: tos_log e tos_stats.
Figura 32 - Job send logs
40
5.5.9 Execução do ETL
Após os arquivos XML’s estarem configurados, as tabelas e procedures do
banco de dados MySQL criadas, o usuário deverá executar o arquivo “start_run.sh” e
automaticamente o “job_start” será iniciado chamando os jobs filhos. O tempo de carga
varia de acordo com a quantidade de pesquisas a serem carregadas. O tempo total para
carregar todas as pesquisas do repositório foi de 7 minutos em ambiente de produção.
5.6 Interface do portal ao usuário final
As Figura 33,Figura 34, Figura 35 e Figura 36 apresentam as possíveis interfaces
ao usuário mostrando os indicadores que se encontram no repositório de dados do
CETIC.br. Essa ferramenta web foi desenvolvida pelo setor de desenvolvimento da
empresa Calandra e consome os dados tratos pelo ETL e armazenados no Solr que é o
repositório final de dados.
Figura 33 - Indicadores da pesquisa de Domicílios 2012
41
Figura 34 - Série histórica Domicílios por computador (2008 - 2012)
Figura 35 - Perfil de usuários com acesso as TIC's
42
Figura 36 – Proporção de Domicílios 2012 que possuem equipamentos TIC
43
6 Conclusão
Este capítulo conclui este trabalho, resumindo o que foi feito ao longo do
mesmo, os resultados obtidos, as possíveis extensões e trabalhos futuros para o projeto.
Relembrando, o primeiro capítulo expôs o objetivo do trabalho visto as necessidades do
cliente. O segundo capítulo apresenta o cliente e o problema prático em si. O terceiro
capítulo descreveu os conceitos usados ao longo projeto assim como as técnicas
utilizadas. O quarto capítulo expõe a modelagem de dados e estruturas de dados
desenvolvidas. No quinto capítulo foi apresentado a solução de ETL e explicado o papel
de cada etapa do desenvolvimento.
6.1 Resultados alcançados
A proposta deste trabalho foi estruturar os dados, fazer a modelagem e
desenvolver o ETL para que os dados sejam devidamente disponibilizados para que o
portal consuma essas informações. Dentro desse escopo, foram alcançados por este
trabalho os seguintes resultados:

O modelo de dados foi modelado corretamente garantindo que as relações entre
as tabelas fossem consistentes;

Foi desenvolvido um ETL que dispõe as regras do negócio cobrindo todos os
tipos e exceções das pesquisas sobre as tecnologias coordenadas pelo CETIC.br;

Com a evolução do protótipo apresentado ao cliente, foi aumentado a
performance do ETL em cerca de 95% quando constatado um baixo
desempenho de dois componentes da ferramenta Talend Open Studio e
desenvolvidos dois componentes que efetuavam a mesma função;

Satisfação do cliente quanto a facilidade de configuração, documentação e
execução do sistema de ETL desenvolvido;

Realização do treinamento para capacitação do cliente para operar o sistema e
conhecer melhor o funcionamento da ferramenta.
44
6.2 Trabalhos futuros e possíveis extensões
O desenvolvimento do ETL atendeu perfeitamente as necessidades e requisitos
do projeto. As possíveis extensões do projeto, que inclusive já foram contratadas pelo
cliente, são:

Substituição das planilhas de dicionários por uma aplicação em que o
preenchimento seja mais ágil e confiável minimizando os erros de digitação que
são frequentes. Com isso o cliente cadastraria as perguntas, respostas e
indicadores nas telas da aplicação e os dados seriam estruturados direto na etapa
de DW;

Efetuar o cálculo do erro dos cruzamentos de agrupamento x dimensão sob
demanda. Atualmente o cálculo do erro é realizado fora do ETL e a partir da
tabela de erro gerada pelo cliente o sistema valida os cruzamentos que devem ser
apresentados. Com o cálculo do erro sendo em tempo real, as possibilidades de
cruzamentos aumentam bastante, dando mais flexibilidade e poder ao usuário
final.
Este trabalho apresentou a solução desenvolvida para modelagem dos dados,
extração, transformação e carga dos dados das pesquisas do Brasil de uso da tecnologia
e informação que são monitoradas pelo CETIC.br.
A divulgação desse projeto foi autorizada previamente pelo cliente.
45
Bibliografia
[1] MACHADO, Felipe Nery Rodrigues, “Tecnologia e Projeto de Data Warehouse:
Uma visão multidimensional”. São Paulo, Brasil, 2008
[2] KIMBALL, Ralf, The Data Warehouse Toolkit: The Complete Guide to
Dimensional Modeling”. USA, 2002.
[3] Portal CETIC.br, http://cetic.br/, Acessado em Julho de 2014.
[4] Documento interno de requisitos da empresa Calandra – Projeto CETIC
[5] ECKERSON, Wayne, WHITE, Colin, “Evaluating ETL and Data Integration
Platforms”. USA, 2003.
[6] FERREIRA, João, MIRANDA, Miguel, ABELHA, Antonio, MACHADO, José,
“O processo ETL em Sistemas Data Warehouse”,
http://inforum.org.pt/INForum2010/papers/sistemas-inteligentes/Paper080.pdf
Acessado em Junho de 2014
[7] RIBEIRO, Lívia, “Um estudo sobre proveniência e complementação de dados no
contexto do processo de ETL”,
http://www.comp.ime.eb.br/techreports/repositorio/2010_01.pdf, Acessado em
Junho de 2014
[8] SANTOS, Valdinei, “Data Warehouse: Análise de ferramentas de ETL”,
http://www.uniedu.sed.sc.gov.br/wp-content/uploads/2013/10/Valdinei-Valmir-dosSantos.pdf Acessado em Julho de 2014
46