Sylvain Pagès

Avec l’ouverture des données Power BI Self-Service vers les utilisateurs (vu dans plusieurs de mes missions) les métiers veulent gérer eux même les droits ou périmètres sur de nombreux utilisateurs finaux (>1000).

En effet dans certains domaines, nous avons une forte contrainte de confidentialité des données contenues dans les cubes SSAS. Les juridictions qui doivent s’appliquer peuvent être changées avec des cadences variables allant de la journée, à des cadences inférieures (temps réel). Pour cela, l’ensemble de la sécurité mise en œuvre est stocké dans la source de données du cube, qui détermine qui peut voir quoi.

A travers cet article, nous allons voir comment rendre dynamique une sécurité de cube SSAS basée sur l’appel à une procédure stockée.

Lire la suite →

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.

Les articles des consultants et experts Infeeny

Auteur : Sylvain Pagès

Sécurité dynamique dans les cubes SSAS avec SQL Server 2012

Intégration d’une source XML en SSIS