Desempenho Acadêmico dos Beneficiários do PNAES
1. Descrição
Com base nos requisitos iniciais identificados não existem limitações.
Conexão direta do Metabase com a base de dados do Data Warehouse (dw.ufms.br).
Semestral.
2. Visão Geral dos filtros e indicadores do painel
2.1. Campo de filtro dedicado
O painel de sucesso acadêmico dos beneficiários da assistência estudantil é constituído por cinco campos de filtragem, cada um acompanhado de seus respectivos valores, a saber:
- Ano: ano em que o beneficiário da assistência estudantil recebeu o pagamento de auxílio(s) na UFMS.
- UAS: contém as Unidades da Administração Setorial da UFMS que possuem beneficiários da assistência estudantil.
- Curso: listagem de todos os cursos de graduação da UFMS que possuem beneficiários da assistência estudantil.
- Desempenho: nível de desempenho do beneficiário da assistência estudantil na UFMS.
- Aluno: possibilidade de pesquisar pelo nome o(s) beneficiário(s) da assistência estudantil na UFMS.
2.2. Indicadores
Destaca o percentual de estudantes de alto desempenho em relação ao total de beneficiários da assistência estudantil, além de fornecer informações sobre as metas estabelecidas no Plano de Desenvolvimento Institucional (PDI) para o período de 2020 a 2024.
- Campo de filtro dedicado para este indicador: todos os filtros.
- Ação ao clique do mouse: Apresenta o percentual de estudantes de alto desempenho em relação ao total de beneficiários da assistência estudantil para o ano selecionado.
Direciona para o site onde são apresentadas informações detalhadas sobre o objetivo 3.2 do PDI.
- Campo de filtro dedicado para este indicador: não há.
- Ação ao clique do mouse: direciona para o site do o objetivo 3.2 do PDI.
Apresenta uma análise detalhada dos dados do gráfico sobre a Taxa de Sucesso dos Beneficiários da Assistência Estudantil em uma tabela à esquerda. No lado direito, um gráfico de linhas compara a quantidade de beneficiários de alto desempenho com o total de beneficiários da assistência estudantil ao longo dos anos. Essa disposição facilita a compreensão das tendências ao longo do tempo.
- Campo de filtro dedicado para este indicador: todos os filtros.
- Ação ao clique do mouse: possibilita ordenar uma coluna em ordem crescente ou decrescente, e também, oferece a opção de filtragem por ano na tabela à esquerda. No gráfico à direita, é possível filtrar por ano para visualizar a quantidade de beneficiários de alto desempenho em relação ao total de beneficiários que receberam assistência estudantil durante o período selecionado.
Exibe de maneira detalhada as informações dos beneficiários de alto desempenho que receberam a assistência estudantil na UFMS.
- Campo de filtro dedicado para este indicador: todos os filtros.
- Ação ao clique do mouse: oferece a possibilidade de ordenar uma coluna em ordem crescente ou decrescente.
Exibe de maneira detalhada as informações de todos os beneficiários que receberam a assistência estudantil na UFMS.
- Campo de filtro dedicado para este indicador: todos os filtros.
- Ação ao clique do mouse: oferece a possibilidade de ordenar uma coluna em ordem crescente ou decrescente.
2.3. Exportação de dados
Os dados de cada indicador ou gráfico podem ser exportados nos formatos CSV, XLSX e JSON. Para realizar essa operação, o usuário deve clicar no ícone localizado no canto superior direito, representado por "..." e selecionar a opção "Baixe os resultados". É importante ressaltar que essa funcionalidade estará acessível somente para usuários autenticados no Metabase e que possuam as permissões de acesso adequadas.
3. Conjunto de dados
Diante da perda do antigo painel de indicadores do Sucesso Acadêmico dos Beneficiários da Assistência Estudantil, devido a um problema técnico nos servidores da UFMS, foi necessário reconstruir o referido painel. Para isso, optou-se por utilizar a tabela "fato_alunos_receberam_pagamento_bolsa_pnaes", desenvolvida no projeto 3.1 do PDI. Para possibilitar a reconstrução do painel, todos os dados relacionados a tabela "fato" foram importados para uma nova tabela, denominada "dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch", também localizada no mesmo schema (Dbo) do DW.
A escolha do prefixo 'dm' no nome da nova tabela ("dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch") reflete a ideia de um Data Mart, visando à organização e ao acesso facilitado aos dados essenciais para a análise da taxa de sucesso dos beneficiários da assistência estudantil. Essa estratégia se mostrou especialmente importante devido às limitações do software de BI utilizado, o Metabase, que não permite, por exemplo, a filtragem de dados anteriores a 2020 e não suporta perguntas com filtros vinculados. A aceitação desta abordagem ocorreu em conjunto com a Diretoria da DIPLAN/PROPLAN. A seguir será apresentada a estrutura da tabela "DM" desenvolvida:
Figura - tabela dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch
4. Codificação dos indicadores e gráficos
O dashboard e as perguntas do painel PDI/PPI 2020-2024 - 3.2 - Sucesso Acadêmico dos Beneficiários da Assistência Estudantil estão localizados na coleção Assistência Estudantil -> Objetivos PDI/PPI. O dashboard único encontra-se na subpasta "Objetivos PDI/PPI", enquanto as consultas estão contidas dentro da subpasta "Perguntas 3.2". Uma observação relevante no projeto: durante a fase de avaliação do painel, optou-se por substituir o termo "bolsista" por "beneficiário". Clique no nome de cada pergunta para visualizar o código SQL correspondente ou a estrutura do notebook nativo do Metabase:
Bolsistas de alto desempenho - Detalhado
Tipo de visualização: Tabela
Gráfico de Qtde bolsistas de alto desempenho e bolsistas por ano
with dados_total_bolsistas as (
select dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.academico_id, cast(dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.ano_recebeu_bolsa as int) as ano_recebeu_bolsa, dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.periodo_atual,
dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.periodo_max_curso, dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.desempenho,
dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.tipo_desempenho, dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.nome_curso, dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.unidade_sigla,
dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.nome_aluno
from dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch
where {{ano}} [[and {{tipo_desempenho}}]] [[and {{curso}}]] [[and {{unidade_sigla}}]] [[and {{nome_aluno}}]]
),
-- Realiza a contagem total dos bolsistas e dos bolsistas de alto desempenho
dados_calculo as (
SELECT cast(dados_total_bolsistas.ano_recebeu_bolsa as int) as ano_recebeu_bolsa, count(dados_total_bolsistas.academico_id) as total_bolsistas,
(
SELECT count(b2.academico_id)
from dados_total_bolsistas b2
WHERE b2.ano_recebeu_bolsa = dados_total_bolsistas.ano_recebeu_bolsa
and b2.tipo_desempenho = 'ALTO'
) as total_academico_alto_desempenho
FROM dados_total_bolsistas
group by dados_total_bolsistas.ano_recebeu_bolsa
)
-- Select principal com as metas do PDI 3.2, percentual
SELECT cast(ano_recebeu_bolsa as int) as Ano, total_academico_alto_desempenho as 'Beneficiários de Alto Desempenho', total_bolsistas as 'Total de Beneficiários', cast(cast(total_academico_alto_desempenho as float)/cast(total_bolsistas as float) as float)*100 as '% Beneficiários de Alto Desempenho'
FROM dados_calculo
order by ano_recebeu_bolsa desc
Tipo de visualização: Linhas
Qtde bolsistas de alto desempenho e bolsistas por ano
with dados_total_bolsistas as (
select dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.academico_id, cast(dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.ano_recebeu_bolsa as int) as ano_recebeu_bolsa, dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.periodo_atual,
dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.periodo_max_curso, dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.desempenho,
dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.tipo_desempenho, dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.unidade_sigla, dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.nome_curso,
dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.nome_aluno
from dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch
where {{ano}} [[and {{nome_curso}}]] [[and {{unidade_sigla}}]] [[and {{tipo_desempenho}}]] [[and {{nome_aluno}}]]
),
-- Realiza a contagem total dos bolsistas e dos bolsistas de alto desempenho
dados_calculo as (
SELECT cast(dados_total_bolsistas.ano_recebeu_bolsa as int) as ano_recebeu_bolsa, count(dados_total_bolsistas.academico_id) as total_bolsistas,
(
SELECT count(b2.academico_id)
from dados_total_bolsistas b2
WHERE b2.ano_recebeu_bolsa = dados_total_bolsistas.ano_recebeu_bolsa
and b2.tipo_desempenho = 'ALTO'
) as total_academico_alto_desempenho
FROM dados_total_bolsistas
group by dados_total_bolsistas.ano_recebeu_bolsa
)
-- Select principal com as metas do PDI 3.2, percentual
SELECT cast(ano_recebeu_bolsa as int) as Ano, total_academico_alto_desempenho as 'Benefic. de Alto Desempenho PNAES', total_bolsistas as 'Total de Beneficiários PNAES', cast(((cast(total_academico_alto_desempenho as float)/cast(total_bolsistas as float))*100) as float) as Realizado,
cast(CASE
WHEN ano_recebeu_bolsa = 2020 THEN 70
WHEN ano_recebeu_bolsa = 2021 THEN 72
WHEN ano_recebeu_bolsa = 2022 THEN 74
WHEN ano_recebeu_bolsa = 2023 THEN 76
WHEN ano_recebeu_bolsa = 2024 THEN 78
END as numeric(10,2)) as 'Meta PDI/PPI'
FROM dados_calculo
order by ano_recebeu_bolsa desc
Tipo de visualização: Tabela
Quantitativo sucesso acadêmico dos beneficiários da assistência estudantil - Ano
with dados_total_bolsistas as (
select dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.academico_id, dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.ano_recebeu_bolsa as ano_recebeu_bolsa, dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.periodo_atual,
dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.periodo_max_curso, dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.desempenho,
dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.tipo_desempenho, dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.unidade_sigla, dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.nome_curso,
dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch.nome_aluno
from dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch
where {{ano}} [[and {{nome_curso}}]] [[and {{unidade_sigla}}]] [[and {{tipo_desempenho}}]] [[and {{nome_aluno}}]]
),
-- Realiza a contagem total dos bolsistas e dos bolsistas de alto desempenho
dados_calculo as (
SELECT dados_total_bolsistas.ano_recebeu_bolsa as ano_recebeu_bolsa, count(dados_total_bolsistas.academico_id) as total_bolsistas,
(
SELECT count(b2.academico_id)
from dados_total_bolsistas b2
WHERE b2.ano_recebeu_bolsa = dados_total_bolsistas.ano_recebeu_bolsa
and b2.tipo_desempenho = 'ALTO'
) as total_academico_alto_desempenho
FROM dados_total_bolsistas
group by dados_total_bolsistas.ano_recebeu_bolsa
)
-- Select principal com as metas do PDI 3.2, percentual
SELECT ano_recebeu_bolsa as Ano, total_academico_alto_desempenho as 'Acadêmico de Alto Desempenho', total_bolsistas as 'Total de Beneficiários', cast(((cast(total_academico_alto_desempenho as float)/cast(total_bolsistas as float))*100) as float) as Total,
cast(CASE
WHEN ano_recebeu_bolsa = 2020 THEN 70
WHEN ano_recebeu_bolsa = 2021 THEN 72
WHEN ano_recebeu_bolsa = 2022 THEN 74
WHEN ano_recebeu_bolsa = 2023 THEN 76
WHEN ano_recebeu_bolsa = 2024 THEN 78
END as int) as 'Meta PDI/PPI'
FROM dados_calculo
order by ano_recebeu_bolsa
Tipo de visualização: Combo
Total de bolsistas - Detalhado
Tipo de visualização: Tabela
5. ETL de sincronização dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch no Pentaho PDI
Este ETL está armazenado no repositório Git da UFMS, localizado em: Pentaho Repository -> BI_UFMS -> PDI -> 3_2. O processo ETL começa com a leitura dos dados no step Table Input "Todos Bolsistas PNAES", onde a tabela principal é fato_alunos_receberam_pagamento_bolsa_pnaes, seguida pelas tabelas relacionais no banco de dados do DW. Este passo utiliza os seguintes parâmetros de conexão:
Figura - parâmetros de conexão
Este step é acompanhado pelo seguinte código SQL:
Todos Bolsistas PNAES
select p.rga, p.academico_id, p.pessoa_id, p.ano_competencia as ano_recebeu_bolsa, p.semestre_competencia as semestre_recebeu_bolsa, p.curso_id_atual,
p.periodo_atual, p.periodo_max_curso, p.ano_ingresso, p.cod_curso, p.cod_emec,
upper(a.nome_aluno) as nome_aluno,
g.nome_curso, g.unidade_sigla,
-- Seleciona o dado mais atual no ano colocando o semestre em ordem decrescente
ROW_NUMBER() OVER (PARTITION BY p.ano_competencia, p.academico_id ORDER BY p.semestre_competencia DESC) AS semestre_atualizado
from fato_alunos_receberam_pagamento_bolsa_pnaes p
left join dim_aluno a on a.cod_aluno = p.rga
left join dim_curso_graduacao g on p.cod_curso = g.cod_curso
where p.modalidade = 'Graduação'and p.ano_competencia >= 2020
GROUP BY p.rga, p.academico_id, p.pessoa_id, p.ano_competencia, p.semestre_competencia, p.curso_id_atual, p.periodo_atual, p.periodo_max_curso, p.ano_ingresso, p.cod_curso, p.cod_emec,
a.nome_aluno, g.nome_curso, g.unidade_sigla
No entanto, é importante notar que este código processa os dados anualmente e requer a obtenção das informações do semestre mais recente do beneficiário. Isso é alcançado usando a função row_number para agrupar os dados por ano_competencia, academico_id, e, em seguida, ordenando-os em ordem decrescente de semestre para obter o semestre mais recente do ano.
Posteriormente, o próximo step - Filter rows, seleciona apenas os dados mais recentes, ou seja, semestre_atualizado = 1.
Em seguida, ocorre a contabilização das cargas horárias no step - Database join "Contabiliza Carga Horária" (incluindo carga horária prevista, carga horária das disciplinas, carga horária das dispensas e carga horária de CCND) dos acadêmicos matriculados no ano e semestre correspondentes, utilizando o seguinte código SQL:
Contabiliza Carga Horária
-- Cálculo da ch prevista
select distinct o.academico_id, o.periodo as periodo_cursado,
(
select sum(d.carga_horaria) as carga_horaria_a_cumprir
from siscad.grades g
join siscad.cursos_sequencias cs on cs.id = g.curso_id
join siscad.disciplinas d on d.id = g.disciplina_id
-- Captura somente a última estrutura do curso de acordo com a tabela Academicos, somente disciplinas obrigatórias, soma todas as ch das disciplinas menor
-- ou igual ao período que houve matrícula na ocorrência e não contabiliza disciplinas de período 0
where g.curso_id = ? and g.tipo_disciplina_id = 'OBR' and (g.periodo <= o.periodo and g.periodo > 0)
group by curso_id
) as carga_horaria_prevista,
-- Cálculo da ch das disciplinas
coalesce((
select sum(at2.carga_horaria)
from siscad.academicos a
left join siscad.academicos_turmas at2 on at2.academico_id = a.id
left join siscad.grades g on g.id = at2.grade_id
left join siscad.disciplinas d on d.id = g.disciplina_id
join pessoas.fisicas f on f.pessoa_id = a.pessoa_id
where a.id = ? and at2.tipo_disciplina_id = 'OBR' and at2.tipo_matricula_situacao_id = 'AP' and (g.periodo <= o.periodo and g.periodo > 0)
group by a.curso_id
),0) as ch_acumulada_disciplinas,
-- Cálculo da ch das dispensas
coalesce((
SELECT sum(ad.carga_horaria)
from siscad.academico_dispensas ad
-- Adiciona a carga horária CCND dos dados alterados no ano e semestre que recebeu a bolsa
where ad.academico_id = ? and ad.tipo_disciplina_id = 'OBR' and date_part('year'::text, ad.data_alteracao) <= ? and
CASE
WHEN date_part('month'::text, ad.data_alteracao) < 7::double precision THEN 1
ELSE 2
END <= ?
group by ad.academico_id
order by ad.academico_id
),0) as ch_acumulada_dispensas,
-- Cálculo da ch CCND
coalesce((
SELECT sum(ac.carga_horaria_cumprida)
from siscad.academico_ccnds ac
join siscad.componentes_curriculares_nao_disciplinares ccnd on ccnd.id = ac.ccnd_id
join siscad.ccnd_situacao_ccnds csc on csc.id = ac.ccnd_situacao_ccnd_id
join siscad.situacao_ccnds sc on sc.id = csc.situacao_ccnd_id
-- Situacao_ccnd = Não Apresentado, Não Cumprida e Reprovado; bloqueado = usado para graduação, ccnd.tipo_ccnd_id - V = Enade
where sc.id not in (1,2,4) and ac.bloqueado = true and ccnd.tipo_ccnd_id not ilike 'V' and
-- Adiciona a carga horária CCND dos dados alterados no ano e semestre que recebeu a bolsa
ac.academico_id = ? and date_part('year'::text, ac.data_alteracao) <= ? and
CASE
WHEN date_part('month'::text, ac.data_alteracao) < 7::double precision THEN 1
ELSE 2
END <= ?
group by ac.academico_id
order by ac.academico_id
),0) as ch_acumulada_ccnd
from siscad.ocorrencias o
-- Verifica a matrícula na ocorrência (códigos 26 e 52)
where o.academico_id = ? and o.ano = ? and o.semestre = ? and o.tipo_ocorrencia_id in (26,52)
order by o.periodo
Os parâmetros provenientes do passo anterior são empregados no código SQL. Para compreender a ordem de leitura das variáveis contidas neste passo (identificadas pelo símbolo "?"), é necessário ler o código SQL de cima para baixo e da esquerda para a direita, seguindo a sequência da lista de variáveis: "Parâmetros para utilizar", conforme figura abaixo:
Figura - Parâmetros utilizados
O próximo step - Calculator "Soma todas as CH e calcula desempenho" realiza as operações matemáticas da carga horária total e do desempenho do aluno:
Figura - Soma todas as CH e calcula desempenho
Em seguida, o step - Fomula "Tipo de desempenho" classifica o desempenho do beneficiário como "Alto" ou "Baixo":
Figura - Tipo de desempenho
Posteriormente, o passo - Select Values "Valores selecionados", obtêm e formata os valores que serão incorporados à tabela "DM":
Figura - Aba Select & Alter
Figura - Meta-data
Logo após realiza-se a ordenação dos dados pelo step - Sort Rows "Ordena dados fato_alunos_receberam_pagamento_bolsa_pnaes":
Figura - Sort Rows
Em um processo paralelo, realiza-se à verificação dos dados já gravados na tabela dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch, para possibilitar a inclusão ou atualização das informações nela contidas. Devido à grande quantidade de dados entre as tabelas "fato" e "dm", optou-se por empregar esta metodologia de comparação entre ambas as tabelas, aprimorando o desempenho deste processo de inclusão e atualização, ao invés de utilizar os steps Table Input e Update que realiza a leitura registro por registro demandando um custo muito alto de tempo. A seguir serão apresentadas as configurações de conexão e o código SQL utilizadas neste passo:
Figura - Parâmetros de Conexão
Código SQL utilizado:
Ler dados dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch
select ano_recebeu_bolsa, cast(semestre_recebeu_bolsa as int) as semestre_recebeu_bolsa, rga, academico_id, pessoa_id,
curso_id_atual, periodo_cursado, ch_prevista, ch_acumulada_disciplinas, ch_acumulada_dispensas, ch_acumulada_ccnd,
ch_total, desempenho, ano_ingresso, periodo_atual, periodo_max_curso, cod_curso, cod_emec, tipo_desempenho,
nome_aluno, nome_curso, unidade_sigla
from dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch
Ainda neste mesmo processo ocorre a preparação dos dados da tabela "dm" com a ordenação de suas informações:
Figura - Ordena dados do fato_alunos_receberam_bolsa_pnaes_desempenho_ch
Neste estágio do ETL, através do step - Merge join "Compara dados", efetua-se a junção dos dados (Left outer) entre os campos-chave das tabelas "fato" e "dm", permitindo assim a execução do processo de inclusão ou atualização dos dados na tabela "dm":
Figura - Compara dados
Em seguida, o step - Filter Rows "Verifica dados novos", compara se há dados a serem incluídos na tabela "dm":
Figura - Verifica dados novos
Se forem identificados novos dados, as informações são encaminhadas para o step Formula "Adiciona última data de inclusão", onde a data atual de inclusão é registrada. Em seguida, os dados são adicionados por meio do step "Table output" chamado "Novos dados dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch", demonstrados conforme figura abaixo:
Figura - Adiciona últ. data inclusão
Observa-se que o step "Novos dados dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch" utiliza os mesmos parâmetros de conexão empregados no step anteriormente citado "Ler dados dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch":
Figura - Novos dados dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch
Se os dados detectados no step Filter rows - "Verifica dados novos" forem apenas para atualização, ou seja, se o valor deste step retornar "false", a única informação a ser atualizada será o nome do aluno. Essa atualização será validada no step Filter rows - "Verifica nome do aluno", onde o valor de retorno deste step é "true". Em seguida, o fluxo será direcionado para o step - Formula "Adiciona últ. data altualizacao", onde será incluída a data atual de alteração, e então os dados serão atualizados no step Update - "Atualiza nome do aluno".
Figura - Verifica nome do aluno
Figura - Adiciona últ. data altualizacao
Observa-se que o step - Update "Atualiza nome do aluno" utiliza os mesmos parâmetros de conexão empregados no step anteriormente citado "Ler dados dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch":
Figura - Atualiza nome do aluno
O ETL deverá ser executado no final de cada semestre.
Figura - ETL dm_fato_alunos_receberam_bolsa_pnaes_desempenho_ch