Power Query l’ETL « Power-User » dans l’ombre de SSIS

Power Query l’ETL « Power-User » dans l’ombre de SSIS

Comparatif Power Query / SSIS

Power Query atouts et faiblesses

Initialement lancé sous le nom de Data Explorer, le mini ETL  Power Query de la suite Power BI s’est amélioré et la version actuelle (2.9.3547.162) apporte quelques nouveautés.

Dans le cadre d’un projet Power BI dont les données sont récupérées d’une liste SharePoint, j’ai eu l’occasion d’utiliser ou essayer d’utiliser devrais-je dire, certaines de ces transformations.

La nouvelle version de Power Query propose beaucoup de nouvelles transformations, elles sont principalement accessibles via une fenêtre d’édition de formules d’où un script est généré.

Elles sont classées dans différentes catégories en fonction du type de transformation.

La première chose que nous remarquons, est que cette dernière ne donne pas beaucoup d’options et applique même parfois la transformation avec une valeur par défaut. Je pense à la transformation    Image       qui enlève les doublons en prenant comme référence la première colonne de la table.

Notons au passage que pour chaque transformation, une requête est générée en Langage M.

Du coup pour personnaliser une transformation il faudra être courageux et aller plonger dans la requête susnommée.

 

1)    Power Query : Dans les faits / Dans le M

Les transformations offertes sont multiples et comme annoncé plus haut, chacune d’elle génère du code en langage M.

Prenons comme exemple la transformation Image

Celle-ci permet de séparer une colonne en plusieurs en utilisant un délimiteur spécifique.

Dans notre cas nous utiliserons le caractère « Espace » comme délimiteur pour séparer une colonne (Created By1) contenant le nom et le prénom d’une personne dans deux colonnes distinctes.

Image

Image

Power Query nous propose plusieurs délimiteurs ainsi que la position à prendre en compte.

Image

Au final nous obtenons le résultat attendu d’une manière très simple.

Il faut l’avouer, la mise en œuvre des transformations fait partie des forces de Power Query.

Sauf que pour aller, plus loin il faudra regarder ce qui se cache derrière.

Et derrière se cache une requête écrite en M. En voici l’extrait correspondant à notre requête.

Image

Notons aussi qu’il est possible d’écrire des formules via un assistant, comme dans le cas de la construction d’une nouvelle colonne.

Contrairement à SSIS  avec le « derived column » par exemple où on a une fenêtre proposant la liste des fonctions disponibles avec une petite description, ici aucune aide n’est proposée.

La seule façon d’ajouter une formule ou une expression c’est de connaître celle-ci (un lien décrivant la liste des formules est disponible dans la partie référentielle).

Image

Chose non moins importante concernant la syntaxe des formules : elles sont sensibles à la casse.

Et c’est par un pur hasard que j’ai constaté cela, en essayant de mettre en place une formule avec IF. En mettant les mots clé en majuscules, nous avons une erreur pas très explicite il faut l’avouer.

Image

2)   Comparatif de certains composants entre Power Query et SSIS

Après un rapide tour des différentes transformations proposées dans Power Query, on se rend compte que la plupart de ces transformations sont disponibles dans SSIS mais de façon moins ergonomique.

Je prends comme exemple la transformation Image

Cette dernière est accessible sous SSIS via le composant « Flat File Source ».

De la même manière, la transformation    Image     présente dans l’interface de Power Query est disponible sous Intégration Services via  le composant « Aggregate ». 

En partant de ce constat, on s’aperçoit que Power Query fait abstraction des différents composants de SSIS qu’il faut mettre en place afin d’avoir accès à certaines transformations.

Il met ainsi à disposition ces transformations directement via l’interface.

Ci-dessous nous avons un tableau faisant l’analogie de certaines transformations entre Power Query et SSIS.

POWER QUERY

SSIS

Merge Merge Join
Unpivot Unpivot
Remove Duplicates Sort
Group By Group by (Aggregat)
Use First Row as Headers Column name in the First Data Row (Import Flat File)
Insert Custom Column Derived Column

Remarque :

Ce tableau n’a pas pour objectif l’exhaustivité mais juste pour exemple comparatif de quelques transformations.

3)   Possibilités offertes par Power Query

Les fonctionnalités de Power Query peuvent s’appliquer aussi bien sur les colonnes que sur les lignes d’une table.

Ainsi dans la suite nous classerons ces fonctionnalités en fonction des éléments sur lesquels ils s’appliquent.

a)     Réduction 

Dans cette section on trouve tout ce qui permet d’enlever une partie des lignes ou des colonnes de la table. Cette réduction peut se faire à plusieurs niveaux.

Image

 Image

Supprimer les n premières lignes de la table.

 Image Supprimer les colonnes sélectionnées de l’affichage et empêcher leur utilisation ultérieure.
 Image Supprimer les lignes alternées de la table en commençant par la première ligne à supprimer et en indiquant le nombre de lignes à supprimer et le nombre de lignes à conserver.
 Image Supprimer les lignes contenant des erreurs dans les colonnes actuellement sélectionnées.
 Image Supprimer toutes les lignes de la table dans lesquelles les valeurs dans les colonnes sélectionnées ont un doublon dans les valeurs antérieures. La ligne contenant la première occurrence d’un ensemble de valeurs n’est pas supprimée.
 Image Spécifier le nombre des premières lignes à conserver dans la table.
 Image Spécifier une plage de lignes commençant par une première ligne et incluant le nombre de lignes à conserver dans la table.

                                                                                                 

b)    Transformation

Les transformations disponibles ne sont pas encore nombreuses, mais la section reste prometteuse au vu de la facilité qu’elle apporte.

Image

 Image Une colonne de texte peut être divisée en plusieurs colonnes de deux façons :•            Par délimiteur•            Par nombre de caractères
Image Résumer les données par valeurs de ligne. Par exemple, avec les colonnes État, Ville et Population dans une table, on peut :

  • Effectuer un regroupement selon l’État et calculer le nombre de villes dans chaque État ou la somme de la population des villes pour obtenir la population de chaque État.
  • Effectuer un regroupement selon la ville et dénombrer les instances des noms de ville.
 Image Remplacer un élément dans la colonne par une autre valeur.
 Image
 Image Pour une valeur de texte, modifier le mode d’affichage des valeurs de la colonne :•            Minuscules•            MAJUSCULES•            Mettre en majuscules la première lettre de chaque mot•            SupprEspace•            Nettoyer•            JSON•            XML

Pour une valeur de type Date/heure, modifier le mode d’affichage des valeurs de la colonne :

•            Date

•            Heure

•            Jour

•            Mois

•            Année

•            Jour de la semaine

 Image Les noms d’en-tête de la table sont remplacés par les valeurs des cellules de la première ligne du tableau
 Image Transformer les colonnes sélectionnées en paires attribut-valeur.

c)     Création

Même constat que la section « Transformation », peu fourni, mais un travail agile peut être fait sur les colonnes.

 

Image

 ImageImage Insérer une nouvelle colonne après la dernière colonne de la table. Les valeurs dans la nouvelle colonne sont déterminées en sélectionnant le type de colonne à insérer :•            Personnalisé (vous définissez la formule qui calcule les nouvelles valeurs)•            Index
Image Crée une copie de la colonne sélectionnée à droite de la table. Le nom donné à la nouvelle colonne est Copie de <nom de la colonne>.

 

d)    Combinaison

Power Query permet de créer plusieurs venant de différentes sources, mais offre aussi la possibilité de les combiner afin d’enrichir son jeu de données.

Cette section en est l’illustration parfaite.

Image

Image Dans la grille Aperçu, créer une requête à partir de deux requêtes existantes. Un résultat de requête contient toutes les colonnes d’une table primaire, une colonne servant de colonne unique contenant un lien de navigation vers une table liée. Pour plus d’informations sur la fusion des requêtes.
Image Dans la grille Aperçu, créer une requête qui contient toutes les lignes d’une première requête suivie de toutes les lignes d’une deuxième requête.

4)  Nouveautés Power Query Décembre 2013 (Microsoft)

Beaucoup de nouveautés ont été introduites avec la mise à jour de Décembre.

Ci-dessous la description de ces nouveautés.

  • Power Query peut maintenant se connecter à plus de sources de données comme :
  • Sybase IQ
  • Exchange
  • Dynamics CRM Online
  • Quand vous sélectionnez plusieurs tables dans le panneau de navigation, Power Query détecte automatiquement les relations de base de données.
  • Le remplacement des requêtes partagées a été supprimé.
  • Vous pouvez maintenant partager votre requête à la volée.
  • De nouveaux boutons ont été ajoutés au ruban de l’éditeur de requête, à savoir Fill Down, Sort Ascending/Descending and Number Transformations.

5)   Références

Les Blogs

http://blogs.microsoft.fr/technet/power-query/feed

http://cwebbbi.wordpress.com/category/power-query/

http://devinknightsql.com/category/power-query/

Langage M (MSDN)

http://msdn.microsoft.com/en-us/library/dd285271.aspx

Descriptif des composants de Power Query

http://office.microsoft.com/en-us/excel-help/guide-to-power-query-context-menus-HA103993856.aspx?CTT=5&origin=HA104003813

Power Query UnPivot

http://office.microsoft.com/en-us/excel-help/unpivot-columns-HA104053356.aspx?CTT=5&origin=HA103993856

Power Query Liste des Formules

http://office.microsoft.com/en-us/excel-help/power-query-formula-categories-HA104122363.aspx?CTT=5&origin=HA104003813

Microsoft Power Query Preview for Excel Formula Language Specification

http://go.microsoft.com/fwlink/?LinkID=320633

 

Liste de mots clé  du langage M:

Ce tableau peut être bien utile au moment où on doute de l’existence d’une expression.

Comme le « switch » par exemple …. Il faudra faire sans.

Opérateurs unitaires NOT, IS
Constant expressions ERROR, FALSE, TRUE
Typage #BINARY, #DATE, #DATETIME, #DATETIMEZONE, #DURATION, #INFINITY, #NAN, #SECTIONS, #SHARED, #TABLE, #TIME
Opérateurs binaires AND, OR
Expressions conditionnelles IF, THEN, ELSE
Type assertion AS
Méta données META
Autre EACH, IN, LET, OTHERWISE, SECTION, SHARED, TRY

Cheikh SECK

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s