Si vous constatez que certaines requêtes sont lentes lorsque vous rapatriez, dans Excel, des données depuis PowerBI.com, Azure Analysis Services ou Analysis Services tabulaire, alors ce billet devrait vous intéresser.
La syntaxe MDX générée par les tableaux croisés dynamiques d'Excel n’est pas toujours la plus adaptée pour interroger les données de Power BI. Les tableaux XLCubed s’appuient eux aussi sur du MDX, mais, dans de nombreux cas, la syntaxe de la requête est optimisée et donc nettement plus performante. C’est un élément clé pour le choix initial de XLCubed par nos clients.
Cependant, lorsqu'il s'agit de rapports au format table (de type « listing ») sur Power BI, Azure Analysis Services ou Analysis Services tabulaire, les requêtes en MDX peuvent parfois être plus lentes qu’escompté. Les temps de réponse peuvent notamment devenir particulièrement problématiques lorsqu’un tableau comporte beaucoup de colonnes, et bien sûr de nombreuses lignes.
Le langage de requête DAX gère souvent mieux ces scénarios d'affichage "à plat". Les tables XLCubed permettent aux utilisateurs non techniques de concevoir rapidement et simplement des rapports de ce type en utilisant le langage DAX. Même si l'amélioration des performances variera en fonction de la source de données et des spécificités de la requête, les résultats ci-dessous, tirés de requêtes basées sur le modèle de démonstration « Contoso » de PowerBI.com, donnent des éléments de comparaison
L'amélioration des performances est très significative. Dans ces exemples, l'approche DAX est 3,5 à 4 fois plus rapide ! Même si un rapport est toujours trop long dès qu'il faut patienter un peu pour récupérer les données, toute amélioration notable des temps de réponse est bonne à prendre.
Si ce cas de figure vous semble familier, essayez donc les quatre étapes ci-dessous :
- Dans Excel, ajoutez une connexion XLCubed à Power BI / Azure Analysis Services / Tabular SSAS
- Ajoutez une nouvelle table à partir du menu Tableaux / Tables puis choisissez l'option "Analysis Services Tabulaire"
- Sélectionnez les colonnes à afficher et les filtres requis dans la boîte de dialogue ci-dessous
- Appuyez sur ok !
Au delà de ces premières étapes, il est bien sûr possible de tirer profit des capacités offertes par XLCubed version 10 pour construire un rapport réutilisable très flexible, en ajoutant par exemple des segments. Parmi toutes les autres possibilités disponibles, deux sont particulièrement intéressantes :
- Le filtrage basé sur une plage de cellules Excel : cette approche permet à l'utilisateur de saisir rapidement une date, un code produit ou le nom d'un commercial directement dans une cellule plutôt que de passer à chaque fois par une boîte de dialogue. Dans la capture d'écran ci-dessus, on peut voir, en bas de la fenêtre, dans la zone "Range" / "Plage" que le filtrage par pays est basé sur le contenu de la cellule $G$2 (on aurait tout aussi bien pu choisir une plage contenant plusieurs cellules à l'aide de l'interface utilisateur).
- L'ajout de calculs supplémentaires : bien que la centralisation des calculs au sein du modèle soit la plus adaptée pour les calculs de base, la plupart des utilisateurs ont besoin de flexibilité pour ajouter des calculs particuliers supplémentaires. Il est ainsi possible, via le bouton "Insérer\Calcul Colonne" d'ajouter une colonne supplémentaire à n'importe quel emplacement de la table, puis de spécifier une formule Excel standard faisant référence à d'autres colonnes du tableau ou même à des cellules externes. Les calculs insérés de cette façon restent entièrement dynamiques et s'adaptent à la taille de la table. Les utilisateurs peuvent ainsi enrichir le modèle de données central avec les calculs simples dont ils ont besoin sans besoin d'une formation particulière.
Merci à Marco Russo qui a récemment rédigé un article sur ce sujet général et m'a ainsi incité à rappeler aux utilisateurs l'approche proposée par XLCubed. Pour les plus techniques d'entre vous, qui avez peut-être déjà travaillé avec Marco & SQLBI, il est possible que vous utilisiez une syntaxe DAX optimisée super-rapide qui, pour un scénario précis, surpasse la syntaxe générée automatiquement. Dans ce cas, vous pouvez utiliser l'option "Requête personnalisée" afin de coller votre propre syntaxe DAX, ou même lire dynamiquement le contenu d'une cellule Excel pouvant contenir une formule, ce qui permet une approche totalement dynamique pour la génération de la syntaxe.
