Como criar consultas MDX em R usando olapR

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores

O pacote olapR no Serviços de Machine Learning do SQL Server dá suporte a consultas MDX em cubos hospedados no SQL Server Analysis Services. Você pode criar uma consulta com relação a um cubo existente, explorar dimensões e outros objetos de cubo e colar em consultas MDX existentes para recuperar dados.

Este artigo descreve os dois principais usos do pacote olapR:

Não há suporte para as operações a seguir:

  • Consultas DAX em um modelo de tabela
  • Criação de novos objetos OLAP
  • Write-back para partições, incluindo medidas ou somas

Criar uma consulta MDX usando o R

  1. Defina uma cadeia de conexão que especifica a fonte de dados OLAP (instância do SSAS) e o provedor MSOLAP.

  2. Use a função OlapConnection(connectionString) para criar um identificador para a consulta MDX e transmitir a cadeia de conexão.

  3. Use o construtor Query() para instanciar um objeto de consulta.

  4. Use as seguintes funções auxiliares para fornecer mais detalhes sobre as dimensões e medidas a serem incluídas na consulta MDX:

    • cube() Especifique o nome do banco de dados de SSAS. Se você estiver se conectando a uma instância nomeada, forneça o nome do computador e o nome da instância.

    • columns() Forneça os nomes das medidas a serem usadas no argumento ON COLUMNS.

    • rows() Forneça os nomes de medidas para usar no argumento ON ROWS.

    • slicers() Especifique um campo ou membros para usar como uma segmentação de dados. Uma segmentação de dados é como um filtro que é aplicado a todos os dados da consulta MDX.

    • axis() Especifique o nome de um eixo adicional para usar na consulta.

      Um cubo OLAP pode conter até 128 eixos de consulta. Em geral, os primeiros quatro eixos são denominados Colunas, Linhas, Páginas e Capítulos.

      Se sua consulta for relativamente simples, você poderá usar as funções columns, rows, etc. para criar a consulta. No entanto, você também pode usar a função axis() com um valor de índice diferente de zero para criar uma consulta MDX com muitos qualificadores, ou para adicionar dimensões extras como qualificadores.

  5. Passe o identificador e a consulta MDX concluída para uma das funções a seguir, dependendo da forma dos resultados:

  • executeMD Retorna uma matriz multidimensional
  • execute2D Retorna um quadro de dados (tabular) bidimensional

Executar uma consulta MDX válida do R

  1. Defina uma cadeia de conexão que especifica a fonte de dados OLAP (instância do SSAS) e o provedor MSOLAP.

  2. Use a função OlapConnection(connectionString) para criar um identificador para a consulta MDX e transmitir a cadeia de conexão.

  3. Defina uma variável de R para armazenar o texto da consulta MDX.

  4. Transmita o identificador e a variável que contendo a consulta MDX para as funções executeMD ou execute2D, dependendo da forma dos resultados.

    • executeMD Retorna uma matriz multidimensional
    • execute2D Retorna um quadro de dados (tabular) bidimensional

Exemplos

Os exemplos a seguir baseiam-se no data mart AdventureWorks e no projeto de cubo, pois esse projeto está amplamente disponível, em diversas versões, incluindo arquivos de backup que podem ser facilmente restaurados para o Analysis Services. Se você não tiver um cubo, obtenha um exemplo de cubo usando uma destas opções:

1. MDX básica com segmentação de dados

Essa consulta MDX seleciona as medidas para a contagem e o valor da contagem de vendas pela Internet e o valor das vendas e as coloca no eixo das Colunas. Ela adiciona um membro da dimensão SalesTerritory como uma segmentação de dadospara filtrar a consulta de modo que somente as vendas da Austrália sejam usadas nos cálculos.

SELECT {[Measures].[Internet Sales Count], [Measures].[InternetSales-Sales Amount]} ON COLUMNS, 
{[Product].[Product Line].[Product Line].MEMBERS} ON ROWS 
FROM [Analysis Services Tutorial] 
WHERE [Sales Territory].[Sales Territory Country].[Australia]
  • Nas colunas, você pode especificar várias medidas como elementos de uma cadeia de caracteres separada por vírgulas.
  • O eixo Linha usa todos os valores possíveis (todos os MEMBROS) da dimensão "Linha de produto".
  • Essa consulta retornará uma tabela com três colunas contendo um resumo de valor acumulado de vendas pela Internet de todos os países/regiões.
  • A cláusula WHERE especifica o eixo de segmentação. Neste exemplo, a segmentação usa um membro da dimensão SalesTerritory para filtrar a consulta de modo que somente as vendas da Austrália sejam usadas nos cálculos.

Para criar essa consulta usando as funções fornecidas em olapR

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs <- OlapConnection(cnnstr)

qry <- Query()
cube(qry) <- "[Analysis Services Tutorial]"
columns(qry) <- c("[Measures].[Internet Sales Count]", "[Measures].[Internet Sales-Sales Amount]")
rows(qry) <- c("[Product].[Product Line].[Product Line].MEMBERS") 
slicers(qry) <- c("[Sales Territory].[Sales Territory Country].[Australia]")

result1 <- executeMD(ocs, qry)

Para uma instância nomeada, escape quaisquer caracteres que possam ser considerados caracteres de controle no R. Por exemplo, a cadeia de conexão a seguir faz referência a uma instância OLAP01 em um servidor chamado ContosoHQ:

cnnstr <- "Data Source=ContosoHQ\\OLAP01; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"

Para executar essa consulta como uma cadeia de caracteres MDX predefinida

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs <- OlapConnection(cnnstr)

mdx <- "SELECT {[Measures].[Internet Sales Count], [Measures].[InternetSales-Sales Amount]} ON COLUMNS, {[Product].[Product Line].[Product Line].MEMBERS} ON ROWS FROM [Analysis Services Tutorial] WHERE [Sales Territory].[Sales Territory Country].[Australia]"

result2 <- execute2D(ocs, mdx)

Se você definir uma consulta usando o construtor MDX no SQL Server Management Studio e, em seguida, salvar a cadeia de caracteres MDX, ele numerará os eixos começando em 0, conforme mostrado aqui:

SELECT {[Measures].[Internet Sales Count], [Measures].[Internet Sales-Sales Amount]} ON AXIS(0), 
   {[Product].[Product Line].[Product Line].MEMBERS} ON AXIS(1) 
   FROM [Analysis Services Tutorial] 
   WHERE [Sales Territory].[Sales Territory Countr,y].[Australia]

Você ainda pode executar essa consulta como uma cadeia de caracteres MDX predefinida. No entanto, para criar a mesma consulta usando o R com a função axis(), renumere os eixos começando em 1.

2. Explore os cubos e seus campos em uma instância do SSAS

Você pode usar a função explore para retornar uma lista de cubos, dimensões ou membros para usar na construção de sua consulta. Isso é útil se você não tiver acesso a outras ferramentas de procura em OLAP ou se você quiser manipular programaticamente ou construir a consulta MDX.

Para listar os cubos disponíveis na conexão especificada

Para exibir todos os cubos ou perspectivas na instância que você tem permissão para exibir, forneça o identificador como um argumento para explore.

Importante

O resultado final não é um cubo; TRUE indica apenas que a operação de metadados foi bem-sucedida. Um erro será gerado se os argumentos forem inválidos.

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs <- OlapConnection(cnnstr)
explore(ocs)
Resultados
Tutorial do Analysis Services
Vendas pela Internet
Vendas do revendedor
Resumo de vendas
[1] TRUE

Para obter uma lista de dimensões do cubo

Para exibir todas as dimensões no cubo ou perspectiva, especifique o nome do cubo ou da perspectiva.

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs \<- OlapConnection(cnnstr)
explore(ocs, "Sales")
Resultados
Cliente
Data
Região

Para retornar todos os membros da dimensão e hierarquia especificada

Depois de definir a fonte e criar o identificador, especifique o cubo, a dimensão e a hierarquia a serem retornados. Nos resultados retornados, itens que têm prefixo -> representam os filhos do membro anterior.

cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
ocs <- OlapConnection(cnnstr)
explore(ocs, "Analysis Services Tutorial", "Product", "Product Categories", "Category")
Resultados
Acessórios
Bikes
Clothing
Componentes
-> Componentes do assembly
-> Componentes do assembly

Confira também

Como usar dados de cubos OLAP no R