PT EN
Back to site

DATTA BI — Models and visual relationships

Crossing data from different sources used to require SQL, exports and a good deal of patience. With DATTA BI Models, you drag datasets as boxes onto a canvas and draw the relationships between them — the equivalent of the Power BI Desktop "Model view". A relationship propagates the filter: a filter on a column the visual does not have reaches it through the relationship keys (see below). It does not join columns — the visual still shows only its own dataset's columns. The relationships exist only in DATTA BI: they never change the original source.

How to access

Menu VisualizarModelos.

What you see on the canvas

  • Draggable dataset boxes showing the dataset and its columns.
  • Relationship lines with a cardinality indicator (1:1, 1:N, N:1, N:N) and the recorded direction (unidirectional or bidirectional).
  • Preview: double-clicking a box opens a table with the dataset's first 100 rows, already with per-column DATTAX editing.

Creating a relationship by dragging

  1. Hover over the right edge of a dataset box — a blue connection anchor appears.
  2. Drag that anchor onto the other box.
  3. In the dialog that opens, fill in:
    • From column — the source column (the foreign key).
    • To column — the target column (the primary key).
    • Cardinality — 1:1, 1:N, N:1 or N:N.
    • Direction — BOTH (bidirectional) or SINGLE (unidirectional): the way the filter crosses (see below).
  4. Confirm: the line is drawn on the canvas and the relationship is saved in the model.

Each relationship is stored with the model it belongs to, the source and target datasets, the linked columns, the cardinality, the direction and the active flag — all in the platform's BI database.

Relationships and cross-filter

Cross-filtering, the Filtros pane filters and the top-bar filters apply directly to each visual whose base (the visual's dataset) has the filtered column. When the base does not have it, the platform looks for an active relationship between the visual's dataset and a dataset that does:

  1. It runs the related dataset with the filter — and with the row-level security that applies to you on it.
  2. It collects the distinct keys of the relationship column.
  3. It filters the visual by those keys: |> FILTER cliente_id IN (...).

Example: Vendas.cliente_id → Clientes.id (N:1). Clicking uf = SP in a Clientes visual filters the Vendas visual to the sales of SP customers.

Direction. BOTH propagates both ways. SINGLE propagates only from the "1" side to the "many" side: in N:1 the To dataset filters the From; in 1:N, the opposite; in 1:1, both sides filter; in N:N, To filters From.

When the filter is not applied — the visual shows its data without it and a warning saying why, never a silently partial result:

  • the filter selects more than 1,000 keys (configurable ceiling, datta.dattabi.relacionamento.teto-de-chaves);
  • there is more than one active relationship the filter could travel — keep only one active;
  • the related dataset's row-level security could not be resolved;
  • the key query failed.

Keys are cached for as long as visual renders; the cache keeps each user's row-level security slice apart.

Calculated columns with DATTAX

In the dataset preview, click a column name: an editor opens where you write a custom DATTAX expression, for example:

UPPER(nome)
coalesce(municipio, "—")

The expression becomes a calculated column and is saved with the dataset, available in any chart.

Hands-on example — social assistance model

The demo seed (seed-demo-data.sh) creates four relationships in the dm-mds-assistencia model:

  1. Pessoa.familiaId → GrupoFamiliar.id (N:1, BOTH)
  2. BeneficioSocial.pessoaId → Pessoa.id (N:1, BOTH)
  3. Transacao.beneficioId → BeneficioSocial.id (N:1, BOTH)
  4. ServicoPublico.uf → Pessoa.uf (N:N via uf, SINGLE)

With them, clicking a family filters the Pessoa visuals (and vice versa, since they are BOTH); a filter on Pessoa filters ServicoPublico by uf.

Known limitations

  • No automatic key detection: you point out the source and target columns manually.
  • N:N does not create an automatic intermediate mapping table — when you need one, build an additional dataset.
  • One hop only: the filter crosses one relationship; the dataset on the other side must have the filtered column (no A → B → C chain).
  • Only field-built visuals (dataset + channels) receive the filter through a relationship; a visual with a hand-written DATTA Query does not.
  • Key columns must hold the same value on both sides.
  • The editor does not flag cycles; the case that matters — two active paths for the same filter — shows up as a warning on the visual.

Date table

In a Model dataset's "..." menu, Marcar como tabela de datas picks the date column. From then on, a bar, line or area visual whose axis is that column opens by year and drills down, by double click, to quarter, month and day — without declaring a hierarchy. The levels are computed in the query by the DATTAX date functions (YEAR, QUARTER, MONTH, DAY); a value that is not a date becomes empty. Clicking a year filters the other visuals by that year — including those of another dataset related to the date table. A declared hierarchy that already uses the date column takes precedence. The same dialog unmarks the table.