Come creare query MDX in R con olapR

Si applica a: SQL Server 2016 (13.x) e versioni successive

Il pacchetto olapR in SQL Server Machine Learning Services supporta le query MDX sui cubi ospitati in SQL Server Analysis Services. È possibile creare una query su un cubo esistente, esplorare le dimensioni e altri oggetti del cubo e incollare le query MDX esistenti per recuperare i dati.

Questo articolo descrive i due usi principali del pacchetto olapR:

• Creare una query MDX da R, usando i costruttori forniti nel pacchetto olapR
• Eseguire una query MDX valida esistente usando olapR e un provider OLAP

Le operazioni non supportate sono elencate di seguito:

• Query DAX su un modello tabulare
• Creazione di nuovi oggetti OLAP
• Writeback delle partizioni, incluse misure o somme

Creare una query MDX da R

1. Definire una stringa di connessione che specifica l'origine dati OLAP (istanza di SSAS) e il provider MSOLAP.

2. Usare la funzione OlapConnection(connectionString) per creare un handle per la query MDX e passare la stringa di connessione.

3. Usare il costruttore Query() per creare un'istanza di un oggetto query.

4. Usare le funzioni helper seguenti per fornire altri dettagli sulle dimensioni e sulle misure da includere nella query MDX:

   • cube() Specificare il nome del database SSAS. Se ci si connette a un'istanza denominata, specificare il nome del computer e il nome dell'istanza.

   • columns() Specificare i nomi delle misure da usare nell'argomento ON COLUMNS.

   • rows() Specificare i nomi delle misure da usare nell'argomento ON ROWS.

   • slicers() Specificare un campo o i membri da usare come filtro dei dati. Si tratta di un filtro che viene applicato a tutti i dati delle query MDX.

   • axis() Specificare il nome di un altro asse da usare nella query.

     Un cubo OLAP può contenere un massimo di 128 assi di query. In genere, i primi quattro assi sono definiti Columns, Rows, Pages e Chapters.

     Se la query è relativamente semplice, è possibile usare le funzioni columns, rowse così via per crearla. È tuttavia possibile usare anche la funzione axis() con un valore di indice diverso da zero per creare una query MDX con molti qualificatori o aggiungere altre dimensioni come qualificatori.

5. Passare l'handle e la query MDX completata in una delle funzioni seguenti, a seconda della forma dei risultati:

• executeMD restituisce una matrice multidimensionale
• execute2D restituisce un frame di dati bidimensionale (tabulare)

Eseguire una query MDX valida da R

1. Definire una stringa di connessione che specifica l'origine dati OLAP (istanza di SSAS) e il provider MSOLAP.

2. Usare la funzione OlapConnection(connectionString) per creare un handle per la query MDX e passare la stringa di connessione.

3. Definire una variabile R per archiviare il testo della query MDX.

4. Passare l'handle e la variabile contenente la query MDX nella funzione executeMD o execute2D, a seconda della forma dei risultati.

   • executeMD restituisce una matrice multidimensionale
   • execute2D restituisce un frame di dati bidimensionale (tabulare)

Esempi

Gli esempi seguenti si basano sul progetto di data mart e cubo AdventureWorks, perché il progetto è ampiamente disponibile, in più versioni, inclusi i file di backup che possono essere facilmente ripristinati per Analysis Services. Se non si dispone di un cubo esistente, ottenere un cubo di esempio in uno dei modi seguenti:

• Creare il cubo usato in questi esempi seguendo l'esercitazione di Analysis Services fino alla lezione 4: Creare un cubo OLAP

• Scaricare un cubo esistente come backup e ripristinarlo in un'istanza di Analysis Services. Ad esempio, questo sito fornisce un cubo completamente elaborato in formato compresso: Adventure Works Multidimensional Model SQL 2014. Estrarre il file e quindi ripristinarlo nell'istanza di SSAS. Per altre informazioni, vedere Backup e ripristino o Cmdlet Restore-ASDatabase.

1. Query MDX di base con filtro dei dati

Questa query MDX seleziona le misure per il conteggio e la quantità di Internet sales count e Sales amount e li inserisce nell'asse Column. Aggiunge un membro della dimensione SalesTerritory come filtro dei datiper filtrare la query in modo da includere nei calcoli solo le vendite relative all'Australia.

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]

• Nelle colonne è possibile specificare più misure come elementi di una stringa con valori delimitati da virgole.
• L'asse Row usa tutti i valori possibili (tutti i MEMBERS) della dimensione "Product Line".
• Questa query restituisce una tabella con tre colonne, che contiene un riepilogo di rollup delle vendite Internet relative a tutti i paesi/aree geografiche.
• La clausola WHERE specifica l'asse di sezionamento. In questo esempio il filtro dei dati usa un membro della dimensione SalesTerritory per filtrare la query in modo da includere nei calcoli solo le vendite relative all'Australia.

Per compilare la query con le funzioni di 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)

Per un'istanza denominata, assicurarsi di usare la sequenza di escape per i caratteri che potrebbero essere considerati caratteri di controllo in R. La stringa di connessione seguente, ad esempio, fa riferimento a un'istanza OLAP01, in un server denominato ContosoHQ:

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

Per eseguire la query come stringa MDX predefinita

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 si definisce una query con il Generatore MDX in SQL Server Management Studio e quindi si salva la stringa MDX, gli assi verranno numerati a partire da 0, come illustrato di seguito:

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]

La query può sempre essere eseguita come stringa MDX predefinita. Tuttavia, per creare la stessa query con R usando la funzione axis(), è necessario rinumerare gli assi a partire da 1.

2. Esplorare i cubi e i relativi campi in un'istanza di SSAS

È possibile usare la funzione explore per restituire un elenco di cubi, dimensioni o membri da usare per creare una query. Questa funzione è utile se non si può accedere ad altri strumenti di esplorazione OLAP o se si desidera modificare o creare la query MDX a livello di codice.

Per elencare i cubi disponibili per la connessione specificata

Per visualizzare tutti i cubi o le prospettive dell'istanza per i quali si dispone dei diritti di visualizzazione, specificare l'handle come argomento di explore.

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

Per ottenere un elenco delle dimensioni del cubo

Per visualizzare tutte le dimensioni del cubo o della prospettiva, specificare il nome del cubo o della prospettiva.

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

Per restituire tutti i membri della dimensione e della gerarchia specificate

Dopo aver definito l'origine e la creazione dell'handle, specificare il cubo, la dimensione e la gerarchia da restituire. Nei risultati restituiti gli elementi con il prefisso -> rappresentano gli elementi figlio del membro precedente.

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

Vedi anche

Uso dei dati dai cubi OLAP in R