[JSS 2013] Session : BI en temps réel SQL Server

Objectif : Capturer un grand nombre d’évènements, les corréler entre eux pour permettre une analyse et un reporting en temps réel

Temps réel BI avec Analysis Services (Architecture) :

Architecture Temps réel BI avec Analysis Services

Source JSS 2013

La capture d’évènement se fait avec Microsoft StreamInsight.

Microsoft StreamInsight est une plate-forme puissante prévue pour développer et déployer des applications de traitement des événements complexes.

Il permet : la corrélation de source de données multiples et extraction, de gérer des flux à temps réel, d’avoir une historique rediffusion de l’événement, la gestion des changements de données pour l’entrepôt de données.

  • Intérêt de StreamInsight
    • Une complétude entre les outils de captures temps réels et les outils d’analyses BI
    • Une adaptabilité du reporting selon les besoins clients

Architecture StreamInsight

2-Architecture_StreamInsight

Source JSS 2013

  • Capture des évènements avec Microsoft StreamInsight

La logique d’intégration des données repose sur un flux se propageant d’un adaptateur source à un adaptateur de destination. Le flux de données est constitué d’évènements dont le type peut être représenté suivant des évènements suivis.

Un évènement est constitué de 2 parties :

  • Header
  • Payload

L’entête (Header) indique le type d’évènement, il peut être de deux types INSERT ou CTI (Current Time Increment). Le Payload est une structure .net contenant les données associées à l’évènement.

Le modèle d’évènement (Payload) est décliné en trois types :

  • Interval Model
  • Point Model
  • Edge Model

Architecture BI global

3-Architecture_BI_global
Source JSS 2013

L’index ColumnStore au service du ROLAP

L’index ColumnStore apporte un réel gain en termes de performance par rapport à une utilisation d’un cube en mode ROLAP.

Rappelons que le ColumnStore a un haut niveau de compression, une structure montée en mémoire, des lectures en readahead importantes.

La version CLUSTERED  du ColumnStoreIndex (que pour SQL Server 2014) permet un accès en Read/Write au données

Temps réel et Reporting avec HTML5
La solution repose alors sur les éléments suivants :

  • Fichier.css: Feuille de style CSS contenant les styles utilisés dans la page du dashboard HTML5
  • Fichier.html: Page HTML5 représentant le Dashboard
  • Fichier.js : Contient le code Javascript faisant les appels à intervalle régulier au web service REST qui ramène les données du cube
  • SSASDataService.svc    : Markup du service web REST de l’interface StreamInsight faisant le lien avec le cube SSAS

Architecture :

 

4-Architecture_Temps_reel_et_Reporting_avec_HTML5

Source JSS 2013

Conclusion :
La solution globale permet alors :

  • De capturer des évènements en temps réel
  • Un stockage en base SQL Server en temps réel
  • Le calcul d’indicateurs directement dans Analysis Services en temps réel
  • L’affichage sur des Dashboard HTML5 multi-device en temps réel

Abdoul – Consultant décisionnel MCNEXT

[JSS 2013] Session : Troubleshooting : Les XE en pratique

Speakers: David Barbarin, Nicolas Souquet
Level: 300

Session consacrée aux événements étendus (Extended Events : XE) de SQL Server pour la gestion des problèmes. La session commence par cette phrase : «Les événements étendus fini d’en parler et place à l’action ! » 

Les XE ont fait leur apparition avec SQL Server 2008 en T-SQL et ont évolués avec SQL 2012 via une interface graphique. Cette session s’est consacré à expliquer des cas concrets d’utilisation après un bref rappel des Options de SESSION :

  • STARTUP_STATE:
    • ON : la session démarre avant que l’instance soit dispo
    • OFF : la session est démarrée auto ou par programmation
  • MAX_EVENT_SIZE
    • Limite la taille des événements que l’on capture
    • A utiliser avec MAX_MEMORY
  • MAX_MEMORY
    • Contrôle la quantité de mémoire (tampon de 4 mo logiquement divisé en 3)
    • A utiliser avec les cibles asynchrones (anneau de mémoire, pairage, fichier binaire)
    • A utiliser lors que la session va récupérer de nombreux événements
  • MEMORY_PARTITION_MODE
    • Contrôle des tampons
      • NONE : 3 tampons
      • PER_NOCE : 3 tampons par nœud NUMA
      • PER_CPU
      • 3 tampons par CPU
      • Nb tampons = 2,5 fois /CPU

Les démos :

  1. Utilisation du wizard des évènements étendus via la library des actions/ évènements que l’on peut monitorer
  2. Démo sur la gestion de montées en charge via les DMVs en « stressant » SQL via SQL-Load Generator et interrogation du résultat via Xquery
  3. Démo sur les problèmes de type réseau (ASYNC_IO_NETWORK) : Les alternatives d’affichage du résultat d’une requête génère souvent de l’attente (WAIT), ici il est démontré que le temps de création du datagrid dans SSMS est plus long que le temps de la requête
  4. Démo connaitre par quoi est appelé une procédure stockée
  5. Démo sur les deadlocks : Possibilité de tracker les deadlocks sous SQL 2008 et 2012. Il n’est pas possible de le faire avec sql server profiler

Conclusion :

David Barbarin intervient de façon récurrente lors des événements français sur SQL Server. Ces sessions sont assez techniques. Celle-ci ne déroge pas à la règle avec plus des ¾ du temps consacrés aux démos. Les possibilités des XE sont assez intéressantes mais il faut jouer avec pour comprendre l’étendue.

Marc – Consultant décisionnel MCNEXT

[JSS 2013] Session : HEKATON

Présentateur : Christophe Laporte
Niveau : 300

Christophe commence par nous poser la question suivante :

Pourquoi HEKATON ?
L’objectif est d’améliorer la performance des bases de données.

Pour cela, Microsoft commence par analyser comment sont consommées les ressources pendant les différentes transactions dans une base de données SQL Server. Et note donc que les disques Flash sont plus rapides que les disques rotatifs, mais moins rapides que la RAM.

Comment améliorer ?
Le choix effectué est celui de supprimer les éléments suivants :

  • Les latches
  • Les locks

HEKATON, c’est quoi ?
C’est un nouveau moteur SQL Server avec des tables et des indexes en mémoire, une compilation native des procédures stockées, plus de locks ni de latches.

HEKATON est pleinement intégré à SQL Server et l’utilisateur a le choix de migrer ou pas certaines tables en mémoire (c’est-à-dire sur le moteur Hekaton). Ce qui signifie qu’une requête peut utiliser des tables provenant de trois moteurs différents.

Durabilité des transactions :
Les trois options sont les suivantes :

  • Schema_And_Data (option par défaut)
  • Schema_Only (plus performant que schema_And_Data)
  • Delayed_Durability

Pendant les démonstrations, on note les points importants ci-dessous :

  • Collation : la collation est importante pendant la création d’une table HEKATON.
  • Memory-Optimized : on doit avoir memory-optimized = ON
  • Chaque transaction a un Begin TimeStamp et un End TimeStamp

Gestion des indexes :
Les indexes ne sont présents qu’en mémoire avec au moins 1 indexe et 8 indexes maximum.

On note deux types d’indexes : Hash et Range.

Conclusion :
HEKATON apporte un gain réel au niveau de la performance.

Ghislain – Consultant décisionnel MCNEXT

[Ouverture du Pass Summit 2013] Keynote

Speaker : Quentin Cark – Corporate Vice Microsoft Data Platform

1. 3 offres autour de SQL server :

Office :

  • Power BI
  • Add-in Power Query
  • Add-in Power Map

Windows Azure :

  • SQL Server dans une VM Azure
  • HD insight Preview
  • SQL Database Premium

SQL Server :

  • PDW (Parallel Data Warehouse)
  • SQL 2012 SP1
  • SQL 2014 CTP2 (annonce faite en live)

2. Nouveautés majeures SQL 2014 :

Amélioration des performances :

Moteur transactionnel (OLTP) « In memory »

Basé sur le column store index déjà présent sur les moteurs d’analyse.

  • 30 x plus performant que l’ancien moteur
  • Requête 100x plus rapide sur des jointures
  • 90% d’économie de stockage sur disque

Haute disponibilité et Recovery

  • « Always on » secondaire dans Azure
  • Backup dans Azure
  • Fichiers de bases dans Azure

Nouvel outil : Backup to Windows Azure Tools

Données critiques dans le cloud

2 niveaux de SLA :

  • SQL Database Web and Business
  • SQL Database Premium

Requêter tous types de données

  • Windows Azure HD Insight : simplifie les projets Haddop pour faire du Big Data
  • DW Virtual Machine dans Windows Azure : rend plus performant les DWH
  • PDW avec Polybase : combine données structurées et non structurées

La BI temps réel pour tout le monde

  • Power Query : Simplifie la recherche et l’accès à la donnée
  • Power Pivot, Power view et Power Map : Analyser et Visualiser avec des outils nouvelle génération
  • Power BI Q&A : Poser des questions aux données
  • Power BI sites : Partager et se connecter aux données depuis n’importe quelle « device »

Sébastien Madar

#sqlpass #summit2013 MCNEXT en route pour Charlotte

PassSummit2013Demain commence le PASS Summit 2013, l’évènement majeur sur SQL Server délivré et organisé par l’incontournable association PASS (Professional Association for SQL Server) avec l’aide de moult sponsors.

Cette année, l’évènement délaisse Seattle pour s’installer 3 jours durant au Convention Center de Charlotte en Caroline du Nord.

Charlotte

L’édition 2013 en chiffres c’est :

  • plus de 5000 visiteurs attendus
  • 52 nationalités
  • + de 150  speakers
  • + de 190 sessions

Pour ceux qui ne se sont pas inscrits, vous pourrez suivre les comptes rendus de 3 nos reporters sur le blog MCNEXT.

  • Sébastien Madar – Directeur du pôle décisionnel
  • Pascal Rouzé – Expert décisionnel
  • Ismail Ben Lamine – Expert décisionnel

ReportersBI

@+

Fred.

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

XML Noeud definition

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

image003

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 :

image007 image005

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.

image009

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

image011

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.

image015

image013

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

image017

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 :

image018

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

image020

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

image022

Sortie du composant Pivot

image024

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.