MDX Query com SQL Server 2008R2

A linguagem MDX é a responsável por realizar consultas OLAP além de permitir a construção de cálculos avançados para soluções de BI. É importante destacar o fato de que o script em uma consulta na linguagem MDX é relativamente pequeno se comparado com a linguagem Transact-SQL, linguagem de consulta utilizada pelo SQL Server.

Um conhecimento, mesmo que básico a respeito da linguagem MDX é de suma importância para o sucesso no desenvolvimento de projetos de Business Intelligence. Apesar da estrutura da linguagem MDX ser semelhante a T-SQL, a grande diferença fica por conta de sua aplicação. Enquanto a linguagem T-SQL é utilizada em consultas sobre modelos relacionais, a linguagem MDX é construída especificamente para modelos multidimensionais.

Assim como em outras linguagens de programação é possível desenvolver uma parte do código de várias formas obtendo o mesmo resultado, assim também é para a linguagem MDX. Algumas alternativas podem ser mais eficientes do que outras durante a execução. Neste artigo veremos alguns fatores que podem tornar uma consulta eficiente quando se executa a linguagem MDX no Microsoft Analysis Services.

Neste artigo utilizaremos como fonte de dados o banco AdventureWorks 2008R2 SR1.

Após a instalação do banco, será criado um cubo utilizando as tabelas do banco AdventureWorks.

Criação do Cubo

Após a realização do download do Sample e posteriormente a sua instalação poderemos dar inicio a criação do cubo que servirá de exemplo para trabalharmos com a linguagem MDX.

Para dar início à criação do Cubo, no menu Iniciar, clique em Todos os Programas, Microsoft SQL Server 2008 e selecione SQL Server Business Intelligence Development Studio (Figura 1).

Figura 1 - Iniciando projeto

No menu Arquivo, selecione Novo Projeto (New Project) para criar um novo projeto Integration Services (Figura 2).

Figura 2 - Criando um novo projeto

Na caixa de diálogo Novo Projeto (New Project), selecione o Projeto do Analysis Services (Analysis Services Project) no painel Modelos (Templates) (Figura 3). Após definir um nome para o projeto e clique em Ok.

Figura 3 - Criando um novo projeto do Analysis Services

Na aba Solution Explorer clique com o botão direito sobre a pasta Data Sources e depois, com o botão esquerdo clique em New Data Source (Figura 4). Após isso a janela de definição do Data Source será exibida (Figura 5)

Figura 4 - Criando uma conexão com um Data Source

Figura 5 - Criando uma nova conexão com um Data Source

Para criar uma nova conexão com a fonte de dados clique em New, a seguinte janela será exibida (Figura 6). Nesta janela deve-se definir o nome do servidor e o banco de dados que será utilizado no modelo.

Figura 6 - Definindo parâmetros de conexão de um Data Source

Após a definição do servidor e do banco de dados descritos anteriormente, o sistema nos remente a seguinte janela (Figura 7). O próximo passo será editar algumas propriedades de conexão com a fonte de dados, para isso clique em Next.

Figura 7 - Selecionando uma conexão

Clicando em Next, a janela abaixo será exibida (Figura 8). Essa janela definirá qual a credencial que será utilizada para obter acesso à fonte de dados, por padrão seleciona-se a opção "Use the service account".

Figura 8 - Definindo credenciais de aceso

Clicando em Next, será exibida uma janela conforme Figura 9. Nesta janela deve-se atribuir um nome para o Data Source, finalmente clique em Finish.

Figura 9 - Definindo nome da fonte de dados

Finalizada a criação da conexão com o Data Source, o próximo passo será definir quais tabelas estarão disponíveis para visualização no cubo, o procedimento é semelhante a criação do Data Source. O primeiro passo é clicar com o botão direito do mouse sobre a pasta Data Source View da aba Solution Explorer e na opção New Data Source View clique com o botão esquerdo do mouse (Figura 10).

Figura 10 - Criando a Data Source View.

Nessa etapa será realizado a seleção do Data Source criado anteriormente. Para isso, selecione o Data Source e depois clique em Next (Figura 11).

Figura 11 - Selecionando um Data Source

A seguinte janela (Figura 12) será apresentada, nesta etapa serão selecionadas as tabelas que farão parte do Cubo, neste caso as tabelas selecionadas foram, DimCustomer, DimDate e FactInternetSales, após a seleção das tabelas clique na seta direita ( ) para efetivar a escolha, em seguida clique em Next.

Figura 12 - Selecionando Tabelas para o cubo

Em seguida deve-se definir o nome do Data Source View(Figura 13)

Figura 13 - Definindo um nome para o Data Source View

Ao finalizar o processo anterior, a seguinte janela será exibida (Figura 14).

Figura 14 - Janela de Exibição do Data Source View.

O próximo passo será definir as dimensões que irão compor as visões do cubo, para isso clique com o botão direito do mouse sobre a pasta Dimensions na aba Solution Explorer e depois com o botão esquerdo clique em New Dimension (Figura 15).

Figura 15 - Criando uma dimensão.

Ao clicar em New Dimension a seguinte janela será exibida (Figura 16). Nesta janela selecione a opção Use an existing Table (Utilizar uma tabela existente) e clique em Next.

Figura 16 - Selecionando um método para criação da dimensão

Na seguinte janela (Figura 17), deve-se selecionar o Data Source View criado em passos anteriores, além das respectivas tabelas dimensão. Ainda nesta janela, é necessário definir qual será Key Column (Coluna Chave) e definir o seu nome.

Após a definição dos campos descritos anteriormente clique em Next, em seguida selecione os atributos que deseja exibir na dimensão (Figura 18) novamente, clique em Next e na próxima janela defina o nome da dimensão (Figura 19) .

Figura 17 - Especificação das informações da fonte de dados.

Figura 18 - Seleção dos atributos das Dimensões Custumer e Date.

Figura 19 - Definição do nome da Dimensão.

Clicando em Finish conclui-se a etapa de criação da dimensão, essa tarefa deve ser realizada para todas as dimensões que possam ser relevantes para a composição do cubo. Devemos editar a dimensão Dim Custumer para que os nomes dos Clientes possam ser exibidos de maneira completa (Nome e Sobrenome), o primeiro passo é dar um duplo clique em Data Source View (Figura 20).

Figura 20 - Duplo clique em Data Source View

Na tela seguinte, clique com o botão direito sobre DimCustomer localizada na Aba Tables e depois em New Named Calculation. (Figura 21)

Figura 21 - New Named Calculation

Após isso daremos o nome para o Named Calculation e em Expression digitaremos : FirstName+ ’ ’ + LastName (Figura 22), feito isso, clique em OK.

Figura 22 - Nomeando Named Calculation

Terminado o passo anterior devemos adicionar a Named Calculation Full Name na dimensão, para isso dê um duplo clique sobre a dimensão Dim Custumer, a seguinte tela será exibida. Clique e arraste o membro Full Name para a aba Atributes, para finalizar clique em processar (Figura 23).

Figura 23 - Full Name Atributes

Após a criação das 2 dimensões (Dim Custumer e Dim Date), a próxima etapa consiste em montar o Cubo, para isso, clique com o botão direito do mouse sobre a pasta Cubes localizada na aba Solution Explorer e posteriormente em New Cube (Figura 24).

Figura 24 - Criação do Cubo.

Ao concluir o passo anterior a seguinte janela será apresentada (Figura 25). Nesta janela deverão ser definidos os métodos para criação do cubo, seleciona-se a primeira opção Use existing tables (Utilizar tabelas existentes) e clique em Next.

Figura 25 - Seleção do método de criação do cubo.

O próximo passo será definir qual será o grupo de medidas (Figura 26).

Figura 26 - Seleção da Tabela que possui um grupo de medidas.

Após a definição do grupo de medidas, neste caso a tabela Fact Internet Sales, deve-se selecionar as medidas que serão incluídas no cubo OLAP (Figura 27), em seguida clique em Next.

Figura 27 - Seleção do grupo de medidas

O passo seguinte será selecionar as dimensões que irão compor o cubo (Figura 28).

Figura 28 - Seleção das Dimensões.

Após a definição das medidas e dimensões do cubo, deve-se definir o nome do cubo (Figura 29), também é possível fazer uma revisão da estrutura do cubo OLAP. Clique em Finish.

Figura 29 - Revisão e definição do nome do cubo

O próximo passo será realizar a implantação (Deploy) do projeto, para isso clique com o botão direito sobre o cubo recém-criado e depois, com o botão esquerdo, clique em Process... (Figura 30), em seguida, a janela referente a Figura 31 será exibida, nesta clique em Run....

Figura 30 - Processar Cubo

Figura 31 - Processar Cubo

As etapas do processo anterior podem ser visualizadas na próxima imagem (Figura 32).

Figura 32 - Resultado do Processamento do Cubo

Ao término do processamento descrito na etapa anterior, clique em Close e em seguida na aba Browser (Figura 33).

Figura 33 - Acessando o Browser do Cubo

A aba Browser corresponde ao local em que serão realizadas as análises (Figura 34), para isso, deve-se fazer uso das dimensões criadas em passos anteriores, além das medidas selecionadas da tabela fato.

Figura 34 - Browser do Cubo

Para obter informações geradas após a criação do cubo, clique e arraste os atributos das dimensões para área Drop Row Fields Here, o mesmo deverá ser feito para as unidades de medida (Measures) selecione uma ou mais métrica e arraste até a área Drop Totals or Detail Fields Here localizada no centro da área de trabalho do BIDS.

Trabalhando com MDX no SQL Server

Para trabalharmos com a linguagem MDX é necessário utilizar o SGBD SQL Server e nos conectar ao Analysis Services (Figura 35).

Figura 35 - Conectando SQL Server

Ao conectar podemos verificar o Banco Artigo_MDX criado em passos anteriores pelo BIDS (Figura 36).

Figura 36 - Escolhendo Banco de dados

Clicando com o botão direito sobre o banco Artigo_MDX surgirão algumas opções, clique em New Query -> MDX (Figura 37).

Figura 37 - Criando MDX

Concluido o passo anterior, a seguinte tela será exibida (Figura 38)

Figura 38 - Tela exibida

Otimizando consultas MDX usando a função NonEmpty()

NonEmpty() é uma ponderosa função MDX, cujo principal objetivo é reduzir o tempo de consulta. Essa função irá retornar a partir de um conjunto especificado, as linhas que não estão vazias, com base no produto cartesiano das dimensões especificadas.

Quando um servidor executa queries, ele se comporta como se cada dado fosse totalmente gravado na memória antes de utilizá-lo, é importante lembrar que esses dados podem ser nulos. Isto introduz questões de recursos significativos para o servidor, pois acarretará em um consumo de tempo que poderia ser evitado.

Primeiramente analisaremos a sobre a quantidade. Como resultado da consulta (listagem 1) onde não foi utilizado a função NonEmpty observou-se um consumo de tempo de 22 segundos.

Listagem 1: Consulta sem NonEmpty

SELECT
	{ [Measures].[Sales Amount] } ON 0,
	NON EMPTY
	Filter(
		{[Dim Customer].[Full Name].[Full Name].MEMBERS } *
		{[Due Date].[Full Date Alternate Key].[Full Date Alternate Key].MEMBERS },
		[Measures].[Sales Amount] > 1000
		) ON 1
FROM [Cube_Artigo]

A consulta acima exibe as vendas por cliente e as datas de suas compras onde o valor dessas compras foi superior a 1000 USD. Depois de algum tempo (35 Segundos), a consulta retornará os seguintes resultados (Figura 39).

Figura 39 - Resultado da consulta

O próximo passo é melhorar o desempenho desta consulta, para isso alguns passos devem ser seguidos:

Primeiramente devemos inserir a função NonEmpty () e no corpo desta função inserir as dimensões Customer e Time que serão o primeiro argumento desta função. Novamente utiliza-se a medida Sales Amount, este será o segundo argumento da função. O resultado final do script para esta consulta deverá ser semelhante a listagem 2.

Listagem 2: Script para consulta

SELECT
   { [Measures].[Sales Amount] } ON 0,
NON EMPTY
   Filter(
  NonEmpty(
		{[Dim Customer].[Full Name].[Full Name].MEMBERS } *
		{[Due Date].[Full Date Alternate Key].[Full Date Alternate Key].MEMBERS }
		   ),
  [Measures].[Sales Amount] > 1000
      ) ON 1
FROM [Cube_Artigo]

Ao executar essa consulta devem ser observados os resultados, bem como o tempo gasto para a realização da consulta. A consulta retornou os mesmos resultados, só que muito mais rápido, desta vez em apenas 3 segundos (Figura 40).

Figura 40 - Tempo de consulta

Funcionamento da Função NonEmpty()

As dimensões Costumer e Date utilizadas neste exemplo não possuem uma grande quantidade de dados (18.484 e 1.188 linhas respectivamente). Porém o cruzamento dessas dimensões resultou em milhares de combinações. A função Filter () não é otimizada para atuar como um bloqueio, o que resulta na execução de uma grande quantidade de cálculos através de seu mecanismo para avaliação do conjunto de linhas.

É para essa finalidade que a função NonEmpty () foi criada. Esta função pode ser usada para reduzir qualquer conjunto de dados, especialmente dados multidimensionais que são resultado de uma operação de intersecção. Um conjunto reduzido de dados possui menos células a serem calculadas e, portanto, a consulta será executada muito mais rápida.

Apesar dos benefícios demonstrados, a função NonEmpty () deve ser usada com cautela. A melhor prática diz que devemos usá-la em funções que não são otimizadas para trabalhar no modo de bloqueio, como acontece com a função Filter(). Por outro lado, devemos evitar o uso da função NonEmpty () em funções agregadas, como Sum () e outras funções MDX que são otimizados para trabalhar no modo de bloqueio.

O uso de NonEmpty () dentro de funções otimizadas irá impedi-las de avaliar o conjunto no modo de bloqueio. Isso porque, o conjunto não será compacto, uma vez que a função NonEmpty () foi utilizada. Como resultado, a função irá quebrar essa consulta em blocos menores e que contenham dados, cada bloco será avaliado separadamente, fazendo com que a duração da consulta seja maior. Nesses casos, é melhor deixar o conjunto original intacto, não importando o seu tamanho, pois o próprio SGBD saberá como executar essa operação em modo otimizado.