Esta análise está baseada em dados abertos do TCE-MG, disponível em https://dadosabertos.tce.mg.gov.br/view/xhtml/paginas/downloadArquivos.xhtml), referente aos meses de Janeiro a Outubro de 2018. O conjunto de dados utilizado para as Despesas está no arquivo desp_pessoal.zip
O arquivo desp_pessoal possui 853 subpastas (uma pasta para cada município de MG), contento os dados descritivos das despesas efetuadas em cada mês, agrupado por categoria e separados por valores do Executivo, Legislativo e o total por Município.
Visando completar a análise, foi retirado do site do IBGE dados sobre a população dos municípios de Minas Gerais, disponível em https://www.ibge.gov.br/estatisticas-novoportal/sociais/populacao/9103-estimativas-de-populacao.html?=&t=downloads,
Segue o passo a passo utilizado nesta análise.
Passo 1: Foi criada uma base de dados no SQL Server, visando a construção de um pequeno DW (Data Warehouse) no formato estrela.
CREATE TABLE tbl_descricao(
id_despesa INT IDENTITY NOT NULL,
cod_despesa varchar(50) NOT NULL,
desc_despesa varchar(200) NULL
CONSTRAINT id_despesa_PK PRIMARY KEY (id_despesa),
CONSTRAINT cod_despesa_unique UNIQUE (cod_despesa)
)
CREATE TABLE tbl_municipio(
id_municipio INT IDENTITY NOT NULL,
cod_municipio varchar(50) NOT NULL,
nome_municipio varchar(200) NULL
CONSTRAINT id_municipio_PK PRIMARY KEY (id_municipio),
CONSTRAINT cod_municipio_unique UNIQUE (cod_municipio)
)
CREATE TABLE tbl_despesa_pessoal(
id_despesa_pessoal INT IDENTITY NOT NULL,
id_municipio INT NOT NULL,
id_despesa INT NOT NULL,
ano INT NOT NULL,
mes INT NOT NULL,
vlr_executivo float,
vlr_legislativo float,
vlr_municipio float
CONSTRAINT id_despesa_pessoal_PK PRIMARY KEY (id_municipio, id_despesa, ano, mes)
)
2º. Passo – Para realizar a carga de dados utilizou-se a ferramenta ETL do SQL Server Integration Services (SSIS).
Fez-se uso do Foreach loop Container para percorrer todas as subpastas e selecionar os arquivos do tipo CSV. Em seguida utilizou-se o DataFlow Task para selecionar/mapear a origem e destino. Uma dica para utilizar o Container Foreach, siga o passo a passo disponível em http://www.linhadecodigo.com.br/artigo/1761/foreach-loop-container-task.aspx.
A documentação da Microsoft também traz muitos detalhes sobre o componente, https://docs.microsoft.com/pt-br/sql/integration-services/control-flow/foreach-loop-container?view=sql-server-2017
3º. Passo – Após a carga realizada nas tabelas do SQL server e as devidas inserções, segue-e para a etapa do Analysis Services. Nesta etapa a principal tarefa é a criação de um cubo que será utilizado no Power BI. Foram definidas a Tabela Fato e as Dimensões, conforme imagem abaixo:
Também criou-se a tabela tempo e associou-se ao mês de referência na Fabela Tato.
Para a criação de um cubo simples no Analysis Services, siga o tutorial disponível em https://www.dirceuresende.com/blog/analysis-services-como-criar-seu-primeiro-cubo-multidimensional-no-modelo-estrela-star-schema/
4º Passo – Após o processamento e publicação do cubo no Analysis Service, o passo seguinte corresponde em acessar o projeto no Power BI e realizar o report. Segue uma breve análise do report gerado:
A despesa com pessoal, entre os meses de Janeiro a Outubro de 2018, de todos os municípios de MG foi de R$ 20.528.079.950,81 (mais de 20 bilhões). O mês com maior despesa foi Julho, representando mais de 2,05 bilhões de Reais.
No mês de Julho o destaque foi para a despesa com pessoal de Cargo Efetivo (Vinculado ao INSS) com valor superior a 385 milhões de Reais, seguido da despesa com pessoal de Cargo Efetivo (vinculado ao RPPS) com mais de 369 milhões de Reais.
Temos na Figura abaixo a análise das TOP 10 categorias de despesas com pessoal mais onerosas de MG/2018. O destaque é para a categoria OUTROS com mais de 2 bilhões e Cargo Comissionado com mais de 1 bilhão. Os maiores gastos com despesa de pessoal veem de ´servidores públicos de Cargo Efetivo (Vinculado ao INSS ou RPPS), não desprezando outras categorias como Cargo Comissionado e Outros.
Entre os municípios de MG, Belo Horizonte representa 7,16% do total das despesas com pessoal. A separação dos municípios em clusters pode ser vista na Figura abaixo.
Foram gerados 4 clusters com base na despesa com pessoal do município. O primeiro cluster é o mais populoso, com 807 municípios, representa 51,48% do total das despesas, possui população de até 116.691 habitantes. Neste cluster destaca-se os municípios de Extrema (35.474 habitantes) com R$ 81.026.214,90 de despesa com pessoal, seguido de São Sebastião do Paraíso (70.450 habitantes) com despesa de R$ 80.693.393,32. Posteriormente, seguem João Monlevade, Lagoa Santa, Pirapora e Alfenas, todos com menos de 80 mil habitantes e com despesa acima de 76 milhões de reais.
Neste cluster temos exemplos positivos quanto ao equilíbrio população versus despesa com pessoal, um deles é o município de Ouro Preto, cuja despesa foi de R$ 12.322.246,65 com população de 73.994 habitantes.
O cluster 3 possui municípios com 51.281 habitantes (Itabirito – despesa de R$ 104.161.134,84) até 432.575 habitantes (Betim – R$ 168.935.224,43).
O destaque positivo é para o município de Montes Claros, com 404.804 habitantes teve uma despesa de R$ 62.658.660,24. Os municípios que mais gastaram são Uberaba (R$ 392.377.725 com 330.361 habitantes) e Governador Valadares (R$ 382.899.925,52 com 278.685 habitantes)
O cluster 2 possui apenas 3 municípios: Uberlândia, Contagem e Juiz de Fora. Dentre esses municípios, Juiz de Fora teve menor despesa com pessoal (R$ 688.580.931,61), já Uberlândia ultrapassou 800 milhões em despesa com pessoal.
Foi criado um Dashboard visando explorar as features população e mês de ocorrência das despesas com pessoal. O Filtro abaixo mostra a população de até 461.110 habitantes. O município de Congonhas chama atenção, com apenas 54.196 habitantes, possui R$ 169.120.651.64 de despesa com pessoal.
Conclusão: através da construção de um dashboard e de reports inteligentes é possível avaliar municípios que possuem altos gastos com despesa com pessoal e outros que conseguem economizar em relação ao seu tamanho populacional. Essa tarefa poderá ser automatizada, buscando dados do TCE, alimentando o ETL e gerando o Cubo. Desse modo, relatórios e dashboards estarão sempre atualizados.