3.2. Sucesso Acadêmico dos Beneficiários da Assistência Estudantil - Informações técnicas
1. Dados do projeto
O painel ora desenvolvido proporciona uma visão completa e detalhada da taxa de sucesso acadêmico dos beneficiários da assistência estudantil na Universidade Federal de Mato Grosso do Sul.
Formas de ApresentaçãoCom 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