PowerPivot

Formations Self-Service BI avec AZEO

Disclaimer : encore un peu de pub pour mon boulot autour de la Self-Service BI.
Cette fois-ci, je suis très fier de pouvoir vous proposer des formations, en partenariat avec, certes des confrères que je croise aux événements internationaux, mais surtout les meilleurs professionnels sur Power Pivot et le DAX.
Surtout qu’ils viennent juste de sortir un super site (en anglais) sur les patterns DAX

imageDans le cadre de son offre de Self-Service BI, AZEO vous propose un éventail de formations dédiées à l’apprentissage des outils de BI personnelle.

Module disponibles Durée
Création de modèles décisionnels avec Power Pivot 2j
Business Intelligence avancée avec Power Pivot 3j
Reporting opérationnel avec Report Builder 2j
Analyse et reporting avec Excel et Power View & Power Map 2j
Façonnage et nettoyage de données avec Power Query 2j

sqlbi-logo-bigPour les formations Power Pivot, AZEO a signé un partenariat avec SQLBI (Marco Russo & Alberto Ferrari) afin de dispenser les PowerPivot Workshops en France, Belgique et Suisse.

livre-powerpivot-big    image

Les 2 versions de ces cours seront disponibles prochainement en français (et dès aujourd’hui en anglais).

Basique Complète

Introduction à Power Pivot et aux modèles de données dans Excel

coche coche

Chargement des données en utilisant différentes sources de données

coche coche

Bases de modélisation de données avec Power Pivot

coche coche

Introduction au langage de calcul DAX

coche coche

Calculs temporels et bonnes pratiques de modélisation

coche coche

Intégration à SharePoint

coche coche

Création de rapports avec Power View

coche coche

Mise en forme de rapports

coche coche

Contexte d’évaluation des formules et instruction CALCULATE

coche

Utilisation de DAX comme langage de requête

coche

Techniques avancées en DAX

coche

Modèles de calcul avec DAX

coche

AZEO est organisme de formation agréé. Toutes nos formations sont éligibles au DIF.

Inscriptions et détails sur le site AZEO
La première session publique aura lieu
du 8 au 10 avril à Paris

image

Vous ne savez pas ce qu’est la Self-Service BI ?

Nous (ré)organisons un événement le 25 mars pour vous présenter le sujet et ma démarche pour l’aborder dans votre SI et votre stratégie Data.

Inscrivez vous à la Self-Service Business Intelligence le 25 Mars 2014(lien vers la 1er édition en décembre)

 

Automatiser la création d’un classeur PowerPivot

 

Il y quelques temps déjà, je vous indiquais qu’on avait, avec Excel 2013, accès au DataModel par programmation. Etrangement, personne n’en a parlé dans la communauté, française ou internationale.

La documentation du modèle objet d’Excel contient bien les entrées avec un objet Model.

image
http://msdn.microsoft.com/en-us/library/office/jj230227.aspx

Je me suis donc mis en tête de creuser ce sujet…

Disclaimer

Malgré ma ténacité (et une bonne partie de mon dimanche), je ne suis pas super satisfait de mes trouvailles car il y a encore beaucoup à creuser dans l’API pour avoir une solution totalement fonctionnelle. Et l’API n’est pas beaucoup documentée.

Cet article vous donne les pointeurs pour démarrer et évidemment, si par la suite je trouve plus de choses, je vous en ferai part. Je compte sur vous pour en faire de même dans les commentaires de cet article.

 

Mode DataModel Excel

La manipulation du DataModel (Model) directement dans le modèle objet Excel (en VBA) est en ReadOnly uniquement. Elle est même carrément absente des API en VSTO.

Il y a toutefois une méthode intéressante :

Workbook.Connections.Add2

Elle permet d’ajouter une connexion au DataModel grâce à un paramètre booléen qui pourrait passer inaperçu dans la doc : CreateModelConnection.

image
http://msdn.microsoft.com/en-us/library/office/ff195949.aspx

Ce code ajoute la table Faits provenant de mon SQL Server local dans le modèle :

ThisWorkbook.Connections.Add2 _
	"VBA Connection", "A Description", _
	"OLEDB;Provider=SQLNCLI11;Data Source=localhost;initial catalog=Lab;integrated security=SSPI", _
	"Faits", 6, _
	True, True

Je n’ai pas poussé à récupérer un modèle complet mais il suffit d’indiquer plusieurs tables dans la CommandText.

Il y a aussi tout un tas de spécificités en fonction de votre source. Avec une source OData, ça donne ça comme ConnectionString (source Azure DataMarket)

"OLEDB;Data Source="Inline Service Document";Include Atom Elements=Auto;Include Expanded Entities=False;Persist Security Info=false;Time Out=600;Schema Sample Size=25;Retry Count=5;Retry Sleep=100;Keep Alive=False;Refresh Token=xxxxx

Mode Table Liée dans Excel

Dans la lignée de la première méthode, il est tout à fait possible par code de remplir une feuille Excel avec toutes les données (limité à 1M de lignes).

Ensuite, un appel à la méthode Add2 avec d’autres paramètres que ceux présentés ci-dessus permet de l’ajouter au Model.

Par contre, dans ce cas, le Data Refresh ne fonctionnera pas.

ThisWorkbook.Connections.Add2 _
	"LinkedTable_TableClasseur", "", _
	"WORKSHEET;C:\Users\Jean-Pierre\Desktop\Book1.xlsm", _
	"Book1.xlsm!TableClasseur",	7, _
	True, False

Mode OpenXML

Il y a également une autre piste, c’est de passer par le SDK OpenXML qui permet de manipuler le fichier Excel sans ouvrir Excel.

Télécharger le SDK OpenXML 2.5

Le contenu du fichier Excel est un ZIP qui contient des fichiers XML et d’autres données (images, etc.).

Dans le fichier workbook.xml, il y a un nœud <dataModel>
documentation MSDN

Pour le modèle en lui-même, c’est en fait un backup d’un cube SSAS Tabulaire.
Vous pouvez en construire un avec l’API AMO, le sauvegarder et le ré-injecter dans le fichier Excel.

Un projet CodePlex contient toutes les routines nécessaires : http://tabularamo2012.codeplex.com/

clip_image002

Notez que je n’ai pas investigué cette méthode donc impossible de vous dire si cela fonctionne bien.

 

Conclusion

L’API n’est pas documentée, il n’y a aucun exemple sur Internet mais les méthodes sont bien présentes. Je n’arrive pas à la conclusion que c’est impossible mais évidemment, il y a encore du travail pour suivre ces pointeurs et avoir un PoC totalement fonctionnel.

Attention : Point important

De ce que j’ai pu trouver, toutes ces méthodes ne sont pas forcément supportées. L’impact est que, si une macro ou le code qui manipule le OpenXML endommage le classeur Excel, le support Microsoft se désengagera.

 

PS : quand je disais que la communauté n’avait rien écrit sur ces API, j’aimerai toutefois remonter 2 pointeurs :

Notez que ces articles sont vieux et donc ne tiennent pas compte de l’extension de l’API en 2013.

#PowerBI for Office 365

SQL 14 est en Preview depuis fin juin et pas un mot sur ce blog ?
Je sais, c’est moche et je n’ai pas d’excuses…

imageAlors pour me rattraper, je n’allais pas louper les grosses annonces concernant la BI faites aujourd’hui à la Worldwide Partner Conference (WPC).

Et pourtant, je suis en retard, car si vous suivez les réseaux sociaux anglophones, vous avez dû voir fleurir de nombreux Power trucs

Allez, faisons un tour rapide des annonces. Moi ou mes confrères de la communauté les détaillerons dans les semaines qui arrivent.

Entrez dans la Power BI

http://office.com/PowerBI

Power BI, c’est le mot magique qui regroupe maintenant les briques de Self-Service BI de Microsoft. Evidemment, la Self-Service est centrée autour de la famille Office.

Le point important des annonces, c’est que tout cela prend forme dans le Cloud avec une offre cohérente et quasi complète pour les utilisateurs d’Office 365.

Avec votre offre O365, vous aller bénéficier des outils de Self-Service BI dans des BI Sites sur SharePoint prêts à l’emploi.
De quoi monter une plate-forme de Self-Service BI en quelques clics (et donc en quelques minutes).

imagePower BI Site

Et mes données Corporate ?

Une plate-forme BI dans le cloud ? Mais mes données sont dans mes DataWarehouse on-premise ?

En effet, on peut s’étonner de cette approche Cloud-First mais qu’on se rassure, vos données maison sont atteignables. Le Cloud sait depuis quelques temps déjà se transformer en une extension de votre Système d’Informations. C’est le cas pour Office 365.

En revanche, cela vous oblige à réfléchir à vos flux de données, à la sécurisation du réseau et des données, etc.

Les rôles d’architectes deviennent Hybrides.

Data Explorer devient Power Query

Data Explorer, la brique de façonnage et de nettoyage de données,  arrive en phase finale avec la mise à disposition de la RTM (??) de l’outil.
Je ne vous fais pas l’affront de revenir sur ce que fait cet outil, je le présente depuis début 2012 : http://blog.djeepy1.net/tag/data-explorer/

Au passage, il prend son nom définitif : Power Query.

Il reste sous la forme d’un addin Excel mais j’ai bien l’impression que les mashups sont prévus pour fonctionner dans votre Office 365 également (à tester).

GeoFlow devient Power Map

GeoFlow, l’addin Excel de représentation et de navigation 3D de données géographiques, présenté lors de la dernière SharePoint Conference prend son nom commercial : Power Map.

Je pense que l’outil restera encore pour l’instant dans Excel sur le poste client étant donné qu’il s’appuie sur les capacités GPU de votre carte graphique.

Toutefois, il prend bien sa place comme un outil de DataViz de l’offre de Self-Service BI de Microsoft.

PowerPivot devient Power Pivot

La première brique de la Self-Service BI de Microsoft, posée en 2009 est toujours au cœur de l’offre.

Rien ne change sur l’outil, ses fonctionnalités et son usage. En revanche, son nom prend un caractère de plus avec un espace entre Power et Pivot (pour que tous les noms restent cohérents).

La grande différence vient de son usage Cloud. On pouvait certes déjà télécharger des classeurs Power Pivot dans un site Office 365 (version 2013 de votre tenant) mais avec une limitation rédhibitoire : quelques Mégas seulement.
Maintenant, c’est 200Mo la taille limite pour un classeur dans O365, de quoi faire de beaux modèles.

Power View

Power View semble être mis en avant comme l’outil de restitution de données dans cette offre de Self-Service BI. Associé à Power Map dans les annonces, il ne semble pas y avoir de nouveauté annoncée. (sauf peut-être la mobilité – cf. plus bas)

Enterprise Data Search

La recherche que vous avez pu tous tester dans Data Explorer Power Query s’étoffe avec la possibilité de rechercher dans ses données d’entreprise en plus des données publiques.

…so customers can query data from within their business and from external data…

J’espère pouvoir vous en parler plus en détail dans peu de temps.

Data Stewardship et télémétrie sur les sources de données et les usages

Evidemment, qui dit sources de données dit Gouvernance desdites données. Les sites Power BI proposent des fonctionnalités pour suivre les sources utilisées et avoir des métriques dessus.

On parle donc d’une Data Management Gateway pour les nouveaux pilotes des données (Data Steward) qui voient leur rôle s’accroitre/apparaitre.

Requêtage en langage naturel

Une belle nouveauté qu’il me tarde également de tester: le requêtage de vos données en langage naturel.

Kézako ? En fait, il s’agit d’écrire la BI que vous souhaitez voir comme dans un moteur de recherche : “je veux les ventes en volume et en valeur des cigares cubains par pays européens

La puissance de la fonctionnalité est qu’elle propose via la complétion des propositions ou des variantes (par année, par rang, etc.), ce qui montre qu’il y a une vraie taxonomie et une analyse des données elles-même (faits et membres de dimensions) derrières.

Quand on vous dit depuis quelques temps (Tabular) que les méta-données sémantiques sont importantes dans un modèle dimensionnel !!
Sans nul doute qu’il y a un lien avec la recherche de Power Query.

image

image

BI Mobile

Je pensais que ce serait le plus gros de l’annonce puisque nous n’avions eu que très peu de nouvelles depuis la démo de Power View sur un iPad au PASS Summit de 2011. (http://blog.djeepy1.net/2012/11/09/bi-mobile-roadmap/)

L’annonce ne fait que confirmer que la réponse Mobile de la Business Intelligence Microsoft arrive. Les 2 applications iOS et Windows RT sont confirmées et nous avons même leur petits noms : Power BI for Windows RT et Power BI for iPad

En plus de ces applications dédiées, c’est maintenant officiel que TOUS les outils Power BI sont compatibles HTML5.
Oui, tous, ce qui inclut Power View Sourire. Par contre, aucune démo…

image

 

Alors, on y va ?

Eh bien non, pas encore Triste.

En effet, les annonces ont eu lieu, le marketing verbatim est défini, le mode d’utilisation aussi et pourtant, ce n’est pas encore en RTM.

J’entends déjà les rabats-joie se plaindre que ça manque de démos, de screenshots, de précisions, de dates, de prix, etc.

Je n’ai pas de date à vous communiquer mais enregistrez vous sur e site Office.com/PowerBI pour être notifié du top départ. Je suis autant frustré que vous et j’ai hâte de pouvoir approfondir tout cela.

 

Quelques liens pour en savoir plus :

Excel 2013 et la Business Intelligence

Je crois que je n’ai jamais gardé un article à l’état de brouillon aussi longtemps, mais le voila enfin.

Le 25 septembre dernier, le GUSS organisait une après-midi consacrée à la Business Intelligence avec la Wave 15 (ie. Office et SharePoint 2013). Nous avons pu vous y montrer toutes les nouveautés liées au décisionnel, grâce notamment à nos confrères du Club SharePoint (Erol, Serge, Geoffrey et Isabelle).

Vous pouvez trouver les slides sur le site GUSS.fr

Pour ma part, j’ai présenté plus particulièrement Excel 2013, la dernière version qui est passée en RTM (Release To Manufacturing – ie. version finale) il y a 15 jours.
Pour l’instant uniquement disponible aux abonnées TechNet et MSDN, elle sera bientôt disponible pour tout le monde (début 2013).

Sans plus attendre, rentrons dans cet article consacré à Excel.

Les nouveautés d’Excel 2013

image

Introduction

On le sait, Excel est un outil BI à part entière. En tant que spécialiste de la BI, je pense évidemment au Tableau Croisé Dynamique (TCD) qui pour moi fait d’Excel le meilleur client OLAP pour Analysis Services et à PowerPivot qui permet de construire des modèles dimensionnels très facilement.

Mais je pense aussi à tout ce qui permet de manipuler des données pour les utilisateurs : formules de calcul, graphes, mise en forme conditionnelle, TCD et même le VBA qu’on croise quelquefois.

La version 2013 porte encore plus loin les possibilités offertes aux utilisateurs, tant en richesse, en puissance mais aussi en ergonomie d’usage.

Quick Analysis

Dès qu’on a une table ou un bloc de cellules (range), on a la fonctionnalité de Quick Analysis disponible.

Quick Analysis - Adorner

Elle est disponible via un adorner (un petit icône qui se place dans un coin de la sélection).

Cette fonctionnalité permet de mettre en place rapidement une visualisation ou une mise en forme des données sélectionnées pour les analyser facilement.

Quick Analysis - aperçuQuick Analysis offre la création de tableaux croisés dynamiques, des calculs, une mise en forme conditionnelle adaptée ou un graphe représentatif.

On peut également prévisualiser directement un aperçu.

Les propositions de graphes ou de TCD dépendent de vos données mais sont souvent pertinentes.

On notera que le placement et l’usage des options/fonctions tient compte d’un usage tactile en étant plus adapté que dans un menu. J’en reparlerai plus loin.

Slicers

Les slicers (segments en français) ne sont pas une nouveautés. Démocratisés avec PowerPivot v1, ils offrent un excellent moyen de filtrer ou naviguer dans vos données.

On peut faire des tableaux de bord complets grâce à eux mais ils souffraient de quelques limitations.

La principale nouveauté est que l’on peu maintenant mettre un slicer sur n’importe quel jeu de données, sans avoir à créer un TCD ou un modèle PowerPivot.

Un nouveau type de slicer fait aussi son apparition : la Timeline.

Slicer Timeline

Elle permet de filtrer/naviguer dans un champ de type Date (une dimension temps dans un modèle dimensionnel). Encore une fois, cela marche sur une simple table, pas besoin de créer un modèle pour en bénéficier.
A partir de la simple date, la Timeline s’occupe de toute la hiérarchie Année/Trimestre/Mois/Jour.

Enfin, autre nouveauté du côté des Slicers, la gestion des connexions entre Slicers et Données.

Slicer - Connexions

Un menu permet de brancher / débrancher les slicers du classeur sur les différentes tables ou TCD. On y accède depuis le menu du slicer (Report Connections) ou du tableau (Filter Connections).

Cela permet de déplacer les slicers dans différentes feuilles du classeur, chose plutôt compliquée à faire avec la précédente version.

Graphes

Il y a pas mal de nouveautés du côté des graphes, outil indispensable à la représentation des données.

Premièrement, les Graphes Croisés Dynamiques ne sont plus liés à un TCD pour fonctionner. Cela permet d’alléger les classeurs.

Deuxièmement, comme pour le Quick Analysis, des icones très accessibles permettent de manipuler très simplement les graphes (mise en forme, ajout d’éléments, trendline, etc.).

 Graphes - styles

Mais la meilleure amélioration sur les graphes est sans conteste la possibilité de naviguer à l’intérieur. En effet, sur une dimension hiérarchique à plusieurs niveaux, on peut “driller” (drill up / drill down) en double cliquant sur les éléments (ou via le ruban).
En revanche cette fonctionnalité n’est disponible que pour les graphes branchés sur des modèles dimensionnels (normal, il faut une hiérarchie).

Enfin, autre amélioration est la proposition de graphe adapté pour vos données. A l’insertion, un menu vous fait des propositions des graphes recommandés en fonction de vos données.

Graphes recommandés

Tableau Croisé Dynamique

Comme je vous le disais, le TCD est pour moi l’outil ultime pour analyser des données. La version 2013 apporte quelques nouveautés.

Comme pour les graphes, Excel nous “recommande” des TCD en fonction de nos données.

TCD - autres tablesD’un premier abord, le tableau croisé dynamique reste classique. Mais on remarque vite dans la fenêtre de configuration (Champs du tableau croisé dynamique) la présence de “More Tables…” (autres tables en français).

Quand on clique dessus, après un message nous indiquant qu’Excel doit créer un nouveau TCD, on voit apparaitre la liste des tables qui se trouvent dans le classeur.

Il est donc possible de croiser des données se trouvant dans des tables distinctes du classeur, chose complètement impossible à faire dans la précédente version.

Toutefois, on se doute bien que le croisement n’est pas si facile. En effet, il faut que le croisement fasse sens et que les données croisées aient un lien.
Mais qu’à cela ne tienne, si vous testez l’ajout d’un champ d’une autre table, vous verrez qu’Excel vous propose de créer l’association à la volée.

TCD - croisement

Modèle de données

Ce qu’on vient de voir ressemble comme 2 gouttes d’eau à l’utilisation de PowerPivot actuellement… Sauf que là, tout se passe de façon transparente, depuis de simples tables dans Excel.
De quoi apporter les fonctionnalités de PowerPivot à Madame Michu, sans installer PowerPivot.

Je ne vais pas mettre de suspens, c’est bien PowerPivot qu’il y a derrière. Sauf que si on regarde bien, on ne voit pas l’onglet PowerPivot…tout simplement parce que l’addin est présent mais non activé.

Toutes les fonctionnalités d’analyse s’appuient directement sur le moteur de PowerPivot. L’analyse de tables les lie automatiquement dans un modèle de données.

Ajouter au modèle de donnéesLa notion de modèle de données est centrale dans Excel 2013.
D’ailleurs, quand on importe des données, une nouvelle option nous permet de l’importer directement dans le modèle plutôt que dans une table.

 

PowerPivot

PowerPivot est présent par défaut dans Excel 2013 mais non activé.

Le modèle de données étant maintenant interne à Excel, PowerPivot est devenu une interface pour le configurer.

Sur le schéma ci-dessous, les liens entre le Data Model et PowerPivot.

Modèle de données interne Excel

source : PowerPivotPro.com

Dans les changements mineurs de PowerPivot, on peut noter quelques changements de vocabulaire. On ne parle plus de mesures mais de champs calculés par exemple.

Quick Explore

Le Quick Explore ressemble au Quick Analysis sauf qu’il ne marche que sur les TCD et les graphes branchés sur un Data Model ou sur OLAP.

Le Quick Explore ajoute des fonctionnalités d’analyse intéressantes. Il permet de faire par exemple :

  • du Drill To (changement d’axe)
  • du Drill Up / Drill Down
  • trouver des saisonnalités (Cycle Chart, lié à la dimension Temps)
  • trouver des tendances (Trend Chart, lié à la dimension Temps)

Quick Explore

UPDATE : dans la RTM, seul le Drill To est disponible Triste

Azure Data Market

Avec la précédente version de PowerPivot, on pouvait charger des Feeds depuis l’Azure Data Market. 2 nouveautés arrivent de ce côté là :

  • une fenêtre enrichie pour rechercher des données
  • un assistant qui essaye de trouver des données en rapport avec les données déjà dans le modèle

Azure Data Market

Ce moteur de suggestion rappelle fortement Data Explorer et sa version Excel qui a été présentée l’an dernier au PASS Summit.

Je n’ai pas encore creusé cette fonctionnalité mais je peux déjà dire qu’elle est peu adaptée au marché français étant donné qu’il n’y a pas de fournisseur de données ou de services liés aux données français (avis aux éditeurs amateurs, le marché est ouvert).

Power View

Power View est maintenant directement intégré dans Excel, plus besoin d’une Infrastructure SharePoint pour faire un dashboard.
Il se trouve sous la forme d’un addin, comme l’est PowerPivot.

Une vue Power View correspond à une feuille du classeur Excel.
Niveau restriction, il ne marche que sur le modèle de données du classeur (pas sur un modèle tabulaire, encore moins sur un multidimensionnel).

Power View dans Excel

Au niveau des fonctionnalités, on retrouve le Power View de SQL Server 2012 avec quelques améliorations :

Reporting géographique

Un nouveau composant fait son apparition, une représentation géographique avec une carte branchée sur Bing Maps.

Le point fort de cette représentation, c’est qu’elle marche même sans avoir de type SqlGeography (ie. latitude/longitude).
Le composant fait tout seul le géocoding via les services de Bing Maps. Le service est localisé et est plutôt performant. Avec des données représentant la fréquentation des musées d’Ile de France et leurs adresses non nettoyées, seule une adresse n’a pas été reconnue sur une cinquantaine.

Power View - Cartographie

Vous noterez qu’on peut mesurer également sur un autre axe, ici les années, ce qui a pour résultat de transformer la pastille en camembert (pie chart).

En revanche, il n’y a pas de possibilité de faire de rapports avec des Shape Files (fichiers ESRI par exemple).

Pie Chart

On aime, on n’aime pas, mais ils font leur apparition dans Power View.
Les graphes camembert (Pie Chart) sont donc un type de graphe disponible pour vos dashboards.

imageCe qui m’a bien plu, c’est la représentation que prend le pie chart quand on sélectionne un élément dans le dashboard, ce qui a pour objectif de faire un focus sur un élément particulier.

Drilldown

On peut maintenant naviguer dans un dashboard Power View à travers une dimension.

Pour cela, on place juste les différents niveaux sur l’axe du graphes. On peut même mettre des attributs de dimensions différentes.
La navigation se fait par double-clic sur la série.

Power View - Drill Down

Mise en forme

Tout un lot d’améliorations sur la mise en forme de vos dashboard arrive avec cette version de Power View.

Je ne vais pas faire de liste exhaustive mais on peut citer la prise en charge des statuts de KPI, les images de fond, les liens hypertexte, etc.

Office Apps

Office et SharePoint arrivent avec un Store (magasin d’applications), comme on peut en voir sur Windows Phone ou Windows 8.

Quel rapport avec la BI me direz-vous ? Tout simplement, on va vite voir arriver des applications Office orientées Business Intelligence qui vont enrichir nos tables et nos modèles de données.

Office Apps

D’ailleurs, je notais plus haut le manque de Shape File dans la cartographie Power View. Sachez que dans les quelques applications déjà présentes sur le Store se trouve une application de cartographie (certes limitée aux états US Triste).

Modèle objet PowerPivot

En parlant programmation, une limitation de PowerPivot était qu’on n’avait nul part accès au modèle objet.

C’est corrigé avec cette nouvelle version puisqu’on peut maintenant accéder au DataModel OM (DataModel Object Model) depuis du code VBA.

Nous allons pouvoir enfin automatiser certaines actions comme, entre autres, rafraichir le modèle depuis une macro.

Excel Services

Je ne vais pas être dythirambique sur Excel Services, tout simplement parce que toutes ces nouveautés sont supportées dans Excel Services 2013. Ni plus, ni moins.

Office 365

Je n’ai pas du tout testé mais Office 365 (la prochaine mise à jour correspondant à SharePoint 2013) devrait offrir toutes ces fonctionnalités également.

Enfin PowerPivot, Power View dans le Cloud, ce qui va mettre la BI personnelle et d’équipe à la portée de tous les clients de l’offre online d’Office de Microsoft.

Conclusion

En conclusion, on peut être satisfait devant tant de fonctionnalités orientées Business Intelligence dans Excel.

La démocratisation de la Business Intelligence continue son avancée avec les outils Microsoft. Sans pour autant transformer la ménagère de moins de 50 ans en Business Analyst, on voit que de plus en plus de personnes vont pouvoir exploiter rapidement et simplement des données avec cette nouvelle version.

On retrouve des fonctionnalités très riches à portée de main, fonctionnalités que l’on a l’habitude de trouver dans des outils Premium comme Performance Point, je pense au Drill To (changement d’axe) dans un graphe ou dans un TCD.

On voit également que les outils analysent eux mêmes vos données pour en déduire des analyses prédéfinies ou vous proposer des données associées. On n’est loin du Data Mining mais on sent bien que des outils aussi simples qu’Excel commencent déjà à nous assister dans la compréhension de nos données.

Sans nul doute la direction est bonne et j’ai hâte de voir ce qui va arriver après Clignement d'œil.

La Business Intelligence avec Office et SharePoint 2013

BI-wave15La prochaine conférence organisée par le Groupe Utilisateurs aura lieu le 25 septembre 2012.

Le thème suivra l’actualité puisque nous vous proposons de vous faire découvrir SQL Server dans le contexte de la Wave 15.

La Business Intelligence avec Office et SharePoint 2013

Actuellement disponible en Preview publique, les nouvelles versions des produits de la gamme Office (client, serveur, online) offrent tout un lot de nouvelles fonctionnalités qui viennent accompagner l’offre Business Intelligence de SQL Server.

Pour démarrer avec les versions 2013 de SharePoint et Office :

Au programme :

Voici ce qui sera présenté lors de cette sessions du Groupe Utilisateurs SQL Server.

Excel 2013

Nous vous montrerons toutes les fonctionnalités d’analyse natives dans le nouvel Excel.

PowerPivot & Power View

Nous vous ferons découvrir les nouvelles versions de PowerPivot et Power View

PerformancePoint 2013

Nous vous présenterons les nouveautés des services PerformancePoint.

SharePoint 2013

Le club SharePoint nous fera un tour d’horizon des nouveautés de la plate-forme serveur.

 

25 septembre 2012 – 14h
Centre de Conférences Microsoft
Issy-les-Moulineaux

Inscription sur GUSS.fr

 

Je ferai bien évidemment partie des speakers de cet événement et je vous prépare une série d’article pour vous faire découvrir un tour d’horizon des nouveautés en une seule lecture.

Problème de types et cultures avec PowerPivot

PowerPivot gère les types suivants :

  • Texte
  • Entier
  • Décimal
  • Monétaire
  • Date
  • Booléen

A la première importation, PowerPivot déduit le type de la colonne en se basant sur le n-premières lignes. (n étant compris entre 1 et 1050 d’après mes tests, je n’ai pas creusé plus).

Cette gestion implicite des types est transparente pour l’utilisateur mais contient quelques subtilités.

La documentation officielle sur Technet

Problème de culture

Le problème que je vais exposer concerne des données financières françaises avec le caractère espace comme séparateur de milliers de type espace :

  •   1 456,00
  • 99 568,00

Prenons un fichier texte qui contient une colonne de nombre et peu de lignes et/ou que les lignes avec des séparateurs sont en minorité. PowerPivot va bien trouver le type décimal mais ne détecte pas l’espace comme séparateur de milliers. Il supprime donc les lignes avec le séparateur.

Si le fichier contient beaucoup de valeurs avec ce séparateur, PowerPivot déduit que c’est du texte et conserve bien le contenu.

Et pourtant, j’ai bien précisé la culture française.

image

Dans PowerPivot, le premier réflexe est donc de changer le type de la colonne ; jusqu’ici tout va bien et la colonne est bien convertie, le séparateur de milieu est bien interprété.

clip_image002

Sauf que si vous faites un refresh de la source de données, la donnée est mal convertie pour certaines lignes (celles qui ont 3 digits dans la partie entière et une virgule ; la virgule devient le séparateur de milliers)

951,12 –> 95 112

Comme quoi ce n’est pas le même algo derrière le bouton « Type de données » et celui de la récupération des données.

Sur quelle version de PowerPivot ?

J’ai vu le même problème sur toutes les versions de PowerPivot, que ce soit la v1 (SQL Server 2008 R2), la v2 (SQL Server 2012) ou la v3 actuellement en beta dans Office 15.

J’ai ouvert un item sur Connect pour remonter le problème. Marco Russo avait également remonté un problème similaire avec une source de données SSAS.

 

Un contournement ?

La solution que j’ai mise en œuvre est de garder le type Texte lié à la source et d’ajouter une colonne avec la formule DAX suivante :  =[col_originale] *1.0.

Ainsi, l’actualisation des données conserve bien l’intégralité de la colonne puisque c’est du texte. Et ensuite, c’est le 2ème algo qui est utilisé pour la conversion.

 

Une conclusion ?

Mine de rien, ne pas être vigilant sur ses données peut être catastrophique. En effet, des lignes mal converties ou supprimées peuvent passées totalement inaperçues au rafraichissement mais impliquer des agrégats erronés.

Retenez cette petite check-list :

  • Vérifier toujours vos données après l’importation !
  • Vérifier toujours vos données après un changement de type (et après un refersh de la Data Source) !
  • S’il y a un problème, utilisez un type texte et faites de champs calculés pour maitriser le format
  • Des TESTS, Des TESTS, Des TESTS !!
  • Proposez une vraie démarche BI avec un DWH et de l’EIM

Calcul de centile en DAX

Après avoir calculé des centiles en MDX (article ici), nous allons faire la même chose dans PowerPivot avec le langage DAX.
Cela marchera également dans un cube tabulaire.

Rappels mathématiques

La formule de base est simple, on récupère la valeur du

p(n+1)/100 –ème élément

p : centile recherché (ici 1 car nous voulons le 1er centile)
n : nombre d’éléments dans l’échantillon

On applique ensuite une interpolation avec l’élément suivant :

1erCentile + (1erCentileSuivant – 1erCentile) * (RangCentileReel – RangCentile)

Les données

Je me base sur AdventureWorks et je récupère les ventes, le catalogue (produit-catégorie) et une dimension temps.

La complexité est que la granularité de la table est au niveau ligne de commande (SalesOrderLineNumber) alors que je souhaite baser mes calculs sur la commande.

Les calculs

  • On échantillonne nos données
  • On calcule le nombre d’échantillons
  • On calcule le rang de chaque échantillon
  • On récupère la valeur de l’échantillon au bon rang et au rang suivant
  • On interpole

Les formules DAX

L’échantillonnage sera fait dans les différentes formules, en faisant les changements de contexte de filtres (Filter Context).

Le nombre d’échantillons se calcule avec un COUNTROWS.

Le rang de chaque échantillon se calcule avec un RANKX.

Enfin, pour récupérer la valeur de l’échantillon, n’ayant pas la fonction ITEM (il n’y a pas de notion de SET en tabulaire), nous prendrons la dernière valeur d’un TOPN avec un MAXX (ça devrait vous rappeler le BOTTOMCOUNT en MDX).

Attention : Tous ces calculs sont des mesures et non pas des colonnes calculées.

Montant des ventes := SUM(Ventes[Montant])

Rang := RANKX(
  CALCULATETABLE(
    Ventes;
    ALL(Ventes[N° Commande]));
  [Montant des ventes];;1
)

CALCULATETABLE permet de changer le contexte de la fonction RANKX. Le ALL permet de retirer le filtre sur le N° de commande et donc de faire le ranking sur toutes les commandes. Tous les autres filtres qui pourraient être mis sont conservés dans le Filter Context.

Rang1erCentile := CALCULATE(
   ( COUNTROWS(Ventes) – 1 ) / 100 + 1;
   ALL(Ventes[N° Commande])
)

MAXX( Table, Valeur )
TOPN ( N, Table, Expression, Ordre )
(Table étant la table ET son contexte de filtres évidemment)

1erCentile := MAXX(
   TOPN(
      ROUNDDOWN([Rang1erCentile]; 0);
      CALCULATETABLE(Ventes; ALL(Ventes[N° Commande]));  
      [Montant des ventes];
      1);
   [Montant des ventes]
)

1erCentileSuivant := MAXX(
   TOPN(
      ROUNDUP([Rang1erCentile]; 0);
      CALCULATETABLE(Ventes; ALL(Ventes[N° Commande]));
      [Montant des ventes];
      1);
   [Montant des ventes]
)

Les 2 mesures se ressemblent évidemment sauf que l’une prend le ROUNDUP et l’autre le ROUNDDOWN.

Et on finit avec l’interpolation :

Centile:= [1erCentile] + ( [1erCentileSuivant] - [1erCentile] ) * ( [Rang1erCentile] - ROUNDDOWN([Rang1erCentile]; 0) )

Un problème d’agrégation ?

Je l’ai amorcé plus tôt dans l’article mais si vous essayez ces scripts, ils ne fonctionneront pas. Vous aurez des rangs incorrects et donc des centiles incorrects également.

Mais pourquoi ? Le problème vient de la granularité de la table source. Je vous disais que je récupère les lignes de la tables FactInternetSales qui sont à la granularité Ligne de commande donc ma table contient plusieurs lignes par N° de commande.

PowerPivot fait tout ses calculs directement sur la table en mémoire. Il n’y a pas d’agrégation intermédiaire comme dans un cube. Dans la version MDX, la granularité la plus fine du cube est la commande (à cause de ma dimension factuelle Commande, cf. l’article MDX).

J’ai beau changer dans la fonction CALCULATETABLE le contexte de filtre sur le n° de commande, cela n’agrège pas les données et j’ai toujours n-lignes, ce qui décale les rangs.

Les solutions

Tout d’abord, retenez que les scripts ci-dessus fonctionnent si vous avez le bon niveau d’agrégation dans vos données. Pour que cela fonctionne, il suffit de charger un agrégat de la table FactInternetSales (avec un GROUP BY SalesOrderNumber dans la requête).

Cela me permet d’insistez sur un point important dans PowerPivot (mais qui est valable également pour les DataWarehouse). Si vous travaillez sur une granularité N, ne chargez pas les données de la granularité N-1. Même si PowerPivot sait gérer un gros volume de données, cela allègera votre classeur PowerPivot et boostera vos calculs.

Une autre solution (car je connais le précepte “je vais tout mettre, on ne sait jamais…”) est d’utiliser un filtre supplémentaire dans le changement de contexte.

CALCULATETABLE(
   Ventes;
   ALL(Ventes[N° Commande]);
   Ventes[SalesOrderLineNumber]=1
)

Evidemment, je peux utiliser ce filtre car je sais que toutes les commandes ont au moins 1 ligne avec la valeur 1.

Merci à Colin Banfield qui m’a fait gagner un temps précieux et des formules optimisées.