Reporting Services 2016 : Mr et Mme R ont un fils…

Article
02/24/2016

Une des grandes nouveautés de l’édition 2016 de SQL Server, est l’intégration de R. Dans un article précédent nous avons vu comment installer et configurer SQL Server 2016 pour réaliser cette intégration avec R.

Dans cet article nous allons voir comment utiliser R, le moteur SQL Server ainsi que Reporting Services, afin d’utiliser les visualisations de R au sein même de Reporting Services.

Pour illustrer cet article, je vais utiliser le jeu de données Iris qui est embarqué par défaut dans R studio

R Studio

Avant de commencer, nous allons faire un tour rapide du côté de R Studio.

Nous allons utiliser le jeu de données Iris. Afin de voir ce qu’il y a dedans, exécutez la commande suivante

head(iris) (si vous souhaitez plus de résultats alors exécutez head(iris,n=20) 20 ou un autre nombre)

Cliquez sur le bouton « run »

Très rapidement, il est possible de représenter ce jeu de données sous forme graphique. Ici nous allons utiliser la librairie « ggplot2 ».

Avant de pouvoir l’utiliser, il faut chercher cette librairie avec la commande ci-dessous.

install.packages("ggplot2")

library(ggplot2)

Sélectionnez les 2 lignes puis cliquez sur « Run »

Exécutez la commande suivante :

qplot(Sepal.Length, Petal.Length, data = iris)

Une visualisation de type plot apparaît alors dans RStudio

Il est tout à fait possible d’améliorer cette représentation. Par exemple avec la commande ci-dessous :

qplot(Sepal.Length, Petal.Length, data = iris, color = Species, size = Petal.Width, alpha = I(0.7))

Intégration avec SQL Server

Dans cet exemple tout simple, nous allons voir comment récupérer les données Iris pour les insérer dans une table SQL

Dans un premier temps nous allons créer une base de données ainsi qu’une table pour notre exemple.

Création d’une base de données

Dans SQL Server Management Studio (SSMS), faîtes un clic droit sur « Database », puis « New Database »

Dans le fenêtre « New Database » entrez les informations de votre base puis cliquez sur le bouton « OK »

Création de la table

Sélectionnez la base de données que vous venez de créer puis cliquez sur « New Query »

Puis dans la fenêtre de droite, copiez le script ci-dessous

CREATE TABLE [dbo].[Iris](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Sepal.Length] [float] NULL,

[Sepal.Width] [float] NULL,

[Petal.Length] [float] NULL,

[Petal.Width] [float] NULL,

[Species] [nvarchar](50) NULL

) ON [PRIMARY]

Cliquez sur le bouton « Execute »

Vous devez avoir normalement quelque chose comme ça :

Création d’une procédure stockée pour récupérer les données Iris

Dans la base de données que nous venons de créer, exécutez la requête suivante

-- Création d'une procédure pour charger les données iris dans une table SQL

drop procedure if exists get_iris_dataset;

go

create procedure get_iris_dataset

as

begin

-- Return iris dataset from R to SQL:

execute sp_execute_external_script

@language = N'R'

, @script = N'

iris_data <- iris;

'

, @input_data_1 = N''

, @output_data_1_name = N'iris_data'

with result sets ((

"[Sepal.Length]" float not null

, "[Sepal.Width]" float not null

, "[Petal.Length]" float not null

, "[Petal.Width]" float null

, "[Species]" nvarchar(50) null ));

end;

go

Cette procédure va utiliser la fonction sp_execute_external_script . Ici, nous allons juste demander à R de charger les données Iris dans une variable iris_data, puis nous allons déterminer un format de sortie de ce jeu de données.

Maintenant nous allons utiliser cette procédure pour charger les données Iris dans notre table SQL.

-- Ajout des données dans la table SQL

insert into Iris ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species")

exec dbo.get_iris_dataset;

select top (20) * from Iris;

go

Ci-dessous un exemple du résultat

Intégration avec Reporting Services

Nous venons juste de voir une intégration simple avec le moteur SQL. Maintenant, ça pourrait être sympas d’utiliser les représentations graphiques de R directement dans Reporting Services.

Pour ce faire, il reste un peu de travail à faire côté moteur Sourire !!!

Création d’une procédure stockée pour invoquer la libraire ggplot2

Dans notre base de données, nous allons créer une nouvelle procédure stockée. Cette procédure va invoquer la librairie ggplot2 sur les données Iris, puis placer le résultat visuel dans une sorte d’image, qui sera ensuite exploitée par Resporting Service.

Ci-dessous la procédure stockée :

create procedure get_iris_plot

as

begin

-- Demonstrate how to generate plots from R & return to any SQL client:

execute sp_execute_external_script

@language = N'R'

, @script = N'

library(ggplot2)

library(gridExtra)

image_file = tempfile();

png(filename = image_file, width=1000, height = 500);

theme_set(theme_gray(base_size = 15))

data_to_plot <- iris

# Création de la représentation graphique avec le librairie ggplot2

p1 <- qplot(Sepal.Length, Petal.Length, data = iris, color = Species, size = Petal.Width, alpha = I(0.7))

print(grid.arrange(p1, ncol=1))

dev.off();

OutputDataSet <- data.frame(data=readBin(file(image_file, "rb"), what=raw(), n=1e6));

'

, @input_data_1 = N''

with result sets ((plot varbinary(max)));

end;

go

Pour vérifier, la procédure stockée « get_iris_plot » doit apparaître dans SSMS :

Création du rapport Reporting Services

Création de la “Data source”

Dans Report Builder, faîtes un clic droit sur « Data Sources » et cliquez sur « Add Data Source »

Renseignez les différents champs pour se connecter au server SQL.

Cliquez sur le bouton « Build » pour renseigner les informations de connexion vers votre base de données :

Cliquez sur le bouton « Test Connection » pour vérifier la connexion sur la base de données.

Création du dataset

Faîtes un clic droit sur « Datasets », puis cliquez sur « Add dataset ».

Dans le fenêtre « Dataset Properties » renseignez les champs pour se connecter à la « datasource » configurée précédemment. Sélectionnez « Stored Procedure » et dans la liste déroulante, sélectionnez la procédure stockée qui invoque le graphique R ggplot2 (dans notre cas “get_iris_plot” ). Puis cliquez sur le bouton « OK ».

Voici ce que vous devez obtenir dans Report Builder :

Création du rapport

Nous allons maintenant créer un rapport qui va présenter le graphique plot que l’on a vu en début d’article.

Pour ce faire, nous allons rajouter un composant image.

Dans le ruban, cliquez sur le menu « Insert » puis cliquez sur « Image »

Déposer le composant comme vous le souhaitez. La fenêtre de propriété apparaît

Le but ici est d’invoquer la procédure stockée du dataset. Renseignez les différents champs comme présenté dans la copie d’écran ci-dessous, puis cliquez sur le bouton « Ok » :