Análise de Pareto nas compras governamentais usando Qlik Sense no GovData

O objetivo deste tutorial é mostrar como se constrói um gráfico de Pareto no ambiente do GovData usando as ferramentas CK (para o metadados), HUE (para a preparação dos dados) e Qlik Sense (para a construção do gráfico).

Para tanto, selecionamos o assunto “SIASG – Compras do Governo Federal” a fim de identificar onde se concentra a maioria dos gastos do Governo Federal no que se refere a compra de materiais.

A) Catálogo de Dados

O Catálogo de Dados é uma solução que faz parte do GovData criada com a finalidade mostrar ao cliente um catálogo explicando os dados disponibilizados neste ambiente.

Caso você não tenha domínio do assunto no qual pretende construir seu gráfico sugere-se começar por esta solução.

Para acessar o Catálogo de Dados do GovData existem duas formas:

Existem 3 Conjuntos de Dados sobre o assunto Compras no GovData:

1- ComprasNet:
O Portal de Compras do Governo Federal (Comprasnet) , é um site WEB, instituído pelo Ministério do Planejamento, Orçamento e Gestão - MP, para disponibilizar, à sociedade, informações referentes às licitações e contratações promovidas pelo Governo Federal, bem como permitir a realização de processos eletrônicos de aquisição.

2 - SIASG:
O Sistema Integrado de Administração de Serviços Gerais – SIASG, instituído pelo art. 7º do Decreto nº1.094, de 23 de março de 1994, é o sistema informatizado de apoio às atividades operacionais do Sistema de Serviços Gerais – SISG. Sua finalidade é integrar os órgãos da Administração Pública Federal direta, autárquica e fundacional.

O Siasg é o sistema onde são realizadas as operações das compras governamentais dos órgãos integrantes do Sisg. O Sistema inclui:

  • Divulgação e a realização das licitações;
  • Emissão de notas de empenho;
  • Registro dos contratos administrativos;
  • Catalogação de materiais e serviços;
  • Cadastro de fornecedores.

Os órgãos que não integram o SISG podem utilizar o SIASG, integralmente ou em módulos específicos, por meio de adesão formal para uso do sistema, mediante assinatura de termo de adesão.


3 - SIASGNet:
O Sistema Integrado de Administração de Serviços Gerais – SIASG, instituído pelo art. 7º do Decreto nº1.094, de 23 de março de 1994, é o sistema informatizado de apoio às atividades operacionais do Sistema de Serviços Gerais – SISG. Sua finalidade é integrar os órgãos da Administração Pública Federal direta, autárquica e fundacional.
O Siasg é o sistema onde são realizadas as operações das compras governamentais dos órgãos integrantes do Sisg. O Sistema inclui:
• Divulgação e a realização das licitações;
• Emissão de notas de empenho;
• Registro dos contratos administrativos;
• Catalogação de materiais e serviços;
• Cadastro de fornecedores.
Os órgãos que não integram o SISG podem utilizar o SIASG, integralmente ou em módulos específicos, por meio de adesão formal para uso do sistema, mediante assinatura de termo de adesão.

B) Preparando os dados no HUE

É através da ferramenta Hue que se tem acesso às bases que estão disponíveis no GovData

Sugere-se preparar o máximo possível os dados no HUE a fim de que o painel tenha melhor desempenho. Se for preciso, crie uma tabela intermediária a ser utilizada pelo Qlik Sense.

Abaixo algumas situações, do que se chama “preparar dados no HUE”:

  • Para construir seu Painel você precisará acessar várias tabelas. Sugere-se escrever um SQL que junte estas Tabelas criando uma tabela resultante do mesmo.

  • Para construir seu Painel só preciso os dados a partir de determinado ano. Neste caso escreva o SQL com filtro para capturar apenas dados a partir daquele ano e crie uma tabela com este resultado deste SQL.

  • Na Base não existe um campo “Grupo de Material” mas você sabe que a “Classe de Material” é composta de 4 posições, onde as duas primeiras são o Grupo; porém, este campo (Classe) é numérico; escreva um SQL transformando este campo num campo texto e retirando do mesmo as duas primeiras posições salvando num novo campo chamado “Grupo de Material”

  • Na Base tenho a data de nascimento do servidor; porém será preciso ter a Idade do mesmo. Escreve um SQL calculando a idade a partir de uma função que pega a Data Corrente e “subtrai” da Data de Nascimento calculando, assim, a idade.

Formas de acessar o HUE:

Entre com seu CPF e senha

Para construir o gráfico deste tutorial, foi acessada a Pasta SIASG e dentro dela os arquivos:

- sidec_resultado_compra onde estão as compras de material

- siasg_identificacao_basica onde estão as informações dos Materiais

- siasg_classe onde estão as informações agrupadas em Classes de Materiais

- siasg_grupo onde estão as informações agrupadas em Grupos de Materiais

Abaixo os SQL´s escritos para criar uma tabela intermediária a ser usada na construção do Painel:

1 - SQL para criar a Tabela de apoio:

Create table treinamento.compras_RM as

SELECT a.num_item_licitacao, b.cod_siasg_identificacao_basica, b.cod_identificacao_basica, b.des_padrao_descritivo_material, b.cod_classe, c.nom_classe, cast(substr(cast(b.cod_classe as string),1,2) as int) as grupo, d.cod_grupo_material, d.des_grupo_material, sum(a.vlr_total_comra) as Valor_Compra

FROM siasg.sidec_resultado_compra a

Left JOIN siasg.siasg_identificacao_basica b

ON (a.num_item_licitacao = b.cod_siasg_identificacao_basica)

left JOIN siasg.siasg_classe c

ON (b.cod_classe = c.cod_classe)

left JOIN siasg.siasg_grupo d

ON (cast(substr(cast(b.cod_classe as string),1,2)as int) = d.cod_grupo_material)

where

*b.cod_siasg_identificacao_basica IS NOT NULL and

c.nom_classe IS NOT NULL and

b.des_padrao_descritivo_material IS NOT NULL and

c.nom_classe IS NOT NULL and

d.des_grupo_material IS NOT NULL and

a.vlr_total_comra > 0

Group by

a.num_item_licitacao,

b.cod_siasg_identificacao_basica,

b.cod_identificacao_basica,

b.des_padrao_descritivo_material,

b.cod_classe,

c.nom_classe,

cast(substr(cast(b.cod_classe as string),1,2) as int),

d.cod_grupo_material,

d.des_grupo_material;

B - SQL para ler os dados da Tabela criada:

SELECT nom_classe num_item_licitacao, des_item_licitacao, sum(vlr_unitario_item * qtd_item) as Valor_Mat

FROM treinamento.compras_rm

group by nom_classe, num_item_licitacao, des_item_licitacao;

C - Acesssando o Qlik Sense no GovData

É no Qlik Sense que se constrói os Painéis

Existem duas formas de acessa-lo:

Em seguida, digite o seu CPF e a senha para fazer login na ferramenta. Aparecerá a tela abaixo:

1 - Carga dos Dados

A primeira coisa a ser feita é a carga dos dados para a memória do Qqlik Sense.

Para tanto, siga os seguintes passos:

  • Se ainda não tiver um Aplicativo, crie um

  • Abra o Aplicativo criado ou entre no que já existe

  • Selecione o Editor de Script

2 - Criando um Gráfico de Pareto

Selecione a opção “Análise”

Selecione uma Dimensão e uma Medida

Coloque um limite para o número de barras

Insira mais uma Medida com esta expressão:

RangeSum(Above(Sum(Valor_compra), 0, RowNo())) / Sum(total Valor_compra)

Feita em cima mesma Medida que usou antes (Valor_compra, neste caso)

Use a expressão da primeira Medida para classificar a Dimensão

Crie três variáveis de cor:

Crie esta expressão para cor usando como condição a segunda Medida:
If((RangeSum(Above(Sum(Valor_compra), 1, RowNo())) / Sum(total Valor_compra)) > 0.9, vCorVermelha08, **
** If((RangeSum(Above(Sum(Valor_compra), 1, RowNo())) / Sum(total Valor_compra)) > 0.8, vCorAmarela14, vCorAzul10))

Formate esta métrica criada a fim de que ela seja exibida “em percentual”

Faça mostrar os Pontos e Rótulos de Valor da Linha

Escolha um título para seu gráfico

Neste caso usado aqui no tutorial o gráfico está mostrando os Materiais comprados pelo Governo agrupados em Classe. Foi colocado um “Painel de Filtro” para que seja selecionado um ou vários Grupos de Materiais

Este gráfico, com determinados Grupos de Materiais selecionados mostra que mais de 80% das compras se concentra nas quatro primeiras classes de Materiais que aparecem no mesmo.

Como podem ver por este tutorial é fácil construir um gráfico no Ambiente do GovData.

No caso acima mostrado é possível, a partir do gráfico construído, fazer análises que possam vir a através da análise fazer grandes descobertas bem como gerenciar os gastos do Governo Federal nas compras de determinados materiais.

1 Curtida