Tableau LOD Expression e Microstrategy Level Metrics

Bom dia.

No artigo de hoje, resolvi falar sobre este assunto que julgo importante, não só para a comunidade Tableau, como também para meus amigos, que assim como eu, vieram do mundo Microstrategy e hoje são multiplataformas: Tableu LOD Expression e Microstrategy Level Metrics.

Quando iniciei com Tableau na versão 8.3, apresentado pelo meu amigo Raphael Almeida (mestre do mundo do marketing digital), fiquei encantado, com a facilidade de criação de análises e dashboards, tudo já pré-formatado, dentro das melhores práticas de visualização.

Logo então, parti para os cálculos pesados, fiquei maravilhado com o recurso Table Calculation. Porém senti falta do que tinha no Microstrategy, o que chamávamos de "métricas dimensionalizadas", Level Metrics.

Na versão 9.0, a Tableau nos trouxe um super presente, o recurso de LOD Expression, que sanava toda essa necessidade de cálculos de nível, exclusão, fixação e inclusão de nível, dentre outras possibilidades.

Vou além, das ferramentas de Data Discovery que conheço, nenhuma possui este recurso. Algumas, possuem em sua versão Enterprise, opções de criação de métricas de nível, criação de datasets para utilização em seu produto de Data Discovery.

Falando um pouco de como funciona Level Metrics no Microstrategy, na criação da métrica, temos uma configuração de Level (Dimensionality). Nesta opção, por padrão, todas as métricas são Report Level, ou seja, do nível do relatório. Você pode inserir então, atributos, definindo 3 opções:

• Target - define o nível que a métrica será calculada

• Filtering - define como a métrica irá trabalhar em relação ao filtro do relatório

• Grouping - define o agrupamento da métrica, Target ou ignora Target 

Basicamente falando, toda métrica é convertida pelo engine do Microstrategy, em um passo de SQL no relatório. O que as opções acima farão, nada mais é do que configurar as cláusulas Select, Where e Group By (não necessariamente ligadas a cada uma das opções acima).

Falando agora de Tableau LOD Expression. É muito simples. basta criar um campo calculado como o abaixo:

Onde:

• INCLUDE - palavra chave para calcular em um nível inferior
• EXCLUDE - para calcular em um nível superior
• FIXED - para calcular em um nível fixo
• [Order ID] - declaração de dimensão (uma ou mais)
• Sum(Valor da Venda) - expressão de agregação

Vamos aos exemplos. Suponha que na minha base de item de pedidos, queira uma média do valor dos pedidos. Se eu simplesmente fizer um AVG(Valor das Vendas), terei uma média dos itens, como abaixo:

O que preciso fazer é: primeiro somar todos os itens por pedido. Depois fazer a média dessa somatória. Como na minha análise o nível de detalhe é Categoria e Ano, vou criar um campo calculado com a expressão Include para calcular em um nível de detalhe inferior a somatória, por Pedido, e depois fazer simplesmente a média por Categoria e Ano.

Repare que o valor é diferente. Agora vamos tirar a prova, criando uma planilha por pedido, na Categoria Tecnologia, no Ano de 2011.

Repare o Grand Total que é a média da soma dos pedidos. 
O segundo exemplo é com Exclude. Temos Categoria e Subcategoria. Coloquei o valor das vendas  por subcategoria e também os totais por categoria. Agora quero visualizar uma métrica com Exclude (calculando em um nível de detalhe acima), para ver, na subcategoria, o total de sua categoria.

Isso é muito utilizado no Microstrategy para construção de Percentual do Total. Mas você não precisa disso no Tableau para construir percentual do total, pode simplesmente usar Table Calculation, explicado no meu artigo anterior, é uma alternativa. 
Agora vamos a mais um exemplo. Também podemos reproduzir o mesmo resultado anterior, com Fixed, fixando o nível de cálculo para Categoria apenas.

Um detalhe interessante sobre o Fixed, segundo a base de conhecimento Tableau, é a ordem em que seu filtro é executado, logo após os filtros de contexto, e antes dos filtros de dimensões. As expressões do tipo Include e Exclude são executadas após o filtro de dimensão.
Outro teste que fiz, pelo fato de seu nível ser fixo, é colocar ou não em sua expressão, as dimensões que estão no filtro. Repare neste teste com Exclude, excluindo a subcategoria Mesas, como o total de sua categoria muda e a métrica com o Exclude reflete este total.

Porém, ao fazer o mesmo teste com o Fixed, colocando somente categoria na métrica, ele desconsidera o filtro de subcategoria excluindo Mesas.

Repare que o total continua sendo de toda a categoria, sem retirar a subcategoria mesas. Agora repare nesta planilha de vendas por ano:

Vamos filtrar 2011 e colocar nossa métrica de total por categoria - Fixed.

Observe, que enquanto a métrica valor de vendas filtrou 2011, a métrica com Fixed não levou em consideração o filtro. Porém, se adicionarmos em sua definição o atributo Data de Venda, passará a trazer os valores corretos:

E assim concluímos nosso artigo. Na minha opinião se uma ferramenta, seja de BI ou Analytics, não fornecer suporte para métricas de nível, métricas de acumulado YTD, MTD e métricas não agregáveis, como saldo de estoque, saldo bancário, é falha na parte de cálculo. Essas duas ferramentas passam tranquilamente neste teste.

Maiores informações na base de conhecimento Tableau:

15 principais expressões de LOD

Entendendo as Expressões de nível de detalhe (LOD)

Espero que tenham apreciado.

Um abraço.

Fabio Idalgo

Tableau - Table Calculations

No artigo de hoje falaremos um pouco sobre cálculos no Tableau, mais especificamente sobre o recurso Table Calculations.

Esta funcionalidade muito fácil de ser utilizada, permite com poucos cliques, criarmos cálculos avançados como: Percentuais do Total, Rank, Média Móvel, Acumulados, Percentil entre outros.

Para ilustrar nossos exemplos, utilizarei a base de dados que acompanha o Tableau, com a ferramenta Tableau Public. Criei a seguinte análise que servirá para validação de nossos cálculos, com os atributos Categoria e Região nas linhas, e Ano e Trimestre nas colunas. Também habilitei todos os totais para que possamos validar os números.

Dupliquei a planilha, e com um clique na métrica, Add Table Calculation ou Quick Table Calculation conseguimos rapidamente adicionar um Cálculo de Tabela. Escolhi, para nosso exemplo, a função Running Sum, que fará o cálculo acumulado dos valores. Repare que, defni, no caso de acumulado, a função de somatória, poderia ser a média por exemplo. Também preciso definir o nível do cálculo, defini em um primeiro instante Célula, que é a menor unidade, neste caso os valores baterão com a planilha de validação em todas as células.

Depois possuímos diversas opções para definição do nível do cálculo, que vão desde a definição dos atributos propriamente ditos como as opções abaixo:

• Table Across
• Table Down
• Table Across the Down
• Table Down the Across
• Pane Across
• Pane Down
• Pane Across the Down
• Pane Down the Across

Abaixo explicaremos as opções:

1 - Table Across - Realiza a soma horizontalmente da esquerda para a direita, por linha, do primeiro trimestre de 2011 até o quarto trimestre de 2012. Se comparar o valor deste último trimestre 571.026 é o mesmo do Grand Total da linha na planilha da validação.

2 - Pane Across - Realiza a soma horizontalmente da esquerda para a direita, por linha, quebrando por ano. Se comparar o valor do último trimestre de 2011, 293.973 é o mesmo do Total da primeira linha de 2011 na planilha de validação.

3 - Table Down - Realiza a soma vertical de cima para baixo de cada trimestre em todas as regiões em todas categorias. Se comparar o valor final do primeiro trimestre, 696.446 é o mesmo do total da primeira coluna de 2011 na planilha de validação.

4 - Pane Down - Realiza a soma vertical de cima para baixo de todas as regiões, por trimestre, em cada categoria. Se comparar o valor da última região, do primeiro trimestre, na categoria Material de Escritório, 232.558 é o mesmo do total da planilha de validação.

5 - Table Across the Down - Realiza a soma de toda a planilha. A ordem começa horizontalmente na primeira linha, da esquerda para a direita, de T1 2011 até T4 2012, indo em seguida para a segunda linha, da esquerda para a direita, de T1 2011 até T4 2012, repetindo o acumulado nessa ordem da primeira região da primeira categoria em T1 2011 até a última região da segunda categoria em T4 2012. Se você comparar o Grand Total 4.661.999 da planilha de validação é igual ao último valor calculado no Table Calculation.

6 - Table Down the Across, Panel Across the Down, Panel Down the Across - Repetem a lógica acima, levando em consideração se é tabela inteira ou apenas o ano. 

Além do nível de cálculo, você pode utilizar diversos tipos de cálculos disponíveis pela ferramenta, por exemplo, percentual do total (Pane Down - Região dentro da Categoria no Trimestre)

Rank (Pane Down)

Moving Average - Média Móvel dos últimos 6 meses

Maiores informações na base de conhecimento da Tableau:

http://onlinehelp.tableau.com/current/pro/online/en-us/help.htm#calculations_tablecalculations_understanding_addressing.html

Por hoje é isso. Um abraço pessoal e uma boa noite.

Fabio Idalgo

Implementando Hierarquias Irregulares no Microstrategy (Ragged Hierarchies)

Continuando o assunto de Hierarquias atípicas no Microstrategy, chegou a hora de falar sobre Hierarquias Irregulares. Este é um outro assunto cuja solução funciona para qualquer ferramenta, e também é faz parte da metodologia de Advanced Data Warehousing da Microstrategy.

Hierarquias Irregulares ou Ragged Hierarchies é algo como o que você vê abaixo:

Para todos os meus exemplos, gosto de fazer pequenos cases, com modelos de dados utilizando a ferramenta DBDesigner. Implementei um pequeno datamart no Microsoft Access, depois inseri um conteúdo fake, e fiz o mapeamento de sua camada semântica no Microstrategy, para poder exemplificar e mostrar como é na prática, tudo aquilo que estou falando.

Começamos pelo pequeno datamart abaixo, que simula a estrutura de um plano de contas fictício de uma empresa, com 4 níveis, da conta mais sintética até a mais analítica. Uma tabela temporal de mês e uma pequena fato.

Nessa estrutura, foi inserido o seguinte conteúdo, mostrado em duas partes. Este que seria um pedaço do nosso plano de contas fictício:

Primeiro o Ativo da empresa, a Conta Nível 1. Ele é dividido em 3 subgrupos: Ativo Circulante, Ativo Não Circulante e Permanente, que são as Contas Nível 2. 
Para este exemplo, vamos isolar o Ativo Circulante. Abrindo sua estrutura, temos as seguintes Contas Níveis 3 e 4:

Ativo Circulante é uma Conta Nível 2. Embaixo dela, temos Contas Nível 3: Clientes e Disponível. Nessas contas, seguindo a estrutura, temos em cada uma suas Contas Analíticas, na cor amarela, cada uma com um valor monetário igual a 10. 
Porém, observe, que a Conta 5, está diretamente ligada ao Nível 2 - Ativo Circulante, sem a existência de um Nível 3. Em outras palavras, a Conta 5 que é do Nível 4, está ligada diretamente ao Ativo Circulante do Nível 2.
Se você é um projetista cuidadoso, resolveu isso ainda nas camadas de datawarehouse e ETL, e este problema não "estourou" no front end. Porém, se chegou até no Microstrategy, olhe as consequências. O modelo de dados analisado foi mapeado em um projeto no developer. O primeiro cenário, é se emitirmos um relatório, somente com as contas analíticas e seus totais:

Veja que todas as contas foram retornadas, com o valor de 10 em cada uma delas, totalizando 50. Então para este nível de detalhe não teremos problema. O problema ocorre quando você adiciona outros níveis superiores no relatório, ou faz o drill entre níveis superiores:

Observe, quando adicionamos os demais níveis, a última conta, aquela que está ligada diretamente do seu Nível 4 para o Nível 2 sem passar pelo Nível 3, não aparece, pois não compõe o join. Desta forma o valor total passa a ser 40, o que é um erro.


Solução:

Para corrigir este problema é simples. Basta inserir o membro faltante entre o Nível 2 e o Nível 4:

Este novo membro é um conteúdo na tabela dim_Conta_Nivel_3, que deverá estar como pai da Conta 5 na dim_Conta_Nivel_4. Quanto a sua descrição, alguns preferem herdar o descrição do nível pai, outros preferem subir a descrição do nível filho. Eu prefiro um conteúdo como "Nível Ausente".  Com esta correção na camada de ETL, veja como fica agora nosso relatório:

Agora observe o total 50. Observe também que todas as cinco contas aparecem e o nível que estava faltando também com seu novo descritivo. Esta é a solução recomendada pela Microstrategy para hierarquias irregulares, e este conteúdo assim como o do post anterior caem na prova da MCEP.

Obrigado e boa noite

Fabio Idalgo

Implementando Hierarquias Recursivas no Microstrategy (Recursive Hierarchies)

Chegou a vez de abordar a ferramenta Microstrategy. Nos próximos artigos pretendo abordar algumas técnicas que julgo interessante nesta plataforma.

Começaremos com Hierarquias Recursivas. Vale lembrar que esta técnica serve para outras ferramentas. Recentemente implementei também em um projeto Tableau.

Vou utilizar para exemplo, parte de um plano de contas fictício. Imagine a seguinte estrutura:

Esta estrutura está implementada no seguinte datamart de exemplo:

Repare que existe uma tabela de Plano de Contas, onde toda estrutura é armazenada. Ela é uma tabela recursiva. Possui um campo ID_CONTA, seja ela conta sintética ou analítica. Também possui um campo ID_CONTA_PAI, que referencia a própria tabela, quem é sua conta pai. Por fim, possui um campo ID_NIVEL_CONTA, que não são todas tabelas recursivas em um datawarehouse que tem. Explicarei das duas formas de implementar.

Observe como a tabela é preenchida:

Veja que todos os níveis estão na mesma tabela. Para fazer o mapeamento no Microstrategy ou em outra ferramenta, você precisará inicialmente saber o número de níveis existentes no modelo, no caso, são 3 níveis.  Cada um dos níveis será isolado em uma view, seja uma Logical View no Microstrategy ou uma Database View, formando a seguinte estrutura:

O código de cada view segue abaixo. Eu particularmente prefiro as database views pois, quando você visualizar o código SQL de seus relatórios microstrategy fica mais enxuto. 

Nível 1:

SELECT  ID_CONTA    AS ID_NIVEL_1,
               DS_CONTA  AS DS_NIVEL_1
FROM    dim_Plano_Conta
WHERE ID_NIVEL_CONTA=1;

Sem o campo ID_NIVEL_CONTA :

SELECT  ID_CONTA    AS ID_NIVEL_1,
               DS_CONTA  AS DS_NIVEL_1
FROM    dim_Plano_Conta
WHERE ID_CONTA_PAI IS NULL

Nível 2:

SELECT  ID_CONTA AS ID_NIVEL_2, 

               DS_CONTA AS DS_NIVEL_2, 

               ID_CONTA_PAI AS ID_NIVEL_1

FROM     dim_Plano_Conta

WHERE  ID_NIVEL_CONTA=2;

Sem o campo ID_NIVEL_CONTA :

SELECT  ID_CONTA AS ID_NIVEL_2, 

               DS_CONTA AS DS_NIVEL_2, 

               ID_CONTA_PAI AS ID_NIVEL_1

FROM     dim_Plano_Conta N2,

               inner join dim_Plano_Conta N1

               on N2.ID_CONTA_PAI = N1.ID_CONTA

WHERE  N1.ID_CONTA_PAI IS NULL;

Nível 3:

SELECT  ID_CONTA AS ID_NIVEL_3, 

               DS_CONTA AS DS_NIVEL_3, 

               ID_CONTA_PAI AS ID_NIVEL_2

FROM     dim_Plano_Conta

WHERE  ID_NIVEL_CONTA=3;

Sem o campo ID_NIVEL_CONTA :

SELECT   ID_CONTA AS ID_NIVEL_3, 

                DS_CONTA AS DS_NIVEL_3, 

                ID_CONTA_PAI AS ID_NIVEL_2

FROM     dim_Plano_Conta N3,

               inner join dim_Plano_Conta N2

               on N3.ID_CONTA_PAI = N2.ID_CONTA

              inner join dim_Plano_Conta N1

              on N2.ID_CONTA_PAI = N1.ID_CONTA

WHERE  N1.ID_CONTA_PAI IS NULL;

Depois faça o mapeamento de cada view no Microstrategy como um atributo.

Defina o Nível 2 como filho do Nível 1, e o Nível 3 como filho do Nível 2. Também defina o relacionamento do Nível 3 com a fato. Depois é só inserir os atributos e montar o relatório:

E assim finalizamos o artigo. Existe também uma maneira de implementar quando o número de níveis não é conhecido. Porém exige um capítulo aparte e eu não vejo muito sua eficácia como este método.

Obrigado

Fabio Idalgo

Interagindo com Dashboards no Tableau Server

Após alguns artigos abordando criação de dashboards e análises na ferramenta Tableau Desktop, vou falar agora um pouco sobre o Tableau Server. Este produto, para os iniciantes, fica instalado em um servidor dedicado, e lá ficam publicados seus dashboards e respectivos dados para a comunidade de usuários, que por sua vez, os acessará via navegador web pela URL, ou dispositivo móvel. Válido lembrar que ele funciona em HTML 5

No artigo de hoje, vou mostrar um pouco como interagir, no navegador, com um dashboard de exemplo que acompanha o produto Tableau Server. O nome do dashboard é Executive Overview - Profitability, e falaremos desde as interações mais simples até as mais avançadas e interessantes. Segue a imagem do dashboard abaixo:

Recursos abordados:

• filtros
• legendas
• gráficos
• BI colaborativo
• mapa interativo
• custom views
• agendamento de envio de dashboard por email

As primeiras interações que você pode fazer são através dos filtros, no canto esquerdo da tela. O primeiro tipo abaixo, Order Date, através de um intervalo de data, mude os ponteiros para alterar o range:

O segundo tipo, Region, é uma lista de seleção única. Você pode escolher apenas um valor, ou todos eles, escolhendo a opção (All). No Tableau Desktop é possível desativar esta opção. 

O terceiro filtro State, e terceiro tipo de filtro, é a lista de seleção múltipla. Você pode selecionar mais de uma opção ou todas. Pode fazer combinação de seleções através de cada checkbox.

Assim falamos dos filtros. No Tableau Desktop, é possível configurar os filtros em cascata, um filtrando o outro. 
Agora vamos falar das legendas. Você pode torná-las interativas clicando no pequeno ícone de lápis (ou seria uma caneta?) no canto da legenda:

Agora, clicando na primeira das opções da legenda, ela irá filtrar todos os gráficos de área. Vamos primeiro filtrar todos os pedidos lucrativos (Profitable). Repare que só a parte azul dos gráficos de área são exibidas.

Agora, vamos ver somente os pedidos não lucrativos (Unprofitable), clicando na outra opção da legenda. Somente a área vermelha dos gráficos de área irão aparecer:

Outra interação que você pode fazer é clicando em um gráfico, no caso o mapa, todos os outros refletirão o filtro daquela localidade. Para desfazer a seleção clique na localidade novamente:

Falando em mapa, observe no canto esquerdo do mapa alguns botões que permitem algumas interações adicionais. Além do zoom com os símbolos de "+" e "-", o primeiro ícone é uma lupa, que permite você pesquisar uma localidade. Vamos imaginar que eu não sei onde fica Vermont. Basta clicar na lupa, digitar o nome da localidade e pressionar enter, o mapa automaticamente trará na tela a localidade:

Clicando no ícone da "casinha", você volta a visão original. Abaixo deste ícone, tem um ícone de uma seta, que permite você escolher a maneira que deseja fazer múltiplas seleções no mapa:

Escolha a seleção retangular, e selecione uma área do gráfico contendo várias localidades conforme abaixo:

Solte o botão do mouse e veja que os gráficos abaixo refletirão todas essas localidades selecionadas. Para desfazer basta clicar em uma localidade:

Por fim, a cereja do bolo, as 3 interações que mais gosto. Embaixo do dashboard, é possível adicionar comentários e registrar informações sobre o dashboard, o tornando colaborativo, Sendo que somente você e o administrador pode apagar suas mensagens:

Outra que eu gosto muito pela simplicidade é o envio de emails. Suponha que você deseja receber este dashboard, seja diariamente, semanalmente ou quando desejar. Enquanto outras ferramentas exigem maior complexidade, no Tableau Server existe o ícone de um "envelopinho" no canto superior direito, basta você clicar, seu email já irá aparecer, e você escolhe a periodicidade que deseja receber. A lista de periodicidades é configurada pelo usuário administrador, de forma muito simples. Válido lembrar, que para este recurso funcionar, é necessário o administrador da ferramenta deixar configurado o servidor SMTP e e-mail no utilitário de configuração da ferramenta.

Por fim, a última interação que gostaria de mostrar é a opção de salvar seleções. Suponha que você faça uma determinada seleção com frequência, um conjunto de filtros aplicados ou observou algo interessante em uma análise. É um subconjunto de informações apresentadas pelo seu dashboard. Poderia ser a seleção abaixo:

Para salvar esta seleção, logo acima do título, existe uma opção com o título Remember my changes. Clique nessa opção e atribua um nome para sua visualização.

Sua seleção ficará salva em My Custom Views. Também ficará salva somente no seu usuário e você poderá excluir. Poderá ter várias visualizações salvas.
Agora suponha que você saiu do Tableau, foi embora, passou o final de semana, voltou na segunda-feira e logou novamente na ferramenta e acessou o dashboard:

Clique em Remember My Changes e lá estará sua visão:

Clique nela e como mágica sua seleção aparecerá:

E assim terminamos o artigo.

Espero que tenham gostado. Um Abraço.

Fabio Idalgo