Power BI + Elasticsearch !!

Elasticsearch est un moteur open source NoSQL proposé par l’entreprise Elastic basé sur la technologie Apache Lucene : https://www.elastic.co/fr/products/elasticsearch

Il est très simple à installer et à utiliser. Si vous ne connaissez pas du tout Elastisearch, n’hésitez pas à suivre ce rapide tuto avant de continuer cet article : http://joelabrahamsson.com/elasticsearch-101/

Pour résumer, Elasticsearch permet de stocker des documents Json dans ce qu’on appelle des index, puis de les requêter via web service HTTP REST.

Et le top, c’est que Http REST + Json fait de Power BI un client nativement compatible avec Elasticsearch ! En effet, Power BI peut appeler des web services Http REST grâce à la fonction Web.Contents(), puis récupérer le résultat de la requête en Json et le parser en dataset (lignes-colonnes).

Principes de requêtage avec Elasticsearch

Pour l’article j’ai stocké 20 606 tweets contenant le mot “car” (voiture en anglais) dans un index appelé “test-index” (très mal nommé Smile ) :

Kopf_index

(J’utilise ici le plugin Koft pour visualiser mes index et administrer mon cluster Elasticsearch => https://github.com/lmenezes/elasticsearch-kopf)

Mon cluster Elasticsearch tourne en local et est accessible sur le port 9200 via l’url http://localhost:9200/. Pour récupérer les tweets, je vais ajouter le nom de mon index à l’url et utiliser la méthode “_search”. J’ajoute aussi “?pretty” pour mettre en forme le résultat. En copiant http://localhost:9200/test-index/_search?pretty dans mon navigateur, s’affichent des résultats sous forme de Json. Si l’on regarde bien je n’obtiens que 10 tweets sur les 20 606.

ES_search

Afin de récupérer plus d’éléments, nous allons utiliser les paramètres from (à partir de quel élément) et size (nombre d’éléments à récupérer), et faire plusieurs appels (requêtage par “page”). Par exemple, pour afficher 50 éléments par lot de 10, nous allons faire 5 appels avec les paramètres from et size suivants :

from size url
0 10 http://localhost:9200/test-index/_search?from=0&size=10
10 10 http://localhost:9200/test-index/_search?from=10&size=10
20 10 http://localhost:9200/test-index/_search?from=20&size=10
30 10 http://localhost:9200/test-index/_search?from=30&size=10
40 10 http://localhost:9200/test-index/_search?from=40&size=10

Il aurait été plus simple de faire qu’un seul appel avec size = 20 606, sauf que size est limité à 10 000. Nous allons donc devoir faire au moins 3 appels pour récupérer tous les tweets (10 000 + 10 000 + 606).

Attention ! Par défaut il y a une règle sur l’index qui empêche les paramètres size + from de dépasser 10 000. Pour y remédier, il faut modifier le paramètre “max_result_window” de l’index et lui affecter une plus grande valeur, avec une requête PUT :

PUT /test-index/_settings
{
    "index": {
        "max_result_window" : "1000000000"
        
    }
}

 

ES_max_result_window

Nous allons aussi ajouter dans notre appel REST la possibilité d’envoyer un body qui contiendra la query DSL (l’équivalent du SQL pour Elasticsearch), plus d’infos : https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl.html

2 petits exemple de query DSL

  • renvoie tout :
POST test-index/_search
{
   "query": {
     "bool": {
       "must": [
         {
           "match_all": { }
         }
       ]
     }
   }
}
  • recherche le mot “mustang” dans le texte des tweet :
POST test-index/_search
 {
  "query": {
     "bool": {
       "must": [
         {
           "match": {
             "text": "mustang"
           }
         }
       ]
     }
   }
 }

Utilisons Power BI

Nous allons créer dans Power BI Desktop une requête vide.

PowerBI_Requete_vide

Copier ensuite la requête M suivante dans l’éditeur avancé :

let
    url = "http://localhost:9200/test-index/_search",
    req = "{""query"": {""bool"": {""must"": [{""match_all"": {}}]}}}",

    totalnb = Json.Document(Web.Contents(url,[Content=Text.ToBinary(req)]))[hits][total],
    max_res = 10000,
    nb_page_temp = Int32.From(totalnb/max_res),
    rest = Number.Mod(totalnb , max_res ),

    nbpages = if Number.Mod(totalnb , max_res ) > 0 then nb_page_temp + 1 else nb_page_temp,

    Source = List.Generate(()=>nbpages, each _ > 0, each _ - 1),
    #"Converti en table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Colonnes renommées" = Table.RenameColumns(#"Converti en table",{{"Column1", "page"}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Colonnes renommées", "req", each Json.Document(Web.Contents(url & "?from=" & Text.From(([page]-1)*max_res) & "&size=" & Text.From(max_res),[Content=Text.ToBinary(req)]))),
    #"req développé" = Table.ExpandRecordColumn(#"Personnalisée ajoutée", "req", {"hits"}, {"req.hits"}),
    #"req.hits développé" = Table.ExpandRecordColumn(#"req développé", "req.hits", {"total", "max_score", "hits"}, {"total", "max_score", "hits"}),
    #"hits développé" = Table.ExpandListColumn(#"req.hits développé", "hits"),
    #"hits développé1" = Table.ExpandRecordColumn(#"hits développé", "hits", {"_index", "_type", "_id", "_score", "_source"}, {"_index", "_type", "_id", "_score", "_source"}),
    #"_source développé1" = Table.ExpandRecordColumn(#"hits développé1", "_source", {"contributors", "truncated", "text", "is_quote_status", "in_reply_to_status_id", "id", "favorite_count", "source", "retweeted", "coordinates", "timestamp_ms", "entities", "in_reply_to_screen_name", "id_str", "retweet_count", "in_reply_to_user_id", "favorited", "user", "geo", "in_reply_to_user_id_str", "possibly_sensitive", "lang", "created_at", "filter_level", "in_reply_to_status_id_str", "place", "retweeted_status", "display_text_range", "extended_entities", "quoted_status_id", "quoted_status", "quoted_status_id_str", "extended_tweet"}, {"_source.contributors", "_source.truncated", "_source.text", "_source.is_quote_status", "_source.in_reply_to_status_id", "_source.id", "_source.favorite_count", "_source.source", "_source.retweeted", "_source.coordinates", "_source.timestamp_ms", "_source.entities", "_source.in_reply_to_screen_name", "_source.id_str", "_source.retweet_count", "_source.in_reply_to_user_id", "_source.favorited", "_source.user", "_source.geo", "_source.in_reply_to_user_id_str", "_source.possibly_sensitive", "_source.lang", "_source.created_at", "_source.filter_level", "_source.in_reply_to_status_id_str", "_source.place", "_source.retweeted_status", "_source.display_text_range", "_source.extended_entities", "_source.quoted_status_id", "_source.quoted_status", "_source.quoted_status_id_str", "_source.extended_tweet"}),
    #"_source.user développé" = Table.ExpandRecordColumn(#"_source développé1", "_source.user", {"name", "profile_image_url"}, {"_source.user.name", "_source.user.profile_image_url"})
in
    #"_source.user développé"

Remplacer les valeurs des variables url et req par votre url de votre cluster ES et votre query DSL (en doublant les « ).

    url = "http://localhost:9200/test-index/_search",
    req = "{""query"": {""bool"": {""must"": [{""match_all"": {}}]}}}",

Le principe de la requête est le suivant :

  1. l’étape totalnb va requêter l’index pour récupérer le nombre total d’éléments retournés par la requête :   ES_search_nbtotal
  2. le nombre d’appels nécessaires (pages) va être calculé dans la variable nbpage (3 dans mon exemple).
  3. une liste est ensuite générée avec le décompte de 1 à nbpage des pages à réaliser : PowerBI_ES_Page
  4. on ajoute une colonne calculée qui va, pour chaque page, exécuter l’appel REST avec les bonnes valeurs des paramètres from et size : PowerBI_ES_Page_requete
  5. Il ne reste plus qu’à naviguer dans la structure du Json en cliquant sur les boutons développer PowerBI_Developper_bouton, aller rechercher les attributs nécessaires et réaliser le rapport : PowerBI_ES_Rapport On peut d’ailleurs vérifier dans le tableau en haut à gauche que les 3 pages ont bien été chargées avec 10 000, 10 000 et 606 éléments.

C’est plutôt simple à réaliser, et le parsing Json de Power BI est l’un des plus intuitifs que je connaisse.

Le gros plus: le rafraichissement de données fonctionne une fois le rapport déployé sur le portail Power BI Services ! Il utilisera la Gateway d’Entreprise si le cluster est on premise, ou alors pourra directement se connecter dessus si le cluster est ouvert sur internet (attention !) ou par exemple hébergé sur le cloud Elastic avec le module de sécurité Shield.

En bonus, la requête M fonctionne aussi dans Excel avec son module de requête (anciennement Power Query), ce qui peut être pratique pour de l’export de données par exemple.Excel_ES_Page_requete

Cette requête permet de s’affranchir de la limitation des 10 000 éléments, mais attention le but n’est pas d’exporter l’ensemble des données d’un index dans un rapport Power BI ! Il est recommandé d’ensuite utiliser les fonctions d’agrégations d’Elasticsearch (https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations.html) pour ramener moins de données et optimiser les temps de réponses, exactement comme on le ferait avec un SELECT GROUP BY et une base de données SQL classique.

Afficher un message d’erreur personnalisé dans la visualisation R de Power BI

Par défaut dans Power BI, lorsqu’un élément visuel de script R plante, un message d’erreur apparaît dans la zone du graphique avec la possibilité d’afficher les détails techniques de l’erreur :

PowerBI_R_error_thumb4

Cela peut être pratique pour débugger son code R lors du développement du rapport. Mais dans certains cas, les graphiques R peuvent fonctionner sur un ensemble de données, puis ne plus fonctionner dans d’autres cas lorsque l’on filtre dynamiquement les données (via des filtres dans le rapport).

Par exemple, une prédiction réalisée avec la librairie R forecast, prédisant les ventes de produits avec une saisonnalité à la semaine, peut planter s’il y a moins de 2 périodes dans le jeu de données. Le script R fonctionnera avec certains produits, mais il plantera pour ceux qui ne justifient pas de la condition de la fonction de forecast. Dans ce cas la on préférera afficher un message customisé (par exemple “Nous sommes désolé mais le forecast n’est pas disponible pour ce produit”) à l’utilisateur plutôt qu’une erreur technique R.

Nous allons donc utiliser 2 principes de codes R :

  • Un try catch pour catcher l’erreur technique. Vous noterez que contrairement à beaucoup d’autres langages, le try catch de R fonctionne sous forme d’une fonction tryCatch() :

result = tryCatch({

#code à réaliser

}, warning = function(w) {

#ce qui se passe en cas de warning

}, error = function(e) {

#ce qui se passe en cas d’erreur

}, finally = {

#ce qui se passe à la fin dans tous les cas

})

  • Un bout de code affichant un plot avec un message. L’idée est de créer une zone de graphique vide avec par() et plot(), puis d’y afficher un texte avec la fonction text() :

par(mar = c(0,0,0,0))

plot(c(0, 1), c(0, 1), ann = F, bty = 'n', type = 'n', xaxt = 'n', yaxt = 'n')

text(x = 0.5, y = 0.5, paste("Texte du message"), cex = 1.6, col = "black")

Voici le code complet :

result = tryCatch({

  #bout de code R

}, error = function(e) {

  par(mar = c(0,0,0,0))

  plot(c(0, 1), c(0, 1), ann = F, bty = 'n', type = 'n', xaxt = 'n', yaxt = 'n')

  text(x = 0.5, y = 0.5, paste("Nous sommes désolé mais le forecast n’est pas disponible pour ce produit"),

        cex = 1.6, col = "black")

})

Ce qui donne :

PowerBI_R_error_catch_thumb2

Plus d’infos sur le try catch en R : http://mazamascience.com/WorkingWithData/?p=912

Afficher une simple table dans la visualisation R de Power BI

Pour afficher une table avec notre jeu de données, il est possible d’utiliser le package R gridExtra et sa fonction grid.table(). C’est très simple à utiliser, il suffit de créer un élément visuel de script R, faire glisser les champs du modèle dans l’élément, puis de copier les lignes de code suivantes dans la partie code :

library(gridExtra)

grid.table(dataset[1:10,])

PowerBI_Table_Plot_zoom_thumb

PowerBI_Table_Plot_thumb2

Cet affichage ne semble pas adapté aux tables avec plus d’une dizaine de lignes, c’est pourquoi je n’affiche que les 10 premières lignes du dataset (dataset[1:10,]). A vous de trier le jeu de données pour afficher un TOP 10 par exemple.

Plus d’infos ici :

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/