Datasets e SQL no BI Constructor do Bitrix24: Guia Técnico

Três Tipos de Dataset: System, Custom e Virtual SQL

O BI Constructor do Bitrix24 (Alaio) oferece três tipos de dataset - system (tabelas físicas), custom (tabelas físicas unidas ou estendidas) e virtual (consultas SQL salvas) - e escolher o tipo certo para cada necessidade de relatório determina o nível de flexibilidade, desempenho e facilidade de manutenção do seu dashboard.

A tabela abaixo resume quando utilizar cada tipo:

Datasets system não podem ser editados - é possível apenas lê-los, sem modificar sua estrutura. Datasets custom e virtual são totalmente editáveis: você pode renomear colunas, definir formatos de exibição, configurar métricas e adicionar colunas calculadas diretamente no editor de datasets.

Para uma introdução prática ao fluxo de criação de relatórios, consulte How to Create a BI Report in Bitrix24 with BI Constructor.

Como o Bitrix24 Expõe Seus Dados: Tabelas, Schema e SQL Trino

Todos os dados do Bitrix24 acessíveis ao BI Constructor estão no schema bitrix24, sobre o engine de banco de dados trino - anteriormente conhecido como PrestoSQL - portanto a sintaxe SQL segue as convenções do Trino, e não as do MySQL ou PostgreSQL.

Ao criar um dataset pela interface, você preenche três campos: Database → trino, Schema → bitrix24, Table → a tabela da entidade desejada. No SQL Lab, basta selecionar o schema bitrix24 e escrever sua consulta.

As principais tabelas de entidades são:

• crm_deal - negócios (campos principais, valores, estágio, responsável)
• crm_deal_uf - campos personalizados de negócios
• crm_deal_product_row - itens de linha / produtos dentro dos negócios
• crm_lead - leads
• crm_lead_uf - campos personalizados de leads
• crm_lead_status_history - histórico de estágios dos leads
• crm_lead_product_row - produtos em leads
• crm_company - registros de empresas
• crm_company_uf - campos personalizados de empresas

Como o engine SQL é o Trino, a conversão de tipos utiliza CAST(... AS TIMESTAMP) e a aritmética de datas usa funções como date_add('day', -30, current_date). O SQL ANSI padrão para SELECT, JOIN, WHERE, GROUP BY e funções de janela funciona normalmente - basta estar atento à sintaxe específica do Trino em casos mais particulares.

Dica sobre nomenclatura de colunas: Ao usar campos calculados ou métricas no editor de datasets, utilize caracteres latinos sem espaços para os aliases de colunas. Nomes de colunas com espaços ou caracteres não latinos podem quebrar o reconhecimento de fórmulas no construtor de gráficos. Em SQL, prefira SELECT field AS deal_name em vez de SELECT field AS "Deal Name" para campos que serão referenciados em fórmulas.

SQL Lab: O Ambiente de Testes do Analista para Criar Datasets Virtuais

O SQL Lab (acessado em BI Constructor → SQL → SQL Lab) é um editor de consultas interativo onde você compõe, testa e salva datasets virtuais - funciona como um rascunho que não afeta a produção até que você clique explicitamente em "Save Dataset".

O fluxo de trabalho no SQL Lab segue um padrão iterativo deliberado:

1. Selecione o schema bitrix24.
2. Escreva um SELECT ... FROM básico para confirmar que a tabela está acessível.
3. Adicione um JOIN por vez, clicando em Run após cada adição para inspecionar a tabela de pré-visualização.
4. Corrija erros de ambiguidade de colunas (o problema mais comum para iniciantes - prefixe toda referência de coluna com o alias da tabela).
5. Adicione filtros WHERE, agregações ou funções de janela.
6. Confirme se a contagem de linhas e os valores das colunas estão de acordo com as expectativas do negócio.
7. Clique em Save Dataset e atribua um nome descritivo.

Uma prática fundamental: não edite um dataset de produção salvo diretamente. Em vez disso, copie o SQL dele para o SQL Lab, itere livremente por lá e depois cole a consulta validada de volta. Isso preserva o dataset em funcionamento enquanto você experimenta.

Técnicas úteis de depuração no SQL Lab:

• Comente seções com -- ou /* ... */ para isolar problemas em etapas intermediárias.
• Leia as mensagens de erro do Trino com atenção - "ambiguous column name" quase sempre significa que duas tabelas unidas compartilham um nome de campo e você precisa usar aliases prefixados com o nome da tabela.
• Se o editor destacar um erro de sintaxe falso, simplesmente execute a consulta novamente; trata-se de uma inconsistência ocasional conhecida da interface.
• Para problemas de desempenho em datasets grandes, reduza as colunas selecionadas - evite SELECT * em datasets finalizados.

Checklist Passo a Passo: Construindo um Dataset Virtual

Um dataset virtual bem projetado é construído de forma iterativa no SQL Lab, salvo com um nome claro e configurado com rótulos de colunas legíveis por humanos antes de qualquer gráfico ser associado a ele.

Use este checklist para cada dataset virtual que você criar:

• Defina a pergunta de negócio - escreva-a em linguagem simples antes de tocar no SQL ("Quais combinações de produtos aparecem com mais frequência no mesmo negócio?")
• Identifique as tabelas de origem - liste todas as tabelas físicas necessárias e as chaves de join entre elas
• Abra o SQL Lab - selecione o banco trino, schema bitrix24
• Construa a consulta de forma incremental - comece com uma tabela, adicione JOINs um por um, execute após cada etapa
• Inclua todas as dimensões de filtro - qualquer campo que o usuário possa querer filtrar ou agrupar deve ser uma coluna no dataset
• Inclua um campo de data no nível da linha - sem uma data por linha, gráficos de série temporal são impossíveis
• Adicione campos legíveis - IDs são necessários para a lógica de join, mas exponha campos com nomes descritivos (ex.: nome do produto, nome do gerente) para a camada de gráficos
• Crie uma chave de combinação única se for necessário filtro cruzado - concatene IDs de entidades em um único campo combo_key para que múltiplos gráficos compartilhem uma dimensão de filtro comum
• Evite dupla agregação de resultados de funções de janela - se uma coluna já contém um total calculado via função de janela, use MAX() no gráfico, não SUM()
• Salve o dataset com um nome claro e descritivo (ex.: deal_product_combinations)
• Renomeie as colunas para facilitar a leitura - acesse Datasets → Edit → aba Columns; defina nomes de exibição, formatos e descrições
• Defina Métricas e Colunas Calculadas no editor de datasets, se necessário
• Sincronize as colunas com a origem após modificar o SQL - clique em "Synchronise columns from source" na aba Columns
• Verifique no Bitrix24 - feche e reabra o relatório dentro do Bitrix24 para confirmar que as alterações são renderizadas corretamente

Os dados fluem das tabelas brutas do Bitrix24, passam pela camada de dataset, chegam aos gráficos e finalmente ao relatório publicado.

flowchart TD
    T1[crm_deal] --> SL[SQL Lab / Trino]
    T2[crm_deal_product_row] --> SL
    T3[crm_deal_uf\nCustom Fields] --> SL
    T4[crm_lead\ncrm_company] --> SL
    SL -->|Save Dataset| VD[Virtual Dataset]
    VD --> C1[Bar / Line Chart]
    VD --> C2[Summary Table]
    VD --> C3[KPI Metric Tile]
    C1 --> DB[Dashboard]
    C2 --> DB
    C3 --> DB
    DB -->|Publish| B24[Bitrix24 Report]

Parâmetros de Relatório e Templates Jinja: Filtragem Dinâmica por SQL

Os parâmetros de relatório no BI Constructor são variáveis dinâmicas injetadas na consulta SQL de um dataset virtual via templates Jinja - eles permitem que os usuários finais filtrem um relatório por intervalo de datas ou outros critérios sem que o desenvolvedor precise reescrever a consulta.

O caso de uso padrão é a filtragem por data. Um WHERE date_create >= DATE '2023-01-01' estático fixa um intervalo que nunca muda. Ao substituí-lo por blocos condicionais Jinja, o dataset passa a responder aos controles de seleção de data visíveis para o usuário do relatório.

O princípio funciona da seguinte forma:

1. A consulta SQL do dataset contém blocos condicionais Jinja que verificam se uma variável de parâmetro (ex.: data de início ou data de fim) foi fornecida pelo usuário.
2. Se a variável estiver presente, o fragmento correspondente da cláusula WHERE é incluído; caso contrário, é ignorado.
3. Uma instrução true ao final garante que a consulta permaneça sintaticamente válida mesmo quando nenhuma data é selecionada.

Esse padrão faz com que um único dataset salvo sirva tanto para uma visão geral sem filtros quanto para uma análise detalhada com escopo de datas - o usuário simplesmente seleciona as datas no dashboard e o SQL se ajusta automaticamente.

Fluxo de trabalho para adicionar parâmetros de data:

1. Crie ou abra seu dataset virtual no SQL Lab.
2. Substitua a condição de data estática no WHERE por um bloco condicional Jinja que referencie as variáveis from_dttm e to_dttm.
3. Salve o dataset atualizado.
4. Abra a aba Columns e clique em Synchronise columns from source para registrar quaisquer novos campos.
5. Crie ou atualize um gráfico nesse dataset; confirme que o campo de filtro de data aparece.
6. Salve o gráfico e verifique se o filtro funciona dentro da visualização do relatório no Bitrix24.

Os parâmetros não se limitam a datas - o mesmo mecanismo Jinja pode ser estendido a outras dimensões de filtro conforme as necessidades do relatório. Essa abordagem garante que o relatório permaneça interativo para os usuários de negócio, enquanto toda a complexidade fica encapsulada na camada SQL.

Para equipes que criam relatórios onde a sensibilidade dos dados é relevante - por exemplo, restringindo quais registros um gerente pode visualizar - esse padrão se integra com facilidade a controles de acesso baseados em papel. Consulte também Self-Hosted Bitrix24 Security Hardening: 25-Point Checklist para considerações sobre controle de acesso em implantações on-premise.

O Pipeline Dataset - Gráfico - Relatório

Todo relatório de BI do Bitrix24 segue uma hierarquia rígida de três camadas: o dataset define o formato dos dados, o gráfico define a visualização desses dados e o relatório (dashboard) reúne múltiplos gráficos em uma visão analítica publicada.

Compreender essa hierarquia evita os erros mais comuns de iniciantes - como construir gráficos diretamente de tabelas brutas quando um dataset virtual era necessário, ou tentar filtrar no nível do relatório o que deveria ter sido filtrado no nível do dataset.

Camada 1 - Dataset: Define quais campos estão disponíveis, seus tipos, nomes de exibição e quaisquer métricas pré-calculadas ou colunas computadas. Um único dataset pode alimentar muitos gráficos.

Camada 2 - Gráfico: Criado na seção Charts ao selecionar um dataset e um tipo de visualização. O construtor de gráficos oferece dois modos de consulta:

• Aggregation - para totais, comparações e métricas agrupadas (ex.: valor total de negócios por estágio)
• Raw Records - para visualizações detalhadas no nível da linha

Os campos são atribuídos às zonas do gráfico por arrastar e soltar:

• Dimensions - categorias qualitativas (nome do produto, categoria, data)
• Metrics - valores numéricos com uma função de agregação (SUM, COUNT, AVG, MAX, MIN, first_value)

Escolha a função de agregação adequada à natureza da métrica: SUM para valores cumulativos, COUNT para contagem de registros, MAX ou MIN para valores extremos. Quando uma coluna já contém um total pré-agregado (ex.: calculado via função de janela), use MAX() no gráfico para evitar re-somagem.

Camada 3 - Relatório (Dashboard): Os gráficos são adicionados a um relatório/dashboard durante a criação do gráfico ("save to report") ou pelo editor do dashboard. Após a publicação, alterações feitas no BI Constructor exigem que o relatório seja fechado e reaberto no Bitrix24 para ter efeito.

Atalho prático: Para criar um novo gráfico rapidamente, abra um gráfico semelhante já existente, reconfigure seus campos e use "Save as New Chart" - nunca sobrescreva o original.

Para uma visão mais ampla de como a análise nativa do Bitrix24 complementa o BI Constructor, consulte Bitrix24 CRM Analytics and Sales Dashboards: Reports, Funnels and Forecasting.

Gerando SQL com um Copiloto de IA: Um Atalho Prático

A geração de SQL via copiloto de IA - inserindo as descrições dos campos do dataset no ChatGPT ou no assistente de IA integrado do Bitrix24 - é uma técnica comprovada e que economiza tempo, produzindo rascunhos funcionais de consultas, embora o desenvolvedor deva revisar a lógica de join e a granularidade dos dados antes de salvar.

A técnica funciona porque a documentação de datasets do BI Constructor já descreve nomes de tabelas, nomes de colunas e relacionamentos em formato estruturado. Ao copiar essa documentação e descrever a pergunta de negócio em linguagem simples, um modelo de IA tem contexto suficiente para gerar SQL Trino sintaticamente correto.

Passos:

1. Abra as descrições dos datasets dentro do BI Constructor (os metadados de colunas das tabelas que você precisa).
2. Copie a lista completa de campos e as descrições das tabelas.
3. Cole no assistente de IA de sua preferência e descreva o que você precisa: "Com base nessas tabelas e colunas, escreva uma consulta SQL Trino que retorne os negócios criados nos últimos 30 dias, agrupados por gerente responsável, com o valor total e a contagem de negócios."
4. Revise o SQL gerado: verifique se as condições de JOIN estão corretas, se não há linhas duplicadas indesejadas e se a granularidade corresponde à intenção do gráfico.
5. Teste no SQL Lab, itere se necessário e depois salve.

Essa abordagem é especialmente útil para analistas que se sentem à vontade para ler e editar SQL, mas têm menos prática em escrever JOINs complexos do zero. Ela não substitui o entendimento do modelo de dados - um JOIN incorreto em um dataset retorna números errados de forma silenciosa, o que é pior do que não ter relatório algum.

Para equipes com partes interessadas não técnicas, a experiência do usuário final permanece totalmente sem código: depois que um desenvolvedor constrói e publica um dashboard, os usuários de negócio interagem apenas com filtros, seletores de data e detalhamentos. O SQL permanece invisível por trás do relatório publicado.

Boas Práticas para Datasets Prontos para Produção

Datasets de BI em produção no Bitrix24 devem ser enxutos (apenas as colunas necessárias), com nomenclatura consistente em caracteres latinos sem espaços, documentados com descrições de colunas e testados de ponta a ponta no Bitrix24 - não apenas no SQL Lab - antes de serem compartilhados com os usuários finais.

Um resumo das práticas mais importantes, extraídas de experiências reais de implementação:

O BI Constructor armazena em cache os dados do dashboard por ~1 hora a partir do primeiro carregamento. Se um relatório precisar de dados quase em tempo real, oriente os usuários a atualizar manualmente pelo menu de configurações, em vez de esperar atualizações ao vivo.

Para organizações que avaliam se o BI Constructor é suficiente em comparação a uma plataforma de BI dedicada, a resposta geralmente depende do volume de dados e das necessidades de relatórios entre sistemas. Quando o Bitrix24 é o principal sistema de registro para CRM, negócios e dados de leads, o BI Constructor com datasets SQL virtuais cobre a grande maioria dos requisitos analíticos - incluindo análise de coorte, velocidade do pipeline e relatórios de mix de produtos - sem necessidade de ferramentas externas. Se você ainda está avaliando o Bitrix24 como plataforma, Bitrix24 vs HubSpot: An Honest Comparison for SMBs e Bitrix24 CRM Analytics and Sales Dashboards oferecem contexto relevante sobre as capacidades analíticas mais amplas.

Para empresas de manufatura ou logística que constroem dashboards operacionais sobre dados de negócios do Bitrix24, os datasets SQL virtuais são especialmente poderosos quando combinados com integração ERP - consulte Bitrix24 ERP & Accounting Integration: Two-Way Data Sync Guide para saber como dados externos podem fluir para as tabelas que o BI Constructor consulta.