Relacionamentos

O arquivo relationships.tmdl define os relacionamentos do modelo semântico. A estrutura geral conecta tabelas de fato a dimensões de fazenda, unidade, usuário, calendário, status, proposta, processamento e tile.

Diagrama ER Simplificado

Este diagrama apresenta uma leitura mais próxima de um DER: tabelas com campos-chave e cardinalidade entre fatos, dimensões e tabelas estatísticas. Ele não substitui o modelo completo do Power BI, mas facilita a leitura documental.

erDiagram
    d_users {
        int id PK
        string email
        string name
    }

    d_sub_users {
        int user_id FK
        int sub_user_id FK
    }

    d_units {
        int id PK
        int user_id FK
        string name
    }

    d_units_users {
        int unit_id FK
        int user_id FK
    }

    d_farms {
        int id PK
        string name
        int user_id FK
        int unit_id FK
    }

    d_Calendario {
        date Data PK
        int Ano
        int MesNumero
        string Mes
    }

    d_status_processing {
        int id PK
        string name
        string stage
    }

    d_status_proposals {
        int id PK
        string name
        string stage
    }

    d_processing {
        int id PK
        int farm_id FK
        int user_id FK
        int unit_id FK
        int status FK
    }

    d_proposals {
        int id PK
        int process_id FK
        int farm_id FK
        int user_id FK
        int unit_id FK
        int status FK
    }

    d_tiles {
        int id PK
        int farm_id FK
        int unit_id FK
        string name
    }

    f_processing {
        int id PK
        int farm_id FK
        int user_id FK
        int unit_id FK
        int status FK
        decimal area
        date created_at
    }

    f_proposals {
        int id PK
        int farm_id FK
        int unit_id FK
        int process_id FK
        int user_id FK
        int status FK
        decimal area
        date created_at
    }

    f_balance_records {
        int id PK
        int user_id FK
        int unit_id FK
        int farm_id FK
        int proposal_id FK
        int process_id FK
        string module
        date created_at
    }

    f_tiles {
        int id PK
        int farm_id FK
        int unit_id FK
        string name
    }

    f_statistics_tiles {
        int proposal_id FK
        int process_id FK
        int tile_id FK
        int farm_id FK
        int unit_id FK
        int user_id FK
        decimal lines_sum_length
        decimal gaps_sum_length
    }

    f_statistics_planting_gaps {
        int proposal_id FK
        int process_id FK
        int farm_id FK
        int unit_id FK
        int user_id FK
        decimal gap_area
    }

    f_statistics_spacing_quality {
        int proposal_id FK
        int process_id FK
        int farm_id FK
        int unit_id FK
        int user_id FK
        decimal spacing_index
    }

    f_statistics_weed_mappings {
        int proposal_id FK
        int process_id FK
        int farm_id FK
        int unit_id FK
        int user_id FK
        string stage
        string type
    }

    f_statistics_survival_initials {
        int proposal_id FK
        int processing_id FK
        int farm_id FK
        int unit_id FK
        int user_id FK
        decimal gap_area_ha
        int count_plants
    }

    f_statistics_forest_wasts {
        int proposal_id FK
        int process_id FK
        int farm_id FK
        int unit_id FK
        int user_id FK
        decimal medium_volume
    }

    d_users ||--o{ d_sub_users : "master"
    d_users ||--o{ d_units : "possui"
    d_users ||--o{ d_units_users : "acessa"
    d_units ||--o{ d_units_users : "permissão"
    d_units ||--o{ d_farms : "agrupa"
    d_farms ||--o{ d_tiles : "contem"

    d_farms ||--o{ f_processing : "farm_id"
    d_units ||--o{ f_processing : "unit_id"
    d_users ||--o{ f_processing : "user_id"
    d_status_processing ||--o{ f_processing : "status"
    d_Calendario ||--o{ f_processing : "created_at"

    d_farms ||--o{ f_proposals : "farm_id"
    d_units ||--o{ f_proposals : "unit_id"
    d_users ||--o{ f_proposals : "user_id"
    d_status_proposals ||--o{ f_proposals : "status"
    d_processing ||--o{ f_proposals : "process_id"
    d_Calendario ||--o{ f_proposals : "created_at"

    d_users ||--o{ f_balance_records : "user_id"
    d_units ||--o{ f_balance_records : "unit_id"
    d_farms ||--o{ f_balance_records : "farm_id"
    d_processing ||--o{ f_balance_records : "process_id"
    d_proposals ||--o{ f_balance_records : "proposal_id"

    d_farms ||--o{ f_tiles : "farm_id"
    d_units ||--o{ f_tiles : "unit_id"
    d_tiles ||--o{ f_tiles : "id"

    d_proposals ||--o{ f_statistics_tiles : "proposal_id"
    d_processing ||--o{ f_statistics_tiles : "process_id"
    d_tiles ||--o{ f_statistics_tiles : "tile_id"
    d_farms ||--o{ f_statistics_tiles : "farm_id"
    d_units ||--o{ f_statistics_tiles : "unit_id"
    d_users ||--o{ f_statistics_tiles : "user_id"

    d_proposals ||--o{ f_statistics_planting_gaps : "proposal_id"
    d_processing ||--o{ f_statistics_planting_gaps : "process_id"
    d_farms ||--o{ f_statistics_planting_gaps : "farm_id"
    d_units ||--o{ f_statistics_planting_gaps : "unit_id"
    d_users ||--o{ f_statistics_planting_gaps : "user_id"

    d_proposals ||--o{ f_statistics_spacing_quality : "proposal_id"
    d_processing ||--o{ f_statistics_spacing_quality : "process_id"
    d_farms ||--o{ f_statistics_spacing_quality : "farm_id"
    d_units ||--o{ f_statistics_spacing_quality : "unit_id"
    d_users ||--o{ f_statistics_spacing_quality : "user_id"

    d_proposals ||--o{ f_statistics_weed_mappings : "proposal_id"
    d_processing ||--o{ f_statistics_weed_mappings : "process_id"
    d_farms ||--o{ f_statistics_weed_mappings : "farm_id"
    d_units ||--o{ f_statistics_weed_mappings : "unit_id"
    d_users ||--o{ f_statistics_weed_mappings : "user_id"

    d_proposals ||--o{ f_statistics_survival_initials : "proposal_id"
    d_processing ||--o{ f_statistics_survival_initials : "processing_id"
    d_farms ||--o{ f_statistics_survival_initials : "farm_id"
    d_units ||--o{ f_statistics_survival_initials : "unit_id"
    d_users ||--o{ f_statistics_survival_initials : "user_id"

    d_proposals ||--o{ f_statistics_forest_wasts : "proposal_id"
    d_processing ||--o{ f_statistics_forest_wasts : "process_id"
    d_farms ||--o{ f_statistics_forest_wasts : "farm_id"
    d_units ||--o{ f_statistics_forest_wasts : "unit_id"
    d_users ||--o{ f_statistics_forest_wasts : "user_id"

Visão Geral dos Relacionamentos

O diagrama abaixo resume o desenho do modelo em alto nível. Para facilitar a leitura, os relacionamentos detalhados foram separados por domínio nas seções seguintes.

flowchart LR
    origem[(PostgreSQL<br/>db_bemagro)]

    subgraph fatos[Fatos operacionais]
        fproc[f_processing]
        fprop[f_proposals]
        fbal[f_balance_records]
        ftiles[f_tiles]
    end

    subgraph dimensoes[Dimensões compartilhadas]
        dcal[d_Calendario]
        dfarms[d_farms]
        dunits[d_units]
        dusers[d_users]
        dproc[d_processing]
        dprop[d_proposals]
        dtiles[d_tiles]
        dstproc[d_status_processing]
        dstprop[d_status_proposals]
    end

    subgraph estatisticas[Estatísticas dos módulos]
        metrics[Field Metrics<br/>Falhas e Paralelismo]
        monitoring[Field Monitoring<br/>Infestação]
        early[Early Control]
        harvesting[Harvesting]
    end

    origem --> fatos
    origem --> estatisticas

    fatos --> dfarms
    fatos --> dunits
    fatos --> dusers
    fatos --> dcal
    fproc --> dstproc
    fprop --> dstprop
    fprop --> dproc
    fprop --> dprop
    ftiles --> dtiles

    estatisticas --> dfarms
    estatisticas --> dunits
    estatisticas --> dusers
    estatisticas --> dcal
    estatisticas --> dproc
    estatisticas --> dprop
    metrics --> dtiles

Relacionamentos Operacionais

flowchart LR
    subgraph Fatos[Fatos operacionais]
        fproc[f_processing]
        fprop[f_proposals]
        fbal[f_balance_records]
        ftiles[f_tiles]
    end

    subgraph Dims[Dimensões principais]
        dcal[d_Calendario]
        dfarms[d_farms]
        dunits[d_units]
        dusers[d_users]
        dproc[d_processing]
        dprop[d_proposals]
        dtiles[d_tiles]
        dstproc[d_status_processing]
        dstprop[d_status_proposals]
    end

    fproc -->|farm_id| dfarms
    fproc -->|unit_id| dunits
    fproc -->|user_id| dusers
    fproc -->|status| dstproc
    fproc -->|created_at| dcal

    fprop -->|farm_id| dfarms
    fprop -->|unit_id| dunits
    fprop -->|user_id| dusers
    fprop -->|status| dstprop
    fprop -->|created_at| dcal
    fprop -->|process_id| dproc
    fprop -->|id| dprop

    fbal -->|user_id| dusers
    fbal -->|farm_id| dfarms
    fbal -->|unit_id| dunits
    fbal -->|process_id| dproc
    fbal -->|proposal_id| dprop
    fbal -->|created_at| dcal

    ftiles -->|farm_id| dfarms
    ftiles -->|unit_id| dunits
    ftiles -->|id| dtiles

Relacionamentos das Estatísticas de Campo

flowchart LR
    subgraph Stats[Estatísticas dos módulos]
        stiles[f_statistics_tiles]
        gaps[f_statistics_planting_gaps]
        spacing[f_statistics_spacing_quality]
        weeds[f_statistics_weed_mappings]
        survival[f_statistics_survival_initials]
        forest[f_statistics_forest_wasts]
    end

    subgraph Dims[Dimensões compartilhadas]
        dfarms[d_farms]
        dunits[d_units]
        dusers[d_users]
        dcal[d_Calendario]
        dproc[d_processing]
        dprop[d_proposals]
        dtiles[d_tiles]
        drtc[d_report_type_configuration]
    end

    stiles -->|farm_id| dfarms
    stiles -->|unit_id| dunits
    stiles -->|user_id| dusers
    stiles -->|process_id| dproc
    stiles -->|proposal_id| dprop
    stiles -->|tile_id| dtiles
    stiles -->|data_criacao_proposta| dcal

    gaps -->|farm_id| dfarms
    gaps -->|unit_id| dunits
    gaps -->|user_id| dusers
    gaps -->|process_id| dproc
    gaps -->|proposal_id| dprop
    gaps -->|data_criacao_proposta| dcal

    spacing -->|farm_id| dfarms
    spacing -->|unit_id| dunits
    spacing -->|user_id| dusers
    spacing -->|process_id| dproc
    spacing -->|proposal_id| dprop
    spacing -->|completion_date| dcal

    weeds -->|farm_id| dfarms
    weeds -->|unit_id| dunits
    weeds -->|user_id| dusers
    weeds -->|process_id| dproc
    weeds -->|proposal_id| dprop
    weeds -->|id_unico_stage_e_type| drtc
    weeds -->|data_criacao_proposta| dcal

    survival -->|farm_id| dfarms
    survival -->|unit_id| dunits
    survival -->|user_id| dusers
    survival -->|processing_id| dproc
    survival -->|proposal_id| dprop
    survival -->|created_at| dcal

    forest -->|farm_id| dfarms
    forest -->|unit_id| dunits
    forest -->|user_id| dusers
    forest -->|process_id| dproc
    forest -->|proposal_id| dprop
    forest -->|data_proposta| dcal

Relacionamentos Principais em Tabela

Fato	Dimensão	Chave
f_processing	d_farms	farm_id -> id
f_processing	d_units	unit_id -> id
f_processing	d_users	user_id -> id
f_processing	d_status_processing	status -> id
f_processing	d_Calendario	created_at -> Data
f_proposals	d_farms	farm_id -> id
f_proposals	d_units	unit_id -> id
f_proposals	d_users	user_id -> id
f_proposals	d_status_proposals	status -> id
f_proposals	d_Calendario	created_at -> Data
f_proposals	d_processing	process_id -> id
f_balance_records	d_users	user_id -> id
f_balance_records	d_farms	farm_id -> id
f_balance_records	d_units	unit_id -> id
f_balance_records	d_processing	process_id -> id
f_balance_records	d_proposals	proposal_id -> id
f_tiles	d_farms	farm_id -> id
f_tiles	d_units	unit_id -> id
f_tiles	d_tiles	id -> id

Estatísticas de Campo

Tabela	Relacionamentos recorrentes
f_statistics_tiles	d_farms, d_processing, d_proposals, d_tiles, d_units, d_users, d_Calendario
f_statistics_planting_gaps	d_farms, d_processing, d_proposals, d_units, d_users, d_Calendario
f_statistics_spacing_quality	d_farms, d_processing, d_proposals, d_units, d_users, d_Calendario
f_statistics_weed_mappings	d_farms, d_processing, d_proposals, d_units, d_users, d_report_type_configuration, d_Calendario
f_statistics_survival_initials	d_farms, d_processing, d_proposals, d_units, d_users, d_Calendario
f_statistics_forest_wasts	d_farms, d_processing, d_proposals, d_units, d_users, d_Calendario

Relacionamentos Inativos

Algumas relações com d_Calendario estão marcadas como inativas, incluindo campos como completion_date, updated_at, deleted_at e request_date. Essas relações existem para suporte a análises específicas e devem ser ativadas em medidas DAX quando necessário.

Relacionamentos Bidirecionais

O modelo possui relacionamentos bidirecionais em dimensões auxiliares, principalmente para suporte a idiomas e status:

Origem	Destino	Uso
d_status_proposals_idiom	d_status_proposals	Rótulos de status de proposta por idioma.
d_status_processing_idiom	d_status_processing	Rótulos de status de processamento por idioma.
d_report_type_configuration_idiom	d_report_type_configuration	Rótulos de tipo/stage por idioma.
d_Mes_Idiom	d_Mes	Rótulos de mês por idioma.

Observação

Para auditoria completa, o arquivo fonte relationships.tmdl deve ser mantido como referência técnica oficial do relacionamento físico do modelo.