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