Exploiter SSAS au maximum de son potentiel

2. Exploiter SSAS au maximum de son potentiel 10 février 2011Romuald COUTAUD et François JEHLConsultantsAccess IT IDF

3. Access It IdF en quelques mots… • Société de services, conseil / expertise,et formation,exclusivement sur les technologies Microsoft • 20+ collaborateurs spécialisés sur les techno MS, dont 11 MVP • Interventions en mode : • Conseil, expertise, coaching et formation • Réalisation, au forfait ou en Assistance Technique .NET Plateforme Applicative Framework et langages .Net, AZURE, Silverlight, WPF/Surface, VS/TFS, Windows Phone 7,… SQL Server (SSIS, SQL, SSAS, SSRS), PowerPivot, SharePoint & on-line, Office 365,… .NET Collab. BI

4. Introduction • Cette session s’adresse : • Aux développeurs qui ont déjà une expérience sur Analysis Services et veulent découvrir les fonctionnalités avancées permettant d’améliorer l’expérience des utilisateurs • Aux administrateurs qui veulent exploiter les capacités méconnues du serveur OLAP en termes de sécurité et de supervision • Mais ne s’adresse pas à la base: • A ceux qui pensent qu’Analysis Services n’apporte rien par rapport aux couches de données sémantiques et à la BI sur le poste client…… mails ils peuvent changer d’avis après cette session 

5. Agenda • Couvrir plus de scenarii • Embarquer de l’intelligence • Améliorer l’expérience utilisateur • Optimiser les performances • Piloter et sécuriser le serveur

6. 1 – Couvrirplus de scénarii

7. Une navigation hiérarchisée • Les dimensions contiennent souvent de nombreux attributs qui risquent de perdre l’utilisateur lorsqu’il navigue dans le cube via un outil de reporting Ad-Hoc type Excel • Le développeur doit alors mettre en place des hiérarchies à plusieurs niveaux pour structurer l’information et rendre la navigation intuitive • S’il est courant de mettre en place des hiérarchies standards deux cas métiers particuliers sont aussi gérés dans Analysis Services : • Les hiérarchie parent-enfant • Les hiérarchie déséquilibrées

8. Hiérarchies parent-enfant • Elles sont contre-performantes sur les grosses volumétries (pas de stockage des agrégats pour les niveaux intermédiaires)… • …Mais bien pratiques pour gérer certaines problématiques métiers (Plan de compte, hiérarchie Employés, nomenclature produit …) • De plus elles simplifient la mise en place pour tous les membres • des Custom Rollup formula • des propriétés de membre communes

9. Hiérarchies déséquilibrées • Sont des hiérarchies utilisateurs standards dont certains membres ne sont pas renseignés ou répétés sur 1 ou plusieurs niveaux intermédiaires • Elles sont parfois utilisées pour remplacer les parent-enfant trop volumineuses • Nécessite une mise à jour de la propriété HideMemberIf pour masquer les membres vides ou redondants • Sont enfin supportées dans Excel 2010 à condition de masquer par le bas

10. Les types de relation • Pour optimiser la conception des cubes Analysis services prend en charge plusieurs types de relation en plus de la simple relation un à plusieurs • Les relations référencées permettent de factoriser la création d’attributs communs à plusieurs dimensions • L’option « Matérialiser » optimise les performances des requêtes en stockant les informations nécessaires au moment du traitement des cubes • Les relations plusieurs à plusieurs s'appuient directement sur les tables de relation de la base SQL et permettent de gérer les problématiques métiers fréquemment rencontrées (clients  comptes bancaires)

11. Normale Référencé Dimension Dimension Groupe de Mesures FK FK PK PK Table de faits Table de dimension Table de dimension Relation référencées

12. Plusieurs à plusieurs Normale Plusieurs à plusieurs Dimension Dimension Groupe de Mesures Groupe de Mesures FK FK FK PK PK Table de faits Table de faits PK Table de dimension Table de dimension Relation Plusieurs à plusieurs

13. Mesure semi-additive • Analysis Services permet en outre de gérer nativement les problématiques de stock ou de moyenne prenant en compte la dimension Temps ce qui dépasse largement les fonctions d’agrégations standards (somme, compte…) • Il dispose pour cela de fonctions d’agrégation semi-additives • LastChild • LastNonEmpty • FirstChild • FirstNonEmpty • AverageOfChildren

14. Temps … Mesures Trim 1 Mar Fév Jan Produits P1 P2 P3 … Total Mesures semi-additives Ventes Additive Stocks 50 250 Semi Additive %Marge 50 200 Non Additive 15 50 150 200 150 25 200 350

15. Démo Hiérarchie déséquilibrée Relation référencée et mesure semi-additive

16. 2 - Embarquer de l’intelligence

17. Membre calculé et jeu nommé • Le besoin d’ajouter des calculs métiers qui dépassent les capacités des fonctions d’agrégations du moteur OLAP n’est pas une limite dans Analysis Services • Le développeur pourra enrichir les cubes en ajoutant des membres calculés , des jeux nommées et développer les capacités d’analyses de l’utilisateur • Pour leur mise, il bénéficie du langage MDX parfaitement adapté à la structure multidimensionnelle du cube • Celui-ci facilite la mise en place de calculs souvent longs et complexes à écrire en SQL sur le Datamart

18. Membre calculé et jeu nommé • Les membres calculés sont le moyen d’ajouter au cube des indicateurs complexes (%age de contribution par rapport au parent, cumul glissant début d’année / sur plusieurs périodes…) • Les jeux nomméssimplifient la mise en place de listes prédéfinies pour simplifier le travail de sélection des utilisateurs dans les outils clients • Dans les deux cas le langage MDX permet de rendre ces expressions génériques pour tenir compte des sélections faites par l’utilisateur lorsqu’il navigue dans le cube en utilisant la fonction Axis

19. Démo Pourcentage de contribution par rapport au parent Liste des produits qui représentent 80% des ventes

20. 3 - Améliorer l’expérience utilisateur

21. Une base internationalisée • La première étape d’une bonne expérience pour l’utilisateur est de comprendre la base qu’il utilise • Sur un projet international ce n’est pas forcément gagné… • Heureusement, les objets d’une base SSAS peuvent disposer de plusieurs libellés selon la culture de l’utilisateur connecté • C’est la fonctionnalité de Traduction d’Analysis Services

22. Une base internationalisée • Le développeur n’étant pas forcément le meilleur traducteur au monde, il est possible d’importer facilement des traductions • Effectuées par exemple par un sous-traitant • En utilisant AMO(AnalysisManagement Objectsest le successeur de DSO) • Cette API en .NET permet d’accéder à toutes les propriétés des objets du serveur dont évidemment les traductions Analysis Services Application .NET AMO

23. Démo Utilisation de AMO pour l’import/export de traductions dans le cube depuis SSIS

24. Des actions contextuelles • Une autre manière de rendre la navigation dans un cube plus agréable est d’y ajouter de l’interactivité • C’est le principe des actions d’Analysis Services qui permettent de lancer via un menu contextuel • Un DrillThrough • Un rapport SSRS • Une page web • Une commande personnalisée renvoyant des données • Le cube devient le hub d’entrée du système décisionnel

25. Démo Mise en place de différentes actions et utilisation depuis Excel 2010

26. Insérer des données dans le cube • SSAS va plus loin qu’un simple moteur OLAP dans Un autre domaine : il permet à l’utilisateur de saisir des données dans le cube • Il est par exemple possible d’effectuer des simulations, d’affecter des objectifs à une équipe… • Et ce nativement depuis Excel 2010 Analysis Services

27. Insérer des données dans le cube • Cette fonctionnalité qui s’active au niveau de la partition est appelléeWriteBack • Elle utilise en arrière plan le MDX UPDATE CUBE pour changer les données du cube et stocker ces changements en ROLAP ou en MOLAP • L’affectation aux niveaux fins peut être égale sur tous les axes ou pondérée par une expression MDX • Les changements sont toujours détaillés dans une table SQL • A noter qu’il est aussi possible d’effectuer du WriteBack dans des dimensions

28. Démo Ecriture pondérée dans le cube avec le WriteBack depuis Excel 2010

29. 4 - Optimiser les performances

30. Le partitionnement • De nombreux projets décisionnels nécessitent la prise en charge de gros volume de données • Cela nécessite la mise en place d’un partitionnement des tables dans les bases SQL • Dans Analysis Services le développeur peut gérer cette volumétrie en partitionnant les cubes

31. Le partitionnement • Cela nécessite une mise place manuelle dans BIDS ou SSMS… • … qui peut être automatiséevia la génération de script XMLA ou l’utilisation une application .Net via l’API AMO Application .NET AMO

32. Les agrégations • Sont définies via le schéma d’agrégation associé à la partition • Sont calculées lors du traitement du cube puis stockées sous forme de fichiers de données en plus des Faits • Permettent d’améliorer les performances des requêtes sur les cubes en évitant les calculs à la volée • Doivent être redéfinies et optimisées en fonction des requêtes utilisateurs

33. Les modes de traitement • Une fois la conception du cube terminée, il faut aussi prendre en compte la structure de celui-ci pour l’alimenter de manière intelligente • Les modes de traitement doivent être choisis pour éviter un recalcul systématique de toutes les données du cube • Le développeur pourra alors d’optimiser les temps de chargement en ne traitant que les partitions nécessaires • Mais aussi en choisissant les modes de traitement adaptés  Traitement Data + Index < Traitement Complet

34. Démo Création et mise à jour automatisées de partitions avec AMO dans SSIS

35. Optimiser les requêtes • Management Studio ne fournit pas autant d’informations sur le MDX que le SQL… • Pour l’optimisation des requêtes, utiliser Perfmon + Profiler ou mieux: MDX Studio de MoshaPasumanski • Extraction des compteurs utiles • Exécution sur cache chaud / froid et comparaison • Analyses et suggestions d’amélioration… MDX Query Formula Engine Storage Engine Partition Data Cache Cache

36. Optimiser les requêtes • Quelques pistes… • Limiter les CellsCalculated i.e. les cellules utilisées pour le calcul • Limiter les CalcCoverset les Sonar Subcubesi.e. passer en Block Computation • Une fois le MDX optimisé, ne restent que: • Le caching • Le partitionnement • Les agrégations

37. Démo Optimisation d’une requête MDX avec MDX Studio 0.4.14

38. 5 - Piloter et sécuriser le serveur SSAS

39. Un pilotage facilité • Un serveur doit fournir à ses administrateurs des protocoles et interfaces permettant de le piloter • SSAS se commande à l’aide d’XMLA • XML for Analysisest une norme basée sur SOAP • Le XMLA Council regroupe Microsoft, SAS, Hyperion-Oracle et SAP • Il est possible de communiquer directement en XMLA • Ex: ASCMD, disponible dans les samples de SSAS • Ou d’utiliser AMO…

40. Un pilotage facilité • La présence d’une telle librairie facilite le développement de back-offices en .NET comme vu précédemment • Mais pour l’administrateur qui dispose de PowerShell… • (Interface en ligne de commande permettant réaliser des scripts utilisant des librairies .NET) • Cela permet aussi de développer des scripts plus puissants et plus maintenables que des exécutions de code XMLA brut. Analysis Services XMLA AMO

41. Une supervision intuitive • Cependant pour rechercher des informations il est souvent plus rapide de passer par des requêtes • Avant SQL Server 2008avec des DISCOVER en XMLA, retournant des résultats en XML • Depuis 2008 les Dynamic Management Viewsou DMV permettent de requêter et récupérer les données selon un formalisme SQL • Il s’agit en fait d’un élargissement à tout SSAS du MODEL.CONTENT du DMX

42. Démo AMO et PowerShell Exemples de DMV usuelles Utilisation des DMV dans Reporting Services

43. Accessible partout • Les commandes XMLA passent classiquement sur du TCP, en authentification intégrée. • Cependant pour gérer certains scénarios il est possible de passer par HTTP(S)

44. Accessible partout • Le pont HTTP vers TCP est une DLL nommée MsMdPump.dll, située dans le dossier bin/isapiqui réécrit les paquets HTTP entrants en TCP • Principe: • Copier le contenu du dossier bin/isapi dans un dossier, et créer un répertoire virtuel dans IIS • Affecter à cette application un AppPool en .NET Classic (IIS 7) en 32 ou 64 bit • Ajouter une Script Mapà l’application pour *.dll associée à MsMdPump.dll • Configurer l'authentification(Anonyme, Basic, Intégrée)

45. Et sécurisé au plus fin • Sur le plan de la sécurité, celle d’Analysis Services est basée sur des Rôles • Le groupe des administrateurs du serveur doit être réduit… • Chaque Rôle permet d’allouer un certain nombre de permissions à un ensemble d’utilisateurs • Ces permissions permettent de sécuriser jusqu’à la cellule de données, en statique (identique pour tout le rôle) ou en dynamique (en utilisant les fonctions UserName et CustomData) via des expressions MDX • Attention aux performances!

46. Démo Authentification Basic sur HTTP Sécurisation dynamique de dimension

47. Conclusion • Analysis Services est bien plus qu’un simple moteur OLAP • Il fournit au développeur les fonctionnalités lui permettant de couvrir la majorité des problématiques métiers qui lui sont soumises • Il offre à l’administrateur des interfaces de gestion et de supervision comparables à un SGBDR • Il constitue la porte d’entrée du système décisionnel pour l’utilisateur final en offrant des possibilités d’analyses riches et intuitives

48. Livres Blancs

49. Ouvrages

50. MSDN et TechNet: l’essentiel des ressources techniques à portée de clic • Portail administration et infrastructure pour informaticiens • Portail de ressources technique pour développeurs http://technet.com http://msdn.com