Composite Models: Directquery for power bi datasets

Esta nova feature traz uma inovação significante na área de dados. O Power BI agora expandir os modelos de dados e dar a flexibilidade que até então não existia.

A funcionalidade composite models na prática adiciona um modelo local a um modelo "remoto" live connection. Ou seja, você pode se conectar no dataset publicado pela empresa e incrementar ele com suas próprias informações, estendendo o modelo (modelo semântico) original  adicionando tabelas, colunas, medidas.

Funciona como se existisse uma cópia do modelo remoto no painel local, mas com o benefício de não duplicar dados já armazenados. Portanto isso é ideal para quem quer utilizar os modelos de dados corporativos e combinar (incrementar) com dados de planilhas ou urls.

Ao habilitar o recurso, uma mensagem irá aparecer nos modelos com live connection 

Quando o modelo for publicado no serviço do power bi, ele irá gerar um dataset novo para o arquivo alterado. O que não acontece quando somente a live connection está sendo usada

Um dataset poderá ser dependente de outro dataset.

Algumas observações importantes que devem ser levadas em consideração:

- Conflitos de nomes de tabelas e medidas

- RLS somente pode ser configurada nas tabelas locais não do data source remoto

- Calculation groups nos data sources remotos não são suportados

- Sort by column são é suportado

- Metadados como pastas/kpis/rls/translations não são importados do data source remoto

- A quantidade máxima de cascateamento de data source é de três 

Exemplos

Após se conectar ao cubo tabular o usuário pode criar:

- Uma nova coluna

- Importar dados externos do excel, csv e relacionar com o cubo sem fazer download dos dados

Essa feature permite por exemplo que dois modelos externos possam ser relacionados em um terceiro modelo que cria medidas que consideram ambos modelos externos.

https://www.sqlbi.com/articles/new-composite-models-in-power-bi-a-milestone-in-business-intelligence/

https://powerbi.microsoft.com/pt-br/blog/directquery-for-power-bi-datasets-and-azure-analysis-services-preview/

Melhores Práticas para Modelagem de Dados Tabular

O processo de fazer a modelagem de dados para um cubo tabular envolve principalmente identificar o escopo do negócio para extrair o real significado por traz dos dados que evidenciam a situação de um processo de negócio. Neste primeiro momento a preocupação fica restrita a encontrar os limites que definem o assunto que o modelo de dados busca suportar. Superando essa fase, iniciamos um estágio mais técnico, onde algumas configurações precisam ser realizadas para garantir um modelo de dados mais eficiente.

A primeira e mais importante recomendação é: buscar a desmoralização das dimensões e a criação de um modelo estrela. Isso é importante porque resulta em um modelo mais simples, tanto para o usuário navegar entre os atributos quanto para o desenvolvedor criar as medidas em DAX. Entre outras práticas não menos importantes estão:

Não trazer chaves primárias ou colunas date/time com horário para o modelo, se for necessário utilizar o horário, separar em outra coluna. Isso aumenta o tamanho do modelo significativamente por causa do método de armazenamento colunar do Power BI.

Desabilitar a opção auto date/time do Power BI. Essa opção que cria internamente tabelas com intervalos de Datas, máximo e mínimo para cada coluna de data do modelo, caso uma das colunas esteja com uma data muito antiga, as tabelas de datas irão impactar no tamanho da base de dados sem necessidade. 

Crie sempre a sua própria tabela de datas: Existe diversas formas de criar sua própria tabela com intervalos de datas contínuos. Pode ser via script sql, por um arquivo em excel, usando a linguagem M no Power Query ou usando DAX. O ponto importante aqui é garantir que ela atenda 100% dos intervalos de datas do seu modelo e tenha o ano completo, começando no primeiro dia do primeiro ano e terminando no último dia do maior ano. Isso é importante para o funcionamento das funções de tempo.

Remover todas as colunas não utilizadas: Manter no modelo apenas o que for usado para análise e relacionamento, ou seja, idealmente o modelo deve ter apenas chaves estrangeiras e colunas utilizadas em medidas na tabela de fatos. Nas dimensões, apenas os atributos utilizados para análise.

Não criar colunas calculadas apenas para suportar um cálculo de uma medida, utilize ou funções iterativas e variáveis. Isso é importante porque cada nova coluna calculada criada o modelo de dados precisam ser  pré-processados, prejudicando a performance do modelo.

Oculte todas as chaves estrangeiras: A princípio a única função delas é manter o relacionamento, por isso, não deixe que elas fiquem visíveis.

Definir corretamente os tipos de dados para cada atributo: Não utilize decimal quando não for necessário. Defina para colunas de localização como município, estados, lat-long, as categorias corretas.

Não utilize as colunas numéricas para fazer a agregações. Crie sempre uma medida, isso é importante porque alguns recursos como transição de contexto em colunas calculadas, ou o recurso calculation groups não funcionam a menos que inclua a coluna dentro de uma função calculate. Mas se for usar as colunas numéricas para agregação, define previamente todas as formas de sumarização e desabilite a agregação para colunas numéricas como ano que não precisam.

Não utilize relacionamento bidirecional. Esse tipo de relacionamento pode causar referência circular no modelo. Esse tipo de relacionamento físico também impacta na forma como o conceito de tabelas expandidas funciona. Para contornar isso e fazer uma medida receber um filtro que não está chegando por falta de relacionamento bidirecional, use o modificador de contexto CROSSFILTER. 

Identifique se existem chaves na tabela de fatos que não possuem valor correspondente nas dimensões. Apesar do Power BI fazer o tratamento destes casos adicionando um valor Blank na dimensão, isso pode gerar distorções quando usamos a funções VALUES ou DISTINCT, ou dúvidas quando o atributo é usado como slice.

Use sempre a função DIVIDE para evitar erros quando houver divisões por Zero.

Utilize nomes claros para atributos. Os nomes padronizados e autoexplicativos podem melhorar muito a experiência de construção e análise do modelo.

Estas são apenas algumas das muitas coisas que podemos fazer para melhorar a experiência de análise e construção de modelos tabular.

Power BI é Simples, Mas Será que é Fácil?

Há alguns anos, falar de BI não era tão simples, explicar o conceito, diferenciar as tecnologias de BI dos relatórios operacionais, justificar o valor para o cliente, engajar ele em um processo de coleta e tratamento cuidadoso dos dados nem sempre era uma tarefa simples. Tudo isso parece estar mudando muito rápido, depois que a Microsoft apostou na centralização dos produtos de BI (antes pulverizados em add-ons, excel, componentes sharepoint, report server), em uma solução como Power BI, com opções gratuitas e acessíveis. No início parecia uma guinada radical e problemática, clientes que tinham grandes projetos implantados utilizando cubos multidimensionais teriam que jogar fora ou reconstruir, um retrabalho que poderia retardar a implantação, no início sequer havia suporte para todos os recursos que já existiam e mesmo assim tudo isso aconteceu bem rápido. Agora a questão se inverteu, o Power BI se popularizou e temos que lidar com clientes que tem a percepção de que tudo passou a ser muito simples e em apenas alguns dias toda a modelagem e os indicadores de negócio podem ser entregues em produção com versão desktop e mobile para toda a empresa com baixo custo. Mas a situação muda rapidamente quando o projeto cresce e os desafios de lidar com bases de dados grandes, que precisam de estratégias de agregação, atualização incremental, medidas complexas em DAX, dimensões com hierarquias desbalanceadas, relacionamentos virtuais, medidas com resultados incorretos começam a surgir, a performance começa a cair, as análises mais complexas não são feitas, e custos de licenciamento de contas Pro ou Premium pressionam o orçamento.

A Microsoft fez e ainda faz um produto incrivelmente simples e produtivo, mas na prática vejo que apesar de todos os esforços as análises que agregam mais valor continuarão sempre dependendo de um profundo conhecimento da linguagem DAX e técnicas de modelagem adequadas. Isso parece não mudar.

Então, mas porque DAX é simples mas não é facil?

Porque é uma linguagem funcional.

Para quem está acostumado com linguagens procedurais, ou com outro paradigma não funcional, onde a execução segue um algoritmo passo a passo, sequencial, pode ter alguma dificuldade em virar a chave para uma visão mais relacionada à matemática, com funções e retornos encadeados, com comportamentos implícitos não declarados no código. 

Porque cada medida é executada em um contexto de avaliação

É necessário entender que os valores apresentados por uma medida não são aqueles agregados a partir do que sendo apresentado na tela, o que para usuários de Excel pode parecer pouco intuitivo. As medidas apresentam o resultado seguindo o contexto de filtro atual, independentemente da linha da tabela que ela está sendo apresentada. Isso é complicado de visualizar, e por isso pode gerar muitas dúvidas. O contexto da linha é apenas um dos filtros, podem ainda existir filtro vindo de outros visuais (explícitos), de parâmetros da medida (implícitos) ou até mesmo pode não haver filtros explícitos quando isso é removido usando algumas funções. Em resumo, os resultados visualizados em tabelas e gráficos podem não ter relação nenhuma com o que está sendo apresentado.

Porque existe algo chamado transição de contexto

O contexto de avaliação do filtro no qual os cálculos são executados podem mudar implicitamente, entre contexto de linha ou contexto de filtro. Identificar em que momento isso está acontecendo não é simples, depende de vários fatores como o tipo de função se é iterativa ou não, qual parâmetro da função está sendo usada, se é medida, tabela calculada, se está dentro de uma chamada CALCULATE, se uma coluna calculada ou não.

Porque depende da forma que foi feita a modelagem dos dados

A modelagem diretamente no tamanho da base de dados, na performance e uso de memória, impacta em recursos como o uso de funções de inteligência de tempo, em como a propagação dos contextos de de filtro irá se comportar. Dependendo dos tipos de relacionamentos entre as tabelas um filtro pode ou não se propagar no modelo, afetando diretamente os resultados das medidas e mudando resultados.

Como a própria ferramenta Power BI, a linguagem é simples com alguns poucos conceitos chave importantes. Mas neste caso, a simplicidade vem aliada a uma mudança de paradigma que faz com que esses princípios sejam imprescindíveis para a evolução na aprendizagem ferramenta.

Gostaria de deixar a referência deste artigo off-topic do site sqlbi.com que traz uma discussão sobre como é o processo de aprendizagem.

https://www.sqlbi.com/blog/alberto/2020/06/20/7-reasons-dax-is-not-easy/

Sincronizando Slicers no Power BI

Slicers são componentes visuais que aplicam filtros por padrão em todos os visuais da página, mas podem ser configurados de forma customizada para afetar partes específicas do painel. São usados quando estamos em busca de um destaque maior para o filtro ou para fazer com que apenas parte dos gráficos sejam impactadas (uma vez que o PB possui uma área de filtros padrão). Além disso, ainda é possível controlar a sincronização entre eles, trazendo maior flexibilidade e atendendo cenários onde as análises estão distribuídas em várias páginas.

Antes que este recurso fosse lançado, ele se transformou em uma das ideias mais requisitadas pelos usuários, mostrando a importância e os vários cenários que ela pode ser aplicada.

O recurso funciona basicamente assim:

Todos os slicers configurados com o mesmo tipo de dado ou atributo do modelo, podem propagar os filtros marcados pelo usuário de um para o outro. Por exemplo: ao alterar o filtro "data" da página 1, outro filtro que usa a mesma coluna "data" na pagina 2 é atualizado com o mesmo valor.

Você pode utilizar por exemplo em uma página inicial que resume alguns indicadores e aplica alguns filtros. Estes filtros uma vez aplicados na pagina inicial, criam o contexto para o usuário navegar nas demais páginas já com os filtros definidos. Isso faria com que a experiência de navegação entre estas páginas ficasse mais fluida e consistente.

Para criar um slice sincronizado:

Acessar a aba "View" e habilitar a janela de sincronização.

Todas as configurações são feitas a partir desta tela.

Neste exemplo temos duas datas diferentes DueDate e OrderDate no mesmo grupo de sincronização "GrupoSincroniaFullDate", isso faz com que fiquem sincronizados na mesma página.

Outro cenário seria sincronizar em páginas diferentes, para isso marque o checkbox da página que o filtro será inserido. Verifique que automaticamente ele foi inserido e o grupo de sincronização configurado.

Com isso, ao alterar o filtro na pagina "Sync Filters" todos os filtros do mesmo grupo de sincronização tanto da página atual quanto das demais páginas serão sincronizados.

Fonte:
https://powerpivotpro.com/2018/02/connect-one-slicer-power-bi-report-pages/
https://prathy.com/2019/02/sync-slicers-sync-slicers-advanced-options/

Análise de Performance e Monitoramento em Modelos Tabular - SSAS e Power BI

Para fazer uma análise mais detalha da performance de modelos Tabular é importante entender o método de armazenamento que essa tecnologia utiliza.

A maioria dos bancos de dados relacionais armazenam fisicamente os dados linha por linha e organiza em páginas de tamanhos específicos (SQL Server 8K), fazendo com que cada página guarde um subconjunto da tabela. Em geral, esse método requer que todas as páginas sejam lidas quando uma agregação é feita utilizando apenas uma coluna. Exemplo, para saber o total de posts todos os arquivos de dados organizados em páginas precisam ser carregadas para obter o resultado.

Já com o método column-orientend que é utilizado pelo SSAS e Power BI, as páginas armazenam as colunas separadamente trazendo maior performance nas agregações, pois para obter um resultado da agregação de uma coluna menos páginas precisam ser lidas. A desvantagem é que essa arquitetura requer maior esforço computacional em casos onde várias colunas estão envolvidas. De fato este método é muito mais eficiente para sistemas analíticos.

Column-oriented

Agora vamos falar especificamente sobre os modelos Tabular do SSAS e Power BI. Eles utilizam a estrutura de dados xVelocity in-memory que implementa o método column-store descrito acima.

Esta arquitetura mantém os dados em colunas altamente compactadas, armazenadas separadamente em dicionários de dados e uma lista de índices que representa dos valores da coluna na sequencia que são armazenados na tabela. São estas estruturas que são carregadas para a memória RAM do servidor quando o modelo é processado.

Por exemplo: a tabela de produtos possui a coluna category que possui apenas dois valores distintos. Neste caso o dicionário irá manter apenas os dados "Accessories, Bikes" e irá criar um índice que representa a sequencia que estes elementos aparecem na tabela de acordo com a posição de cada elemento no dicionário. 

Como podemos ver, o dicionário pode ser a parte que irá demandar mais recursos de armazenamento. Essa arquitetura traz algumas questões importantes que devem ser consideradas durante a construção do modelo:

- Estimar o tamanho do banco de dados column-store não é tão simples quanto em modelos row-oriented (tamanho do registro * numero de linhas) que com apenas alguns exemplos e histórico de uso é possível estimar com precisão.

- O tamanho das tabelas em modelos Tabular podem ser determinados pelos seguintes fatores em ordem de importância:

• Número de colunas
• Tamanho do dicionário (número de itens distintos)
• Tipo de dado das colunas
• Número de linhas

Analisando Performance e Utilização de Memória

Os principais fatores que influenciam a pressão por mais memória são, o próprio modelo, execução de medidas complexas e usuários concorrentes. Veremos como monitorar e fazer alguns ajustes nestes itens.

Modelo

O que podemos fazer para reduzir no tamanho dos dicionários?

A primeira coisa que temos que ter em mente é: Os modelos Tabulares devem ter apenas as colunas que serão usadas para análise ou criação de relacionamentos. 

Temos que identificar as colunas com maior cardinalidade e se possível remover ou segmentar.

Como não podemos remover as colunas usadas nos relacionamentos do modelo, como por exemplos os códigos das dimensões em um modelo estrela, importe apenas as chaves que serão usadas para concretizar relacionamentos. As chaves primárias das tabelas fatos criam dicionários com a quantidade de elementos do tamanho da tabela e podem aumentar muito o tamanho do dicionário.

Em tabelas de fatos não é necessário carregar uma chave primária ou substituta, o modelo cria implicitamente uma coluna "RowNumber" para identificar o registro. Por exemplo, uma medida COUNT(<chave>) pode ser substituída por COUNTROWS(). Isso pode salvar muito espaço em memória.

Não utilize colunas com datetime com horas. Isso irá gerar um dicionário muito grande, formada pela maioria de elementos distintos. Se realmente for necessário, faça a separação do dia e hora em colunas diferentes. A cardinalidade irá diminuir para 365 dias por ano e 24 * 60 na coluna de horas e se precisar calcular a diferença entre duas datas, faça antecipadamente no momento da carga incluindo em uma view por exemplo.

Caso a tabela de fatos tenha algum código da transação que implemente alguma regra ou taxonomia que possa ser pré-determinada, faça a separação em colunas diferentes. Exemplo: os primeiros 3 caracteres de um código da transação representa a categoria de um produto. Identifique e separe esta coluna, isso irá diminuir a cardinalidade.

Identificar se existe alguma coluna que pode ser obtida através da criação de uma medida. Por exemplo: uma tabela de fatos de vendas que tenha a coluna de quantidade vendida e o valor total da venda. Neste caso, a coluna valor total da venda pode ser removida e uma medida DAX pode ser criada para substitui-la.

SUMX ('Internet Sales'; RELATED( Product['Unit Price']) * 'Internet Sales'[Order Quantity])

Analisando o Modelos

A análise dos modelos pode ser feita tanto de cima para baixo, analisando as querys executadas pelos painéis e identificando as mais lentas, quanto de baixo para cima, analisando os metadados do modelo através das DMVs da SSAS que apresentam o consumo de memória e o tamanho das dicionários.

De cima para baixo:

Ative a aba "Performance Analyzer" inicie a gravação e copie a query do visual que está com pior performance.

Abra o programa DAX Studio e se conecte na instância do modelo. 

Ative o botão "Server Timings" e execute a query.

Serão apresentadas as consultas realizadas no modelo, os tempos, a quantidade de linhas, espaço em memória. Estes dados servem de referência para identificar os atributos envolvidos e as medidas que estão afetando a performance. 

De baixo para cima.

Outra abordagem a consulta dos metadados do modelo para identificar o tamanho dos dicionários e espaço em memória ocupados.

Identificando o tamanho do dicionário:

Identificando o espaço ocupado em memória por cada atributo. Perceba que a maioria dos atributos com maior volume de espaço em memória são as datas e as chaves.

A coluna OBJECT_MEMORY_NONSHRINKABLE está em bytes, para converter para KB divida por 1024.

Identificando as sessões de usuário conectadas e o tempo de cpu

Depois desta analise é possível ter uma boa ideia sobre como está o modelo e onde é possível intervir para otimizar o uso dos recursos. 

Com as DMVs podemos ainda construir um painel no próprio power bi para monitoramento destas medidas listando os bancos, as dimensões/atributos com a quantidade de memória e o tamanho dos dicionários.

Fonte: Microsoft Analysis Services 2012 - Understanding xVelocity and DirectQuery