Uso de um Processo ETL em um Modelo Data

USO DE UM PROCESSO ETL EM UM MODELO DATA
WAREHOUSE PARA A GERAÇÃO DE DASHBOARDS DE
INDICADORES DE REDES DE TELEFONIA CELULAR
Antonio Luiz Bonna de Lyra
Projeto de Graduação apresentado ao Corpo
Docente do Departamento 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
à obtenção do título de Engenheiro Eletrônico
e de Computação.
Orientador: Flávio Luis de Mello
Rio de Janeiro
Agosto de 2016
USO DE UM PROCESSO ETL EM UM MODELO DATA
WAREHOUSE PARA A GERAÇÃO DE DASHBOARDS DE
INDICADORES DE REDES DE TELEFONIA CELULAR
Antonio Luiz Bonna de Lyra
PROJETO DE GRADUAÇÃO SUBMETIDO AO CORPO DOCENTE DO
DEPARTAMENTO 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:
Antonio Luiz Bonna de Lyra
Orientador:
Prof. Flávio Luis de Mello, D.Sc.
Examinador:
Prof. Heraldo Luís Silveira de Almeida, D.Sc.
Examinador:
Prof. Ricardo Rhomberg Martins, D.Sc.
RIO DE JANEIRO, RJ – BRASIL
AGOSTO DE 2016
Bonna de Lyra, Antonio Luiz
Uso de um Processo ETL em um Modelo Data
Warehouse para a Geração de Dashboards de Indicadores
de Redes de Telefonia Celular / Antonio Luiz Bonna de
Lyra. – Rio de Janeiro: UFRJ/Escola Politécnica, 2016.
XVI, 90 p.: il.; 29, 7cm.
Orientador: Flávio Luis de Mello
Projeto de Graduação – UFRJ/Escola Politécnica/
Departamento de Engenharia Eletrônica e de Computação,
2016.
Referências Bibliográficas: p. 62 – 66.
1. ETL. 2. Banco de Dados. 3. Data Warehouse.
4. Redes de Telefonia.
5. KPI. 6. Dashboard.
I.
Luis de Mello, Flávio. II. Universidade Federal do Rio de
Janeiro, Escola Politécnica, Departamento de Engenharia
Eletrônica e de Computação. III. Uso de um Processo
ETL em um Modelo Data Warehouse para a Geração de
Dashboards de Indicadores de Redes de Telefonia Celular.
iii
UNIVERSIDADE FEDERAL DO RIO DE JANEIRO
Escola Politécnica - Departamento de Eletrônica e de Computação
Centro de Tecnologia, bloco H, sala H-212, 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).
iv
A todos os professores e
funcionários da UFRJ.
v
AGRADECIMENTOS
Agradeço a equipe da Huawei NPM, principalmente aos engenheiros Carlos Eduardo Covas Costa e Eduardo Martins Montenegro, pela ajuda, ensinamentos e amizade durante todo o período em que fiz meu estágio na empresa, onde tive a oportunidade de desenvolver a ferramenta que apresento neste trabalho; aos meus chefes
Eng. Bruno Leonardo Barbosa de Oliveira e Eng. Henrique Amaral Omena, que
coordenaram o desenvolvimento da ferramenta na Huawei.
Agradeço a todos os professores do curso de Engenharia Eletrônica e de Computação da UFRJ que contribuíram para minha formação acadêmica, em especial
ao meu orientador acadêmico e coordenador de curso Carlos José Ribas D’Avila, e
aos professores Heraldo Luís Silveira de Almeida e Ricardo Rhomberg Martins que
aceitaram o convite de participação na banca de defesa deste projeto de graduação.
E, finalmente, ao professor Flávio Mello que aceitou me orientar nesse trabalho.
vi
Resumo do Projeto de Graduação apresentado à Escola Politécnica/UFRJ como
parte dos requisitos necessários para a obtenção do grau de Engenheiro Eletrônico
e de Computação
USO DE UM PROCESSO ETL EM UM MODELO DATA WAREHOUSE PARA
A GERAÇÃO DE DASHBOARDS DE INDICADORES DE REDES DE
TELEFONIA CELULAR
Antonio Luiz Bonna de Lyra
Agosto/2016
Orientador: Flávio Luis de Mello
Departamento: Engenharia Eletrônica e de Computação
RESUMO
Este trabalho destina-se em desenvolver uma ferramenta capaz coletar dados das
redes de telefonia 3G e 4G e gerar relatórios com os indicadores de performance
da rede com menor atraso de tempo possível. A intenção foi desenvolver um
processo ETL (Extract, Transform, Load ) capaz de extrair dados dos servidores
da operadora, transformá-los e carregá-los em um Data Warehouse. Inicialmente
foram feitos testes para escolher quais tecnologias a serem utilizadas. Esses
testes foram de suma importância para garantir que o processo ocorra de forma
rápida, com poucos erros e com menos atraso. Neste sentido, foram escolhidas diferentes ferramentas para cada etapa do processo. A partir de então, os
dados já carregados no Data Warehouse foram processados para cálculos dos
principais indicadores das redes de telefonia, e depois agrupados em maiores
níveis de granularidade. Por fim, foi utilizada uma aplicação web para extrair
esses dados do Date Warehouse para gerar dashboards em uma interface web, de
modo que o usuário possa analisar esses dados através de diversos tipos de consultas.
Palavras-Chave: ETL, Banco de Dados, Data Warehouse, KPI, 3G, 4G, dashboard.
vii
Abstract of Graduation Project teste presented to POLI/UFRJ as a partial
fulfillment of the requirements for the degree of Electronic and Computer Engineer
AN ETL PROCESS IN DATA WAREHOUSE TO GENERATE KPI
DASHBOARDS FOR MOBILE NETWORKS
Antonio Luiz Bonna de Lyra
August/2016
Advisor: Flávio Luis de Mello
Department: Electronic and Computer Engineering
ABSTRACT
This work aims to develop a tool that can collect data from 3G and 4G mobile
networks and generate reports with the network performance indicators with
less delay as long as possible. The intention was to develop an ETL (Extract,
Transform, Load) process capable of extracting data from the enterprise servers,
transform and load it in a Data Warehouse. Initially tests were made to choose
which technologies to be used. These tests were of very importance to guarantee
that the process occurs as fast as possible, with fewer errors and less delay. In
this sense, they were chosen different tools for each step of the process. With the
data already loaded in the Data Warehouse, KPIs (Key Performance Indicator) of
the mobile networks are calculated and grouped into higher levels of granularity.
Finally, a web application extract data from the Data Warehouse to generate
dashboards on a web interface, then the user can analyze the data from different
kinds of views.
Key-words: ETL, Database, Data Warehouse, KPI, 3G, 4G, dashboard.
viii
Sumário
Lista de Figuras
xi
Lista de Tabelas
xiii
Lista de Abreviaturas e Siglas
xiv
1 Introdução
1.1 Tema . . . .
1.2 Delimitação
1.3 Justificativa
1.4 Objetivos .
1.5 Metodologia
1.6 Descrição .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
2 Fundamentação Teórica
2.1 Tecnologias de telefonia móvel . . . . .
2.1.1 Breve histórico sobre a telefonia
2.1.2 Cenário atual . . . . . . . . . .
2.1.3 Características do UMTS . . . .
2.1.4 Características do LTE . . . . .
2.2 Conceitos de KPI . . . . . . . . . . . .
2.3 Conceitos de BI . . . . . . . . . . . . .
2.3.1 ETL . . . . . . . . . . . . . . .
2.3.2 Data Warehouse . . . . . . . .
2.3.3 Data Marts . . . . . . . . . . .
2.3.4 Dashboard . . . . . . . . . . . .
3 Solução implementada
3.1 Arquitetura do Sistema . . . . .
3.1.1 Servidores Externos . . .
3.1.2 Implementação do ETL
3.1.3 Aplicação MVC . . . . .
.
.
.
.
ix
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
. . . .
móvel
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
. . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
1
1
1
2
2
3
4
.
.
.
.
.
.
.
.
.
.
.
5
5
5
6
8
11
12
18
19
20
21
22
.
.
.
.
23
23
24
27
37
3.2
Materiais e Métodos . . . . .
3.2.1 Infra-estrutura . . . .
3.2.2 Ferramentas utilizadas
3.2.3 Funcionamento . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
4 Resultados e Discussões
4.1 KPIs básicos para tecnologia UMTS . . . . .
4.2 Principais ofensores por RNC . . . . . . . .
4.3 KPIs personalizados para tecnologia UMTS
4.4 KPIs básicos para tecnologia LTE . . . . . .
4.5 Discussões . . . . . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
39
39
39
48
.
.
.
.
.
50
53
55
56
57
57
5 Conclusões e Trabalhos Futuros
59
5.1 Conclusões . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
5.2 Trabalhos futuros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Referências Bibliográficas
62
A Códigos Fonte - ETL
A.1 Extração . . . . . . . . . . . . .
A.1.1 Ftp.py . . . . . . . . . .
A.1.2 Auto-get-performance.sh
A.2 Conversão . . . . . . . . . . . .
A.2.1 pm-convert.sh . . . . . .
A.2.2 auto-convert.sh . . . . .
A.2.3 parser.py . . . . . . . . .
A.2.4 auto-parse.sh . . . . . .
A.3 Carga . . . . . . . . . . . . . .
A.3.1 staging.sh . . . . . . . .
A.3.2 auto-staging.sh . . . . .
.
.
.
.
.
.
.
.
.
.
.
67
68
68
70
71
71
73
74
77
78
78
82
.
.
.
.
.
.
.
83
84
84
85
86
87
87
89
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
B Códigos Fonte - Processamento
B.1 Cálculo dos KPIs de Acessibilidade . . . . . . . . . . . . . . . . .
B.1.1 View vw_acessibility.sql . . . . . . . . . . . . . . . . . . .
B.1.2 Função PL/SQL inserir_kpi_accessibility.sql . . . . . . . .
B.1.3 inserir_kpi_accessibility.sh . . . . . . . . . . . . . . . . .
B.2 Cálculo dos ofensores por RNC dos KPIs de Acessibilidade . . . .
B.2.1 Função PL/SQL inserir_worst_cells_rnc_accessibility.sql
B.2.2 inserir_worst_cells_rnc_accessibility.sh . . . . . . . . . .
x
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Lista de Figuras
2.1
2.2
2.3
.
Números de celulares no mundo ano a ano, em bilhões. Fonte: Teleco [2]. .
Evolução dos padrões de telefonia móvel 3GPP. Fonte: 5G Americas [1].
Projeção do números de assinantes globais em cada tecnologia, em milhões.
. . . . . . . . . . . . . . . . . . . . . . .
Projeção para os acessos móveis no Brasil. Fonte: 5G Americas [4]. .
Divisão lógica da rede móvel em rede de acesso e núcleo de rede. . .
Topologia UMTS. Fonte: Qualcomm Wireless Academy [5]. . . . . .
Fonte: 5G Americas [3].
2.4
2.5
2.6
2.7
6
7
.
.
.
.
.
.
.
.
. 7
. 8
. 9
. 10
UTRAN - UMTS Terrestrial Radio Access Network. Fonte: Qualcomm
Wireless Academy [5]. . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
2.8
2.9
2.10
2.11
2.12
2.13
2.14
2.15
2.16
Core Network Fonte: Qualcomm Wireless Academy [5]. . . . . . . . . . . 11
3.1
3.2
3.3
3.4
3.5
Arquitetura do Sistema. . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Arquitetura E-UTRAN . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Valores típicos de RTWP. Fonte: telecomHall [6] . . . . . . . . . . . . . . 15
Softer Handover. Fonte: Teleco [7] . . . . . . . . . . . . . . . . . . . . . 15
Soft Handover. Fonte: Teleco [7] . . . . . . . . . . . . . . . . . . . . . . 16
. . . . . .
Inter-RAT Hard Handover. Fonte: Teleco [7] .
Arquitetura tecnológica de um BI. [8] . . . .
Estrutura do ETL. [9] . . . . . . . . . . . .
Hard Handover. Fonte: Teleco [7]
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
16
17
19
19
Estrutura do EMS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Diagrama de Fluxos do Processo de ETL. . . . . . . . . . . . . . . . . . 28
Exemplo de um arquivo XML contendo uma família com contadores de RRC. 30
Objeto de um arquivo XML contendo o nome do RNC, e o nome e identificador de uma célula.
3.6
.
.
.
.
. . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Objeto de um arquivo XML da rede LTE contendo o nome e identificador
de um eNode-B, e o nome e identificador de uma célula. . . . . . . . . . . 31
3.7
3.8
3.9
3.10
Tempo de carga em uma base de dados vazia, em segundos. . . . . . . . . 32
. . . . 32
Arquivo do exemplo anterior após a conversão para uma planilha em CSV. 32
Arquivo do exemplo anterior após o parser. . . . . . . . . . . . . . . . . 33
Tempo de carga em uma base de dados já populada, em segundos.
xi
3.11
3.12
3.13
3.14
3.15
3.16
Volume de dados por arquivo (MB). . . . . . . . . . . . . . . . . . . . . 35
Diagrama de Fluxos do Processamento dos Dados. . . . . . . . . . . . . . 36
. . . . . . . . . . . . . 38
Diagrama de um processador XSLT. . . . . . . . . . . . . . . . . . . . . 41
Arquivo DTD usado para validar um arquivo XML. . . . . . . . . . . . . 42
Arquitetua MVC. Fonte: CodeIgniter Brasil [10].
Tempo de carga de 4 GB de dados, em segundos. Fonte: página oficial do
pg_bulkload [11]. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
3.17 Tempo de carga de 1 GB de dados para diferentes métodos de inserção, em
minutos. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
3.18 Monitoramento do ETL . . . . . . . . . . . . . . . . . . . . . . . . . . 48
3.19 Monitoramento dos KPIs . . . . . . . . . . . . . . . . . . . . . . . . . . 48
4.1
4.2
4.3
4.4
4.5
4.6
4.7
4.8
4.9
4.10
4.11
4.12
4.13
4.14
4.15
5.1
Organização do Banco de Dados. . . . . . . . . . . . . . . . . . . . . . . 51
Página inicial da ferramenta. . . . . . . . . . . . . . . . . . . . . . . . . 51
Menu com os indicadores de performance. . . . . . . . . . . . . . . . . . 52
. . . . . . . . . . .
Agregações de KPIs disponíveis. . . . . . . .
KPIs básicos de Accessibility e Retainability. .
KPIs básicos de Traffic. . . . . . . . . . . .
Tecnologias disponíveis.
KPIs básicos de Service Integrity e Retention.
KPIs básicos de Mobility. . . . . . . . . . . .
KPIs básicos de Availability e Coverage. . . .
Ranqueamento das piores células de um RNC.
.
KPIs personalizados. . . . . . . . . . . .
Piores células para o KPI QDA PS . . . .
KPIs básicos da rede LTE. . . . . . . . .
KPI de uma célula ao longo de um dia.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
52
52
53
53
54
54
54
55
55
56
56
57
Modelo Multidimensional proposto. [8] . . . . . . . . . . . . . . . . . . . 60
xii
Lista de Tabelas
2.1
Principais mercados de celular no mundo, em milhões. Fonte: Teleco [2]. .
8
3.1
3.2
3.3
3.4
RNCs disponíveis em cada um dos servidores do EMS . . . . . . . . . . . 26
Exemplo de uma tabela de uma família de contadores por RNC. . . . . . . 33
. . . . . . . . . . . 34
Exemplo de uma família de contadores do LTE . . . . . . . . . . . . . . 34
Exemplo de uma família com contadores por Node-B
xiii
Lista de Abreviaturas e Siglas
1G
1st Generation, p. 5
2G
2nd Generation, p. 5, 8
3GPP
3rd Generation Partnership Project, p. 5
3G
3rd Generation, p. 1, 2, 4, 5, 8, 9, 23
4G
Fourth Generation, p. 1, 2, 4, 6, 8, 23
API
BI
CRUD
CSV
CS
Application Programming Interface, p. 48
Business Intelligence, p. 4, 18, 39
Create, Read, Update, Delete, p. 38
Comma-separated values, p. 31, 34, 41, 42, 45, 59
Circuit Switched, p. 9–14, 17
CTL
Control Temporal Logic, p. 45
DTD
Document Type Definition, p. 42
E-UTRAN
EDGE
Evolved Universal Terrestrial Radio Access, p. 11
Enhanced Data rates for GSM Evolution, p. 5
EMS
Element Management System, p. 25, 29, 49
EPC
Evolved Packet Core, p. 11
ETL
Extract, Transform, Load, p. 1, 3, 4, 18–20, 23, 27, 31, 39, 45,
48, 49, 58, 59
FTP
File Transfer Protocol, p. 27, 40, 49
GERAN
GGSN
GSM EDGE Radio Access Network, p. 9
Gateway GPRS Support Node, p. 11
xiv
GMSC
Gateway Mobile Switching Center, p. 11
GPRS
General Packet Radio Services, p. 5
GSM
GUI
HSDPA
HSPA
HSUPA
IP
Global System for Mobile Communications, p. 5, 7, 9, 15–17,
59
Graphical User Interface, p. 44
High Speed Downlink Packet Access, p. 6, 14, 18
High Speed Packet Access, p. 6, 9
High Speed Uplink Packet Access, p. 6
Internet Protocol, p. 27
ISDN
Integrated Service Digital Network, p. 11
JSON
JavaScript Object Notation, p. 3
KPI
Key Performance Indicator, p. 1–4, 12, 13, 18, 23, 27, 29, 35,
37, 39, 40, 43, 45, 46, 48–58, 83
LTE
Long Term Evolution, p. 6–8, 11, 17, 25, 29, 31, 34, 35, 37, 50,
57
MSC
Mobile Switching Center, p. 11
MVC
Model, View, Controler, p. 3, 37, 39, 46, 59
OFDM
Orthogonal Frequency Division Multiple Access, p. 11
OLAP
Online Analytical Processing, p. 60
PL/SQL
PSTN
PS
Procedural Language/Structured Query Language, p. 39, 43,
44, 46
Public Switched Telephone Network, p. 11
Packet Switched, p. 9–11, 13, 14, 17
RAB
Radio Access Bearer, p. 13, 14, 27
RAN
Radio Access Network, p. 9
RF
Radio Frequência, p. 1, 10
xv
RNC
Radio Network Controller, p. 10, 12, 14, 15, 25–27, 30, 33, 37,
50, 52, 55, 60
RRC
Radio Resource Control, p. 13, 27, 29
RTWP
SGSN
Received Total Wideband Power, p. 14
Serving GPRS Support Node, p. 11
SMS
Short Message Service, p. 5
SQL
Structured Query Language, p. 2, 43, 44, 49
SSH
Secure Shell, p. 47
UF
UMTS
USIN
UTRAN
Unidade da Federação, p. 27, 57
Universal Mobile Telecommunication System, p. 5, 6, 8, 9, 11,
15–17, 25, 29, 30, 35, 37, 50, 53, 57
Universal Subscriber Identity Module, p. 9
UMTS Terrestrial Radio Access Network, p. 9, 10, 12, 17
VBA
Visual Basic for Applications, p. 2
VPN
Virtual Private Network, p. 49
VoIP
Voice over Internet Protocol, p. 14
WCDMA
Wideband Code Division Multiple Access, p. 8
XML
eXtensible Markup Language, p. 27, 31, 41, 42, 59
XSLT
eXtensible Stylesheet Language for Transformation, p. 41
xvi
Capítulo 1
Introdução
1.1
Tema
O tema do trabalho é o desenvolvimento de uma ferramenta que utiliza o processo de ETL (Extract, Transform, Load - Extração, Transformação, Carga) voltado
para arquitetura de um Data Warehouse (Armazém de Dados) [12] com a finalidade
de gerar dashboards (painel de indicadores) contendo os principais indicadores de
performance de redes de telefonia celular que utilizam as tecnologias 3G (3rd Generation - Terceira Geração) e 4G (Fourth Generation - Quarta Geração). Neste
sentido, pretende-se estudar os seguintes problemas: (1) como extrair dados de fontes diversas de uma empresa de telecomunicações (que fornece serviços para uma
operadora de telefonia celular) e transformá-los de modo a serem carregados em um
Banco de Dados; (2) como esses dados podem ser processados para cálculos dos
KPIs (Key Performance Indicator - Indicador-chave de Desempenho) da rede; (3)
como esses indicadores podem ser apresentados em dashboards por uma interface
web, com menor atraso de tempo possível.
1.2
Delimitação
O desenvolvimento da ferramenta é voltado para o mercado de telecomunicações,
onde os engenheiros de RF (rádio frequência) usam arquivos de performance extraídos dos servidores das empresas para monitoramento ou confecção de relatórios.
O objeto de estudo do trabalho é limitado ao desenvolvimento de uma solução
ETL para um modelo Data Warehouse e geração de dashboards de indicadores em
uma interface web. Não faz parte do escopo do trabalho a análise desses indicadores.
Tampouco o design da interface web, mas sim o modelo da aplicação web que realiza
consultas a um Banco de Dados.
1
1.3
Justificativa
No ramo das telecomunicações atualmente é muito exigido pelas empresas contratantes a elaboração de relatórios técnicos por engenheiros, contendo os diversos
indicadores de performance sobre as redes de telefonia. Além da elaboração dos relatórios, outra tarefa dos engenheiros é o monitoramento da rede e a realização das
otimizações necessárias a fim de melhorar os indicadores. Isso acaba tomando boa
parte do seu tempo, pois primeiro eles têm que extrair manualmente os dados da
rede em forma de planilhas, depois fazer gráficos no Excel, para só depois analisar o
estado da rede, elaborar relatórios e fazer as otimizações. Outra dificuldade é a falta
de padronização dos relatórios, podendo cada engenheiro realizá-los de uma forma
bem diferente.
Com o aumento da quantidade de dispositivos móveis, chegando, inclusive, a mais
de 7,7 bilhões de dispositivos móveis espalhados pelo mundo [13], surgiu a necessidade de expansão de toda rede de telefonia. Com isso, a demanda de engenheiros da
área de telecomunicações é crescente, e a tendência do mercado é juntar os conhecimentos técnicos de engenharia e de Banco de Dados, pois o números de dados a
serem analisados é cada vez maior, ficando cada vez mais inviável o uso de planilhas
e a criação de macros em VBA (Visual Basic for Applications) no Excel.
É seguindo essa tendência que surgiu a ideia desenvolvida na ferramenta deste
trabalho, com a criação de um Banco de Dados contendo, de forma centralizada, as
informações necessárias de uma rede de telefonia celular, para consultas simplificadas
via SQL (Structured Query Language - Linguagem de Consulta Estruturada). Além
do Banco de Dados, uma interface Web poderá gerar relatórios de forma rápida,
dinâmica e customizável através de consultas ao banco. Através dos dashboards da
interface web, o monitoramento da rede pode ser feito de forma constante.
1.4
Objetivos
O objetivo geral do trabalho é desenvolver de uma ferramenta OpenSource (Código
Aberto) capaz de gerar dashboards com os KPIs de redes de telefonia 3G e 4G
através de uma consulta a um Data Warehouse. Desta forma, tem-se como objetivos
específicos:
• Extrair dados dos servidores externos de uma empresa de telecomunicações,
contendo os arquivos necessários para análise das redes 3G e 4G
• Realizar as transformações nos arquivos para carregá-los em um Banco de
Dados
2
• Calcular os KPIs da rede de telefonia
• Fazer agregações dos KPIs por datas (dia e semana) e por outros parâmetros
de acordo com a tecnologia da rede em questão, como por RNC, Node-B,
eNodeb, cidade, etc para obter um nível maior de granularidade dos dados, e
calcular os principais ofensores de cada KPI para cada granularidade
• Gerar tabelas e gráficos em uma interface web com os indicadores calculados
1.5
Metodologia
Para o desenvolvimento do trabalho, foi consultada a documentação oficial da
empresa de telecomunicações [14] que fornece os serviços a uma operadora de redes de
telefonia, com o objetivo de saber quais os principais KPIs usados, quais as fórmulas
associadas para o cálculo de cada KPI e quais contadores específicos precisam ser
extraídos dos servidores da empresa para realizar os cálculos.
O passo seguinte foi a criação de um Banco de Dados em um servidor próprio,
com tabelas e campos no mesmo formato dos arquivos de performance e configuração presentes nos servidores da empresa. As tabelas foram divididas em schemas
(esquemas), sendo indexadas nos campos onde são realizadas a maior parte das consultas e adicionadas constraints (restrições) para persistência dos dados, seguindo a
modelagem relacional. [12]
Outro servidor de arquivos foi usado para o primeiro passo do ETL, onde arquivos
de performance e configuração são extraídos dos servidores externos e armazenados
em disco, e onde esses arquivos são transformados para o mesmo formato das tabelas
correspondentes no Banco de Dados. A última etapa do ETL foi realizada no servidor do Banco de Dados, ocorrendo a carga dos dados e processamento para cálculos
dos KPIs, agregação dos dados para obter maior nível de granularidade e cálculo
dos principais ofensores da rede. Todas essas etapas do ETL foram agendadas para
serem executadas variando de um minuto a duas horas.
O último passo foi o desenvolvimento de uma aplicação web em um servidor
utilizando a arquitetura MVC (Model, View, Controler - Modelo, Visão, Controle)
[15]. Assim foi possível separar a camada de acesso aos dados do Data Warehouse
(Model ) da interface (View ). Alguns arquivos no formato JSON (JavaScript Object
Notation) também foram gerados para diminuir os acessos ao Banco de Dados.
3
Com isso, o presente trabalho reúne conceitos de telecomunicações, onde serão
estudados como são estruturadas as redes de telefonia móvel que usam as tecnologias
UMTS (3G) e LTE (4G) e quais seus principais indicadores utilizados para análise de
performance da rede e satisfação do usuário. Reúne também conceitos de Banco de
Dados e ETL, onde um grande volume de dados será armazenado e processado. Por
fim reúne o conceito de aplicação web, onde uma interface web irá gerar dashboards
através de consultas ao Banco de Dados.
1.6
Descrição
No capítulo 2 será feita a fundamentação teórica do trabalho, começando com
um breve histórico sobre a telefonia celular e descrevendo as características das
UMTS e LTE. Após serão apresentados os conceitos básicos de KPI, ressaltando os
principais indicadores utilizados para análise de redes de telecomunicações. Por fim
serão apresentados os principais conceitos de BI (Business Intelligence - Inteligência
de Negócios) para o entendimento do tema abordado no trabalho.
O capítulo 3 apresenta a solução proposta e implementada. Será detalhada cada
etapa do trabalho dando alguns exemplos práticos do uso do processo ETL. Neste capítulo também serão apresentadas as ferramentas e tecnologias utilizadas e o porquê
de cada uma dessas escolhas.
Os resultados são apresentados no capítulo 4. Nele será apresentada a interface
web do trabalho, o menu com as opções disponíveis e alguns exemplos de gráficos e
tabelas que podem ser gerados com a ferramenta desenvolvida.
As conclusões e sugestões para trabalhos futuros se encontram no capítulo 5.
4
Capítulo 2
Fundamentação Teórica
2.1
2.1.1
Tecnologias de telefonia móvel
Breve histórico sobre a telefonia móvel
Os telefones móveis analógicos começaram a ser utilizados na década de 80, e são
classificados como de primeira geração, 1G (1st Generation). Gradativamente esses
foram substituídos pelos de segunda geração, 2G (2nd Generation), que utilizam
a tecnologia GSM (Global System for Mobile Communications - Sistema Global
para Comunicações Móveis) e técnicas digitais. Estes sistemas foram desenvolvidos
para suportar comunicações de voz, porém, é possível também enviar pequenas
mensagens de texto (SMS - Short Message Service - Serviço de mensagens curtas)
entre os dispositivos da rede. [16, 17]
Os sistemas 2G evoluíram para permitir que os usuários acessassem a Internet a
partir de seus aparelhos. Estes sistemas ficaram conhecidos como sistemas de segunda geração e meia (2,5G). Dentre os sistemas 2,5G, destacam-se o GPRS (General
Packet Radio Services - Serviços Gerais de Pacote por Rádio) e o EDGE (Enhanced
Data rates for GSM Evolution - Taxas de Dados Ampliadas para a Evolução do
GSM), ambos são evoluções do GSM. No GPRS, um usuário alcança uma taxa de
pico, para transmissão de dados, de 140 Kbps, enquanto no EDGE esta taxa chega
a 384 Kbps. [16]
Entretanto, as taxas atingidas por esses sistemas não eram suficientes para atender
as novas demandas de comunicação. Em dezembro de 1998, foi criada a 3GPP (3rd
Generation Partnership Project), uma organização global de comunicações sem fio
que trabalha em colaboração para desenvolver normas e especificações para tecnologias de rádio e arquiteturas de serviço. Como evolução das redes GPRS e EDGE,
o UMTS (Universal Mobile Telecommunication System - Sistema Universal de Tele5
comunicações Móveis) foi proposto e apresentado na Release-99 (R99) da 3GPP no
ano de 2000, para ser uma solução integrada para aspectos de transmissão de voz e
dados na telefonia 3G.
Com o rápido avanço do UMTS, uma nova tecnologia foi incorporada na Release2005 (R5), oferecendo maiores taxas de download. Desta forma, o sistema também
recebeu o nome de High Speed Downlink Packet Access (HSDPA). Por conseguinte,
foi incorporada na Release-2006 (R6) a tecnologia HSUPA , que permitiu maiores
taxas de upload. Quando as tecnologias HSDPA e HSUPA são implementadas juntas
em uma rede, são comumente referidos como HSPA (High Speed Packet Access). [1]
A evolução do HSPA veio na Release 7, chamado de HSPA+. Foi na Realease 8 que
foi introduzida a quarta geração de tecnologia móvel (4G), a tecnologia LTE (Long
Term Evolution), com o objetivo de oferecer velocidades maiores na transmissão de
dados. A Figura 2.1 mostra o gráfico da evolução das tecnologias 3GPP durante
os anos, incluindo o LTE-Advance, proposto na Release 10 e que propôs melhorias
tecnológicas para o LTE.
Figura 2.1: Evolução dos padrões de telefonia móvel 3GPP. Fonte: 5G Americas [1].
2.1.2
Cenário atual
A maior parte dos habitantes do mundo já possui acesso à telefonia móvel. Conforme dito na seção 1.3, o número de dispositivos móveis já ultrapassou a marca de
7 bilhões desde 2015, conforme ilustra a Figura 2.2. Além disso, deve-se considerar que o número de usuários com dois ou mais dispositivos (podendo ser celulares,
tablets, notebooks, etc) está se tornando cada vez mais comum. Isto sugere que o
número de dispositivos móveis irá ultrapassar o número de habitantes no mundo.1
Atualmente (agosto de 2016), o número de habitantes no mundo passou a marca de 7,4 bilhões
de pessoas.[18]
1
6
Figura 2.2: Números de dispositivos móveis no mundo ano a ano, em bilhões. Fonte:
Teleco [2].
Os recursos de comunicações móveis são, na grande maioria, destinados a serviços
de envio de mensagens de texto (SMS), chamadas de voz e acesso básico a rede
de dados (Internet). Neste cenário das comunicações móveis, o LTE vem sendo
adotado como o próximo padrão de telefonia móvel pela maior parte das operadoras
de telefonia celular. A Figura 2.3 mostra que, apesar de atualmente a maioria dos
usuários de dispositivos móveis no mundo serem assinantes da tecnologia GSM, a
tendência é que até 2020 a maioria utilize a tecnologia LTE.
Figura 2.3: Projeção do números de assinantes globais em cada tecnologia, em milhões.
Fonte: 5G Americas [3].
7
No Brasil, também é visível o crescimento acelerado das comunicações móveis. A
tecnologia LTE vem surgindo para suprir a demanda dos usuários por serviços móveis
sempre com taxas de transmissão de dados cada vez maiores, pois as tecnologias
2,5G e 3G utilizadas pelas operadoras brasileiras não conseguem oferecer serviços
de qualidade aos clientes em função das limitações de taxa de transferência de dados.
Atualmente o Brasil é o quinto maior mercado do mundo em telefonia celular,
conforme indicado na tabela 2.1. Até 2014, cerca de 40% dos dispositivos ainda
utilizavam a tecnologia 2G, enquanto apenas 2% destes usavam a tecnologia 4G.
Seguindo a tendência mundial, a projeção é que até 2019 a porcentagem de acessos
4G suba para 47,5% e a de acessos 2G caia para 11%, como mostra os gráficos da
Figura 2.4.
Tabela 2.1: Principais mercados de celular no mundo, em milhões. Fonte: Teleco [2].
Ranking
1
2
3
4
5
6
7
País
China
Índia
EUA
Indonésia
Brasil
Rússia
Japão
2008
641
347
270
141
152
188
110
2009
747
525
286
159
174
208
115
2010
859
752
296
220
203
215
121
2011
986
894
316
237
242
228
130
2012
1.112
865
326
281
262
231
138
2013
1.229
886
336
304
271
243
146
2014
1.286
944
355
319
281
221
153
MAno
4,6%
6,5%
5,8%
4,9%
3,6%
-9,1%
4,6%
Figura 2.4: Projeção para os acessos móveis no Brasil. Fonte: 5G Americas [4].
2.1.3
Características do UMTS
O UMTS usa a técnica de múltiplo acesso WCDMA (Wideband Code Division
Multiple Access) como interface de rádio, pois necessita de uma banda larga com
alta capacidade. Ela utiliza um canal de rádio portador de 5 Mhz, e aperfeiçoa a
8
utilização dessa banda, apresentando menor custo por bit transmitido. Isso o torna
melhor adaptado para a realização múltiplos serviços como internet móvel, e-mail,
transferência de dados em alta velocidade, vídeo-chamada, multimídia, vídeo sobre
demanda e streaming de áudio. Estes serviços de dados necessitam maior velocidade e menor tempo de atraso na transmissão de dados. Com as novas tecnologias
HSPA, é possível atingir taxas de download e upload de 14,4 Mbits/s e 5,76 Mbits/s
respectivamente.
No WCDMA existe uma divisão lógica entre a rede de acesso de rádio, RAN
(Radio Access Network - Rede de Acesso), que garante acesso sem fio do usuário em
um ambiente móvel, e o de núcleo de rede, Core Network (Núcleo da Rede), que
processa as chamadas de voz e conexão de dados, garantindo a conexão do usuário
com outras redes de telecomunicações. Essa divisão é ilustrada na Figura 2.5.
Figura 2.5: Divisão lógica da rede móvel em rede de acesso (RAN) e núcleo de rede
(Core Network.)
A Figura 2.6 mostra a topologia de uma rede UMTS. O primeiro bloco refere-se
ao dispositivo do usuário, como um aparelho celular que possuiu seu próprio Sim
Card (USIM - Universal Subscriber Identity Module). O segundo bloco refere-se
a rede de acesso, onde a fim de garantir o investimento das operadoras, existe a
compatibilidade do dispositivo e entre as redes 2G e 3G. Para o primeiro caso, a
rede de acesso chama-se GERAN (GSM EDGE Radio Access Network ), que serve
para as tecnologias GSM, GPRS e EDGE. No segundo caso, a rede se chamada
UTRAN (UMTS Terrestrial Radio Access Network ), que garante o acesso para as
tecnologias UMTS e HSPA. Por fim a rede se comunica com a Core Network, que
possui o domínio PS (Packet Switched - Comutação por Circuitos) e o domínio
CS (Circuit Switched - Comutação por Pacotes), sendo o primeiro responsável pelo
acesso à internet e o segundo responsável por chamadas de voz.
9
Figura 2.6: Topologia UMTS. Fonte: Qualcomm Wireless Academy [5].
A Figura 2.7 detalha o funcionamento da UTRAN, que faz a interface entre o
dispositivo móvel e a Core Network. Ela é composta de dois equipamentos principais, o Node-B e o RNC (Radio Network Controller ). O Node-B, ou estação
móvel, desempenha funções de amplificação de sinal RF, modulação e codificação, e
multiplexação/demultiplexação das informações dos usuários. É o equipamento da
UTRAN que implementa a interface de comunicação sem fio com as unidades móveis. Cada Node-B tipicamente possuiu três setores de cobertura, a qual chamamos
de célula. O RNC é um elemento que gerencia vários Node-Bs sendo responsável
principalmente pelos procedimentos ligados à alocação de canais na interface aérea
e a garantia da qualidade dos enlaces de RF.[5, 16, 19]
Figura 2.7: UTRAN - UMTS Terrestrial Radio Access Network. Fonte: Qualcomm
Wireless Academy [5].
A Figura 2.8 mostra o núcleo de uma Core Network. Pode-se observar que os
domínios PS e CS ocorrem em paralelo na rede. A interface de um RNC com a Core
10
Network CS é chamada de Iu-CS, enquanto a interface de um RNC com a Core
Network se chama de Iu-PS.
No domínio PS existem dois elementos, SGSN (Serving GPRS Support Node)
e GGSN (Gateway GPRS Support Node), que servem para prover serviços de dados comutados por pacotes à rede de telefonia móvel. O SGSN tem a função de
roteamento e transferência de pacotes, conexão e desconexão de estações móveis,
autenticações e gerenciamento lógico das conexões. O GGSN serve como interface
para outra rede baseada em comutação de pacotes, como a internet. [5, 16, 17]
No domínio CS destaca-se a MSC (Mobile Switching Center ), que tem tem as
funções de comutação das chamadas de voz, gerência de mobilidade dos usuários,
sinalização de conexão, entre outras. A MSC pode se conectar a uma rede de
telefonia pública comutada (PSTN – Public Switched Telephone Network ), ou a uma
rede digital com integração de serviços (ISDN – Integrated Service Digital Network )
por meio de uma ponte de rede, denominado GMSC (Gateway Mobile Switching
Center ). Assim é possível realizar ligações entre telefonias fixas e móveis. [5, 16]
Figura 2.8: Core Network. Fonte: Qualcomm Wireless Academy [5].
2.1.4
Características do LTE
A tecnologia LTE possui transmissão de dados de 100 Mbps para o downlink e de
50 Mbps para o uplink em uma banda de 20 MHz. Utiliza a técnica de transmissão
OFDM (Orthogonal Frequency Division Multiple Access).
A arquitetura do LTE é considerada mais simples que a da UMTS, como pode-se
ser visto na Figura 2.9. A rede de acesso agora é chamada de E-UTRAN (Evolved
Universal Terrestrial Radio Access) e o núcleo de rede de EPC (Evolved Packet
Core). A E-UTRAN é caracterizada, basicamente, por dois requisitos:
11
(1) Suporte apenas para comutação de pacote (não existindo mais o domínio CS)
(2) Baixa latência
Para atingir esses requisitos foi necessário diminuir os elementos de rede, pois
assim o processamento com relação aos protocolos de rede é menor, assim como é
menor o custo com testes e interfaces. Com isso a rede de acesso contém apenas
os eNode-Bs (Evolved Nodeb-B), que incorpora as funções que RNC e Node-B tinham na rede UTRAN. Pode-se notar ainda que um eNode-B também se comunica
diretamente com outros eNode-Bs. [16]
Figura 2.9: Arquitetura E-UTRAN.
2.2
Conceitos de KPI
KPI é um indicador chave, utilizado para medir o desempenho dos processos de
uma iniciativa, estratégia ou negócio de uma empresa e, com essas informações,
colaborar para que alcance seus objetivos. Esse indicador deve ser quantificável
por meio de um índice (normalmente representado por um número) que retrate o
andamento do processo como um todo ou em parte.
KPIs não são exclusividade de telecomunicações ou da área tecnológica, podendo
existir KPIs financeiros ou econômicos. Nesses casos é de suma importância definir
e escolher qual indicador usar para que esteja ligado ao objetivo do processo. Neste
trabalho usaremos os KPIs mais utilizados e já definidos na documentação oficial da
empresa de comunicações [14], que são comumente usados para monitorar e avaliar
o funcionamento da uma rede de telefonia celular, além de monitorar o tráfego de
rede, monitorar a distribuição de recursos e facilitar a expansão da rede e otimização.
Na grande maioria dos casos esses KPIs estão diretamente ligados à qualidade da
rede, no entanto alguns KPIs são criados com a finalidade de retratar, ou melhor,
tentar retratar a experiência do usuário ou qualidade do serviço.
12
KPIs são, geralmente, computados por fórmulas e não dados brutos. As fórmulas
normalmente não são iguais para operadoras de telefonia diferentes. Como exemplo
temos uma fórmula para o KPI Acessibilidade RRC :
RRC.SuccConnEstab.OrgConvCall + RRC.SuccConnEstab.OrgStrCall
+ RRC.SuccConnEstab.OrgInterCall + RRC.SuccConnEstab.OrgBkgCall
+RRC.SuccConnEstab.OrgSubCall + RRC.SuccConnEstab.T mConvCall
+ RRC.SuccConnEstab.T mStrCall + RRC.SuccConnEstab.T mItrCall
100×
RRC.AttConnEstab.OrgConvCall + RRC.AttConnEstab.OrgStrCall
+ RRC.AttConnEstab.OrgInterCall + RRC.AttConnEstab.OrgBkgCall
+RRC.AttConnEstab.OrgSubCall + RRC.AttConnEstab.T mConvCall
+ RRC.AttConnEstab.T mStrCall + RRC.AttConnEstab.T mItrCall
Onde:
OrgConvCall = Originating Conversational Call
OrgStrCall = Originating Streaming Call
OrgInterCall = Originating Interactive Call
OrgBkgCall = Originating Background Call
OrgSubCall = Originating Subscribed traffic Call
TmConvCall = Terminating Conversational Call
TmStrCall = Terminating Streaming Call
TmItrCall = Terminating Interactive Call
Os elementos que fazem parte da fórmula são os contadores, ou dados de
desempenho, retirados dos servidores da empresa. Pode-se observar que a fórmula usa o conceito Sucessos/T entativas. Também é possível usar o conceito
[(T entativas − F alhas)/T entativas] que exprime a mesma realidade.
Os KPIs escolhidos para o desenvolvimento do trabalho são divididos em grupos,
conforme a documentação oficial da empresa [14]. São estes:
• Acessibilidade (do inglês, Accessibility): Capacidade do usuário para obter o
serviço de chamada, seja de voz (domínio CS) ou dados (domínio PS). Podemos
dividir uma chamada em duas partes simples: a sinalização (ou controle) e os
dados (ou informação). Já adiantando um dos principais conceitos, podemos
entender o RRC (Radio Resource Control ) como responsável pela parte de
controle, e o RAB (Radio Access Bearer ) como responsável pela parte da
informação. [20]
13
– RRC Setup Succcess Ratio: Mede a taxa de sucessos (em %) de conexões
RRC para chamadas de voz, streaming, chamadas de emergência, chamadas de espera, etc. Note que uma conexão RRC apenas não é suficiente
para estabelecimento desses serviços de chamada.
– CS RAB Setup Success Ratio (apenas para tecnologia UMTS): Mede
a taxa de sucessos (em %) de conexões RAB para chamadas de voz e
streaming no domínio CS da Core Network. Só é possível estabelecer
uma conexão RAB após o estabelecimento de uma conexão RRC.
– PS RAB Setup Success Ratio (apenas para tecnologia UMTS): Mede a
taxa de sucessos (em %) de conexões RAB para chamadas de voz e streaming no domínio PS da Core Network.
– E-RAB Setup Success Rate (apenas para tecnologia LTE): Mede a taxa
de sucessos (em %) de conexões E-RAB (Evolved Radio Access Bearer )
para todos os serviços de rádio ou VoIP (Voice over Internet Protocol ).
Só é possível estabelecer uma conexão E-RAB após o estabelecimento de
uma conexão RRC.
– HSDPA RAB Setup Success Ratio (apenas para tecnologia UMTS): Mede
a taxa de sucessos (em %) de conexões RAB para serviços que utilizam
a tecnologia HSDPA, no domínio PS.
• Disponibilidade (do inglês, Availability): Indica a disponibilidade dos serviços da rede.
– Cell Unavailability duration: Mede a duração total do tempo (em segundos) que uma célula ficou indisponível na rede por causa de alguma falha
no sistema.
• Cobertura (do inglês, Coverage): Monitora as interferências das células e o
uso de Soft Handovers em um RNC.
– RTWP (Received Total Wideband Power ): Representa a medida do nível
total de ruído (em dB) dentro da banda de frequência de uma célula. Está
relacionada com a interferência de uplink, e a sua monitoração ajuda no
controle de queda de chamadas - principalmente no domínio CS. (apenas
para tecnologia UMTS). Em redes não congestionadas, o valor médio
aceitável de RTWP é de -104.5 a -105.5 dBm. Valores muito abaixo
indicam interferência de uplink na rede, conforme indica a Figura 2.10.
– Soft Handover Overhead (apenas para tecnologia UMTS): Verifica o consumo da rede devido à Soft Handovers.
14
Figura 2.10: Valores típicos de RTWP. Fonte: telecomHall [6]
• Mobilidade (do inglês, Mobility): Monitora as taxas de sucesso de vários tipos
de handover. O conceito de sucesso de handover é manter a integridade de uma
chamada em movimento, sem que haja interrupções. Isto é necessário porque
o usuário pode mover-se (talvez com alta velocidade) e seria inconveniente se
a chamada caísse quando o usuário mudasse de uma célula para outra. Em
muitos casos, um usuário pode até entrar em uma área onde há cobertura da
rede UMTS e terminar a mesma chamada sem interrupções em uma rede GSM
/ GPRS.
– Softer Handover Success Ratio (apenas para tecnologia UMTS): Mede a
taxa de sucesso (em %) de Softer Handover em um RNC, que consiste
na transferência de chamada de uma célula para outra célula do mesmo
Node-B.
Figura 2.11: Softer Handover. Fonte: Teleco [7]
– Soft Handover Success Ratio (apenas para tecnologia UMTS): Mede a
taxa de sucesso (em %) de Soft Handover em um RNC, que consiste na
15
transferência de chamada de um Node-B para outro Node-B.
Figura 2.12: Soft Handover. Fonte: Teleco [7]
– Intra-frequency Hard Handover Success Ratio: Mede a taxa de sucesso
(em %) do Intra-frequency Hard Handover Success, que ocorre quando a
conexão com o Node-B atual é interrompida antes de ser conectada com
um novo Node-B de mesma frequência.
– Inter-frequency Hard Handover Success Ratio: Mede a taxa de sucesso
do Intra-frequency Hard Handover Success, que ocorre quando a conexão
com o Node-B atual é interrompida antes de ser conectar com um novo
Node-B de outra frequência.
Figura 2.13: Hard Handover. Fonte: Teleco [7]
– W2G Inter-RAT Hard Handover Success Ratio: Mede a taxa de sucesso
(em %) da transferência de chamada de um sistema UMTS para GSM
ou vice-versa. A finalidades básica é manter a continuidade da chamada
em sistema de borda de cobertura UMTS, transferindo assim a chamada
sem interrupções para um sistema GSM ou de um GSM para um UMTS.
16
Figura 2.14: Inter-RAT Hard Handover. Fonte: Teleco [7]
– L2W Inter-RAT Handover Out Success Rate: Mede a taxa de sucesso
(em %) da transferência de chamada de um sistema LTE para UMTS ou
vice-versa.
– L2G Inter-RAT Handover Out Success Rate: Mede a taxa de sucesso
(em %) da transferência de chamada de um sistema LTE para GSM ou
vice-versa.
• Capacidade de Retenção (do inglês, Retainability): Mede a capacidade do
usuário de manter uma conexão estabelecida sem que esta seja finalizada de
forma anormal. Comumente medimos a porcentagem de quedas nos serviços
providos aos usuários, mas também é possível medi-la nos canais de sinalização.
– CS Service Drop Ratio: Taxa de quedas de serviços CS na rede UTRAN.
– PS Call Drop Ratio: Taxa de quedas de chamadas PS na rede UTRAN.
• Integridade de Serviço (do inglês, Service Integrity): Indica principalmente
a capacidade da taxa de transferência em cada célula e qualidade de serviço
na rede.
– HSDPA Throughput: Indica a média de downlink throughput (taxa de
transferência, em kbit/s) em uma célula.
– HSUPA Throughput: Indica a média de uplink throughput (taxa de transferência, em kbit/s) em uma célula.
• Tráfego (do inglês, Traffic): Mede o tráfego da rede, quantidade de dados e
de usuários.
17
– HSDPA RLC Traffic Volume: Fornece o total em bytes de downlink de
todos os tipos de serviços em uma célula.
– HSUPA RLC Traffic Volume: Fornece o total em bytes de uplink de todos
os tipos de serviços em uma célula.
– Number of HSDPA Users: Fornece a quantidade total e a quantidade
média de usuários usando a tecnologia HSDPA.
– Number of HSUPA Users: Fornece a quantidade total e a quantidade
média de usuários usando a tecnologia HSUPA.
Os contadores e fórmulas usadas para cálculo desses KPIs podem ser encontrados
na documentação oficial da empresa. Os KPIs apresentados no trabalho são geralmente os padrões que as empresas utilizam para análise de uma rede de telefonia,
mas é possível também definir um KPI personalizado para uma operadora, sabendo
que contadores usar e qual a metodologia para o seu cálculo.
2.3
Conceitos de BI
O termo Business Intelligence (BI), inteligência de negócios, refere-se ao processo
de coleta, organização, análise, compartilhamento e monitoramento de informações
que oferecem suporte à gestão de negócios. É o conjunto de teorias, metodologias, processos, estruturas e tecnologias que transformam uma grande quantidade
de dados brutos em informação útil para tomadas de decisões estratégicas. [21]
Pode-se resumir da definição anterior que BI é um sistema capaz de transformar
uma quantidade de informação em informações realmente úteis para uma rápida visualização dos objetivos propostos. Esse conceito será utilizado no trabalho, quando
dados provenientes de fontes dispersas serão transformados com o objetivo de gerar
KPIs das redes de telecomunicações. A Figura 2.15 mostra a arquitetura tecnológica de um BI, onde é possível verificar a existência de fontes externas ou internas
contendo os dados desejados, podendo essas fontes serem arquivos em diversos formatos ou outros bancos de dados. Pode-se observar também a existência de um
processo ETL para carga dos dados em uma Data Warehouse. Por fim, informações
presentes em Data Marts serão analisadas pelos usuários de um sistema BI através
de aplicações Front-End. No caso deste trabalho, através de dashboards.
18
Figura 2.15: Arquitetura tecnológica de um BI. [8]
Cada um dos conceitos usados em BI será melhor explicado a seguir:
2.3.1
ETL
A sigla ETL significa Extração, Transformação e Carga (em inglês Extract, Transform and Load ) e visa trabalhar com toda a parte de extração de dados de fontes
externas, transformação para atender às necessidades de negócios e carga dos dados
dentro do Data Warehouse.[22] ETL é uma das etapas mais importantes do projeto,
sendo necessária bastante atenção na integridade dos dados a serem carregados em
um Data Warehouse. Existem estudos que indicam que o ETL e as ferramentas de
limpeza de dados consomem cerca de 70% dos recursos de desenvolvimento e manutenção de um projeto de Data Warehouse.[12] A Figura 2.16 apresenta cada uma
das etapas do ETL que serão descritas a seguir:
Figura 2.16: Estrutura do ETL. [9]
19
Extração
É a coleta de dados dos sistemas de origem, que podem ser outras bases de
dados relacionais ou arquivos, extraindo-os e transferindo-os para a staging area
(área de transição ou área temporária), onde o processo de ETL pode operar de
forma independente.
Transformação
É nesta etapa que são realizados os devidos ajustes dos dados extraídos, aplicando uma série de regras ou funções para assim melhorar a qualidade dos dados e
consolidar dados de duas ou mais fontes. Algumas fontes de dados necessitarão de
muito pouca manipulação de dados. Em outros casos, diversos tipos de transformações são feitas para atender as regras do negócio. [9, 22]
Carga
Consiste em carregar os dados para dentro do Data Warehouse. Dependendo
das necessidades da organização, o tempo de execução deste processo pode variar
bastante, podendo ter dados atualizados semanalmente ou a cada meia hora.
2.3.2
Data Warehouse
É um depósito de dados é utilizado para armazenar, de forma consolidada, informações relativas às atividades de uma organização em bancos de dados. Nesse
contexto, o Data Warehouse possibilita a confecção de relatórios, a análise de grandes volumes de dados históricos, permitindo uma melhor análise de eventos passados,
oferecendo suporte às tomadas de decisões estratégicas que podem facilitar a tomada
de decisão.
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.
Fora de um caso específico 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 1980. Com
o amadurecimento dos sistemas de informação empresariais, as necessidades de análise dos dados cresceram paralelamente. 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.
20
Como principais requisitos para um Data Warehouse bem elaborado, pode-se
destacar: [22]
• Tornar a informação facilmente acessível. O Conteúdo do Data Warehouse
deve ser intuitivo e de fácil entendimento para o usuário (não apenas para o
desenvolvedor).
• Apresentar informações consistentes. Informação consistente significa informação de alta-qualidade. Significa que todos os dados são relevantes, precisos
e completos. Os dados apresentados devem ser confiáveis e íntegros.
• Adaptável e flexível à mudanças. As necessidades dos usuários, os dados e as
condições do negócio vão mudar com o passar do tempo, então é preciso que
o Data Warehouse esteja apto a endereçar novas questões, novas necessidades
sem que se perca todo o trabalho realizado.
• Auxiliar no processo de tomada de decisão e ser aceito pela comunidade de
negócios. De nada adianta um Data Warehouse com milhões de dados que
não tragam os indicadores necessários para a tomada de decisão da empresa.
Um dos maiores problemas no desenvolvimento do Data Warehouse é a compreensão dos dados, onde as dimensões devem ser definidas conforme a necessidade
de visualização do usuário. Pode-se armazenar grandes quantidades de informação,
sendo o modelo mais utilizado conhecido como modelagem multidimensional. Apesar de bastante utilizado, não existe um padrão na indústria de software para o
armazenamento de dados. Existem, na verdade, algumas controvérsias sobre qual
a melhor maneira para estruturar os dados em um Data Warehouse.[23, 24] Neste
trabalho não será usada a modelagem multidimensional, pois acredita-se que isso
atrasaria a carga dos dados e aumentaria tempo de latência. Porém a modelagem
multidimensional não está descartada em trabalhos futuros.
2.3.3
Data Marts
O Data Warehouse é normalmente acedido através de Data Marts, que é uma
estrutura similar ao do Data Warehouse, porém com uma proporção menor de informações. Trata-se de um subconjunto de informações do Data Warehouse que
podem ser identificadas por assuntos ou departamentos específicos. 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. [9, 23]
21
2.3.4
Dashboard
Na tradução simples um dashboard é um painel de indicadores, com seus principais
indicadores e gráficos compilados em um único painel de fácil acesso, manuseio e
visualização.
O dashboard organizará e apresentará melhor os conteúdos informativos, dando a
eles um design mais profissional e interativo. Todo resultado apresentado será mais
facilmente analisado possibilitando uma melhor tomada de decisões. [25]
22
Capítulo 3
Solução implementada
O escopo da ferramenta desenvolvida no trabalho, conforme descrito na seção 1.4,
é criar dashboards com os principais KPIs de redes de telefonia que utilizam as
tecnologias 3G e 4G. Ela possui somente a interface de usuário, que irá interagir
apenas com a interface web.
Este capítulo descreve a arquitetura do trabalho e suas funcionalidades na seção 3.1. Na seção 3.2 serão apresentadas as ferramentas e tecnologias utilizadas no
trabalho e alguns dos motivos de suas escolhas.
3.1
Arquitetura do Sistema
A Figura 3.1 apresenta a arquitetura geral do sistema, mostrando como se encontram divididas as principais etapas do trabalho. Pode-se observar a presença
de servidores externos onde arquivos contendo os dados necessários serão extraídos,
transformados e carregados em um Data Warehouse (que na arquitetura do sistema,
fica dentro do Servidor de Dados), que é justamente o processo de ETL descrito na
seção 2.3.1 da fundamentação teórica. A seguir será realizada uma descrição de cada
uma dessas etapas.
23
Figura 3.1: Arquitetura do Sistema.
3.1.1
Servidores Externos
São elementos que fazem parte da rede de móvel que irão prover os dados necessários ao sistema. O trabalho foi desenvolvido usando os servidores externos da
empresa Huawei, mas poderiam ser usados os de qualquer outra empresa de telecomunicações que possua os dados de performance e configuração de redes de telefonia
celular.
24
A solução da Huawei para gerenciar redes móveis se chama EMS (Element Management System), uma plataforma de gerenciamento para suportar operadoras de
telecomunicações com uma única interface para as diversas tecnologias existentes,
os modelos disponíveis são conhecidos como M2000 e U2000. O sistema opera de
uma forma cliente-servidor e a estrutura do hardware geralmente é composta por
servidores, clientes, alarm-boxes dedicados e outros dispositivos dedicados de rede
como arrays de discos, roteadores, etc. A Figura 3.2 mostra como se organiza a
estrutura do EMS.
Figura 3.2: Estrutura do EMS.
O trabalho utiliza três servidores do EMS para a rede UMTS, chamados de ATAE,
15 e 40. Já para a rede LTE utiliza apenas um servidor, chamado de 10. Cada
servidor da rede UMTS possui um conjunto de RNCs, conforme indica a tabela 3.1,
em um total de 43 RNCs.
25
Tabela 3.1: RNCs disponíveis em cada um dos servidores do EMS
Servidor
15
40
ATAE
RNC
RNCMG01
RNCMG02
RNCMG03
RNCMG04
RNCMG05
RNCMG06
RNCMG07
RNCMS01
RNCMS02
RNCMS03
RNCMT02
RNCRO01
RNCTO01
RNCAL01
RNCBA01
RNCBA02
RNCBA03
RNCBA04
RNCBA05
RNCCE01
RNCCE02
RNCPB01
RNCPE01
RNCPE02
RNCPE04
RNCPI01
RNCPI02
RNCRN01
RNCAC01
RNCDF02
RNCDF03
RNCDF04
RNCES01
RNCGO01
RNCGO04
RNCGO05
RNCPR01
RNCPR02
RNCPR03
RNCPR04
RNCPR05
RNCSC01
RNCSC02
26
Através da tabela 3.1 pode-se perceber que a nomenclatura de cada RNC segue o
padrão {RNC}{UF}{Numeração com dois algarismo}, sendo possível determinar o
estado do Brasil onde se encontra um RNC através do UF (Unidade da Federação).
Cada um dos três servidores possui seu endereço IP (Internet Protocol - Protocolo
de Internet) onde é possível conectar-se através de uma interface FTP (File Transfer
Protocol - Protocolo de Transferência de Arquivos) e extrair os arquivos de configuração (contendo diversas informações como a quantidade de Node-Bs e células
que um RNC possui, em qual frequência cada célula se encontra, em qual bairro
ou cidade uma célula está localizada, etc.) e de performance nos formatos XML
(eXtensible Markup Language - Linguagem de Marcadores Extensível).
Os arquivos de performance contém contadores em amostras de 15, 30 ou 60
minutos de diversos tipos de famílias, como as famílias com contadores de conexões
RRC e RAB, as de medidas dos diversos tipos de handover, as de medidas de tráfego,
etc. Esses contadores são usados para cálculos dos KPIs, conforme dito na seção 2.2.
3.1.2
Implementação do ETL
O conceito de ETL introduzido na seção 2.3.1 foi implementado no trabalho conforme o Diagrama de Fluxos da Figura 3.3. É importante ressaltar que antes de se
iniciar o processo é necessário atender os seguintes pré-requisitos:
• Tabelas de todas as famílias de contadores tenham sido criadas no Banco de
Dados
• Uma tabela de log de todos os arquivos baixados tenha sido criada no Banco
de Dados
Em seguida será detalhada cada etapa do processo ETL, dando exemplos práticos
para o seu melhor entendimento.
27
Figura 3.3: Diagrama de Fluxos do Processo de ETL.
28
Extração
Arquivos de desempenho e configuração são baixados dos servidores EMS, onde
apenas os arquivos de performance contendo as famílias dos contadores usados no
trabalho para os cálculos dos KPIs serão extraídos. Para a rede UMTS isso significa
um total de 29 famílias, enquanto para a tecnologia LTE são extraídos arquivos
para 11 famílias de contadores. Das 29 famílias de contadores da rede UMTS, 27
são disponíveis em amostras de 30 minutos e duas em amostras de 60 minutos.
Para a rede LTE, todas as famílias baixadas são de contadores com amostras de 60
minutos. A informação de cada arquivo baixado é armazenada em uma tabela de
log no Banco de Dados para que durante esta etapa seja feita uma verificação de
quais arquivos já foram baixados, a fim de evitar downloads desnecessários.
Os arquivos baixados estão todos no formato XML. O XML é uma linguagem de
programação de marcadores como a HTML e foi desenhada para descrever dados,
com a grande vantagem de que é extensível , ou seja, pode-se criar as suas próprias
tags, assim sendo ela é uma linguagem auto definível. O XML possui estrutura
em árvore, contendo elementos da raiz (pai), elementos filhos e assim por diante.
Usando a estrutura de árvore, pode-se conhecer todos os ramos que partem da raiz.
[12, 26]
XML é muito usado para a transferência de dados. A desvantagem do formato
XML é que com uma grande quantidade de dados em uma mesma estrutura, podese gerar um arquivo extremamente complexo e ineficiente para ser carregado em
uma base de dados. A Figura 3.4 mostra um exemplo de um arquivo XML usado
no trabalho para uma família com contadores de RRC da rede UMTS, aberto em
um editor de texto. O objeto <cbt> destacado na quarta linha contem a data da
amostra com os valores do ano, mês, dia e hora. O objeto <gp> destacado na nona
linha contem o intervalo da mostra em minutos, no caso de 30 minutos. Os demais
objetos a partir da décima linha possuem os nomes dos contadores.
29
Figura 3.4:
Exemplo de um arquivo XML contendo uma família com contadores de
RRC.
A linha 156 do mesmo arquivo possui o objeto <moid>, contendo as informações
do nome e identificador de uma célula, e qual seu RNC, conforme destacado na
Figura 3.5. Abaixo do objeto estão os valores que os contadores dessa célula recebem.
Toda a rede UMTS possui aproximadamente 46 mil células.
Figura 3.5: Objeto de um arquivo XML contendo o nome de um RNC, e o nome e
identificador de uma célula.
30
A Figura 3.6 mostra agora um exemplo do objeto <moid> para a rede LTE,
contendo então o nome e identificador de um eNode-B, e o nome e identificador de
uma célula. A rede LTE possui aproximadamente 8.500 células.
Figura 3.6: Objeto de um arquivo XML da rede LTE contendo o nome e identificador
de um eNode-B, e o nome e identificador de uma célula.
Transformação
Como dito anteriormente, arquivos XML muito grandes acabam se tornando ineficientes para serem carregados em um Data Warehouse. Pensando nisso, a segunda
etapa do processo ETL é dividida em duas partes, sendo a primeira a conversão do
arquivo do formato XML para CSV (Comma-separated values) e a segunda a transformação desse arquivo para o mesmo formato da sua respectiva tabela no Banco
de Dados, comumente chamada de parser.
Antes da primeira conversão é necessário validar o arquivo XML para garantir sua
integridade, evitando erros de arquivos danificados ou mal-baixados. A conversão
do arquivo XML para planilhas no formato CSV faz com que o arquivo fique mais
leve e fácil de ser manipulado, além de ser muito mais inteligível para o usuário. Em
média, um arquivo convertido para CSV fica 70% menor que o original em XML.1
Com isso, a carga de um arquivo no formato CSV no Banco de Dados é muito mais
rápida do que de um arquivo XML.
Para se ter uma ideia mais precisa da vantagem de um arquivo CSV, foram testados dois cenários: o primeiro comparando o tempo de carga de um arquivo XML
com o tempo das etapas de transformação, parser e carga de um arquivo CSV em
uma base de dados vazia; o segundo faz a mesma comparação, mas para uma base
de dados já povoada. A Figura 3.7 mostra que, para o primeiro cenário, a carga
do arquivo XML de 26 MB demorou aproximadamente 13 segundos, enquanto todas as etapas para o arquivo CSV demoraram aproximadamente 11 segundos. O
segundo cenário é apresentado na Figura 3.8, onde o mesmo arquivo XML demorou aproximadamente 37 segundos e as etapas com o arquivo CSV próximo dos 14
segundos.
O arquivo XML do exemplo anterior tinha 26 MB, ficando com 8 MB após a conversão para
CSV.
1
31
Figura 3.7: Tempo de carga em uma base de dados vazia, em segundos.
Figura 3.8: Tempo de carga em uma base de dados já populada, em segundos.
O arquivo do exemplo anterior após a conversão é mostrado na Figura 3.9, aberto
no Excel. Em vez de tags e objetos, a nova estrutura é formada por linhas e colunas
em que os cabeçalhos são CBT, GP, MOID e os contadores daquela família.
Figura 3.9: Arquivo do exemplo anterior após a conversão para uma planilha em CSV.
32
A segunda etapa da conversão consiste em ajustar os campos da coluna CBT para
data no formato padrão do Banco de Dados (ano-mês-dia hora:minuto), quebrar os
campos da coluna CBT em várias colunas e ordenar todas as colunas para o mesmo
formato da sua tabela correspondente no Banco de Dados.
Essa etapa também elimina cabeçalhos e os dados de contadores que não estão
sendo utilizados, diminuindo o tamanho do arquivo e fazendo com que a carga para
dentro do Data Warehouse se torne mais rápida. O formato das tabelas no Banco
de Dados para as famílias de contadores por RNC segue o padrão da tabela 3.2,
contendo sempre as mesmas cinco primeira colunas no cabeçalho (RNC, Cellname,
CellID, Datetime e GP) e os contadores nas colunas restantes.
Tabela 3.2: Exemplo de uma tabela de uma família de contadores por RNC.
RNC
RNCAC01
...
RNCSC02
Cellname
UACABL01A
...
USCTIO02G
Cellid
63096
...
64843
Datetime
2016-06-01 00:00
...
2016-06-01 23:30
GP
30
...
30
...
...
...
...
Continuando com os exemplos anteriores, a Figura 3.10 mostra o arquivo anterior
após o parser, aberto no Excel. Foi tirado o cabeçalho e agora existem colunas
novas e reordenadas seguindo o modelo da tabela 3.2. Notam-se também colunas
com todos os campos em branco, indicando que essa família possui contadores que
não estão sendo utilizados no trabalho.
Figura 3.10: Arquivo do exemplo anterior após o parser.
33
Para famílias com contadores por Nodeb-B, o padrão seguido é o da tabela 3.3.
Neste trabalho existem duas famílias desse tipo, ambas com amostragem de uma
hora (exatamente por isso a coluna GP possui valor igual a 60).
Tabela 3.3: Exemplo de uma família com contadores por Node-B
Nodeb
CBA0036S
...
WTOTAQ08
Locell
0
...
2
Datetime
2016-06-01 00:00
...
2016-06-01 23:30
GP
60
...
60
...
...
...
...
A tabela 3.4 mostra um exemplo para uma família do LTE, possuindo sempre as mesmas cinco primeiras colunas (eNodebID, eNodeb, Locall Cell, Cellname,Datetime e GP) com contadores em amostras de uma hora.
Tabela 3.4: Exemplo de uma família de contadores do LTE
eNodebid
270016
...
880216
eNodeb
EESCAR01
...
ECESOL12
LoCellid
0
...
2
Cellname
EESCAR01A
...
ECESOL12C
Datetime
2016-06-01 00:00
...
2016-06-01 23:30
GP
60
...
60
...
...
...
...
Carga dos Dados
Após os arquivos passarem pelo processo de transformação, já é possível ser feita
a carga destes no Data Warehouse. Quando os dados são carregados no Banco
de Dados acabam ocupando um maior volume do que quando estavam no formato
de arquivo CSV, conforme mostra a Figura 3.11. Porém é importante ressaltar que
existem grandes vantagens de utilizar uma base de dados, das quais pode-se destacar
a resposta mais rápida a consultas e manipulação dos dados.
34
Figura 3.11: Volume de dados por arquivo (MB).
Dados de configuração ficam armazenados no Banco de Dados para consultas,
sendo atualizados diariamente. Os dados de performance por sua vez precisam passar por mais uma etapa, onde ocorrerá o processamento dos dados para os cálculos
dos KPIs das redes UMTS e LTE.
Processamento dos Dados
A Figura 3.12 apresenta o diagrama de fluxos do processamento dos dados da ferramenta. É feita sempre uma verificação periódica se todos arquivos de performance
que foram carregados para alguma meia hora.
35
Figura 3.12: Diagrama de Fluxos do Processamento dos Dados.
36
Com meias horas completas, já é possível começar a calcular os indicadores de
30 minutos por célula e RNC mencionados na seção 2.2. Os dados dos indicadores
calculados são armazenados em tabelas no Banco de Dados. Ao final de um dia
espera-se que todos os KPIs das 48 meia horas do dia já tenham sido calculados
para rede UMTS, assim como os KPIs das 24 horas do dia para rede LTE. Então
são calculados outros indicadores diários, tais como:
• Cálculos dos indicadores diários por célula, agregando também por região, por
RNC e por cidade. Cada cálculo é armazenado em sua tabela correspondente
no Banco de Dados.
• Cálculos dos ofensores diários. Neste caso são ranquedas as piores células
diárias (em geral, as com maior quantidades de falhas) por RNC, cidade e
regional, e é feita a previsão de quanto cada KPI irá melhorar quando corrigidas
essas falha.
• Cálculo de outros KPIS customizáveis, não presentes na seção 2.2, asism como
seus ofensores.
Ao término de uma semana, as mesmas agregações dos indicadores diários também
são feitas para semana que passou.
3.1.3
Aplicação MVC
MVC (Model, View, Controller ) é a modelagem padrão usada para comunicação
entre a interface web e o Banco de Dados. Essa arquitetura é muito utilizada em
Engenharia de Sofware, onde é possível dividir tarefas, garantindo que cada camada
da aplicação tenha seu próprio escopo e definição e que a comunicação entre todas
elas se dê de maneira eficiente e controlada.
Em aplicações que enviam um conjunto de dados para o usuário, o desenvolvedor,
frequentemente, separa os dados (Model ) da interface (View ). Desta forma, alterações feitas na interface não afetam a manipulação dos dados, e estes podem ser
reorganizados sem alterar a interface do usuário. O Model-View-Controller resolve
este problema através da separação das tarefas de acesso aos dados e lógica do negócio da apresentação e interação com o usuário, introduzindo um componente entre
os dois: o Controller. [10, 27]
A arquitetura MVC é apresentada na Figura 3.13, onde é possível visualizar a
divisão de cada uma das camadas descritas a seguir:
37
Figura 3.13: Arquitetura MVC. Fonte: CodeIgniter Brasil [10].
Descrição da camada de Visão (do inglês, View )
Nesta camada estão as telas do sistema que fazem as interfaces com o usuário.
É a apresentação, é o que aparece, é o que é visualizado por quem usa o sistema.
É no View que as informações, sejam elas quais forem e de qual lugar tenha vindo,
serão exibidas para a pessoa. No caso do trabalho, essas informações são exibidas
através de dashboards.
Essa camada possui além da definição da interface, controladores para a correta
manipulação da tela em questão. Tratam corretamente tanto o envio quanto a
apresentação de dados.
Descrição da camada de Modelo (do inglês, Model )
Esta camada representa os dados da aplicação e as regras do negócio que governam o acesso e a modificação dos dados. É esta camada que manipula as estruturas
de dados e realiza a conexão com o Banco de Dados. É somente no Model que as
operações de CRUD (Create, Read, Update, Delete) devem acontecer.
Descrição da camada de Controle (do inglês, Controller )
Como sugere o nome, camada de controle é responsável por controlar todo o
fluxo do programa, a ligação da camada Model com a camada View. É o “cérebro”
38
e o “coração” do aplicativo, quem define o comportamento da aplicação, é ela que
interpreta as ações do usuário e as mapeia para chamadas do modelo. No controller
é definido desde o que deve ser consultado no Banco de Dados à tela que vai ser
exibida para quem usa o programa.
3.2
3.2.1
Materiais e Métodos
Infra-estrutura
Para o ambiente de desenvolvimento foram utilizados dois computadores com
sistema operacional Linux, distribuição Debian 8.1. O primeiro deles foi usado para o
servidor de arquivos conforme indicado na Figura 3.1, com um processador Intel Core
i7, com 8 GB de memória RAM e disco rígido com capacidade de armazenamento
de 500 GB. O segundo foi usado para o servidor de dados, com um processador Intel
Core i5, com 8 GB de memória RAM e disco rígido de 1,5 TB.
3.2.2
Ferramentas utilizadas
Segundo Kimball [12], para o uso de um processo de ETL pode-se utilizar ferramentas pagas existentes no mercado ou desenvolver todo o código do programa
através de uma ou mais linguagens de programação. Para o autor, uma das vantagens da ferramenta paga é a sua rapidez (não é preciso perder tempo desenvolvendo
todo o código), robustez (um programa já bastante testado e consolidado no mercado tende a ter menos erros) e facilidade no uso (geralmente acompanha um manual
para o usuário, não sendo necessário que o mesmo tenha um amplo conhecimento
em programação). Já o segundo método permite uma maior flexibilidade do processo, afinal utilizando linguagens de programação pode-se desenvolver programas
com inúmeras possibilidades e menos custosos computacionalmente (desde que sejam
escritos códigos que façam somente o estritamente necessário para o projeto).
Inicialmente no projeto foi utilizado o Pentaho para o processo de ETL, um
sofware de BI desenvolvido em Java. [28] Depois foram desenvolvidos programas
escritos nas linguagens Python e Shell Script para realizarem o processo, havendo
um ganho considerável de desempenho, o que se encontra de acordo com a teoria
de Kimball. Para gerenciar o Banco de Dados, foi usado o software PostgreSQL.
É também no PostgreSQL que foram desenvolvidas funções na linguagem PL/SQL
(Procedural Language/Structured Query Language) para cálculos dos KPIs. Já para
a aplicação MVC, foi utilizado o framework PHP CodeIgniter. O servidor web
utilizado é baseado em tecnologia Apache e opera sob o sistema operacional Linux.
39
Essas e as demais ferramentas utilizadas no trabalho serão descritas a seguir:
Python
Python [29] é uma linguagem de programação de altíssimo nível, de sintaxe
moderna, interpretada, orientada a objetos, com tipagem forte (não há conversões
automáticas) e dinâmica (não há declaração de variáveis e elas podem conter diferentes objetos) e multiplataforma. Sua primeira versão foi lançada em 1991, e a
versão mais recente (3.4.1) foi lançada em maio de 2014. Atualmente possui um
modelo de desenvolvimento comunitário e aberto. [30]
A linguagem foi projetada com a filosofia de enfatizar a importância do esforço do
programador sobre o esforço computacional. Prioriza a legibilidade do código sobre a
velocidade ou expressividade. Combina uma sintaxe concisa e clara com os recursos
poderosos de sua biblioteca padrão e por módulos e frameworks desenvolvidos por
terceiros.
A grande motivação para o uso do Python no trabalho foi o fato da linguagem ser
multiplataforma e ser atualmente a quarta linguagem mais usada no mundo [31],
fazendo com que haja muito material disponível na internet para realizar consultas
e tirar dúvidas. A etapa do processo ETL do trabalho que executa a extração dos
dados foi desenvolvida em Python usando a biblioteca ftplib [32], que implementa
um servidor FTP. A etapa que faz o parser dos arquivos também foi desenvolvida
em Python, utilizando a biblioteca csv [33]. Para a conexão com o PostgreSQL, foi
usada a biblioteca psycopg [34].
Shell Script
O Shell Script é uma linguagem simples e que não precisa ser compilada para
executar as tarefas, ou seja, ela é interpretada diretamente pelo shell. É usada em
vários sistemas operacionais, com diferentes dialetos, dependendo do interpretador
de comandos utilizado. O interpretador de comandos usado no trabalho é o bash
[35], disponível na grande maioria das distribuições GNU/Linux.
Diversos scripts em Shell foram desenvolvidos no trabalho, para todas as etapas
do ETL e também para os cálculos dos KPIs. Inclusive os programas em Python
descritos anteriormente são executados por scripts em Shell, que antes verificam se
o programa já está em execução através do comando pgrep e também possibilitam
que vários scripts rodem em paralelo através da ferramenta GNU Parallel, a próxima
a ser descrita.
40
GNU Parallel
GNU Parallel [36] é uma ferramenta de código aberto que possibilita a execução
de vários processos em paralelo, tanto no mesmo computador, quanto em computadores diferentes.
Ela foi utilizada no trabalho para rodar em paralelo scripts em Shell e em Python.
A execução é feita usando o comando parallel, podendo opcionalmente ser passado como parâmetro a quantidade de processos que deseja-se serem executados em
paralelo. Por padrão, o número de processos executados em paralelo é o mesmo da
quantidade de núcleos do processador.
Saxon
Saxon [37] é um processador XSLT (eXtensible Stylesheet Language for Transformation) OpenSource(Código Aberto) desenvolvido na linguagem Java pela empresa
Saxonica. Com ele é possível transformar arquivos no formato XML para os formatos CSV, texto ou HTML. O Saxon é executado por linha de comando, devendo ter
um ambiente Java habilitado para o seu funcionamento.
O Saxon realiza a transformação de um arquivo XML para o formato CSV conforme o diagrama da Figura 3.14:
Figura 3.14: Diagrama de um processador XSLT.
Para o Saxon executar a transformação é preciso de um arquivo externo XSL
contendo um código na linguagem XSLT. A linguagem XSLT serve justamente para
transformar arquivos XML em qualquer outro formato, contendo uma sintaxe simples para quem já está familiarizado com a estrutura de um XML. No trabalho foi
utilizado o programa Stylus Studio [38] para criar o template contendo o código
XSLT necessário para a transformação.
41
Abaixo um exemplo do uso do Saxon para transformar uma fonte XML em um
arquivo CSV:
java -cp saxon9he.jar net.sf.saxon.Transform -t -s:fonte.xml \
-xsl:estilo.xls -o:arquivo.csv
LibXML
Antes de ser feita conversão do arquivo XML é necessário validá-lo para garantir
sua integridade, conforme já havia sido indicado no diagrama de fluxos da Figura 3.3.
Para que um documento XML seja validado é preciso usar a Definição do Tipo do
Documento ou, originalmente, DTD (Document Type Definition).
O propósito do DTD é definir uma construção de blocos válida para um documento XML, e ela define a estrutura do documento usando uma lista de elementos
válidos. O DTD pode ser declarado dentro de um documento XML ou em um
arquivo externo. [26]
O DTD permite descrever cada marca e fornecer regras para interpretar cada informação usada em um arquivo XML. Para o trabalho foi usado um arquivo externo,
apresentado na Figura 3.15:
Figura 3.15: Arquivo DTD usado para validar um arquivo XML.
Para fazer a validação do XML foi usada a libXML [39], uma ferramenta gratuita
escrita na linguagem C que faz diversas análises de arquivos no formato XML. É
42
preciso colocar o arquivo DTD no diretório indicado no arquivo XML, e executar o
comando xmllint, seguido da opção valid e do nome do arquivo:
xmllint --valid arquivo.xml
PostgreSQL
PostgreSQL [40] é um sistema gerenciador de Banco de Dados relacional e orientado a objetos. Um de seus atrativos é ser otimizado para aplicações complexas, isto
é, que envolvem grandes volumes de dados ou que tratam de informações críticas.
Além disso, trata-se de um Banco de Dados versátil, seguro, com suporte a grande
parte do padrão SQL, gratuito e de código aberto. [41]
Entre suas características e funcionalidades, tem-se:
• Compatibilidade multi-plataforma, ou seja, executa em vários sistema operacionais
• Consultas complexas em SQL
• Chaves estrangeiras
• Integridade transacional
• Gatilhos
• Visões
• Funções
• Tipos de Dados
• Operadores
• Funções de agregação
• Métodos de índice
• Linguagens procedurais (PL/SQL, PL/Python, PL/Java, PL/Perl)
A escolha do PostgreSQL como o gerenciador de Banco de Dados do trabalho foi
devido ao fato de ser bastante otimizado para trabalhar com um grande volume de
dados, tendo também bastante flexibilidade nas suas configurações de desempenho.
Foram criadas diversas funções na linguagem PL/SQL para os cálculos dos KPIs e
dos principais ofensores, que depois de armazenados podem ser chamados por linha
de comando, passando opcionalmente parâmetros como data, cidade, RNC, etc.
43
A linguagem PL/SQL pode ser entendida como uma extensão da linguagem SQL,
adicionada de funcionalidades que a tornam uma linguagem de programação completa: controle de fluxo, tratamento de exceções, orientação a objetos, entre outras.
Com a PL/SQL podemos escrever programas inteiros, desde os mais simples até os
mais sofisticados. [42]
Para auxiliar no uso do PostgreSQL foi utilizada a GUI (Graphical User Interface)
pgAdmin [43], que é uma plataforma grátis desenvolvida em C++ e a mais popular
para o PostgreSQL. O pgAdmin está disponível para os sistemas operacionais Linux,
FreeBSD, Solaris, Mac OSX e Windows, com isso foi possível acessar remotamente o
Banco de Dados para realizar consultas em SQL ou desenvolver funções em PL/SQL
mesmo de uma máquina com Windows.
Pg_bulkload
O pg_bulkload [11] é uma ferramenta para o PostgreSQL desenvolvida para
fazer a carga de uma grande quantidade de dados em um Banco de Dados. É uma
alternativa mais rápida para o comando COPY [44] do PostgreSQL, além de possuir
outras funcionalidades como substituir linhas duplicadas e ignorar eventuais erros
de parser.
A Figura 3.16 apresenta o tempo de carga para 4 GB de dados em tabelas vazias
indexadas e não-indexadas, usando o comando COPY e usando o pg_bulkload. Notase que tabelas com índices demoram mais tempo para carregar os dados, mas é uma
boa prática indexar algumas colunas da tabela para otimizar as consultas via SQL.
Existem dois métodos de escrita para o pg_bulkload, direct e parallel, sendo o
parallel o mais rápido e que pode durar menos de 50% do tempo do COPY.
44
Figura 3.16: Tempo de carga de 4 GB de dados, em segundos. Fonte: página oficial
do pg_bulkload [11].
Para a última etapa do processo ETL foi utilizado o pg_bulkload para carregar
os dados dos arquivos CSV nas respectivas tabelas do Banco de Dados através do
comando:
pg_bulkload -i arquivo.csv -O tabela_do_bd arquivo_controle.ctl
O arquivo de controle no formato CTL (Control Temporal Logic) contém as opções
utilizadas para o pg_bulkload, como o método de escrita (parallel ), tipo de arquivo
(CSV), além de outras opções como ignorar erros e substituir linhas duplicadas por
linhas novas.
O pg_bulkload também foi usado no trabalho para carregar os KPIs calculados
nas tabelas de KPIs do Banco de Dados. Para isso foram testados cinco métodos
diferentes:
1. Utilizando a instrução SQL INSERT [45]
2. Utilizando o comando COPY do PostgreSQL
3. Utilizando o comando COPY binário do PostgreSQL
4. Com o pg_bulkload para um arquivo CSV
5. Com o pg_bulkload para uma função
45
Para os métodos 2, 3 e 4 é preciso antes criar um arquivo intermediário (CSV
ou binário) usando o comando COPY antes de carregá-lo no Banco de Dados. Os
métodos 1 e 5 possuem a vantagem de não precisar de arquivos intermediários. A
Figura 3.17 apresenta os tempo necessários para carregar 1 GB de dados em tabelas
vazias e em tabelas já com 4 GB de dados para cada um dos métodos citados:
Figura 3.17: Tempo de carga de 1 GB de dados para diferentes métodos de inserção,
em minutos.
O método de inserção usando o pg_bulkload com uma função como tipo de entrada foi o mais rápido nos testes tanto para tabelas vazias quanto para tabelas já
populadas. Essa foi a solução utilizada no trabalho, sendo necessário então criar as
funções em PL/SQL com as fórmulas necessárias para o cálculo dos KPIs e executar
o seguinte comando para inserir os dados com KPIs calculados nas tabelas de KPIs:
pg_bulkload -i nome_funcao() -O tabela_kpi arquivo_controle.ctl
CodeIgniter
O CodeIgniter [46] é um framework de desenvolvimento gratuito de aplicações
em PHP. Seu objetivo é disponibilizar um framework de máxima performance e
capacidade, que seja flexível e o mais leve possível. A primeira versão pública do
CodeIgniter foi lançada em 28 de fevereiro de 2006.
O CodeIgniter permite que se mantenha o foco em um projeto, minimizando a
quantidade de código necessário para uma dada tarefa. CodeIgniter foi desenvolvido
sob o paradigma da programação Orientada a Objetos sob o padrão de arquitetura
de software MVC.
A aplicação MVC do trabalho descrita na seção 3.1.3 foi construída utilizando o
CodeIgniter devido à ampla biblioteca de classes disponíveis neste framework, que
46
possuem uma estrutura de atributos e métodos que facilitam a implementação de
tarefas comuns ao desenvolvimento de qualquer uma das aplicações necessárias, tais
como, conexão com o Banco de Dados, tratamento e consultas de dados retornados,
construção de formulários e outros conteúdos HTML para a criação da interface
visual da aplicação, entre várias outras. Além disso, possui um conjunto de helpers,
que pode ser compreendido como bibliotecas de funções, agrupadas de acordo com
suas finalidades.
As URL’s geradas pelo CodeIngniter são limpas e amigáveis. Está entre os frameworks PHP mais utilizados em 2016 [47].
Apache
O servidor Apache [48] é o mais bem sucedido servidor web livre. Foi criado
em 1995 por Rob McCool. Em uma pesquisa realizada em novembro de 2015 [49],
foi constatado que a utilização do Apache representava cerca de 50% dos servidores
ativos no mundo.
Assim como qualquer servidor do tipo, o Apache é responsável por disponibilizar
páginas e todos os recursos que podem ser acessados por um internauta. Envio de
e-mails, mensagens, compras online e diversas outras funções podem ser executadas
graças a servidores como o Apache. O que vale destacar no Apache é que, apesar de
tudo, ele é distribuído sob a licença GNU, ou seja, é gratuito e pode ser estudado
e modificado através de seu código fonte por qualquer pessoa. É graças a essa
característica que o software foi (e continua sendo) melhorado com o passar dos anos.
Graças ao trabalho muitas vezes voluntário de vários desenvolvedores, o Apache
continua sendo o servidor Web mais usado no mundo.
MobaXterm
O MobaXterm [50] é um terminal de simulação OpenSource para Windows, usado
principalmente para estabelecer conexões seguras de acesso remoto a servidores via
SSH (Secure Shell ). Permite abrir múltiplas abas para executar simultaneamente
várias operações diferentes. Eles rodam com o servidor gratuito Xorg, para exportar o display Unix/Linux e vários dos novos comandos GNU Unix. Foi utilizado
no trabalho para poder acessar os servidores Linux via SSH através de máquinas
Windows.
47
Telepot
Com o intuito de monitorar cada etapa em todos os processos do trabalho, foi
utilizado o Telepot [51], um framework em Python que funciona como um bot utilizando a API (Application Programming Interface) do Telegram [52], um aplicativo
de mensagens para celular.
Foram desenvolvidos scripts em Python para o envio automático de mensagens
para um canal no Telegram usando a biblioteca do Telepot. A Figura 3.18 mostra
um exemplo de mensagem indicando o andamento de cada parte do processo ETL,
e a Figura 3.19 apresenta um exemplo de mensagem indicando para quais horas os
KPIs já foram calculados.
Figura 3.18: Monitoramento do ETL
3.2.3
Figura 3.19: Monitoramento dos KPIs
Funcionamento
Antes de começar o processo ETL, é necessário criar todas as tabelas necessárias
no PostgreSQL, que são: as tabelas de todas as famílias de contadores de performance, as tabelas para todos os grupos de KPIs, as tabelas que irão armazenar os
dados de configuração e as tabelas de controle que irão armazenar o log de arquivos
baixados, convertidos e carregados no Banco de Dados.
48
Após todas as tabelas terem sido criadas, os scripts desenvolvidos para o processo
ETL são agendados na cron [53] do Linux. Para extrair os arquivos dos quatro
servidores do EMS, é preciso que uma VPN (Virtual Private Network ) da empresa
esteja conectada. Os scripts que fazem o FTP são agendados para serem executados
a cada 10 minutos no servidor de arquivos.
Os outros scripts do processo ETL que fazem a conversão e o parser no servidor de
arquivos são agendados para serem executados a cada minuto. Os arquivos prontos
para serem carregados no Data Warehouse são enviados para o servidor de dados
onde os scripts de carga são agendados para serem executados a cada dois minutos.
Também neste servidor é feito o processamento dos dados, já descritos anteriormente
conforme a Figura 3.12, agendados para serem executados a cada 30 minutos. Como
o processo de carga e de cálculos dos KPIs são muito pesados e devido à limitação dos
processadores usado no trabalho, foi feito um controle simples para que os mesmos
não sejam executados ao mesmo tempo: enquanto os cálculos dos KPIs ocorrem é
criado um arquivo temporário em um diretório específico impedindo que carga dos
dados seja inicializada.
Logo após qualquer etapa do processamento dos dados, são executados os comandos SQL Vacuum e Analyze, para limpeza dos registros e atualização das estatísticas
dos cálculos, a fim de sempre otimizar o desempenho do Banco de Dados.
49
Capítulo 4
Resultados e Discussões
A ferramenta desenvolvida no trabalho apresentou resultados satisfatórios, incluindo os seguintes recursos:
• Interface gráfica contendo os KPIs básicos para as tecnologias UMTS e LTE.
• Os mesmos KPIs podem ser acessados quando agrupados por célula, região,
RNC, cidade, meias-horas, dia e semana.
• Ranqueamento das piores células, mostrando os maiores ofensores por RNC
(UMTS) e região (LTE) para um determinado KPI, e quanto este KPI irá
melhorar quando corrigidas suas falhas.
• KPIs personalizados, agregados por dia e semana e mostrando também seus
piores ofensores por RNC.
A Figura 4.1 mostra a interface do pgAdmin, destacando como foi feita a organização do Banco de Dados no PostgreSQL. Divisão em esquemas por tecnologia
(UMTS e LTE) e por KPI, control, counter, configuration etc serviram para organizar e facilitar o acesso aos dados do banco.
50
Figura 4.1: Organização do Banco de Dados.
A Figura 4.2 exibe a página inicial da interface web da ferramenta. Por padrão é
exibida uma tabela com os KPIs básicos da última semana calculada, por região. No
menu superior é possível selecionar se deseja exibir os indicadores de performance
agrupados por dia ou semana, ou se deseja exibir outros indicadores personalizados,
como mostra a Figura 4.3.
Figura 4.2: Página inicial da ferramenta.
51
Figura 4.3: Menu com os indicadores de performance.
No menu superior à direita é possível selecionar a tecnologia (Figura 4.4) e as
agregações desejadas dos KPIs, por região, RNC ou cidade (Figura 4.5).
Figura 4.4: Tecnologias disponíveis.
Figura 4.5: Agregações de KPIs disponíveis.
52
Nas seções seguintes serão destacados alguns dos principais dashboards gerados
no trabalho.
4.1
KPIs básicos para tecnologia UMTS
A seguir, serão apresentados exemplos de relatórios semanais contendo os gráficos de KPIs básicos, sendo eles de Accessibility e Retainability (Figura 4.6); Traffic
(Figura 4.7); Service Integrity e Retention (Figura 4.8); Mobility (Figura 4.9); Availability e Coverage (Figura 4.10). Nesses exemplos aparecem as variações dos KPIs
diários para toda a rede UMTS.
Figura 4.6: KPIs básicos de Accessibility e Retainability.
Figura 4.7: KPIs básicos de Traffic.
53
Figura 4.8: KPIs básicos de Service Integrity e Retention.
Figura 4.9: KPIs básicos de Mobility.
Figura 4.10: KPIs básicos de Availability e Coverage.
54
4.2
Principais ofensores por RNC
Uma das funcionalidades desenvolvidas no trabalho foi a possibilidade fazer drilldown em cada um dos KPIs de um determinado RNC e descobrir quais foram
suas piores células durante um dia específico. Em geral, a pior célula é aquela que
apresenta mais falhas. É feito o ranqueamento dessas células e qual o peso que todas
as falhas de cada célula tiveram no KPI do RNC, e o quanto o KPI do RNC irá
melhorar quando as falhas forem corrigidas. Como exemplo, a Figura 4.11 mostra
as piores células para o RNC RNCMG01 do KPI Acessibilidade RRC.
Figura 4.11: Ranqueamento das piores células de um RNC.
É possível também fazer drill-down em uma célula específica e descobrir qual foi a
variação no KPI daquela célula durante as horas do dia, como mostra a Figura 4.12.
Figura 4.12: KPI de uma célula ao longo de um dia.
55
4.3
KPIs personalizados para tecnologia UMTS
Os KPIs básicos apresentados anteriormente servem, em teoria, para medir o
desempenho da rede de telefonia. Portanto bons níveis do KPI deveriam também
garantir a satisfação dos usuários destas redes. Porém esse conceito não é um consenso em todas as empresas, por conta disso algumas delas criaram KPIs próprios
para chegar a um indicador que se aproxime mais do nível de satisfação do usuário.
Alguns desses KPIs personalizados foram feitos para o trabalho, chamados de
AMX NQI. Encontram-se na Figura 4.13, e os ofensores do KPI QDA PS com o
ranqueamento das piores células do RNC RNCMG01 pode ser visto na Figura 4.14.
Figura 4.13: KPIs personalizados.
Figura 4.14: Piores células para o KPI QDA PS.
56
4.4
KPIs básicos para tecnologia LTE
A Figura 4.15 mostra KPIs básicos gerados para rede LTE.
Figura 4.15: KPIs básicos da rede LTE.
Seguindo o mesmo padrão da rede UMTS, a ferramenta também gerou relatórios
dos KPIs da rede LTE agrupados por região, cidade e UF, além de tabelas com as
piores células de cada região.
4.5
Discussões
A ferramenta desenvolvida neste trabalho cumpriu seu objetivo de gerar dashboards com indicadores de performance rede de telefonia celular para as tecnologias
UMTS e LTE, através de uma interface web. O atraso apresentado para os cálculos
KPIs foi de no mínimo duas horas (diferença entre a hora atual e a hora do último
KPI calculado). Apesar dessa limitação, os resultados foram bastante satisfatórios,
sendo possível consultar não só os KPIs do dia atual como os dos dias retroativos,
além de diversas outras agregações e funcionalidades, tais como KPIs das cidades,
RNCs e regiões, seus principais ofensores, consulta aos parâmetros e configurações
da rede, etc.
57
O trabalho inicial de extração, transformação e carga em um modelo Data Warehouse. Desde o início teve-se a dúvida se seria melhor usar uma ferramenta paga
para fazer o processo de ETL ou se criava-se todos os códigos para cada etapa do
processo. O segundo método foi escolhido porque observou-se que o processo ficava
mais rápido e consumia menos recursos visto as limitações de processamento dos
computadores utilizados. Um processo mais rápido é fundamental para garantir que
os KPIs sejam calculados com o menor atraso possível no trabalho.
58
Capítulo 5
Conclusões e Trabalhos Futuros
5.1
Conclusões
Todas as ferramentas escolhidas para o processo ETL e para aplicação MVC se
mostraram úteis e funcionais. Porém uma das dificuldades para desenvolver todo
o processo com várias ferramentas diferentes foi a falta de controle externo para
gerenciar todas as etapas do processo, algo presente nas ferramentas pagas. A
forma escolhida para contornar esse problema foi através de envio automático de
mensagens pelo Telegram para monitorar cada etapa.
O trabalho todo foi feito usando dados de performance e de configuração presentes
nos servidores externos da empresa Huawei, mas pode ser estendido para qualquer
empresa de telecomunicações que forneça esses arquivos nos formatos XML ou CSV.
5.2
Trabalhos futuros
Mesmo com boa parte dos objetivos iniciais cumpridos, é importante ressaltar
que muitas outras funcionalidades podem ser incorporadas na ferramenta, como
cálculo dos ofensores para outros tipos de agregações (cidade, semana, mês, etc),
indicadores de capacidade das redes de telefonia, georreferenciamento de antenas,
adição de outras tecnologias como GSM, etc.
Outra das principais e possíveis mudanças no trabalho é adotar a modelagem multidimensional. A modelagem multidimensional é a técnica de projeto mais freqüentemente utilizada para a construção de um Data Warehouse. O objetivo é buscar
um padrão de apresentação de dados que seja facilmente visualizado pelo usuário
final e que possua um bom desempenho para consultas.
59
O modelo dimensional é formado por uma tabela central (tabela de fatos) e várias outras a ela interligadas (tabelas de dimensão), sempre por meio de chaves
especiais, que associam o fato a uma dimensão do cubo. O conceito de dimensão
pode ser entendido como a organização dos dados, determinando possíveis consultas/cruzamentos. Para o trabalho, por exemplo, poderiam ser RNC, Node-B, data,
hora e cidade. Cada dimensão pode ainda ter seus elementos, chamados membros,
organizados em diferentes níveis hierárquicos. As tabelas de dimensão geralmente
são tabelas simples em relação ao número de linhas, mas podem conter um número
muito grande de colunas. [8, 54]
A tabela de fatos é a principal tabela de um modelo dimensional, onde as medições numéricas de interesse da empresa ficam armazenadas. A palavra “fato” é
usada para representar uma medição de negócio da empresa, ou seja, cada linha
da tabela representa uma medição. A tabela de fatos registra os fatos que serão
analisados e os dados a serem agrupados. A Figura 5.1 mostra uma futura proposta
de modelo multidimensional para o trabalho, utilizando o chamado Modelo Estrela
(Star Schema).
Figura 5.1: Modelo Multidimensional proposto. [8]
Como consulta a banco de dados multidimensionais temos um conjunto de aplicações que se denominam ferramentas OLAP (Online Analytical Processing - Processamento Analítico Online). OLAP é a denominação que se dá a uma ferramenta que
tem a capacidade de manipular e analisar um grande volume de dados sob múltiplas
perspectivas, chamada de cubo. O principal benefício do uso de uma ferramenta
60
OLAP é a disponibilidade de métodos para acessar, visualizar e analisar dados com
muita flexibilidade e velocidade.
61
Referências Bibliográficas
[1] AMERICAS, G.
“Evolução Tecnológica da 3GPP”. 2016.
Disponível
em:
<http://www.4gamericas.org/pt-br/resources/
technology-education/3gpp-technology-evolution/>.
(Acesso
em 12 de maio de 2016).
[2] TELECO. “Estatísiticas de Celular no Mundo”. 2016. Disponível em: <http:
//www.teleco.com.br/pais/celular.asp>. (Acesso em 08 de maio de
2016).
[3] AMERICAS, G. “Mobile Technology Statistics - Global”. 2016. Disponível em: <http://www.4gamericas.org/en/resources/statistics/
statistics-global/>. (Acesso em 13 de maio de 2016).
[4] AMERICAS, G. “Mercado móvel Brasil 2014 - 2019”. 2015. Disponível
em: <http://www.4gamericas.org/pt-br/resources/infographics/
mercado-movel-argentina-2014-20191/>. (Acesso em 09 de maio de
2016).
[5] PERINI, P. UMTS/HSDPA Protocols, Procedures and Operations. Relatório
técnico, Qualcomm Wireless Academy, Washington, D.C., May 2003.
[6] TELECOMHALL. “O que é RRC e RAB?” 2011. Disponível em: <http:
//www.telecomhall.com/BR/o-que-e-rtwp.aspx>. (Acesso em 18 de
maio de 2016).
[7] TELECO. “Redes 3G: Tipos de Handover”. 2016. Disponível em: <http://www.
teleco.com.br/tutoriais/tutorial3ghandover/pagina_2.asp>.
(Acesso em 18 de maio de 2016).
[8] DOS SANTOS, V. V. “Data Warehouse: Análise da Performance de Ferramentas
de ETL”. 2013. Disponível em: <http://www.uniedu.sed.sc.gov.br/
wp-content/uploads/2013/10/Valdinei-Valmir-dos-Santos.pdf>.
(Acesso em 20 de maio de 2016).
62
[9] ELIAS, D. “Entendendo o processo de ETL”. 2015. Disponível em: <http:
//corporate.canaltech.com.br/noticia/business-intelligence/
entendendo-o-processo-de-etl-22850/>. (Acesso em 22 de maio de
2016).
[10] ZEMEL, T.
“MVC (Model – View – Controller)”. 2009.
Disponível em:
<http://codeigniterbrasil.com/passos-iniciais/
mvc-model-view-controller/>. (Acesso em 20 de junho de 2016).
[11] “pg_bulkload: Project Home Page”. Disponível em: <http://ossc-db.
github.io/pg_bulkload/index.html>. (Acesso em 03 de julho de
2016).
[12] KIMBALL, R., CASERTA, J. The Data Warehouse ETL Toolkit. USA, Wiley,
2004.
[13] INTELLIGENCE, G. “Definitive data and analysis for the mobile industry”. 2016. Disponível em: <https://www.gsmaintelligence.com/>.
(Acesso em 07 de maio de 2016).
[14] BSC6900 UMTS Product Documentation, v900r015c00 ed. Huawei, April 2015.
[15] GABARDO, A. C. PHP e MVC com CodeIgniter. Brasil, NOVATEC, 2012.
[16] AQUINO, G. P. “Curso Tecnologia Celular 4G-LTE”. 2013. Inatel.
[17] GUSSEN, C. M. G. “Estudo e Simulação da Camada Física do 3G–LTE na
Conexão Downlink ”. 2009. Disponível em: <http://monografias.poli.
ufrj.br/monografias/monopoli10002720.pdf>.
[18] WORLDOMETERS. “Current World Population”. Disponível em: <http://
www.worldometers.info/world-population/>. (Acesso em 02 de julho
de 2016).
[19] D’ÁVILA, D. C. K. “UTRAN - UMTS Terrestrial Radio Access Network |
3G Wireless | O que é”. 2009. Disponível em: <http://www.cedet.com.
br/index.php?/O-que-e/3G-Wireless/utran.html>. (Acesso em 16 de
março de 2016).
[20] TELECOMHALL. “O que é RRC e RAB?” 2013. Disponível em: <http:
//www.telecomhall.com/br/o-que-e-rrc-e-rab.aspx>. (Acesso em
18 de maio de 2016).
63
[21] DA NET, O.
“O que é Business Intelligence?”
2014.
Disponível
em:
<https://www.oficinadanet.com.br/post/
13153-o-que-e-business-intelligence>. (Acesso em 20 de maio de
2016).
[22] RIBEIRO, V.
“O que é ETL?” 2011.
Disponível em: <https:
//vivianeribeiro1.wordpress.com/2011/06/28/o-que-e-etl-2/>.
(Acesso em 10 de março de 2016).
[23] OLIVEIRA, M., 2008. Disponível em: <http://www.datawarehouse.inf.
br/Academicos/A%20PUBLICAR_DATA_WAREHOUSE_MARCELL_OLIVEIRA.
pdf>. (Acesso em 23 de maio de 2016).
[24] WIKIPÉDIA, A. E. L. “Armazém de dados”. Disponível em: <https:
//pt.wikipedia.org/wiki/Armaz%C3%A9m_de_dados>. (Acesso em 23
de maio de 2016).
[25] BORGES, L.
“Dicas importantes para elaborar um dashboard útil
e profissional”. Disponível em:
<http://blog.luz.vc/excel/
dicas-importantes-para-elaborar-um-dashboard-util-e-profissional/>.
(Acesso em 30 de maio de 2016).
[26] MACORATTI.NET. “XML – Introdução e conceitos básicos”. Disponível em:
<http://www.macoratti.net/xml.htm>. (Acesso em 31 de maio de
2016).
[27] DA SILVA ANTUNES, D. L. “Sistema de Gerenciamento e Automatização
de Cálculo de Indicadores (SGACI)”. 2009. Disponível em: <http://
monografias.poli.ufrj.br/monografias/monopoli10002083.pdf>.
[28] “Pentaho | Data Integration, Business Analytics and Big Data Leaders”. Disponível em: <http://www.pentaho.com/>. (Acesso em 1° de julho de
2016).
[29] “Python Programming Language”. . Disponível em: <https://www.python.
org/>. (Acesso em 1° de julho de 2016).
[30] BRASIL, P. “PerguntasFrequentes/SobrePython”. Disponível em: <http://
wiki.python.org.br/PerguntasFrequentes/SobrePython>. (Acesso
em 1° de julho de 2016).
[31] CASS, S.
“The
Disponível em:
2015 Top Ten Programming Languages”. 2015.
<http://spectrum.ieee.org/computing/software/
64
the-2015-top-ten-programming-languages>. (Acesso em 1° de julho
de 2016).
[32] “ftplib - FTP protocol client - Python documentation”. Disponível em: <https:
//docs.python.org/2.7/library/ftplib.html>. (Acesso em 02 de julho de 2016).
[33] “csv - CSV File Reading and Writing - Python documentation”. . Disponível
em: <https://docs.python.org/2/library/csv.html>. (Acesso em
02 de julho de 2016).
[34] “PostgreSQL + Python | Psycopg”. Disponível em: <http://initd.org/
psycopg/>. (Acesso em 02 de julho de 2016).
[35] “Bash - GNU Project - Free Software Foundation”. Disponível em: <https:
//www.gnu.org/software/bash/>. (Acesso em 1° de julho de 2016).
[36] “GNU Parallel - GNU Project - Free Software Foundation”. Disponível em:
<https://www.gnu.org/software/parallel/>. (Acesso em 02 de julho
de 2016).
[37] “Saxonica - XSLT and Xquery Processing”. Disponível em: <http://www.
saxonica.com/>. (Acesso em 02 de julho de 2016).
[38] “XML Editor, XML Tools, and XQuery - Stylus Studio”. Disponível em: <http:
//www.stylusstudio.com/>. (Acesso em 02 de julho de 2016).
[39] “libXML - The XML C parser and toolkit of Gnome”. Disponível em: <http:
//xmlsoft.org/>. (Acesso em 02 de julho de 2016).
[40] “PostgreSQL: The world’s most advanced open source database”. Disponível em:
<https://www.postgresql.org/>. (Acesso em 1° de julho de 2016).
[41] ALECRIM, E. “Banco de dados MySQL e PostgreSQL”. 2008. Disponível em:
<http://www.infowester.com/postgremysql.php>. (Acesso em 03 de
julho de 2016).
[42] CORRÊA, E. “Conhecendo o PL/SQL”. Disponível em: <http://www.
devmedia.com.br/conhecendo-o-pl-sql/24763>. (Acesso em 03 de julho de 2016).
[43] “pgAdmin: PostgreSQL administration and management tools”. Disponível em:
<https://www.pgadmin.org/>. (Acesso em 03 de julho de 2016).
65
[44] “PostgreSQL: Documentation: 9.5: COPY”. Disponível em: <https://www.
postgresql.org/docs/current/static/sql-copy.html>. (Acesso em
10 de julho de 2016).
[45] “PostgreSQL: Documentation: 9.5: INSERT”. Disponível em: <https://www.
postgresql.org/docs/current/static/sql-insert.html>. (Acesso
em 10 de julho de 2016).
[46] “CodeIgniter Web Framework”. Disponível em: <https://www.codeigniter.
com/>. (Acesso em 1° de julho de 2016).
[47] REVISIONS, W.
“Best PHP Framework for 2016”. 2016.
Disponível
em:
<http://webrevisions.com/tutorials/
php-framework-the-best-php-framework-for-2013/>.
(Acesso
em 11 de julho de 2016).
[48] “The Apache HTTP Server Project”. Disponível em:
apache.org/>. (Acesso em 11 de julho de 2016).
<https://httpd.
[49] NETCRAFT.
“November 2015 Web Server Survey”. 2015.
Disponível em:
<http://news.netcraft.com/archives/2015/11/16/
november-2015-web-server-survey.html>. (Acesso em 11 de julho de
2016).
[50] “MobaXterm free Xserver and tabbed SSH client for Windows”. Disponível em:
<http://mobaxterm.mobatek.net/>. (Acesso em 11 de julho de 2016).
[51] “Python framework for Telegram Bot API”. . Disponível em: <https:
//github.com/nickoala/telepot>. (Acesso em 11 de julho de 2016).
[52] “Telegram Messenger”. . Disponível em: <https://telegram.org/>. (Acesso
em 11 de julho de 2016).
[53] “Agendando Tarefas com cron e atd”. Disponível em:
<https:
//www.debian.org/doc/manuals/debian-handbook/sect.
task-scheduling-cron-atd.pt-br.html>.
(Acesso em 12 de julho de 2016).
[54] ROHDEN, R. B.
“Banco de Dados: Relacional X Multidimensional”. Disponível em:
<https://pt.scribd.com/doc/22742853/
Artigo-Banco-de-Dados-Relacional-vs-Multidimensional>.
(Acesso em 14 de julho de 2016).
66
Apêndice A
Códigos Fonte - ETL
Neste apêndice se encontram os códigos fonte do processo ETL, contendo as
partes de extração, conversão e carga. Além desses códigos fonte, também está
disponível o script em Shell que faz a execução de cada uma dessas partes.
67
A.1
A.1.1
1
2
3
4
5
6
7
8
Extração
Ftp.py
### i m p o r t s n e c e s s á r i o s ###
from f t p l i b import FTP
import psycopg2
import time
import d a t e t i m e
import o s
import s h u t i l
import r e
9
10
11
12
13
14
15
16
d e f getpm ( ip , u s e r , password , pmfolder , p r e f i x ) : # f u n ç ã o que f a z o FTP
### d e f i n i ç õ e s dos a r q u i v o s e d i r e t ó r i o s ###
l o c a l d i r = ' / e t l /umts/ p e r f o r m a n c e /temp/ f t p / '
f i n a l d i r = ' / e t l /umts/ p e r f o r m a n c e /raw/ '
c o n v e r t e d d i r = ' / e t l /umts/ p e r f o r m a n c e / c o n v e r t e d / '
b a c k u p d i r = ' / e t l / backup /umts/ p e r f o r m a n c e / '
l o g f i l e = ' /home/ e t l u s e r / l o g / f t p . l o g '
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
try :
conn = psycopg2 . c o n n e c t ( "dbname=' p o s t g r e s ' u s e r =' p o s t g r e s ' h o s t=
host_ip password=password_db " ) # c o n e c t a no Banco de Dados
except :
p r i n t ( ' db e r r o r ' )
s q l l o a d e d = "SELECT CONCAT( o s s , '_ ' , f i l e ) FROM umts_control . l o g _ e t l
WHERE d a t e t i m e >= ( c u r r e n t _ d a t e − i n t e r v a l ' 4 day ' ) AND o s s = '% s '
ORDER BY f i l e ; " % s e r v e r
s q l s e l e c t e d = "SELECT DISTINCT(CONCAT( f u n c t i o n s u b s e t _ i d , '_ ' , gp ) ) FROM
umts_control . c o u n t e r _ r e f e r e n c e WHERE c o u n t e r _ e n a b l e = 'TRUE ' ; "
c u r s o r = conn . c u r s o r ( )
cursor . execute ( sqlloaded )
time . s l e e p ( 0 . 5 )
s q l l o a d e d f i l e s = [ item [ 0 ] f o r item i n c u r s o r . f e t c h a l l ( ) ]
cursor . execute ( s q l s e l e c t e d )
time . s l e e p ( 0 . 5 )
s q l s e l e c t e d s u b s e t = [ item [ 0 ] f o r item i n c u r s o r . f e t c h a l l ( ) ]
f t p = FTP( ip , u s e r , password )
ftp . login ()
f t p . cwd ( p m f o l d e r )
d i r l i s t = ftp . nlst ()
35
36
37
38
f o r dirname i n d i r l i s t :
matchdir = r e . match ( ' . ∗ pmexport_ . ∗ ' , dirname )
i f matchdir :
68
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
f t p . cwd ( p m f o l d e r+dirname )
filenames = ftp . nlst ()
f i l e n a m e s = s o r t e d ( f i l e n a m e s , key=lambda x : x . s p l i t ( "_" ) [ 3 ] )
for filename in filenames :
OutputFilename = p r e f i x + f i l e n a m e
i f OutputFilename not i n s q l l o a d e d f i l e s :
for s e l e c t i o n in s q l s e l e c t e d s u b s e t :
m a t c h f i l e = r e . match ( ' . ∗ '+s t r ( s e l e c t i o n )+ ' . ∗ ' , f i l e n a m e )
i f matchfile :
i f ( o s . path . e x i s t s ( f i n a l d i r+OutputFilename ) o r o s . path .
e x i s t s ( c o n v e r t e d d i r+OutputFilename+ ' . c s v ' ) ) :
p r i n t ( '%s E x i s t s ' % OutputFilename )
else :
p r i n t ( ' Opening l o c a l f i l e ' + OutputFilename )
f i l e = open ( l o c a l d i r +OutputFilename , 'wb ' )
p r i n t ( ' G e t t i n g ' + OutputFilename )
try :
f t p . r e t r b i n a r y ( 'RETR %s ' % f i l e n a m e , f i l e . w r i t e )
c u r s o r . e x e c u t e ( 'INSERT INTO umts_control . l o g _ e t l ( o s s
, f i l e , f s s , datetime , f t p ) VALUES (%s , %s , %s , %s , %s ) ' , ( p r e f i x . s p l i t (
'_ ' ) [ 0 ] , f i l e n a m e , f i l e n a m e . s p l i t ( '_ ' ) [ 1 ] , f i l e n a m e . s p l i t ( '_ ' ) [ 3 ] [ 0 : 4 ]
+ '− ' + f i l e n a m e . s p l i t ( '_ ' ) [ 3 ] [ 4 : 6 ] + '− ' + f i l e n a m e . s p l i t ( '_ ' )
[ 3 ] [ 6 : 8 ] + ' ' + f i l e n a m e . s p l i t ( '_ ' ) [ 3 ] [ 8 : 1 0 ] + ' : ' + f i l e n a m e .
s p l i t ( '_ ' ) [ 3 ] [ 1 0 : 1 2 ] + ' : 0 0 ' , s t r ( d a t e t i m e . d a t e t i m e . now ( ) ) [ 0 : 1 9 ] ) )
conn . commit ( )
time . s l e e p ( 0 . 5 )
f i l e . close ()
o s . rename ( l o c a l d i r+OutputFilename , f i n a l d i r+
OutputFilename )
s h u t i l . c o p y f i l e ( f i n a l d i r+OutputFilename , b a c k u p d i r+
OutputFilename )
time . s l e e p ( 0 . 5 )
l o g = open ( l o g f i l e , " a " )
l o g . w r i t e ( d a t e t i m e . d a t e t i m e . now ( ) . s t r f t i m e ( '%d/%m/%Y
− %H:%M:%S ' )+" : a r q u i v o "+OutputFilename+" baixado \n" )
log . close ()
except :
print ( " Error " )
print ( ' Closing f i l e ' + filename )
f i l e . close ()
ftp . quit ()
cursor . close ()
72
73
getpm ( s e r v e r _ i p , s e r v e r _ u s e r , server_password , server_path , s e r v e r + '_ ' )
69
A.1.2
1
Auto-get-performance.sh
#! / b i n / bash
2
3
4
LOG=" /home/ e t l u s e r / l o g / auto−get−p e r f o r m a n c e . l o g "
FTPLOG=" /home/ e t l u s e r / l o g / f t p . l o g "
5
6
7
8
9
10
### d e f i n i ç õ e s ###
h o r a r i o ( ) # f u n ç ã o que pega a data a t u a l
{
d a t e +%d/%m/%Y" − "%H:%M:%S
}
11
12
pgrep −f f t p . py # v e r i f i c a s e o s c r i p t de FTP j á e s t á sendo e x e c u t a d o
13
14
15
16
17
18
19
20
i f [ $ ? −ne 0 ] ; then
echo " $ ( h o r a r i o ) : FTP e x e c u t a d o . ">>$LOG
echo −e "−\n$ ( h o r a r i o ) : I n i c i o da e x e c u c a o . ">>$FTPLOG
python / e t l / s c r i p t s / f t p . py # e x e c u t a o s c r i p t de FTP
echo " $ ( h o r a r i o ) : Fim da e x e c u c a o . ">>$FTPLOG
exit 0
fi
21
22
echo " $ ( h o r a r i o ) : P r o c e s s o j a em e x e c u c a o . ">>$LOG
23
24
exit 0
70
A.2
A.2.1
1
Conversão
pm-convert.sh
#! / b i n / bash
2
3
#d e f i n i ç õ e s
4
5
6
7
8
9
10
11
12
TEMP_DIR=" / e t l /umts/ p e r f o r m a n c e /temp/ x s l t / "
OUT_DIR=" / e t l /umts/ p e r f o r m a n c e / c o n v e r t e d / "
IMP_DIR=" / e t l /umts/ p e r f o r m a n c e /raw/ "
XSLT=" / e t l / s c r i p t s / pmResult . x s l "
SAXON=" / u s r / s h a r e /maven−r e p o / n e t / s f / saxon / Saxon−HE/ d e b i a n / Saxon−HE−
debian . j a r "
LOG=" /home/ e t l u s e r / l o g / ConvertXml2Csv . l o g "
TREADS=4
T e s t I n t e r v a l =1
13
14
e x p o r t TEMP_DIR OUT_DIR IMP_DIR XSLT SAXON LOG TREADS T e s t I n t e r v a l
15
16
17
#mata a e x e c u ç ã o s e o d i r e t o r i o não f o r e n c o n t r a d o
[ −d $IMP_DIR ] | | e x i t 1
18
19
20
21
22
23
24
25
#f u n c a o que pega hora a t u a l
horario ()
{
d a t e +%d/%m/%Y" − "%H:%M:%S
}
#e x p o r t a a f u n ç ã o para s h e l l s f i l h o s
e x p o r t −f h o r a r i o
26
27
28
#f u n ç ã o que f a z a c o n v e r s a o
Convert2Csv ( ) {
29
30
31
FileName=$1
[ −r $FileName ] && x m l l i n t −−noout −−v a l i d $ {IMP_DIR}/ $ { FileName }
32
33
34
35
36
37
i f [ $ ? −eq 0 ] ; then
time j a v a −cp $ {SAXON} n e t . s f . saxon . Transform −t −s : $ { FileName
} −x s l : $ {XSLT} −o : $ {TEMP_DIR}/ $ { FileName } . c s v \
&& p s q l −d p o s t g r e s −h $HOST_IP −U p o s t g r e s −c "UPDATE
umts_control . l o g _ e t l SET x s l t ='$ ( d a t e +"%Y−%m−%d %T" ) ' WHERE o s s ='$
{ FileName%%_∗ } ' AND f i l e ='$ { FileName#∗_} ' ; " \
&& rm $ { FileName } \
&& mv $ {TEMP_DIR}/ $ { FileName } . c s v $ {OUT_DIR}
38
71
echo " $ ( h o r a r i o ) : Arquivo $FileName c o n v e r t i d o ">>$LOG
39
40
else
p s q l −d p o s t g r e s −h $HOST_IP −U p o s t g r e s −c "DELETE FROM
umts_control . l o g _ e t l WHERE o s s ='$ { FileName%%_∗ } ' AND f i l e ='$ {
FileName#∗_} ' ; "
rm −f $ { FileName }
41
42
43
fi
44
45
46
47
}
#e x p o r t a a f u n ç ã o para s h e l l s f i l h o s
e x p o r t −f Convert2Csv
48
49
50
51
T o t a l F i l e s=$ ( l s $ {IMP_DIR} ∗ . xml | wc − l )
cd $ {IMP_DIR}
echo −e "−\n$ ( h o r a r i o ) : I n i c i o da e x e c u c a o ">>$LOG
52
53
p a r a l l e l −j$TREADS −u Convert2Csv { } \ ; ' echo −e " \ n P r o g r e s s : {#}/ '
$ T o t a l F i l e s ' F i l e s c o n v e r t e d \n" ' \ ; s l e e p $ T e s t I n t e r v a l : : : $ ( l s ∗ .
xml | s o r t −t "_" −k 5 )
54
55
56
57
echo " $ ( h o r a r i o ) : Fim da e x e c u c a o ">>$LOG
echo −e " \n"
exit 0
72
A.2.2
1
auto-convert.sh
#! / b i n / bash
2
3
4
IMP_FILES="/ e t l /umts/ p e r f o r m a n c e /raw/ "
LOG=" /home/ e t l u s e r / l o g / auto_convert . l o g "
5
6
7
8
9
horario ()
{
d a t e +%d/%m/%Y" − "%H:%M:%S # f u n ç ã o que pega a data a t u a l
}
10
11
12
13
14
15
16
17
18
19
20
21
if
[ [ ! $ ( l s −A $IMP_FILES / ∗ . xml ) ] ] ; then
echo " $ ( h o r a r i o ) : Nao ha a r q u i v o s a serem c a r r e g a d o s . ">>$LOG
exit 1
else
pgrep −f pm−c o n v e r t . sh # v e r i f i c a s e o s c r i p t de c o n v e r s ã o j á e s t á
sendo e x e c u t a d o
i f [ $ ? −ne 0 ] ; then
echo " $ ( h o r a r i o ) : Converted e x e c u t a d o . ">>$LOG
/ e t l / s c r i p t s /pm−c o n v e r t . sh
exit 0
fi
fi
22
23
24
echo " $ ( h o r a r i o ) : P r o c e s s o j a em e x e c u c a o . ">>$LOG
exit 0
73
A.2.3
1
2
3
4
5
6
7
8
9
import
import
import
import
import
import
import
import
import
parser.py
os
csv
time
psycopg2
time
datetime
re
sys
ftplib
10
11
12
13
14
15
#####SET DIR
c o n v e r t e d d i r = ' / e t l /umts/ p e r f o r m a n c e / c o n v e r t e d / '
tempdir = ' / e t l /umts/ p e r f o r m a n c e /temp/ p a r s e r / '
o u t d i r = ' / p e r f o r m a n c e /umts/ '
l o g f i l e = ' /home/ e t l u s e r / l o g /parsePM . l o g '
16
17
18
#####Get F i l e
f i l e = s y s . argv [ 1 ]
19
20
21
22
#####Connect t o t h e d a t a b a s e
db_conn = psycopg2 . c o n n e c t ( "dbname=' p o s t g r e s ' u s e r =' p o s t g r e s ' h o s t ='
host_ip ' password ='password_db ' " ) # c o n e c t a no Banco de Dados
c u r s o r = db_conn . c u r s o r ( )
23
24
try :
print (
ifile
reader
ofile
writer
25
26
27
28
29
' C o n v e r t i n g f i l e '+ f i l e )
= open ( c o n v e r t e d d i r+f i l e , " r " )
= c s v . r e a d e r ( i f i l e , d e l i m i t e r= ' ; ' )
= open ( tempdir+f i l e , "w" , n e w l i n e=" \n" , e n c o d i n g=" u t f −8" )
= c s v . w r i t e r ( o f i l e , d e l i m i t e r= ' ; ' , q u o t i n g=c s v .QUOTE_MINIMAL
)
30
31
32
33
34
35
36
37
38
39
40
###p a r s e h e a d e r
f o r row i n r e a d e r
row . i n s e r t ( 0 , " d a t e t i m e " )
row . i n s e r t ( 0 , " c e l l i d " )
row . i n s e r t ( 0 , " c e l l n a m e " )
row . i n s e r t ( 0 , " r n c " )
row = [ r . r e p l a c e ( " . " , "_" ) f o r r i n row ]
row = [ r . l o w e r ( ) f o r r i n row ]
w r i t e r . w r i t e r o w ( row )
break
41
42
43
###p a r s e body
f o r row i n r e a d e r :
74
44
45
46
47
48
49
50
51
row = [ r . r e p l a c e ( "NULL" , " " ) f o r r i n row ]
row . i n s e r t ( 0 , ( row [ 0 ] [ 0 : 4 ] + '− ' + row [ 0 ] [ 4 : 6 ] + '− ' + row [ 0 ] [ 6 : 8 ]
+ ' ' + row [ 0 ] [ 8 : 1 0 ] + ' : ' + row [ 0 ] [ 1 0 : 1 2 ] ) )#d a t e t i m e
row . i n s e r t ( 0 , row [ 3 ] . s p l i t ( ' , ' ) [ 1 ] [ 8 : ] ) ##c e l l i d
row . i n s e r t ( 0 , row [ 4 ] . s p l i t ( ' , ' ) [ 0 ] . s p l i t ( '= ' ) [ 1 ] )##c e l l n a m e
row . i n s e r t ( 0 , row [ 5 ] . s p l i t ( ' / ' ) [ 0 ] )##r n c
w r i t e r . w r i t e r o w ( row )
i f i l e . close ()
o f i l e . close ()
52
53
54
55
56
57
58
59
###Query s e l e c t e d columns
f s s = s t r ( f i l e . s p l i t ( '_ ' ) [ 2 ] )
query = "SELECT counter_name FROM umts_control . c o u n t e r _ r e f e r e n c e
WHERE c o u n t e r _ e n a b l e = 'TRUE ' and f u n c t i o n s u b s e t _ i d = ' "+f s s+" ' ; "
c u r s o r . e x e c u t e ( query )
enabled_columns = [ item [ 0 ] f o r item i n c u r s o r . f e t c h a l l ( ) ]
s e l e c t e d _ c o l u m n s = [ ' r n c ' , ' c e l l n a m e ' , ' c e l l i d ' , ' d a t e t i m e ' , ' gp ' ] #
s e l e c t header
s e l e c t e d _ c o l u m n s . extend ( enabled_columns )
60
61
62
63
64
65
###Query columns o r d e r
t a b l e = ' f s s _ '+s t r ( f i l e . s p l i t ( '_ ' ) [ 2 ] )
query = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE
table_schema = ' umts_counter ' and table_name = ' "+t a b l e+" ' o r d e r by
ordinal_position ; "
c u r s o r . e x e c u t e ( query )
columns = [ item [ 0 ] f o r item i n c u r s o r . f e t c h a l l ( ) ]
66
67
68
69
70
71
###Read and w r i t e columns i n b u l k l o a d format
i f i l e = open ( tempdir+f i l e , " r " )
r e a d e r = c s v . DictReader ( i f i l e , d e l i m i t e r= ' ; ' )
o f i l e = open ( tempdir+ f i l e + ' . t x t ' , "w" , n e w l i n e=" \n" , e n c o d i n g=" u t f
−8" )
w r i t e r = c s v . D i c t W r i t e r ( o f i l e , f i e l d n a m e s=columns , r e s t v a l= ' ' ,
e x t r a s a c t i o n= ' i g n o r e ' , d e l i m i t e r= ' \ t ' )
72
73
74
75
76
77
78
#w r i t e r . w r i t e h e a d e r ( )
f o r row i n r e a d e r :
newrow = {k : v f o r k , v i n row . i t e m s ( ) i f k i n s e l e c t e d _ c o l u m n s }
w r i t e r . w r i t e r o w ( newrow )
i f i l e . close ()
o f i l e . close ()
79
80
81
82
83
except IndexError :
p r i n t ( ' Couldnt c o n v e r t ' )
i f i l e . close ()
o f i l e . close ()
75
84
o s . remove ( tempdir+ f i l e )
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
else :
p r i n t ( ' Could Convert ' )
s e s s i o n = f t p l i b .FTP( ' host_ip ' , ' p o s t g r e s ' , ' password_db ' )
u p l o a d f i l e = open ( tempdir+ f i l e + ' . t x t ' , ' rb ' )
s e s s i o n . s t o r b i n a r y ( 'STOR '+o u t d i r+ f i l e + ' . t x t ' , u p l o a d f i l e )
#
e n v i a o a r q u i v o para o s e r v i d o de dados
uploadfile . close ()
session . quit ()
query = "UPDATE umts_control . l o g _ e t l SET f i l t e r ='"+s t r ( d a t e t i m e .
d a t e t i m e . now ( ) ) [ 0 : 1 9 ] + " ' WHERE o s s ='"+s t r ( f i l e . s p l i t ( '_ ' ) [ 0 ] ) +" '
AND f i l e ='"+s t r ( ( f i l e . r p a r t i t i o n ( f i l e . s p l i t ( '_ ' ) [ 0 ] + '_ ' ) [ 2 ] ) [ : − 4 ] )+
" '"
c u r s o r . e x e c u t e ( query )
db_conn . commit ( )
l o g = open ( l o g f i l e , " a " )
l o g . w r i t e ( d a t e t i m e . d a t e t i m e . now ( ) . s t r f t i m e ( '%d/%m/%Y − %H:%M:%S ' )+"
: a r q u i v o "+ f i l e +" p a r s e a d o \n" )
log . close ()
o s . remove ( c o n v e r t e d d i r+ f i l e )
o s . remove ( tempdir+ f i l e )
o s . remove ( tempdir+ f i l e + ' . t x t ' )
102
103
cursor . close ()
76
A.2.4
1
auto-parse.sh
#! / b i n / bash
2
3
4
5
6
7
IMP_FILES="/ e t l /umts/ p e r f o r m a n c e / c o n v e r t e d / "
LOG=" /home/ e t l u s e r / l o g / auto_parse . l o g "
PARSELOG=" /home/ e t l u s e r / l o g /parsePM . l o g "
TREADS=4
T e s t I n t e r v a l =0
8
9
e x p o r t IMP_FILES LOG PARSELOG TREADS T e s t I n t e r v a l
10
11
12
13
14
15
h o r a r i o ( ) # f u n ç ã o que pega a data a t u a l
{
d a t e +%d/%m/%Y" − "%H:%M:%S
}
e x p o r t −f h o r a r i o
16
17
18
19
20
if
[ [ ! $ ( l s −A $IMP_FILES) ] ] ; then
echo " $ ( h o r a r i o ) : Nao ha a r q u i v o s a serem c a r r e g a d o s . ">>$LOG
exit 1
else
21
pgrep −f p a r s e r . py # v e r i f i c a s e o s c r i p t de p a r s e r j á e s t á sendo
executado
i f [ $ ? −ne 0 ] ; then
echo " $ ( h o r a r i o ) : Parse e x e c u t a d o . ">>$LOG
echo −e "−\n$ ( h o r a r i o ) : I n i c i o da e x e c u c a o . ">>$PARSELOG
cd $IMP_FILES
T o t a l F i l e s=$ ( l s ∗ . c s v | wc − l )
p a r a l l e l −j$TREADS −u python3 . 4 / e t l / s c r i p t s / p a r s e r . py { } \ ; ' echo −e
" \ n P r o g r e s s : {#}/ ' $ T o t a l F i l e s ' F i l e s p a r s e d \n" ' \ ; s l e e p
$ T e s t I n t e r v a l : : : $ ( l s ∗ . c s v | s o r t −t "_" −k 5 )
echo " $ ( h o r a r i o ) : Fim da e x e c u c a o . ">>$PARSELOG
exit 0
fi
22
23
24
25
26
27
28
29
30
31
32
fi
33
34
echo " $ ( h o r a r i o ) : P r o c e s s o j a em e x e c u c a o . ">>$LOG
35
36
exit 0
77
A.3
A.3.1
1
Carga
staging.sh
#! / b i n / bash
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#d e f i n i e s
PSQL=" p s q l "
IOSTAT=" / u s r / b i n / i o s t a t "
#V a r i a v e i s t r o c a r conforme n e c e s s i a d e
T e s t I n t e r v a l =1
TAB_Pref="umts"
IMP_DIR=" /home/ p o s t g r e s /$TAB_Pref/ p e r f o r m a n c e / "
LOG=" /home/ p o s t g r e s / l o g / s t a g i n g / "
CONTROLE=" /home/ p o s t g r e s / s c r i p t s / s t a g i n g _ b u l k l o a d . c l t "
#c o n t r o l e de p r o c e s s o s
MAXCPULOAD=3.2 #s y s l o a d 1min
MINMEMFREE=100000 #kB
MAXkBreads=50000 #kB_read/ s i n s t a l l s y s s t a t
MAXkBwrtns=100000 #kB_wrtn/ s
MAXTreads=1
MAXHDUsage=90 # % de d i s k para p a r a r
BLOCK_DIR=" /home/ p o s t g r e s / b l o c k _ p r o c e s s "
20
21
22
#e x p o r t a t o d a s a s v a r i a v e i s para poder u s a r com o s s h e l l s f i l h o s
e x p o r t PSQL IOSTAT T e s t I n t e r v a l TAB_Pref IMP_DIR MAXCPULOAD MINMEMFREE
MAXkBreads MAXkBwrtns MAXTreads MAXHDUsage LOG CONTROLE BLOCK_DIR
23
24
25
#mata a execuo s e o d i r e t o r i o no f o r e n c o n t r a d o
[ −d $IMP_DIR ] | | e x i t 1
26
27
#python $STATUS_MESSAGE ' S t a g i n g . sh r e s t a r t e d '
28
29
30
31
32
33
34
35
36
37
38
CatchErrors ( ) {
i f [ $ ? −eq 1 ] | | [ $ ? −eq 2 ] ; then
echo −e " \ n S c r i p t aborted , b e c a u s e o f e r r o r s . \ n"
k i l l $$
exit 1
fi
}
e x p o r t −f C a t c h E r r o r s
#funo para r e a l i z a r o import propiamente d i t o
RunPSQL ( ) {
39
40
41
FileName=$1
TAB_Name=$ ( echo $1 | c u t −d"_" −f 3 )
78
42
43
44
45
46
47
48
49
50
echo −ne " \n\ npg_bulkload − i $FileName −O umts_counter . fss_$TAB_Name\
n"
[ −r $FileName ] && time pg_bulkload − i $FileName −O umts_counter .
fss_$TAB_Name −P $ {LOG}parseLog$TAB_Name . t x t −u $ {LOG}
duplicadosLog$TAB_Name . t x t $CONTROLE
CatchErrors
DATE=$ ( d a t e +"%Y−%m−%d %T" )
C u t F i l e=$ { FileName##∗/}
OSS=$ ( echo $ C u t F i l e | c u t −d"_" −f 1 )
FILE=$ ( echo $ { C u t F i l e%%.c s v . t x t } | c u t −d"_" −f2 −6)
p s q l −c "UPDATE umts_control . l o g _ e t l SET b u l k l o a d = '$DATE'
WHERE o s s = ' $OSS ' AND f i l e = ' $FILE ' ; "
rm −f " $FileName " && echo " removed $ C u t F i l e "
51
52
53
54
55
}
#e x p o r t a a funo para s h e l l s f i l h o s
e x p o r t −f RunPSQL
ControlLoad ( ) {
56
57
58
59
60
61
62
#r o t i n a s de t e s t e
CPULOAD=$ ( c a t / p r o c / l o a d a v g | c u t −d" " −f 1 | t r " , " " . " )
MEMFREE=$ ( c a t / p r o c / meminfo | g r e p "MemFree" | t r −s " " | c u t −d" "
−f 2 | t r " , " " . " ) # pode sem com o MemAvailable
IOrwvet=($ ($IOSTAT | g r e p " sda " | t r −s " " | c u t −d" " −f3 , 4 | t r " ,
" " . ") )
NTreads=$ ( j o b s | g r e p −c "RunPSQL" )
HDUsage=$ ( d f 2> / dev / n u l l | g r e p " / var / l i b / p o s t g r e s q l " | t r −s " " |
c u t −d" " −f 5 | t r −d "%" ) # t r o c a r o argumento do g r e p p e l o
d i r e t o r i o de montagem como a p a r e c e no d f 2> / dev / n u l l
63
64
65
66
67
68
69
[ $ ( echo " $CPULOAD >= $MAXCPULOAD " | bc ) −eq 1 ] && blkCPU=1 | |
blkCPU=0
[ $ ( echo " $MEMFREE <= $MINMEMFREE " | bc ) −eq 1 ] && blkMEM=1 | |
blkMEM=0
[ $ ( echo " $ { IOrwvet [ 0 ] } > $MAXkBreads | | $ { IOrwvet [ 1 ] } > $MAXkBwrtns
" | bc ) −eq 1 ] && blkIO=1 | | blkIO=0
[ $ ( echo " $NTreads >= $MAXTreads " | bc ) −eq 1 ] && blkTread=1 | |
blkTread=0
[ [ $ ( echo " $HDUsage >= $MAXHDUsage " | bc ) −eq 1 ] ] && blkHDFull=1
| | blkHDFull=0
[ [ ` l s −A "$BLOCK_DIR" ` ] ] && BLOCK_PROCESS=1 | | BLOCK_PROCESS=0
70
71
72
73
74
# echo " $CPULOAD >= $MAXCPULOAD "
# echo " $MEMFREE <= $MINMEMFREE "
# echo " ${ IOrwvet [ 0 ] } > $MAXkBreads | | $ { IOrwvet [ 1 ] } > $MAXkBwrtns "
BLOQUEADO=$ ( echo " $blkCPU + $blkMEM + $blkIO + $blkTread +
$BLOCK_PROCESS " | bc )
79
75
76
77
78
79
80
81
82
83
84
85
86
87
w h i l e [ $BLOQUEADO −ne 0 ] ; do
#r o t i n a de t e s t e
CPULOAD=$ ( c a t / p r o c / l o a d a v g | c u t −d" " −f 1 | t r " , " " . " )
MEMFREE=$ ( c a t / p r o c / meminfo | g r e p "MemFree" | t r −s " " | c u t −d"
" −f 2 | t r " , " " . " ) # pode sem com o MemAvailable
IOrwvet=($ ($IOSTAT | g r e p " sda " | t r −s " " | c u t −d" " −f3 , 4 | t r
" ," " . ") )
NTreads=$ ( j o b s | g r e p −c "RunPSQL" )
HDUsage=$ ( d f 2> / dev / n u l l | g r e p "/ var / l i b / p g s q l " | t r −s " " | c u t
−d" " −f 5 | t r −d "%" ) # t r o c a r o argumento do g r e p p e l o d i r e t o r i o
de montagem como a p a r e c e no d f 2> / dev / n u l l
[ $ ( echo " $CPULOAD >= $MAXCPULOAD " | bc ) −eq 1 ] && blkCPU=1 | |
blkCPU=0
[ $ ( echo " $MEMFREE <= $MINMEMFREE " | bc ) −eq 1 ] && blkMEM=1 | |
blkMEM=0
[ $ ( echo " $ { IOrwvet [ 0 ] } > $MAXkBreads | | ${ IOrwvet [ 1 ] } >
$MAXkBwrtns " | bc ) −eq 1 ] && blkIO=1 | | blkIO=0
[ $ ( echo " $NTreads >= $MAXTreads " | bc ) −eq 1 ] && blkTread=1 | |
blkTread=0
[ [ $ ( echo " $HDUsage >= $MAXHDUsage " | bc ) −eq 1 ] ] && blkHDFull
=1 | | blkHDFull=0
[ [ ` l s −A "$BLOCK_DIR" ` ] ] && BLOCK_PROCESS=1 | | BLOCK_PROCESS=0
88
89
BLOQUEADO=$ ( echo " $blkCPU + $blkMEM + $blkIO + $blkTread +
$blkHDFull + $BLOCK_PROCESS" | bc )
90
91
92
93
94
95
96
97
98
echo " Blk s t a t u s ($BLOQUEADO) ; CPU: $CPULOAD >= $MAXCPULOAD( $blkCPU )
/ MEM: $MEMFREE <= $MINMEMFREE($blkMEM) / IO : $ { IOrwvet [ 0 ] } >
$MAXkBreads | | $ { IOrwvet [ 1 ] } > $MAXkBwrtns ( $blkIO ) / NTreads :
$NTreads >= $MAXTreads ( $blkTread ) / HD: $HDUsage >= $MAXHDUsage (
$blkHDFull ) | | FLAG BLOCK: $BLOCK_PROCESS"
sleep $TestInterval
done
#chama a funo de import em background
RunPSQL $1
}
#e x p o r t a a funo para s h e l l s f i l h o s
e x p o r t −f ControlLoad
99
100
101
102
103
104
105
106
#l i s t a o s a r q u i v o s ordenando por data e r e v e r t e a s e q u e n c i a chamando a
funo a cada l i n h a
T o t a l F i l e s=$ ( l s $IMP_DIR | wc − l )
count=1
f o r t i m e s i n $ ( l s $IMP_DIR | c u t −d"_" −f 5 | s o r t | uniq ) ; do
# echo −ne "\ r \ t \ t \ t t i m e s : $ t i m e s "
f o r FILE i n $ ( l s $IMP_DIR/∗ $ { t i m e s }_∗ ) ; do
echo −ne " \ n P r o g r e s s : $count / $ T o t a l F i l e s F i l e s l o a d . "
80
107
108
109
110
111
112
113
( ( count++))
#echo $FILE
ControlLoad $FILE
#s l e e p $ T e s t I n t e r v a l
done
done
echo −e " \n"
81
A.3.2
1
auto-staging.sh
#! / b i n / bash
2
3
4
5
6
7
BLOCK_DIR=" /home/ p o s t g r e s / b l o c k _ p r o c e s s "
IMP_FILES="/home/ p o s t g r e s / p e r f o r m a n c e /umts"
PROCESS=" s t a g i n g . sh "
LOG=" /home/ p o s t g r e s / l o g / s t a g i n g . t x t "
E r r o r l o g=" /home/ p o s t g r e s / l o g / auto_stag . t x t "
8
9
10
11
12
h o r a r i o ( ) # f u n ç ã o que pega a data a t u a l
{
d a t e +%d/%m/%Y" − "%H:%M:%S
}
13
14
[ [ ` l s −A $BLOCK_DIR` ] ] && echo " $ ( h o r a r i o ) : S t a g i n g bloqueado . ">>
$ E r r o r l o g && e x i t 1
15
16
17
18
19
20
21
22
23
24
25
26
if
[ [ ! ` l s −A $IMP_FILES` ] ] ; then
echo " $ ( h o r a r i o ) : Nao ha a r q u i v o s a serem c a r r e g a d o s . ">>$ E r r o r l o g
exit 1
else
pgrep −f $PROCESS # v e r i f i c a s e o s c r i p t de s t a g i n g j á e s t á sendo
executado
i f [ $ ? −ne 0 ] ; then
echo " $ ( h o r a r i o ) : S t a g i n g e x e c u t a d o . ">>$ E r r o r l o g
/home/ p o s t g r e s / s c r i p t s /$PROCESS
exit 0
fi
fi
27
28
29
echo " $ ( h o r a r i o ) : P r o c e s s o j a em e x e c u c a o . ">>$ E r r o r l o g
exit 0
82
Apêndice B
Códigos Fonte - Processamento
Neste apêndice estão alguns dos códigos fonte do processamento proposto neste
trabalho. Para não ficar muito extenso, está disponível apenas o cálculo dos KPIs
de Acessibilidade e o cálculo dos ofensores por RNC destes KPIs.
83
B.1
B.1.1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Cálculo dos KPIs de Acessibilidade
View vw_acessibility.sql
CREATE OR REPLACE VIEW umts_kpi . v w _ a c c e s s i b i l i t y AS
SELECT f s s _ 6 7 1 0 9 3 6 5 . rnc ,
fss_67109365 . cellname ,
fss_67109365 . c e l l i d ,
f s s _ 6 7 1 0 9 3 6 5 . datetime ,
f s s _ 6 7 1 0 9 3 6 5 . gp ,
rrc_succconnestab_orgintercall + rrc_succconnestab_orgbkgcall +
rrc_succconnestab_orgsubcall + rrc_succconnestab_tmitrcall +
r r c _ s u c c c o n n e s t a b _ t m b k g c a l l AS acc_rrc_num ,
rrc_attconnestab_orgintercall + rrc_attconnestab_orgbkgcall +
rrc_attconnestab_orgsubcall + rrc_attconnestab_tmintercall +
r r c _ a t t c o n n e s t a b _ t m b k g c a l l AS acc_rrc_den ,
vs_rab_succestabcs_amr + vs_rab_succestabcs_amrwb AS acc_cs_rab_num
,
vs_rab_attestab_amr + vs_rab_attestabcs_amrwb AS acc_cs_rab_den ,
vs_rab_succestabps_conv + vs_rab_succestabps_str +
vs_rab_succestabps_int + vs_rab_succestabps_bkg AS acc_ps_rab_num ,
vs_rab_attestabps_conv + v s _ r a b _ a t t e s t a b p s _ s t r +
vs_rab_attestabps_int + vs_rab_attestabps_bkg AS acc_ps_rab_den ,
vs_hsdpa_rab_succestab AS acc_hs_num ,
vs_hsdpa_rab_attestab AS acc_hs_den ,
v s _ a t t r e c f g _ f 2 h _ d a t a t r a n s t r i g + v s _ a t t r e c f g _ p 2 h _ d a t a t r a n s t r i g AS
acc_hs_f2h_num ,
v s _ s u c c r e c f g _ f 2 h _ d a t a t r a n s t r i g + v s _ s u c c r e c f g _ p 2 h _ d a t a t r a n s t r i g AS
acc_hs_f2h_den
FROM umts_counter . f s s _ 6 7 1 0 9 3 6 5
JOIN umts_counter . f s s _ 6 7 1 0 9 3 6 8 ON f s s _ 6 7 1 0 9 3 6 5 . r n c = f s s _ 6 7 1 0 9 3 6 8 .
r n c AND f s s _ 6 7 1 0 9 3 6 5 . d a t e t i m e = f s s _ 6 7 1 0 9 3 6 8 . d a t e t i m e AND
fss_67109365 . c e l l i d = fss_67109368 . c e l l i d
JOIN umts_counter . f s s _ 6 7 1 0 9 3 7 2 ON f s s _ 6 7 1 0 9 3 6 5 . r n c = f s s _ 6 7 1 0 9 3 7 2 .
r n c AND f s s _ 6 7 1 0 9 3 6 5 . d a t e t i m e = f s s _ 6 7 1 0 9 3 7 2 . d a t e t i m e AND
fss_67109365 . c e l l i d = fss_67109372 . c e l l i d
JOIN umts_counter . f s s _ 6 7 1 0 9 3 9 0 ON f s s _ 6 7 1 0 9 3 6 5 . r n c = f s s _ 6 7 1 0 9 3 9 0 .
r n c AND f s s _ 6 7 1 0 9 3 6 5 . d a t e t i m e = f s s _ 6 7 1 0 9 3 9 0 . d a t e t i m e AND
fss_67109365 . c e l l i d = fss_67109390 . c e l l i d
LEFT JOIN umts_counter . f s s _ 8 2 8 6 4 1 3 1 ON f s s _ 6 7 1 0 9 3 6 5 . r n c =
f s s _ 8 2 8 6 4 1 3 1 . r n c AND f s s _ 6 7 1 0 9 3 6 5 . d a t e t i m e = f s s _ 8 2 8 6 4 1 3 1 . d a t e t i m e
AND f s s _ 6 7 1 0 9 3 6 5 . c e l l i d = f s s _ 8 2 8 6 4 1 3 1 . c e l l i d ;
84
B.1.2
1
2
3
4
Função PL/SQL inserir_kpi_accessibility.sql
CREATE OR REPLACE FUNCTION umts_kpi . i n s e r i r _ k p i _ a c c e s s i b i l i t y (
data timestamp w i t h o u t time zone )
RETURNS SETOF umts_kpi . a c c e s s i b i l i t y AS
$BODY$
5
6
BEGIN
7
8
9
10
11
r e t u r n query e x e c u t e format ( ' s e l e c t v w _ a c c e s s i b i l i t y . rnc ,
v w _ a c c e s s i b i l i t y . cellname , v w _ a c c e s s i b i l i t y . c e l l i d ,
v w _ a c c e s s i b i l i t y . datetime , v w _ a c c e s s i b i l i t y . gp ,
v w _ a c c e s s i b i l i t y . acc_rrc_num , v w _ a c c e s s i b i l i t y . acc_rrc_den ,
v w _ a c c e s s i b i l i t y . acc_cs_rab_num , v w _ a c c e s s i b i l i t y . acc_cs_rab_den ,
v w _ a c c e s s i b i l i t y . acc_ps_rab_num , v w _ a c c e s s i b i l i t y . acc_ps_rab_den ,
v w _ a c c e s s i b i l i t y . acc_hs_num , v w _ a c c e s s i b i l i t y . acc_hs_den ,
v w _ a c c e s s i b i l i t y . acc_hs_f2h_num , v w _ a c c e s s i b i l i t y . acc_hs_f2h_den
12
13
14
15
16
from umts_kpi . v w _ a c c e s s i b i l i t y WHERE d a t e t i m e = ' '%I ' ' %s ' ,
data ) ;
END;
$BODY$
LANGUAGE p l p g s q l
85
B.1.3
1
inserir_kpi_accessibility.sh
#! / b i n / bash
2
3
4
5
6
l o g=" /home/ p o s t g r e s / l o g / i n s e r i r K P I A c e s s i b i l i d a d e . t x t "
l o g E r r o r=" /home/ p o s t g r e s / l o g / e r r o r I n s e r i r K P I A c e s s i b i l i d a d e . t x t "
CONTROLE=" /home/ p o s t g r e s / s c r i p t s / b u l k l o a d _ c o n t r o l _ f u n c t i o n . c l t "
BLOCK_DIR=" /home/ p o s t g r e s / b l o c k _ p r o c e s s / "
7
8
9
10
11
12
13
14
15
catch_errors () {
i f [ $ ? −eq 1 ] | | [ $ ? −eq 2 ] ; then
echo −e " \n$ ( h o r a r i o ) : S c r i p t f i n a l i z a d o d e v i d o a e r r o s . \ n"
echo " $ ( h o r a r i o ) : S c r i p t f i n a l i z a d o d e v i d o a e r r o s . ">>$ l o g
k i l l $$
exit 1
fi
}
16
17
18
19
20
21
horario ()
{
d a t e +%d/%m/%Y" − "%H:%M:%S
}
DATA=$1 # r e c e b e a data por l i n h a de comando
22
23
24
25
echo "−">>$ l o g
echo " $ ( h o r a r i o ) : I n i c i o da e x e c u c a o . ">>$ l o g
echo −e " \n$ ( h o r a r i o ) : I n i c i o da e x e c u c a o para c a l c u l o de KPIs
A c e s s i b i l i d a d e para a data $DATA"
26
27
28
29
30
31
time ( echo −e " \n"
pg_bulkload − i " umts_kpi . i n s e r i r _ k p i _ a c c e s s i b i l i t y ( '$DATAS' ) " −O
umts_kpi . a c c e s s i b i l i t y −P $ l o g E r r o r $CONTROLE
catch_errors
echo " $ ( h o r a r i o ) : KPI a c e s s i b i l i d a d e c a l c u l a d o para a data $DATA">>
$log
echo −e " \n$ ( h o r a r i o ) : KPI a c e s s i b i l i d a d e c a l c u l a d o para a data $DATA
")
32
33
34
echo " $ ( h o r a r i o ) : Fim da e x e c u c a o . ">>$ l o g
echo −e " \n$ ( h o r a r i o ) : Fim da e x e c u c a o para c a l c u l o s do KPI
a c c e s s i b i l i d a d e para a data $DATA"
35
36
exit 0
86
B.2
Cálculo dos ofensores por RNC dos KPIs de
Acessibilidade
B.2.1
Função PL/SQL inserir_worst_cells_rnc_accessibility.sql
1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE FUNCTION umts_kpi .
inserir_worst_cells_rnc_accessibility (
kpi text ,
data d a t e )
RETURNS SETOF umts_kpi . w o r s t _ c e l l s _ r n c _ a c c e s s i b i l i t y AS
$BODY$
declare
cur_row2 t e x t ;
cur_row r e c o r d ;
r worstcells_rnctype ;
rank i n t e g e r ;
11
12
13
14
15
16
17
18
19
20
21
begin
f o r cur_row2 i n e x e c u t e format ( 'SELECT d i s t i n c t r n c FROM umts_kpi .
a c c e s s i b i l i t y _ r n c WHERE d a t e t i m e : : d a t e = ' '%s ' ' ORDER BY r n c ' , data
)
loop
r . new_rnc_kpi := 2 ;
r . rank := 0 ;
f o r cur_row i n e x e c u t e format ( 'SELECT A. rnc , A. c e l l i d , A. date , %
s_num a s cell_kpi_num , %1$s_den a s cell_kpi_den , (%1$s_den − %1
$s_num ) a s c e l l _ f a i l s , rnc_kpi_num , rnc_kpi_den ,
( rnc_kpi_den − rnc_kpi_num ) a s r n c _ f a i l s FROM umts_kpi .
a c c e s s i b i l i t y _ d a i l y A JOIN
(SELECT rnc , date , SUM(%1$s_num ) a s rnc_kpi_num , SUM(%1$s_den ) a s
rnc_kpi_den FROM umts_kpi . a c c e s s i b i l i t y _ d a i l y WHERE r n c = ' '%s ' '
AND d a t e = ' '%s ' ' GROUP BY rnc , d a t e ) B
ON A. r n c = B . r n c AND A. d a t e = B . d a t e
WHERE A. r n c = ' '%2$ s ' ' AND A. d a t e = ' '%3$ s ' ' ORDER BY c e l l _ f a i l s
DESC ' , kpi , cur_row2 , data )
22
23
loop
24
25
26
27
i f ( r . new_rnc_kpi = 2 ) then
r . new_rnc_kpi := COALESCE( cur_row . rnc_kpi_num / NULLIF ( cur_row
. rnc_kpi_den , 0 ) , 1 ) ;
end i f ;
28
29
r . r n c := cur_row . r n c ;
87
30
31
32
33
34
35
36
37
38
39
40
41
r . c e l l i d := cur_row . c e l l i d ;
r . d a t e := cur_row . d a t e ;
r . k p i := format ( '%s ' , k p i ) ;
r . cell_kpi_num := cur_row . cell_kpi_num ;
r . cell_kpi_den := cur_row . cell_kpi_den ;
r . c e l l _ f a i l s := cur_row . c e l l _ f a i l s ;
r . rnc_kpi_num := cur_row . rnc_kpi_num ;
r . rnc_kpi_den := cur_row . rnc_kpi_den ;
r . impact := COALESCE( cur_row . c e l l _ f a i l s / NULLIF ( cur_row .
rnc_fails , 0) , 0) ;
r . rnc_kpi := COALESCE( cur_row . rnc_kpi_num / NULLIF ( cur_row .
rnc_kpi_den , 0 ) , 1 ) ;
r . new_rnc_kpi := r . new_rnc_kpi + COALESCE( cur_row . c e l l _ f a i l s /
NULLIF ( cur_row . rnc_kpi_den , 0 ) , 0 ) ;
r . rank := r . rank + 1 ;
42
43
44
45
r e t u r n next r ;
end l o o p ;
end l o o p ;
46
47
48
49
end ;
$BODY$
LANGUAGE p l p g s q l
88
B.2.2
1
inserir_worst_cells_rnc_accessibility.sh
#! / b i n / bash
2
3
4
5
l o g=" /home/ p o s t g r e s / l o g / i n s e r i r W o r s t C e l l s R N C A c c e s s i b i l i t y . t x t "
l o g E r r o r=" /home/ p o s t g r e s / l o g / e r r o r I n s e r i r W o r s t C e l l s R N C A c c e s s i b i l i t y . t x t
"
CONTROLE=" /home/ p o s t g r e s / s c r i p t s / b u l k l o a d _ c o n t r o l _ f u n c t i o n . c l t "
6
7
8
9
10
11
12
13
14
catch_errors () {
i f [ $ ? −eq 1 ] | | [ $ ? −eq 2 ] ; then
echo −e " \n$ ( h o r a r i o ) : S c r i p t f i n a l i z a d o d e v i d o a e r r o s . \ n"
echo " $ ( h o r a r i o ) : S c r i p t f i n a l i z a d o d e v i d o a e r r o s . ">>$ l o g
k i l l $$
exit 1
fi
}
15
16
17
18
19
20
horario ()
{
d a t e +%d/%m/%Y" − "%H:%M:%S
}
DIA=$1 #r e c e b e o d i a por l i n h a de comando
21
22
23
24
echo "−">>$ l o g
echo " $ ( h o r a r i o ) : I n i c i o da e x e c u c a o . ">>$ l o g
echo −e " \n$ ( h o r a r i o ) : I n i c i o da e x e c u c a o para c a l c u l o s das p i o r e s
c e l u l a s para o d i a $DIA"
25
26
27
28
29
30
l i s t a D e K P I s=" r r c _ e f c
cs_rab_acc
ps_rab_acc
hs_acc
hs_acc_f2h "
31
32
33
34
35
36
37
38
f o r KPI i n $ l i s t a D e K P I s ; do
time ( echo −e " \n"
pg_bulkload − i " umts_kpi . i n s e r i r _ w o r s t _ c e l l s _ r n c _ a c c e s s i b i l i t y ( ' $KPI
' , ' $DIA ' ) " −O umts_kpi . w o r s t _ c e l l s _ r n c _ a c c e s s i b i l i t y −P $ l o g E r r o r
$CONTROLE
catch_errors
echo " $ ( h o r a r i o ) : Worst C e l l s RNC A c c e s s i b i l i t y do KPI $KPI
c a r r e g a d o s para o d i a $DIA">>$ l o g
echo −e " \n$ ( h o r a r i o ) : Worst C e l l s RNC A c c e s s i b i l i t y do KPI $KPI
c a r r e g a d o s para o d i a $DIA" )
done
39
89
40
41
42
echo " $ ( h o r a r i o ) : Fim da e x e c u c a o . ">>$ l o g
echo −e " \n$ ( h o r a r i o ) : Fim da e x e c u c a o para c a l c u l o s das p i o r e s c e l u l a s
para o d i a $DIA"
p s q l −c "VACUUM ANALYZE umts_kpi . w o r s t _ c e l l s _ r n c _ a c c e s s i b i l i t y ; "
43
44
exit 0
90