Embed Size (px)
Citation preview
PONTIFÍCIA UNIVERSIDADE CATÓLICA DE MINAS GERAIS
Programa de Pós-Graduação em Business Intelligence
Daniel Silva Marques Vilela
CONSTRUÇÃO DE UMA APLICAÇÃO ETL PARA O SICOP
Belo Horizonte
2014
II
Daniel Silva Marques Vilela
CONSTRUÇÃO DE UMA APLICAÇÃO ETL PARA O SICOP
Relatório Técnico apresentado
Programa de Pós-Graduação
em Informática da Pontifícia
Universidade Católica de Minas
Gerais, como requisito parcial
para obtenção de título de
Especialista em Business
Intelligence.
Orientadora: Sheila Mara
Oliveira Dias.
Belo Horizonte
2014
III
Daniel Silva Marques Vilela
CONSTRUÇÃO DE UMA APLICAÇÃO ETL PARA O SICOP
Relatório Técnico apresentado
Programa de Pós-Graduação
em Informática da Pontifícia
Universidade Católica de Minas
Gerais, como requisito parcial
para obtenção de título de
Especialista em Business
Intelligence.
______________________________________________
Sheila Mara Oliveira Dias
______________________________________________
Daniel Silva Marques Vilela
Belo Horizonte, 14 de outubro de 2014
IV
RESUMO
Este trabalho demonstrou a criação de uma aplicação ETL (Extract, Transform, Load
– Extrair, Transformar, Carregar) para o Sistema de Controle Operacional (SICOP) da
Cemig Telecomunicações S/A (CEMIGTelecom). Uma aplicação ETL é a etapa do
projeto de Business Intelligence (BI) onde é criado um banco de dados, baseado no
banco de dados transacional do sistema original, já com todas amarrações entre
tabelas e todos ajustes necessários para facilitar a utilização na interface de Análise
do usuário final. O objetivo desta aplicação foi realizar uma Prova de Conceito (Proof
of Concept – POC) dessa etapa inicial de um projeto de BI para verificar suas
possíveis vantagens no contexto de extração de dados para geração de informação e
conhecimento. A aplicação foi focada no módulo do Órgão Implantação do Sistema,
mais especificamente no controle de Viabilidade, Ordem de Serviço (OS), Ordem
Técnica de Serviço (OTS), Ordem de Serviço Interna (OSI) e Solução de Atendimento
(SA) para realizar o controle de Obra. A ferramenta usada na condução do trabalho
foi o Microsoft SQL Server Integraton Services (SSIS), pois além de ter sido uma das
ferramentas aprendidas durante o curso de pós-graduação, ela faz parte do pacote do
contrato da CEMIGTelecom com a Microsoft, portanto não houve custos para
adiciona-la ao projeto.
Palavras-chave: ETL. Extract, Transform, Load. Extrair, Transformar, Carregar,
SICOP. Sistema de Controle Operacional. BI. Business Intelligence. POC. Proof of
Concept. Prova de Conceito. Viabilidade. OS. Ordem de Serviço. OTS. Ordem
Técnica de Serviço. OSI. Ordem de Serviço Interna. SA. Solução de Atendimento.
SSAS. SQL Server Analysis Services. Bancos de Dados. Analytics.
V
ABSTRACT
This project presented the creation of an ETL application (Extract, Transform, Load) to
the Sistema de Controle Operacional (SICOP – Operational Control System) belonging
to Cemig Telecomunicações S/A (CEMIGTelecom). An ETL application is one of the
first steps of a Business Intelligence (BI) project, in which is created another database,
based off the original system transactional database, where it already has all table
joins and necessary adjustments in order to facilitate end user utilization of the Analysis
interface. The objective in this application was to manage a Proof of Concept (POC)
of a BI project initial step to verify all the advantages in the generation of information
and knowledge context. The application was mainly focused on SICOP’s
Implementation Department module, specifically on controls of Viability, Service Order
(SO), Technical Service Order (TSO), Internal Service Order (ISO) and Solution to
accomplish Civil Work management. Microsoft SQL Server Integration Services (SSIS)
was the tool used on this task, for reasons of previous knowledge (the tool was taught
during the graduate school program) and no-cost budget, because it was part of bundle
in CEMIGTelecom’s agreement with Microsoft.
Key-words: ETL. Extract, Transform, Load. SICOP. Operational Control System. BI.
Business Intelligence. POC. Proof of Concept. Viability. SO. Service Order. TSO.
Technical Service Order. ISO. Internal Service Order. Solution. SSAS. SQL Server
Analysis Services. Database. Analytics.
VI
LISTA DE FIGURAS
Figura 1 – Fases do BI. ..................................................................................................................... 12
Figura 2 – As fases do ETL. ............................................................................................................. 13
Figura 3 – Processo Operacional da CEMIGTelecom ................................................................. 15
Figura 4 – Exemplo da tabela de Tipos de Equipamento. ........................................................... 16
Figura 5 – Exemplo da tabela de Pedidos de Viabilidade. .......................................................... 17
Figura 6 – Exemplo de uma tabela de fluxo do Hibernate ........................................................... 18
Figura 7 – Parte do script de geração de dados de OS ............................................................... 19
Figura 8 – Planilha OS ....................................................................................................................... 19
Figura 9 – Esquema Estrela ............................................................................................................. 21
Figura 10 – Modelagem Fato Prazos em Esquema Estrela ........................................................ 23
Figura 11 – Dimensão Obra.............................................................................................................. 24
Figura 12 – Dimensão OS ................................................................................................................. 24
Figura 13 – Dimensão Endereço ..................................................................................................... 25
Figura 14 – Dimensão Datas ............................................................................................................ 25
Figura 15 – Fato Prazos .................................................................................................................... 26
Figura 16 – SSIS – ETL – Parte do Projeto Geral ........................................................................ 28
Figura 17 – SSIS – ETL – Detalhe Dataflow Obra ........................................................................ 29
Figura 18 – SSIS – ETL – Detalhe Obra 2 – Data Source ........................................................... 30
Figura 19 – SSIS – ETL – Detalhe Obra 3 – Data Convertion .................................................... 31
Figura 20 – SSIS – ETL – Detalhe Obra 4 – Lookup .................................................................... 32
Figura 21 – SSIS – ETL – Detalhe Obra 5 – Carga DM............................................................... 33
Figura 22 – Parte da Staging Area 2 ............................................................................................... 34
Figura 23 – STG 2 – Tabela Obra Transformada ......................................................................... 34
Figura 24 – STG 2 – Tabela POP Transformada .......................................................................... 35
Figura 25 – Dashboard do Usuário Final ........................................................................................ 36
VII
SUMÁRIO
RESUMO .............................................................................................................................................. IV
ABSTRACT ........................................................................................................................................... V
LISTA DE FIGURAS ........................................................................................................................... VI
1 INTRODUÇÃO .............................................................................................................................. 8
1.1 Justificativa ............................................................................................................................ 8
1.2 Objetivo Geral ....................................................................................................................... 9
1.3 Objetivos Específicos ........................................................................................................... 9
1.4 Metodologia ........................................................................................................................... 9
2 REFERENCIAL TEÓRICO ........................................................................................................ 10
2.1 Business Intelligence ......................................................................................................... 10
2.2 BI e Data Warehouse ......................................................................................................... 10
2.3 Fases ETL ........................................................................................................................... 11
2.4 A empresa CEMIGTelecom .............................................................................................. 14
3 RESULTADOS ............................................................................................................................ 15
3.1 Arquitetura do banco de dados transacional do SICOP ............................................... 15
3.2 Arquitetura dimensional criada para o SICOP ............................................................... 18
3.2.1 Levantamento dos Dados ......................................................................................... 18
3.2.2 Modelagem Dimensional e o Data Warehouse ..................................................... 20
3.3 Ferramentas utilizadas e o desenvolvimento ETL ........................................................ 27
3.4 Ferramentas utilizadas e o desenvolvimento do dashboard ....................................... 35
4 CONCLUSÃO ............................................................................................................................. 37
REFERÊNCIAS .................................................................................................................................. 38
8
1 INTRODUÇÃO
De alguns anos até hoje, para empresas a partir de médio porte, somente ter seus
bancos de dados bem organizados e com dados bem saneados não é o suficiente
para manter-se à frente da concorrência. É necessário aprofundar nos dados, estuda-
los, extrair informações e finalmente transformar essas informações em
conhecimento. É aí que se faz imprescindível um projeto de BI (Business Intelligence
ou Inteligência do Negócio) na empresa (Schelegelmilch, 2014). O BI permite que o
corpo decisório entenda seu negócio, e o mercado onde está incluído, e tome decisões
precisas baseadas em informações. No momento da decisão é necessário que o
responsável por ela tenha avaliado previamente o desempenho da empresa, testado
hipóteses e tentado prever possíveis cenários futuros. Traduzindo, é necessário saber
o que aconteceu, como e porque aconteceu e o que ainda pode acontecer (FELD,
2009, BUYTENDIJK, 2009).
Entretanto, para facilitar as análises do negócio de uma empresa, é necessária a
criação e manutenção de um Data Warehouse (DW). Para tanto, os dados, que estão
difundidos em sistemas legados, precisam ser extraídos e copiados para o DW, que
é onde os dados são integrados e consolidados, provendo então uma base única de
informações para o BI. Esse processo de extração de dados e cópia para uma base
unificada é chamado de ETL (Extração, Transformação, Carga – Extract, Transform,
Load) (LANE, 2005).
Sendo assim, objetiva-se neste relatório técnico ilustrar a concepção e execução de
uma aplicação ETL, uma das fases mais importantes de um projeto de BI, para o
módulo do Órgão Implantação do SICOP na CEMIGTelecom, mais especificamente
do controle de Prazos de Obra e Ordem Técnica de Serviço.
1.1 Justificativa
A CEMIGTelecom por atuar em uma área específica do mercado de
telecomunicações, e por estar no modelo de novos negócios de telecomunicações,
precisa manter-se atualizada. Diante desse cenário viu-se a necessidade de um
projeto de Prova de Conceito (POC) de uma aplicação ETL do SICOP. Essa POC visa
demonstrar a facilidade da criação e apuração dos Indicadores de Performance
Corporativa afetando diretamente, e em médio prazo, nos resultados da empresa,
9
bem como trazer uma forma alternativa de gerar conhecimento da empresa para a
própria empresa.
1.2 Objetivo Geral
Propor um sistema de ETL para o Órgão de Implantação da empresa CEMIGTelecom
a partir do sistema proprietário de controle de fluxo, SICOP, afim de atender a um
Indicador de Performance Corporativa.
1.3 Objetivos Específicos
Levantar dados do sistema transacional.
Criar uma modelagem dimensional que atenda o indicador “Prazo Global de
Obras”.
Construir na ferramenta ETL SSIS (SQL Server Integration Services) a carga
do indicador “Prazo Global de Obras”.
Construir um painel para o indicador “Prazo Global de Obras” no SharePoint.
1.4 Metodologia
A metodologia usada neste trabalho classifica-se quanto a sua natureza como uma
pesquisa aplicada, uma vez que objetiva gerar conhecimento para aplicação prática
dirigida à solução de problemas específicos, envolvendo assim verdades e interesses
locais (Freitas, 2013).
Ainda para esse autor, quanto aos objetivos trata-se de uma pesquisa exploratória
sendo que o tema foi orientado através dos objetivos definidos, descobrindo assim um
novo enfoque para o assunto, através do estudo do tema sob diversos ângulos ou
aspectos. Ainda também, classifica-se, quanto ao procedimento, como uma pesquisa
experimental já que foi necessário manipular variáveis relacionadas ao objeto de
estudo.
10
2 REFERENCIAL TEÓRICO
2.1 Business Intelligence
Business Intelligence (BI) é um termo geral que inclui as aplicações, infraestrutura e
ferramentas, e as melhores práticas que permitem acesso a informações e a análise
delas para melhorar e otimizar decisões e performance (GARTNER, 2013).
As tecnologias do BI são capazes de manusear grandes quantidade de dados não
estruturados para identificar, desenvolver e por fim criar novas oportunidades
estratégicas de negócio com uma vantagem competitiva e estabilidade a longo prazo
(RUD, 2009).
O BI pode ser usado para suportar um extenso leque de decisões de negócio, variando
desde o operacional, como alocação de produtos em determinados lugares em uma
loja ou precificação, até o estratégico, como prioridades, metas e direções no mais
alto nível da empresa. Em todo caso, o BI é mais efetivo quando combina dados
externos a empresa (dados do mercado onde a empresa se encaixa) com dados
internos (dados financeiros e operacionais), possibilitando assim a criação de um
cenário completo que gera a “inteligência” do negócio (COKER, 2014).
2.2 BI e Data Warehouse
O data warehouse (DW) é um repositório de dados centralizado, caracterizado por
orientação ao assunto, variante no tempo (versionamento), não volátil e integrado
(INMON, 1996). O ambiente operacional (ou transacional) é projetado com base nas
aplicações e funções da empresa. O DW, por outro lado, é projetado com base nos
principais assuntos da empresa, por isso diz-se que ele é orientado ao assunto
(INMON, HACKATHORN, 1994).
A característica de versionamento do DW se dá porque as informações contidas nele
são referentes à evolução ao longo do tempo, diferentemente dos dados no ambiente
operacional, que representam somente os dados mais recentes, e normalmente são
utilizados em um período da ordem poucos de meses (INMON, 1996).
A não volatilidade é a característica que diz respeito à alta durabilidade da base de
dados, ou seja, o DW permite que os dados sejam armazenados de forma histórica
11
ao longo de qualquer período de tempo que seja conveniente a empresa (INMON,
1996).
A consistência dos dados armazenados é garantida, pois eles são somente inseridos
uma vez no DW e acessados várias, sem sobrescrita e deleções. As operações de
alteração e remoção são realizadas apenas no ambiente transacional. Assim, uma
vez carregados os dados no DW, a consistência é mantida independentemente da
quantidade de acessos ao DW.
Diferentemente dos dados do ambiente operacional, necessários para o dia-a-dia da
empresa, os dados armazenados em um DW são voltados para a análise e tendência,
por isso diz-se que auxiliam no processo de tomada de decisões (KIMBALL, ROSS,
2002).
O DW deve garantir algumas propriedades. Entre elas:
Deve organizar as informações tal que possam ser facilmente acessadas e de
forma consistente.
Deve ser facilmente adaptável.
Deve garantir segurança de dados.
Deve ser essencial para a tomada de decisão. (KIMBALL, ROSS, 2002).
2.3 Fases ETL
As tarefas de uma aplicação ETL já são bem consolidadas no meio da Tecnologia da
Informação (TI) e são o alicerce desse meio. Os dados devem ser compartilhados
entre os vários sistemas de uma empresa, afim de integra-los (LANE, 2005).
As etapas são Extração (extrair os dados de uma ou mais fontes externas),
Transformação (transformar os dados de forma que fiquem organizador e legíveis
para a fase final) e Carga (inserir os dados no DW) (Figura 2).
12
Figura 1 – Fases do BI.
Fonte: Adaptado de LANE (2005).
A primeira fase, que em muitos casos é a mais importante, pois define o sucesso de
todo o projeto de ETL, é onde acontece a extração dos dados provindos de outros
sistemas. A maioria dos projetos de DW consolidam dados de vários sistemas (fontes
externas) e cada um desses sistemas pode ter sua própria forma de organização e
formatação dos dados. Comumente, essas fontes externas são bancos de dados
transacionais e podem variar de um simples arquivo texto até uma base de dados não
relacional ou não estruturada. No geral, o objetivo desta etapa é converter os dados
num único formato, próprio para a próxima etapa, e inseri-los em uma área chamada
Staging Area 1. (PEDERSEN, 2006).
A fase de Transformação aplica uma série de regras e funções nos dados extraídos
para depois serem carregados no DW. Algumas dessas regras e funções são
descritas como (PEDERSEN, 2006):
Selecionar apenas algumas colunas da tabela fonte.
Decifrar códigos em texto.
Mapear abreviações em texto completo.
Criar um campo calculado.
Ordenar.
13
Unir dados de mais de uma tabela.
Agregar.
Criar chaves substitutas.
Transpor linhas em colunas e vice versa.
Separar uma coluna em várias.
Desagregar uma coluna em uma tabela explicativa.
Validar dados em suas fontes.
Criar estruturas para guardar histórico de alterações dos dados.
Em algumas aplicações ETL, os dados depois de transformados são carregados
diretamente no DW. Em outras, os dados são antes inseridos numa área chamada
Staging Area 2.
Enfim a fase de Carga grava os dados transformados na base de dados alvo,
normalmente o DW. Algumas bases de DW podem ser configuradas para serem
sempre sobrescritas, outras podem guardar informações com histórico cumulativo de
mudanças, outras com histórico somente da última alteração. A frequência dessas
atualizações também pode ser variada (diariamente, semanalmente, mensalmente,
sob demanda). Essas definições de como guardar o histórico de alterações e a
frequência das atualizações são decididas de acordo com a necessidade do projeto
de BI (PEDERSEN, 2006) (Figura 2).
Figura 2 – As fases do ETL.
Fonte: Adaptado de PEDERSEN (2006).
14
2.4 A empresa CEMIGTelecom
Criada em 1999 e pertencente ao Grupo CEMIG, a antiga Infovias, hoje
CEMIGTelecom, oferece a maior rede óptica para transporte de serviços de
telecomunicações do estado de Minas Gerais utilizando-se da infraestrutura da
CEMIG.
O modelo de negócios da CEMIGTelecom é o de “CARRIER’s CARRIER”, ou seja,
ela presta seus serviços de telecomunicações através de sua estrutura de redes em
fibras ópticas prioritariamente para as Operadoras de Telecomunicações que desejam
aumentar sua área de atuação dentro do estado de Minas Gerais e estados adjacentes
ou simplesmente desejam atender seus clientes finais sem investir em redes próprias,
optando por alugá-las.
No início das operações da Empresa e até pouco mais de um ano, o controle dos
processos operacionais era feito em uma ferramenta simples, desenvolvida em PHP
e MySQL, chamada PPO. O PPO funcionava como uma planilha eletrônica avançada
e persistente para controlar simplesmente as Obras da CEMIGTelecom. Criou-se
então, em parceria com fábricas de software, o SICOP (Sistema de Controle de
Processos Operacionais) que teve o início de sua operação em janeiro de 2013. A
função do SICOP é facilitar todos os processos operacionais da empresa, desde um
Pedido de Viabilidade no Comercial, passando pelas Soluções de Atendimento (SA)
na Engenharia, Ordem de Serviço (OS), Ordem Técnica de Serviço (OTS), Ordem de
Serviço Interna (OSI), Ativação e Obra na Implantação até o Faturamento de volta no
Comercial (Figura 3).
15
Figura 3 – Processo Operacional da CEMIGTelecom
Fonte: Própria.
3 RESULTADOS
3.1 Arquitetura do banco de dados transacional do SICOP
Todo o levantamento das fontes de dados para a origem do indicador “Prazo Global
de Obras” foi feito a partir das tabelas do banco de dados do sistema SICOP. Para
determinar as informações necessárias foi necessário reunir com a equipe técnica
responsável pelo sistema e assim determinar quais e quantas tabelas seriam
necessárias.
De um universo de 37 tabelas transacionais do sistema SICOP relacionadas direta e
indiretamente ao objetivo do indicador, foram usadas 23 no processo de carga ETL
desse trabalho.
Quanto ao resultado do levantamento com a equipe técnica, pôde-se também
descobrir, que o SICOP tem seu banco de dados transacional desenvolvido no Oracle
(10g R2) e possui 532 tabelas até o momento da criação deste trabalho. As
informações armazenadas no banco variam desde dados simples como Tipo de
Equipamento (Figura 4) até dados georeferenciados (Figura 5) e tabelas complexas
de fluxo do sistema criadas pelo Hibernate (Figura 6).
16
Figura 4 – Exemplo da tabela de Tipos de Equipamento.
Fonte: Própria.
17
Figura 5 – Exemplo da tabela de Pedidos de Viabilidade.
Fonte: Própria.
18
Figura 6 – Exemplo de uma tabela de fluxo do Hibernate
Fonte: Própria.
3.2 Arquitetura dimensional criada para o SICOP
3.2.1 Levantamento dos Dados
Um dos Indicadores de Performance Corporativa que o Órgão de Implantação da
CEMIGTelecom precisa apresentar é o Indicador de Obras. Este indicador é divido
em duas partes
Quantidade de Obras atendidas por Tipo de Atendimento.
Relação entre a meta para atendimento e o tempo real de atendimento.
Anterior ao trabalho feito neste projeto, os dados para criação do relatório que
alimentava o indicador eram gerados a partir de um script escrito em Visual Basic for
Applications (VBA) (Figura 7) e armazenados em uma planilha de Excel (Figura 8).
19
Figura 7 – Parte do script de geração de dados de OS
Fonte: Própria.
Figura 8 – Planilha OS
Fonte: Própria.
20
Tendo conhecimento do script, do resultado que ele gerava e da necessidade do
Órgão de Implantação, foi possível modelar um DW para o indicador conforme
explicado no subitem a seguir.
3.2.2 Modelagem Dimensional e o Data Warehouse
Para construir um DW, é preciso criar um banco de dados em um modelo dimensional
de dados. O modelo dimensional provê métodos que tornam o banco de dados mais
simples e de mais fácil compreensão. Imagina-se um banco de dados dimensional
como um “cubo de três ou mais dimensões”, onde os usuários podem acessar fatias
desse “cubo” em torno de quaisquer dimensões que ele tenha (IBM, 2005).
Um modelo dimensional usa os conceitos de Fatos (medidas) e Dimensões (contexto).
Os Fatos normalmente, mas nem todas as vezes, são valores numéricos os quais
podem ser agregados. As Dimensões são grupos de hierarquias e descrições que
definem os Fatos. Os exemplos mais comuns para Fatos e Dimensões são,
respectivamente, vendas, tempo, produtos, número do registro, número ou nome da
loja. Os modelos dimensionais são construídos em torno dos processos do negócio
da empresa, por exemplo vendas de uma loja específica, inventário, evolução de uma
certa unidade da empresa (KIMBALL, 1997).
O banco de dados dimensional pode ser construído naturalmente por um modelo
chamado star-like schema, ou esquema estrela. Nesse esquema estrela, as
Dimensões cercam o Fato. Para construir esse esquema, é necessário seguir os
seguintes passos (KIMBALL et al, 2008):
1. Escolher o processo de negócio.
Literalmente escolher um processo do negócio da empresa e descreve-lo por
meio de texto ou modela-lo via notações. Por exemplo, o processo da situação
das vendas de um produto em uma filial ou de um conjunto de filiais.
2. Definir o grão (menor dado a ser apresentado).
Para definir o grão, dado o processo de negócio escolhido anteriormente, deve-
se resumi-lo em uma única e menor unidade de medida. É a partir do grão que
as tabelas de Dimensões são criadas. O grão pode ser alterado a medida que
novas informações surgem.
3. Identificar as Dimensões.
21
As Dimensões são a base para a tabela Fato e é onde os dados da Fato são
provindos. É nas Dimensões onde os dados ficam armazenados. Dados como
as características da Filial, da Obra, do Circuito.
4. Identificar o Fato.
Nesse passo é onde são identificados os números que se deseja medir. É no
Fato que ficam os valores que corpo gerencial da empresa precisa para as
tomadas de decisões.
No banco dimensional, são criados dois conceitos: chave natural, ou natural key (NK),
e chave substituta, ou surrogate key (SK). A chave natural é o identificador natural de
um registro que veio do banco de dados transacional. A chave substituta é um
identificador criado para o registro no modelo dimensional e ela passa a ser o
identificador único desse registro no modelo dimensional (Figura 9).
Figura 9 – Esquema Estrela
Fonte: Adaptado de KIMBALL et al (2008).
Uma característica importante das dimensões em um DW é que elas podem
armazenar o histórico da empresa. O nome dessas dimensões que armazenam o
histórico é slowly changing dimensions (SCD). Uma dimensão que não armazena
22
histórico é chamado de Tipo 1. Nela os dados são sobrescritos a cada carga do ETL.
As SCD muito comumente usam um dos dois outros métodos (KIMBALL et all, 2008):
Tipo 2:
Esse método cria na dimensão várias ocorrências, ou tuplas, da mesma chave
natural com os novos atributos do registro. O que varia é a chave substituta.
Outra forma de histórico desse mesmo método é a criação de campos de
“validade” do registro. Um campo de “Data Inicial” e outro de “Data Final”.
Dessa forma continua-se criando várias ocorrências da mesma chave natural,
porém o controle é feito nos novos campos de data.
Tipo 3:
Esse método mantém somente uma ocorrência tanto da chave natural quanto
da chave substituta e armazena o histórico com a criação de duas colunas
chamadas “Valor Original” e outra “Valor Corrente” ou “Valor Válido”. Dessa
forma, toda vez que houver uma nova carga, o valor antigo é copiado no campo
“Valor Original” e o novo valor é guardado no campo “Valor Corrente”. Esse
método é o mais limitado, pois guarda somente a última alteração do campo.
Neste trabalho o DW para o Indicador de Obra foi modelado no SSIS com um
esquema estrela usando uma tabela Fato (Fato Prazos) e quatro tabelas de
Dimensões. As dimensões criadas foram Obra, OS, Endereço e Data. Nenhuma das
dimensões foi definida como slow changing, pois, no cenário escolhido, não há
relevância e nem sentido no armazenamento de histórico de mudanças dos dados,
cabendo essa função somente à tabela de fato.
Seguindo passos definidos acima para criação de um esquema estrela (Figura 10):
1. Escolher o processo de negócio.
Encontrar a quantidade de Obras atendidas por Tipo de Atendimento e a
relação entre a meta para atendimento e o tempo real de atendimento.
2. Definir o grão.
O grão para esse DW é a Obra. Ela é a menor porção de informação necessária
para o Indicador de Obra.
3. Identificar as Dimensões.
As dimensões que compõe a fato para o indicador são as com os dados das
Obras (Figura 11), das Ordens de Serviço (Figura 12), do Endereço (Figura 13)
23
das OS e das Datas (Figura 14) associadas a cada OS. Nessas figuras
apresentou-se a estrutura das dimensões a título de esclarecimento.
4. Identificar o Fato.
As métricas definidas para a Fato Prazos do indicador foram somente três:
quantidade de Obras atendidas por Tipo de Atendimento, a meta para cada tipo
de atendimento (explicada a seguir) e o tempo real de atendimento.
Figura 10 – Modelagem Fato Prazos em Esquema Estrela
Fonte: Própria.
24
Figura 11 – Dimensão Obra
Fonte: Própria.
Figura 12 – Dimensão OS
Fonte: Própria.
25
Figura 13 – Dimensão Endereço
Fonte: Própria.
Figura 14 – Dimensão Datas
Fonte: Própria.
26
Figura 15 – Fato Prazos
Fonte: Própria.
A métrica “Meta Duração OS” é calculada tomando como base o endereço da OS.
Se a OS pertencer à Região Metropolitana de Belo Horizonte (RMBH) a meta é
menor do que se a OS for fora da RMBH. A seguinte tabela explica o cálculo da
meta (Tabela 1):
Tabela 1 – Metas de Atendimento (em dias)
METAS DE ATENDIMENTO (em dias)
Tipo Atendimento RMBH Não RMBH
Abordado 20 25
Drop 20 35
Acima de 600m 35 40
Fonte: Própria.
As definições de Tipos de Atendimento são:
Abordado:
Quando o endereço do cliente já tem um cabo de fibra óptica instalado, ou
atendendo ao próximo cliente, ou a um vizinho em outro andar, por exemplo.
Drop (até 600 metros):
Quando o ponto da rede da CEMIGTelecom existente de onde é lançado o
cabo óptico está a até seiscentos metros do cliente. É um atendimento mais
complexo que o anterior, mas mais simples que o próximo.
Acima de 600 metros:
27
Semelhante ao anterior, porém a distância para o atendimento é superior a
seiscentos metros. É o atendimento mais complexo e também o mais custoso,
por isso a meta maior.
A métrica “Duração OS” é dada por um simples cálculo da diferença entre a data de
recebimento da OS e a da data encerramento da OS.
A métrica “Quantidade de Obras atendidas por Tipo de Atendimento” é executada
em tempo real e só existe no momento de visualização do dashboard.
3.3 Ferramentas utilizadas e o desenvolvimento ETL
Foi utilizado o SQL Developer para consultas simples ao banco transacional do SICOP
e para pesquisas dos índices e relacionamentos entre as tabelas. O SQL Developer é
uma ferramenta gráfica gratuita da Oracle para aumentar a produtividade e simplificar
as tarefas de desenvolvimento relacionadas a banco. Com ela é possível visualizar os
objetos de um banco, executar scripts SQL, editar e depurar comandos PL/SQL,
manipular e exportar dados, e criar e visualizar relatórios.
A arquitetura do ambiente de desenvolvimento do BI foi uma máquina virtual com
Windows Server 2012 Standard (64 bits) com 8 GB de memória RAM e 80 GB de
armazenamento. Neste ambiente foi instalado o SQL Server 2012 SP2, SQL Server
Management Studio, Microsoft Visual Studio com os seguintes suplementos para BI
do SQL Server: SQL Server Data Tools (SSDT), SQL Server Integration Services
(SSIS), SQL Server Analysis Services (SSAS) e SQL Server Reporting Services
(SSRS). Por fim foi utilizada a integração do SSRS com o recém implantado
SharePoint para geração de um dashboard, ou painel, para visualização do DW
modelo (definido no item 3.2.2 deste trabalho).
O Microsoft Visual Studio é um IDE (Integrated Development Environment – Ambiente
de Desenvolvimento Integrado) usado para desenvolver soluções para ambientes
Microsoft assim como web sites, aplicações web e web services. Ele funciona com
ferramentas integradas e permite a adição de novos módulos, como as interfaces dos
suplementos de BI SSDT, SSIS, SSAS e SSRS provindos da instalação do SQL
Server. Neste projeto, o Visual Studio foi o ambiente mais utilizado, pois nele foi
centrado todo o desenvolvimento do ETL (pelo módulo do SSIS, descrito abaixo),
28
desde as transformações para as Staging Areas 1 e 2 (STG1 e STG2) até a carga
final do DW.
Para o armazenamento dos dados dos estágios do ETL e do DW, foi utilizado o banco
de dados do SQL Server. O SQL Server é o Sistema Gerenciador de Bancos de Dados
(SGBD) da Microsoft. Ele foi concebido para suportar diferentes tipos de carga,
variando desde pequenas aplicações em estações locais até sistemas de larga escala
com controle de concorrência de usuários. O SGBD suporta, além de ANSI SQL, uma
linguagem de script própria chamada Transaction SQL (T-SQL) (MSDN, 2014a).
SQL Server Integration Services (SSIS): É o serviço onde é feito o ETL. O SSIS tem
as ferramentas gráficas, integrado ao Visual Studio, para construir o fluxo de extração
dos dados de várias fontes, consultas de dados, transformações (incluindo
agregrações, deduplicação, agrupamento) e exportação para o DW (MSDN, 2014b).
O SSIS foi utilizado para a criação de fato do ETL. Nele foi criada a conexão com a
base Oracle transacional do SICOP. Dessa conexão foram carregadas as primeiras
tabelas para a criação da STG1 (Figura 16).
Figura 16 – SSIS – ETL – Parte do Projeto Geral
Fonte: Própria.
29
Depois de carregadas as tabelas, foi necessário criar um dataflow (fluxo de dados) de
cada tabela. No dataflow (Figura 17) é onde de fato uma tabela é escolhida a partir da
fonte de dados definida previamente no projeto do SSIS (Figura 18). É nele também
onde acontecem as primeiras transformações dos dados, por exemplo uma conversão
de número para texto, uma tradução de um código para um texto descritivo (Figura
19) ou um agrupamento de dois campos de tabelas diferentes para um só em uma
tabela destino (lookup) (Figura 20).Depois dos dados transformados é o momento de
carregar os “novos” dados em uma tabela do DW com uma nova estrutura,
normalmente já em formato mais amigável para o usuário final. Essa carga é feita
ainda no mesmo dataflow (Figura 21).
Figura 17 – SSIS – ETL – Detalhe Dataflow Obra
Fonte: Própria.
30
Figura 18 – SSIS – ETL – Detalhe Obra 2 – Data Source
Fonte: Própria.
31
Figura 19 – SSIS – ETL – Detalhe Obra 3 – Data Convertion
Fonte: Própria.
32
Figura 20 – SSIS – ETL – Detalhe Obra 4 – Lookup
Fonte: Própria.
33
Figura 21 – SSIS – ETL – Detalhe Obra 5 – Carga DM
Fonte: Própria.
Ao final de todos os dataflows de todas as tabelas envolvidas, o resultado é a STG2
(Figura 22). No caso deste projeto a STG2 coincide com o DW. O DW será utilizado
na criação do painel de indicadores do usuário final. Como exemplo estão
demonstradas a tabela Obra (Figura 23) com as transformações dos nomes dos
Clientes, Tipo de Obra e Nome do Empreiteiro e a tabela POP (Figura 24) com a
transformação do Endereço.
34
Figura 22 – Parte da Staging Area 2
Fonte: Própria.
Figura 23 – STG 2 – Tabela Obra Transformada
Fonte: Própria.
35
Figura 24 – STG 2 – Tabela POP Transformada
Fonte: Própria.
3.4 Ferramentas utilizadas e o desenvolvimento do dashboard
O SharePoint é um framework e uma plataforma para desenvolvimento web, criada
pela Microsoft. Esse framework, a princípio, existe para integrar gerenciamento de
conteúdo de intranet e gerenciamento eletrônico de documentos (GED), porém suas
capacidades excedem essas duas funcionalidades (OLESON, 2011). Entre elas estão
a criação de portais de intranet, colaboração, redes sociais, extranets (sites externos)
e BI. A plataforma envolve um conjunto de tecnologias web, sustentado por uma
estrutura central. Por padrão o SharePoint tem uma interface parecida com a do
Microsoft Office e é muito bem integrada com toda a solução Microsoft Office de forma
que suas ferramentas web são facilmente manipuladas por usuários não técnicos
(GILBERT et al, 2011).
A integração com o SharePoint foi feita usando os recursos básicos do SQL Server
Reporting Services (SSRS). O SSRS é um ambiente de geração de relatórios
administrado por uma interface web. Por ele foi criada a interface para a geração do
dashboard, ou painel (MSDN, 2014d).
36
Com as ferramentas disponibilizadas pela plataforma, e, finalizada a fase ETL
(carregamento dos dados), foi criado, juntamente com o usuário final o dashboard
para visualização dos indicadores criados. (Figura 25).
Figura 25 – Dashboard do Usuário Final
Fonte: Própria.
37
4 CONCLUSÃO
Este relatório técnico ilustrou a concepção e execução de uma aplicação ETL para o
módulo do Órgão Implantação do SICOP na CEMIGTelecom, mais especificamente
do controle de Prazos de Obra e Ordem de Serviço.
A empresa ainda precisa amadurecer nos conceitos de Business Intelligence em
geral, principalmente o corpo gerencial de forma que seja possível, futuramente, criar
um projeto de BI que de fato agregue valor ao negócio através dos indicadores de
performance corporativa.
Por outro lado, como Prova de Conceito, o projeto foi bem sucedido e os usuários que
participaram do desenvolvimento perceberam os benefícios que um BI pode dar ao
negócio.
38
REFERÊNCIAS
BIERE, Mike. The New Era of Enterprise Business Intelligence: Using Analytics to Achieve a Global Competitive Advantage. 2010.
BUYTENDIJK, Frank; LANDRY, David. BI Optimization: Building a Better Business Case for Business Intelligence. Redwood Shores, CA: Oracle Whitepaper. 2009.
COKER, Frank. Pulse: Understanding the Vital Signs of Your Business. Ambient Light Publishing. p. 41-42. 2014.
FELD, Charlie. Blind Spot: A Leader’s Guide to IT – Enable Business Transformation. Olive Press 2009.
FREITAS, Ernani Cesar de; PRODANOV, Cleber Cristiano. Metodologia do Trabalho Científico: Métodos e Técnicas da Pesquisa e do Trabalho Acadêmico. 2ª Edição. 2013.
GARTNER. Gartner IT Glossary. Disponível em (http://www.gartner.com/it-glossary/business-intelligence-bi/). 2013.
GILBERT, Mark R.; SHEGDA, Karen M.; PHIFER, Gene; MANN, Jeffrey. SharePoint 2010 Is Poised for Broader Enterprise Adoption. Gartner. 2011.
IBM. IBM Concepts of Dimensional Data Modeling. Disponível em (http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ddi.doc/ddi222.htm). 2005.
INMON, William Harvey; HACKATHORN, Richard D. Using the Data warehouse. 1a Ed. USA: Wiley, 1994.
INMON, William Harvey. Building the Data Warehouse. 2a Ed. USA: Wiley, 1996.
KIMBALL, Ralph; ROSS, Margy. The data warehouse toolkit: the complete guide to dimensional modeling. 2a Ed. USA: Wiley, 2002.
KIMBALL, Ralph. A Dimensional Modeling Manifesto. Disponível em (http://www.kimballgroup.com/1997/08/a-dimensional-modeling-manifesto). 1997.
KIMBALL, Ralph. ROSS, Margy. THORNTHWAITE, Warren. MUNDY, Joy. The Data Warehouse Lifecycle Toolkit: Expert Methods for Designing, Developing, and Deploying Data Warehouses. Segunda Edição. Wiley. 2008.
LANE, Paul. Oralce Database Data Warehousing Guide. 2005.
MSDN a. Microsoft SQL Server. Disponível em (http://msdn.microsoft.com/en-us/library/bb545450.aspx). 2014.
MSDN b. Overview (Integration Services). Disponível em (http://msdn.microsoft.com/en-us/library/ms141263.aspx). 2014.
MSDN c. Analysis Service Architecture. Disponível em (http://msdn.microsoft.com/en-us/library/ms174918.aspx). 2014.
39
MSDN d. Reporting Services. Disponível em (http://msdn.microsoft.com/en-us/library/ms159106.aspx). 2014.
OLESON, Joel. 7 Years of SharePoint - A History Lesson. Joel Oleson's Blog - SharePoint Land (Microsoft Corporation). MSDN Blogs. 2011.
PEDERSEN, Torben Bach. Extract, Transform, Load (ETL). 2006.
RUD, Olivia. Business Intelligence Success Factors: Tools for Aligning Your Business in the Global Economy. Hoboken, N.J: Wiley & Sons. 2009.
SCHELEGELMILCH, Jeffrey; ALBANESE, Joseph. Applying Business Intelligence Innovations to Emergency Management. Journal of Business Continuity & Emergency Planning. 2014.
