Apresentada a arquitetura do sistema de Business Intelligence implementado e as bases de dados relacionais são expostos nesta secção o Data Warehouse que foi projeto e implementado bem como o processo de ETL.
O modelo, ilustrado na Figura 3.6, integra quatro tabelas de factos: Incidencia_Pneumonais, Incidencia_Patologias, Incidencia_Incendios e Dados_Estatisticos. Cada uma destas, está ligada a um conjunto de tabelas de dimensão que permitem a análise dos dados disponíveis sob diferentes perspetivas. Relativamente às tabelas de dimensão, o modelo é constituído por dez tabelas: Individuo, Hospital, Local, Tempo_Mes, Tempo_Ano, Patologias, Incendio, Causa, Classificacao, Tipo_Incendio.
Figura 3.6: Modelo multidimensional do Data Warehouse.
Tal como o nome indica, a tabela de factos Incidencia_Pneumonias permite o armazenamento da informação relativa à incidência de pneumonias registadas em Portugal Continental. Esta tabela é constituída pelos factos: evento_pneumonia que corresponde a um contador de eventos utilizado para quantificar o número de pacientes com pneumonia, n_reingressos que indica quantas vezes o paciente reingressou ao hospital devido à pneumonia, dias_internamento que contabiliza o número de dias que o paciente permaneceu internado na unidade hospitalar e por fim a flag_vitima_mortal que indica se o paciente faleceu ou não durante o período de internamento (Tabela 3.15). Esta flag é uma variável do tipo Integer que toma o valor de 1 caso o paciente se trate de uma vítima mortal ou 0, caso contrário.
Tabela Incidencia_Pneumonias
Variáveis Descrição Tipo
cod_local Código da dimensão Local Int – Not Null
cod_mes Código da dimensão Tempo_Mes Int – Not Null
cod_individuo Código da dimensão Individuo Int – Not Null
cod_hospital Código da dimensão Hospital Int – Not Null
n_reingressos Número de reingressos do paciente Int – Not Null
evento_pneumonia Contador de eventos de pneumonias Int – Not Null
dias_internamento Dias de internamento do paciente Int – Not Null
flag_vitima_mortal Vítima mortal durante o internamento Int – Not Null
Tabela 3.15: Tabela de factos Incidencia_Pneumonias.
A Figura 3.7 apresenta um extrato da informação disponível na tabela de factos Incidencia_Pneumonias.
Figura 3.7: Excerto da informação contida na tabela Incidencia_Pneumonias.
A tabela de factos Incidencia_Incendios caracteriza-se pelo armazenado da informação relativa à ocorrência de incêndios em Portugal Continental. Esta tabela é constituída pelos factos: evento_incendio, que corresponde a um contador de eventos utilizado para quantificar o número de incêndios ocorridos, flag_falso_alarme que indica se a ocorrência se trata de um falso alarme, flag_reacendimento que informa se ocorreu um posterior reacendimento no local do incêndio. É composta ainda pelos factos: duração_inicio_intervencao, duração_intervencao e duração_incendio que correspondem a períodos de duração tendo em conta a data de início do incêndio, a data de intervenção e a data de extinção do incêndio. Por fim os factos: a_povoamento, a_mato, a_agricola, a_espaco_florestal e a_total que se referem a quantidades de área ardida (Tabela 3.16). Os factos flag_falso_alarme e flag_reacendimento
tratam-se de variáveis do tipo Integer, que tomam o valor de 1 em caso afirmativo ou 0, caso contrário.
Tabela Incidencia_Incendios
Variáveis Descrição Tipo
cod_incendio Código da dimensão Individuo Int - Not Null
cod_tipo_incendio Código da dimensão Patologias Int - Not Null
cod_local Código da dimensão Ano Int - Not Null
cod_mes Contador de eventos de patologias Int - Not Null
cod_causa Código da dimensão Causa Int - Not Null
cod_classificacao Código da dimensão Classificação Int - Not Null
flag_falso_alarme Indica se ocorrência se trata de um falso
alarme Int - Not Null
flag_reacendimento Indica se ocorreram reacendimentos Int - Not Null
evento_incendio Contador de eventos de incêndios Int - Not Null
duracao_inicio_intervencao Duração entre o alerta e a chegada ao local Int - Not Null
duracao_intervencao Duração de intervenção do incêndio Int - Not Null
duracao_incendio Duração total do incêndio Int - Not Null
a_povoamento Área de povoamento ardida Float – Not
Null
a_mato Área de mato ardida Float – Not
Null
a_agricola Área agrícola ardida Float – Not
Null
a_espaco_florestal Área de espaço florestal ardida Float – Not
Null
a_total Área total ardida Float – Not
Null
Tabela 3.16: Tabela de factos Incidencia_Incendios.
A Figura 3.8 apresenta um extrato de algumas das informações disponíveis na tabela de factos Incidencia_Incendios.
Figura 3.8: Excerto da informação contida na tabela Incidencia_Incendios.
A tabela Incidencia_Patologias armazena informação relativa a incidência de determinada patologia, nos pacientes portadores de pneumonia. Esta tabela é constituída pelos factos: evento_patologia, que corresponde a um contador de eventos utilizado para quantificar o número de pacientes com determinada patologia (Tabela 3.17). Esta tabela é uma factless fact table, à qual é atribuído o contador de eventos.
Tabela Incidencia_Patologias
Variáveis Descrição Tipo
cod_individuo Código da dimensão Individuo Int - Not Null
cod_patologia Código da dimensão Patologias Int - Not Null
cod_ano Código da dimensão Ano Int - Not Null
evento_patologia Contador de eventos de patologias Int - Not Null
Tabela 3.17: Tabela de factos Incidencia_Patologias.
A Figura 3.9 apresenta um extrato da informação disponível na tabela de factos Incidencia_Patologias.
Por último, a tabela de factos Dados_Estatísticos que armazena um conjunto de informações relativas a indicadores populacionais de Portugal Continental obtidos através dos Censos realizados em 20117 (Tabela 3.18).
Tabela Dados_Estatisticos
Variáveis Descrição Tipo
cod_local Código da dimensão Local Int - Not Null
cod_ano Código da dimensão Ano Int - Not Null
n_individuos_residentes Número de habitantes Int - Not Null
n_individuos_residentes_m Número de habitantes do sexo masculino Int - Not Null
racio_incpopulacao_f Número de habitantes do sexo feminino Int - Not Null
n_individuos_residentes_0a4 …
n_individuos_residentes_65
Número de habitantes por classes de
idades Int - Not Null
n_individuos_residentes_m_0a 4
…
n_individuos_residentes_m_65
Número de habitantes do sexo masculino
por classes de idades Int - Not Null
n_individuos_residentes_f_0a4 …
individuos_residentes_f_65
Número de habitantes do sexo feminino
por classes de idades Int - Not Null
Tabela 3.18: Tabela de factos Dados_Estatisticos.
A Figura 3.10 apresenta um extrato de algumas das informação disponíveís na tabela de factos Dados_Estatisticos.
Figura 3.10: Excerto da informação contida na tabela Dados_Estatisticos.
Relativamente às tabelas de dimensão o modelo é constituído pela tabela de dimensão Individuo que contém as informações de caris mais pessoal do individuo, como a idade e sexo. Para além destes dois atributos, foram integrados o atributo classe_idade e classe_internamento. O atributo classe_idade integra as classes [0-4], [5-9], [10-13], [14-19],
[20-24], [25-64] e [65+], as mesmas utilizadas pelos Censos, de forma a permitir o cálculo dos atributos da Tabela 3.18. Relativamente ao atributo classe_internamento a FPP sugeriu as classes “sem internamento”, [1-3], [4-6], [7-10], [11-29] e [30+] (Tabela 3.19).
Tabela Individuo
Variáveis Descrição Tipo
cod_individuo Código da dimensão que caracteriza o
paciente Int - Not Null
sexo Sexo do paciente Varchar(45) - Not Null
idade Idade do paciente o paciente Int - Not Null
classe_idade Classe da idade do paciente Varchar(45) – Not Null
classe_d_internamento Classe de internamento do paciente Varchar(45) – Not Null
latitude_shk Coordenada latitudinal do paciente Varchar(45) – Not Null
longitude_shk Coordenado longitudinal do paciente Varchar(45) – Not Null
Tabela 3.19: Tabela de dimensão Individuo.
A tabela de dimensão Local armazena as informações disponíveis relativas à localização, nomeadamente o distrito, concelho e freguesia (Tabela 3.20). Os atributos relativos ao cod_distrito, cod_concelho e cod_freguesia correspondem aos códigos atributos pelo INE.
Tabela Local
Variáveis Descrição Tipo
cod_local Código da dimensão que caracteriza o local Int – Not Null
cod_dtccfr Código INE Varchar(45) - Not Null
cod_distrito Código do distrito Varchar(45) - Not Null
desig_distrito Designação do distrito Varchar(45) – Not Null
cod_concelho Código do concelho Varchar(45) Not Null
desig_concelho Designação do concelho Varchar(45) Not Null
desig_freguesia Designação da freguesia Varchar(45) Not Null
cod_freguesia Código da freguesia Varchar(45) Not Null
latitude Coordenada latitudinal Varchar(45) Not Null
longitude Coordenado longitudinal Varchar(45) Not Null
Relativamente à tabela de dimensão Hospital, esta armazena as informações relativas ao hospital em que o paciente reingressou, nomeadamente a sigla e designação de cada hospital (Tabela 3.21).
Tabela Hospital
Variáveis Descrição Tipo
cod_hospital Código da dimensão Hospital Int – Not Null
sigla_hospital Sigla do hospital Varchar(45) – Not Null
desig_hospital Designação do hospital Varchar(45) – Not Null
Tabela 3.21: Tabela de dimensão Hospital.
A tabela de dimensão Tempo_Mes armazena as informações relativas aos dados temporais, nomeadamente: dia, mês, ano e trimestre (Tabela 3.22).
Tabela Tempo_Mes
Variáveis Descrição Tipo
cod_mes Código da dimensão Tempo_Mes Int - Not Null
dia Dia de entrada do paciente no hospital Varchar(45) – Not Null
mes Mês de entrada do paciente no hospital Varchar(45) – Not Null
trimestre Trimestre de entrada do paciente no
hospital Varchar(45) – Not Null
Tempo_ano_cod_ano Ano de entrada do paciente no hospital Int - Not Null
Tabela 3.22: Tabela de dimensão Tempo_Mes.
A tabela de dimensão Tempo_ano foi adicionada ao Data Warehouse pela introdução das tabelas de factos: Dados_Estatisticos, uma vez que os dados contidos nelas integram os dados dos censos que são organizados por ano e não por dia nem mês (Tabela 3.23).
Tabela Tempo_Ano
Variáveis Descrição Tipo
cod_ano Código da dimensão Tempo_Ano Int – Not Null
ano Ano de entrada do paciente no hospital Int – Not Null
A tabela de dimensão Patologias armazena informações relativas a um conjunto de patologias possíveis (Tabela 3.24). Cada patologia é caracterizada por uma identificação científica e pela respetiva designação tendo em conta o documento denominado “Clusters de Patologias.doc”8 cedido pela FPP. Nesse documento encontram-se as patologias que revelam
maior importância para o estudo do comportamento da pneumonia.
Para as patologias que não fazem parte do documento mencionado, foi atribuída a designação Não disponível.
Tabela Patologias
Variáveis Descrição Tipo
cod_patologia Código da dimensão Patologia Int – Not Null
n_patologia Identificação científica da patologia Varchar(45) – Not Null
desig_patologia Designação da patologia Varchar(45) – Not Null
Tabela 3.24: Tabela de dimensão Patologias.
A tabela de dimensão Incendio armazena a localização exata do incêndio recorrendo às coordenadas longitudinais e latitudinais (Tabela 3.25).
Tabela Incendio
Variáveis Descrição Tipo
cod_incendio Código da dimensão Incendio Int – Not Null
latitude Coordenada latitudinal do incêndio Varchar(45) – Not Null
longitude Coordenada longitudinal do incêndio Varchar(45) – Not Null
Tabela 3.25: Tabela de dimensão Incendio.
A tabela de dimensão Classificacao armazena informação que caracteriza o incêndio quanto à classificação: Fogacho, Queimada, Incendio ou Agricola (Tabela 3.25).
Tabela Classificacao
Variáveis Descrição Tipo
cod_classificacao Código da dimensão Classificacao Int –Not Null
classificacao Designação da classificação do incêndio Varchar(45) – Not Null
Tabela 3.26: Tabela de dimensão Classificacao.
Relativamente à tabela de dimensão Causa armazena informação que caracteriza o incêndio quanto à sua causa (Tabela 3.27). Os valores relativos aos atributos cod_causa e tipo_causa foram definidos tendo em conta o documento “Codificação e definição das categorias das causas.pdf”9.
Tabela Causa
Variáveis Descrição Tipo
cod_causa Código da dimensão Causa Int – Not Null
tipo_causa Designação do tipo de causa do incêndio Varchar(45) – Not Null
Tabela 3.27: Tabela de dimensão Causa.
Por último, a tabela de dimensão Tipo_Incendio armazena informação que caracteriza o incêndio quanto ao tipo: Florestal, Falso Alarme, Agrícola, Queimada (Tabela 3.28).
Tabela Tipo_Incendio
Variáveis Descrição Tipo
cod_tipo_incendio Código da dimensão Tipo_Incendio Int – Not Null
tipo_incendio Designação do tipo de incêndio Varchar(45) – Not Null
Tabela 3.28: Tabela de dimensão Tipo_Incendio.
Após a apresentação do modelo de Data Warehouse nomeadamente das tabelas de factos e de dimensão que o constituem, bem como a exposição e explicação dos atributos, é altura de analisar o conjunto inicial de dados e proceder às transformações necessárias para posterior carregamento dos dados para o Data Warehouse.
O processo de ETL integra a extração dos dados através de procedimentos adequados, a transformação e limpeza dos dados para garantir a qualidade destes e o posterior carregamento dos dados para o Data Warehouse garantindo a consistência e a integridade dos dados. Como já referido neste documento, a qualidade das soluções de Business Intelligence é proporcional à qualidade dos dados, ou seja, quanto maior for a qualidade dos dados melhor será a qualidades das soluções de Business Intelligence obtidas, daí a elevada importância desta fase no processo de Business Intelligence.
O primeiro passo no processo de ETL foi a identificação de erros e dados omissos nos conjuntos iniciais de dados e a sua respetiva correção. Para além disso, procedeu-se à
transformação de alguns dados, como por exemplo, o atributo sexo caracterizava-se pelos valores, 1 para o sexo masculino e 2 para o sexo feminino e de forma a tornar a análise mais intuitiva, o sexo masculino passou a ser representado pelo caracter m e o sexo feminino pelo caracter f.
Para a deteção de dados errados ou omissos foi utilizada a ferramenta Talend Open Studio for Data Quality. Esta ferramenta permite a análise de dados recorrendo a gráficos, que tornam mais fácil a visualização dos resultados e avaliação do nível de qualidade dos dados bem como uma serie de funcionalidades adicionais10, Figura 3.11.
Figura 3.11: Ambiente de trabalho da ferramenta Talend Open Studio for Data Quality.
Para correção dos dados errados e omissos detetados, foram pensadas e aplicadas técnicas, como cálculos de média e moda, de forma a obter valores o mais próximos possível do valor real. Uma vez que os dados iniciais estavam em ficheiros excel, a correção foi feita recorrendo essencialmente a scripts desenvolvidas na linguagem VBA. Por sua vez, as transformações efetuadas foram realizadas recorrendo também a scripts desenvolvidas na linguagem VBA e também à ferramenta Talend Open Studio for Data Integration.
Para obter informações mais detalhadas sobre estas operações basta aceder ao relatório da qualidade dos dados das pneumonias e ao relatório da qualidade dos dados dos incêndios11.
Após a fase de limpeza e transformação dos dados procedeu-se ao carregamento destes para o Data Warehouse recorrendo à ferramenta Talend Open Studio For Data Integration12, que
fornece um conjunto de ferramentas de integração de dados flexível que permite aceder, transformar e integrar dados de diferentes fontes de dados. Neste caso particular as fontes de dados foram tabelas de base de dados relacionais.
A Figura 3.12 mostra um exemplo de um dos projetos desenvolvidos no Talend Open Studio For Data Integration que efectua o carregamento de dados para uma tabela, com uma função adicionar que transforma a data de nascimento com formato inicial dd-mm-yyyy para o formato yyyy-mm-dd.
Figura 3.12: Ambiente de trabalho da ferramenta Talend Open Studio For Data Integration.
Portanto, após os dados iniciais serem extraídos, limpos, transformados e carregados para o Data Warehouse, estes ficam disponíveis para as posteriores análises.
11 O relatório relativo aos dados das pneumonias encontra-se no anexo A e o relatório relativo aos dados dos
4
DATA ANALYTICS
Apresentado o modelo Data Warehouse implementado para o armazenamento dos dados são agora apresentados um conjunto de Dashboards resultantes da análise de indicadores que caracterizam o comportamento das pneumonias e dos incêndios em Portugal Continental entre o ano de 2002 e 2011. Estas análises foram efetuadas utilizando a ferramenta Tableau13
que permite criar diversos dashboards integrando a informação das diversas tabelas de factos.