Tag SSIS

Quel livre pour apprendre le MDX?

Fleid 7 commentaires

Voici l’occasion de me servir de ce blog pour demander votre aide! En effet je renouvelle la bibliothèque de notre pôle de consultants en BI Microsoft, et je suis tombé sur un os en ce qui concerne la partie MDX.

Car autant j’ai une vision assez claire des références absolues sur quasiment tous les sujets décisionnels (voir en dessous), autant mon guide du MDX, Fast Track to MDX – celui qui m’a tout appris, commence à dater un peu. Quelqu’un aurait un ouvrage à me recommander sur ce sujet? Un cookbook peut être?

Et pour référence, voici mes ouvrages recommandés par sujet. N’hésitez pas à commenter cette liste 🙂

Merci à tous dans les commentaires 😉

Le bouquin SSIS 2012, par le trio gagnant Coutaud / Harel / Jehl

Fleid 8 commentaires

C’est avec un grand plaisir que j’ai reçu un exemplaire gracieux du livre SQL Server Integration Services 2012 – Mise en œuvre d’un projet ETL avec SSIS 2012, aux éditions ENI, écrit par les très respectables Patrice Harel, Romuald Coutaud, et François Jehl.

SSIS 2012 - Coutaud, Harel et Jehl - Editions ENI

Je dois encore me le faire dédicacer (perso je profiterai des JSS2012 pour le faire, n’hésitez pas à amener votre copie si vous voulez faire de même), mais ça ne m’a pas empêché de déjà le terminer !

En deux mots : c’est un très bon livre sur SSIS 2012, définitivement le meilleur écrit en français (le seul ? :p), et même sans cet avantage il n’a pas à rougir de son contenu face aux poids lourds américains.

A qui se destine ce livre :

  • Les développeurs, experts techniques et architectes en décisionnel Microsoft qui veulent se mettre à jour sur 2012. On se fait un chapitre tous les jours pendant 2 semaines et c’est réglé.
  • Les autodidactes qui veulent stabiliser leurs connaissances. On apprend le pourquoi des fonctionnalités et quels sont les cas d’usages classiques du produit (oui on fait tous de l’OLE-DB, pas la peine de souffrir à essayer de faire autre chose). Excellent quand on n’a pas accès à quelqu’un d’expérimenté qui peut répondre aux questions et transmettre les bonnes pratiques.
  • Les développeurs d’ETL non Microsoft, c’est une très bonne première approche pour cerner le produit. Je conseillerais même de lire le livre avant de toucher à SSIS, cela vous donnera une familiarité avec l’interface qui aidera grandement le transfert de compétences.
  • Les chefs de projet : c’est un bon point de référence pour participer aux discussions techniques avec vos développeurs. Il y a un choix d’implémentation à faire et vous voulez comprendre les tenants et aboutissants de la conversation ? Hop hop ouvrez la page correspondante du livre et vous comprendrez les enjeux (attention quand même à ne pas déraper ;))

Ce n’est pas pour :

  • J’aurai du mal à recommander le livre à un vrai débutant. Les auteurs nous préviennent d’ailleurs – le livre nécessite des compétences générales en base de données – j’irai plus loin, il nécessite d’avoir déjà été exposé un minimum au monde joyeux de l’ETL d’entreprise. Parce que si le niveau technique est progressif et bien structuré, ce n’est pas un tutoriel, et un débutant ne saurait pas par quel bout commencer s’il n’avait que ce bouquin sous la main.
  • De même pour un expert technique déjà sur 2012 depuis quelque temps, à qui ne peuvent s’adresser que les 3 derniers chapitres.

Rapide revue des chapitres :

  1. Introduction à SSIS : Un peu trop rapide à mon gout. Il manque l’historique du produit, de sa place dans la chaîne BI Microsoft, de son positionnement par rapport aux concurrents (forces et faiblesses)… Mais vous le savez, c’est ma marotte, j’aime avoir le contexte, connaître le pourquoi d’une situation, d’un produit, et ça s’applique même dans un bouquin sur SSIS 😉
  2. Flux de contrôle : Excellent chapitre, exhaustif et qui donne des éléments de contexte sur les fonctionnalités. Must read : la revue des connecteurs de base de données (ADO.NET, ODBC, OLE DB…) et le petit guide de quand employer lequel (p41).
  3. Variables, paramètres et expressions : Très bonne lecture, mais à mon sens il manque une petite explication du besoin fonctionnel avant le détail et l’exemple d’implémentation. On a les outils et la solution sans avoir le problème initial. Par ailleurs c’est un très bel exemple d’implémentation qui a été choisit, qui illustre bien le rôle de chacune des fonctionnalités. Must read : la liste des types de données des variables et leur correspondance en type de base de données (Double c’est DT_R8 ou DT_I8 ? réponse page 135 :)).
  4. Manipulation de données simples : Passage assez descriptif obligatoire mais pas vraiment passionnant pour un vieux de la vieille !
  5. Transformation de données : Une explication des composants de la boîte à outils de SSIS 2012. J’aime les petites remarques qui viennent rappeler les cas d’usages réels, leur emploi dans la vraie vie en somme, et pas uniquement les vœux de Microsoft. Quelques pages sur le CDC et DQS, c’est bon à prendre !
  6. Flux de données multi-source et jointures : Un bon comparatif des différentes solutions de brassage de flux de données dans le data flow. J’aime qu’on se détache un peu des composants pour penser flux de données. Must read : les petits schémas explicatifs des LEFT/RIGHT/INNER/FULL OUTER JOIN (p238), un petit test que d’ailleurs j’adore faire passer à mes développeurs juniors pour valider leur compréhension de la chose.
  7. Événements et suivi d’exécution : Le détail du gestionnaire d’événements (avec le bon conseil de modérer son utilisation), de la gestion des logs, du debug, du monitoring et de la nouveauté 2012 : les data taps. Des sujets pas forcément ultra-sexys mais présentés de manière très digeste.
  8. Administration SSIS : Chapitre qui débute avec un comparatif entre l’ancien mode de déploiement (granularité package) et le nouveau (granularité projet) qui cohabitent dans SSIS 2012. Je ne suis pas convaincu comme les auteurs que le nouveau mode doit être utilisé systématiquement. J’ai fait quelques projets Datastage (chut !) qui utilise largement la notion de repository, et j’aime beaucoup l’indépendance que fournit SSIS en mode déploiement de package (rien de tel que les déploiements en mode dépose de fichiers dtsx sur le disque du serveur, aucune équipe d’exploitation ne peut se planter). Je suis content que le choix reste possible dans 2012 et j’espère qu’il le restera dans le futur. Le reste du chapitre détaille bien les possibilités d’administration offertes par SSIS dans les 2 modes de déploiement.
  9. Checkpoints et transactions : Un très bon chapitre sur une fonctionnalité à oublier de SSIS, c’est un peu du gâchis ! En tout cas respect aux auteurs d’avoir pris le temps de traiter le champ de mines qu’est la configuration des checkpoints. Pour les transactions je dirais que soit elles sont traitées trop rapidement, soit elles ne sont pas assez creusées. Mais je ne peux pas leur en vouloir : c’est un sujet délicat qui justifierait à lui seul plusieurs chapitres et qui n’est finalement que très rarement employé.
  10. Notions avancées et bonnes pratiques : Un chapitre définitivement trop court ! Je comprends que les auteurs ne veulent pas nous révéler tous leurs secrets, mais j’en aurais voulu plus 😉 Par exemple quelques implémentations des scénarios classiques dans SSIS : la détection de doublons, le nettoyage d’un champ texte, la conversion d’une date, le chargement d’une table de fait, la gestion d’une table de transcodage…
  11. Programmation de composants SSIS : J’avoue avoir parcouru le chapitre en diagonale, mais j’y ai trouvé ce qui me semble être un très bon tutoriel pour le développement de son premier composant perso, partant des méandres des éléments à installer sur son poste et les serveurs jusqu’à l’ajout d’une interface graphique pour le paramétrage à l’utilisation. Beau boulot !

A mon sens il manque, dans le désordre :

  • Le load balancing de packages sur une ferme de serveurs SSIS, et plus globalement un petit chapitre sur le déploiement de SSIS sur des grosses infrastructures
  • La gestion des comptes de service et l‘héritage des droits à l’exécution (oui je te regarde SQL Agent)
  • Des recommandations / abaques sur les performances attendues en fonction de volumétries classiques sur 2/3 configurations matérielles standard. Histoire de savoir si on est dans les clous ou à côté de la plaque en traitant 5 millions de lignes de 20 colonnes de texte en 10 minutes sur un quad-core avec 16Go de RAM et un disque 7200tpm. Très grosse maille évidemment.
  • Une vision un peu plus architecture et un peu moins produit. Gérer sa solution et ses projets, l’atomicité d’un package et d’un data flow, l’enchainement des packages (children ou par SQL Agent)…
  • Le détail de l’accès aux features en fonction des éditions (standard, BI, entreprise)
  • Une petite conclusion sur le futur de SSIS dans le cloud azuré?

Évidemment le bouquin fait déjà 450 pages, alors à un moment il faut savoir tailler pour sauver les arbres 😀

En conclusion :

Je recommande sans problème. Beau travail messieurs, merci pour votre dur labeur, à quand le prochain sur SSAS ? 🙂

Conseil aux développeurs d’ETL : automatisez vos traitements au plus tôt

Fleid Laisser un commentaire

Votre ETL est planifié en traitements périodiques ? Quotidiens, hebdomadaires, mensuels ?

Alors faites les tourner de manière automatique le plus tôt possible dans le projet, avec la fréquence la plus haute possible. Si vous avez des alimentations mensuelles qui, vous le savez, passent en hebdo, passez les en hebdo.

N’attendez pas la phase d’intégration (si vous en avez une), ou la phase de recette, pour les exécuter régulièrement. Dès qu’ils sont prêts, automatisez-les !

Pourquoi ?

Si je peux prendre une image, les premières alimentations d’un datawarehouse avec un ETL c’est l’équivalent de vider un étang avec une pompe fragile et un tuyau percé :

  • Plus vous utiliserez votre système, plus vous constaterez les fuites du tuyau. Si vous le faites suffisamment en amont, vous aurez un maximum de temps pour aller coller des rustines.
  • Au fond de l’étang, il y a la vase – les cas spécifiques tordus, les anomalies de production – et quoi qu’il se passe il va falloir l’aspirer. Le plus tôt ça passe dans le tuyau, le plus tôt ça fait étouffer la pompe, le plus vous aurez de marge de manœuvre pour adapter vos traitements et nettoyer tout ça. Mieux vaut s’en débarasser dans la phase de réalisation qu’en recette.
Eviter de patauger !

Eviter de patauger !

Cela peut paraître évident, mais cet été j’ai été confronté à 2 projets dont les ETL n’étaient toujours pas automatisés au lancement de la phase de recette fonctionnelle. Bon courage aux équipes de développement respectives pour éponger la dette technique.

SSIS – Message d’erreur du jour : Insufficent system resources

Fleid 6 commentaires

Le sujet technique du jour c’est SSIS – SQL Server Integration Services, l’ETL de Microsoft inclus dans SQL Server – qui nous le fournit avec le message d’erreur suivant:

SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred.
Error code: 0x80004005. An OLE DB record is available. 
Source: "Microsoft SQL Native Client"  Hresult: 0x80004005 
Description: "Protocol error in TDS stream".  An OLE DB record is available. 
Source: "Microsoft SQL Native Client"  Hresult: 0x80004005 
Description: "Communication link failure".  An OLE DB record is available. 
Source: "Microsoft SQL Native Client"  Hresult: 0x80004005 
Description: "TCP Provider: Insufficient system resources exist to complete the requested service.  ".

Ce message d’erreur provient évidemment de sysdtslog90 (SSIS 2005), que la bienséance nous impose de mettre en œuvre 😉

Quant au contenu, hum…

La stack TCP qui fait des siennes? Ressources systèmes insuffisantes? C’est vrai que la machine est un peu au tacquet, mais quand même! Regardons le package qui a généré l’erreur:

20120123 SSIS : Avant

C’est propre, c’est bien rangé. Si on zoome dans les flux de données rien de bien méchant: on passe de notre base de staging à notre base de staging en faisant quelques opérations de nettoyage de données:

20120123 SSIS DataFlow

De nouveau c’est propre, rien de particulier. En soit le package n’est pas vraiment lourd. Certes. Mais on l’a vu dans le Control Flow, c’est 10 flux de données comme celui-ci qui vont s’exécuter en parallèle. En terme de buffers et de connexions à la base, c’est certainement là où on atteint les limites de la machine.

Et en effet, il suffit de repasser en séquentiel pour que tout aille mieux:

20120123 SSIS Apres

On passe d’un échec critique à une exécution en moins de 2 minutes 🙂 (Hola pas si vite malheureux, cf mise à jour en bas de l’article)

A retenir: par défaut, enchainez vos dataflows les uns après les autres, laissez SSIS gérer la parallélisation des flux ne vous préoccupez pas de la parallélisation!

Évidemment comme toute règle elle comporte des exceptions et une des premières se situe au niveau de la phase d’extraction (la collecte). En effet vous pouvez aller lire toutes vos données sources en même temps puisque dans ce cas, en général ce sont les bases distantes et/ou la connexion réseau qui sont les facteurs limitant, pas votre serveur (enfin restez raisonnables quand même ;))

Concernant les messages d’erreur en eux-même: « Protocol error in TDS stream » et « TCP Provider: Insufficient system resources exist to complete the requested service« , ce qui est certain c’est qu’ils ne sont pas tellement explicites. C’est un fait, avec SSIS il faut apprendre à interpréter ces messages et là pas de secrets : cela vient avec l’expérience.

Enfin, si vous voulez creuser la parallélisation des flux, et plus globalement comment SSIS fonctionne sous le capot, je vous laisse consulter ces articles: le MSDN sur SSIS 2005, Arshad Ali sur SSIS 2008 et Todd McDermid qui fait le tour sur le parallélisme dans SSIS. Bonne lecture 🙂

Mise à jour 24/01/2012 : On m’indique dans l’oreillette que nos problèmes ne sont pas complètement réglés! Le fait de sérialiser les flux de données a certes allégé la pression sur le serveur, mais il continue tout de même de renvoyer de manière intermittente le même message d’erreur sur certains packages. Il s’agirait au final d’un bug « connu » du SP2 de Windows Server 2003. Le remède, cette ligne de commande : « Netsh int ip set chimney DISABLED ». Je mettrai à jour l’article si on trouve autre chose (et par on je veux dire David 🙂 )

Mise à jour 31/01/2012 : Problème résolu! Solution = Sérialisation + Chimney Disabled

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

Fleid 13 commentaires

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!

SQL Server 2008 SP2 et autres sucreries

Fleid 2 commentaires

La nouvelle de la journée : le SQL Server 2008 SP2 sort aujourd’hui! Il est détaillé sur le Team Blog, et se télécharge par là.

J’en profite pour faire le relai d’une sélection d’articles techniques que j’ai collectionné ces 2 derniers mois:

SSRS

  • Importer des cartes autres que celle des USA dans les rapports, via Kasper de Jonge.

SSIS

  • Minimiser les dégâts lorsqu’on effectue un produit cartésien (cross join) dans un flux de données, via Todd McDermid.
  • Une belle remise à plat sur l’utilisation des variables dans les Script Tasks et Script Components, via Todd McDermid encore.
  • Un composant à tester: un nouveau Data Conversion, via Todd McDermid toujours.
  • Une petite piqure de rappel sur les Events et Event Handlers, via le Database Journal.
  • Importer les valeurs d’un result set ADO dans un chaîne, séparées par des virgules. C’est un peu tortueux, mais c’est parfait pour générer des requêtes SQL à la volée. Via SqlServerCentral (enregistrement gratuit)

SSAS

  • MDX : Existing, Count et Filter via Thomas Ivarsson. Il n’explique pas assez à mon gout, mais en jouant avec ses requêtes on comprend bien.

T-SQL

  • Implémenter des tests unitaires automatisés dans Visual Studio 2010 pour le développement des procédures stockées. Ça c’est un sujet à creuser, via Jamie Thomson.
  • Utiliser le MERGE dans SQL Server 2008, via Robert Sheldon de Simple Talk. J’ai honte mais je n’ai pas encore eu le temps de vraiment regarder… Bouh!
  • Dédoublonner un data set grâce à la clause PARTITION BY. Pas sur que ça nous soit très utile en BI cependant, via SqlServerCentral (enregistrement gratuit)

ASP.NET

SharePoint

  • Un livre blanc sur l’authentification Kerberos dans SharePoint 2010, via Kasper de Jonge.