Baixe o app para aproveitar ainda mais
Prévia do material em texto
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 bibli- otecas 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 Edu- ardo Covas Costa e Eduardo Martins Montenegro, pela ajuda, ensinamentos e ami- zade durante todo o período em que fiz meu estágio na empresa, onde tive a opor- tunidade 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 Com- putaçã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 escolhi- das 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.1 Tema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.2 Delimitação . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.3 Justificativa . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 1.4 Objetivos . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 1.5 Metodologia . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.6 Descrição . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 2 Fundamentação Teórica 5 2.1 Tecnologias de telefonia móvel . . . . . . . . . . . . . . . . . . . . . . 5 2.1.1 Breve histórico sobre a telefonia móvel . . . . . . . . . . . . . 5 2.1.2 Cenário atual . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 2.1.3 Características do UMTS . . . . . . . . . . . . . . . . . . . . . 8 2.1.4 Características do LTE . . . . . . . . . . . . . . . . . . . . . . 11 2.2 Conceitos de KPI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 2.3 Conceitos de BI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 2.3.1 ETL . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . 19 2.3.2 Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . 20 2.3.3 Data Marts . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 2.3.4 Dashboard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 3 Solução implementada 23 3.1 Arquitetura do Sistema . . . . . . . . . . . . . . . . . . . . . . . . . . 23 3.1.1 Servidores Externos . . . . . . . . . . . . . . . . . . . . . . . . 24 3.1.2 Implementação do ETL . . . . . . . . . . . . . . . . . . . . . 27 3.1.3 Aplicação MVC . . . . . . . . . . . . . . . . . . . . . . . . . . 37 ix 3.2 Materiais e Métodos . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 3.2.1 Infra-estrutura . . . . . . . . . . . . . . . . . . . . . . . . . . 39 3.2.2 Ferramentas utilizadas . . . . . . . . . . . . . . . . . . . . . . 39 3.2.3 Funcionamento . . . . . . . . . . . . . . . . . . . . . . . . . . 48 4 Resultados e Discussões 50 4.1 KPIs básicos para tecnologia UMTS . . . . . . . . . . . . . . . . . . . 53 4.2 Principais ofensores por RNC . . . . . . . . . . . . . . . . . . . . . . 55 4.3 KPIs personalizados para tecnologia UMTS . . . . . . . . . . . . . . 56 4.4 KPIs básicos para tecnologia LTE . . . . . . . . . . . . . . . . . . . . 57 4.5 Discussões . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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 67 A.1 Extração . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 A.1.1 Ftp.py . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 A.1.2 Auto-get-performance.sh . . . . . . . . . . . . . . . . . . . . . 70 A.2 Conversão . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 A.2.1 pm-convert.sh . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 A.2.2 auto-convert.sh . . . . . . . . . . . . . . . . . . . . . . . . . . 73 A.2.3 parser.py . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 A.2.4 auto-parse.sh . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 A.3 Carga . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 A.3.1 staging.sh . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 A.3.2 auto-staging.sh . . . . . . . . . . . . . . . . . . . . . . . . . . 82 B Códigos Fonte - Processamento 83 B.1 Cálculo dos KPIs de Acessibilidade . . . . . . . . . . . . . . . . . . . 84 B.1.1 View vw_acessibility.sql . . . . . . . . . . . . . . . . . . . . . 84 B.1.2 Função PL/SQL inserir_kpi_accessibility.sql . . . . . . . . . . 85 B.1.3 inserir_kpi_accessibility.sh . . . . . . . . . . . . . . . . . . . 86 B.2 Cálculo dos ofensores por RNC dos KPIs de Acessibilidade . . . . . . 87 B.2.1 Função PL/SQL inserir_worst_cells_rnc_accessibility.sql . . 87 B.2.2 inserir_worst_cells_rnc_accessibility.sh . . . . . . . . . . . . 89 x Lista de Figuras 2.1 Evolução dos padrões de telefonia móvel 3GPP. Fonte: 5G Americas [1]. . 6 2.2 Números de celulares no mundo ano a ano, em bilhões. Fonte: Teleco [2]. . 7 2.3 Projeção do números de assinantes globais em cada tecnologia, em milhões. Fonte: 5G Americas [3]. . . . . . . . . . . . . . . . . . . . . . . . . . . 7 2.4 Projeção para os acessos móveis no Brasil. Fonte: 5G Americas [4]. . . . . 8 2.5 Divisão lógica da rede móvel em rede de acesso e núcleo de rede. . . . . . 9 2.6 Topologia UMTS. Fonte: Qualcomm Wireless Academy [5]. . . . . . . . . 10 2.7 UTRAN - UMTS Terrestrial Radio Access Network. Fonte: Qualcomm Wireless Academy [5]. . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 2.8 Core Network Fonte: Qualcomm Wireless Academy [5]. . . . . . . . . . . 11 2.9 Arquitetura E-UTRAN . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 2.10 Valores típicos de RTWP. Fonte: telecomHall [6] . . . . . . . . . . . . . . 15 2.11 Softer Handover. Fonte: Teleco [7] . . . . . . . . . . . . . . . . . . . . . 15 2.12 Soft Handover. Fonte: Teleco [7] . . . . . . . . . . . . . . . . . . . . . . 16 2.13 Hard Handover. Fonte: Teleco [7] . . . . . . . . . . . . . . . . . . . . . 16 2.14 Inter-RAT Hard Handover. Fonte: Teleco [7] . . . . . . . . . . . . . . . . 17 2.15 Arquitetura tecnológica de um BI. [8] . . . . . . . . . . . . . . . . . . . 19 2.16 Estrutura do ETL. [9] . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 3.1 Arquitetura do Sistema. . . . . . . . . . . . . . . . . . . . . . . . . . . 24 3.2 Estrutura do EMS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 3.3 Diagrama de Fluxos do Processo de ETL. . . . . . . . . . . . . . . . . . 28 3.4 Exemplo de um arquivo XML contendo uma família com contadores de RRC. 30 3.5 Objeto de um arquivo XML contendo o nome do RNC, e o nome e identi- ficador de uma célula. . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 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. . . . . . . . . . . 31 3.7 Tempo de carga em uma base de dados vazia, em segundos. . . . . . . . . 32 3.8 Tempo de carga em uma base de dados já populada, em segundos. . . . . 32 3.9 Arquivo do exemplo anterior após a conversão para uma planilha em CSV. 32 3.10 Arquivo do exemplo anterior após o parser. . . . . . . . . . . . . . . . . 33 xi 3.11 Volume de dados por arquivo (MB). . . . . . . . . . . . . . . . . . . . . 35 3.12 Diagrama de Fluxos do Processamento dos Dados. . . . . . . . . . . . . . 36 3.13 Arquitetua MVC. Fonte: CodeIgniter Brasil [10]. . . . . . . . . . . . . . 38 3.14 Diagrama de um processador XSLT. . . . . . . . . . . . . . . . . . . . . 41 3.15 Arquivo DTD usado para validar um arquivo XML. . . . . . . . . . . . . 42 3.16 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 Organização do Banco de Dados. . . . . . . . . . . . . . . . . . . . . . . 51 4.2 Página inicial da ferramenta. . . . . . . . . . . . . . . . . . . . . . . . . 51 4.3 Menu com os indicadores de performance. . . . . . . . . . . . . . . . . . 52 4.4 Tecnologias disponíveis. . . . . . . . . . . . . . . . . . . . . . . . . . . 52 4.5 Agregações de KPIs disponíveis. . . . . . . . . . . . . . . . . . . . . . . 52 4.6 KPIs básicos de Accessibility e Retainability. . . . . . . . . . . . . . . . . 53 4.7 KPIs básicos de Traffic. . . . . . . . . . . . . . . . . . . . . . . . . . . 53 4.8 KPIs básicos de Service Integrity e Retention. . . . . . . . . . . . . . . . 54 4.9 KPIs básicos de Mobility. . . . . . . . . . . . . . . . . . . . . . . . . . . 54 4.10 KPIs básicos de Availability e Coverage. . . . . . . . . . . . . . . . . . . 54 4.11 Ranqueamento das piores células de um RNC. . . . . . . . . . . . . . . . 55 4.12 KPI de uma célula ao longo de um dia. . . . . . . . . . . . . . . . . . . 55 4.13 KPIs personalizados. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 4.14 Piores células para o KPI QDA PS . . . . . . . . . . . . . . . . . . . . . 56 4.15 KPIs básicos da rede LTE. . . . . . . . . . . . . . . . . . . . . . . . . . 57 5.1 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 RNCs disponíveis em cada um dos servidores do EMS . . . . . . . . . . . 26 3.2 Exemplo de uma tabela de uma família de contadores por RNC. . . . . . . 33 3.3 Exemplo de uma família com contadores por Node-B . . . . . . . . . . . 34 3.4 Exemplo de uma família decontadores do LTE . . . . . . . . . . . . . . 34 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 Application Programming Interface, p. 48 BI Business Intelligence, p. 4, 18, 39 CRUD Create, Read, Update, Delete, p. 38 CSV Comma-separated values, p. 31, 34, 41, 42, 45, 59 CS Circuit Switched, p. 9–14, 17 CTL Control Temporal Logic, p. 45 DTD Document Type Definition, p. 42 E-UTRAN Evolved Universal Terrestrial Radio Access, p. 11 EDGE 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 GSM EDGE Radio Access Network, p. 9 GGSN Gateway GPRS Support Node, p. 11 xiv GMSC Gateway Mobile Switching Center, p. 11 GPRS General Packet Radio Services, p. 5 GSM Global System for Mobile Communications, p. 5, 7, 9, 15–17, 59 GUI Graphical User Interface, p. 44 HSDPA High Speed Downlink Packet Access, p. 6, 14, 18 HSPA High Speed Packet Access, p. 6, 9 HSUPA High Speed Uplink Packet Access, p. 6 IP 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 Procedural Language/Structured Query Language, p. 39, 43, 44, 46 PSTN Public Switched Telephone Network, p. 11 PS 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 Received Total Wideband Power, p. 14 SGSN 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 Unidade da Federação, p. 27, 57 UMTS Universal Mobile Telecommunication System, p. 5, 6, 8, 9, 11, 15–17, 25, 29, 30, 35, 37, 50, 53, 57 USIN Universal Subscriber Identity Module, p. 9 UTRAN 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 pro- cesso 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 Ge- neration - Terceira Geração) e 4G (Fourth Generation - Quarta Geração). Neste sentido, pretende-se estudar os seguintes problemas: (1) como extrair dados de fon- tes 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 con- tratantes 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 re- lató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 necessi- dade 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 conhe- cimentos 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 ferramentaOpenSource (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 camposonde são realizadas a maior parte das con- sultas 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 servi- dor 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 ca- pí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 Tecnologias de telefonia móvel 2.1.1 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 se- gunda 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 tecno- logias 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 Tele- 5 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 Release- 2005 (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. Con- forme 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 conside- rar 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 1Atualmente (agosto de 2016), o número de habitantes no mundo passou a marca de 7,4 bilhões de pessoas.[18] 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 País 2008 2009 2010 2011 2012 2013 2014 MAno 1 China 641 747859 986 1.112 1.229 1.286 4,6% 2 Índia 347 525 752 894 865 886 944 6,5% 3 EUA 270 286 296 316 326 336 355 5,8% 4 Indonésia 141 159 220 237 281 304 319 4,9% 5 Brasil 152 174 203 242 262 271 281 3,6% 6 Rússia 188 208 215 228 231 243 221 -9,1% 7 Japão 110 115 121 130 138 146 153 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 veloci- dade 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 prin- cipais, 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 da- dos 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 ti- nham 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 : 100× RRC.SuccConnEstab.OrgConvCall +RRC.SuccConnEstab.OrgStrCall +RRC.SuccConnEstab.OrgInterCall +RRC.SuccConnEstab.OrgBkgCall +RRC.SuccConnEstab.OrgSubCall +RRC.SuccConnEstab.TmConvCall +RRC.SuccConnEstab.TmStrCall +RRC.SuccConnEstab.TmItrCall RRC.AttConnEstab.OrgConvCall +RRC.AttConnEstab.OrgStrCall +RRC.AttConnEstab.OrgInterCall +RRC.AttConnEstab.OrgBkgCall +RRC.AttConnEstab.OrgSubCall +RRC.AttConnEstab.TmConvCall +RRC.AttConnEstab.TmStrCall +RRC.AttConnEstab.TmItrCall 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ór- mula usa o conceito Sucessos/Tentativas. Também é possível usar o conceito [(Tentativas− Falhas)/Tentativas] 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, chama- das 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 stre- aming 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 segun- dos) 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 con- sumo 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 trans- ferê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 geral- mente 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, metodolo- gias, 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 vi- sualizaçã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 for- matos 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, Trans- form 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 ma- nutençã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, apli- cando 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, in- formaçõ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 gran- des 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 infor- maçã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 compre- ensã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. Ape- sar 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 in- formaçõ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 en- contram 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 ne- cessários ao sistema. O trabalho foi desenvolvido usando os servidores externos da empresa Huawei, mas poderiam ser usados os de qualquer outra empresa de teleco- municaçõ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 Ma- nagement 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-servidore 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 RNC 15 RNCMG01 RNCMG02 RNCMG03 RNCMG04 RNCMG05 RNCMG06 RNCMG07 RNCMS01 RNCMS02 RNCMS03 RNCMT02 RNCRO01 RNCTO01 40 RNCAL01 RNCBA01 RNCBA02 RNCBA03 RNCBA04 RNCBA05 RNCCE01 RNCCE02 RNCPB01 RNCPE01 RNCPE02 RNCPE04 RNCPI01 RNCPI02 RNCRN01 ATAE 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 confi- guraçã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 con- forme 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, pode- se 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 ine- ficientes 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 trans- formaçã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 tes- tados 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 to- das as etapas para o arquivo CSV demoraram aproximadamente 11 segundos. O segundo cenário é apresentado na Figura 3.8, onde o mesmo arquivo XML demo- rou aproximadamente 37 segundos e as etapas com o arquivo CSV próximo dos 14 segundos. 1O arquivo XML do exemplo anterior tinha 26 MB, ficando com 8 MB após a conversão para CSV. 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 eliminacabeç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 Cellname Cellid Datetime GP ... RNCAC01 UACABL01A 63096 2016-06-01 00:00 30 ... ... ... ... ... ... ... RNCSC02 USCTIO02G 64843 2016-06-01 23: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 Locell Datetime GP ... CBA0036S 0 2016-06-01 00:00 60 ... ... ... ... ... ... WTOTAQ08 2 2016-06-01 23:30 60 ... A tabela 3.4 mostra um exemplo para uma família do LTE, possuindo sem- pre as mesmas cinco primeiras colunas (eNodebID, eNodeb, Locall Cell, Cell- name,Datetime e GP) com contadores em amostras de uma hora. Tabela 3.4: Exemplo de uma família de contadores do LTE eNodebid eNodeb LoCellid Cellname Datetime GP ... 270016 EESCAR01 0 EESCAR01A 2016-06-01 00:00 60 ... ... ... ... ... ... ... ... 880216 ECESOL12 2 ECESOL12C 2016-06-01 23:30 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 pas- sar 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 fer- ramenta. É 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, alte- raçõ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 gover- nam 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 Materiais e Métodos 3.2.1 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 fer- ramentas 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 vanta- gens 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 mer- cado 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 pro- cesso, 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). Inicialmenteno 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 di- ferentes 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 computa- dores 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 pas- sado 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 Transfor- mation) OpenSource(Código Aberto) desenvolvido na linguagem Java pela empresa Saxonica. Com ele é possível transformar arquivos no formato XML para os forma- tos 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 con- forme 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 sim- ples 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 docu- mento 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 in- formaçã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 orien- tado 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 opera- cionais • 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 com- pleta: 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.
Compartilhar