Diretoria Executiva de Tecnologia da Informação e Comunicação

Plataforma de documentação operacional e gerencial da DETIC

Ferramentas do usuário

Ferramentas do site


start:projetos:projetos_dev:sedi_data_mart_emprego:documentacao

Documentação do Projeto

Histórico de Alterações
Data Versão Descrição Autor
04/09/2019 1.0 Criação da documentação modelo Rodrigo Arnaldo Kreuzberg
11/09/2019 1.1 Documentação da Dimensão CNAE HUDSON FERNANDO MENDES DE FRANÇA
11/09/2019 1.2 Documentação da Dimensão IBGE Subsetor HUDSON FERNANDO MENDES DE FRANÇA
13/09/2019 1.3 Documentação da Dimensão Empregado (Cargas dimensões) HUDSON FERNANDO MENDES DE FRANÇA
16/09/2019 1.4 Correção da Documentação da Dimensão Empregado - Correção visual da tabela Dimensão x Indicadores HUDSON FERNANDO MENDES DE FRANÇA
18/09/2019 1.5 Documentação da Dimensão Ocupação Rodrigo Arnaldo Kreuzberg
20/09/2019 1.6 Inclusão da periodicidade da fonte de dados Rodrigo Arnaldo Kreuzberg
23/09/2019 1.7 Documentação da Dimensão Tempo HUDSON FERNANDO MENDES DE FRANÇA
09/10/2019 1.8 Atualização das dimensões HUDSON FERNANDO MENDES DE FRANÇA
11/10/2019 1.9 Atualização das dimensões e inserção da fato emprego Rodrigo Arnaldo Kreuzberg
14/10/2019 1.10 Documentação da Tabela Fato, da Dimensão Tempo e Revisão das demais dimensões com inserção de novos prints das mesmas. Abdenildo Deividy Sobreira dos Santos
16/10/2019 1.11 Atualização da documentação, inclusão da topologia lógica de rede e inclusão do link dos Dashboards Abdenildo Deividy Sobreira dos Santos

Canvas

A ideia do BMC ou do Quadro de Modelos de Negócios é fazer o Business Plan, um Plano de Negócio macro, ou seja, planejar e visualizar as principais demandas de um negócio, para qualquer setor, de uma forma muito mais rápida e barata do que fazer um Business Plan no modo tradicional, como um primeiro rabisco.

A relização do canvas para o Data Mart de Emprego ocorreram em dois encontros nos dias 22, 29 e 30 de Julho de 2019 e com os seguintes envolvidos:

  • Thalles - SEDI
  • Ederson - SEDI
  • Heraclito Ferreira - SEDI
  • Lucas Lemos - SEDI
  • Maico Moreira - EpR
  • David - EpR

O produto dos encontros está diponível neste link

Matriz de Necessidades

Indicadores

Qual o total de admissões? Total de Admissões realizadas no Estado de Rondônia, campo UF valor [11], é considerada admissão todos os registros na base de dados do CAGED cujo campo Admitidos/Desligados contenha o valor [ 1 ]
Qual o total de desligamentos? Total de Desligamentos realizadas no Estado de Rondônia, campo UF valor [11], é considerada admissão todos os registros na base de dados do CAGED cujo campo Admitidos/Desligados contenha o valor [ 2 ]
Qual o saldo mensal? Resultado to Total de Admissões subtraindo-se o Total de Demissões, mês a mês

DIMENSÕES


Identificação e Validação das fontes


Modelagem

Matriz Dimensão x Indicador

Indicadores x Dimensões Tempo Empregado CNAE Regiões Geográficas Ocupação Total de Admissão
Total de Admissão X X X
Total de desligamento X X X
Saldo mensal X X X

Validação com o cliente

A plenária realizada no dia 12 de Agosto de 2019 aprovou a modelagem realizada pelo Time Baymax.

ETL - Extração, Transformação e Carregamento

Carregar a Stagin Area

Os arquivos utilizados para realização da Stagin Area estão disponíveis no Gitlab Detic.

Tratar os dados

ETL - DimensõesETL - Dimensão tempoCarga Fato

Carregar as dimensões

dim_cbo_2002_ocupacao
Classificação Dimensão (X) Fato ( )
Descrição Armazena os dados referente a Classificação Brasileira de Ocupações (CBO) publicada em 2002, apresentada na tabela de layout do CAGED
Nome Descrição Tipo de dado Tamanho Restrições de Domínio
codigo Código referente a Classificação Brasileira de Ocupações disponibilizada na tabela de Layout do CAGED. Apresentada na tabela de layout do CAGED. int - PK, Identity
descricao Descrição referente a Classificação Brasileira de Ocupação disponibilizada na tabela de Layout do CAGED. Apresentada na tabela de layout do CAGED. varchar 200 Not null


dim_classe_10
Classificação Dimensão ( X ) Fato ( )
Descrição Armazena os dados referente a Classificação Nacional de Atividades Econômicas (CNAE). Apresentada na tabela de layout do CAGED
Nome Descrição Tipo de dado Tamanho Restrições de Domínio
codigo Código referente a Classificação Nacional de Atividades Econômicas (CNAE). Apresentada na tabela de layout do CAGED varchar 15 PK
descricao Descrição referente a Classificação Nacional de Atividades Econômicas (CNAE). Apresentada na tabela de layout do CAGED varchar 200 Not null


dim_cnae_20_subclas
Classificação Dimensão ( X ) Fato ( )
Descrição Armazena os dados referente a Classificação Nacional de Atividades Econômicas (CNAE) versão 2.0, apresentada na tabela de layout do CAGED
Nome Descrição Tipo de dado Tamanho Restrições de Domínio
codigo Código referente a CNAE disponibilizada na tabela de Layout do CAGED. Apresentada na tabela de layout do CAGED varchar 15 PK
descricao Descrição referente a CNAE disponibilizada na tabela de Layout do CAGED. Apresentada na tabela de layout do CAGED varchar 200 Not null


dim_cnae_2_classe
Classificação Dimensão ( X ) Fato ( )
Descrição Armazena os dados referente a Classificação Nacional de Atividades Econômicas (CNAE) versão 2.0. Conforme apresentada na tabela de layout do CAGED
Nome Descrição Tipo de dado Tamanho Restrições de Domínio
codigo Código referente a CNAE disponibilizada na tabela de Layout do CAGED. Apresentada na tabela de layout do CAGED varchar 15 PK
descricao Descrição referente a CNAE disponibilizada na tabela de Layout do CAGED. Apresentada na tabela de layout do CAGED varchar 200 Not null


dim_ibge_subsetor
Classificação Dimensão ( X ) Fato ( )
Descrição Armazena os dados referente ao subsetor do Instituto Brasileiro de Geografia e Estatística (IBGE). Apresentado na tabela de layout do CAGED
Nome Descrição Tipo de dado Tamanho Restrições de Domínio
valor_na_fonte Código referente ao subsetor determinado pelo IBGE. Apresentado na tabela de layout do CAGED int - PK
categoria Descrição referente ao subsetor determinado pelo IBGE. Apresentando na tabela de layout do CAGED varchar 67 Not null


dim_mesorregiao
Classificação Dimensão ( X) Fato ( )
Descrição Armazena os dados referente a mesorregião divulgada pelo Instituto Brasileiro de Geografia e Estatística (IBGE). Apresentado na tabela de layout do CAGED
Nome Descrição Tipo de dado Tamanho Restrições de Domínio
codigo Código referente a mesorregião determinada pelo IBGE. Apresentado na tabela de layout do CAGED int - PK
descricao Descrição referente a mesorregião determinada pelo IBGE. Apresentando na tabela de layout do CAGED varchar 200 Not null


dim_microrregiao
Classificação Dimensão ( X ) Fato ( )
Descrição Armazena os dados referente a microrregião divulgada pelo Instituto Brasileiro de Geografia e Estatística (IBGE). Apresentado na tabela de layout do CAGED
Nome Descrição Tipo de dado Tamanho Restrições de Domínio
codigo Código referente a microrregião determinada pelo IBGE. Apresentado na tabela de layout do CAGED int - PK
descricao Descrição referente a microrregião determinada pelo IBGE. Apresentando na tabela de layout do CAGED varchar 200 Not null


dim_empregados
Classificação Dimensão ( X ) Fato ( )
Descrição Armazena os dados referente aos empregados. A base e limitada a 3600 empregados, e a unicidade é baseada na composição entre idade, sexo e escolaridade. Por motivo de privacidade o CAGED não divulga dados pessoais de empregados, por esse motivo os dados estão limitados.
Nome Descrição Tipo de dado Tamanho Restrições de Domínio
idade Valor referente a idade do empregado int - PK
cod_sexo Código do sexo apresentado na Tabela de Layout do CAGED int - Pk
cod_grau_instrucao Código da escolaridade apresentado na Tabela de Layout do CAGED int - Pk
sexo Descrição do sexo, conforme apresentado na Tabela de Layout do CAGED varchar 9 Not null
grau_instrucao Descrição do grau de instrução, conforme apresentado na tabela de Layout do CAGED varchar 23 Not null


dim_raca_cor
Classificação Dimensão ( X ) Fato ( )
Descrição Armazena os dados referente a raça / cor dos empregados. Conforme apresentado no CAGED layout
Nome Descrição Tipo de dado Tamanho Restrições de Domínio
cod_raca_cor Código da raça/cor conforme apresentado na tabela layout do CAGED. int - PK
raca_cor Descrição da raça/cor conforme apresentado na tabela layout do CAGED varchar 9 Not null


dim_municipios
Classificação Dimensão ( X ) Fato ( )
Descrição Armazena os dados referentes aos municípios. De acordo com apresentado na tabela de Layout do CAGED
Nome Descrição Tipo de dado Tamanho Restrições de Domínio
codigo Código referente ao município. Apresentado na tabela de layout do CAGED int - PK
estado Sigla do estado referente ao município. Apresentado na tabela de layout do CAGED varchar 2 Not null
cidade Nome do município Apresentado na tabela de layout do CAGED. varchar 200 Not null


dim_tipo_mov_desgregado
Classificação Dimensão ( X ) Fato ( )
Descrição Armazena os tipos de movimentação do desgregado. Apresentação na tabela de layout do CAGED
Nome Descrição Tipo de dado Tamanho Restrições de Domínio
cod_tipo_mov_desgregado Código do tipo de movimentação do desgregado. Apresentado na tabela de layout do CAGED int - PK
tipo_mov_desgregado Descrição do tipo de movimentação do desgregado. Apresentado na tabela de layout do CAGED varchar 45 Not null


dim_tipo_defic
Classificação Dimensão ( X ) Fato ( )
Descrição Armazena os tipos de deficiência considerados pelo CAGED. Apresentado na tabela de layout do CAGED
Nome Descrição Tipo de dado Tamanho Restrições de Domínio
cod_tipo_defic Código referente ao tipo de deficiência. Apresentado na tabela de layout do CAGED int - PK
tipo_defic Descrição do tipo de deficiência. Apresentado na tabela de layout do CAGED varchar 20 Not null


dim_tipo_estab
Classificação Dimensão ( X ) Fato ( )
Descrição Armazena os dados referente ao tipo de estabelecimento. Apresentado na tabela de layout do CAGED
Nome Descrição Tipo de dado Tamanho Restrições de Domínio
cod_tipo_estab Código do tipo de estabelecimento. Apresentando na tabela de Layout do CAGED int - PK
tipo_estab Descrição do tipo de estabelecimento. Apresentado na tabela de layout do CAGED varchar 11 Not null


dim_tempo
Classificação Dimensão (X)
Descrição Armazena os dados referente ao tempo. Nesta tabela está concentrada os dados para analise temporal, a menor granularidade é dia e a maior é ano.
Nome Descrição Tipo de dado Tamanho Restrições de Domínio (PK, FK, Not Null, Check, Default, Identity)
sk_tempo Surrogate da tabela dim_tempo; float8 - PK, Identity
cod_dia Chave primaria da tabela, formada a partir da concatenação do ano + mês + dia; int - default
data Armazena o dia. Representa a menor granularidade dos dados da tabela dim tempo. Formato ISO 8601; timestamp without time zone - default
cod_semana Código referente a quantidade de semanas compreendidas em um ano. Todo ano tem 53 semanas; bigin - default
nome_dia_semana Descrição em formato referente ao nome do dia da semana. Não é utilizado o sufixo “-feira” na descrição; varchar 7 default
cod_mes Código numeral referente a ordem em que os meses são dispostos ao longo do ano; bigint - default
nome_mes Descrição em formato referente ao nome do mês; varchar 9 default
cod_mes_ano Código referente ao mês e ano. Resultado da concatenação de mês + ano. Formato MM-AAAA; varchar 7 default
nome_mes_ano Descrição em formato de texto referente ao nome do mês e ano; varchar 14 default
cod_trimestre Código numeral referente a ordem em que os trimestres estão dispostos ao longo do ano; bigint - default
nome_trimestre Descrição em formato de texto referente ao nome do trimestre. Não possui números; varchar 18 default
cod_trimestre_ano Código referente ao trimestre do ano. Resultado da concatenação entre cod_trimestre e ano. Formato TT-AAAA; varchar 7 default
nome_trimestre_ano Descrição em formato de texto a representação do cod_trimestre_ano; varchar 23 default
cod_semestre Código numeral referente a ordem em que os semestres estão dispostos ao longo do ano; bigint - default
nome_semestre Descrição em formato de texto referente ao nome do semestres. não possui números; varchar 17 default
cod_semestre_ano Código referente ao semestre do ano.Resultado da concentração entre cod_semestre e ano. Formato SS-AAAA; varchar 7 default
ano Representa o ano em formato numeral; bigint - default
tipo_dia Representa em formato de texto se o dia é útil o fim de semana. varchar 13 default


ft_emprego
Classificação Dimensão ( ) Fato ( X )
Descrição Armazena os dados referentes a cada eventualidade emprego. De acordo com apresentado na tabela de Layout do CAGED
Nome Descrição Tipo de dado Tamanho Restrições de Domínio
admitidos_desligados codigo referente às admissões ou desligamentos. 1 para admissão e 2 para desligamento int - PK
competencia_declarada Código do ano concatenado ao mês do registrado. varchar 2 Not null
municipio Nome do município Apresentado na tabela de layout do CAGED. varchar 200 Not null
ano_declarado Ano registrado int - Not null
cbo_2002 Codigo referente à CBO de 2002 int - Not null
cnae_10_classe Código da classe do CNAE 1.0 int - Not null
cnae_20_classe Código da classe do CNAE 2.0 int - Not null
cnae_20_subclas Código da sub-classe do CNAE 2.0 int - Not null
faixa_empr_inicio_jan Tamanho do estabelecimento em janeiro do ano de referência int - Not null
grau_instrucao Grau de instrução ou escolaridade int - Not null
qtd_hora_contrat Quantidade de horas contratuais por semana int - Not null
ibge_subsetor Subsetor Econômico segundo IBGE int - Not null
idade Idade do trabalhador (quando acumulada representa a soma das idades) int - Not null
ind_aprendiz Indicador de movimentação referente a contrato de aprendizagem int - Not null
ind_portador_defic Indicador se o empregado/servidor de portador de deficiência habilitado ou beneficiário reabilitado int - Not null
raca_cor Raça e Cor do Trabalhador int - Not null
salario_mensal Salário mensal em moeda corrente numeric (10,2) Not null
saldo_mov Saldo de movimentação (1 para admissão e -1 para desligamento) int - Not null
sexo Sexo int - Not null
tempo_emprego Tempo de emprego do trabalhador (quando acumulada representa a soma dos meses) numeric (5,1) Not null
tipo_estab Tipo de estabelecimento int - Not null
tipo_defic Tipo de deficiência/Beneficiário habilitado int - Not null
tipo_mov_desgregado Tipo de movimento int - Not null
uf Município de localização do estabelecimento int - Not null
mesorregiao Messoregião int - Not null
microrregiao Microrregião int - Not null


Arquitetura atual do Projeto


Construção dos Dashboards

Dashboards publicados DataMartEmprego


Construir o metadado

Elaborar Dicionário de Dados

Script de criação das tabelas do Data Mart

script_dim_cbo_2002_ocupacao.sql
CREATE TABLE dm_emprego.dim_cbo_2002_ocupacao (
    codigo int8 NULL,
    descricao VARCHAR(200) NULL
);
script_dim_cbo_94_ocupacao.sql
CREATE TABLE dm_emprego.dim_cbo_94_ocupacao (
    codigo int8 NULL,
    descricao VARCHAR(200) NULL
);
script_dim_classe_10.sql
CREATE TABLE dm_emprego.dim_classe_10 (
    descricao VARCHAR(200) NULL,
    codigo VARCHAR(15) NULL
);
script_dim_cnae_20_subclas.sql
CREATE TABLE dm_emprego.dim_cnae_20_subclas (
    descricao VARCHAR(200) NULL,
    codigo VARCHAR(15) NULL
);
script_dim_cnae_2_classe.sql
CREATE TABLE dm_emprego.dim_cnae_2_classe (
    codigo VARCHAR(15) NULL,
    descricao VARCHAR(200) NULL
);
script_dim_empregados.sql
CREATE TABLE dm_emprego.dim_empregados (
    cod_sexo int8 NULL,
    cod_ano int8 NULL,
    sexo VARCHAR(9) NULL,
    cod_grau_instrucao int8 NULL,
    grau_instrucao VARCHAR(23) NULL
);
script_dim_ibge_subsetor.sql
CREATE TABLE dm_emprego.dim_ibge_subsetor (
    categorias VARCHAR(67) NULL,
    valor_na_fonte int4 NULL
);
script_dim_mesorregiao.sql
CREATE TABLE dm_emprego.dim_mesorregiao (
    codigo int8 NULL,
    descricao VARCHAR(200) NULL
);
script_dim_microrregiao.sql
CREATE TABLE dm_emprego.dim_microrregiao (
    codigo int8 NULL,
    descricao VARCHAR(200) NULL
);
script_dim_municipios.sql
CREATE TABLE dm_emprego.dim_municipios (
    codigo int8 NULL,
    estado VARCHAR(2) NULL,
    cidade VARCHAR(200) NULL
);
script_dim_raca_cor.sql
CREATE TABLE dm_emprego.dim_raca_cor (
    cod_raca_cor int8 NULL,
    raca_cor VARCHAR(9) NULL
);
script_dim_tipo_defic.sql
CREATE TABLE dm_emprego.dim_tipo_defic (
    cod_tipo_defic int8 NULL,
    tipo_defic VARCHAR(20) NULL
);
script_dim_tipo_estab.sql
CREATE TABLE dm_emprego.dim_tipo_estab (
    cod_tipo_estab int8 NULL,
    tipo_estab VARCHAR(11) NULL
);
script_dim_tipo_mov_desgregado.sql
CREATE TABLE dm_emprego.dim_tipo_mov_desgregado (
    cod_tipo_mov_desgregado int8 NULL,
    tipo_mov_desgregado VARCHAR(46) NULL
);
procedure.concat.sinonimos.sql
CREATE OR REPLACE FUNCTION f_concat_sinonimos() RETURNS setof cbo_ocupacao_concat AS $$
    DECLARE
        cont INT := 8388;--(select count(*) from st1.cbo_sinonimos);
        i INT := 1;
        sinonimos VARCHAR := (SELECT t.titulo_ocupacao FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = 1);
        cod_ocup INT := 0;
        cod_sino INT := 0;
 
    BEGIN
        while (i <=  cont) loop
            cod_ocup := (SELECT t.cod_ocupacao FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = i );
            cod_sino := (SELECT t.cod_sinonimo FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = i);
 
            IF (cod_ocup = cod_sino) THEN
 
                IF((SELECT t.cod_sinonimo FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = i-1) IS NULL )THEN
                    sinonimos := (SELECT t.titulo_ocupacao FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = i);
                END IF;
 
                IF ((SELECT t.cod_ocupacao FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = i )
                    <> (SELECT t.cod_sinonimo FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = i-1)) THEN
 
                    INSERT INTO st1.cbo_ocupacao_concat (cod_ocupacao, titulo_ocupacao)
                    VALUES ((SELECT t.cod_ocupacao FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = i-1 ), sinonimos);
                    sinonimos := (SELECT t.titulo_ocupacao FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = i);
 
                END IF;-- fim do 1° IF
                sinonimos := concat(sinonimos, ' | ' , (SELECT t.titulo_sinimo FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = i ));
 
            ELSE
               IF( ((SELECT t.cod_sinonimo FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = i-1)
               = (SELECT t.cod_sinonimo FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = i-1))
               AND (SELECT t.cod_sinonimo FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = i) IS NULL ) THEN
 
                       INSERT INTO st1.cbo_ocupacao_concat (cod_ocupacao, titulo_ocupacao)
                    VALUES ((SELECT t.cod_ocupacao FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = i-1 ), sinonimos);
 
               END IF;
 
                INSERT INTO st1.cbo_ocupacao_concat (cod_ocupacao, titulo_ocupacao)
                VALUES ((SELECT t.cod_ocupacao FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = i ),
                (SELECT t.titulo_ocupacao FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = i ));
            END IF;
 
            IF ((SELECT t.cod_ocupacao FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = 8388 ) = 992225) THEN
                INSERT INTO st1.cbo_ocupacao_concat (cod_ocupacao, titulo_ocupacao)
                VALUES ((SELECT t.cod_ocupacao FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = 8388 ),
                (SELECT t.titulo_ocupacao FROM st1.cbo_ocupac_c_sino t WHERE t.sk_ocup_sinon = 8388 ));
            END IF;
            i := i + 1;
 
        END loop;
    END;
    $$ LANGUAGE plpgsql;
 
DELETE FROM st1.cbo_ocupacao_concat;
 
SELECT f_concat_sinonimos();
dim_tempo.sql
 

Integrantes do projeto

start/projetos/projetos_dev/sedi_data_mart_emprego/documentacao.txt · Última modificação: 2019/11/18 14:07 por Abdenildo Deividy Sobreira dos Santos