Processo Seletivo · Prefeitura do Recife
| # | Desafio | Status | Onde encontrar |
|---|---|---|---|
| 1 | ETL em Python – leitura, limpeza e exportação das tabelas | Concluído | 01_etl/etl_medicamentos.py |
| 2 | Modelo relacional no Power BI (star schema) | Concluído | 02_03_powerbi/NIDI.pbix |
| 2.1 | DDL + DML PostgreSQL | Concluído | 02_1_banco_de_dados/schema_medicamentos.sql |
| 3 | Dashboard analítico no Power BI | Concluído | 02_03_powerbi/NIDI.pbix |
| 4 | Relatório de análise (2 páginas) | Concluído | 04_relatorio/relatorio_analise.docx |
| 5 | Proposta de solução com IA para BI (2 páginas) | Concluído | 05_proposta_ia/proposta_ia.docx |
DINÂMICA - SELEÇÃO ANALISTA BI-IA/
│
├── DESAFIO PRÁTICO.docx ← Enunciado original
│
├── 00_dados_brutos/ ← CSVs originais fornecidos (8 distritos)
│ ├── medicamentos_distrito01.csv
│ ├── medicamentos_distrito02.csv
│ ├── ...
│ └── medicamentos_distrito08.csv
│
├── 01_etl/ ← DESAFIO 1 – ETL Python
│ └── etl_medicamentos.py
│
├── 02_dados_processados/ ← Saída do ETL · entrada do Power BI e PostgreSQL
│ ├── medicamentos_consolidado.csv ← base completa (41.481 registros)
│ ├── dim_distrito.csv ← 8 distritos sanitários
│ ├── dim_unidade.csv ← 184 unidades de saúde
│ ├── dim_classe.csv ← 123 classes terapêuticas
│ ├── dim_produto.csv ← 1.035 medicamentos
│ └── fato_medicamentos.csv ← tabela fato com chaves surrogate
│
├── 02_03_powerbi/ ← DESAFIOS 2 e 3 – Modelo + Dashboard
│ └── NIDI.pbix ← star schema + 2 páginas de dashboard
│
├── 02_1_banco_de_dados/ ← DESAFIO 2.1 – PostgreSQL
│ └── schema_medicamentos.sql ← DDL + DML (COPY) + instruções de conexão
│
├── 04_relatorio/ ← DESAFIO 4 – Relatório de análise
│ └── relatorio_analise.docx
│
└── 05_proposta_ia/ ← DESAFIO 5 – Proposta de IA
└── proposta_ia.docx
Arquivo: 01_etl/etl_medicamentos.py
O script lê os 8 arquivos CSV (um por distrito sanitário), consolida e exporta as tabelas prontas para o modelo relacional.
Problema identificado nos dados: os CSVs possuem 10 colunas de dados, mas apenas 9 nomes no cabeçalho — a coluna num_distrito não tem nome e era absorvida como índice pelo pandas, deslocando semanticamente todas as demais colunas. A solução foi fornecer os 10 nomes corretos explicitamente (names=COLUNAS_CORRETAS + skiprows=1).
Transformações aplicadas:
- Remoção de espaços excedentes e padronização em maiúsculas (
.strip(),.upper()) - Conversão de colunas numéricas com tratamento de erros (
errors="coerce") - Classes terapêuticas com string vazia →
"SEM CLASSIFICAÇÃO" - 1.815 registros sem código CADUM mantidos (usa
codigo_produtocomo FK alternativa) - Concatenação dos 8 DataFrames em 41.481 linhas
Saída gerada em 02_dados_processados/:
| Arquivo | Conteúdo |
|---|---|
medicamentos_consolidado.csv |
Base completa — 41.481 registros |
dim_distrito.csv |
8 distritos sanitários |
dim_unidade.csv |
184 unidades de saúde |
dim_classe.csv |
123 classes terapêuticas |
dim_produto.csv |
1.035 medicamentos |
fato_medicamentos.csv |
Tabela fato com chaves surrogate |
Como executar:
pip install pandas
python 01_etl/etl_medicamentos.pyArquivo: 02_03_powerbi/NIDI.pbix
Os 6 CSVs de 02_dados_processados/ foram importados no Power BI Desktop. O modelo estrela (star schema) foi construído manualmente na visão de modelo com 5 relacionamentos M:1:
fato_medicamentos
├── id_distrito → dim_distrito (M:1)
├── id_unidade → dim_unidade (M:1)
├── id_classe → dim_classe (M:1)
└── id_produto → dim_produto (M:1)
Medidas DAX criadas: Total Dispensado, Qtd Unidades, Qtd Produtos, Ranking Unidade.
Arquivo: 02_1_banco_de_dados/schema_medicamentos.sql
DDL completo com:
- 5 tabelas (
dim_distrito,dim_unidade,dim_classe,dim_produto,fato_medicamentos) - Chaves primárias, chaves estrangeiras e restrições CHECK
- Índices nas colunas de junção da tabela fato
- Carga via comando
COPYapontando para os CSVs de02_dados_processados/ - Comentários com instruções de conexão ao Power BI via driver ODBC
Arquivo: 02_03_powerbi/NIDI.pbix (mesma pasta do modelo, página 2 e 3)
O dashboard tem duas páginas:
Página 1 – Visão Geral
- 4 cartões KPI: Total Dispensado, Qtd Unidades, Qtd Produtos, Qtd Distritos
- Gráfico de barras por distrito sanitário
- Gráfico de rosca por classe terapêutica
- Tabela de produtos com quantidade
Página 2 – Detalhe por Unidade
- Gráfico de barras horizontais — ranking de unidades de saúde
- 3 segmentadores independentes: Distrito, Classe Terapêutica, Tipo Produto
Arquivo: 04_relatorio/relatorio_analise.docx
Relatório de 2 páginas com os principais achados da análise:
- Distrito IV lidera com 5.266.775 unidades dispensadas (23,0% do total)
- US 128 – Policlínica Lessa de Andrade é a unidade de maior consumo (856.974 unidades)
- Anti-hipertensivos e antidiabéticos dominam as classes terapêuticas — perfil epidemiológico predominantemente cardiovascular e metabólico
- Dificuldades encontradas: desalinhamento de cabeçalhos, nulos em CADUM, strings vazias em classe terapêutica
Arquivo: 05_proposta_ia/proposta_ia.docx
Proposta de 2 páginas descrevendo um Assistente BI Conversacional baseado em LLM para democratizar o acesso às análises de dispensação de medicamentos sem exigir conhecimento técnico dos usuários.
Arquitetura proposta em 5 camadas: Interface → LLM → Orquestrador → Execução (Python + Power BI API) → Resposta. Stack sugerida: Claude (Anthropic), LangChain, PostgreSQL, FastAPI. Cronograma de implementação: 12 semanas em 3 fases.
| Métrica | Valor |
|---|---|
| Total de registros | 41.481 |
| Distritos sanitários | 8 |
| Unidades de saúde | 184 |
| Classes terapêuticas | 123 |
| Medicamentos distintos | 1.035 |
| Registros sem código CADUM | 1.815 |
Distribuição por Distrito:
| Distrito | Unidades Dispensadas | Participação |
|---|---|---|
| Distrito IV | 5.266.775 | 23,0% |
| Distrito V | 3.650.811 | 15,9% |
| Distrito VI | 3.093.884 | 13,5% |
| Distrito VIII | 3.047.170 | 13,3% |
| Distrito II | 2.635.885 | 11,5% |
| Distrito VII | 2.601.321 | 11,4% |
| Distrito I | 1.672.860 | 7,3% |
| Distrito III | 1.049.050 | 4,6% |