Projet décisionnel : choisir la bonne technologie dans l’offre Microsoft SQL Server

Je vous parlais tantôt de gestion de projet décisionnel, et en passant je vous disais que le choix d’une technologie pour un projet décisionnel n’était pas une décision anodine. Je voulais vous en dire plus, c’est le moment !

Rappelons d’abord que les projets décisionnels répondent à 3 besoins (cf ma session aux Journées SQL Server pour ceux qui prennent le wagon en route) :

Le décisionnel : Besoin Historisation

Historisation. Les bases de données des applications de l’entreprise sont régulièrement purgées (commandes livrées = commandes effacées du système). Pourtant ces informations sont importantes, il faut les conserver.

Le décisionnel : Besoin Centralisation

Centralisation. Les applications de l’entreprise sont des silos indépendants. Pourtant être capable de croiser ces domaines pour comprendre, par exemple, l’impact des actes commerciaux (CRM) sur les ventes (Logiciel de caisse) est indispensable.

Le décisionnel : Besoin Analyse

Analyse. Mon entreprise est un organisme qui vit dans un environnement. Mes applications (CRM, RH, ERP…) sont des capteurs qui génèrent des informations, des stimuli locaux de ce qu’il se passe dans chaque processus métier. J’aimerai analyser ces informations pour obtenir une image globale et comprendre le monde autour de moi.

Dans un projet décisionnel, on répond à ces 3 besoins à travers 5 fonctions :

  1. L’extraction : à la charge du décisionnel d’aller chercher les données qu’il souhaite
  2. Le nettoyage : ces données doivent être uniformisées et transformées pour être exploitables
  3. Le stockage : on archive les données pour garantir leur pérennité, on les historise pour être capable de comparer le passé au présent
  4. L’analyse : on modélise et interprète les données  pour en tirer un sens
  5. Le reporting : on apporte le résultat des analyses et des requêtes aux utilisateurs

Le décisionnel : 3 Besoins 5 Fonctions
Dans le monde Microsoft, ces fonctions sont assurées par les produits suivants :

Le décisionnel : Produits Microsoft

Ma liste est limitée, il existe d’autres produits (ReportBuilder… et tous les nouveaux sur le Cloud dont Data Explorer) mais on a là les piliers de l’offre.

D’abord on peut se poser la question du pourquoi Microsoft et pas un autre éditeur? Ma réponse c’est que c’est la gamme de produits avec le rapport efficacité / facilité d’usage le plus élevé, et de loin, sur le marché à l’heure actuelle. Notez que ce n’est pas forcément le plus performant sur chaque problématique (Informatica sur l’ETL en temps réel par exemple), ni forcément le plus facile d’utilisation (SSRS…), mais le plus complet, le plus équilibré, celui qui flatte le plus le développeur et l’utilisateur.

On en revient au tableau, pour noter qu’il n’existe au final que 3 domaines ou un choix de technologie existe.

Côté Archivage (je stocke mes données au format source, pour répondre à un besoin d’audit et/ou de sécurité), on stocke directement les fichiers sources sur le disque, ou les tables sans transformation dans la base. Rien de très intéressant par ici. Au passage : attention à ne pas systématiquement utiliser ces données pour vider et régénérer complétement le DWH à tous les chargements. Cette pratique est une bonne pratique uniquement dans certains cas d’utilisation mais pas dans tous. Voir les 2 excellents documents de Marco Russo et Alberto Ferrari sur le sujet, spécifiquement le chapitre « Classification of BI solutions« , dans le PDF introduction.

Côté Reporting, le choix se fait en fonction du type d’utilisation souhaité. Des analyses à la demande ? Excel et les TCD. Du reporting de masse ? SSRS. Du « collaboratif » ? SharePoint et ses Services. Un tableau de bord ? PerformancePoi… non je blague, n’importe quoi d’autre 😉

Le problème avec l’offre jusqu’à aujourd’hui, c’était que le choix de solution de reporting impactait le choix du moteur d’analyse. En effet les tableaux croisés dynamiques d’Excel et les services SharePoint étaient obligatoirement branchés sur du SSAS classique (maintenent BISM-Multidimensional). Heureusement c’est une contrainte qui saute, ou plutôt qui évolue, avec SQL Server 2012 et la refonte de SSAS. Certes cette refonte introduit de nouvelles contraintes (PowerView sur du Tabular), mais elle libère Excel et les TCD.

Ce qui fait que le choix va se faire beaucoup plus librement sur le moteur d’analyse, entre :

  • Monter un datamart répondant à un besoin spécifique directement dans la base SQL
  • Construire un cube : SSAS – BISM Multidimensional
  • Construire un modèle tabulaire : SSAS – BISM Tabular

Et avec Excel 2010 (plus PowerPivot dans certains cas) on peut accéder facilement à ces 3 sources et offrir des tableaux croisés dynamiques bien velus à nos utilisateurs, indépendamment du moteur d’analyse. Ça c’est cool 🙂

La dernière question qui reste est donc quel moteur d’analyse choisir entre SSAS-Multidimensionnal, SSAS-Tabular ou le dB Engine ? La réponse n’est pas encore définitive, elle se précisera au fur et à mesure que nous ferons des projets sur les technos, mais des pistes apparaissent déjà:

  • BISM – Multidimensional : Techno « complexe », données hiérarchisées, grosses volumétries avec reporting à niveau agrégé, relations complexes (many to many…), comparaisons temporelles (mais pas trop les faits en période), des chiffres (pas trop des lettres)
  • BISM – Tabular : Techno simple et performante (elle rattrape les erreurs de développements assez bien), rapide à implémenter, beaucoup plus libre sur le modèle de données, agrège bien mais traite aussi bien le détail, costaud sur le distinct count, attention cependant aux trop grosses volumétries
  • Datamart SQL : J’entends par là des tables d’agrégats bien pensées. Dedans on mettra tout le reste 🙂

Pour plus d’infos, n’hésitez pas à consulter le webcast d’Aurélien Koppel et François Jehl sur le sujet, et n’hésitez pas non plus à en causer dans les commentaires, tous les avis sont bons à prendre!

Tour d’horizon: Techniques BI sur SQL Server

Désolé pour les amateurs de la partie business du blog, mais je suis dans une période technique et ça se sent dans les articles. Promis j’équilibrerai bientôt 😉

Comme la dernière fois, je vous livre quelques articles qui m’ont bien plu chez les bloggeurs BI:

Bonne lecture 🙂

SQLPASS 2010 – Journées 2 et 3

Oh mon dieu! Il s’en est passé des choses en 2 jours! C’est la révolution 🙂

D’abord SSIS, qui d’après Jamie Thomson ne change pas beaucoup en dehors de la refonte de l’interface dans VS2010:

Ensuite SSAS, qui lui explose dans tous les sens:

Vous avez remarqué comme le powerpivotiste aime et le pro-mdx n’aime pas?

Pour voir pourquoi, il faut comprendre que désormais les deux fonctions du datawarehousing (stockage longue durée et analyse/restitution) sont vraiment séparées dans les produits Microsoft.

En stockage longue durée on a SSIS pour l’alimentation et SQL Server (dB Engine) pour le stockage. Les deux produits sont matures, de très bonne qualité et reconnus sur le marché. Pas de débat là dessus.

C’est point de vue analyse et restitution que ça change beaucoup. Avant (enfin maintenant quoi…) on avait trois possibilités pour faire le job:

  • Source SQL Server restituée dans SSRS: facile (SQL) mais pas optimisé pour 2 sous
  • Source SSAS restituée dans SSRS: complexe autant dans le langage de requête (MDX) que dans l’incapacité chronique des 2 softs à tourner ensemble, mais super performant
  • Source SSAS restituée dans Excel: sympa mais uniquement pour les gros joueurs d’Excel qui veulent jouer avec la donnée

Ce sont ces schémas qui évoluent pour passer à deux nouvelles manières d’organiser les données en source, que tous les outils de reporting devraient être capable de requêter:

  • La nouvelle vision, le BISM (BI Semantic Model), qui est une vue relationnelle des données, qui utilise le moteur VertiPaq et le langage DAX
  • La vision actuelle, l’UDM (Unified Data Model), qui est la vue multidimensionnelle qu’on connait déjà, utilisant le moteur SSAS « classique » et le langage MDX

En image ça donne ça:

Bon bin c’est super en fait! Ça nous donne une nouvelle corde à notre arc sans éclater l’existant, en fait c’est chouette!

Sauf que… Sauf que le nouveau SSRS (Project Crescent) est DAX only, tout comme l’est PowerPivot, et qui dit PowerPivot dit Excel et SharePoint à moyen terme. Ça ne laisse donc que l’éco-système hors Microsoft pour s’occuper du MDX à moyen terme. Juste quand le langage commence à gagner de la traction chez les autres vendeurs. Ça ressemble beaucoup à du fire and motion tout ça. C’est pas joli-joli!

On comprend donc que les experts SSAS tirent la tronche et les experts PowerPivot sabrent le champagne 😉

Et pour nous commun des mortels? Personnellement je suis plutôt optimiste, je cite Rob Collie (ancien program manager SSAS quand même) dans l’article que j’ai linké plus haut:

The SQL team at MS has long had three teams in the BI space:  SSAS, SSRS (Reporting Services), and SSIS (Integration Services).  Both AS and RS got along great with IS, but in all honesty, AS and RS have behaved more like rivals for as long as I can remember.

Well, a number of the personalities behind the scenes that were responsible for that competitive vibe have departed.  And the results are resoundingly positive – the two teams are now cooperating.  Fully.

Donc pour la première fois les équipes SSAS et SSRS bossent main dans la main. Vu le talent de ces équipes, personnellement j’ai confiance 🙂

SQLPASS 2010 – Première journée

Qu’est ce que le SQLPASS? Ce n’est rien d’autre que la plus grande messe SQL Server réalisée par une organisation qui n’est pas Microsoft. En effet cette conférence est organisée par le PASS (Professionnal Association for SQL Server) dont l’antenne en France est le GUSS (Groupe des Utilisateurs de SQL Server).

Bien qu’elle ne soit pas réalisée par Microsoft, la société y est bien présente, et on y apprend pas mal de nouveautés sur ce qui nous attend dans la prochaine version de SQL Server (SQL11, nom de code Denali).

Si ça vous intéresse, n’hésitez pas à suivre Christian Robert qui nous rapporte en français sur son blog ce qui se trame là bas.

Personnellement j’ai apprécié le résumé de la première journée faite par Chris Webb, qui comme François Jehl se concentre sur les nouveautés BI qui sont:

  • L’intégration du moteur Vertipaq (stockage verticale) dans SQL Server, voir l’article de Chris Webb pour ce que cela signifie point de vue usage.
  • PowerPivot devrait recevoir une nouvelle version plus « pro », on en saura plus dans les jours qui viennent.
  • L’équipe SSRS nous pond un nouvel outil de reporting ad-hoc: Project Crescent.
  • SSIS qui évolue autant sur le moteur que sur l’interface, Jamie Thomson en fait le détail.

En tout cas ça fait plaisir de voir que ça bouge enfin, même si pour le moment j’ai un peu du mal à percevoir la stratégie globale qui se cache derrière tout ça.

Sécurité des données dans l’environnement SQL Server

Lors d’un des derniers projets auquel j’ai participé, nous avons eu à mettre en place une sécurité à la ligne. Rien d’extraordinaire en soi, mais c’est un bon cas d’étude pour comprendre comment fonctionne la sécurité entre les différentes briques de l’environnement SQL Server.

J’ai essayé de schématiser ça en 3 dessins Visio que je laisse en téléchargement par ici (C’est la première fois que j’utilise Google Document comme ça, mailez moi si ça ne marche pas).

Pour faire court :

  • Un groupe d’administration accède à une interface ASP.NET qui lui permet de définir des couples Utilisateur / Périmètre. Le périmètre s’applique sur une hiérarchie organisation, il se compose d’un niveau et du code de l’entité à ce niveau.
  • Le groupe d’utilisateur accède à un reporting qui utilise un cube et des données de la base. On doit lui appliquer la sécurité définit par les administrateurs.

Pour implémenter une sécurité à la donnée, on ne peut pas donner des droits DataReader/DataWriter aux utilisateurs sur la base. En effet la granularité minimale de la sécurité sur SQL Server c’est la table, ni la ligne ni la colonne.
Il va donc falloir utiliser des procédures stockées (droits GRANT EXECUTE) et joindre les résultats sur la table qui stocke les couples Utilisateur / Périmètre.
Pour le cube, il va falloir définir un rôle qui utilise cette même table.

Le point important dans tout ça, c’est que si votre configuration n’est pas mono-serveur, c’est-à-dire si votre service SSRS n’est pas installé sur le serveur qui héberge la base et/ou le cube, il va falloir mettre en place la délégation d’autorité en utilisant Kerberos. C’est en effet la seule manière de faire propager le UserId depuis le SSRS frontal jusqu’aux cubes et aux procédures stockées. Dans le cas contraire, ce sera le compte de service de SSRS qui se propagera jusqu’aux données.

On peut contourner ce pré-requis si on n’utilise pas de cube dans la solution. Pour ce faire, il suffit de transmettre la constante User !UserId de SSRS en paramètre à la procédure stockée appelée. Ce n’est pas la best practice, mais ça marche. Cette manipulation n’est pas possible pour SSAS puisque la gestion des droits est transparente dans le cube.

Voici le premier schéma, il présente une vue d’ensemble de la configuration:

Vue d'ensemble de la sécurité

Concernant la sécurité fonctionnelle, la sécurité à la ligne, on peut utiliser un jeu de tables comme cela pour l’implémenter:

Définition de la sécurité

Et enfin, pour utiliser tout ça, il faut employer les scripts suivants:

Utilisation de la sécurité

Rappel : si vous voulez le VSD de ces schémas, il suffit de remonter en haut de l’article.

Ne disposant pas de la science infuse, j’apprécierai sincèrement les éventuelles corrections / améliorations que vous auriez à proposer 🙂

La rentrée!

Bin oui c’est la rentrée pour les gros bloggeurs BI français, qui étaient partis en vacances et qui reviennent enfin nous distiller leur sagesse 🙂

Le premier à rentrer c’est François Jehl, d’abord avec un article sur comment tricher dans SSRS pour réaliser un Magic Quadrant, ensuite avec une méthode pour mettre à plat une dimension parent child en T-SQL. Que du bon!

Le second c’est Christian Robert. Avec Christian c’est pas difficile: à chaque fois je crois maitriser un sujet, à chaque fois il publie un article et je me rend compte que je ne voyais que la partie visible de l’iceberg. Cette fois-ci c’est sur le merveilleux monde du 32/64bit dans SQL Server.

Bonne lecture!

Ps: quand je dis « gros bloggeurs BI français », c’est gros par le talent hein, je ne me permettrai pas… 😉