Médias Móveis Dinâmicas com Calculation Groups do Power BI

 As médias móveis são medidas muito interessantes para auxiliar a análise de tendência e entender como implementar e dar dinamismo na escolha das medidas que serão usadas como referência é o tema deste post.

Para alcançar esse objetivo vamos usar a feature calculation groups para dar esse dinamismo.  O resultado final fará com que o modelo permita que o usuário escolha qualquer medida e tenha diversas opções de médias móveis.

1-Criar a medida em destaque com DATESINPERIOD

2-Depois de criar a medida no DAX Studio e testar, vamos acessar o Tabular Editor e criar um calculation group com um item para cada janela de tempo, 3, 6, 12 meses, e substituir a medida [Sales Amount] pela função SELECTEDMEASURE().

Com o calculation group criado, os itens com as opções de medias móveis podem ser usados como legenda ou filtro nos painéis. É importante lembrar de sempre criar um calculation item "Valor" genérico para representar o valor padrão.

Codigo: https://dax.do/rIWfJKRUUN343z/

Vídeo: https://youtu.be/QAYbEXteNdI

Modificadores - USERELATINSHIP ativa um relacionamento inativo no modelo

Usando em medidas CALCULATE para ativar que por padrão estão inativos.

Um exemplo comum é quanto temos mais de uma data relacionada aos fatos que queremos compartilhar a tabela de datas para que seja possível calcular na mesma linha do tempo eventos que aconteceram no mesmo fato, como data do vencimento e data da entrega.

Exemplo: medidas de total de vendas considerando datas diferentes.

Mas e quando estamos criando código DAX no contexto de linha?

Podemos pensar em usar a função RELATED para buscar o valor da data. mas ao criar uma coluna calculada o mecanismo de DAX utiliza sempre o relacionamento padrão, isso faz com que por exemplo a data retornada seja aquela do relacionamento ativo. Então a melhor saída é recorrer a relacionamentos virtuais com LOOKUP.

Para contornar essa limitação, a função LOOKUP atende. Ela faz uma relação entre as chaves de data sem que exista uma relação física criada.

* onde [DueDateKey]  foi igual a [DateKey] retorna o nome do dia da semana.

Modificadores do CALCULATE - CROSSFILTER e a mudança do sentido do relacionamento no modelo

As funções modificadoras do calculate trazem uma funcionalidades adicionais aos filtros que podem ser usados para modificar o contexto. Em resumo, é como se além de manipular os filtros você pudesse manipular a configuração do próprio modelo de dados, como a direção do relacionamento usando a função CROSSFILTER.

Essa função faz com que uma medida navegue em um relacionamento customizado.

Neste exemplo o relacionamento físico é unidirecional, mas com a função podemos "forçar" o relacionamento bidirecional entre vendas e produtos mesmo sem que esteja configurado no modelo.

Ao filtrar a Cor de um produto não é possível calcular a quantidade de clientes porque o filtro de cor não chega até Customer por causa da direção padrão do relacionamento de Customer com Sales.

Mas se usar o CROSSFILTER o DAX força o relacionamento ambos os sentidos

Com isso é possível totalizar a quantidade de clientes por qualquer atributo de Product mesmo que por padrão esse relacionamento não se propague.

Diferenças entre CALCULATEDTABLE e FILTER em DAX

Qual é verdadeira diferença entre CALCULATETABLE e FILTER se ambas retornam um tabela e aplicam uma expressão como filtro? A resposta é depende, depende da existência ou não de contexto de filtro.

Se não houver contexto de filtro elas funcionarão de forma semelhante, retornam 99 linhas com os produtos da cor vermelha.

  

Mas quanto elas são avaliadas em um contexto de filtro onde uma medida está sendo avaliada a sequencia de execução é diferente, o que muda o resultado.

No caso da CALCULATEDTABLE, primeiramente o filtro é aplicado e depois a tabela do primeiro argumento é criada mantendo o contexto de filtro.

No caso de filter isso não acontece. Filter primeiramente cria a table e depois aplica o filtro. Isso faz com que o resultado seja diferente.

 

Outra diferença é que FILTER aceita expressões de filtro em medidas e CALCULATEDTABLE não.

Os códigos deste exemplo estão em: Dax code

Portfólio de projetos públicos

Neste post eu trago exemplos de projetos realizados utilizando o conjunto de ferramentas de BI da Microsoft.

Projeto OCID

Este projeto apresenta estatísticas da área de direitos humanos do estado do ES. 

Tecnologias utilizadas:

Fonte de dados: SQL Server

ETL: Power Query

Modelo: Power BI

Painel: Power BI

Painel Covid19 Espírito Santo

Este projeto foi realizado com o objetivo de dar transparência aos dados da evolução da pandemia no estado.

Tecnologias utilizadas:

Fonte de dados: MySql, Sql Server, .csv, MinIO

Data warehouse: SQL Server

ETL: Integration Services, Power Query

Modelo: Power BI

Painel: Power BI

Painel de Vacinação

Este projeto foi desenvolvido para dar transparência aos dados de distribuição de doses para a população dos grupos prioritários.

Anuário 2020 Rede Gazeta

Este projeto foi desenvolvido divulgar dos principais indicadores econômicos do estado, como parte do já tradicional conjunto de grandes reportagens sobre o panorama econômico do estado. O layout de página foi restrito o que fez com que fosse necessário trabalhar muito com calculation groups para alternar entre a dezenas de medidas nos mesmos mapas e tabelas.

Painel Chamados

Este é um exemplo de um painel para gestão de serviços de TI com os principais indicadores de prazos de atendimentos, categorias, grupos, etc..

Painel de Telefonia:

Exemplo de painel de telefonia com as principais estatísticas de quantidades de atendimentos, abandonos e tempos médios.

IASES Adolescentes

Perfil de entradas e saídas

Observatorio digital da socioeducação

Neste projeto, são apresentadas diversas estatísticas socioeconômicas do perfil dos menores acautelados e a evolução histórica do fluxo da demanda de ressocialização.

Colunas Calculadas Para Tabela Calendário: Dias desde o início do ano, Dias até o final do ano, Dias desde o início do trimestre e dias até o final do trimestre

Um exemplo de como criar esse tipo de coluna calculada para incrementar a tabela de calendário.

Dias desde o início do ano e Dias até o fim do ano:

"Dias Desde o Início do Ano", DATEDIFF ( STARTOFYEAR ( 'Date'[Date] ), 'Date'[Date], DAY ) + 1

A função STARTOFYEAR retorna a data que representa o primeiro dia do ano do contexto de linha da coluna, com isso, usando a função DATEDIFF conseguimos obter a diferença. Como o primeiro valor é zero, precisamos adicionar 1.

Para fazer a contagem reversa seguimos a mesma lógica, só que desta vez usamos a função ENDOFYEAR.

"Dias Até o fim do Ano", DATEDIFF ( 'Date'[Date], ENDOFYEAR ( 'Date'[Date] ), DAY ),

Para fazer fazer as colunas de inicio e fim do trimestre, usamos as funções STARTOFQUARTER e ENDOFQUARTER.

Veja como ficou o resultado final:

Link para o código: https://dax.do/nC0pJNKP7q6Hj0/

KEEPFILTERS: Removendo repetições do total retornado por uma CALCULATE

Quando um filtro é aplicado em um CALCULATE por padrão ele sobrescreve/faz um merge com o contexto de filtro do painel, com isso, para garantir que o filtro do painel seja mantido a função KEEPFILTERS é usada.

Se tem um contexto de filtro na CALCULATE e o mesmo atributo aplica um filtro no visual por padrão o que vale é da CALCULATE mas KEEPFILTERS inverte isso.

Faz um merge dos filtros da media com os filtros dos visuais e não sobrescreve com o filtro da calculate.

Sem usar KEEPFILTERS o resultado se repete na coluna utilizada como filtro no CALCULATE.

Usando KEEPFILTERS o filtro continua sendo aplicado mas quando encontra a coluna usada no filtro ele volta a incluir ela como filtro. Ou seja, mantém o filtro. É como se ele adicionasse o filtro que foi removido quando a função calculate foi usada.

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

Atualização de Dimensões Usando Slowly Changing Dimensions Type 1 e Tipo 2 no SSIS

Slowly Changing Dimensions (SCD) é uma técnica de modelagem usada em data warehouses para atualização das tabelas de dimensões.

Existem duas principais metodologias para atualização de dimensões:

Tipo 1

Para esse tipo de SCD, você simplesmente sobrescreve os valores de dados existentes com ops novos valores da origem. Isso facilita a atualização da dimensão e limita o crescimento da tabela de dimensões apenas para novos registros. A desvantagem disso é que você perde o valor histórico dos dados porque a dimensão sempre conterá os valores atuais de cada atributo. Por exemplo, você tem uma dimensão de armazenamento que possui um atributo para uma região geográfica. Se houver uma mudança e algumas lojas se moverem de uma região para outra, todos os dados históricos serão movidos. Por exemplo, uma loja que pode estar reportando resultados na região Nordeste, depois altera sua localização para a região do Meio Atlântico. Mas, como resultado da atualização, agora toda a história dessa loja antes da mudança é essencialmente removida do Nordeste e transferida para o distrito do Meio Atlântico. Essa alteração distorcerá os relatórios históricos e os relatórios serão executados antes que a atualização não corresponda mais aos relatórios executados após a atualização no mesmo período de tempo.

Tipo 2

Este é o tipo mais comumente usado para contornar o problema de alteração de histórico do tipo 1. Nesse tipo, a atualização de um dos campos, marca o registro antigo como inativo. Isso permite que a tabela de fatos continue a usar a versão antiga dos dados para fins de geração de relatórios históricos, deixando os dados alterados no novo registro para impactar apenas os dados de fato desse ponto em diante. Várias colunas devem ser adicionadas à tabela de dimensões (datas de início / término do registro ativo e um sinalizador de registro ativo atual) para fornecer o gerenciamento de alterações históricas e garantir o uso ideal do registro ativo. Usando o mesmo exemplo da dimensão Tipo 1 acima, a alteração na região da loja, marcará as colunas de identificação do registro como inativo, e irá incluir um novo registro com a versão atualizada com uma nova chave. Essa nova chave de dimensão será usada na geração da tabela de fatos em andamento. Isso permite que a tabela de fatos ainda use os dados armazenados na chave de dimensão antiga para relatórios históricos. Isso garantirá que os dados permaneçam os mesmos e que um relatório histórico para o mesmo período de tempo executado antes de a atualização ser feita continuará exibindo exatamente os mesmos dados de antes da alteração ter sido feita.

Implementando o Tipo 1

Nesse tipo, os registros da tabela de dimensão destino são atualizados com os novos valores da tabela de origem. Os valores são basicamente substituídos e nenhum histórico é mantido.

Veja como será o fluxo de dados do exemplo:

1-Etapa Source SalesTerritory: Os dados de origem são extraídos da tabela de origem Sales.SalesTerritory

2-Lookup Country Region: O nome da região do país é inserido no fluxo de dados de origem para complementar o fluxo de dados de entrada. Esta etapa é opcional.

3-Slowly Changing Dimension: Etapa principal do fluxo de dados. Ela identifica os campos que serão monitorados no processo de atualização.

Nesta tela, você irá apontar para a tabela de dimensão no destino e irá marcar a coluna da chave negócio.

O chave de negócio representa a chave da tabela de origem, é a chave nativa do sistema de origem. A dimensão usará ela como chave candidata.

Ainda nesta tela, estão listadas todas as colunas de entrada que serão usadas no processo de atualização da dimensão. Marque todas como na tela abaixo e clique em próximo.

- Selecionar o tipo de alteração: identificar quais colunas de entradas serão usadas para realizar a comparação entre o sistema de origem e a dimensão de destino. Marque todas que serão monitoradas pelo processo de atualização.

Ao finalizar o processo, serão criadas duas saídas. Uma para os registros novos e outra para os registros com os campos atualizados.

- Inserir Destino - Novos registros: Verifique se os relacionamentos da tarefa "Inserir Destino" estão criados corretamente.

- Comando OLE DB - Registros atualizados: Verifique o comando de atualização e o mapeamento dos parêmentros.

Para cada linha com dados atualizados, este comando será processado.



































Mapeamento dos parâmetros.

Este tipo de atualização de dimensões é muito comum pois atende grande parte dos cenários, avalie sempre a real necessidade de usar o tipo 2, pois ela adiciona complexidade e aumenta o volume de dados na tabela de dimensão.

Este processo do tipo 1 pode ser substituído por outras técnicas de atualização incremental, já demonstrada em outros artigos neste blog.

Implementando o Tipo 2

A atualização SCD Tipo 2 é um método de atualização mais complexo, ele precisa lidar com cada atributo da dimensão de uma forma diferente.

Existem 3 tipos de tratamento que podem ser configurado para cada atributo.

- Atributo fixo: o valor não é alterado, usado quando é necessário garantir que o valores serão constantes.

- Atributo de alteração: o valor atual é sobrescrito com um novo valor. Usado em atributos que não geram a necessidade de versionar o registro para manutenção de histórico.

- Histórico de Atributo: o valor atual é marcado como inativo e um novo registro é inserido com a mesma chave candidata mas com uma chave substituta nova. Este versionamento, permite que o histórico seja mantido na tabela de fatos. Usar em casos como mudança de localização por exemplo.

Após configurar a fonte de dados, conecte à um componente SCD e inicie a configuração.













Selecione a tabela de Dimensão de Destino. Esta tabela deve ter uma coluna com a chave candidata marcada com o tipo "chave de negócio".

Além disso, a dimensão deve ter duas colunas para identificar a data de criação do registro (StartDate) e a data de inativação do registro (EndDate). Estes campos serão usados para o versionamento de registros com  colunas do tipo "histórico".

Marcar o tipo de tratamento que será dado para cada atributo. Neste momento deve ser avaliado cada atributo, caso a caso.

Nesta etapa serão configurados os tratamentos dos atributos históricos. Para identificar o valor atual e os registros históricos, serão utilizadas as colunas StartDate e EndDate da dimensão.

Após configurar o winzard são geradas automaticamente 4 outputs.

Historical Attribute Inserts Output: todos os registros que foram alterados em seus atributos marcados com o tipo "Histórico". Neste fluxo de dados, um novo registro é inserido, o registro antigo recebe a data atual na coluna "EndDate".

UPDATE [dbo].[DimEmployee]
SET [EndDate] = ? --Recebe a data atual
, [Status] = NULL
WHERE [EmployeeNationalIDAlternateKey] = ? AND [EndDate] IS NULL --NULL representa o registro atual

Após alteração, o fluxo de dados se une aos novos registros da outra saída "New Output"

New Output: São os registros identificados como novos, neste caso eles são apenas inseridos no fluxo e unidos com os registros alterados.

Changing Attribute Updates: São os registros com os atributos marcados com o tipo atributo de alteração. Neste caso, os valores atuais são sobrescritos, não são versionados.

UPDATE [dbo].[DimEmployee]
SET [CurrentFlag] = ?,[EmailAddress] = ?,[FirstName] = ?,[Gender] =
?,[LastName] = ?,[LoginID] = ?,[MaritalStatus] = ?,[MiddleName] = ?,
[Phone] = ?,[SickLeaveHours] = ?,[Title] = ?,[VacationHours] = ?
WHERE [EmployeeNationalIDAlternateKey] = ?


Com cada saída identificada, o fluxo de dados gerado pelo compomente SCD trata a maioria dos casos possíveis garantindo assim a correta atualização das dimensões.




Fonte:
https://www.nuwavesolutions.com/slowly-changing-dimensions/
Livro: Professional SQL Server Integration Services 2014