MDX-query's maken in R met behulp van olapR

Van toepassing op: SQL Server 2016 (13.x) en latere versies

Het olapR-pakket in SQL Server Machine Learning Services ondersteunt MDX-query's op kubussen die worden gehost in SQL Server Analysis Services. U kunt een query maken op basis van een bestaande kubus, dimensies en andere kubusobjecten verkennen en bestaande MDX-query's plakken om gegevens op te halen.

In dit artikel worden de twee belangrijkste toepassingen van het olapR-pakket beschreven:

• Een MDX-query maken van R met behulp van de constructors in het olapR-pakket
• Een bestaande, geldige MDX-query uitvoeren met olapR en een OLAP-provider

De volgende bewerkingen worden niet ondersteund:

• DAX-query's op basis van een tabellair model
• Nieuwe OLAP-objecten maken
• Terugschrijven naar partities, inclusief metingen of sommen

Een MDX-query maken vanuit R

1. Definieer een verbindingsreeks die de OLAP-gegevensbron (SSAS-exemplaar) en de MSOLAP-provider aangeeft.

2. Gebruik de functie OlapConnection(connectionString) om een ingang voor de MDX-query te maken en de verbindingsreeks door te geven.

3. Gebruik de Query() constructor om een queryobject te instantiëren.

4. Gebruik de volgende helperfuncties voor meer informatie over de dimensies en metingen die moeten worden opgenomen in de MDX-query:

   • cube(): Geef de naam van de SSAS-database op. Als u verbinding maakt met een benoemd exemplaar, geeft u de computernaam en de naam van het exemplaar op.

   • columns(): Geef de namen op van de metingen die moeten worden gebruikt in het argument ON COLUMNS .

   • rows(): Geef de namen op van de metingen die moeten worden gebruikt in het argument ON ROWS .

   • slicers(): Geef een veld of leden op die als slicer moeten worden gebruikt. Een slicer is vergelijkbaar met een filter dat wordt toegepast op alle MDX-querygegevens.

   • axis(): Geef de naam op van een extra as die in de query moet worden gebruikt.

     Een OLAP-kubus kan maximaal 128 queryassen bevatten. Over het algemeen worden de eerste vier assen aangeduid als kolommen, rijen, pagina's en hoofdstukken.

     Als uw query relatief eenvoudig is, kunt u de functies columns, rowsenzovoort gebruiken om uw query te bouwen. U kunt de axis() functie echter ook gebruiken met een niet-nul-indexwaarde om een MDX-query met veel kwalificaties te bouwen of om extra dimensies toe te voegen als kwalificatie.

5. Geef de handle en de voltooide MDX-query door aan een van de volgende functies, afhankelijk van de vorm van de resultaten:

• executeMD: retourneert een multidimensionale matrix
• execute2D: retourneert een tweedimensionaal (tabellair) gegevensframe

Een geldige MDX-query uitvoeren vanuit R

1. Definieer een verbindingsreeks die de OLAP-gegevensbron (SSAS-exemplaar) en de MSOLAP-provider aangeeft.

2. Gebruik de functie OlapConnection(connectionString) om een ingang voor de MDX-query te maken en de verbindingsreeks door te geven.

3. Definieer een R-variabele om de tekst van de MDX-query op te slaan.

4. Geef de ingang en de variabele met de MDX-query door aan de functies executeMD of execute2D, afhankelijk van de vorm van de resultaten.

   • executeMD: retourneert een multidimensionale matrix
   • execute2D: retourneert een tweedimensionaal (tabellair) gegevensframe

Voorbeelden

De volgende voorbeelden zijn gebaseerd op het datamart- en kubusproject AdventureWorks, omdat dat project algemeen beschikbaar is, in meerdere versies, waaronder back-upbestanden die eenvoudig kunnen worden hersteld naar Analysis Services. Als u geen bestaande kubus hebt, haalt u een voorbeeldkubus op met een van de volgende opties:

• Maak de kubus die in deze voorbeelden wordt gebruikt door de Analysis Services-zelfstudie tot les 4 te volgen: Een OLAP-kubus maken.

• Download een bestaande kubus als back-up en herstel deze naar een exemplaar van Analysis Services. Deze site biedt bijvoorbeeld een volledig verwerkte kubus in zip-indeling: Adventure Works Multidimensional Model SQL 2014. Pak het bestand uit en herstel het vervolgens naar uw SSAS-exemplaar. Zie Back-up en herstel of Restore-ASDatabase voor meer informatie.

1. Basic MDX met slicer

Deze MDX-query selecteert de metingen voor het aantal en de hoeveelheid internetverkopen en de verkoophoeveelheid, en plaatst deze op de kolomas. Hiermee wordt een lid van de dimensie SalesTerritory als slicer toegevoegd om de query te filteren, zodat alleen de verkoop uit Australië wordt gebruikt in berekeningen.

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]

• In kolommen kunt u meerdere metingen opgeven als elementen van een door komma's gescheiden tekenreeks.
• De rij-as gebruikt alle mogelijke waarden (alle LEDEN) van de dimensie Productlijn.
• Deze query retourneert een tabel met drie kolommen, met een samenvatting van de internetverkopen uit alle landen/regio's.
• Met de WHERE-clausule wordt de sliceras opgegeven. In dit voorbeeld gebruikt de slicer een lid van de dimensie SalesTerritory om de query te filteren, zodat alleen de verkoop uit Australië in berekeningen wordt gebruikt.

Bouw deze query met behulp van de functies in 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)

Zorg ervoor dat u voor een genoemd exemplaar tekens escapet die als controletekens in R worden beschouwd. Bijvoorbeeld, de volgende verbindingsreeks verwijst naar een exemplaar OLAP01 op een server met de naam ContosoHQ:

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

Deze query uitvoeren als een vooraf gedefinieerde mdx-tekenreeks

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)

Als u een query definieert met behulp van de MDX Builder in SQL Server Management Studio en vervolgens de MDX-tekenreeks opslaat, worden de assen genummerd vanaf 0, zoals wordt weergegeven in het volgende voorbeeld:

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]

U kunt deze query nog steeds uitvoeren als een vooraf gedefinieerde MDX-tekenreeks. Als u echter dezelfde query wilt maken met behulp van R met behulp van de axis() functie, moet u de assen hernummeren vanaf 1.

2. Kubussen en hun velden op een SSAS-exemplaar verkennen

U kunt de explore functie gebruiken om een lijst met kubussen, dimensies of leden te retourneren die u kunt gebruiken bij het samenstellen van uw query. Dit is handig als u geen toegang hebt tot andere OLAP-browserprogramma's of als u de MDX-query programmatisch wilt bewerken of samenstellen.

De kubussen weergeven die beschikbaar zijn voor de opgegeven verbinding

Als u alle kubussen of perspectieven wilt bekijken op het exemplaar waarvoor u toestemming heeft om te bekijken, voorziet u de handler als argument voor explore.

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

Een lijst met kubusdimensies ophalen

Als u alle dimensies in de kubus of het perspectief wilt weergeven, geeft u de naam van de kubus of het perspectief op.

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

Alle leden van de opgegeven dimensie en hiërarchie retourneren

Nadat u de bron hebt gedefinieerd en de ingang hebt gemaakt, geeft u de kubus, dimensie en hiërarchie op die u wilt retourneren. In de geretourneerde resultaten vertegenwoordigen items met het voorvoegsel -> kindelementen van het vorige lid.

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

Verwante inhoud

• Gegevens uit OLAP-kubussen gebruiken in R