Data Mart e Analysis Services: entendendo a linguagem de consultas MDX

Estrutura do MDX - Apresentação

Meu nome é Victorino Vila e vamos começar mais um curso. Acompanhando a implementação do projeto de Business Intelligence na AtacaDez. Projeto esse que é liderado pela Paula.

Audiodescrição: Victorino é uma pessoa de pele clara e olhos escuros. Seus cabelos são curtos, lisos e grisalhos. Usa barba e bigode e está vestindo uma camiseta preta.

Agora temos na empresa AtacaDez, desde o Data Warehouse (Armazém de Dados) e o Data Lake (Lago de Dados) projetados e carregados, bem como o Data Mart criado e também carregado.

Próximos passos deste curso

Precisamos agora saber como vamos consultar dados lá no Data Mart. Isso vai ser feito através do MDX.

O MDX é a linguagem de consulta para Data Mart OLAP. Alguns podem dizer que ele é mais ou menos semelhante ao SQL, mas eu pessoalmente acho, exceto na sua estrutura básica, completamente diferente de um SQL.

Em um SQL, você supõe que o banco de dados é relacional e ele possui tabelas, campos, relacionamentos e outras estruturas especiais dos bancos de dados relacionais. Já no Data Mart OLAP, nós temos apenas dimensões, hierarquias, níveis, atributos e indicadores.

Logo, o conceito de selecionar algo numa base OLAP tem muito mais ligação com aquela estrutura matricial onde todo mundo cruza com todo mundo, que é bem diferente de uma estrutura de um banco de dados relacional onde usamos o SQL.

O que vamos aprender?

Neste curso você vai ter o primeiro contato com o MDX e vamos já começar esse curso com um grande desafio. A construção de um relatório de Pareto para a empresa AtacaDez.

Vamos lá?

Até o próximo vídeo!

Estrutura do MDX - Análise de Pareto

Vamos iniciar este curso falando um pouco sobre a análise de Pareto.

Análise de Pareto

Nas empresas, sabemos que tempo é dinheiro. Logo, quando vamos analisar um problema na empresa, é importante que investiguemos o motivo principal daquele problema que precisamos resolver.

Por exemplo, se precisarmos melhorar os custos da empresa, é importante que atuemos onde temos os maiores gastos. Se economizarmos onde gastamos mais, reduziremos nossos custos como um todo. Se precisarmos, por exemplo, manter nossa lucratividade alta, precisamos saber onde lucramos menos para podermos atuar e aumentar essa lucratividade.

Como fazemos esse tipo de análise? Utilizamos, entre várias outras maneiras, um relatório chamado relatório de Pareto. Este é baseado em uma regra chamada regra do 80/20. Essa regra foi descoberta por um matemático chamado Vilfredo Pareto. Por isso, nossa análise de Pareto tem esse nome.

O Vilfredo é considerado um dos pais da estatística moderna. Ele observou que 80% das terras italianas estavam nas mãos de apenas 20% da população. E essa relação 80/20 ele viu que ocorria em muitos outros contextos. Por exemplo, numa plantação de peras, 80% das frutas colhidas vinham de apenas 20% das árvores. Essa relação acontecia sempre que se analisava algo estatisticamente com um espaço amostral muito grande.

Como podemos aplicar isso em Business Intelligence? Nos problemas que mencionamos, se quisermos diminuir nossos custos, faremos uma análise de 80/20 para descobrir onde se concentram os 80% dos custos e atuaremos nesses departamentos. Ou, se fizermos uma análise de 80/20 da lucratividade, descobriremos quais são os clientes que concentram apenas 20% dos lucros e trabalharemos para melhorar essa margem.

Atenção: a regra do 80/20 não é exata, é uma estatística. Dependendo do caso, podemos encontrar uma relação 90/10, 70/30, 60/40. Dependerá da distribuição do indicador dentro dos elementos que geram o indicador.

Construindo a análise de Pareto

Abaixo temos uma análise de Pareto ou análise de 80/20:

Como construímos nossa análise de Pareto? Primeiramente, selecionamos a dimensão que iremos analisar. Neste momento, estamos focando em cidades. Na segunda coluna, especificamos um ranking para o indicador que desejamos analisar.

No nosso caso, estamos analisando o faturamento. Apresentamos as cidades, começando pela que teve o maior volume de vendas até a que teve o menor. Começamos com Goiás, que registrou um faturamento de R$ 2,6 milhões, e descemos até a última cidade, Salvador, que teve vendas de apenas R$ 350 mil em um período específico.

A outra coluna denominada "% Vendas" representa o percentual de vendas, que é o indicador apresentado na primeira coluna, o valor 13,33% representa o percentual de vendas em Goiás (em relação a todas as outras cidades).

Já o valor 26,54% representa a venda acumulativa dos contribuintes desse indicador (Goiás e Belo Horizonte). A coluna "% Cidades" é um percentual acumulativo dos geradores desse indicador, no caso, um percentual acumulativo do número de cidades.

Se tivermos 13 cidades no relatório, uma cidade, como Goiás, representa 7,69% de todas as cidades no relatório, sendo um sobre treze avos. Ao analisar o valor 15,38% na coluna "% Cidades" estamos informando que duas cidades (Goiás e BH) representam 15,38% de todas as cidades.

Com isso, entendemos o que cada coluna representa.

Ao analisar onde, na segunda coluna, temos o valor mais próximo de 80, observamos que 78,60% está dentro de 53,85% das nossas cidades. Esse grupo de cidades, de Goiás ao Rio de Janeiro, é onde mais realizamos vendas.

Por outro lado, o grupo de Porto Alegre a Salvador é onde menos vendemos. Se quisermos aprimorar nossas vendas, podemos realizar uma campanha de marketing direcionada especificamente para esse conjunto de cidades que está fora da faixa do 80/20%.

A representação gráfica da análise de Pareto ou 80/20% também é possível, mas não é o foco deste curso. Nosso objetivo é obter esse relatório do banco de dados multidimensional, do DataMart. Geralmente essa análise gráfica da análise de Pareto ou 80/20 é denominada curva ABC. Mas isso não será assunto para este curso.

Como obter do banco de dados multidimensional essa análise? Realizando uma consulta ao DataMart. Para visualizar essa consulta, é necessário acessar o Management Studio, analisar o Analysis Services e examinar o banco de dados multidimensional que construímos.

Então, vamos seguir essa abordagem: vamos ao Analysis Services e examinar esta consulta. No Management Studio, se analisarmos abaixo, temos a mesma análise que foi apresentada no slide. Ela foi gerada, e posso reproduzi-la clicando no botão "Executar" na parte superior.

A tabela foi transcrita acima

A consulta é formada por esta sentença.

A sentença abaixo foi parcialmente transcrita.

WITH
SET [CLIENTES] AS
'ORDER([Cliente]. [Hierarquia Geográfica].[Nível Cidade], [Measures].[Valor Da Venda], BDESC)'
MEMBER [Measures].[Posição no Rank] as
'RANK ([Cliente]. [Hierarquia Geográfica].Currentmember, [CLIENTES])', FORMAT_STRING = "#;#;"
MEMBER [Measures]. [Faturamento de Vendas] as
[Measures].[Valor Da Venda]', FORMAT_STRING = "#####.00"
MEMBER [Measures].[% Vendas] as
[Measures].[Faturamento de Vendas]/([Measures]. [Faturamento de Vendas], [Cliente]. 

…

Esta é a consulta que estamos executando na base de dados multidimensional. Para aqueles que não estão familiarizados, pode parecer diferente do SQL, mas isso é chamado MDX. Para realizar consultas no banco de dados multidimensional, não usamos SQL, utilizamos MDX. Este MDX é o responsável por gerar a análise de Pareto.

Conclusão e Próximos Passos

Nosso objetivo neste curso será aprender MDX e construir o relatório apresentado. É um desafio considerável. Nosso primeiro passo para começar a compreender o MDX é entender a estrutura básica de um MDX.

No entanto, isso será abordado apenas no próximo vídeo. Agradecemos a todos, um abraço.

Até breve!

Estrutura do MDX - Estrutura do MDX

No final da última aula, vimos uma consulta à base de dados multidimensional de vendas da AtacaDez, que exibia o resultado das vendas por cidade através da análise de Pareto. Diferentemente da base multidimensional, não utilizamos SQL nem outra linguagem de programação específica da Microsoft, mas sim o MDX (Multidimensional Expressions).

Nesta aula, vamos entender o que é o MDX.

Multi Dimensional Expressions (MDX)

SELECT
[ { [Cliente].[Estado].&[Goias]} ] ON Rows
,[ { [Measures].[Valor Da Venda]} ] ON
Columns
From [DW ATACADEZ]
Where ([Produto].[Setor].&[Bebidas]]

Acima, temos uma consulta em MDX bastante simples, que obtém as vendas do Estado de Goiás apenas para produtos do setor de bebidas.

Analisando a expressão, podemos começar a entender o que a consulta realiza. O MDX possui uma estrutura básica, a qual vamos compreender agora.

Estrutura básica do MDX

Primeiro, o MDX é construído sobre uma estrutura definida por três palavras-chave: SELECT, FROM e WHERE.

Quem está familiarizado com SQL reconhecerá essas palavras, e no MDX o significado é o mesmo. O SELECT define o que será visto, o FROM é o nome da base de dados e o WHERE é o filtro da análise. No entanto, ao contrário do SQL e das bases de dados relacionais, onde os resultados das consultas são apresentados em forma de tabela, o banco de dados multidimensional permite observar os dados de diversas maneiras.

Mencionamos em cursos anteriores sobre como o OLAP (Online Analytical Processing) estrutura os dados, lembrando que no OLAP os dados ficam em uma estrutura em forma de matriz n-dimensional. Com três dimensões temos um cubo; com mais de três, um poliedro espacial.

As vantagens de armazenar dados em uma estrutura multidimensional incluem performance, conforme discutido anteriormente, e a liberdade de exibir o resultado em qualquer formato.

Fazendo uma analogia com o Excel, que muitos conhecem, há uma funcionalidade chamada PivotTable, que permite manipular os dados. De maneira similar, o resultado de uma base OLAP pode se apresentar como uma PivotTable, permitindo posicionar elementos nas linhas, colunas ou filtros do relatório. O que determina o que aparecerá nas linhas e colunas do relatório são as cláusulas On Rows e On Columns.

ON ROWS/ON COLUMNS

ON ROWS/ON COLUMNS representam o formato de saída da consulta.

No SQL, não temos uma estrutura On Rows ou On Columnsm isso porque o dado sempre vem em formato tabular. No banco de dados multidimensional, além de selecionar o que queremos visualizar, podemos escolher como desejamos visualizar.

Tuplas

TUPLAS são os elementos que farão parte da consulta. Destacados acima os elementos das linhas da consultas

Dentro da estrutura On Rows e On Columns, temos o que chamamos de tuplas, que representam o que desejamos visualizar. No trecho abaixo da consulta, selecionamos que desejamos em linhas, no caso, Goiás:

[Cliente].[Estado].&[Goias]

E para visualizar o valor da venda na coluna, temos:

[Measures].[Valor Da Venda]

Representamos Goiás e Valor da Venda através de tuplas, sendo representada pela estrutura hierárquica do elemento. Ao construirmos a base de dados OLAP especificamos que a cidade de Goiás faz parte de um nível chamado Estado e que faz parte da dimensão Cliente.

Ao representar um elemento, como o estado de Goiás, precisamos respeitar sua estrutura hierárquica dentro da dimensão, representando-o como uma tupla. Também selecionamos o filtro através de uma tupla: [Produto].[Setor].&[Bebidas].

TUPLAS que representam os filtros da consulta

O filtro no MDX não é uma estrutura lógica como no SQL; basta mencionar o elemento da dimensão que desejamos filtrar no formato de tupla. Lembre-se de que, no OLAP, tudo já está calculado, então o MDX simplesmente seleciona os elementos pré-calculados que desejamos exibir.

Como nossa estrutura é n-dimensional, mas visualizamos o relatório de forma bidimensional (com linhas e colunas). Para representar através do MDX o que não foi selecionado em ON Rows e ON Columns ou ignoramos e tudo será selecionado; ou especificamos quem desejamos visualizar dentro da estrutura estrutura da base que estamos analisando no momento da consulta.

Especificamos no FROM o nome da base de dados no início, por exemplo, selecionando o nome do nosso data mart. Essa consulta, resultará na seguinte tabela:

Conclusão

Vamos agora entender como acessar o Management Studio, selecionar o Analysis Services e executar essa ou qualquer outra consulta usando a ferramenta com a qual já estamos acostumados para fazer seleções em bancos de dados relacionais.

A novidade é usar o Management Studio para seleções em bancos multidimensionais, o que aprenderemos nos próximos vídeos.

Um abraço e até o próximo vídeo!

Sobre o curso Data Mart e Analysis Services: entendendo a linguagem de consultas MDX

O curso Data Mart e Analysis Services: entendendo a linguagem de consultas MDX possui 165 minutos de vídeos, em um total de 66 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Estrutura do MDX
• Ordem e exibição dos elementos
• Cálculo de elementos do OLAP
• Período de tempo
• Dados ordenados
• Relatório Pareto