Utiliser un model ML entrainé en R avec PowerBI

Cet article fait suite au précédent article dans lequel j’explique comment envoyer un model entrainé dans Azure ML. Le principe sera plus ou moins le même ici sauf qu’il s’applique cette fois-ci à Power BI Smile .

Nous allons donc tout d’abord créer un modèle entrainé en R et l’enregistrer sur le disque. Reprenons par exemple le modèle de classification d’iris :

library(mda)

data(iris)

fit <- mda(Species~., data=iris)

save(fit, file = "C:/model.rda")

Assurez vous que les librairies R utilisées soient bien installées (la librairie utilisée dans cet exemple est la librairie “mda”).

Une fois le modèle enregistrer sur le disque, nous allons passer à la partie Power BI. Pour rappel, depuis quelques mois Power BI permet d’intégrer du code R à plusieurs endroits :

1 – Dans les requêtes :

  • En source de données

R_source_PowerBI

  • En étape de transformation

R_transform_PowerBI

2 – Dans les visualisations via “l’élément visuel de script R”

R_visual_PowerBI

Dans cet article, nous allons utiliser le mode étape de transformation R, vous vous demandez sans doute pourquoi ? Parce qu’il permet, contrairement aux autres modes source et visualisation, d’appliquer un traitement R sur n’importe quelle source Power BI (base de données, fichiers, R source…), puis d’utiliser tous les éléments visuels Power BI sur le jeu de données (natifs, custom, R visual…). Voici le processus :

    1. Tout d’abord ouvrir Power BI Desktop et charger les données à scorer dans une requête :
      R_PowerBI_Req1
    2. Cliquer ensuite sur “Exécuter un script R” dans l’onglet “Transformer” puis copier le code R qui charge le modèle et score les données :

      library(mda)

      load("C:/model.rda")

      pred <- predict(fit, dataset)

      out <- cbind(dataset,pred)

      out

      R_PowerBI_Req2

    3. Cliquer sur OK puis renseigner le niveau confidentialité (le niveau de confidentialité doit être le même pour les deux sources, “Public” par exemple) : PowerBI_niveau_confidentialite
    4. La requête listera en suite les dataframe présents dans le code R, cliquer sur “Table” en face du dataframe “out” pour naviguer dans le résultat du script : R_PowerBI_Req3
    5. Le jeu de données avec la colonne de prédiction apparaît alors : R_PowerBI_Req4

Vous n’avez plus qu’à enregistrer votre requête, puis réaliser les visuels qui vous intéressent. Par exemple une petite matrice de confusion :

R_PowerBI_Visual

Cette démo prouve que cela fonctionne en mode desktop. Prochaine étape : tester cela en mode Power BI Services (en ligne), et surtout que le rafraichissement des données fonctionne aussi… affaire à suivre !

Lancer un Batch Azure ML en PowerShell

Dans Azure ML, lorsque l’on veut scorer un important jeu de données de façon automatisé, il est recommandé d’utiliser le web service en mode “Batch”, plutôt que le mode “Request” (qui engendre beaucoup trop d’appel). La documentation des web services propose des exemples de codes en C#, R et Python… ce qui est très bien. Cependant, il manque le langage le plus important lorsque l’on cherche à scripter une tâche dans un univers Windows : le PowerShell !!!!! 

Avec le PowerShell, plus besoin d’installer Python/R sur le serveur ou de développer une appli console en C#, il est nativement installé dans les OS Windows. Un petit script “.ps1”, une ligne de commande, et le tour est joué. Pratique si l’on souhaite utiliser l’Agent SQL Server comme ordonnanceur par exemple.

Je me suis donc fortement inspiré du script Python. En voici la version PowerShell :

#copier la clé de l'API
$key='…'

#copier l'uri du mode batch (qui se termine par ‘/jobs’) sans la partie '?api-version=2.0'
$uri = '…'

#Soumet le job
$url = $uri + '?api-version=2.0'

$headers = @{'Authorization'='Bearer ' + $key}

#Renseigner le body si besoin..
$json ='{}'

$out_submit = Invoke-WebRequest -Uri  $url -Headers $headers -Method POST -ContentType 'application/json' -Body $json -TimeoutSec 0


#Demarre le job
$url = $uri + "/" + $out_submit.Content.Replace('"','') + "/start?api-version=2.0" 

$url

$out_start = Invoke-WebRequest -Uri $url -Headers $headers -Method POST -ContentType 'application/json'  -TimeoutSec 0

$out_start

#Boucle qui verifie le statut du job
$url = $uri + "/" + $out_submit.Content.Replace('"','') + "?api-version=2.0" 

$url

$StatutCode = ""
while (1 -eq 1) {
	$out_status = Invoke-WebRequest -Uri $url -Headers $headers  -TimeoutSec 0

	$res = $out_status.Content | ConvertFrom-Json
	$StatutCode = $res.StatusCode

	if  ($res.StatusCode -eq 0 -or $res.StatusCode -eq "NotStarted"){
		write-host "Job " $out_submit.Content.Replace('"','') " not yet started..."
	} 
	if  ($res.StatusCode -eq 1 -or $res.StatusCode -eq "Running"){
		write-host "Job " $out_submit.Content.Replace('"','') " running..."
	} 
	if  ($res.StatusCode -eq 2 -or $res.StatusCode -eq "Failed"){
		write-host "Job " $out_submit.Content.Replace('"','') " failed!"
		write-host "Error details : " $res.Details
	break
	} 
	if  ($res.StatusCode -eq 3 -or $res.StatusCode -eq "Cancelled"){
		write-host "Job " $out_submit.Content.Replace('"','') " cancelled!"
		break
	} 
	if  ($res.StatusCode -eq 4 -or $res.StatusCode -eq "Finished"){
		write-host "Job " $out_submit.Content.Replace('"','') " finished!"
		write-host "Result : " $res
		break
	} 
	Start-Sleep -Seconds 2
}

Il est bien sur possible de créer le même type de script pour le mode “Request”, mais à part pour du debug, j’y vois beaucoup moins d’intérêt.

Petit test d’incrustation d’un rapport Power BI

Depuis quelques jours, il est possible de partager ses rapports Power BI sur le web, cf. le lien suivant qui vous explique comment faire exactement : https://powerbi.microsoft.com/fr-fr/documentation/powerbi-service-publish-to-web/

Cette fonctionnalité est en preview et ne semble pas être activée pour les comptes trial.

Voila ce que ca donne :

Correspondances entre SQL et R

Je ne sais pas si vous avez remarqué, mais le langage R commence à apparaître vraiment partout dans l’écosystème Microsoft depuis le rachat de Revolution Analytics : Power BI Desktop, SQL Server 2016, Azure ML…

Si vous êtes consultant BI, Data Analyste… plutôt à l’aise avec le SQL, et que vous vous intéressez au R, je vous propose une petite table de correspondances de syntaxe entre ces 2 langages pour quelques actions élémentaires.

Prérequis pour mieux comprendre ce tableau, la colonne “R” du tableau traite des objets de type “dataframe”. Le “dataframe” est un des formats de stockage de données dans R. Si vous ne savez pas du tout ce que c’est, je vous conseille l’article suivant : http://ww2.coastal.edu/kingw/statistics/R-tutorials/dataframes.html

Voici le tableau de correspondances :

Action

SQL

R

Afficher toutes les lignes et colonnes de la table

select * from tb

tb

Affectation, stocker un résultat dans un objet

select * into tb2 from tb1

tb2 <- tb1

Filtrer des lignes en fonction de valeurs de colonnes

select * from tb where col1 = 1 and col2 is not null or col3 <> 3

tb[tb$col1 == 1 & !is.na(tb$col2) | tb$col3 != 3,]

Filtrer les 5 premières lignes

select top 5 * from tb

tb[1:5,]

Afficher certaines colonnes de la table

select col1, col2 from tb

tb[,c('col1','col2')]

Regrouper des lignes en agrégeant une colonne

select AVG(col1) from tb group by col2

aggregate(col1, by=list(col2), FUN=mean)

Compter le nombre de lignes par regroupement

select count(*) from tb group by col

data.frame(table(tb$col))

Faire un union de 2 tables (ajouter les lignes des 2 tables ensemble)

select col1, col2 from tb1 union all select col1, col2 from tb2

rbind(tb1,tb2)

Faire une jointure interne

select * from tb1 inner join tb2 on tb1.pktb1 = tb2.fktb2

merge(tb1, tb2, by.x = "pktb1", by.y = "fktb2")

Faire une jointure externe gauche

select * from tb1 left join tb2 on tb1.pktb1 = tb2.fktb2

merge(tb1, tb2, by.x = "pktb1", by.y = "fktb2",all.x = TRUE)

Concaténer des chaines de caractères

select 'hello' + ' world'

paste0("hello", " world")

Conversion de texte en date

select convert(date,'2015-03-27', 120)

as.Date("2015-03-27")

Conversion de texte en datetime

select convert(datetime,'2015-03-27 01:30:00', 120)

as.POSIXct(strptime("2015-03-27 01:30:00", "%Y-%m-%d %H:%M:%S"))

Supprimer une table

drop table tb

rm('tb')

Une autre solution est d’utiliser le package R sqldf qui permet de requêter des objets R en SQL, par exemple :

install.packages("sqldf")
library(sqldf)
sqldf("select * from iris")

https://cran.r-project.org/web/packages/sqldf/index.html

http://www.r-bloggers.com/make-r-speak-sql-with-sqldf/

Il existe bien sûr plusieurs façons de faire toutes ces actions en R ou en SQL, et je vous conseille de pousser vos recherches en regardant par exemple les liens suivants :

http://www.statmethods.net/

https://stat.ethz.ch/R-manual/

Comment envoyer un modèle déjà entrainé dans Azure ML et l’utiliser via Web Service

Le contexte de cet article est le suivant :

  • Vous voulez utiliser un modèle entrainé (de type classification, régression…) via un web service d’Azure ML
  • Vous avez des données sensibles TOP SECRET et vous ne voulez pas les envoyer dans le cloud Azure

Il existe alors une solution pour vous si vous vous s’y connaissez en R, voici la procédure :

  1. Entrainer votre modèle dans un script R en local à partir de vos jeux de données. Par exemple, voici comment créer un modèle entrainé de classification à partir du dataset iris :
  2. library(mda)
    data(iris)

    fit <- mda(Species~., data=iris)

    Le modèle est alors stocké dans l’objet R appelé “fit”

  3. Enregistrer le modèle dans un fichier “.rda” avec la commande R suivante :

    save(fit, file = "C:/model.rda")

  4. Zipper le fichier model.rda en model.zip et uploader le zip dans un dataset dans Azure ML :
    • snip_20160214121005
  5. Créer une nouvelle expérimentation Azure ML avec la configuration suivante :
    • Premier composant contenant une simple ligne de test avec les bonnes features et formats en entrée du modèle. Pour simplifier les choses, j’ai utilisé un script R qui envoie dans le data frame de sortie la première ligne du dataset IRIS :
    • data.set = iris[1,1:4]

      maml.mapOutputPort("data.set");

      Ce composant servira juste à la validation de l’expérimentation.

    • Le dataset “model.zip” que nous venons de créer
    • Et le plus important, un composant de script R qui fera l’équivalant d’un SCORE, avec en entrée Dataset1 le composant contenant la ligne de features et en entrée Script Bundle (zip) le dataset model.zip. Ce composant va charger le modèle, l’utiliser et envoyer la prédiction dans le dataset de sortie via le code R suivant :
    • dataIN <- maml.mapInputPort(1) # class: data.frame

      load("src/model.rda");
      library(mda)


      predictions <- predict(fit, dataIN)

      data.set <- data.frame(dataIN, predictions)
      maml.mapOutputPort("data.set");
  6. Faire ensuite un Run de l’expérimentation pour s’assurer que tout fonctionne correctement, et ensuite passer en mode web service en mettant l’input et l’output web service en entrée et sortie du script R de SCORE :
    • snip_20160214121617

      Et enfin, déployer et utiliser le Web Service !

      Nous arrivons donc avec ce procédé à utiliser un model en mode web service d’Azure ML sans l’entrainer dans Azure ML.

      Pour plus d’infos, des exemples de scripts R sur la classification du dataset Iris : http://machinelearningmastery.com/non-linear-classification-in-r/ 


Liaison directe entre Power Query et Power Pivot avec Excel 2010

Pour info, cet article est un repost de l’article que j’ai écrit il y a quelques mois sur le blog de Jean-Pierre Riehl : http://blog.djeepy1.net/2015/03/18/liaison-directe-entre-power-query-et-power-pivot-avec-excel-2010/

Power BI étant un des meilleurs outils de self-service BI, il est assez rare de travailler chez des clients possédant déjà des licences Excel 2013 voir Office 365 + Power BI. Il faut savoir que Power Query et Power Pivot sont aussi compatibles et fonctionnent plutôt bien avec Excel 2010 en 32 et 64 bit.

Seulement comparé à 2013, le chargement des données d’une requête PQ vers une table PP n’est pas aussi évidant avec 2010, en effet le bouton « charger dans le modèle de données » n’existe pas !

clip_image002

Mais il est possible de charger les données des requêtes directement dans PP sans passer par un chargement des données dans un onglet. Pour cela, une fois que la requête à charger est créée, il faut :

Décocher le chargement dans la feuille de calcul (non obligatoire mais conseillé pour réduire la taille du fichier Excel)

Ouvrir la fenêtre Power Pivot

clip_image004

Aller dans l’onglet « Conception » puis cliquez sur « Connexions existantes »

clip_image006

Sélectionner la requête Power Query qui apparait dans liste « Connexions au classeur » et cliquer sur ouvrir

clip_image008
Et ensuite le process est le même que pour un ajout de table Power Pivot classique Suivant -> Suivant -> Terminer … A la fin de l’importation des données, cliquer sur « Fermer »

clip_image010

La table apparait alors dans l’interface.

Mon test a été effectué avec les versions :

· Excel 2010 Professionnel Plus 14.0.7128.5000 64 bits

· Power Query v2.11

· Power Pivot version 11.0.3000.0

2 717 607 lignes ont été chargées sans problème.

Par contre pour rafraichir les données, il faut utiliser le bouton « Actualiser » de la fenêtre Power Pivot :

clip_image012

La modification des requêtes source Power Query (modification du code M) entrainent une erreur lors du rafraichissement de la table.

“OLE DB or ODBC error: The query ‘Requete’ or one of its inputs was modified in Power Query after this connection was added. Please remove and re-add the connection. This can be done by disabling and re-enabling download of ‘Requete’ in Power Query..”

Pour corriger ce problème :

http://www.powerpivotpro.com/2014/07/i-modified-an-existing-table-in-power-query-and-now-it-wont-refresh-a-fix/

Newer posts