3 METODOLOGIA
4.2 MODELAGEM DIMENSIONAL
5.1.5 Microsoft Excel
Excel é um software da Microsoft para criação e edição de planilhas eletrônicas, com a opção de utilizar a linguagem Visual Basic for Aplications (VBA). Neste trabalho o Excel
é utilizado para validação de dados, comparativos de tabelas, filtros simples e categorização manual de hierarquias.
5.1.6 SQL Power Architect
SQL Power Architect é um software da Best of BI criada por designers de DW e possui muitos recursos exclusivos voltados especificamente para o arquiteto de DW. Ele permite que os usuários façam engenharia reversa de bancos de dados existentes, executem a criação de perfis de dados em bancos de dados de origem e gerem automaticamente metadados ETL.
5.1.7 DAX
Data Analysis Expressions (DAX) é a linguagem utilizada pelo Microsoft Power
BI para a criação de colunas e medidas personalizadas. Nela consta funções e operadores que podem ser combinadas para criar fórmulas simples ou complexas com o objetivo de fornecer uma melhor análise de dados para o usuário.
5.2 EXPERIMENTO
De acordo com a problemática e conforme o objetivo, ambos descritos no capítulo 1, este trabalho consiste na criação de uma solução de BI com os indicadores disponibilizados pelo CNJ. A figura 13 ilustra a solução completa e no quadro 6, as explicações de cada etapa do fluxo.
Figura 13 – Processo Desenvolvimento
Fonte: Elaborado pelos autores.
Quadro 6 – Processo de Desenvolvimento Passo Descrição
1 Os tribunais preparam o envio dos dados referentes aos indicadores definidos pelo SIESPJ.
2 O CNJ recebe e trata os dados enviados pelos tribunais.
3 Os dados são disponibilizados ao público no portal do CNJ em formato .csv para
download.
4 Os dados passam por um tratamento prévio e necessário no Pentaho Data Intagration. 5 Os dados são enviados para uma base SQL Server no banco STG.
6 Com os dados disponíveis no Banco STG, utiliza-se o Pentaho Data Intagration para o início do processo de modelagem dimensional.
7 Os dados são enviados para uma base SQL Server no banco DWCNJ.
8 São criados relatórios utilizando Power BI com os dados disponíveis no banco DWCNJ. 9 Usuários finais consomem e utilizam o relatório criado.
Fonte: Elaborado pelos autores.
Na próxima subseção, são apresentadas informações sobre os arquivos utilizados neste trabalho.
5.2.1 Arquivos
Nessa etapa foi realizada o download da base de dados do Justiça em Números disponibilizada pelo CNJ e Instituto Brasileiro de Geografia e Estatística (IBGE). Os arquivos disponibilizados foram renomeados e descritos conforme o quadro 7.
Quadro 7 – Arquivos Fonte
Nome Origem Novo Arquivo Fonte Descrição
Variaveis_27-Ago- 2018.csv
Variaveis.csv CNJ Possui os dados de nome e id dos indicadores
JN_27-Ago-2018.csv Indicadores.csv CNJ Possui os dados de valor dos indicadores por dados de tribunal e ano.
05eab2c73cfa6de84 c1c02013ab1fd79.xl s
Documentacao.csv CNJ Possui dados de classificação dos indicadores da Justiça Militar Estadual.
8c3f0d00a7f254aad 6173049da0ed37d.x ls
Documentacao.csv CNJ Possui dados de classificação dos indicadores da Justiça Eleitoral.
b487a7b61f7c6e46c 89003faa8e8d5c6.xl s
Documentacao.csv CNJ Possui dados de classificação dos indicadores da Justiça Federal.
b33a6cb00c8c94248 7e61ac3e6fffbe3.xls
Documentacao.csv CNJ Possui dados de classificação dos indicadores da Justiça do Trabalho.
140817d734eed2be ab5d7c8ef1eb270a. xls
Documentacao.csv CNJ Possui dados de classificação dos indicadores da Justiça Estadual.
8cbc236817f57eca5 73f25b5581fc9ab.xls
Documentacao.csv CNJ Possui dados de classificação dos indicadores da Justiça Militar da União.
2f89d07e46d1d4ce7 613e6d5c5244a73.xl s
Documentacao.csv CNJ Possui dados de classificação dos indicadores do Tribunal Superior Eleitoral.
fb3f40c13f9ff05425 d23abc306dcc4b.xls
Documentacao.csv CNJ Possui dados de classificação dos indicadores do Tribunal Superior do Trabalho.
3fed704d3cceed47d 667919b4a51376d.x ls
Documentacao.csv CNJ Possui dados de classificação dos indicadores do Superior Tribunal de Justiça.
UfEstado.csv UfEstado.csv IBGE Possui dados do nome do estado e unidade federativa.
AnoEleicao.csv AnoEleicao.csv Autoria Própria
Possui dados de ano e se foi um ano que teve eleições para governador e presidente.
Fonte: Elaborado pelos autores.
5.2.2 Banco de Dados
Após a definição dos arquivos fontes, foi realizado a criação dos bancos de dados SQL Server para staging e DW, respectivamente nomeados como STG e DWCNJ.
O banco de dados STG foi modelado de acordo com os scripts presentes no apêndice Y e possui as seguintes tabelas:
a) anoeleicao. b) documentacao. c) indicadores. d) ufestados. e) variáveis.
O banco de dados DWCNJ possui as tabelas do modelo dimensional de acordo com o apresentado na seção 4.2 e foi modelado de acordo com os scripts presentes no apêndice X.
5.2.3 Transformação Staging
As transformações Staging conforme explicado na subseção 2.2.7, são realizadas para tratar previamente os dados e armazena-los no mesmo banco de dados.
A tabela stg_indicadores foi criada para armazenar as informações presentes no arquivo Indicadores.csv disponibilizado pelo CNJ. Conforme a figura 14, antes de enviar os dados para a tabela stg_indicadores, foi necessário submete-los a um step chamada Row
Normaliser, que transforma colunas em linhas, dada a razão de que o número de colunas do
arquivo fonte, ultrapassava o limite de 1024 colunas do SQL Server, em seguida, os dados são enviados para o Banco de Dados STG.
Figura 14 – Transformação Staging Indicadores
Fonte: Elaborado pelos autores.
As demais transformações staging, conforme a figura 15, possuem apenas input dos arquivos fonte .csv e output para o banco de dados STG.
Figura 15 – Transformação Staging Documentacao
Fonte: Elaborado pelos autores.
Na próxima subseção, é apresentado informações sobre a transformação da dimensão porte.
5.2.4 Transformação dPorte
Conforme a figura 16, para a dimensão de porte do tribunal é realizado uma consulta distinta dos campos idporte e porte da tabela de Indicadores do banco STG, o código está ilustrado na figura 17, os campos são renomeados para respectivamente cdPorte e nmPorte e em seguida realizada a carga dos dados para a tabela dPorte do banco DWCNJ, onde serão adicionados os campos de controle skPorte, date_from, date_to e version.
Figura 16 – Transformação dPorte
Figura 17 – Select Input dPorte
Fonte: Elaborado pelos autores.
Na próxima subseção, é apresentado informações sobre a transformação da dimensão tempo.
5.2.5 Transformação dTempo
Conforme a figura 18, para a dimensão de tempo é realizado uma consulta distinta dos campos Ano e flEleicao, respectivamente das tabelas de Indicadores e anoeleicao do banco STG, o código está ilustrado na figura 19, são renomeados para respectivamente nuAno e
flEleicao e em seguida realizada a carga dos dados para a tabela dTempo do banco DWCNJ,
onde serão adicionados os campos de controle skTempo, date_from, date_to e version.
Figura 18 – Transformação dTempo
Figura 19 – Select Input dTempo
Fonte: Elaborado pelos autores.
Na próxima subseção, é apresentado informações sobre a transformação da dimensão tribunal.
5.2.6 Transformação dTribunal
Conforme a figura 22, para a dimensão de tribunal é realizado uma consulta dos campos justiça, sigla, dsc_tribunal, uf_Sede e uf_abrangida da tabela de Indicadores e os campos unidade e UF da tabela ufestados do banco STG respectivamente ilustrado pela figura 20 e figura 21, o campo dsc_tribunal é dividido em dois novos campos: Tipo Tribunal e Regiao.
Figura 20 – Select Input dTribunal
Fonte: Elaborado pelos autores.
Figura 21 – Select Input dTribunal 2
É realizado uma junção entre as duas tabelas pelos campos UF e uf_sede, após isso são selecionados os campos justica, sigla, Tipo Tribunal, uf_sede, Unidade e uf_abrangida e renomeados respectivamente para nmJustica, cdTribunal, deTipo, nmUF, nmEstado,
deAbrangencia.
Valores nulos na tabela são tratados e em seguida realizada a carga dos dados para a tabela dTribunal do banco DWCNJ, onde serão adicionados os campos de controle
skTribunal, date_from, date_to e version.
Figura 22 – Transformação dTribunal
Fonte: Elaborado pelos autores.
Na próxima subseção, é apresentado informações sobre a transformação da dimensão indicador.
5.2.7 Transformação dIndicador
Conforme a figura 25, para a dimensão de indicador é realizado uma consulta dos campos categoria, Sub Categoria e Chave da tabela de documentacao e os campos sigla e
dsc_sigla da tabela de variaveis do banco STG, respectivamente ilustrado pela figura 23 e figura
24, é realizado uma junção entre as duas tabelas pelo campos sigla e Chave.
Figura 23 – Select Input dIndicador
Fonte: Elaborado pelos autores.
Figura 24 – Select Input dIndicador 2
Fonte: Elaborado pelos autores.
Após isso são selecionados os campos sigla, dsc_sigla, Categoria e Sub Categoria e renomeados respectivamente para cdIndicador, nmIndicador, nmCategoria e nmSubcategoria.
Valores nulos na tabela são tratados e em seguida realizada a carga dos dados para a tabela dIndicador do banco DWCNJ, onde serão adicionados os campos de controle
skIndicador, date_from, date_to e version.
Figura 25 – Transformação dIndicador
Na próxima subseção, é apresentado informações sobre a transformação da tabela fato indicador.
5.2.8 Transformação fIndicador
Conforme a figura 27, para a fato de indicadores é realizado uma consulta dos campos ano, sigla, indicadores, idporte e valor da tabela de indicadores do banco STG, o código está ilustrado na figura 26, e renomeados para respectivamente os campos cdAno,
cdTribunal, cdIndicador, cdPorte e vlIndicador.
Figura 26 – Select Input fIndicador
Fonte: Elaborado pelos autores.
Valores do tipo texto no campo vlIndicador são substituídos por nulo, após isso, utiliza-se o steps chamado Dimension Lookup / Update, que permite por meio da chave natural, trazer o último registro da surrogate key das dimensões dTempo, dTribunal, dIndicador e dPorte,
São removidos os campos cdAno, cdTribunal, cdIndicador e cdPorte e por último os dados são enviados para a tabela fIndicadores do banco DWCNJ
Figura 27 – Transformação fIndicador
Fonte: Elaborado pelos autores.
Na próxima subseção, é apresentado informações sobre os Jobs utilizados no desenvolvimento.
5.2.9 Job Staging
O Job Staging irá transferir os dados .csv para tabelas no banco de dados STG, conforme a figura 28, em sequência, o job executará as transformações das tabelas variaveis, ufestados, indicadores, documentacao e anoeleicao.
Figura 28 – Job Staging
Fonte: Elaborado pelos autores.
Na próxima subseção, é apresentado o processo de Job DW, explicando o seu funcionamento e utilização.
5.2.10 Job DW
O Job DW irá transferir das tabelas variaveis, ufestados, indicadores, documentacao e anoeleicao do banco STG para tabelas no banco de dados DW, conforme a figura 29, em sequência, o job executará as transformações das tabelas dimensão dPorte, dTempo, dIndicador, dTribunal e fIndicadores.
Figura 29 – Job DW
Na próxima subseção, é apresentado o processo do Power BI, explicando o seu funcionamento e utilização.
5.2.11 Protótipo
Conforme dito na subseção 5.1.1 a ferramenta escolhida para visualização de dados foi o Microsoft Power BI, a figura 30 ilustra a tela inicial do sistema na versão desktop e a figura 31 ilustra a tela de versão mobile.
Figura 30 – Tela Inicial do Sistema Desktop
Figura 31 – Tela Inicial do Sistema Mobile
Fonte: Elaborado pelos autores.
Por padrão os dados exibidos são sempre do último ano disponível ou selecionado. Todos os elementos gráficos interagem entre si como filtros, ao clicar em um dado dentro de um elemento gráfico é possível filtrar um outro elemento gráfico.
Para melhor entendimento do usuário final, foi realizada a alteração do nome de alguns campos do modelo dentro do Power BI conforme o quadro 8, demais campos não citados no quadro e presentes no modelo dimensional da figura 11 foram ocultados.
Quadro 8 – Campos Power BI
Nome Origem Novo Campo
nmCategoria Categoria
cdIndicador Código Indicador
nmIndicador Indicador
nmSubcategoria Sub Categoria
nmPorte Porte
nuAno Ano
flEleicao Teve Eleição?
deAbragendo Abrangência nmEstado Estado nmJustica Justiça deTipo Tipo cdTribunal Tribunal nmUF UF
Fonte: Elaborado pelos autores.
As medidas Data Analysis Expressions (DAX) criadas dentro do Power BI estão no apêndice D.
5.2.11.1 Filtros
Os elementos gráficos de filtro, localizados no lado esquerdo da figura 30, possuem a funcionalidade de segmentar os dados.
Figura 32 – Filtro Indicador
Fonte: Elaborado pelos autores.
Ilustrado pela figura 32, o botão representado pela legenda 1 permite ativar o modo de foco, o qual expande o elemento gráfico em tela cheia, o botão representado pela legenda 2 permite utilizar funções de pesquisa, exportar dados, mostrar dados, destaque, classificar em ordem decrescente e classificar em ordem crescente, o botão representado pela legenda 3 permite limpar seleções aplicadas e o botão representado pela legenda 4 permite visualizar e selecionar dados para filtrar.
5.2.11.2 Cards
Os cards, localizados na parte superior da figura 30 trazem de forma direta valores do indicador previamente selecionado, há duas formas de visualizar os dados, filtrando apenas um indicador ou filtrando um indicador e um tribunal. Na figura 33 foi filtrado apenas o indicador: Arq – Processos arquivados definitivamente.
Figura 33 – Cards com Indicador Filtrado
Fonte: Elaborado pelos autores.
Ainda de acordo com a figura 33, no card representado pela legenda 1 solicita que seja filtrado um tribunal e um indicador, o card representado pela legenda 2 tem como informação a média do valor do indicador selecionado de todos os tribunais e o card representado pela legenda 3 separa a informação do card de legenda 2 por porte do tribunal.
Figura 34 – Cards com Indicador e Tribunal Filtrados
Fonte: Elaborado pelos autores.
Ao filtrar um indicador e um tribunal, conforme a figura 34 é possível ter a outra visão dos dados. No card representado pela legenda 1 é apresentado o valor do indicador para o tribunal filtrado, o card representado pela legenda 2 tem como informação a média do valor do indicador selecionado de todos os tribunais exceto o tribunal filtrado e o card representado pela legenda 3 separa a informação do card de legenda 2 por porte do tribunal.
5.2.11.3 Gráficos Principais
Os elementos gráficos localizados na parte central da figura 23 e conforme a figura 27 possuem os dados detalhados dos indicadores.
Figura 35 – Gráficos
Fonte: Elaborado pelos autores.
De acordo com a figura 35, os elementos gráficos estão divididos em:
a) Detalhado: possui dados da média do valor do indicador pela seguinte hierarquia de dados: Categoria, Subcategoria, Indicador e Tribunal.
b) Média do Indicador: possui dados da média do valor do indicador pela seguinte hierarquia de dados: Tribunal e Ano.
c) Média por Porte: possui dados da média do indicador por porte, caso haja um tribunal selecionado, os dados do mesmo não são calculados para média do porte correspondente.
d) Evolução Porte: Situação anual da classificação do porte do tribunal.
Os elementos gráficos Detalhado e Média do Indicador possui as opções de realizar operações OLAP de acordo com a subseção 2.5.1.
Figura 36 – Operações OLAP
Fonte: Elaborado pelos autores.
Conforme a figura 36, o botão representado pela legenda 1 permite a operação drill
up, o botão representado pela legenda 2 permite a operação drill down, o botão representado
pela legenda 3 permite a operação drill across, o botão representado pela legenda 4 permite a operação drill throught, o botão representado pela legenda 5 permite ativar o modo de foco, o qual expande o elemento gráfico em tela cheia e o botão representado pela legenda 6 permite utilizar funções de exportar dados, mostrar dados, destaque e opções de classificação.
As perguntas estratégicas definidas na subseção 4.1.1 são respondidas conforme o quadro 9 de acordo com os elementos gráficos da figura 30.
Quadro 9 – Perguntas Estratégicas x Elementos Gráficos
Pergunta estratégica Elemento(s) gráfico(s)
Quais são os indicadores? Detalhado.
Qual é o histórico do indicador selecionado? Média do Indicador por Ano. Qual o porte atual do tribunal e seu histórico? Evolução Porte.
Qual é a média do indicador selecionado? Cards, Média por Porte,
Média do Indicador por Ano e detalhado.
Comparativo do tribunal selecionado com outros tribunais do mesmo porte.
Qual a média de todos os outros tribunais desse indicador?
Cards.
Qual a média por porte do indicador selecionado? Cards.
Qual a categorização e subcategorização de um determinado indicador?
Detalhado.
Quais são os tipos de tribunais disponíveis para análise? Filtro Tipo. Fonte: Elaborado pelos autores.
Na próxima seção, é apresentado a avalição de entrevista com o stakeholder.