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!

Anúncios

4 comentários sobre “Excel vs. MDX

  1. Opa ! Não conhecia esse tal de MDX !
    Achei bem interessante.
    E concordo em um momento o Excel não suporta o volume.

    Obrigado.

  2. Oi Fabio, muito legal a comparação.

    Ainda conheço pouco, mas me surpreendi com a flexibilidade de utilizar consultas MDX. Mais ainda quando trabalhamos com membros calculados no Mondrian. Seria legal falar mais sobre o assunto. Você consegue abordar um conceito que acho difícil de assimilar de forma muito didática.

    Grande abraço

    1. Obrigado, Wellington. Eu me esforço por ser didático, é bom saber que estou conseguindo sê-lo. MDX é muito poderoso e sofisticado, mas com isso sua curva de aprendizado é bem íngreme. Ter feito este primeiro artigo me deu apoio para tentar discussões mais elaboradas. Fique de olho! ;-)

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s