Powerpivot (ex Gemini) et DAX étendant les possibilités des Tableaux croisés dynamiques (TCD) dans EXCEL

D'après un article publié sur http://blogs.msdn.com/powerpivot/
Remerciements à Howie Dickermann [MSFT]

Les Tableaux croisés dynamiques (TCD) d’EXCEL  ne sont pas nouveaux mais le fait de pouvoir les baser sur de multiples tables est une nouveauté rendu possible par l’add-in Powerpivot d’EXCEL 2010. Associé au nouveau langage d’expression DAX (Data Analysis Expression), cette nouvelle possibilité offre un outil puissant pour l’analyse des données vu du côté d'un "EXCELcolique". En effet, plus besoin d'une DSI jargonante, d'expert business intelligence et de cube Analysis Services

Egalement fini le message "requête OLAP en cours d'exécution"  lors du rafraichissement d'un TCD sur le poste client puisque tout est sur le poste client.

Pour ceux qui en douteraient encore, pas d'autre solution: testez ! j'ai donc tenté l'aventure avec une configuration très légère: portable 1Go RAM dual core Windows Seven evaluation Office 2010 Professional Plus beta et SQL SERVER 2008 R2 CTP. Je n'ai pas poussé cette pauvre machine dans ses derniers retranchements en lui assénant Shrapoint 2010.  

L’exemple suivant est tiré de la base exemple AdventureWorks2008 en extrayant les tables product et salesorder ainsi que les tables associées.

Dans l’application Powerpivot lancé depuis EXCEL, sélection d’une source SQL SERVER. Il est cependant possible de choisir toute autre forme de format de données du type relationnel:

Sélectionner la table Product et cocher la case “tables associées”.

La liste des tables associées à [Product] apparait. On choisit de tous sélectionner. Idem pour les tables [SalesOrder]

L’import débute dans une fenêtre qui ressemble à la fenêtre d’exécution de SSIS. On notera au passage que les 121 317 lignes de la table SalesOrderDetails s’importe sans aucune difficulté.

Au final, l’ensemble des tables sont importés dans le classeur Powerpivot (et non dan un classeur EXCEL classique).

Créons le tableau en cliquant sur PivotTable. Et nous voici de retour sous EXCEL un TCD basé sur Product, ProductSubCategory, SalesOrderDetail et SalesTerritory:

Il ne reste plus qu'à ajouter les filtres et slicer avec une gestion de dépendance et le tour est joué. On peut ensuite publier sur Sharepoint le tableau de bord.

Si il avait fallu réaliser ce TCD sous EXCEL, il aurait fallu créer une feuille de calcul opérant une jointure entre Product, Product, ProductSubCategory, SalesOrderDetail et SalesTerritory avec les problèmes de performances et de synchronisation de cette feuille. Dans certaines situations avec des relations de type plusieurs à plusieurs, la création d’un seule feuille contenant l’ensemble des données n’est même pas possible.

Cette fois ci il est possible de renommer ces axes d’analyse et mesures et de créer ses propres formules de calcul depuis les mesures suivant une syntaxe familière aux utilisateurs avancés d'EXCEL: les DAX. Là non plus, point de MDX ni d'effort de modélisation ou de programmation.

En terme de volumétrie de données, notre fichier excel 2010 pèse 14,1 Mo 

A titre de comparaison, le même fichier au format EXCEL 2007 avec les mêmes données à plat dans des feuilles pèse 14 Mo également donc je n’ai pas noté sur cet exemple d’amélioration sensible en terme de compression de données, mais peut être que la volumétrie n'est pas significative.

En terme de performance, difficile d'en juger: les temps de réponse sont imperceptibles ! là encore il faudrait tester sur de plus grosses volumétries.

En conclusion, le produit semble prometteur et surtout très performant. Il permet surtout à un non IT de pousser très loin la conception en terme d'outil d'analyse jusqu'à la réalisation d'une maquette quasi opérationnelle. Personnellement, j'irai même jusqu'à dire qu'il convient de s'interroger si il ne suffit pas de mettre les données de production à disposition de l'équipe d'analyste sur un serveur SQL et de les laisser concevoir leur tableaux de bord plutôt que de partir dans la phase de conception d'un modèle multidimensionel qui reste une phase longue, couteuse et non dénuée de risque.

En terme de stratégie commerciale, on peut également s'interroger de savoir si PowerPivot ne cannibalise pas quelque peu la suite BI SQL SERVER 2008. Après tout SQL SERVER 2008 n'est absolument pas indispensable pour bâtir avec PowerPivot  et quelques vieux routiers des macros et formules EXCEL une plate forme décisionnelle tout à fait à la hauteur et ni SQL SERVER ni Analysis Services n'ont été sollicités pour réaliser cet exemple.

IBM poursuit sa politique d'acquisition en rachetant l'éditeur SPSS. Les deux sociétés se sont accordées sur un montant de 50 dollars l'action en numéraire soit un montant global de 1,2 milliard de dollars. Créé en 1968, SPSS est devenu est un des leaders de l'analyse prédictive. Le rachat devrait être finalisé dans la seconde moitié de l'année 2009.

Les solutions d’analyse prédictive de SPSS couvrent les différents objectifs métier de l’entreprise, l’accent étant mis sur la totale convergence entre analyse, architecture et processus métier.
L’utilisation de l’analyse dans l’entreprise n’est pas nouvelle. Ce qui est nouveau c’est le passage de la business intelligence à la prévision utilisable. La puissance prédictive vient de la connaissance du client, laquelle est uniquement possible à partir d’une véritable compréhension des individus, de leurs comportements et de leurs préférences.

IL s'agit là à mon sens d'une acquisition stratégique dans un secteur en pleine ébullition. En effet, l'étape qui consiste à passer d'un simple outil d'analyse BI à un outil capable d'effectuer des prévisions est probablement la plus délicate à mettre à mettre en oeuvre oujourd'hui mais aussi celle qui peut assurer un maximum d'information utiles pour les décideurs. Il s'agit de passer de la logique de restitution en tableau de bord à l'implémentation du processus de décision.

SPSS dispose pour cela d'un outil à mons sens assez performant et unique sur le marché: le PASW Modeler ex SPSS Clementine.

A ma connaissance et en excluant les outils de statistiques avancées demandant une forte expertise, il n'existe pas d'autre outil sur le marché capable de conduire des simulations et des prédictions de manière relativement accessible à un opérationnel.

Microsoft Corp a dévoilé une surprise de taille pour les utilisateurs de son produit Office PerformancePoint Server: un nouveau Financial planning accelerator (FPA) qui pourrait proposer de prolonger la vie de ce produit - pour certains utilisateurs, en tout cas.
En Janvier 2009, Microsoft envisage de cesser de diffuser PerformancePoint, qui avait été lancé (après une longue Community Technology Preview) moins de deux ans auparavant. Le scorecarding, le suivi et les capacités d'analyse de PerformancePoint 2007seront inclus dans la prochaine version de Microsoft SharePoint Server 10 (MSS, née de Microsoft Office SharePoint Server). Redmond envisagait alors de cesser la diffussion de PerformancePoint Server Planning - outil qui n'a été utilisé que par une petite fraction des utilisateurs de PerformancePoint et de le refondre dans un hypothétique produit en cours d'étude Reporter Manager.

Le résultat à court terme est que est que les les utilisateurs de Microsoft PerformancePoint planning se retrouve le bec dans l'eau (voir http://www.tdwi.org/Publications/display.aspx?Id=9434). MS décide donc de livrer le code source du module Planning. Ce n'est pas véritablement un «accélérateur» (Microsoft a livré plusieurs BI Accelerators dans le passé) car il s'agit de divulguer à ces clients le code source de PerformancePoint planning.  Les partenaires et les clients ayant un accod sont libres de faire ce qu'il veulent avec - y compris de développer des offres de services autour de PerformancePoint planning.

D'un autre côté, Microsoft ne publie pas le code source de PerformancePoint planning ou ne le rend pas disponible via une Microsoft Public License (MPL) ou Microsoft Reciprocal License (LMR) - qui ont la bénédiction de l'Open Source Initiative ( OSI). Les clients doivent signer un accord de licence plus restrictif, aux termes duquel ils doivent accepter de renoncer à tout support de l'éditeur pour tout ce qu'ils développent.

Un produit jamais fini 
L'outil Planning de Microsoft Office PerformancePoint Server a longtemps semblé être une version pre realease. Contrairement à PerformancePoint scorecarding pour le suivi et les capacités d'analyse - basé sur Business Scorecard Manager (BSM) et des outils acquis lors de l'acquisition de l'ex groupe ProClarity - Planning Server est un outil développé "from scratch". Plus précisément, Business Scorecard Manager et ProClarity ont amené des outils tout de suite crédibles en matière de scorecarding et d'analyse.

PerformancePoint planning, d'autre part, a été décrit (par le spécialiste visionnaire du maché OLAP Nigel Pendse) comme un outil "qui colle parfaitement ou pas du tout". Bien que disposant de fonctionnalités interessantes (Nigel Pendse cite les possibilité de conversion de devises), il note aussi quelques omissions flagrantes - tels que l'absence d'une interface web de saisie des données, ce qui d'après Pendse constitue «une lacune jamais vue dans aucun des produits récents de planification budgétaire".

En soi, la dernière démarche de Microsoft ne fait rien pour améliorer les fonctionnalités de PerformancePoint Planning. Toutefois, en donnant aux clients l'accès au code source du composant , Redmond encourage les utilisateurs à se débrouiller par eux-mêmes. 
Ces clients vont donc devoir s'y habituer. Microsoft a officiellement cessé la diffusion de PerformancePoint Server en avril 2009, et - bien que Redmond prévoit de sortir plusieurs autres service packs PerformancePoint  (y compris un SP3 prévu cet été), - le nouveau code est mis à disposition tel quel.

Microsoft ne prévoit pas de proposer de nouvelle version. "Quand nous avons fait cette annonce et en avons parlé à nos clients, ce que nous avons entendu est qu'ils aimeraient avoir accès au code source de PerformancePoint planning à des fins de diagnostic", explique Kristina Kerr, senior product manager de Microsoft Office Business Applications group. Redmond a le même retour de ses partenaires, affirme Kerr.

"Ainsi, en annonçant Financial Planning Accelerator, nous mettons le code source de PerformancePoint Planning à la disposition des clients, et il est destiné aux clients afin de faire l'auto-diagnostic et aux partenaires afin de personnaliser les solutions existantes pour leurs clients. Ce code ne sera pas soutenu par nous en aucune façon. 

Les clients de Financial Planning Accelerator,  seront en mesure de faire ce qu'ils veulent avec ce code, mais une partie de l'accord de licence stipule que nous avons la propriété intellectuelle (IP) de ce que qui est exposé dans le code source - que, fondamentalement, nous nous efforçons à protéger la propriété intellectuelle tout en répondant aux besoins de nos clients », souligne-t-elle.

De la même façon, les clients - et non Microsoft - bénéficient de droits de propriété intellectuelle sur les améliorations qu'ils se développent. "Si les clients faire des améliorations, cela devient leur propriété intellectuelle. Si, par exemple, un partenaire voulait développer une solution personnalisée en utilisant PerformancePoint Planning, ils peut le vendre sur le marché que par le biais de leurs revendeur. La seule exigence est qu'ils disposent d'une licence SharePoint Server Enterprise".

Robert Kerr précise que le Service Pack 3 pour PerformancePoint Server s'applique au module Planning tel qu'il existe aujourd'hui. "Tout service pack pour PerformancePoint planification sera uniquement pour le produit tel qu'il est aujourd'hui, nous ne ferons pas de service packs pour Financial Planning Accelerator", déclare Robert Kerr. SP3 apportera quelques améliorations attendues sur PerformancePoint - détail au niveau de la ligne - ainsi que la compatibilité avec les correctifs pour SQL Server 2008.
Microsoft prévoit également de fournir des futurs Service Pack qui traitent des problèmes de compatibilité avec les produits à venir, comme Office 2010, MSS 2010 et SQL Server 2008 R2. «Nous sommes également sur la planification d'un autre service pack pour les futures versions d'Office, SharePoint et SQL Server. Nous programmerons d'autres service packs afin de maintenir la compatibilité de ces versions", explique Kerr.

Pour plus d'informations:
Microsoft's BI Roadshow: Products, Projects, and Promises
Microsoft's Analytic Workbench Takes Shape
Microsoft's PerformancePoint Shake-up
Workgroup BI Poised for a Comeback

Analysis Services

Agreggation Manager ***

• Intégration de l'application VS "Agreggation Manager" dans BIDS qui permet de concevoir, modifier et ajouter des aggrégations à partir du Query Log

    

 

Idem sur les partitions du cube :

Voir le Performance Guide de Microsoft pour la mise en oeuvre de la sratégie d'aggrégations proprement dite.

Calculation Helpers **

Permet d'ajouter une description sur la ligne de calcul. Enregistre la configuration (formulaire ou script). Intègre les nouvelles mesures calculées

Column Usage Reports ***

Colonnes inutilisées dans la DSV. Pour des performances optimales, la DSV ne doit contenir que les colonnes strictement nécessaires à l'analyse.

 

 

Deploy MDX Script

permet de ne déployer que les script de calcul dans l'onglet calcul

Non-Default Properties Report

Permet de vérifier toutes les valeurs par défaut qui ont été modifiées. Compte tenu des mécanismes d'héritage, il est parfois difficile de s'y retrouver. Fonctionne sur Analyssis Services et Integration services.

Ne fonctionne malheureusement pas avec la version française.

Printer Friendly Aggregations report **

Liste toutes les aggrégations du cube dans un format Report Viewer exploitable pour une documentation au un audit.

Dimension Data Type Discrepancy Check ***

Vérifie la compatibilité entre les types de données dans la Data source view et les types des attributs de dimension.

Dimension health check: ne fonctionne pas sur mon poste de développement:

Unable to connect to data source [Adventure Works DW] to test attribute relationships and key uniqueness

Dimension Optimization Report ***

Permet de visualiser les propriétés des dimensions en vue d'une optimisation. Très complet.

Measure Group Health Check

Permet d'effectuer un profiling sur les types données des mesures afin d'optimiser le stockage des agrégats. Il permet de mettre en évidence des problèmes plutôt difficiles à détecter.

 

 

Test Aggregation Performance ****

Probablement l'outil le plus efficace de BIDS HELPER en terme de gain de performance sur les agrégations. Cet outil de test fournit des rapports complets sur les performances de requêtes standards avec et sans aggrégations. Attention, les requêtes ne sont peut être pas significatives de l'utilisation réelle du cube et assurer vous de bien tester les performances dans un environnement de test avant suppression des agrégations proposées. Dans Agregations Manager choisir Agregations performance Test:

Attention cette batterie de test est assez lourde. A effectuer durant une période de maintenance.

Similar Aggregations

Présente les aggrégations incluses dans d'autres aggrégations et pouvant donc être fusionnées.  Utiliser plutôt le Test Aggregation Performance.

 

Show Extra Properties

Permet d'ajouter des propriétés personnalisées aux objets Analysis Services dans la zone Annotations. Modifie le comportement de l'interface afin d'ajouter plusieurs lignes dans les zones de propriétés. Activer cette fonctionnalités dans Outils.. Options...BIDS Helper

Sync Descriptions

Permet d'ajouter des propriétés étendues au delà des MS_Description afin de récupérer des propriétés issues des schémas de la base relationnelle.

Update Estimated Counts

Met à jour de manière exacte les EstimatedCount property des attributs de dimension et des partitions avant l'utilisation de l'assistant agrégation par exemple. Attention le processus peut être très long et ne peut être arrêté sans fausser les données existantes (remises à zéro).

Validate Aggregations *

Vérifie les règles d'aggrégations par rapport à un certain nombre de best practices. Disponible dans le Aggregation Manager.

Visualize Attribute Lattice ***

Permet de visualiser les relations d'attributs en particulier les relations indirectes entre attributs lorsque cela est possible afin de viser de meilleures performances. Excellent pour l'établissement d'une documentation.

Les fonctionnalités SSIS et RS figureront dans un prochain billet.