R koppelen aan een SQL database om data te analyseren en te visualiseren

Inleiding: R en jouw database

Met de open source statistische programmeertaal R kunnen zeer waardevolle inzichten gevonden worden. Dit komt omdat R een zeer geavanceerd en uitgebreid arsenaal heeft aan functionaliteiten om uitgebreide analyses te doen. Dit zijn komt door de ingebouwde functionaliteiten van R, maar vooral door de grote actieve community om R heen die continu bezig is met het ontwikkelen van nieuwe modules (packages)die R nog meer mogelijkheden geven om data te analyseren. Het belangrijkste stuk in dit hele verhaal is natuurlijk jouw data: naast de optie om losse datasets te analyseren is een naadloze koppeling van R aan de bestaande dataset een uitstekende toevoeging aan je bestaande Business Intelligence (BI) architectuur. Door R te integreren aan je BI landschap maak je een grote sprong in je mogelijkheden met analytics in je database. In dit artikel laat ik zien hoe je R kunt koppellen aan je bestaande database en welke mogelijkheden je hiermee kunt bereiken. Dit artikel is onderverdeeld in de volgende delen:

  • Datasource aanmaken;
  • Tabel laten zien in Microsoft SQL Server Management Studio (SSMS);
  • Tabel als dataframe importeren in R;
  • Eenvoudige statistieken en visualisaties weergeven.

De ingrediënten voor dit recept zijn:

  • R/RStudio;
  • RODBC package;
  • googleVis package
  • Adventure Works database;
  • (eventueel) Microsoft SQL Server Management Studio (SSMS).

Belangrijk! In dit voorbeeld wordt er gebruik gemaakt van Microsoft SQL Server Management Studio. Echter kun je deze stappen ook uitvoeren voor andere databases zoals Oracle of mySQL.

Datasource (ODBC) aanmaken

Tik “odbc” in het zoekvenster in van het Configuratiescherm (snelste manier).

Datasource aanmaken

Ga naar de “Systeem-DSN”-tab. Voeg daar een nieuwe gegevensbron in. Het onderstaande screenshot geeft het uiteindelijke resultaat aan als je de stappen voor het toevoegen van een DSN hebt doorlopen. Uiteindelijke is er een Systeem DSN met de naam “AdventureWorks2012” aangemaakt met SQL Server Native Client 11.0 als stuurprogramma.

Systeem DSN

Klaar! De datasource die we gaan gebruiken om R te koppen aan de database is aangemaakt!

Transactietabel uit database bekijken

De Adventure Works database wordt gebruikt als case. Uit deze database gebruiken we de tabel [Production].[TransactionHistoryArchive]  , een mooie tabel met transactie historie over de verkochte producten die zich uitstekend leent om wat leuke analyses op te doen. Je kunt de query in de screenshot gebruiken om deze tabel te selecteren. Dit is puur om even te zien hoe de dataset er uit ziet. In R gaan we namelijk dezelfde query gebruiken om de tabel in te laden.

Query Transactietabel Adventure Works

Query:

SELECT[TransactionID]
,[ProductID]
,[ReferenceOrderID]
,[ReferenceOrderLineID]
,CAST([TransactionDate] AS DATE) AS TransactionDate
,[TransactionType]
,[Quantity]
,[ActualCost]
,CAST ([ModifiedDate] AS DATE) AS ModifiedDate
FROM [AdventureWorks2012].[Production].[TransactionHistoryArchive]
ORDER BY [TransactionDate] ASC

Tabel als dataframe importeren in R

Het R-script die voor dit artikel wordt gebruikt kun je via een link aan het einde van dit artikel downloaden. Dit R script wordt hier stap voor stap uitgelegd:

Als eerst moeten de R packages geladen worden die we gaan gebruiken.

(als je de packages nog niet geinstalleerd hebt, gebruik “install.packages(‘RODBC’) ” en ” install.packages(‘googleVis’) “.

Packages laden R

We maken verbinding met de aangemaakte data scource. Daarna maken we een object aan die de namen van de tabellen in de data source bevat.

Connectie datasource en tabellen R

We importeren data uit een tabel in de data source op de manier zoals we die willen door middel van een query (de query die we zojuist gebruikt hebben bij SSMS kunnen we direct in deze functie plakken). Er wordt nu dus een query gebruikt om een tabel over te zetten van SSMS naar de R-omgeving.

Tabel Query-en naar R omgeving

Door middel van een aantal basisfuncties van R kunnen we wat eigenschappen van de zojuist geïmporteerde tabel bekijken.

Tabel bekijken R functies

We maken subsets aan van de tabel. Het doel is om een tabel te maken die per datum de totaal opgetelde (voor alle producten die dag) Quantity en Cost.

Aggregate tabellen

Nu gaan we de eerste visualisatie voorbereiden en laten zien. We willen een grafiek maken die twee x-assen bevat en één y-as met de datum. Zo hebben we in één weergave een overzicht van de ontwikkeling van de Quantity en de Cost voor alle producten.

googleVis voorbereiden

Bij het invoeren van plot(plot) wordt de webbrowser geopend met onze grafiek Door de muis over de lijn te bewegen, krijgen we details te zien over het datapunt. Ondanks dat is het nog niet echt een overige weergave, vooral niet als we een maand goed willen bekijken.

googleVisPlotAll

Daarom maken we in de volgende stap een subset van de dataset en plotten we het opnieuw.

googlevisjan2006GoogleVisPlotJan

Ten slotte kunnen we het uitgebreide arsenaal van R gebruiken om analyses te doen op de tabellen.

overige analyse R

Met als resultaat:

RplotBarTransactionType

Resultaat overig analyse R 1

Slot

Je kunt zien dat het vrij eenvoudig is om je database aan R te koppelen. Op deze manier ben trek je de bron veel dichterbij je analyse omgeving, je kunt op deze manier namelijk gewoon SQL  queries doen in R! Dit artikel zoomde vooral in op het koppeling gedeelte, de analyse waren namelijk peanuts en waren niet echt mind blowing omdat de focus in dit artikel ligt op het maken van een koppeling. In het volgender artikel ligt de focus meer op het analyseren en het ontwikkelen van een voorspellingsmodel met data uit de database.

Download hier het gebruikte R script

Referenties

Brian Ripley and Michael Lapsley (2015). RODBC: ODBC Database Access. R package version 1.3-12. http://CRAN.R-project.org/package=RODBC

Markus Gesmann and Diego de Castillo. Using the Google Visualisation API with R. The R  Journal, 3(2):40-44, December 2011.

https://cran.r-project.org/web/packages/googleVis/vignettes/googleVis_examples.html

Geef een reactie

Vul je gegevens in of klik op een icoon om in te loggen.

WordPress.com logo

Je reageert onder je WordPress.com account. Log uit /  Bijwerken )

Google photo

Je reageert onder je Google account. Log uit /  Bijwerken )

Twitter-afbeelding

Je reageert onder je Twitter account. Log uit /  Bijwerken )

Facebook foto

Je reageert onder je Facebook account. Log uit /  Bijwerken )

Verbinden met %s

Deze site gebruikt Akismet om spam te bestrijden. Ontdek hoe de data van je reactie verwerkt wordt.

%d bloggers liken dit: