[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

[JSS2013] Session : SSAS Tabular : DAX

Speaker : Marco RUSSO
Niveau : 300-400

Objectif de la session :

Analyser les plans d’exécution DAX avec SQL Profiler pour améliorer les perfs

Introduction :

Le modèle tabulaire se base sur 2 moteurs : Formula Engine et Storage Engine (VertiPaq/xVelocity)

Les différents types de Plan d’exécution :

On a trois types de plan d’exécution : logique (flux logique de la requête), physique () et VertiPaq Queries (les requêtes exécutées par le moteur xVelocity).

Chaque code DAX va générer un plan d’exec différent.

Voici les évènements à tracer :

  • Error
  • Query begin (requête DAX)
  • Query end
  • DAX Query Plan
  • VertiPaq Storage Engine (SE) Query Begin
  • VertiPaq SE Query Cache
  • VertiPaq SE Query End

Analyser les traces

Pour avoir une meilleure lisibilité des traces, on peut utiliser le codeplex DAX Studio. C’est un outil avec un requêteur et un profiler intégrés. Le résultat des traces est récupérable sur Excel.

Les opérations VertiPaq

  • VertiScan => Filtre les données sur un ou plusieurs colonnes
  • VertiCalc => pour des calculs simples les calculs sont faits par le moteur, pour des calculs complexes le moteur fait appel au formula engine pour effectuer les calculs et effectuer des allers retours

Exemple :

  • Le SUM est exécuté dans le moteur xVelocity et pas dans le Formula Engine. Le SUM est donc très performant.
  • Le SUMX est exécuté dans le Formula Engine car il évalue les calculs ligne à ligne. Tant que les calculs sont simples, ils se font dans le storage engine. Dès qu’on voit apparaître le CallbackDataID, c’est que l’expression devient assez complexe et le moteur rebascule en formula engine pour chaque ligne.

N.B : N’oubliez pas de vider le cache lors de vos tests.

Quelques bonnes pratiques

  • Filtrez les données au plus tôt pour réduire le plan d’exécution
  • Utilisez les relations au lieu du CALCULATE
  • Privilégiez ADDCOLUMNS à la place du SUMMARIZE
  • Délaissez les fonctions de type If…

Conclusion :

Très bonne session de Marco Russo qui nous explique bien la mécanique interne du moteur VertiPaq.

David – Consultant décisionnel MCNEXT

[JSS 2013] Session : Gestion des données, Power BI

Speaker : Jean Pierre Riehl

Introduction :

Power BI est une solution de Business Intelligence composée de plusieurs éléments.

3 grands usages de Power BI :

  1. Information worker – Excel
  2. Data Steward – Excel, PowerBI site
  3. IT admin – Data management Gateway, IT admin Portal

Power Query :

C’est un Add-in Excel gratuit.
C’est l’outil de BI Self-Service qui peut charger des données qui ne sont pas dans le format attendu depuis toutes les sources de données et qui peut connecter les informations.

Jean-Pierre Riehl nous montre 3 démos

Démo 1 : Façonner des données

On peut créer une requête dans Power Query, traiter les données et nettoyer les données pour arriver le jeu de données qui peut répondre aux questions.

Il y a trois fonctionnalités utiles dans Power Query

Json.Document : il nous permet de lire, transformer et exploiter ce format assez nativement.

Merge : L’opération Merge crée une nouvelle requête de multi-sources de données.

Un pivot : Il crée une colonne d’attributs pour la colonne sélectionnée et une colonne de la valeur pour chaque valeur de la cellule sélectionnée.

On peut aussi créer notre propre fonction avec le langage M.

Démo 2 : Partager et Rechercher

L’objectif : Collaborer avec l’équipe et avec l’ensemble de service et partager des travaux.

Share Query : qui permet de partager la requête sur Office 365 et mettre la disposition à l’autre.

(Partager uniquement les métadonnées pas EXCEL!)

Online Search : le moteur nous permet de rechercher partout avec le mot clé

  1. Chercher les données public (Wikipédia)
  2. Chercher les données dans mon organisation.

Démo 3 : Data Management Gateway

Il se charge de communiquer et mettre le lien entre On-Premise et Office 365 – Cloud

Il permet de mettre à jour les données sur Cloud et d’exposer en flux Odata les informations d’une base de données qui se trouvent sur site.

Conclusion :

Quand User est dans EXCEL, il peut communiquer directement avec Office 365. Ça sert à rechercher et partager les informations.

Data Management Gateway, il s’agit comme proxy et il permet de Data Refresh indexation des métadonnées.

Yuanfan – Consultant décisionnel MCNEXT

[JSS 2013] Session : L’agilité expliquée aux pros de la données

Speaker : Michel Perfetti
Level : 200

Agilité ?

2001 : création du Manifeste Agile

Les 4 principes fondamentaux de l’agilité :

  1. Les individus et leurs interventions plus que les processus et les outils.
    C’est-à-dire que l’on favorise la communication directe.
  2. Des logiciels opérationnels plus qu’une documentation exhaustive.
    Logiciels fonctionnent et la documentation n’est pas une fin.
  3. La collaboration avec les clients plus que la négociation contractuelle.
    Collaboration étroite entre le client et l’équipe de développement
  4. L’adaptation au changement plus que le suivi d’un plan de règles précises.
    Il  y a des règles précises pour le changement.

Des méthodes de gestion de projet ET des techniques de développement.

Scrum :

Découpage du temps en itération courtes.

Focaliser sur l’organisation et la gestion de projet et pas sur la réalisation.

Un sprint est une itération, d’une durée allant de 2 à 3 semaines.

Qui définit 3 rôles : Product Owner (établir la fonctionnalité), Team de développeurs, Scrum Master (Garantir le processus Scrum qui est respecté)

Kanban

L’approche Kanban consiste globalement à visualiser le Workflow.

La méthodologie Kanban est donc utilisée dans des services de support au client

Identifier des goulots d’étranglement

Extrem programming :

Focaliser sur la réalisation et ensuite sur la gestion de projet.

4 solutions pour résoudre des points de frictions de l’agilité :

L’idée d’agilité c’est faire le cycle court qui dit moins de problèmes et  qui dit aussi plusieurs moment on peut s’améliorer.

  1. L’intégration continue : Tracer les changements
  2. La collaboration accrue : Mélanger les équipes et  faire sauter les barrières
  3. Appliquer les pratiques du Développement classique à la base de données :
    Versionner les modelés de base de données
  4. L’automatisation (Feedback rapide): Automatise des tests sur les applications d’avoir le jeu de données et automatise les déploiements

On note les points suivants pendant la démonstration (SCRUM en équipe BI) pour viser des problèmes et s’améliorer.

  1. Planification de sprint
  2. Tracer le flux de travaille
  3. Team Communication
  4. Traitement des problèmes

Conclusion :

L’Agilité est donc une méthodologique importante pour la gestion de projet

Yuanfan – Consultant décisionnel MCNEXT

[JSS 2013] Session : SSAS ROLAP sur Column Store Index (CSI)

Avec les possibilités offertes par SQL Server 2012, il est peut-être temps de repenser la place des bases SSAS dans les systèmes d’informations. Nous vous proposons de détailler les modes de conception et les performances de deux architectures : le ROLAP sur du XVelocity SQL Server d’un côté et du Tabular de l’autre.

 Level : 400
Speaker(s) : Patrice Harel, Charles-Henri Sauget

Le principal problème du ROLAP est un problème de performance car ce mode attaque directement la base relationnelle.

L’idée est d’utilise ici le CSI permettant d’améliorer grandement les performances des requêtes sur une base relationnelle.

– Pour le test : a été récupérée la base Wikipedia. La plus grosse table de faits contient 250 millions de lignes. La base fait 120 Go.

– Le CSI : la donnée est stockée en colonne et pas en ligne. Elle est compressée. Chaque requête ramène moins de données : on ne sélectionne qu’une partie de la table (le reste des colonnes n’est pas ramené).

Les tables sont séparées en partitions, les partitions en segments. Chaque segment étant rempli par un thread, les partitions peuvent être remplies en multithreading : gain de performance.

3 ajouts sur la version 2014 de SQL Server :dictionnaire secondaire et le delta store. Le plus important pour nous : insertion, suppression et mise à jour sur les tables avec le CSI !

Best practice : si plusieurs champs sont présents dans le CSI, il faut toujours partir du champ qui a le moins de données vers celui qui en a le plus.

Les avantages du CSI :

  • Cluster CSI en lecture/écriture dorénavant
  • BLOB
  • Segment Elimination
  • Batch Mode
  • Compression Archive.

Le CSI permet un gain de place énorme sur le disque : facteur 17 sur la base de démo des speakers.

Les intérêts du ROLAP :

  • Pas ou peu de temps de process
  • Temps réel
  • Partitionnement côté SQL

Les optimisations principales à apporter sur le ROLAP :

  • Eviter le Spill (quand il n’y a pas assez de mémoire sur le poste, la requête s’exécute en tempDB.
  • Création de statistiques
  • Dans le plan d’exécution, on doit être en batch et pas en row sur le CSI. L’option OPTION HASH JOIN sur une requête permet de supprimer les Nested Loop.
  • Optimiser les many to many

Les speakers ont ensuite présenté un tableau comparatif entre ROLAP, MOLAP et Tabular.

Dans 2 tests sur 3, le ROLAP s’est montré le plus rapide. Le tabular étant dernier, ce qui peut s’expliquer par la quantité de données chargées en mémoire provoquant des accès disque.

Pour être honnêtes, les 2 speakers ont aussi annoncé que le MOLAP  n’était pas optimisé.

Les limites du CSI :

  • Des types non supportés (varchar(max), XML,…)
  • 2 types de CSI : Clustered et non clustered. Le clustered doit être unique dans la table.

Conclusion :

2 super speakers qui maitrisent leur sujet sur le bout des doigts.

Le ROLAP reprend ici toutes ses lettres de noblesse grâce au CSI.

Il était tout simplement quasi inutilisable en l’état à cause de ses lenteurs. D’ailleurs la dernière fois que je l’avais vu « en vrai », c’était en 2009.

Couplé à la rapidité du CSI, la solution redevient viable en termes de performance.

Frédéric – Consultant décisionnel MCNEXT

[JSS 2013] Session : SSAS : Test de montée en charge avec Visual Studio

Speaker : Arnaud Voisin
Level : 300-400

Cette session a pour but de nous montrer comment réaliser des tests de montée en charge sur Visual Studio.

Pourquoi en réaliser ?
Pour valider les exigences de performance de la solution, que ce soit l’architecture, le design, l’usage de l’application ou encore la qualité du code.

La session a été découpée en 4 parties :

  1. Déploiement de projets SSAS
  2. Stress tests
  3. Méthodologie des tests de performance
  4. Architecture de test

1/ Déploiement de projets SSAS

Dans cette partie, on va voir qu’il faut être vigilant sur certains points avant le déploiement d’un projet SSAS :

  • Meilleure optimisation SSAS sur les schémas en étoile
  • Parent child : il faut limiter les agrégations et ne les faire que sur les attributs clés, dans le meilleur des cas
  • Distinct count : créer une clé de partitionnement
  • Stockage : ne pas avoir des partitions trop petites (< 200Mo) ou trop grosses (> 3Go)

2/ Stress tests

Voici une liste de pré-requis avant de pouvoir réaliser les stress tests. Il faut avoir :

  • ASQueryPlan (outil de génération de Workload)
  • ASLoadSim (injecteur de charge)
  • Visual Studio Ultimate (Team System)
  • SQL Server
  • Solution SSAS OLAP / Tabular

Il faut aussi choisir son Workload (un Report RS, Excel, Power Pivot, …) et le variabiliser avec ASQueryPlan.

>> DEMO : Workload + Variabilisation

  • Il faut commencer par ouvrir SQL Profiler pour superviser les traitements qui seront lancés. Ensuite, à l’aide d’un refresh sur un fichier Excel, on regarde ce qui remonte dans SQL Profiler
  • Avec l’aide de ASLoadSim, on va pouvoir récupérer les requêtes qui ont été variabilisées dans l’ASQueryPlan >> C’est du code C#

3/ Méthodologie des tests de performance

On commence par définir nos objectifs/cas de test et la longueur des stress tests dont la formule est :

(Nb requêtes/fichiers) * nb fichiers * (tps de réponse estimé + thinktime (??)) Il faut ensuite choisir le modèle de charge voulu (par étape, en fonction des objectifs ou constant). On peut mixer différents tests. Enfin, on choisit les compteurs à monitorer (CPU, I/O, mémoire, requêtes).

>> DEMO : Run d’un load test

Pour cette démo, il nous a montré son code et l’a exécuté. Vous pouvez voir un résultat ci-dessous :

4/ Architecture de test

  • ASLoadSim est monobase c’est-à-dire qu’il ne peut être associé qu’à un cube. Il est possible de le lancer sur plusieurs cubes mais il faudra le dupliquer autant de fois qu’il y a de cube.
  • Architecture controleur-agent : il est conseillé d’avoir un CPU utilisé inférieur à 70% et une RAM supérieure à 10%.
  • Les tests de montée en charge peuvent être dispo sur le cloud via Visual Studio Online. Il faut néanmoins mettre à disposition les fichiers de Workload, le cube et les fichiers de conf associés.

Conclusion :

Les tests de montée en charge se font grâce à Visual Studio. On utilise le provider ADOMDNet pour les cubes MultiDim et Tabular (MDX, DMX et DAX), et d’autres providers pour du SAP, Oracle.
Session assez technique.

David – 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