Développement configuration et déploiement avec SSIS 2012

SSIS 2012 offre une toute nouvelle manière de gérer la configuration des packages. Cette nouvelle façon de gérer la configuration est accompagnée d’un nouveau modèle de déploiement.

Cet article aura pour but de décortiquer ces nouveaux usages. Est ce qu’il y a un impact sur le développement ? sur la gestion des packages sur le server ? etc.

Côté développement

Petite piqûre de rappel, avec SSIS 2012 il n’ y a plus de fichier de configuration.

Dans SQL Server Data Tools on a maintenant des paramètres. Ces paramètres peuvent avoir deux portées (scope) soit une portée Package soit une portée Projet. Il faut voir la portée Projet comme des “variables globales” de notre projet SSIS et la portée package comme des “variables globales du package”. Par exemple : nom de server, chemin d’accès d’un emplacement sur le réseau etc.

Cela ressemble étrangement aux variables qu’on stockait dans notre fichier de configuration en 2008…
Attention, la notion de paramètre peut être mêlée avec la notion de variables, sauf que ce sont deux notions totalement différentes.

Déjà en terme d’utilisation et sans compter que les types supportés par les paramètres sont différents des types des variables. Par exemple il n’y a pas de type Objet dans les paramètres.
Les paramètres peuvent être utilisées dans les expressions et leur nomenclature diffère de celle des variables : @[$Project::ParameterName]

On peut voir les paramètres à deux endroits :

Si le scope est Projet : Solution Explorer, en double cliquant sur Project.params

Si le scope est package  : Onglet Parameters du package.

Ces paramètres nous aident donc à faire la configuration de nos packages SSIS. Donc oui il y a un impact sur le développement comme c’était déjà le cas en 2008. Il faut donc utiliser impérativement ces paramètres pour rendre le développement configurable et exécutable dans différents environnements.

Par contre tout les éléments d’une solution SSIS ne peuvent être paramétrés. On peut paramétrer la plupart des tâches du control flow et les connexions du Connection Manager.

En parlant des connexions, elles peuvent désormais être converties en connexions de projet, ce qui permet de les rendre disponibles à l’ensemble des packages du projet SSIS.

ProtectionLevel

Le niveau de proctection des packages SSIS, joue également un rôle dans cette floppée de configuration, la propriété ProtectionLevel existe toujours au niveau du package et elle est présente également dans les propriétés du projet projet.

Un dernier point intéressant, on peut créer différentes configurations dans notre projet SSIS, grâce au configuration Manager de Visual Studio,ce n’est pas nouveau on pouvait le faire en 2008. Personnellement je ne jamais touché à cela…

Mais grâce à ceci peut changer la valeur des paramètres durant le Design Time. Donc être capable de déveloper et debugger selon différentes configurations.

Le déploiement

Une fois le développement terminé et les paramêtres en place on peut passer au déploiement.

Pour un simple développeur, un clic droit sur la solution puis deploy.

Mais il existe d’autres méthodes de déploiement. Sachez tout de même que lorsqu’on Build un projet SSIS, un fichier .ispac est créé dans le dossier Bin de votre projet.

Attention : si vous utilisez des configurations choisissez la bonne avant de compiler.

Si vous cliquez sur ce .ispac l’assistant de déploiement va se lancer.

Que contient ce fichier .ispac ?
Si vous changez l’extension du fichier en .zip ou .cab vous allez pouvoir voir le contenu. Vous aller retrouver tous les éléments de votre projet SSIS (package, paramètre, fichier xml de configuration)

Côté administration

Avec SQL Server 2012 on a un server dédié SSIS : Integration Services Catalog. Ce qui sous entend qu’on fait un déploiement en MSDB.

A partir de ce catalogue on peut configurer nos packages en attribuant des valeurs aux paramètres définis dans le développement. On peut avoir plusieurs jeux de valeurs de paramètres car on est capable désormais de définir des environements.

Ces environements pemettent donc d’exécuter des packages dans un certain contexte.

Pour chaque environnement on va définir un ensemble de variables.

Conseil : je ne connais pas encore quelles sont les bonnes pratiques à avoir avec cette nouvelle façon de procéder, mais je vous conseille de garder les mêmes noms de variables dans chaque environnement : donc de reproduire le même set de variables.

Et on on va pouvoir configurer notre projet SSIS. Un clic droit Configure sur le projet.

Le premier panneau de configuration vous permet de modifier les différents paramètres de votre projet sauf qu’il faut avant tout faire des références aux envrionnements créés précédemment.

Donc rendez-vous dans le deuxième panneau References. c’est dans ce panneau que vous allez donner les différentes envrionnement de configuration.

Note : On peut directement créer des environnements via ce panneau.

Par la suite vous pourrez utiliser vos variables d’environnement dans le premier panneau.

Les ConnectionString

Comme vous pouvez voir dans mes screenshots, il y a un onglet dédié aux Parameters et un autre pour le Connections Managers.

Vous pouvez voir dans l’onglet parameters que j’ai paramétrer une connectionString et que l’on peut faire de mêne dans l’onglet Connection Managers.

Quelle approche prendre, paramétrer toutes les connectionString via un paramètre où modifer les connexions dans l’onglet Connection Managers ?

Réponse : les deux sont possibles à vous de voir vos connexions en fonction de votre besoin et d’ajuster votre configuration. Au final on reviendra à la même chose, une variable d’un de vos environnements peut changer les valeurs. Sauf que d’un côté une connectionString à changer et de l’autre, 6 propriétés (ConnectionString,InitiaalCatalog, Password,RetainSameConnection,ServerName,UserName).

Note: On peut toujours donner une valeur manuelle sans passer par des variables d’environnement.

Dans mon cas, la connectionString de ma source ne change pas mais je l’ai tout de même mis en paramètre, si il faut la changer un jour je peux le faire… Par contre mes connexions de destination sont variables car tout dépend de l’environnement.

Je vous conseille très fortement  de faire une validation de votre configuration : Clic droit Validate sur votre projet.

Conclusion

La prise en main de ce nouveau modèle de déploiement parait complexe au premier abord. Cela est dû je pense à plusieurs choses.
Dans un premier temps les appellations : “variables, paramètres, variables d’environement, configuration etc.” qui naturellent quand on l’entend nous fait penser à des choses existantes par conséquent on peut être facilement confus, mêlé voir perdu … Un bon départ est d’oublier un peu ce que l’on sait et reconstituer le tout petit à petit en mettant les nouvelles choses au bon endroit.

La découverte de ce modèle me laisse penser qu’on a plus de flexibilité et permet à tous les intervenants d’un projet d’intégration de données avec SSIS, d’avoir leur part du travail dans de meilleurs conditions. Dans un deuxième temps, étant habitué à développer et déployer de la même manière depuis un petit moment déjà il est difficile d’absorber ce modèle…les nouveaux réflexes s’apprennent vite et au fure et à mesure on peut se dire que ” ce truc est quand même pas mal fichu et qu’on galère moins qu’avant…” Pour l’instant c’est sûre que l’on est en période de changement…les premiers projets avec SQL Server 2012 pointent leur nez…sans conpter queSSIS 2012 réserve encore pas mal de suprises je pense notamment au reporting intégré et une meilleure intégration des projets avec TFS.

Déploiement package SSIS 2008 R2 File System + Fichier de configuration + SQL Agent

Un article pour parler d’une chose assez abstraite (surtout lorsque l’on débute SSIS) : Le déploiement de SSIS.

Il existe différentes méthodes de déploiement avec SSIS 2008.

Pour infos la version 2012 offre une toute nouvelle approche.

Alors en 2008, le déploiement des packages SSIS peut paraitre farfelu.

Déjà il faut choisir un mode File System ou MSDB (Déploiement fichier ou base de données).

Je ne vais pas débattre sur le choix le plux optimisé. Tout dépend des organisations et à la bonne volonté de chacun.

Après il faut aussi penser à la configuration des packages :

• Fichier Configuration
• Variable d’environnement
• Configuration Indirecte

Dans différents projets utilisant le mode File System et la configuration indirecte, il y a toujours pas mal d’interrogations.

Je vais essayer de tout expliquer … Retenez que tout est une histoire de contexte.

Développement des packages

Commencons par le début : le développement des packages. Le développeur travaille sur son poste, il cré des packages à la pelle. Il utilise des comptes pour se connecter aux sources et aux destinations. Un genre de DOMAIN\etl_user pour le sources : qui ne fait que lire la ou les différentes sources. Et ce même compte pour faire manipuler les données dans la destination / Staging etc.

Le développeur possède un compte active directory, DOMAIN\IamTheDev. Si ses packages utilisent divers fichiers de configuration DtsConfig (indirect ou direct) alors ils doivent contenir les ConnectionString vers les sources et les destinations. Qui dit ConnectionString dans une fichier de configuration dit : identifiants pour accéder aux données = DOMAIN\read_user + Mot de passe. Et oui ces informations sont stockées en claires dans le fichier.

Lorsque le développeur va exécuter un package dans BIDS, ce dernier étant configuré il va utiliser toutes les information de connexions contenues dans le fichier de configuration pour se connecter aux sources et aux destinations. Vu que le développeur possède les droits sur le fichier de configuration alors celui va être lu et utlisé dans ce contexte (exécution par le développeur sur son poste).

Le mode/type de déploiement déjà définit préalablement le développeur sait que l’on va déployer en file system et utiliser un fichier de configuration.

Sauf qu’à la création d’un package celui ci possède un ProctectionLevel qui par défaut est mis à EncryptSensitiveWithUserKey. Ce qui signifie que les informations du package sont encryptées avec les informations de la personne qui a créé le package, dans notre cas le développeur. Donc si on laisse cette propriété par défaut, et que l’on souhaite exécuter ce package ailleurs par un autre utilisateur ou  sur notre serveur SSIS alors il y aura à coup sûre un message d’erreur.

Par conséquent il faut changer le protectionLevel de vos packages : DontSaveSentive ou SaveSensitiveWithPassword.

Le déploiement en file system est assez simple:

• Un bon vieux copier coller des packages
• Utilisation du DeploymentManifest
• Autres ?

Vers le dossier contenant les packages sur votre serveur. Idem pour les fichiers de configurations.

Exécution et planification des packages

Pareil plusieurs manières d’exécuter un package SSIS. (Ligne de commande DTUTIL, DTEXEC et SQL Agent) Concentrons nous sur le service SQL Agent, il  doit être lancé dans un premier temps. Dans SQL Agent, un job devra être créé. Ce job contiendra votre package SSIS.

Sauf que…

Je vais vous ai dit tout en haut de cet article que tout est une histoire de contexte. A coup sûre tout ne fonctionnera pas du premier coup. Surtout si vous avez des bases de données externes de votre serveur SSIS.

Pour bien exécuter un package, il faut comprendre que celui va appeller différente sources données. Et que le service SQL Agent de base ne pourra pas forcément y accéder.

C’est pour cela qu’il faut utiliser un proxy. Et un un proxy nécessite un credential qui est lui même lié à un compte active directory. : DOMAIN\etl_user

Un bon schéma vaut mieux qu’un long discours !

Retour – Certifications SQL Server 2012

Bien que SQL Server 2012 soit officiellement sorti ses certifications sont encore en BETA.

Ayant profité de l’offre gratuite de passage de certifs, j’ai pu testé ces nouveaux examens.

Merci Christian pour le bon plan : http://blogs.codes-sources.com/christian/archive/2012/03/23/certifications-sql-server-2012-alias-denali-disponibles-gratuitement-en-beta.aspx

Alors autant vous dire tout de suite que je me suis rendu dans le centre de test sans vraiment préparer. Je comptais essentiellment sur ma veille technologique, les conférences, Webcasts et articles que j’ai pu faire, assister et lire. Je n’espère pas grand chose…

Voici la liste des certifications SQL Server 2012, et la combinaison d’exam forment soit la stack Data Platform ou la stack Business Intelligence.

• Exam 70-461: Querying Microsoft SQL Server 2012
•  Exam 70-462: Administering Microsoft SQL Server 2012 Databases
• Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012
• Exam 70-464: Developing Microsoft SQL Server 2012 Databases
• Exam 70-465: Designing Database Solutions for Microsoft SQL Server 2012
• Exam 70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
• Exam 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

Au programme, sur les 7 certifications SQL Server 2012 je me suis inscrit à 3 d’entre elles le même jour . (Une bonne piqûre !)

• Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012
• Exam 70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
• Exam 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

Je me suis attaqué aux certifs de la stack Business Intelligence,  il faudrait ajouté 70-461 et  70-462 pour avoir la totale.
Avant d’y aller, j’ai jeté un oeil sur le contenu des certifications :

•  http://www.microsoft.com/learning/en/us/exam.aspx?ID=70-463#tab2
•  http://www.microsoft.com/learning/en/us/exam.aspx?ID=70-466#tab2
•  http://www.microsoft.com/learning/en/us/exam.aspx?ID=70-467#tab2

Contrairement aux certifications 2008 on a vraiment plus de certifications et on voit bien que la BI prend vraiment un sens au niveau des compétences SQL Server.
En effet les composantes BI et les domaines de compétences sont mieux dissociés au lieu d’avoir 2 certifications BI regroupant SSAS, SSIS, SSRS et un peu de reste  (comme c’était le cas pour 2008) on a désormais 3 certications qui partagent un peu les 3 grandes familles d’outils BI.

Je ne vais pas tout détailler, mais globalement les questions sont bien posées et maintenant elles sont interractives.

Fini les textes très long à lire on a des screenshots interractifs, des drag and drop des étapes à mettre en place pour répondre à une question, je vous rassure les QCM sont encore là.

La 70-463 est intéressante enfin des mises en situation de  data warehouse (modélisation, chargement etc).

La 70-466 : Plutot dure, n’étant pas un Master du MDX et de l’optimisation de cube, et ne connaissant pas tous les secrets du BISM : Multidimensionnal VS Tabular, j’avoue avoir répondu un peu au hasard.

La 70-467 : est un peu équivalente à la 70-452 de SQL Server 2008. Elle regroupe une peu tout est plus plus orienté cas de tout les jours.

Les 3 ont a peu près 60 questions chacune

Toutes les nouveautés sont couvertes dans différentes proportions : on parle de SSDT, SQL Azure, DQS, MDS, PowerPivot, PowerView, ColumnStore, Nouveautés SSIS en veux tu en voilà, Modèle Tabulaire, DAX.

Cela reste des BETA, tout peut encore arrivé.

Et pas mal de question en référence à 2008 aussi.

Certaines questions sont vraiment pointilleuses, d’autres sont des “par coeur”, il y en a des fastoches TAC-O-TAC et par rapport aux certifications 2008 il y avait des questions toutes simples mais Dieu sait qu’on a la réponse mais on ne sait plus comment s’appelle ce truc, on l’utilise tous les jours mais on ne fait plus attention au nom de cette propriété, de cette tâche, composant ou onglet. (GRRrrr)

A croire les feuilles imprimées en sortant résultat par mail dans 8 semaines.

Business Intelligence en France : FrenchConnection.BI

Un article pour parler d’une toute nouvelle communauté qui vient de se mettre en place en France.

La FrenchConnection.BI ! Cette volonté de faire connaitre les technologies Microsoft en matière de BI, dans un cadre communautaire en France est initiée par 4 personnes.

• Francois Jehl
• Aurélien Koppel
• Florian Eiden
• Jean Pierre Riehl

Un peu la crême de la crême, si il fallait monter une équipe BI Nationale ces 4 là seront à coup sûre sélectionnés. Juste pour dire que c’est bon de savoir que la communauté MS BI en France est entre de très bonnes mains.

Très bonne initiative, au programme des évènements pour l’instant pas de grande annonce mise à part un AfterWork et un sujet d’échange sur le Self-Service BI.

Il faudra s’ attendre à d’autres évènements comme des conférences, des débats , des articles, des tips etc., le tout dans une ambiance conviviale.

Plus d’infos visitez le site : http://frenchconnection.bi/communaute-sql-business-intelligence/

Le Facebook : https://www.facebook.com/FrenchConnectionBI

Retour évènement – Lancement en ligne de SQL Server 2012

Un évènement en ligne, beaucoup de monde apparemment vu la lenteur du site à l’ouverture. Des petits problèmes de  Login et de latence …mais après quelques minutes tout fonctionnait bien.

Au programme, Key Note  de Ted Kunnert et Quentin Clark  et par la suite le choix entre 3 tracks. Pour ma part je suis resté dans la track Breaktrough Insights.

Je vous conseille de jeter un oeil  à la session de Dave Campbell.

Dans les autres sessions du PowerView, du Power Pivot, du DQS, du SSIS, du SSAS Tabular et d’autred sujets plus ou moins intéressants (HADOOP, Parallel DWH, DBA Oracle, CEP etc.).

Ce sont de courtes sessions 20-30 minutes, les intervenants vont droit au but par rapport au sujet, le tout est bien expliqué et illustré par des démos . Par exemple la session SSIS, comment expliquer beaucoup de changements en l’espace de 20 minutes.

Pour finir ce lancement, il ya un site web interractif et social.

J’ai bien aimé leur hall d’exposants virtuel, un hall qu’on aurait pu avoir en vrai, mais aussi leur partie sociale/Communautaire avec participation pour poser des question, joindres des mini Topics et discuter.

Le fait de participer à cet évènement vous permet d’accumuler des points et débloquer des Achievements : regarder toute les sessions BI, Regarder la Key Note, poser une question, visiter le stand d’un partenaire etc.

Ces points vous permette de gagner des levels  et des goodies comme une Xbox 360 + Kinect.  Le principe est cool, plus de détails sur cette partie : ici

Cela ne dure pas toute la vie, le contenu du site est disponible durant 90 jours je crois.

Il n’est pas trop tard pour participer, plus d’infos, enregistrez vous et connectez vous sur https://www.sqlserverlaunch.com

Et oui, la version finale de SQL Server 2012 est prévue pour le 1er Avril, en espérant que ce n’est pas une farce…

Installation VM BI Microsoft Windows Server 8, SQL Server 2012, SharePoint 2010

Cette année 2012 est une bonne année pour ce qui est des nouveautés dans le monde Microsoft en général.

J’ai pu me bâtir un environnement flambant neuf avec :

• Windows Server 8 Beta
• SQL Server 2012 RC0
• SharePoint 2010

Liens pour télécharger l’ ISO de Windows Server 8 : http://technet.microsoft.com/en-us/evalcenter/hh670538.aspx?ocid=&wt.mc_id=TEC_108_1_33

Note : Si vous utilisez VM Ware n’installez pas les VMWARE Tools même si certains articles montrent que cela fonctionne bien avec Windows 8 avec la version Server ce n’est pas le cas. Après installation des VM Tools l’affichage est boggué. Allez savoir pourquoi ? Driver graphique ?

Premières impressions installation Windows Server 8

Installation simple et rapide, juste penser à choisir une installation avec GUI afin de profiter de la toute nouvelle interface

En parlant de cette interface…

Déjà le start up a changé dans le cadre de la BETA on retrouve ce fameux BETA Fish.

Avec Windows 7 on avait à l’époque celui là certes plus beau et la tête est de l’autre côté mais on garde l’esprit et les bulles ! (souvenir souvenir)

Bref..

Plus sérieusement cette nouvelle interface est agréable mais déroutante.

Tout à l’air plus léger, plus simple en allant de l’installation à l’identification de session.

Arrivée sur le bureau, premier choc ce bleu cyan pique un peu les yeux …

Le bouton DEMARRER n’existe plus. En effet  ce fameux menu démarrer laisse place à l’interface métro.

Plusieurs manières d’y accéder apparemment.

Soit avec la touche Windows du clavier

Soit en glissant la souris dans l’angle inférieur gauche.

Soit en glissant la souris dans l’angle inférieur droit qui fait apparaitre un menu.

Installation de Active Directory (dcpromo)

Partis pour mettre en place un envrionnement BI de toute dernière génération, je décide par habitude de mettre en place un Active Directory via la commande DCPROMO dans une invite de commande.

Une petite fenête m’informe que l’installation se fait désormais dans Server Manager.

DCPROMO n’est donc plus possible en cmd…la fenêtre indique un lien qu’ on ne peut pas cliquer… le voici :  http://technet.microsoft.com/en-us/library/hh472162.aspx#BKMK_GUI

Le Server manager à été revu, un beau splash screen pour commencer.

On arrive sur un dashboard, toujours dans le style métro. A partir d’ici on va lancer la procédure d’installation d’ Active directory.

Add Roles and Feature -> Next -> Role-Based or feature-based installation -> Next -> Select a server from server pool -> Next ->Sélectionnez Active Directory Domaine Services -> Add Features -> Next -> Next -> Next -> Install -> Close

Nouveauté on peut quitter l’assistant et l’installation se fait en tâche de fond,  un apperçu de l’avancé est disponicle dans l’icône alerte.

Une fois l’installation terminée il y aura une autre alerte pour configurer. Il suffit de cliquer sur ” Promote this server to a domain controller “.

Faites votre configuration dans mon cas :

Add a new forest
Donnez un nom de domaine
Next
Forest function Level : Windows 2008 R2
Domain functional Level : Windows 2008 R2
Domain Name System (DNS) server coché par défaut.
Next -> Next ->Next -> Next -> Next -> Install

Un redémarrage s’effectue…voilà on a installé Active Directory sur Windows Server 8.

Installation de SQL Server 2010 RC0 avec Windows 8 Server

L’installaiton se passe sans problème avec Windows Server 8.

Au moment où je termine cette article SQL Server 2012 est passé en RTM.

Plus d’infos ici: http://www.microsoft.com/sqlserver/en/us/get-sql-server/try-it.aspx

Téléchargement : http://www.microsoft.com/download/en/details.aspx?id=29066

Installation de SharePoint 2010 avec Windows 8 Server

Avant de faire quoi que ce soit je vous conseille de rouler un script de Graig Lussier qui se trouve à cette adresse : http://gallery.technet.microsoft.com/SharePoint-2010-and-dee17600

Ce script va tout simplement vous sauver du temps car il met en place les pré requis.

• Installation des Roles et des Features dans Windows 8 Server.
• Modification de IIS pour SharePoint 2010
• Téléchargement et installation des pré requis de SharePoint 2010

Avant d’exécuter le script, il faut changer la politique d’exécution des scripts powershell via la commande Set-ExecutionPolicy RemoteSigned.

A l’étape 4, après avoir téléchargé les différents pré-requis les installations vont se faire l’une après l’autre.

Pour plus d’information sur l’installation :http://craiglussier.com/2012/03/01/install-sharepoint-2010-on-windows-server-8-beta/

Mais ce n’est pas terminé. SharePoint 2010 ne va peut pas s’installer sur Windows Server 8.

L’installeur va tout simplement se bloquer pour cause de pré-requis, comme pas mal d’installations sur des nouveaux OS de Microsoft. Pour contourner cette étape, il faut télécharger le fichier disponible via le blog ci-dessous et le placer dans C:\Windows\System32

Plus d’infos ici : http://blog.hand-net.com/sharepoint/2010-06-10-error-lors-de-linstallation-des-office-web-apps-2010-sur-windows-7.htm

Ce n’est qu’ à partir de ce momemt que l’on peut installer SharePoint 2010.

Une fois le tout installé le menu Start de Windows Server 8 est bien rempli…. il faudra faire un ménage là dedans.

Il me reste plus qu’à tout configurer et installer des petites choses:

• Business Intelligence Center
• PowerPivot For SharePoint
• Créer une instance tabulaire de SSAS
• Voir comment SSRS est désormais intégré
• Configurer PerformancePoint 2010
• Configurer DQS
• Installer office + Add In data Mining PowerPivot et MDS
• Jouer avec PowerPivot 2 et PowerView et peut être participer au PowerView Contest …

Que du bonheur !

Report Builder 3.0 SharePoint 2007 + IE 9

Symptômes et solution

Travaillant en ce moment sur un environnement SharePoint 2007 avec SSRS 2008 R2 en mode intégré, je devais modifier un rapport avec ReportBuilder 3.0.

En éditant le .rdl dans SharePoint, je me suis retrouvé avec un le message suivant :

“To use Report Builder, you must install .Net Framework 3.5 on this computer” 

Après vérification de mon poste, j’avais bien le Framework 3.5  d’installé. Il s’avère que ce problème viendrai de  Internet Explorer 9 et du mode vue de compatibilité.

Un clic sur cette icône de page déchirée permet de résoudre ce problème et l’application clickOnce de ReportBuilder se lance.
A votre prochaine visite une fois RB 3.0 téléchargé il n’y aura plus de message.

SSIS – Charger une dimension hiérarchique style Employee (Initial, Incrémental, SCD Type 2)

Récemment on m’a demandé de me pencher sur ce cas. En prenant en compte certains pré requis

Le tout consiste à charger une dimension Dim_Employees avec prise en compte du Slowly Changing Dimension de Type 2. Le SCD s’active lorsque le manager change.

Considérations de l’exercice.

• Utiliser le plus possible SSIS
• Prendre en compte le composant SCD Natif
• Le volume de données est faible (de l’ordre du millier)
• Ne pas changer la structure des tables
• Pas de tables temporaires

Ci-dessous la structure des tables. Rien d’extraordinaire, noté juste la présence des clés (EmployeeKey et ParentEmployeeKey) de la source dans la destination. Elles sont préfixé par src_ .

Comment on charge  ?

” On charge les managers puis les autres” –> Bouing !
” On charge les employées puis les managers” –> Presque !

Une voir LA réponse : On charge tout le monde sans les managers puis on affecte les managers.
La colonne manager acceptant les valeurs NULL on peut la laisser sans valeur.

Note : Dans le cas ou cette colonne était NOT NULL il aurait fallu mettre une valeur par défaut comme -1. Cela voudrait dire que chaque employé possède un manager, et voir la possibilité qu’un employé soit manager de lui même si il n’a pas de manager. Bref…

Le traitement s’effectuera en deux passes, on charge tous les employés (peu importe qu’ il soit manager ou pas) puis on assignera les managers via un Update.
Mais….
il faut prendre en compte du SCD sur le manager, si on fait notre UPDATE alors le  SCD va s’activer, cela aura pour conséquence de créer une nouvelle ligne. Oui c’est vrai mais ne l’oublions pas on a toujours un scénario de chargement Initial et un scénario Incrémental.

En effet lors du chargement initial le SCD ne sera pas appelé, par contre dans l’incrémental il faudra l’utiliser.

Ce qui donne un package de ce genre :

Explication rapide :
Le composant  SQL_Count_Row va faire un COUNT sur la table de destination et stocker la valeur du count dans une variable SSIS. Il y a des vérifications par la suite si le Count retourne 0 alors c’est un chargement Initial si le count est supérieur à 0 alors on est dans un chargement Incrémental.

Note : Dans un développement ETL avec SSIS, plusieurs manières de gérer le chargement initial et le chargement incrémental. On aurait pu développer deux packages différents pour chaque scénarios : Un package initial et un package incérmental. On aurait alors soit un package Master pour chaque type de chargement soit un seul package master et des tables de configurations pour gérer les types d’exécution des packages.

Chargement Initial

Le Sequence Container SEQ_Initial_Load, contient deux tâches de flux de données.

Le premier (DFT_Load_All_Employees) est tout simple il insère les employés en laissant la colonne Manager_Id à NULL.

Le second (DFT_Affect_Manager_To_Employee) est un peu plus complexe. Il y a un double Lookup pour assigner le bon Manager_Id en se basant sur le nouvel Employee_Id généré par la table.

Détail du premier Lookup (LKP_Employeed_Id)

Détail du second Lookup (LKP_Manager_Id)

Etat des données :

A la source :

Après l’exécution du DFT_Load_All_Employees

Après l’exécution du DFT_Affect_Manager_To_Employee

Chargement Incrémental

Si on exécute le package par la suite il ira dans la branche incrémentale.

Explications :

Le premier lookup (LKP_DESTINATION) va juste vérifier si l’enregistrement existe. Il va comparer les clés de la source avec la clé naturelle de la destination. Si il n’y a pas de correspondance alors c’est une insertion d’un nouveau record (DST_Insert_New_Employees).

Note du 7 Mars : Après revisite de la solution il manque un lookup avant d’insérer un nouvel enregistrement. Ce lookup va récupérer l’ID du manager un peu comme dans le chargement initial.

 Si il y a correspondance alors l’enregistrement existe dans ce cas là on laisse le SCD gérer le changement.

Configuration du composant SCD_Manage_Data :

Le dernier lookup (LKP_Manager_Id) est nécessaire ici car il re assigner le nouvel ID du manager en récupérer son Employee_ID.

Détail du Lookup :

Note : La sortie du composant SCD New Output peut être supprimé elle ne sert à rien étant donné que le premier lookup gère les nouvelles entrées.

Test

On change les données de la source.

On exécute le package.

Résultats dans la destination :

Conclusion

Un article assez long pour un sujet sensiblement facile aux premiers abords.

Le chargement de données dans un entrepôt de données est un art, pour réaliser cette oeuvre il faut jouer de différentes techniques.
La prodédure décrite dans cet article permet de remplir les objectifs dans un certain contexte. Différents facteurs peuvent intervenir et de ce fait l’approche peu changer. Tout de même certains principes demeureront à jamais. Un data warehouse connait un chargement Initial et un chargement incrémental, il faut donc adapter le développement pour chacune de ces phases. Les problématiques et les questionnements peuvent trouver leurs solutions tout simplement en leur mettant dans le contexte associé.

Microsoft BI, BIG DATA, HADOOP, HIVE, SQOOP – Kézako

Le monde de la Business Intelligence évolue et les solutions s’adaptent. Des mots comme BIG DATA, HADOOP, HIVE, SQOOP sont utilisés dans certains discours.
Cet article aura pour but d’expliquer succinctement ces termes, une sorte de mini sensibilisation.

BIG DATA

Le BIG DATA n’est rien d’autre qu’ un concept. Les défintions sont variées car ce concept évolue ce qui a pour effet de flouter ses caractéristiques.
A l’origine BIG DATA est associé à un très très grand volume de données, on parle là de plusieurs Teraoctets, des Peta et voir plus encore.
Les premiers acteurs du BIG DATA sont les moteurs de recherches dont Google est l’un des premiers précurseurs.

Les données quant à elles sont de nature différentes : on a d’un côté les données structurées, traditionnellement stockées dans les bases de données relationnelles et multi dimensionnelles. Et d’un autre côté des données non structurées qui sont plus difficiles à traiter, on y trouve des fichiers (pdf, mail, ppt, vidéos etc…), Internet : des informations des réseaux sociaux, informations dans le cloud etc.

Pour faire simple le BIG DATA représente la gestion et la manipulation de l’ensemble des données structurées et non structurées qu’une compagnie possède, génère et recherche.

Note : On peut aussi entendre le concept de No-SQL qui signifie Not Only SQL. Cela signifie que les données sont  traitées d’une manière différente que dans un schéma relationnelle classique. On parle alors d’une structure de base de données capable de traiter les gros volumes de données, le fameux BIG DATA.

HADOOP : HIVE, SQOOP

HADOOP est un projet Open Source d’ Apache écrit en JAVA. C’est un framework libre qui permet de gérer le BIG DATA à travers de différents outils. HADOOP stocke les données dans le système de fichier en utilisant HDFS (Hadoop Distributed File System). Plusieurs composants existent dans HADOOP : HIVE, SQOOP, HDFS, Pig, MapReduce, MAHOUT etc. et chacun assure une fonction.

HIVE permet de centraliser du BIG DATA, il joue le rôle de source de données principale de type data warehouse (en File System). On a la possibilité de chercher des informations grâce à des rêqûetes HiveQL.

SQOOP permet d’échanger des données entre entre HADOOP et SQL Server. Microsoft propose des connecteurs HADOOP, vous pouvez les télécharger à partir du lien ci-dessous :

http://www.microsoft.com/download/en/details.aspx?id=27584

Avec des lignes de commande on pourra soit exporter des données depuis HADOOP soit importer des données de SQL Server dans HADOOP. D’après le guide d’utilisations les actions permises sont les suivantes :

With SQL Server-Hadoop Connector, you import data from:

• tables in SQL Server to delimited text files on HDFS
• tables in SQL Server to SequenceFiles files on HDFS
• tables in SQL Server to tables in Hive*
• result of queries executed on SQL Server to delimited text files on HDFS
• result of queries executed on SQL Server to SequenceFiles files on HDFS
• result of queries executed on SQL Server to tables in Hive*

Note: importing data from SQL Server into HBase is not supported in this release. With SQL Server-Hadoop Connector, you can export data from:

• delimited text files on HDFS to SQL Server
• sequenceFiles on HDFS to SQL Server
• hive Tables to tables in SQL Server

SQL Server 2012 – Lancement

Un article rapide pour vous parler du lancement virtuel de SQL Server 2012 le 7 Mars 2012.

Cela se passe ici pour les enregistrements : http://www.sqlserverlaunch.com/ww/Home

Du beau monde en perspective, si vous voulez des détails sur ce qui va être présenté : http://www.sqlserverlaunch.com/ww/Agenda