olapR (pacote de R nos Serviços de Machine Learning do SQL Server)

Artigo
05/30/2023

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

O olapR é um pacote de R da Microsoft usado para consultas MDX em um cubo OLAP do SQL Server Analysis Services. As funções não são compatíveis com todas as operações de MDX, mas você pode criar consultas que fatiam, dividem, analisam detalhadamente, efetuam rollup e dinamizam em dimensões. O pacote está incluído nos Serviços de Machine Learning do SQL Server e no SQL Server 2016 R Services.

Você pode usar esse pacote em conexões com um cubo OLAP do Analysis Services em todas as versões compatíveis do SQL Server. Não há suporte para conexões a um modelo de tabela no momento.

Carregar pacote

O pacote olapR não é pré-carregado em uma sessão de R. Execute o comando a seguir para carregar o pacote.

library(olapR)

Versão do pacote

A versão atual é a 1.0.0 em todos os produtos e downloads somente do Windows que fornecem o pacote.

Disponibilidade e localização

Esse pacote é fornecido nos seguintes produtos, bem como em várias imagens de máquina virtual no Azure. A localização do pacote varia de acordo.

Produto	Location
Serviços de Machine Learning do SQL Server (com integração de R)	C:\Arquivos de Programas\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\library
SQL Server 2016 R Services	C:\Arquivos de Programas\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\library
Microsoft Machine Learning Server (R Server)	C:\Arquivos de Programas\Microsoft\R_SERVER\library
Cliente do Microsoft R	C:\Arquivos de Programas\Microsoft\R Client\R_SERVER\library
Máquina Virtual de Ciência de Dados (no Azure)	C:\Arquivos de Programas\Microsoft\R Client\R_SERVER\library
Máquina Virtual do SQL Server (no Azure) ¹	C:\Arquivos de Programas\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\library

¹ A integração do R é opcional no SQL Server. O pacote olapR será instalado quando você adicionar o Machine Learning ou o recurso de R durante a configuração da VM.

Como usar o olapR

A biblioteca olapR fornece uma API de estilo R simples para gerar e validar consultas MDX em relação a um cubo do Analysis Services. O olapR não fornece APIs para todos os cenários MDX, mas abrange a maioria dos casos de uso, incluindo fatia, dados, busca detalhada, acúmulo e cenários dinâmicos em N dimensões. Você também pode inserir uma consulta MDX direta para o Analysis Services para consultas que não podem ser construídas usando as APIs olapR.

Fluxo de trabalho para usar o olapR

Carregue a biblioteca.
Crie uma cadeia de conexão apontando para um cubo MOLAP no Analysis Services.
Verifique se você tem acesso de leitura no cubo
Use a cadeia de conexão em uma conexão.
Verifique a conexão usando a função explore.
Configure uma consulta enviando uma cadeia de caracteres de consulta MDX ou criando uma estrutura de consulta.
Execute a consulta e verifique o resultado.

Para executar uma consulta MDX em um Cubo OLAP, primeiro você precisa criar uma cadeia de conexão (olapCnn) e validar usando a função OlapConnection(connectionString). A cadeia de conexão deve ter uma Fonte de Dados (como localhost) e um Provedor (MSOLAP).

Depois que a conexão for estabelecida, você poderá transmitir uma consulta MDX totalmente definida ou construir a consulta usando o objeto Query(), definindo os detalhes da consulta usando cube(), axis(), columns(), slicers() e assim por diante.

Por fim, transmita olapCnn e a consulta para executeMD ou execute2D para obter uma matriz multidimensional ou uma estrutura de dados de volta.

Importante

olapR requer o provedor OLE DB do Analysis Services. Se você não tiver o SQL Server Analysis Services instalado no seu computador, baixe o provedor da Microsoft: Provedores de dados usados para conexões do Analysis Services

A versão exata que você deve instalar para o SQL Server 2016 está aqui.

Lista de funções

Função	Descrição
`OlapConnection`	Crie a cadeia de conexão para acessar o banco de dados do Analysis Services.
`Query`	Construa um objeto Query a ser usado no banco de dados do Analysis Services. Use cubo, eixo, colunas, linhas, páginas, capítulos, segmentações para adicionar detalhes à consulta.
`executeMD`	Usa um objeto Query ou uma cadeia de caracteres MDX e retorna o resultado como uma matriz multidimensional.
`execute2D`	Usa um objeto Query ou uma cadeia de caracteres MDX e retorna o resultado como uma estrutura de dados 2D.
`explore`	Permite a exploração de metadados do cubo.

Conceitos MDX

MDX é a linguagem de consulta para cubos MOLAP (OLAP multidimensionais) que contêm dados processados e agregados armazenados em estruturas otimizadas para análise e exploração de dados. Os cubos são usados em aplicativos empresariais e científicos para extrair insights sobre relações em dados históricos. Internamente, os cubos consistem em grande parte de dados numéricos quantificáveis, que são divididos em dimensões como data e hora, geografia ou outras entidades. Uma consulta típica pode acumular vendas para uma determinada região e período de tempo, divididas por categoria de produto, promoção, canal de vendas e assim por diante.

Os dados do cubo podem ser acessados usando várias operações:

Divisão – use um subconjunto do cubo, selecionando um valor para uma dimensão, resultando em um cubo que é uma dimensão menor.
Segmentação – crie um subcubo, especificando um intervalo de valores em várias dimensões.
Drill-Down/Up – navegue de intervalos de dados mais gerais para mais detalhados ou vice-versa.
Acúmulo – resuma os dados em uma dimensão.
Dinamização – gire o cubo.

As consultas MDX são semelhantes a consultas SQL, mas, devido à flexibilidade dos bancos de dados OLAP, podem conter até 128 eixos de consulta. Os quatro primeiros eixos são nomeados para conveniência: Colunas, Linhas, Páginas e Capítulos. Também é comum usar apenas dois (Linhas e Colunas), conforme mostrado no exemplo a seguir:

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

Ao usar um cubo OLAP do AdventureWorks do tutorial de cubo multidimensional, essa consulta MDX seleciona a contagem de vendas pela Internet e o valor das vendas e os coloca no eixo Coluna. No eixo Linha, ela coloca todos os valores possíveis da dimensão "Linha de produto". Em seguida, usando a cláusula WHERE (que é o eixo de segmentação em consultas MDX), ela filtra a consulta para que apenas as vendas da Austrália sejam importantes. Sem o eixo de segmentação, iríamos acumular e resumir as vendas de todos os países/regiões.

Exemplos de olapR

# load the library
library(olapR)

# Connect to a local SSAS default instance and the Analysis Services Tutorial database.
# For named instances, use server-name\\instancename, escaping the instance name delimiter.
# For databases containing multiple cubes, use the cube= parameter to specify which one to use.
cnnstr <- "Data Source=localhost; Provider=MSOLAP; initial catalog=Analysis Services Tutorial"
olapCnn <- OlapConnection(cnnstr)

# Approach 1 - build the mdx query in R
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(olapCnn, qry)

# Approach 2 - Submit a fully formed MDX query
mdx <- "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 Country].[Australia]"

result2 <- execute2D(olapCnn, mdx)

Confira também

Como criar consultas MDX usando olapR