Comment la BI Microsoft est en train d’évoluer !

BI on-demand, Big Data, Cloud, Mobilité, Machine Learning, Social BI…. Décryptage !

Cet article a pour but d’expliquer l’évolution actuelle et à venir de la Business Intelligence Microsoft et de décrypter son écosystème pas si simple à appréhender. En effet l’émergence du cloud, de la mobilité, des « big data » (réseau sociaux, e-commerce…) et de l’analyse qu’on peut en faire, le tout associé aux demandes avec des délais de plus en plus cours de la part des décideurs ont fait naître de nouvelles technologies de Business Intelligence plus modernes et qui pour certaines sont parfois en phase transitoire et en mode « preview ».

L’écosystème se découpe en 4 domaines et un ensemble d’outils associés comme le montre le schéma d’ensemble ci-dessous :

_MSBI

La BI On-Premise (traditionnel) :

Depuis plus de quinze ans Microsoft fait évoluer ses outils décisionnels « On-Premise » autour de SQL Server pour permettre aux sociétés de mettre en place eux même une solution décisionnelle d’entreprise centralisée, robuste et évolutive, en harmonie avec l’infrastructure locale existante et qui répond à des exigences à la fois de sécurité, de performance mais aussi de budget.

Traditionnellement, pour la conception d’un environnement décisionnel, une société peut faire appel à l’IT, on parle alors pour une solution « Corporate » d’entreprise. Mais un utilisateur final souvent appelé « Power User » peut également mettre en place une solution et devenir autonome dans la totalité ou une partie des phases d’un projet, de la conception jusqu’au déploiement.

Dans le dernier cas on parle souvent de solution BI « On-demand » ou « Self-service » et parfois personnelle si elle est conçu intégralement par l’utilisateur.

La suite d’outils BI traditionnels, et ceux-ci autour de la SQL Server, est composée :

Du moteur de base de données : SQL DB Engine
De l’ETL : SSIS
Du moteur d’analyse multidimensionnel : SSAS
Du serveur de rapports : SSRS

Pour la BI « On-Demand », Excel est l’outil de prédilection.

La BI « Corporate » :

Avec SQL Server et depuis la version 7.0, la plateforme BI Corporate de Microsoft est en perpétuelle évolution toujours dans un but d’être plus robuste, performante et avec de nouvelles fonctionnalités souvent inédites.

Aujourd’hui nous en sommes à la version SQL Server 2014 maintenant orientée « Hybrid Cloud » pour faciliter de manière transparente la gestion du stockage, des sauvegardes ou des transferts des données que cela soit sur site ou dans le cloud.

Microsoft a également conçu et développé depuis la version SQL Server 2012 un nouveau moteur d’analyse « In Memory » qui rend plus puissant le traitement des données, les calculs d’agrégats et des formules d’analyse en mémoire, ainsi que la restitution et l’analyse de données autant fines et « Tabulaire » qu’agrégées et en tableau croisé.

De plus, un nouveau type d’index « Column Store Index » permet d’améliorer considérablement les performances de chargement des entrepôts et son requêtage.

Enfin, SQL server permet de réindustrialiser dans SSAS les données provenant des cubes « On-demand », que nous verrons plus loin, conçus avec « Power Pivot », stockées en local dans Excel, de les exposer dans SharePoint pour donner la possibilité à l’utilisateur final de concevoir directement dans un site des analyses ad-hoc grâce au composant de « Data Vizualisation » « Power View » issu de SQL Server et intégré à un service SharePoint 2013.

En complément, pour permettre aux utilisateurs de mieux collaborer, la solution SharePoint 2013 permet, en plus de ce qu’on a vu au-dessus avec « Power Pivot » et « Power View », la mise en place de portail décisionnel, le partage des classeurs Excel d’analyse, l’intégration des rapports SSRS et l’utilisation des fonctionnalités intrinsèques à SharePoint comme par exemple la GED, les intranets, les réseaux sociaux d’entreprise… Un petit plus dans SharePoint avec Power View : la possibilité d’exporter les « Dashboards » dans des slides Power Point en conservant l’interactivité et la possibilité de mettre à jour les données au sein d’une présentation.

Un service appelé « Performance Point » qui intègre entre autre l’outil d’analyse racheté à la société « Proclarity » n’est plus mis en avant.

Dans la gamme des outils « On-Premise », un nouveau « petit » vient de naitre, il s’appelle « DataZen », il vient d’être racheté par Microsoft, il est fourni gratuitement à partir de la version SQL Server 2008 édition Entreprise avec la Software Assurance.

Comme « Reporting Services », « DataZen » est un outil de restitution, il comporte un « Publisher » pour permettre à l’IT de concevoir des rapports avant les déployer coté serveur.

Par contre, contrairement à « Reporting Services », « DataZen » est un outil orienté « Multi-device » et permet par exemple d’analyser des données sur tout type de tablettes ou smartphones (Windows, iOS et Android), puisque qu’il est compatible HTML5.

De plus, il a été conçu pour faire du « Dashboarding » plus que du « Reporting » opérationnel et fait partie de la gamme d’outil d’analyse visuelle de « Data Visualization ».

Enfin, il offre intrinsèquement la possibilité de faire de la BI collaborative en gérant des flux de commentaires entre les utilisateurs.

Petit bémol, il ne comporte pas comme « Reporting Services » les fonctionnalités de « push mail » automatisé, l’intégration à SharePoint et le « Data Alert ». Par contre il permet contrairement à Power BI pour le moment de faire des analyses en mode déconnecté.

On voit bien que Microsoft a construit au fil du temps une véritable plateforme BI On-Premise solide et avec des évolutions permanentes. Aujourd’hui, IT ou l’utilisateur final bénéficie de toute l’expérience de l’éditeur, de la maturité et la robustesse de sa solution pour mettre en place ou utiliser un système d’information décisionnel d’entreprise accessible depuis partout (tablette, smartphone…) et en mode collaboratif.

La BI « On-Demand » :

Excel, l’outil idéal pour d’analyse des données « On-demand » a bien évolué. Si on regarde en arrière, avec la version Excel 2003, pour faire de l’analyse avec une connexion OLAP, on se restreignait qu’à un simple tableau croisé dynamique (« Pivotable »). Aujourd’hui avec Excel 2013, on a une véritable solution complète de Business intelligence personnelle avec ses outils permettant à l’utilisateur final de concevoir, comme avec l’IT et SQL Server, sa propre solution en libre-service composée :

D’un ETL : Power Query
D’un moteur d’analyse et son « Modeler » : Power Pivot
De deux outils de « Data Vizualisation » : Power View et Power Map pour l’aspect Géospatial

La philosophie de « Power View » et « Power Map » est de mettre en place comme Power Point un scénario, « Story Boarding » avec des slides, dans le but de faire une présentation issue de sa propre analyse et de faire en quelque sorte « parler les chiffres » ou faire du « Story Telling ».

Cette présentation peut être du « One-Shot » et être exposée qu’une seule fois par exemple à un Codir. D’où l’utilité en la mettant en œuvre rapidement avec Excel, outil très familier de l’utilisateur final, de ne pas passer par des cycles de conception et développement avec des délais qui peuvent parfois être annoncés comme importants par l’IT.

Par contre dans le cas où cette solution nécessite d’être stable, pérenne, sécurisée et partageable, il faut la réindustrialiser de Excel vers SQL Server grâce à des outils d’ « Import » qui existent.

La Business Intelligence dans le « Cloud » :

L’évolution des services dans le cloud, notamment autour de l’offre Azure de Microsoft, permet à l’IT maintenant d’une manière transparente de disposer de coûts d’administration mieux maîtrisés. L’infrastructure technique et applicative est plus robuste, performante et évolutive. Enfin les cycles de mise à jour sont plus courts et les opérations de maintenance et l’hébergement sont gérés directement par Microsoft.

Trois solutions vont être décrites dans la suite de cet article:

« IaaS »: Infrastructure as a Service

« PaaS »: Platform as a Service

« SaaS » : Software as a Service

La solution SaaS Power BI :

Elle permet de concevoir simplement une solution personnelle de Business Intelligence depuis Excel, avec les composants Power Query, Power Pivot, Power View, Power Map. Mais aussi depuis un concepteur dédié appelé « Power BI Designer » ou « On-Line » à partir du site powerbi.com avec un espace dédié et un compte associé.

Office 365 comporte également un site dédié Power BI où sont publiés et visualisés les rapports.

Les utilisateurs se connectent à des sources de données sur site ou dans le cloud pour mettre en œuvre plusieurs rapports et peuvent également les imbriquer simplement dans des « Dashboards ».

Plusieurs connecteurs sont nativement fournis comme par exemple Analysis Services, Salesforce, Univers Business Object, Dynamics CRM, Azure….

Une fois la solution publiée sur le site powerbi.com, les utilisateurs pourront depuis un mobile (iPhone et Windows Phone pour le moment) ou d’autres « Devices » se connecter à la solution pour visualiser leurs « Dashboards ».

Des fonctionnalités avancées existent également :

– « Q&A ». Une fonctionnalité de requêtage en langage naturelle (uniquement en anglais pour le moment) qui permet de poser des questions Ad-hoc sans connaissances techniques.

– «Data Refresh » permet de planifier la mise à jour des données au sein des rapports.

– « Data Management Gateway » permet de se connecter depuis le cloud à des sources « On-Premise » comme des cubes SSAS.

– « Data Catalog » permet d’exposer les données des rapports dans le cloud pour en faire des sources pour d’autres utilisateurs concevant des rapports.

Aujourd’hui Power BI existe en version Preview car il est en perpétuelle évolution, il peut être mis en œuvre et utilisé gratuitement, sauf si vous voulez utiliser les fonctionnalités avancées ci-dessus ou si vous dépassez la capacité limite de 1Go de stockage de données, d’un débit de 10k rows/ heures ou de cycle de rafraîchissement des données de plus de un jour. Dans ce cas le coût actuel est de 9,99 $ par utilisateur/mois.

L’intégration dans l’environnement Microsoft avec un « Designer » très convivial et ergonomique, la conception BI possible depuis Excel , des composants graphiques très riches, des « Dashboards » dynamiques et interactifs, des connecteurs très variés, la publication sur des mobile et tablettes, les cycles MS de mises à jour très réguliers font de Power BI LA solution personnelle et complète pour faire de la « Business Intelligence » « On-demand ».

Face aux solutions concurrentes Tableau Software et Qlik (ex QlikView), Power BI n’a rien à se reprocher.

La BI Mobile :

Avec les deux solutions « Power BI » et « Datazen » la mobilté cross-plateform est possible. Selon les usages, vous pouvez utiliser l’un des deux outils. « Power BI » est idéal pour le Cloud en mode connecté, gratuit selon certains critères ou avec un système d’abonnement et « Datazen » pour une infrastructure sur site et en mode connecté ou déconnecté, il est inclus dans les licences SQL Server avec la « Sofware assurance » Microsoft.

Une différence importante : pour concevoir un rapport avec Power BI, l’utilisateur n’a pas besoin d’être développeur, il peut le créer dans Excel ou le Power BI Designer. Pour « Datazen », il faut être développeur et être formé sur le « Datazen Publisher ».

La Business Intelligence dans Azure (IaaS et PaaS) :

Autour des services de données PaaS et de la possibilité d’utiliser des VM dans des infrastructures IasS ou des « Appliances » va simplifier le travail des administrateurs sur site et offrir des possibilité inédites pour faire par exemple des traitements de « Big Data » ou alors du Machine Learning sur des « Datacenters » mutualisés, ultra-puissants où la mise à jours des services pourra s’effectuer plus simplement.

L’offre IaaS :

Elle offre la possibilité de porter simplement son infrastructure local dans le cloud dans des VM et de pouvoir la superviser à distance et sans avoir à supporter les coûts de maintenance associés.

Les appliances APS :

APS ex PDW a été conçue conjointement par Microsoft et des architectes « Hardware » pour offrir la meilleur performance en MPP (Massive Parallel Processing) au sein d’une appliance. Elle offre également la possibilité d’intégrer sous le nom de HDInsigt le moteur NoSQL d’Hadoop pour des traitements « Big Data » et d’offrir au travers du moteur « Polybase » la possibilité de faire des requêtes en langage SQL pour ensuite exposer les données à des fins d’analyse.

L’offre PaaS :

Plusieurs services de données ont été mis en œuvre dans Azure et sont accessibles via son portail. Ceux-ci sont mis à jour régulièrement et certains sont encore en mode « Preview ».

Pour le traitement des « Big Data », Microsoft a intégré dans sa plateforme Azure le moteur Hadoop de la société HortonWorks bien connu pour son architecture de données distribuées, HDFS, sous le nom de « HD Insight ».

Avec HD Insight », sont remodelés, la création de clusters pour la parallélisation des traitements de données, la mise en œuvre des process « Map/Reduce » pour tout type de transformation comme l’agrégation ou le nettoyage de données déstructurées comme par exemple le comptage de l’occurrence de mots parmi une multitude de messages comme des tweets, de sms ou flux RSS.

Pour ce faire le service ELT « Azure Data Factory » avec une interface très conviviale, permet d’ordonnancer tous ces traitements, il permet d’extraire des données sources, d’appeler des transformations en passant par des bibliothèques qui s’appuient sur les fonctions « Map/reduce » pour simplifier l’écriture du code. Par exemple « Hive » pour exécuter des traitements SQL DDL (CREATE…) ou DML (SELECT…) ou alors « Pig » pour appeler comme dans un ETL des fonctions de transformations (Agrégation, comptage, boucles,…) et enfin les charger dans des « Data Warehouses ».

Pour la gestion des entrepôts le service « Azure SQL Data Warehouse » peut être utilisé pour mieux exposer ses données à des outils d’analyse comme SSAS ou Power BI.

Dans le cas de besoins d’analyse ou monitoring temps réels et pour une prise de décision rapide, le service « Azure Stream Analytics » permet de traiter des flux de données très volumineux et avec un débit important comme des « Click Streams » provenant de site internet e-commerce par exemple, de les ingérer, les regrouper, agréger ou filtrer grâce à une bibliothèque de fonctions proche du SQL, pour ensuite les exposer à des fins d’analyse avec Power BI ou de Machine Learning. « Azure Stream Analytics » peut s’appuyer sur les services d’Azure de files d’attente « Event Hub » et « Services Bus ».

Enfin avec le service, « Azure Machine Learning », les « Data Scientists » peuvent faire de l’analyse prédictive dans le cloud et exposer leur résultats à des outils front ou des process via à un appel de « Web services ». Ce service permet de créer dans un concepteur dédié, appelé « ML Studio », des unités de traitement appelés expériences comportant une boite à outils pour créer des jeux de données sources, évaluer des modèles, les entrainer ou faire du « Scoring », effectuer tout type de transformations en mode graphique comme on le ferait avec SSIS mais d’utiliser aussi le langage R pour le faire et enfin produire les résultats statistiques et de probabilités.

Pour mettre en œuvre tous ces services il existe un système d’abonnements dont le coût varie selon un ensemble de paramètres comme la volumétrie, le temps, le débit….

Vous constatez que maintenant, avec l’offre PaaS BI dans Azure, il existe un kit de services clef en main de plus en plus matures, robustes et performants pour traiter des données de Business Intelligence en Back Office surtout pour des process associés au « Big Data » au temps réel et au « Machine Learning ».

A venir :

Comme vous avez pu le constater, le paysage autour de la Business Intelligence Microsoft c’est pas mal transformé depuis ces derniers temps et il continue encore. SQL Server 2016 est annoncé avec de nouvelles évolutions comme l’unification de « BIDS » et « SSDT », l’intégration de HDInsight et Polybase qui n’étaient actuellement que dans APS ou Azure, la possibilité d’intéragir avec Azure Data Factory depuis SSIS, des nouvelles fonctionnalités de « Data Vizualisation » dans SSRS et la possibilité de publier des .rdl dans le site Power BI. Du nouveau également dans SSAS Tabular puisque les relations « many to many » seront supportées et enfin des évolutions autour de SSAS multidimensionnel et MDS.

Concernant les outils « Front », une nouvelle version d’Excel dans Office 2016 est à venir où Power Query va maintenant être natif à Excel, la possibilité maintenant de créer un rapport Power View sur des cubes SSAS multidimensionnels et d’autres fonctionnalités qui seront probablement annoncées bientôt.

Ce qui reste à améliorer :

La BI MS a encore de beaux jours devant elle, par contre l’offre peut paraître encore « décousu » et pas forcément simple à comprendre tant au niveau des usages, que du mode de licensing.

Par exemple, si l’utilisateur demande une publication de son modèle « Power Pivot » dans SharePoint, on s’aperçoit que le mode de licencing est complexe et qu’il faut composer entre les licences d’Excel 2013, de SQL Server mais aussi de SharePoint.

De plus, il faut un travail important de pédagogie envers l’utilisateur qui ne comprend pas pourquoi il y a autant d’offres. Par exemple, il peut être perdu avec les outils de « Data Vizualisation »: SSRS, Datazen, Power View…. donc il faut lui expliquer les correspondances qu’il y a entre les usages qu’on peut en faire et les outils associés.

Enfin, dans un composant comme Power View qui est distribué à la fois dans Excel et SharePoint On-premise, il peut y avoir des petits plus qui sont dans l’un mais pas dans l’autre, par exemple l’export vers Power Point qui n’est que dans SharePoint. De plus les composants Power BI d’Excel 2013 et du Power BI Designer ont été découplés mais les fonctionnalités sont beaucoup plus avancées dans le dernier.

Pour conclure :

Comme on vient de le voir avec les outils de « Data Vizualisation », les enjeux à venir de Microsoft vont être de les faire converger pour en faire une offre homogène, multi-usage, en plus d’être muti-device et cross-platform.

De même, une convergence entre le cloud et le « On-Premise » doit continuer à s’installer autour de l’hybride : faire en sorte homogénéiser les outils, les échanges de données ou de process inter plateformes, où tout sera transparent pour les développeurs, administrateurs, DBA, Data Manager ou Stewardship, Business Analyst, Power User, et utilisateur final, et où ils ne s’apercevront plus de la différence entre concevoir ou consommer de la BI sur un PC ou un mobile, « On-Premise » ou dans le cloud.

Sébastien MADAR

MVP SQL Server

Power Query l’ETL « Power-User » dans l’ombre de SSIS

Comparatif Power Query / SSIS

Power Query atouts et faiblesses

Initialement lancé sous le nom de Data Explorer, le mini ETL Power Query de la suite Power BI s’est amélioré et la version actuelle (2.9.3547.162) apporte quelques nouveautés.

Dans le cadre d’un projet Power BI dont les données sont récupérées d’une liste SharePoint, j’ai eu l’occasion d’utiliser ou essayer d’utiliser devrais-je dire, certaines de ces transformations.

La nouvelle version de Power Query propose beaucoup de nouvelles transformations, elles sont principalement accessibles via une fenêtre d’édition de formules d’où un script est généré.

Elles sont classées dans différentes catégories en fonction du type de transformation.

La première chose que nous remarquons, est que cette dernière ne donne pas beaucoup d’options et applique même parfois la transformation avec une valeur par défaut. Je pense à la transformation qui enlève les doublons en prenant comme référence la première colonne de la table.

Notons au passage que pour chaque transformation, une requête est générée en Langage M.

Du coup pour personnaliser une transformation il faudra être courageux et aller plonger dans la requête susnommée.

1) Power Query : Dans les faits / Dans le M

Les transformations offertes sont multiples et comme annoncé plus haut, chacune d’elle génère du code en langage M.

Prenons comme exemple la transformation

Celle-ci permet de séparer une colonne en plusieurs en utilisant un délimiteur spécifique.

Dans notre cas nous utiliserons le caractère « Espace » comme délimiteur pour séparer une colonne (Created By1) contenant le nom et le prénom d’une personne dans deux colonnes distinctes.

Power Query nous propose plusieurs délimiteurs ainsi que la position à prendre en compte.

Au final nous obtenons le résultat attendu d’une manière très simple.

Il faut l’avouer, la mise en œuvre des transformations fait partie des forces de Power Query.

Sauf que pour aller, plus loin il faudra regarder ce qui se cache derrière.

Et derrière se cache une requête écrite en M. En voici l’extrait correspondant à notre requête.

Notons aussi qu’il est possible d’écrire des formules via un assistant, comme dans le cas de la construction d’une nouvelle colonne.

Contrairement à SSIS avec le « derived column » par exemple où on a une fenêtre proposant la liste des fonctions disponibles avec une petite description, ici aucune aide n’est proposée.

La seule façon d’ajouter une formule ou une expression c’est de connaître celle-ci (un lien décrivant la liste des formules est disponible dans la partie référentielle).

Chose non moins importante concernant la syntaxe des formules : elles sont sensibles à la casse.

Et c’est par un pur hasard que j’ai constaté cela, en essayant de mettre en place une formule avec IF. En mettant les mots clé en majuscules, nous avons une erreur pas très explicite il faut l’avouer.

2) Comparatif de certains composants entre Power Query et SSIS

Après un rapide tour des différentes transformations proposées dans Power Query, on se rend compte que la plupart de ces transformations sont disponibles dans SSIS mais de façon moins ergonomique.

Je prends comme exemple la transformation

Cette dernière est accessible sous SSIS via le composant « Flat File Source ».

De la même manière, la transformation présente dans l’interface de Power Query est disponible sous Intégration Services via le composant « Aggregate ».

En partant de ce constat, on s’aperçoit que Power Query fait abstraction des différents composants de SSIS qu’il faut mettre en place afin d’avoir accès à certaines transformations.

Il met ainsi à disposition ces transformations directement via l’interface.

Ci-dessous nous avons un tableau faisant l’analogie de certaines transformations entre Power Query et SSIS.

POWER QUERY	SSIS
Merge	Merge Join
Unpivot	Unpivot
Remove Duplicates	Sort
Group By	Group by (Aggregat)
Use First Row as Headers	Column name in the First Data Row (Import Flat File)
Insert Custom Column	Derived Column

Remarque :

Ce tableau n’a pas pour objectif l’exhaustivité mais juste pour exemple comparatif de quelques transformations.

3) Possibilités offertes par Power Query

Les fonctionnalités de Power Query peuvent s’appliquer aussi bien sur les colonnes que sur les lignes d’une table.

Ainsi dans la suite nous classerons ces fonctionnalités en fonction des éléments sur lesquels ils s’appliquent.

a) Réduction

Dans cette section on trouve tout ce qui permet d’enlever une partie des lignes ou des colonnes de la table. Cette réduction peut se faire à plusieurs niveaux.

	Supprimer les n premières lignes de la table.
	Supprimer les colonnes sélectionnées de l’affichage et empêcher leur utilisation ultérieure.
	Supprimer les lignes alternées de la table en commençant par la première ligne à supprimer et en indiquant le nombre de lignes à supprimer et le nombre de lignes à conserver.
	Supprimer les lignes contenant des erreurs dans les colonnes actuellement sélectionnées.
	Supprimer toutes les lignes de la table dans lesquelles les valeurs dans les colonnes sélectionnées ont un doublon dans les valeurs antérieures. La ligne contenant la première occurrence d’un ensemble de valeurs n’est pas supprimée.
	Spécifier le nombre des premières lignes à conserver dans la table.
	Spécifier une plage de lignes commençant par une première ligne et incluant le nombre de lignes à conserver dans la table.

b) Transformation

Les transformations disponibles ne sont pas encore nombreuses, mais la section reste prometteuse au vu de la facilité qu’elle apporte.

	Une colonne de texte peut être divisée en plusieurs colonnes de deux façons :• Par délimiteur• Par nombre de caractères
	Résumer les données par valeurs de ligne. Par exemple, avec les colonnes État, Ville et Population dans une table, on peut : Effectuer un regroupement selon l’État et calculer le nombre de villes dans chaque État ou la somme de la population des villes pour obtenir la population de chaque État. Effectuer un regroupement selon la ville et dénombrer les instances des noms de ville.
	Remplacer un élément dans la colonne par une autre valeur.

	Pour une valeur de texte, modifier le mode d’affichage des valeurs de la colonne :• Minuscules• MAJUSCULES• Mettre en majuscules la première lettre de chaque mot• SupprEspace• Nettoyer• JSON• XML Pour une valeur de type Date/heure, modifier le mode d’affichage des valeurs de la colonne : • Date • Heure • Jour • Mois • Année • Jour de la semaine
	Les noms d’en-tête de la table sont remplacés par les valeurs des cellules de la première ligne du tableau
	Transformer les colonnes sélectionnées en paires attribut-valeur.

c) Création

Même constat que la section « Transformation », peu fourni, mais un travail agile peut être fait sur les colonnes.

	Insérer une nouvelle colonne après la dernière colonne de la table. Les valeurs dans la nouvelle colonne sont déterminées en sélectionnant le type de colonne à insérer :• Personnalisé (vous définissez la formule qui calcule les nouvelles valeurs)• Index
	Crée une copie de la colonne sélectionnée à droite de la table. Le nom donné à la nouvelle colonne est Copie de <nom de la colonne>.

d) Combinaison

Power Query permet de créer plusieurs venant de différentes sources, mais offre aussi la possibilité de les combiner afin d’enrichir son jeu de données.

Cette section en est l’illustration parfaite.

	Dans la grille Aperçu, créer une requête à partir de deux requêtes existantes. Un résultat de requête contient toutes les colonnes d’une table primaire, une colonne servant de colonne unique contenant un lien de navigation vers une table liée. Pour plus d’informations sur la fusion des requêtes.
	Dans la grille Aperçu, créer une requête qui contient toutes les lignes d’une première requête suivie de toutes les lignes d’une deuxième requête.

4) Nouveautés Power Query Décembre 2013 (Microsoft)

Beaucoup de nouveautés ont été introduites avec la mise à jour de Décembre.

Ci-dessous la description de ces nouveautés.

Power Query peut maintenant se connecter à plus de sources de données comme :
Sybase IQ
Exchange
Dynamics CRM Online
Quand vous sélectionnez plusieurs tables dans le panneau de navigation, Power Query détecte automatiquement les relations de base de données.
Le remplacement des requêtes partagées a été supprimé.
Vous pouvez maintenant partager votre requête à la volée.
De nouveaux boutons ont été ajoutés au ruban de l’éditeur de requête, à savoir Fill Down, Sort Ascending/Descending and Number Transformations.

5) Références

Les Blogs

http://blogs.microsoft.fr/technet/power-query/feed

http://cwebbbi.wordpress.com/category/power-query/

http://devinknightsql.com/category/power-query/

Langage M (MSDN)

http://msdn.microsoft.com/en-us/library/dd285271.aspx

Descriptif des composants de Power Query

http://office.microsoft.com/en-us/excel-help/guide-to-power-query-context-menus-HA103993856.aspx?CTT=5&origin=HA104003813

Power Query UnPivot

http://office.microsoft.com/en-us/excel-help/unpivot-columns-HA104053356.aspx?CTT=5&origin=HA103993856

Power Query Liste des Formules

http://office.microsoft.com/en-us/excel-help/power-query-formula-categories-HA104122363.aspx?CTT=5&origin=HA104003813

Microsoft Power Query Preview for Excel Formula Language Specification

http://go.microsoft.com/fwlink/?LinkID=320633

Liste de mots clé du langage M:

Ce tableau peut être bien utile au moment où on doute de l’existence d’une expression.

Comme le « switch » par exemple …. Il faudra faire sans.

Opérateurs unitaires	NOT, IS
Constant expressions	ERROR, FALSE, TRUE
Typage	#BINARY, #DATE, #DATETIME, #DATETIMEZONE, #DURATION, #INFINITY, #NAN, #SECTIONS, #SHARED, #TABLE, #TIME
Opérateurs binaires	AND, OR
Expressions conditionnelles	IF, THEN, ELSE
Type assertion	AS
Méta données	META
Autre	EACH, IN, LET, OTHERWISE, SECTION, SHARED, TRY

Cheikh SECK

[JSS 2013] Session : SSIS Design Patterns

Speakers : David Joubert, Thomas Ricquebourg

SSIS : petite chronologie de l’ETL chez Microsoft

SQL Server 7 : Data Transformation Services (DTS)
SQL Server 2005 : SQL Server Integration Services

L’importance du SSIS:

Les speakers commencent par nous dire que malgré qu’aujourd’hui l’accent est mis sur la BI Self-Service, SSIS reste notre principal allié dans la plupart des cas, par exemple pour alimenter un datawarehouse, ou si la volumétrie des données est très importante …

Parce qu’un projet BI, c’est 70% du temps consacré à l’importation, le nettoyage et la transformation des données, d’où vient l’importance du SSIS.

Pour quoi des design patterns?

« Design Pattern » c’est les modèles de conception que nous utilisons pour optimiser et rationaliser nos packages, et ça se traduit en l’utilisation du bon composant au bon endroit selon la situation (gestion des MAJ, historisation … )

Ils donnent l’exemple de dessiner un smiley sur Paint :

On peut arriver à le faire à la main.
Mais avec des notions de patron, on utilisant les ronds et les formes de Paint, on peut arriver à faire quelque chose de plus conventionnel et beau 🙂 .

C’est pareil lorsqu’on est dans SSDT, on peut arriver à faire des packages qui marchent et qui donnent le résultat attendu (ils nous montrent un package très mal structuré ou on ne sait pas vraiment ce qu’il fait), mais on utilisant des design patterns on arrivera à faire un travail plus optimisé, organisé et mieux structuré (Ils donnent comme exemple un package de chargement de DWH parallélisé avec gestion d’erreur, et on comprend très vite qu’il s’agit d’un DFT de dimension SCD1 sans prise en charge de SCD2).

Critères à prendre en considération :

L’outil de développement : SSDT
Les données : volumétrie
La solution : l’architecture
Infrastructure : performance
Les gens : compétence

Points forts et points faibles de SSIS :

Points Forts :
- Outil complet
- Forte maintenabilité
- Travail en mémoire
- Bonne ergonomie
Points Faibles :
- Parfois moins performant que du script SQL
- Gestion de métadonnées figée
- Scénario

Il est important de connaître le caractère synchrone ou asynchrone des composants :

Non bloquant : Conditionnal Split \ Data Conversion \ Derived Column \ Lookup –> Le moteur fait l’opération au fur et au mesure de l’arrivée des données

Semi bloquant : Merge \ Merge Join \ Union All –> Le moteur peut commencer l’opération même avant le chargement total des données dans la mémoire.

Bloquant : Pivot \ Aggregate \ Sort –> Le moteur ne fait l’opération qu’une fois tout est chargé dans la mémoire.

Les speakers nous font une démo sur le Moteur SQL VS SSIS :

Ils nous montrent les variations des performances par rapport à l’utilisation de différents composants : Par exemple un simple chargement de 20millions de lignes avec un composant de tri prend 50 secondes, la même opération a mis 10 secondes si on fait le tri dans la requête SQL (Order By).

Parallélisation :

– La parallélisation peut être appliquée sur les 3 objets de SSIS :

Package
Control Flow
Data Flow

– Efficace pour gagner en performance.

– En utilisant les Propriétés : MaxConcurrentExecutables et EngineThreads

Un petit conseil : « Diviser votre solution en opérations simples permettra une plus grande facilité de parallélisation »

Une démo pour montrer comment on peut contrôler via la propriété MaxConcurrentExecutables le nombre d’objets que l’on veut lancer en parallèle.

Il a 10 DFT qui peuvent tous se lancer en parallèle, mais la capacité maximale du moteur ne le permet pas, donc il fixe la propriété MaxConcurrentExecutables à 2 (qui était à -1 par défaut) et maintenant les DFT vont s’exécuter 2 à 2.

L’inconvénient c’est que le choix des objets est totalement aléatoire, cela veut dire que dans le cas ou il y a des objets qui sont lents en exécution et d’autres courts, rien ne nous garantie que les lents vont être exécutés en parallèle afin de gagner en temps global d’exécution. L’alternative dans ces cas est de mettre les objets que l’on veut exécuter en parallèle dans des « sequence countainer ».

Après ils nous montrent un exemple de chargement de plusieurs fichiers avec une boucle « Foreach »avec une gestion des erreurs pour que les fichiers mal formatés ou erronés ne bloquent pas l’exécution.

SCD – Slowly Changing Dimension :

Concept de base en Business Intelligence.
Composant existant dans SSIS, optimisation à apporter à la configuration de base.
Composants tiers disponibles : Kimball Method SCD (payant sur 2012)

Une démo de l’utilisation du composant SCD de base dans SSIS et son optimisation, le composant n’est pas compliqué, suivre les étapes en mettant les paramètres en fonction des cas d’usages … et à la fin génération automatique de la chaine des composants par le composant SCD.

En termes d’optimisation, on peut passer l’insert oledb en fastload, et mettre la connection en retainmachin.

UPSERT :

Cas particulier du SCD 1
On insère ou on update les lignes
3 possibilités (en plus du composant SCD vu précédemment) :
- Double lookup (démo : Temps exéc 50 secondes)
- Double lookup vers table temporaire (11 secondes)
- Script SQL avec MERGE (9 secondes)

Range Lookup :

Le range lookup correspond à un lookup sur une theta-jointure
On attend la feature depuis 2005
4 possiblités (en attendant) :
- Lookup
- Conditionnal split
- Script
- SQL

NULL Substitution :

Lorsqu’on récupère les lignes qui ne matchent pas sur un lookup on remplace le NULL par la clé inconnue.

Il est plus judicieux de configurer du « Ignore Error » et de remplacer les NULL en une seule fois avec un composant « drived column »

Foudhil – Consultant décisionnel MCNEXT

[PASS SUMMIT 2013] CAT : High Performance, High Volume Data Movement in Azure

Speaker : Steve Howard

Niveau de la session : 300

Plusieurs outils associés pour le transfert de données :

BCP
Custom Code (BulkCopy API, TVP, .NET)
SSIS

Il existe 5 patterns du mouvement de données :

Telemetry Aggregation
Hierarchy Aggregation
Data Synchronisation et Bulk mouvement
Data Stream Multiplexing (Sharding)
Cloud ETL

Dans Sharding :

Le Principe : améliorer les performances du « Scale out »

Les datasets sont distribué dans plusieurs bases

Il existe un composant SSIS dans codeplex « Database Sharding »

Démo

Script qui lance plusieurs Package et place en file d’attente les données

Sébastien Madar

[PASS SUMMIT 2013] Automate your ETL infrastructure with SSIS and PowerShell

Speaker : Allen White – Practice Leader UpSearch

Niveau de la session : 200

1. Simple ETL Process :

* Pour gérer les modifications dans la source et les impacter dans la cible (DWH) : Change Data Capture, Change Tracking, Simple Triggers,

* Exemple d’incremental update (aka UPSERT) :

Source –> Correlate –> Destination (en cas de No Match)

–> Filter (en cas de Match) –> Updates stage

* Le merge en T-SQL n’est pas performant avec les gros volumes de données,

2. Basic BIML Introduction :

* BIML (Business Intelligence Markup Language) :

* Langage basé sur le projet « Vulcan » de Microsoft (à voir sur codeplex),

* BIML utilise le XML pour définir (construire) un package SSIS,

* Il faut installer « BIDS Helper » pour pouvoir faire du BIML directement dans le projet SSIS (clic droit sur le projet, « Add new BIML File »),

* Le développement en BIML est simplifié grâce à l’Intellisens,

* Une fois le code XML du package prêt, il suffit de faire clic droit sur le fichier .biml (disponible sous le dossier projet « Miscellaneous ») et de choisir l’option « Generate SSIS package »,

3. BIML script with PowerShell :

* PowerShell est un langage basé sur l’objet (Object Based Language),

* La création des packages SSIS via PowerShell repose sur le namespace .NET System.Xml.XMLTEXTWRITER,

* Démo : Passer par PowerShell pour créer le script XML du package SSIS qui lui même repose sur BIML (on fait du BIML via PowerShell pour générer un lot SSIS),

4. Conclusion :

Session intéressante qui permet de découvrir d’autres possibilités de créer un package SSIS (autre que via l’éditeur graphique et le drag and drop qui s’en suit). Elle nécessite, néanmoins, la connaissance des 2 langages BIML et PowerShell afin de tirer profit de la session et ne pas se contenter de suivre un code qui est assez long. L’intérêt de créer des packages via PowerShell ou BIML par rapport à la génération classique des lots (via simple clic droit et choix des composants via la toolbox) est un sujet à débattre. On parle d’automatiser et donc simplifier les développements mais je n’en sors pas totalement convaincu eu égard à la longueur du code et au fait de devoir TOUT coder dans le package (connexion, composants, etc.) ! A suivre…

Ismaïl Ben Lamine

[Pass Summit 2013] Reduce Reuse Recycle – Automating your BI Framework

Speaker : Stacia Misner, Principal Consultant Data Inspirations

Niveau de la session : 300

Agenda :

Frameworks Options : which ways ?
Changing a BI solution : the long way
Changing a BI solution : the short way

1. The long way :

ETL design patern : lots templates réutilisables (selon staging ou dwh)

Master Extract Package (staging) et Master Transform load Package (chargement du dwh) et entre les 2 : création d’un snaphost sur la base (retour arrière si erreur)

Extract Package = duplication du lot par copy manuel et modif des mapping (avec convention de nommage des composants par trigramme)

Load Pattern pour les faits (insert des news rows seulement) et dimension (lecture all data et update si besoin : pattern selon Type 0,1 ou 2 du SCD mais transformations qui varient selon les dimensions)

Facts Extract for Ongoing Load

2. The Short way :

Dimension loader Package Creator: utilitaire à télécharger (google) = demo de 10 minutes : ça semble pas mal (merge en t-sql pour les dimensions) mais pas certain que ce projet soit bien maintenu (lots générés en version sql 2008).

BIML : présentation du biml ratée (la présentatrice a dû passer le relais à son collègue en secours car elle n’y arrivait pas) .

Piste très intéressante d’utiliser le BIML (sert aussi pour du SSAS par exemple).

Présentation confuse (mais très prometteuse) d’un outil (payant !) de génération du biml (notamment pour SSIS) : logiciel de la société varigence.

A noter que c’est cette société qui fournit dans le BIDS helper le générateur du code BIML (menu conceptuel générer les lots ssis et créer un fichier biml).

3. Conclusion :

Déception : options d’automatisation = copier coller de templates SSIS … et templates qui ne sont pas adaptés à la réalité (fact table = insert new row only donc par exemple pas de gestion des suppression, utilisation du scd comme composant…).

De plus la session a parlé plus de concepts généraux de conception de lots SSIS (gestion de proportion du temps de présentation des sujets est ratée) plutôt que d’industrialisation via d’autres techniques de dev hors SSIS (powershell + SSIS, .NET + SSIS, biml).

A creuser donc : biml surtout et tester (trial version) de Varigence.

En complément, sur le site http://www.varigence.com/ :

Vous pouvez regarder les vidéos de présentation de leurs outils : MIST (notamment), voir d’autres outils (VIVID?).

Bref je n’avais même pas entendu le nom de la boîte Varigence ou les outils utilisés très rapidement (MIST) : je dois être sourd et pas de chance aveugle (ça fait beaucoup pour une seule personne) également (car je n’ai vu aucun logo de MIST ou de Varigence)

Pascal Rouzé

Introduction à Data Explorer Preview pour Excel

Contexte

Nous travaillons avec beaucoup de fichiers Excel, mais n’avons pas forcément accès à un ETL de type SSIS. Ne vous inquiétez pas, Microsoft a pensé à cela et travaille actuellement sur une version finale de l’add-in Excel (2010 SP1 et 2013) permettant d’effectuer des tâches d’intégration basiques et courantes. Cet article a pour but de vous introduire le fonctionnement de cette preview add-in gratuit.

Lire la suite →

Intégration d’une source XML en SSIS

Contexte

Dans le cadre d’une mission, nous intégrons des données de facturation provenant d’un fournisseur externe , afin de les restituer dans nos cubes d’analyse. Ces informations transmises par fichiers XML sont intégrées dans une base de données SQL Server.

La structure du fichier d’échange XML n’est pas sous contrôle, celle-ci est imposée par l’émetteur : l’adaptation du consultant BI s’impose.

Voyons comment intégrer une source XML au travers « SQL Server Integration Services ».

L’influence de la structure du fichier

XML

Le XML « Extensible Markup Language » est un langage de balises lisible pour l’homme permettant de structurer de l’information en vue d’échange.

Voyons les composants XML de base constituant un arbre structuré

Nœud document	Elément racine à tout XML. Il est le point de départ
Balise	Une balise est un nom commode pour désigner les constructions entre deux chevrons <…> dans un fichier XML
Texte	Un nœud texte n’ayant pas d’enfant. Il est toujours contenu dans une balise.
Attribut	Un attribut est un nom et une valeur. Il est toujours porter par une balise. Sa valeur ne sera jamais nulle, mais vide <balise attribut= ‘’/>. L’attribut est unique sur une balise qui peut en avoir plusieurs. <balise attribut1=’valeur1’ attribut2=’valeur2’ …/>

Étudions dans un premier temps la structure de notre fichier XML.

Notre cas exemple

Structure du fichier

<?xml version="1.0" encoding="iso-8859-1"?>
<JD_GENERIQUE xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Definition>
    <PieceComptable>FARC130014</PieceComptable>
    <Redevance>RC</Redevance>
    <Activite>Act1</Activite>
    <SSActivite>ssAct1</SSActivite>
    <ServTransport>Train</ServTransport>
    <Date>10/12/2012</Date>
  </Definition>
  <TableauDetaille Search_Code="044101">
    <ENTETE>
      <ElementEnTete Nom="NM">044101</ElementEnTete>
      <ElementEnTete Nom="TYPCIRC">réelle</ElementEnTete>
      <ElementEnTete Nom="HD">00:10:00</ElementEnTete>
      <ElementEnTete Nom="DE">Cerbère</ElementEnTete>
      <ElementEnTete Nom="FE">Portbou</ElementEnTete>
      <ElementEnTete Nom="LT">0,9</ElementEnTete>
      <ElementEnTete Nom="MT">3,08</ElementEnTete>
    </ENTETE>
    <BODY>
      <LIGNE>
        <ElementDetail Nom="NL">994</ElementDetail>
        <ElementDetail Nom="SEGMTARIF">LC</ElementDetail>
        <ElementDetail Nom="LNG">0,0</ElementDetail>
        <ElementDetail Nom="TCT">FMC</ElementDetail>
        <ElementDetail Nom="PU">3,425</ElementDetail>
        <ElementDetail Nom="COEFTRAFIC">0,600</ElementDetail>
        <ElementDetail Nom="M">0,00</ElementDetail>
      </LIGNE>
      <LIGNE>
        <ElementDetail Nom="NL">995</ElementDetail>
        <ElementDetail Nom="SEGMTARIF">LC_FM</ElementDetail>
        <ElementDetail Nom="LNG">0,9</ElementDetail>
        <ElementDetail Nom="TCT">FMC</ElementDetail>
        <ElementDetail Nom="PU">3,425</ElementDetail>
        <ElementDetail Nom="COEFTRAFIC">1,000</ElementDetail>
        <ElementDetail Nom="M">3,08</ElementDetail>
      </LIGNE>
…

JD_GENERIQUE	Balise racine du document
Definition	Balise structurante, définissant le fichier dans sa globalité.
PieceComptable RedevanceActivite SSActivite ServTransport Date	Balises de données contenues dans la balise « Definition »
TableauDetaille	Balise structurante, définissant des éléments de facturation
Entete	Balise structurante, regroupant des éléments d’entête.
ElementEnTete Nom= »NM » ElementEnTete Nom= »TYPCIRC » ElementEnTete Nom= »HD » ElementEnTete Nom= »DE » ElementEnTete Nom= »FE » ElementEnTete Nom= »LT » ElementEnTete Nom= »MT »	Balises de données avec attribut structurant
BODY	Balise structurante, définissant le corps
LIGNE	Balise structurante, définissant une ligne
ElementDetail Nom= »NL » ElementDetail Nom= »SEGMTARIF » ElementDetail Nom= »LNG » ElementDetail Nom= »TCT » ElementDetail Nom= »PU » ElementDetail Nom= »COEFTRAFIC » ElementDetail Nom= »M »	Balises de données avec attribut structurant

? Une facture est constituée d’une unique entête, de plusieurs tableaux de facturation. Ces derniers sont composés d’une entête de tableau et de plusieurs lignes de détails.

Traitement des valeurs

Le traitement d’un fichier XML revient à remettre à plat les données qui ont été hiérarchisées. La difficulté vient de l’aisance à y arriver.

Le traitement d’un nœud comme celui « Definition » est relativement simple. La mise en colonne de l’information est triviale avec les outils SSIS

Le traitement d’un nœud « Entete » ou « Ligne » demandera plus de manipulation pour préparer les données à l’intégration.

Dans un but d’intégration des données, nous remarquerons qu’il y a de nombreux attributs de balise présent et ceux à plusieurs reprise dans les balises « ElementEntete » et « ElementDetail ». Ces derniers ont une valeur fonctionnelle réelle. L’information aurait pu être résumée comme suit pour notre exemple :

Le format du fichier XML nous étant imposé en l’état, aucun changement de la source n’est possible.

Comment traiter ces balises XML avec attributs fonctionnels ? Nous aborderons une façon de faire sans qu’elle soit unique dans la section suivante. Celle-ci traite de l’utilisation des tâches SSIS mise à disposition dans BIDS.

Traitement par SSIS sous BIDS SQL Server 2008 R2

Source XML

Comme son nom l’indique, la tâche Source XML permet d’utiliser les données stockées dans des fichiers XML dans le flux de données. Si le XML provient d’un fichier, il suffit de spécifier le chemin d’accès ou obtenir le chemin du fichier grâce à une variable au moment de l’exécution. Il est également possible de spécifier le chemin d’accès d’un XSD qui définit la structure de notre XML. Le XSD peut ce générer à partir du fichier XML renseigné.

Création d’un composent « XML Source ». Nous renseignons le document source avec la localisation. La génération d’un fichier XSD est obligatoire pour la structure.

Sur la seconde page du composent « Columns », nous retrouvons les structures de données qui ont été importées.

Pour garantir la hiérarchie du fichier XML d’entrée, le composant génère une multitude de structures imbriquées. Plusieurs sorties sont disponibles et un identifiant unique ID apparait pour garantir l’arborescence.

SSIS rajoute une clé de substitution à chaque élément enfant. Un identifiant ParentID est ajouté au parent, un ChildID à l’enfant et un ChildParentID pour désigner la liaison entre le parent et l’enfant. Les données sont aplaties dans une forme tabulaire en sortie du composant.

En fonction de la complexité de l’arborescence du fichier XML, nous pouvons vite imaginer la difficulté de gérer cette solution.

Il reste à achever le traitement de transformation une fois les sources de sortie du composent identifiées. Rappelez-vous les attributs de balise comme ElementEntete ou ElementDetail ! Comment prendre en compte le nom du champ contenu dans l’attribut et la valeur réelle dans le texte de la balise ? Et bien, on va pivoter le tout.

Pivot

Cette tâche de transformation de tableau croisé dynamique permet à un ensemble de données normalisées en ligne d’être modifier en données moins normalisées, mais plus compacte. Ceci grâce au croisement dynamique des données du flux.

A partir d’une représentation tabulaire d’un niveau (balise XML). La ligne d’entête est à extraire de la chaine.

XML source extrait

Représentation tabulaire de sortie souhaitée

ENTETE_ID	NM	TYPCIRC	HD	DE	FE	LT	MT
3	044101	Réelle	00:10:00	Cerbèce	Portbou	0,9	3,08

En sortie du composant XML Source, la structure générée est la suivante :

Les éléments attribut de balise XML sont récupérés sous le champ « Nom » et la valeur sous « text » (voir image ci-dessus).

En utilisant le composant de tâche Pivot, nous obtenons la représentation tabulaire de sortie souhaitée (voir ci-dessus le tableau). En effet, nous allons faire une transformation de pivot avec la valeur « Texte », en colonne « Nom » et en ligne l’identifiant de l’entête.

Pivot_Key	Nom
Set Key	ENTETE_ID
Pivot_Value	Text

Mise en place de la tâche pivot.

1 – Positionner sur l’onglet des propriétés d’entrées et sortie. Etendre jusqu’au « Input Columns » est renseigné le paramètre « PivotUsage » des champs comme suit :

LIGNE_ID : PivotUsage à 1
Nom : PivotUsage à 2
Text : PivotUsage à 3

PivotUsage	0	La valeur n’est pas pivotée, elle passe en sortie du traitement
	1	La valeur fait partie de la clé d’ensemble qui identifie une ou plusieurs rangées dans le cadre d’une série. Toutes les lignes d’entrée avec la clé même ensemble sont combinées en une seule ligne de sortie.
	2	La colonne est une colonne pivot. Au moins une colonne est créée à partir de chaque valeur de colonne.
	3	Les valeurs de cette colonne sont mises en colonnes qui sont créées à la suite du pivotement.

2 – Configuration des champs de sortie. Cette partie est la plus manuelle

a. Etendre « Pivot Default Output » jusqu’au « Output Columns ».
b. Cliquer sur « Add Columns » pour obtenir 8 colonnes
c. Sélectionner la première colonne

i. Renommer la « LIGNE_ID »
ii. Changer la propriété « SourceColumn » pour qu’il corresponde avec la propriété « LineageID » du champ LIGNE_ID présent dans les « Input Columns ». Ici 85 dans notre exemple.

d. Sur le reste des colonnes

i. Renommer les champs avec les libellés contenus dans la colonne « Nom »
ii. Changer pour tous la propriété « SourceColumn » pour qu’il corresponde avec la propriété « LineageID » du champ Text présent dans les « Input Columns ». On renseigne ici l’alimentation de la future colonne de sortie lors du process.
iii.La propriété « PivotKeyValue » qui permet le dispache des données doit-être renseigner. Les valeurs possibles proviennent de la colonne d’entrée « Nom ». Exemple pour la sortie NM, PivotKeyValue = NM

Sortie du composant Pivot

Merge Join

Ce composant de rapprochement des données est utilisé pour reconstruire dans un dataset les valeurs qui seront par la suite stockées.

Nous n’allons pas revenir sur un composant simple. Il s’agit ici d’utiliser les identifiants ID générés automatiquement par la mise à plat du fichier XML.

Conclusion

Dans cet article, Intregration Services peut être utilisé pour sortir des données d’attribut des balises d’un fichier XML. Le composant source XML effectue un travail amont de tabularisation des données qui est simple d’exploiter. Le pivotement des données est clé dans l’exemple pris. N’oubliez pas que le traitement du fichier se réalise en mémoire vis-à-vis des volumétries.