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

Posted by Arie Twigt on 11 augustus 2015 in Analytics, database, Handleiding, R |Geen reacties

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).

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.

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:

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’) “.

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.

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.

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

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.

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.

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.

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

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

Met als resultaat:

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