Tableau versus PowerPivot : Calcul de commissions

En ce moment je m’amuse beaucoup avec Tableau, et je pense avoir atteint le stade de la compétence consciente – ou bien je suis en pleine désillusion, on ne sait jamais vraiment… Mais à force de formation, de démonstrations, de maquettes, de prototypes et de webcasts (je vous recommande l’advanced training, « live online » tous les mercredi soir à 20h), je commence à me sentir bien avec le produit. La technique d’un côté plus la théorie de l’autre, je maîtrise mon processus de montée en compétence!

Tout ça pour dire que ça fait longtemps que je n’ai pas fait un petit article technique, et que désormais je me sens assez confiant sur Tableau pour me lancer. Evidemment c’est Tableau, donc « technique » c’est un bien grand mot. Avec ce soft on parle plus de recettes, d’astuces, que de code pur et dur. Vous allez voir !

Pour la problématique à traiter je suis parti trouver l’inspiration sur l’excellent blog PowerPivotPro, définitivement le meilleur blog sur PowerPivot à l’heure actuelle. Si d’habitude le blog est tenu par le merveilleux Rob Collie, pour le sujet qui nous intéresse l’auteur du jour est David Churchward. Un grand merci à eux pour cette source d’inspiration (et le classeur de données), et vous l’aurez compris mon objectif va être de résoudre le même cas métier qu’eux en utilisant Tableau plutôt que PowerPivot. Voici les liens vers la série d’articles qui nous intéresse : 1, 2 et 3, j’encourage vivement ceux qui savent ce que DAX veut dire d’aller les consulter, ne serait-ce que pour leur culture personnelle.

Le cas métier est le suivant : calculer les commissions des commerciaux selon un barème par tranche. Pour ce faire on utilisera deux méthodes : la prise en compte du taux maximal comme unique coefficient sur les ventes d’une part, et  d’autre part la ventilation de ce même calcul sur les tranches. Pas de panique, on va détailler ça.

Tout repose sur une table des taux de cette forme :

Illustration - Table des taux

Mes commerciaux sont commissionnés tous les mois. Chaque mois on va donc agréger leur chiffre des ventes par type de produit, puis déterminer la ligne de taux de commission applicable en fonction de sa date de validité et du montant des ventes réalisés. Vu qu’on est pas (complétement) pervers, on pose comme hypothèse qu’un taux ne peut pas changer dans un même mois.

Par exemple, en février 2012 Bob a vendu 80 000$ de café (ça en fait des dosettes) et 60 000$ de thé. Sur cette période (du 01/02/2012 au 29/02/2012), le taux applicable pour ce montant de café est 20% (deuxième ligne du tableau ci-dessus), le taux applicable pour ce montant de thé est 30% (dernière ligne du tableau).

A partir de là, deux méthodes de calcul sont possibles :

  • En taux maximal, Bob touchera 34 000$, soit 20% * 80 000 + 30% * 60 000
  • En ventilation par tranche, Bob touchera 15 000$ pour le café soit 10%*10 000 sur la première tranche plus 20%*70 000 sur la deuxième tranche. On ventile son résultat sur chaque taux intermédiaire plutôt que sur le taux maximal. Il touchera également 17 000$ pour le thé soit 10%*5 000 sur la première tranche plus 30%*55 000 sur la deuxième. Ce calcul est le même que pour nos impôts sur le revenu en fait. Avec cette formule Bob touchera au total 32 000$, évidemment moins qu’avec le taux maximal.

Voilà pour le fond du problème. Si vous avez besoin de plus d’explications, n’hésitez pas à consultez l’article original ou à poser des questions en commentaire.

D’un point de vue source de données, je vais brancher Tableau Desktop directement sur le classeur qu’utilise David Churchward (lien tout en bas de la page). Evidemment je pourrais exploiter le résultat de ses efforts et aspirer directement les infos de son tableau croisé dynamique final, une manière tout à fait convenable dans le quotidien, mais le but de cet article c’est de voir comment implémenter le calcul dans Tableau, donc je ne vais prendre que ses onglets sources. J’en profite pour rappeler que Excel+PowerPivot et Tableau Desktop sont très, très bons amis. Les limites de l’un étant très bien compensées par les forces de l’autre, et vice et versa. Mais c’est un sujet pour un autre jour !

Je dispose donc des éléments suivants :

  • Des commandes :

Source Excel : Orders

  • Des gens (pour faire joli):

Source Excel : People

  • Des types de produit (pour faire joli encore):

Source Excel : Type

  • Des taux par type, par période (From_Date, To_Date) et par tranche de montant (From, To) :

Source Excel : Taux

Vous noterez que pour le moment j’ai simplifié la problématique complète résolue par David Churchward. De son côté il calcule des commissions pour les commerciaux sur leurs ventes, et pour les managers en fonction du résultat de leurs commerciaux. De mon côté je ne calcule que les premières. A mon sens la mécanique est la même, si j’ai le temps je compléterai mais je ne vois pas de difficultés supplémentaires.

Je lance donc Tableau et je me connecte au fichier Excel (note pour les utilisateurs d’Excel 64bit, Tableau peut vous demander de télécharger un driver complémentaire, pas de panique, le message d’erreur est explicite et le driver est ici).

J’ouvre une nouvelle connexion de données de type Excel et je pointe vers le fichier source :

Première connection à Excel

On sent bien dans notre cas que tout le problème réside dans la jointure entre les taux (Rates) et les commandes (Orders). Mon premier réflexe ici est donc de monter 2 connexions de données, une contenant les commandes auxquelles je vais joindre les types de produits et les managers, et une seconde contenant les taux. Une fois les données chargées je pourrai toujours créer un champ calculé avec le langage d’expression de Tableau pour calculer mes commissions.

Roulez jeunesse, je me connecte aux commandes (Orders) et je joins les tables People et Types :

Première connection à Excel avec jointure

On repasse par l’écran d’accueil pour créer dans le même classeur Tableau une deuxième connexion en parallèle de la première :

Deuxième connection à Excel dans le même classeur

Et j’obtiens les champs tant attendus dans Tableau :

Zone de données dans Tableau

Je peux donc monter une première visualisation qui va mélanger mes données en faisant juste un drag and drop de mes champs… ou pas :

Tableau m'avertit que je fais une bétise

Evidemment ce n’est pas magique ! La jointure est complexe, et Tableau ne gère que les LEFT JOIN simples entre les datasets qui ont déjà été chargés.

Ok pour que ça ne marche pas tout seul, j’essaye donc de définir un champ calculé qui vaudrait le taux souhaité quand les conditions de jointures sont bonnes, et 0 sinon, mais sans grand succès.

En effet j’essaye de brasser des données qui n’ont pas du tout le même niveau de granularité. Ne serait-ce que par rapport au temps, dans mon dataset des commandes je suis au jour de la commande, du côté du dataset des taux ma granularité temporelle est la période de validité d’un taux (From/To), et j’attends un résultat au mois… Délicat.

Tableau est avant tout un outil de visualisation, s’il est tout à fait capable de faire des calculs à des niveaux d’agrégation intermédiaires sans difficulté, il nécessite que ces niveaux apparaissent dans la visualisation ! Alors prévoir tout ça dans une visualisation c’est certainement possible pour un jedi, mais pour le commun des mortels, c’est un peu trop velu. Il va falloir trouver une autre manière de faire.

Notez au passage que de son côté PowerPivot se régale de ce genre de problématiques. Son langage d’expression, le DAX, dispose d’une syntaxe qui permet « facilement » d’exprimer des calculs intermédiaires à différents niveaux d’agrégation. Mais on a déjà dit qu’on voulait traiter ce cas avec Tableau, alors insistons un peu !

Mais pas trop! Car heureusement Tableau a plus d’un tour dans son sac, dont un qui me plait particulièrement : il peut attaquer les fichiers Excel en SQL. Oui Monsieur, oui Madame ! Et le SQL c’est un peu mon premier amour, autant vous dire qu’à partir de là, pour moi, le problème se simplifie 🙂

On ferme la connexion sur les taux, elle ne sert à rien, et on modifie la connexion sur les commandes en passant en mode SQL :

Connection à un fichier Excel en SQL

Voici le SQL que j’ai bricolé :

SELECT
       YEAR([Orders$].[Date]) AS [Order_Year]
       ,MONTH([Orders$].[Date]) AS [Order_Month]
       ,[Orders$].[Salesman] AS [Salesman]
       ,[Orders$].[Type] AS [Type]
       ,[Types$].[Type_Name] AS [Type_Name]
       ,[People$].[Manager] AS [Manager]
       ,['Rates (2)$'].[From] AS [From]
       ,['Rates (2)$'].[To] AS [To]

       ,SUM([Orders$].[Value]) AS [Value]
       ,MAX(['Rates (2)$'].[Rate]) AS [Rate]

FROM ( ( [Orders$]
  INNER JOIN [People$] ON [Orders$].[Salesman] = [People$].[Name] )
  INNER JOIN [Types$] ON [Orders$].[Type] = [Types$].[Type_Code] )
  INNER JOIN ['Rates (2)$'] ON
             ([Orders$].[Type] = ['Rates (2)$'].[Type])
       AND ([Orders$].[Date] >= ['Rates (2)$'].[From_Date])
       AND ([Orders$].[Date] <= ['Rates (2)$'].[To_Date])

WHERE
       ['Rates (2)$'].[Rate_Group] = 'Salesperson'

GROUP BY
       YEAR([Orders$].[Date])
       ,MONTH([Orders$].[Date])
       ,[Orders$].[Salesman]
       ,[Orders$].[Type]
       ,[Types$].[Type_Name]
       ,[People$].[Manager]
       ,['Rates (2)$'].[From]
       ,['Rates (2)$'].[To]

Pour ceux que ça effraie, je vous en fais une relecture rapide (cliquez dessus pour le voir en grand):

Relecture du SQL utilisé

Le dataset ainsi composé renvoie les lignes suivantes:

Dataset retourné par la requête SQL sur le fichier Excel source

Vous  noterez qu’à ce moment précis mes données sont « fausses ». En effet le vendeur Adam sur le type AV sur janvier 2012 a ses 54239$ de ventes répétés 4 fois (4 premières lignes au-dessus). C’est complétement normal, on va corriger ça juste après.

Je me permets de faire vite fait sur les dates dans le SQL (juste Mois et Année) parce que je vais utiliser un champ calculé dans Tableau pour régénérer une vraie date complète, qui elle me permettra d’utiliser toutes les fonctions temporelles de l’outil :

Champ calculé Tableau : Mois des commissions

Maintenant que nous sommes dans Tableau, on va recalculer un montant des ventes correct, qui ignore la démultiplication qu’on a observé plus tôt. Pour se faire on va créer un champ calculé qui vaut 0 quand le montant des ventes n’est pas dans la tranche du taux, et ce même montant si c’est le cas. On va dé-multiplier la démultiplication :

Champ calculé Tableau : Ventes corrigées

Une fois qu’on a ce montant, calculer les commissions sur taux maximal devient enfantin :

Champ calculé Tableau : Commissions sur taux maximal

Je vous illustre ça tout de suite pour bien comprendre ce qu’on vient de faire :

Tableau : Illustration du calcul des commissions sur taux maximal

On part de Value qui est retourné par la requête SQL sur toutes les tranches. Sales est passé à 0 si Value n’est pas entre From et To. Et Commission vaut Rate que multiplie Sales. Pas mal non ?

De la même manière, on va calculer un montant de ventes qui lui sera ventilé sur les taux :

Champ calculé Tableau : Ventes par tranche de taux

Ok, celle-là est un peu plus sioux. Voilà l’opération réalisée ligne par ligne :

Illustration du calcul des ventes par tranche

Résultat qu’on utilisera pour calculer les commissions ventilées :

Champ calculé Tableau : Commission par tranche de taux

Et voilà ce que ça donne au niveau fin, pour bien comprendre la mécanique :

Tableau : Illustration du calcul des commissions par tranche

Enfin, voici le résultat final à niveau agrégé. Notez que pour les sales on peut utiliser les 2 mesures qu’on a calculé puisqu’elles s’agrègent correctement (sales et sales (tiered)), mais pas le value original, évidemment :

Tableau : visualisation complète

Pas si difficile que ça en fin de compte ? 🙂

Et pour ceux qui trouveraient que la syntaxe SQL et les champs calculés sont complexes, voici le DAX qui réalise la même chose côté PowerPivot :

=IF(COUNTROWS(VALUES(Dates[MonthEndDate]))=1
   &&COUNTROWS(VALUES(People[Name]))=1
   &&COUNTROWS(VALUES(Types[Type_Code]))=1,
     IF([Sales_Value]=BLANK(),BLANK(),
       CALCULATE([Sales_Value]-MAX(Rates[From]),
          FILTER(Rates,
             Rates[From]<=[Sales_Value]
             &&Rates[To]>=[Sales_Value]
             &&Rates[From_Date]<=MAX(Dates[MonthEndDate])
             &&Rates[To_Date]>=MAX(Dates[MonthEndDate])
             &&Rates[Type]=VALUES(Types[Type_Code])
             &&Rates[Rate_Group]=”Salesperson”
                 )
                )
        ),
    IF(COUNTROWS(VALUES(Dates[MonthEndDate]))=1
      &&COUNTROWS(VALUES(People[Name]))>1
      &&COUNTROWS(VALUES(Types[Type_Code]))=1,
       IF([Sales_Value]=BLANK(),BLANK(),
         CALCULATE([Sales_Value]-MAX(Rates[From]),
          FILTER(Rates,
             Rates[From]<=[Sales_Value]
             &&Rates[To]>=[Sales_Value]
             &&Rates[From_Date]<=MAX(Dates[MonthEndDate])
             &&Rates[To_Date]>=MAX(Dates[MonthEndDate])
             &&Rates[Type]=VALUES(Types[Type_Code])
             &&Rates[Rate_Group]=”Manager”
                 )
                )
        )
      )
)

Après tout est question de goût 😉

Alors voilà, on a chargé nos infos dans Tableau, on les a vérifié, c’est maintenant que le vrai travail de visualisation de données peut commencer. Moi je vais interroger les managers pour connaître leurs interrogations et construire les graphes qui y répondront… Je vous laisse faire de même et poursuivre avec le classeur que voici (à télécharger à travers Tableau Public ci-dessous, en bas à droite de la page qui s’ouvre).

Amusez-vous bien 🙂

Rapide revue de Tibco Spotfire face à Tableau

Je vous l’ai déjà dit, je travaille sur un petit comparatif des différentes solutions de BI self-service qui existent sur le marché. Aujourd’hui j’avais une heure ou deux à tuer, et j’en ai profité pour essayer un des produits sur ma liste : Tibco Spotfire.

Je vais donc sur leur site web, je scanne rapidement pour trouver le lien de téléchargement, fastoche : il est en haut et en rouge.

Tibco Spotfire 1 - Download

J’arrive sur place et je trouve rapidement ce que je cherche : le lien « Download Now« . Hop, c’est cliqué.

Nouvelle page et premier lézard : on me demande de choisir une version, avec pour seul facteur différenciant le prix de la licence. Si on me laisse le choix vous vous doutez bien que je prendrai la moins chère… Mais je suis en mode comparatif, donc je clique sur le petit lien qui liste les différences entre les produits. Bien m’en a pris : il me faudra au moins les versions à plus de 1000$ par an pour s’alimenter sur autre chose que des fichiers Excel/Access. Vu que je veux tout voir, je vais tenter la version à 4500$ par an, qui comprend toutes les visualisations possibles.

Première remarque, point de vue tarif, c’est plus cher que Tableau (2000$ pour Desktop, 1000$ pour Server pour la première année, avec une maintenance à 20% par an après ça)

Et là je tilte : je ne suis plus sur spotfire.tibco.com, mais sur silverspotfire.tibco.com, la solution d’analyse sur le cloud de Tibco. Ceci explique surement qu’on me montre des prix récurrents annuels plutôt que des prix d’achats de licence + maintenance. Enfin peut-être… Parce qu’en fait je ne comprends plus si Spotfire est un client lourd ou pas. Continuons, on verra bien !

Je finis le processus de téléchargement : je me crée un compte, on m’envoie un mail, je télécharge un client, et hop, voilà l’icône sur mon bureau (non je ne fais pas de placement produit, non non)

Tibco Spotfire - 2 - Mon bureau

Je clic clic sur la jolie icone et une invitation apparaît :

Tibco Spotfire - 3 - Ecran de login

Hum… Si c’est vraiment un client lourd, il m’a l’air bien connecté au serveur… Mon problème ? Je n’ai pas vu passer une installation serveur sur mon poste (50Mb l’installeur + aucune demande de paramétrage serveur dans les next/next). Donc je choisis quoi comme serveur ?

Je retourne voir le mail de téléchargement pour voir si ça cause serveur… nop.

Je clic sur help, mais partout il est sous-entendu que je dispose déjà d’une URL vers un serveur. Bon, direction le site web où je trouve assez facilement l’accueil du support :

Tibco Spotfire - 4 - Support

Je confirme, la version « analyst » c’est bien ma version. Pas de chance avec les 2 « support guides » en lien, c’est les modes d’emploi de comment s’enregistrer pour ouvrir un ticket – je refuse de croire qu’il va me falloir ouvrir un ticket pour utiliser la démo gratuite du produit. Je dois avoir raté quelque chose !

Donc je continue à parcourir la page et je tombe sur une FAQ, qui elle répond à ma question dès les premières lignes :

Tibco Spotfire - 5 - FAQ de support

A ce moment-là je m’interroge : si l’URL du serveur public est fixe, ça aurait peut-être été pas mal de la mettre en dur dans l’installeur ? Mais je diverge.

Retour vers la fenêtre d’invitation, où je vais « manager mon serveur » :

Tibco Spotfire - 6 - Ecran de gestion des serveurs

Production ou test ? Je ne sais pas de quoi il s’agit. Alors je choisis Production, je suis comme ça moi !

Je saisis mon login / mot de passe :

Tibco Spotfire - 7 - Ecran de login complété

Et hop ! Enfin non, pas hop, mon client n’est plus à jour (je l’ai téléchargé il y’a 5 minutes pourtant). Donc c’est parti pour une mise à jour… qui durera en tout et pour tout moins d’1 minute. Ok je commence à être mauvaise langue, restons serein 😉

Voilà ! La bête est installée, on va pouvoir jouer ! Pourquoi vous avoir détaillé ainsi mon parcours ? Parce que je trouve que c’est dans ce genre de détails qu’on voit l’attention d’un éditeur à ses clients. Et là pour moi cette première impression n’est pas la bonne.

 Tibco Spotfire - 8 - Enfin l'écran d'accueil!

Pour ce qui est de l’outil en lui-même, je vous encourage à prendre 5 minutes pour regarder la vidéo de prise en main. Elle est rapide et bien faite. Dommage qu’elle ne soit pas mise en avant plus tôt, elle présente vraiment bien le produit et met en valeur ses avantages.

Voici à quoi ressemble Spotfire une fois qu’on a construit un tableau de bord (celui là est de démo – cliquez dessus pour voir en grand):

Tibco Spotfire - 9 - Interface complète

Mon avis, forcément biaisé (rappel, je suis responsable d’offre pour un produit concurrent dans une société de conseil) :

  • Interface
    • Globalement l’interface est plus simple que Tableau Desktop, voir même simpliste maintenant que je suis habitué à celle de Tableau. J’ai peur qu’en utilisation lourde on soit vite fatigué par l’aspect non-explicite des différentes options. Tableau fait vraiment application solide et robuste. Spotfire me donne l’impression contraire.
    • Cette impression vient surement du fait qu’avec Tableau Desktop on travaille sur un seul graphique à la fois et qu’ensuite on utilise ces graphiques comme des briques de légo pour construire un dashboard complet. Le fait de dissocier ces 2 activités permet d’optimiser les interfaces pour chacune des taches. Une fois les dashboards prêts on peut les publier sur Tableau Server, qui offre alors un troisième cas d’utilisation: accéder aux rapports dans un browser. Dans Spotfire on a l’impression de tout faire dans un seul écran, et forcément ça complique les choses. Explorer ses données, construire un tableau de bord et le consommer sont des activités différentes qui à mon sens doivent disposer d’interfaces différentes. Mais ça n’engage que moi!
    • Un vrai truc sympa : les graphes évoluent en live quand on joue avec les filtres. Ainsi on voit des portions disparaîtrent ou apparaîtrent et on comprend le passage d’un état à l’autre. Ça manque à Tableau et ça renvoie à la présentation de Jean-Daniel FEKETE, chercheur en dataviz à l’INRIA, lors du dernier Visual Decision Forum sur les bonnes pratiques en la matière.
  • Visualisations
    • Des vraies treemaps ! (j’ai pas sur Tableau, ça me manque)
    • Des sparklines directement en drag and drop, chouette !
    • Sinon la plupart des représentations classiques est bien présente et les couleurs par défaut sont bien choisies (comme Tableau)
  • Aspect technique
    • Côté sources de données : Excel, Access, CSV d’un côté. Connecteurs Oracle, ODBC, OLE DB en mode saisie de la chaîne de connexion de l’autre. Mouais, bof bof quand on voit la liste côté Tableau.
    • Pour les performances, je n’ai pas pu tester la chose sur du vrai volume, mais je n’ai encore jamais vu de retour négatif sur le sujet
  • Licences
    • A confirmer, mais si effectivement les licences de Spotfire ne sont pas des licences d’achat mais des licences d’utilisation annuelles, l’écart de facture avec Tableau est vraiment important et se creuse avec chaque année qui passe.
  • Positionnement du produit
    • Pour un avis plus neutre que le mien, voir la série d’article d’Andrew Pandre, surtout celui-là, en faisant attention aux dates et aux numéros de version des produits. Ça bouge très vite sur ces outils et les comparatifs sont vites dépassés.
    • De mon côté, je trouve que Tableau Desktop est un produit plus individualiste que Spotfire, ce qui est un bon point pour de la BI Self Service. Car en effet si Tableau Desktop peut fonctionner en total autonomie côté utilisateur, SpotFire nécessite une infrastructure et donc l’intervention de l’IT. Par contre une fois cette structure déployée, je pense qu’on peut ouvrir le client Spotfire à une population d’utilisateur qui pourrait avoir peur de Tableau Desktop. Oui, ok, je parle des analystes qui ont peur des tableaux croisés dynamiques dans Excel, mais ce sont des utilisateurs quand même !
    • Enfin, et histoire d’être complet, je vous pointe vers un comparatif qui lui est biaisé en faveur de Spotfire. Pensez quand même à lire sur Linkedin le contre argumentaire par les utilisateurs de Tableau, l’auteur du comparatif s’étant trompé sur quelques points concernant Tableau.

En conclusion je dirais que Spotfire me semble être un bon produit. Il est propre, carré, mais je dois dire que sa philosophie ne correspond pas à la mienne. Je veux un produit transparent et simple, certes, mais qui permet également de monter en puissance quand on en a besoin. Je veux également un produit qui peut fonctionner en totale autonomie, sans support de l’IT, et qui sait aussi s’intégrer si besoin. Tableau répond à ces deux critères à travers la dissociation de Tableau Desktop – un vrai client lourd d’exploration de données – et Tableau Server – le portail de diffusion – à mon sens Spotfire non (SilverSpotfire tout du moins).