Excel vs. MDX

Não me entenda mal: não quero começar uma guerra santa entre usuários de Excel e de MDX, até porque eu provavelmente pertenceria ao exército santo do Excel.

Mas a verdade é que, quando alguém precisa tabular dados, a primeira escolha é sempre o Excel. Ele é simples e fácil de usar, e dá resultados mais ou menos intuitivos.

Neste post eu vou mostrar um caso típico de uso do Excel – comparar valores de dois períodos – e como fazer o mesmo usando MDX. Daí eu vou tirar algumas conclusões e, espero, te convencer da importância de investir no estudo de MDX.

Vamos lá.

O Problema

Eu recebi um e-mail de um ex-aluno com o seguinte problema:


(No meu cubo) tenho uma coluna de valor faturado e, estou fazendo um comparativo entre os anos de 2014 e 2015, por empresa, pois somos um grupo de empresas. A minha métrica é o valor faturado. Então, como proceder se eu tiver que dividir um valor pelo o outro?


Quantas vezes não precisamos fazer isso? Comparar um ano com outro, uma linha de produtos com outra, faturamento em estado com outro… Qual é a primeira idéia que nos ocorre para tratar esse problema?

Montar uma planilha eletrônica – um Excel. É ou não é?

Vamos fazer isso aqui: vamos pegar o problema que meu ex-aluno trouxe e resolvê-lo, primeiro no Excel e depois como uma consulta MDX.

Para facilitar a compreensão do raciocínio, eu vou alterar ligeiramente o problema trazido pelo aluno. Ao invés de considerarmos os dados da empresa dele, que são sigilosos e relativamente complexos, vamos usar o cubo de vendas da SteelWheels, que é a empresa usada na demonstração do Pentaho BA Server.

Reformulando o problema com a SteelWheels, fica:


Estou fazendo um comparativo entre os anos de 2004 e 2005, por linha de produto. A minha métrica é o valor vendido (Sales). Como proceder se eu tiver que dividir um valor (Sales em 2005) pelo o outro (Sales em 2004) para cada linha de produto?


Resolvendo com Excel

Para resolver esse problema usando uma planilha eletrônica (nome genérico do Excel, que serve para designar não apenas o Excel, mas o Calc, do LibreOffice, o Lotus 1-2-3 etc.), precisamos primeiro extrair os dados para um arquivo, que vai ser importado pelo Excel e só então aparecer como uma planilha. Normalmente, para bancos de dados relacionais, fazemos isso exportando para CSV o resultado de uma consulta SQL, e depois importando esse CSV para dentro da planilha eletrônica.

E para fazer isso nós precisamos:

  1. De uma ferramenta que permita rodar SQL contra a base e exportar o resultado para arquivos CSV;
  2. Conhecer a base de dados, entendendo que tabelas possuem os dados que queremos, e como elas se relacionam entre si; e finalmente,
  3. Saber SQL o bastante para escrever uma consulta válida.

Vamos começar por entender os dados. Eis abaixo o esquema do banco, construído no Power*Architect:

Esquema que alimenta o cubo SteelWheels Sales.
Esquema que alimenta o cubo SteelWheels Sales.

Montei esse diagrama fazendo engenharia reversa no esquema Mondrian (que por sua vez foi retirado do servidor Pentaho) e na base de dados.

Como queremos os dados das vendas feitas por ano, por linha de produto nos interessam as tabelas PRODUCTS, ORDERFACT e DIM_TIME. O SQL que retorna a lista de valores vendidos por linha de produto, por ano é:

SELECT
  PRODUCTS.PRODUCTLINE AS PRODUCTS_PRODUCTLINE,
  DIM_TIME.YEAR_ID AS DIM_TIME_YEAR_ID,
  SUM(ORDERFACT.TOTALPRICE) AS SALES
FROM PUBLIC.PRODUCTS PRODUCTS
INNER JOIN PUBLIC.ORDERFACT ORDERFACT
  ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
INNER JOIN PUBLIC.DIM_TIME DIM_TIME
  ON ORDERFACT.TIME_ID = DIM_TIME.TIME_ID
GROUP BY PRODUCTS_PRODUCTLINE,
         DIM_TIME_YEAR_ID
ORDER BY DIM_TIME.YEAR_ID ASC,
PRODUCTS.PRODUCTLINE ASC`

(Eu não sou esse mago do SQL. Eu consegui essa expressão usando o SQLeonardo. E não, não existe o SheldonQL.)

O SQLeonardo já seria o bastante para exportar os dados, mas eu resolvi usar a grande dica do Rômulo, e montei uma conexão com o banco usando uma interface gráfica padrão do HSQLDB:

Conexão do cliente HSQLDB com o SampleData.
Conexão do cliente HSQLDB com o SampleData.

O HSQLDB é o banco de dados portátil, usado pelo Pentaho BA Server.


Depois da conexão feita, rodamos a consulta acima:

Resultado da consulta que traz o dados que desejamos.
Resultado da consulta que traz o dados que desejamos.

E salvamos esse resultado em formato texto:

Arquivo texto resultado do comando 'File -> Save Result'.
Arquivo texto resultado do comando ‘File -> Save Result’.

Depois de um pouco de escovação, finalmente aparece como uma planilha eletrônica:

Colocando os anos lado-a-lado na planilha.
Colocando os anos lado-a-lado na planilha.

Pronto, podemos nos dedicar a montar a resposta ao problema. Primeiro, rearranjamos a planilha para colocar os anos lado-a-lado:

Colocando os anos lado-a-lado na planilha.
Colocando os anos lado-a-lado na planilha.

Agora ficou fácil: basta calcular a razão (=divisão) entre quaisquer colunas e inserir o resultado em uma nova coluna. Do enunciado do problema sabemos que queremos a divisão de 2005 por 2004. Assim, apaguei a coluna de 2003 e coloquei uma nova coluna, 2005/2004, contendo a fórmula que divide uma pela outra. Formatei como porcentagem e finalmente temos (com destaque para a fórmula):

Calculando 2005 dividido por 2004, como uma porcentagem.
Calculando 2005 dividido por 2004, como uma porcentagem.

Voilá! Problema resolvido!

MultiDimensional eXpressions

MDX é a linguagem que a Microsoft criou para seu produto OLAP, o MS SQL Server Analysis Services, ou SSAS, como é conhecido. O SSAS é uma base multidimensional real, o que significa que ele guarda cada célula de um cubo OLAP como um registro físico, assim como um banco relacional guarda registros em disco. Em comparação, o Mondrian é um servidor OLAP, que monta o cubo em memória a partir de um banco de dados relacional tradicional, conforme processa comandos MDX. Em comum, SSAS e Mondrian têm exatamente isso: a linguagem MDX.

A meta da Microsoft com o SSAS era popularizar BI, e nisso o MDX era instrumental. A idéia por trás do MDX era viabilizar justamente o tipo de manipulação que fazemos com o outro produto da MS – o Excel – mas para grandes e complexas massas de dados. O livro Fast Track to MDX traz um histórico, simples mas muito valioso, de como o SSAS (e o MDX) nasceu e cresceu.

Resolvendo com MDX

Para resolver esse mesmo problema com MDX usamos um Pentaho BA Server (eu usei a versão 5.4, mas funciona com qualquer uma – mesmo!) Depois de subir o servidor, acessamos o endereço http://localhost:8080, fazemos login com usuário admin e senha password e criamos uma nova visão OLAP:

Abrindo uma nova visão OLAP do cubo de vendas SteelWheels.
Abrindo uma nova visão OLAP do cubo de vendas SteelWheels.

Assim que você seleciona o esquema SteelWheels e o cubo SteelWheelsSales e clica em Ok, a visão inicial do cubo se abre:

Visão inicial do cubo de vendas da SteelWheels.
Visão inicial do cubo de vendas da SteelWheels.

Usando o primeiro ícone do jPivot, Cube Navigator, e mudando o tipo de drill para Drill Replace (ícone de setas vermelhas), chegamos a uma visão análoga à da planilha:

Cubo rearranjado para mostrar a mesma visão de planilha.
Cubo rearranjado para mostrar a mesma visão de planilha.

Neste ponto, quando usamos o Excel, criamos uma nova coluna e colocamos a fórmula nela. Vamos fazer exatamente a mesma coisa aqui, mas com MDX.

Até agora manuseamos os dados em uma interface gráfica. Vamos ver o que existe de MDX por trás disso. Clicando-se no botão MDX do jPivot, vemos a consulta que construímos enquanto lapidávamos o cubo:

SELECT NON EMPTY Crossjoin( {[Measures].[Sales]},
              [Time].[All Years].Children ) ON COLUMNS,
NON EMPTY [Product].[All Products].Children ON ROWS
FROM [SteelWheelsSales]

Em MDX-zês, uma “nova coluna” equivale a uma métrica calculada (bom, tecnicamente falando, é um membro calculado, mas como é um membro do conjunto de métricas, fica sendo uma métrica calculada mesmo.) Essa métrica calculada pode ser definida, em português, assim:

    COM O NOME "Razao_2005_2004" USE A FORMULA ([2005,Categoria]/[2004,Categoria]), <OPÇÕES>

O truque todo está na fórmula: pense no cubo OLAP como uma mega-planilha, na qual toda métrica pode ser referenciada com um conjunto de coordenadas, exatamente como em uma planilha Excel. A diferença é que as coordenadas não são colunas e linhas, como na fórmula da figura anterior, mas sim membros dos conjuntos das dimensões!


Outro detalhe importante: se você não explicitar uma dimensão, assume-se que a fórmula vale em todas as “coordenadas” daquela dimensão. Ainda apelando para a analogia do Excel, é como fazer referência à planilha: a menos que você indique uma célula em outra planilha, a fórmula Excel vai assumir que a coluna/linha indicada é na mesma planilha (o “membro atual”, do inglês current member – essa noção é importantíssima!!), e se você copiar essa fórmula para outra planilha, o mesmo vai continuar valendo.

Trocando o Português por MDX castiço, a fórmula fica:

WITH MEMBER [Measures].[Razao_2005_2004] AS (
  ([Measures].[Sales],[Time].[2005],[Product].CurrentMember) / 
  ([Measures].[Sales],[Time].[2004],[Product].CurrentMember)
                                   ), format_string = "#.00%"

O format_string serve para que os valores calculados apareçam como porcentagens. Sem ele ali, os valores seguiriam a formatação da métrica original.

Agora vamos colocar essa fórmula no nosso MDX anterior:

WITH MEMBER [Measures].[Razao_2005_2004] AS (
  ([Measures].[Sales],[Time].[2005],[Product].CurrentMember) / 
  ([Measures].[Sales],[Time].[2004],[Product].CurrentMember)
                                   ), format_string = "#.00%"
SELECT NON EMPTY Crossjoin({[Measures].[Sales]},
               [Time].[All Years].Children) ON COLUMNS,
NON EMPTY [Product].[All Products].Children ON ROWS
FROM [SteelWheelsSales]

Se você testar essa consulta no jPivot, copiando-a e colando-a, vai ver que nada de novo aparece, mas também não dá erro. Isso acontece porque, apesar de termos definido a nova métrica, ela não está sendo usada na consulta. É preciso incluir a nova métrica na consulta para que ela apareça no cubo. Fazemos isso alterando o termo logo depois de SELECT NON EMPTY Crossjoin:

WITH MEMBER [Measures].[Razao_2005_2004] AS (
  ([Measures].[Sales],[Time].[2005],[Product].CurrentMember) / 
  ([Measures].[Sales],[Time].[2004],[Product].CurrentMember)
                                   ), format_string = "#.00%"
SELECT NON EMPTY Crossjoin(
          {[Measures].[Sales],[Measures].[Razao_2005_2004]},
           [Time].[All Years].Children ) ON COLUMNS,
NON EMPTY [Product].[All Products].Children ON ROWS
FROM [SteelWheelsSales]

Notou que o nome da nova métrica, definida no WITH MEMBER, agora aparece na consulta? Se tudo deu certo para você, seu cubo agora deve se parecer com este aqui:

Cubo com a nova métrica calculada.
Cubo com a nova métrica calculada.

Algumas coisas ficaram esquisitas: a coluna se repetiu, com o mesmo valor, para os três anos. Essa visualização é estranha, e não ajuda muito. Se você pensar um pouco, verá que a fórmula que calculamos opera sempre apenas sobre 2005 e 2004. Logo, não há sentido em mostrar o ano


Ao remover a dimensão Ano da grade OLAP, a métrica Sales passará a ser agregada para todos os anos, e não veremos mais as duas colunas para 2004 e 2005. Precisamos aprender um pouco mais de MDX para saber como mostrar a métrica Sales em 2004, em 2005 e, em uma terceira coluna, a métrica calculada – mas aí já é outra história. Entretanto, se você quiser procurar, a solução para este caso passa por NAMED SETS (NS).


Grade agora mostra só a métrica calculada.
Grade agora mostra só a métrica calculada.

Tudo isso parece um pouco confuso no início, mas eu posso dizer algumas coisas para ajudar:

  • É uma fórmula que resolve esse o problema atual, e nenhum outro. Em outras palavras, não é uma solução genérica e geral, mas sim uma bem específica;
  • Pense sempre como uma planilha Excel: para calcular a metade do valor da célula F4, você coloca “=F4/2” na célula G4. Certo? Em MDX é a mesma coisa, mas uma “célula” é o cruzamento das dimensões e uma ou mais métricas. Para calcular uma nova relação, construímos uma nova coluna, e assim por diante;
  • Logo, a solução de outros problemas demandam outras consultas MDX. Preparamos uma nova solução sempre que tivermos uma nova necessidade.

Conclusão

Meu modesto objetivo era mostrar como uma conta feita em planilha pode ser replicada com MDX. Ou seja, eu queria mostrar que MDX pode ser encarado como uma outra forma de se manusear planilhas de dados. Por favor deixe sua opinião nos comentários: eu consegui?

Se você pensar um pouco, vai ver a mesma situação acontece com bancos relacionais e SQL: em busca de uma resposta específica, podemos escrever um programa (Java, Kettle, PHP etc.) que itera sobre as linhas de uma tabela – para calcular uma média por exemplo, ou podemos tentar escrever uma consulta SQL. Em geral, a consulta SQL vai ser mais enxuta, poderosa, rápida e flexível, porque ela foi pensada para fazer bem certos tipos de trabalho.

Com MDX temos a mesma situação: podemos montar uma planilha, ou podemos escrever uma consulta MDX sobre um cubo OLAP. Em geral, MDX vai ser mais enxuto, poderoso, rápido e flexível, porque ele foi pensado para resolver problemas analíticos.

Há algumas outras coisas dignas de nota em nossa breve aventura com planilhas e OLAP:

  • Ambos os métodos partem da existência de um banco de dados pronto para consulta. Criar esse banco já é um trabalho considerável, dependendo da empresa e das complexidades (negócios, dados etc.) envolvidas;
  • Uma vez que você consiga montar esse banco, ainda precisa escrever o SQL para poder chegar à planilha;
  • Se você optar por montar um cubo OLAP, precisa mapear o banco com um esquema Mondrian, e depois construir o MDX que resolve seu problema.

Ou seja, por qualquer caminho que peguemos (MDX ou Excel), sempre vamos precisar “escrever” algo (um SQL ou um MDX), mesmo que para isso usemos uma ferramenta visual.

Bom, se pelos dois caminhos temos o mesmo trabalho, se não maior no caminho MDX, porque então vale a pena usar MDX? Porque não ficamos só com Excel?

Resposta: volume de dados e complexidade.

Veja como tínhamos poucas linhas e poucas colunas para manusear em uma planilha eletrônica. Conforme o volume de dados e a complexidade desse volume aumenta, cresce a complexidade das operações necessárias para montar a planilha. Nosso exemplo usa apenas anos e linha de produto. Como ficaria para Mês/Ano e Produto com Cliente? Com duas dimensões conseguimos “ver” a planilha numa boa, mas e com três, quatro, cinco?…

Mais ainda: para cada visão em planilha, o comando SQL precisa mudar para tratar das agregações nos níveis corretos!

O livro OLAP Solutions desenvolve esse argumento melhor, mas o ponto que eu quero destacar é que planilhas podem ser práticas e muito úteis apenas até certo ponto! A partir daí, as operações para montar e explorar as planilhas ficam sobejamente complexas.

É quando MDX passa a ser mais interessante que Excel.

Até a próxima!

Testando o Vertica

Já há alguns anos eu quero testar um banco de dados colunar. Desde o Pentaho Day 2014 eu fiquei muito curioso para brincar com o HP Vertica, mas não tinha tido o tempo (nem um banco com volume) suficiente para todo o trabalho que isso implica.

No final de 2014 eu consegui instalar uma máquina virtual com o Vertica 7.1.1 (em um Ubuntu Server 14.04.1.) Construí uma dúzia de transformações que copiaram todos os cubos de um DW em Postgres para esse Vertica. Configurei um BI Server 5.1 com as duas fontes de dados, e um esquema Mondrian para cada uma dessas fontes. Ontem em consegui fazer e tabular um experimento simples: usando o jPivot, abri o maior dos cubos (14.095.514 de linhas) e fiz uma exploração simples: abri e fechei cada uma das seis dimensões principais. Todas elas tinham entre um e três membros no nível hierárquico superior, exceto por uma dimensão data, que tinha 11 membros no nível do ano.

O Experimento

Fiz essas navegações logo após o boot do servidor, com cache vazio, e com um esquema (=banco) de cada vez. Capturei o log MDX e SQL do Mondrian para cada caso. Elas foram tão idênticas que foi possível comparar até mesmo o SQL gerado entre os dois experimentos. Como o Vertica é um Postgres colunar, o SQL era idêntico até a última vírgula, e isso facilitou a comparação.

Veja, eu não estava fazendo um estudo milimetricamente planejado. Eu só queria obter um sentimento da relação de performance entre as duas tecnologias. Logo, o resultado vai refletir o preparo do experimento. A informação será mínima, binária: em plataformas de hardware parecidas, qual base é mais rápida?

Tempo total para fazer a mesma coisa.
Tempo total para fazer a mesma coisa.

É o Vertica, sem sombra de dúvida. A máquina virtual do Vertica tinha 2GB de RAM e uma CPU – um quarto do meu i7 2.4GHz. A máquina do Postgres é a minha máquina real, com 16GB de RAM além de toda a CPU disponível. A máquina virtual estava desligada, de modo que minha CPU não estava particionada no momento do teste do Postgres.

O gráfico anterior mostra o tempo total para repetir a mesma operação com o mesmo cubo, usando bases diferentes. Já o gráfico abaixo compara cada uma das operações – que por acaso são 15 – usando uma escala logarítmica no eixo Y.

Tempo por operação nas duas tecnologias.
Tempo por operação nas duas tecnologias.

Curiosamente, e talvez até previsivelmente, o Vertica teve um desempenho uniformemente melhor que o Postgres em todas as operações que levavam mais tempo, mas perdeu feio nas operações M e N, que duraram menos de 50 ms. Destas operações, a M é o pior resultado do Vertica: 42 ms a 0 ms para o Postgres. Ou seja, uma operação que durou mais de 40 ms no Vertica foi tão rápida no Postgres que o log do Mondrian não conseguiu medir.

Lendo a documentação do Vertica eu vi um tópico que discute exatamente isso: consultas menores tendem a ter um overhead proporcionalmente maior que bancos relacionais.

Legal.

Em compensação, o Vertica foi mais rápido em tudo que levou mais de 50 ms. Em alguns casos, como o O, ele chega a ter mais de uma ordem de grandeza de vantagem – 22 vezes mais rápido no caso da operação O.

A Conclusão…

… é óbvia: o Vertica parece ser muito interessante para exploração OLAP de fatos com milhões de linhas. Sendo um cientista, eu estou perfeitamente ciente do risco que seria generalizar essa conclusão para um “logo ele será bom para tudo”. Existe um caso de uso no qual eu estou particularmente interessado, um relatório (PRD) tabular construído sobre esse mesmo cubo. Meus próximos testes vão tentar determinar se é uma vantagem usar Vertica ou não.

E eu aposto que é.

Feliz Ano Novo!!

Cruzando Duas Hierarquias da Mesma Dimensão

Em 7/2/14, Camila Botelho postou uma curiosa pergunta:

Camila pergunta sobre análises em duas hierarquias da mesma dimensão.
Camila pergunta sobre análises em duas hierarquias da mesma dimensão.

Eu respondi com curiosidade:

Eu não sei... ainda! :-)
Eu não sei… ainda! :-)

O teste era simples: primeiro, criar a hierarquia gênero no esquema e, segundo, espetar cliente de novo no cubo. O resultado é este aqui:

Nova hierarquia no cubo Beltrano, mostrando o Cliente espetado duas vezes.
Nova hierarquia no cubo Beltrano, mostrando o Cliente espetado duas vezes.

Até agora, tudo lógico e razoável. Teste de fogo: dá para criar uma análise de Gênero do Cliente por Estado do Cliente? Resposta:

Duas hierarquias cruzadas entre si no Saiku.
Duas hierarquias cruzadas entre si no Saiku.

Sim! A visão da figura anterior está filtrada por tipo de cliente (exibe apenas PF, já que PJ não possui gênero), e em cada eixo (coluna e linha) eu tenho um atributo de cara hierarquia da dimensão Cliente. O Saiku não expôs o nome da dimensão setada com Dimension Usage (Cliente H1 e Cliente H2), mas oferece o cliente duas vezes.

A pergunta que não quer calar é “peraí! A cada hierarquia que eu queira cruzar, preciso espetar a dimensão no cubo de novo?” Bom, se você usar o Pentaho Analyzer (EE), não. Caso contrário, por enquanto, sim. Eventualmente, consultas MDX não possuem essa limitação – indicando que é um ponto a ser melhorado no cliente OLAP, e não no Mondrian:

SELECT
NON EMPTY Hierarchize(Union(CrossJoin({[Cliente H2.Genero].[F]}, {[Measures].[Quantidade]}),
                             CrossJoin({[Cliente H2.Genero].[M]}, {[Measures].[Quantidade]}))) ON COLUMNS,
NON EMPTY {Hierarchize({[Cliente H1].[Estado].Members})} ON ROWS
FROM [Pedidos]

É isso. Respondido, Camila?

O que é Business Analytics

Na minha cabeça, todo bom trabalho começa com um bom entendimento do problema. Se isso sair errado, todo restante fica comprometido.

Comecei a ler o Mondrian in Action. Passei o prefácio, introdução, blá, blá, blá e cheguei no capítulo 1. Primeira sentença:

Business analytics is a process for gaining insight into business performance based on the analysis of historical data.

Caraca, isso é gênio puro! Eu sonho em um dia poder começar um trabalho escrevendo uma coisa tão simples, concisa e precisa! Nunca mais eu vou gastar minutos explicando o qué análise de negócios (ainda não achei uma boa tradução para Business Analytics). A partir de hoje eu só vou dizer:

Análises de Negócios (BA) é o processo de obter percepções acerca do estado dos negócios baseado na análise de dados históricos.

Mondrian in Action, 2013

De uma só tacada ele envolve o rendimento e o estado (performance) dos negócios com temporalidade (dados históricos) e a busca de conhecimento (análises.)

Eu não sabia o que era BA. Agora eu sei.

Mondrian in Action

Como dizem em inglês, I’m a sucker for new books. Eu recebi um código promocional da Pentaho para compra do novo livro sobre Mondrian, o Mondrian in Action e caí dentro!

Capa do livro
Capa do livro

A entrevista dos autores foi decisiva para comprá-lo sem avaliá-lo antes:

2. What is the main goal if the book? What do you aim to bring across?

Bill:  I wanted to create a single resource that people can turn to answer their questions about Mondrian.  This includes understanding how it is used by organizations to analyze their business data, how it is used by various tools, such as Pentaho Analyzer and Reporting, and how to integrate Mondrian into a user’s application.

Nick: The goal of the book was to bring together practical knowledge, from 3 of the top practitioners who have worked with Mondrian at over a hundred customers.  The mix of skills between the 3 authors meant we got diverse topics to make a comprehensive and practical book.  Julian is the code expert, Bill knows the integration/Pentaho aspects, and I have extensive customer modeling/configuration experience.  Readers should benefit from our varied experience with Mondrian!

5. What’s next?

(…)Julian: First of all, I am working on the next Mondrian release. There are lots of new features and architectural improvements, most of which are described in the book. The whole Mondrian dev team is really excited to get it to market.

Agora, ler! Como eu comprei a versão eletrônica, ela já está aqui – tanto que a imagem da capa foi tirada diretamente da minha cópia, na minha máquina. Postarei minha opinião assim que formá-la, mas dificilmente vai ser alguma coisa que não “muito bom!”