show/hide menu
Uncategorized

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

0

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 »

clip_image002

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 »

clip_image004

Exécutez la commande suivante :

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

Une visualisation de type plot apparaît alors dans RStudio

clip_image006

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

clip_image008


 

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 »

clip_image009

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

clip_image011

Création de la table

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

clip_image013

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]

GO

Cliquez sur le bouton « Execute »

clip_image014

Vous devez avoir normalement quelque chose comme ça :

clip_image015

 

 

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

clip_image017

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 :

clip_image018

 

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.

clip_image020

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

clip_image021

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

clip_image023

Voici ce que vous devez obtenir dans Report Builder :

clip_image025

 

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 »

clip_image027

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

clip_image028

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 » :

clip_image029

Cliquez sur le bouton « Run » pour vérifier le bon fonctionnement du rapport

clip_image030

Ci-dessous, le résultat Sourire :

clip_image032

Et voici le rapport une fois publié :

clip_image034

Au fait, la réponse à la question du titre, c’est Sam ! Sourire

Franck Mercier

Sessions de formation gratuites :

Windows Server  :

Evaluation SQL Server 2014 :

Evaluation SQL Server 2016 CTP2 :

Evaluation Power BI :

Testez Azure gratuitement pendant un mois :

articles liés

Débuter avec les conteneurs Linux, Windows, Docker, Azure – partie 3 – Déployer Docker dans Azure

Débuter avec les conteneurs Linux, Windows, Docker, Azure – partie 3 – Déployer Docker dans Azure

Machines virtuelles Linux dans Microsoft Azure – vidéo et ressources utiles

Machines virtuelles Linux dans Microsoft Azure – vidéo et ressources utiles

Plan de reprise d’activité avec Hyper-V Replica

Plan de reprise d’activité avec Hyper-V Replica

Les commentaires sont fermés.