Melhor estrutura para tabelas Fato que precisam detalhar os dados (visuais com listas)

[Michele]

Pessoal,

Sabemos que as tabelas Fato não devem conter muitas colunas, pois isso prejudica a otimização do modelo, assim como não devemos ter Dimensões com muitas linhas.

Porém no cenário da minha empresa para tudo precisa de "nominal", dizer detalhes do que aconteceu, quem foi a pessoa, datas, etc.

Primeiro tentamos fazer uma dimensão Pessoa, mas aí temos milhões de linhas numa dimensão, não era performático, então começamos a colocar as colunas nas Fatos, mas daí começamos a ter muitas colunas nas Fatos, é bem mais performático, mas estamos começando a sofrer com lentidão e erro ao acessar relatórios, dependendo da quantidade de pessoas usando simultaneamente.

Outro dificultador é que precisamos ficar criando campos personalizados, com última resposta disso quando tal cenário acontecer, etc. e são mais colunas nas Fatos. E por fim temos centenas de relatórios, mais de 300, pois temos um modelo do programa de saúde vendido para as empresas e aí, claro, uma publicação para cada empresa, filtrando pela empresa em questão.

Queria saber se alguém passa pelo mesmo problema e quais foram as ações tomadas para melhorar isso. No nosso cenário usamos a licença Embedded e, até hoje, tínhamos o nível 1, paliativamente/emergencialmente passamos para o nível 2, até identificar o que conseguiremos otimizar.

Uma coisa que estou sugerindo (quase que dizendo que não tem outra opção) é a redução da base histórica, pois hoje trazemos todas as linhas, estou sugerindo um corte para apenas dados dos últimos 24 meses.

Nossa estrutura geral é:

• SERVIDOR PRODUÇÃO > SERVIDOR BI (tabelas com dados resumidos e estruturados já em forma mais macro)
• SERVIDOR BI: faz a ETL tratando os campos e criando as tabelas com os dados tratados.
• POWER BI ONLINE: Fluxo de Dados "principal" que busca os dados no SERVIDOR BI
• Fluxo de dados "secundários" que separa as bases de clientes e relatórios internos, para ficar mais performático o agendamento das atualizações.
• Conjuntos de Dados: que busca dados dos Fluxos de Dados para cada modelo de relatório, de modo que quando atualizar aquele conjunto atualize os relatórios de todos os clientes que usam aquele modelo.
• Relatórios:
  • os que usam Conjunto de Dados precisam apenas da atualização do Conjunto.
  • os que são gerenciais internos e personalizados de clientes cada um possui um Conjunto próprio e é agendado de forma separada.

Exemplo:

Editado Janeiro 9 por Michele

[Michele]

Olá a todos,

Apenas para deixar registrado algumas coisas que definimos fazer e que podem ajudar alguém futuramente:

1. Passar validações que estávamos fazendo no PQ para a ETL no banco de dados, deixando no PQ o mínimo possível de transformações.
2. Removendo validações nas medidas e que dava para já fazer na ETL no banco de dados.
3. Criaremos uma tabela sumarizada da nossa maior tabela, que tem 8M de linhas, para usar em relatórios onde não precisemos de nominais e onde precisar de nominais ainda vamos ter que estudar como fazer isso, se vamos conseguir reduzir essa tabela de 8M de linhas ou vamos precisar continuar usando ela nesses casos.

Nossa intenção era conseguir manter a utilização do nível A1 da licença Embedded, mas por enquanto isso não está sendo possível e estamos tendo que usar a A2. Depois que aplicarmos todas essas otimizações vamos fazer teste novamente se conseguimos regredir, caso contrário, pelo menos, que não precisemos escalar tão cedo novamente.

Por enquanto foi isso que identificamos que é possível fazer, considerando o nosso cenário. Estamos também tentando usar o App Microsoft Fabric Capacity, alinhando com o suporte da Microsoft como ler os gráficos, para saber onde há gargalos, para então analisarmos o relatório/conjunto de dados e poder avaliar o que tem de diferente nele e que possa ser otimizado.

Espero estar ajudando alguém futuramente que possa passar pela mesma situação.

[Joao Raulino]

Suave @Michele?

Gosto muito desse tipo de tópico que propõe um debate sobre melhores praticas 🙌, apesar de ainda não ver muitos desse tipo por aqui... 😁

Bem interessante esse teu cenário e certamente outra empresas de BI com publicações centralizadas devem sofrer dos mesmos problemas!

A queda de performance que você tem visto é mais nos visuais ou também na parte de carregamento dos fluxos/conjuntos de dados? E vocês já utilizam algum tipo de atualização incremental e/ou particionamento de dados no modelo de vocês?

Abs!

[Edson Igari]

@Michele, uma possível solução é utilizar agregações e modelo composto (Import e DQ) como o Léo mostra nessa aula.

Utilizando agregações e modelo composto (Import e DQ) - Trabalhando com Grandes Bases e Projetos Corporativos (xperiun.com)

[Michele]

Olá @Joao Raulino e @Edson Igari!

A parte de carregamento dos fluxos está ok, tem uma performance boa, o problema está no carregamento dos visuais, na demora e por consequência no ponto de esgotamento que acabou acontecendo e tivemos que passar nossa licença Embedded para o nível 2.

Atualização incremental e modelo composto não creio que vá ajudar muito, pois se não estou muito enganada isso é mais quando o carregamento dos dados para o BI está sofrendo. Se for isso mesmo, este não é nosso caso, é na ponta do usuário mesmo, na utilização dos relatórios e na ida do relatório ao conjunto de dados para apresentar as informações.

Agregações entendo que ajudariam se nosso problema fosse no cálculo das medidas, mas não é, é ter que apresentar "nominal" de cada indicador, listar as pessoas, atendimentos, etc. e todas as informações envolvidas, que nos fazem precisar de muitas colunas nas tabelas Fatos e, obviamente, deixa nossos modelos fora das melhores práticas, mas infelizmente ainda não conseguir achar uma solução que atenda isso sem gerar esse custo de processamento e memória. Além das colunas nas fatos, para não virem linhas indevidas, a gente acaba colocando no filtro lateral a medida do indicador e pedindo para listar apenas linhas onde o resultado da medida seja > 0, que pesa ainda mais, mas também ainda não achamos forma de fazer sem isso.

Quanto à redução dos dados, essa é uma das partes que vamos "atacar", hoje nos reunimos para definir um corte na base histórica, de modo a trazer apenas dados dos últimos 24 meses, porém ainda precisamos definir se todas as tabelas são passíveis essa ação, senão teremos alguma coisa nesses anos anteriores que precisa ser trazida. Com isso eu creio que vai dar uma melhorada para, pelo menos, podermos estudar se existe outra forma de entregar esses nominais sem ser tão custoso para os modelos.

Eu queria era saber se alguém já passou por isso, precisou ter no modelo essas várias colunas nas Fatos para criar visuais de detalhamento dos indicadores e como fez para ficar otimizado.

Depois que fizermos esses cortes na base histórica para trazer apenas dados dos últimos 24 meses, voltar nossa licença para o nível 1 e testar se vai atender por enquanto, vamos focar em tentar achar uma solução para esse problema dos "nominais", se achar alguma solução conto aqui.

Fico aguardando se alguém tem alguma sugestão para testarmos 😁

[Renato Carlos Rossato]

Olá @Michele, os fluxos de dados para bases muito grandes geralmente a atualização é mais demorada mesmo. Agora para a questão das visualizações um ponto que impacta muito é medidas em que se usa colunas com strings no filtro, isso em projetos que já desenvolvimento aqui quando troquei os filtros das medidas para uma coluna de número por exemplo a melhora foi imediata, não sei se é o caso aí, mas tente dar uma olhada nas medidas que tem condições como texto nos filtros e tente substituir por números que acredito que deve amenizar o problema.

A ideia que citou também de eliminar a base histórica, você pode também criar um outro PBIX somente com a base histórica e manter de forma separada.

Espero ter ajudado.

Editado Janeiro 11 por Renato Carlos Rossato

[champanjonata]

Em 09/01/2024 em 16:24, Michele disse:

Pessoal,

Sabemos que as tabelas Fato não devem conter muitas colunas, pois isso prejudica a otimização do modelo, assim como não devemos ter Dimensões com muitas linhas.

Porém no cenário da minha empresa para tudo precisa de "nominal", dizer detalhes do que aconteceu, quem foi a pessoa, datas, etc.

Primeiro tentamos fazer uma dimensão Pessoa, mas aí temos milhões de linhas numa dimensão, não era performático, então começamos a colocar as colunas nas Fatos, mas daí começamos a ter muitas colunas nas Fatos, é bem mais performático, mas estamos começando a sofrer com lentidão e erro ao acessar relatórios, dependendo da quantidade de pessoas usando simultaneamente.

Outro dificultador é que precisamos ficar criando campos personalizados, com última resposta disso quando tal cenário acontecer, etc. e são mais colunas nas Fatos. E por fim temos centenas de relatórios, mais de 300, pois temos um modelo do programa de saúde vendido para as empresas e aí, claro, uma publicação para cada empresa, filtrando pela empresa em questão.

Queria saber se alguém passa pelo mesmo problema e quais foram as ações tomadas para melhorar isso. No nosso cenário usamos a licença Embedded e, até hoje, tínhamos o nível 1, paliativamente/emergencialmente passamos para o nível 2, até identificar o que conseguiremos otimizar.

Uma coisa que estou sugerindo (quase que dizendo que não tem outra opção) é a redução da base histórica, pois hoje trazemos todas as linhas, estou sugerindo um corte para apenas dados dos últimos 24 meses.

Nossa estrutura geral é:

• SERVIDOR PRODUÇÃO > SERVIDOR BI (tabelas com dados resumidos e estruturados já em forma mais macro)
• SERVIDOR BI: faz a ETL tratando os campos e criando as tabelas com os dados tratados.
• POWER BI ONLINE: Fluxo de Dados "principal" que busca os dados no SERVIDOR BI
• Fluxo de dados "secundários" que separa as bases de clientes e relatórios internos, para ficar mais performático o agendamento das atualizações.
• Conjuntos de Dados: que busca dados dos Fluxos de Dados para cada modelo de relatório, de modo que quando atualizar aquele conjunto atualize os relatórios de todos os clientes que usam aquele modelo.
• Relatórios:
  • os que usam Conjunto de Dados precisam apenas da atualização do Conjunto.
  • os que são gerenciais internos e personalizados de clientes cada um possui um Conjunto próprio e é agendado de forma separada.

Exemplo:

Olá @Michele, tudo bem?

Sei que o tópico foi marcado como resolvido, mas gostaria de compartilhar algumas dicas que não foram ditas:

1 - Explique pro pessoal que o desenvolvimento de um BI requer planejamento, dito isso, não podemos ficar criando colunas calculadas a torto e a direita.. no começo pode parecer prático mas no futuro é só dor de cabeça. Um projeto estruturado normalmente tem mapeado muita regra e isso é refletido no ETL (aqui você cria coluna).

2 - Verifique qual (ais) medidas estão causando a lentidão e tente escrevê-las de forma mais eficiente (poste o dax aqui se for o caso e peça ajuda, eu mesmo já fiz isso e vale muito a pena).

3 - Verifique suas ligações: normalmente uma medida lenta não é problema de volume de dados, ou temos ligações incorretas/erradas ou o DAX não está performático. Não sei o que seria um volume 'grande' pra você, mas aqui por exemplo eu tenho uma tabela fato com 20 milhões de registros e os cálculos carregam em 1s.

4 - Se achar prudente e necessário, compartilhe o pbix e forneça mais detalhes de quais telas/medidas estão lentas que tento lhe ajudar melhor.

É isso, sucesso no seu projeto.