Le Post Infeeny

Les articles des consultants et experts Infeeny

Archives de Catégorie: [Evt] Pass Summit 2012

Clôture du Pass Summit 2012

Bonjour à tous,

Les sessions BI se clôturent à l’instant.

Merci de nous avoir supporté cette semaine 🙂 , nous passons le relais aux SharePointers.

Sébastien, Fred et Julien

[DAX] Mon nom c’est DAX !

 DAX : Data Analysis eXpression

Montre des notions très basiques du DAX.

Les formules DAX sont utilisées dans PowerPivot et SSAS Tabular pour créer des colonnes calculées et des mesures calculées.

Colonne calculées : calcul au niveau le plus fin.

Mesures calculées : calcul au niveau agrégé.

Destiné aux Power Business Users.

Intégrés dans Excel (Mesures calculées) et PowerPivot (Colonnes calculées et Mesures calculées).

Les différents calculs selon les contextes :

« Row Context » = calcul ligne à ligne (pour chaque « current row ») et colonne par colonne.

« Filter Context » = calcul selon le contexte utilisateurs c’est-à-dire les filtres qu’il a effectué.

Il y a 135 fonctions Excel DAX (64 unique aux DAX, les autres propres à Excel.

On en voit que quelque unes :

  • Fonction de calcul contextuel

= CALCULATE(Expression, Filter..) effectue des calculs d’expressions DAX selon le « Filter Context » de l’utilisateur ou de l’argument Filter de la fonction CALCULATE.

  • Fonctions d’agrégation

=SUM() : Fonction Excel qui s’appuie sur une colonne de la table courante pour effectuer une somme.

  • =SUMX() : Fonction DAX qui s’appuie sur une colonne d’une autre table pour effectuer une somme.

….

  • Fonctions DAX Date/Time

=DATE (..)

=PARALLELPERIOD(..)

=DATEADD(..)

=DAY (..)

=FORMAT(..)

…..

  • Fonction de référence d’une colonne d’une autre table

=RELATED()

Si on n’utilise pas cette fonction en renseignant directement les tables, il ne fait pas le lien pour chaque « current row ».

Ensuite il y a eu que des démonstrations.

Sébastien

[SIS] Slowly Changing Dimensions

Speaker : Mark Stacey – Niveau : 300

Une session pour faire un tour complet du SCD avec son implémentation dans SSIS et ce que ça implique dans SSAS.

On commence par un classique, les types de dimension :

–          Type 0 : Attributs fixes

–          Type 1 : Pas d’historique

–          Type 2 : On insère de nouvelles lignes pour gérer l’historique

–          Type 3 : On gère l’historique avec des colonnes différentes dans la table

–          Type 4 : On a une table d’historique séparée

On va s’intéresser aux dimensions de type 2

On commence par insister sur la notion de « Surrogate Keys » très importante pour gérer notre historique (à distinguer de la clé technique).

Puis, on poursuit par les démonstrations :

–          Présentation d’une dimension « Produit » de type 2 avec les dates de début, de fin, une colonne « Statut », etc.

–          Différentes techniques d’alimentation de cette dimension dans un package SSIS

–          Alimentation d’une table de faits contenant les ventes avec un « Lookup » permettant de gérer le type 2 (« INNER JOIN » avec la dimension « Temps »).

–          Optimisation de cette dimension dans un « UDM » (On peut par exemple préciser sur les attributs « dates de début » et « date de fin » de la dimension comme les dates du « SCD ».

–          Implémentation dans un modèle tabulaire et création d’un « RunningValue » en DAX (?)

Rien de bien nouveau ici pour les personnes ayant déjà eu à gérer des dimensions de type 2 dans leur projet.

Julien

[DAX] Inside DAX Query Plans

Speaker : Marco Russo – Durée : 75 mn – Niveau : 400

Objectif de la session

Plonger dans le SQL Profiler pour analyser les plans d’exécution DAX.

Introduction

Pour attaquer un modèle tabulaire on peut utiliser du MDX, DAX pour attaquer le moteur de stockage VertiPaq

Le moteur VertiPaq exécute une requête en parallèle, un cœur par segment

Le formula engine est optimisé pour des exécutions sur des données compressées.

Dax Query Plan Types

On a deux types de plan d’exec : 1 logique (le flux logique de la requête), 1 physique (celui qu’on va analyser pour optimiser les perfs)

Les évènements à tracer sont les suivants :

  • Errors,
  • Query begin,
  • Query end,
  • Dax Query Plan,
  • VertiPaq SE Query Begin,
  • VertiPaq SE Query Cache Match,
  • Vertipaq SE Query End

N.B : quand 2 requêtes sont lancées en parallèle, 1 requête peut utiliser le résultat de l’autre dans le cache, ce qui augmente les perfs.

Comprendre les traces

Les traces sous SQL Server ne sont pas évidentes à lire. La solution est de récupérer DAX Studio disponible sur codeplex.

Outil bien complet avec un requêteur, un profiler permettant de récupérer les traces le tout pouvant être manipulé dans Excel avec le plugin disponible aussi sur codeplex.

Les traces dans DAX Studio permettent de mettre en évidence les points sur lesquels nous devons faire attention (ils apparaissent en rouge)

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 et que le logical plan ne fait pas de CallbackDataID les calculs se font dans le storage engine. Dès qu’on voit apparaître le CallbackDataID  c’est que le moteur appelle le formula engine pour chaque ligne. Lorsque vous manipuler plusieurs millions de lignes cela peut plomber les performances.

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

Notez qu’actuellement, on ne voit pas dans le plan d’exécution les plans d’exécutions internes utilisées sur des calculs complexes. Faites attention au CallbackDataID

Quelques astuces

  • Sur les traces dès que vous avez du CallbackDataID sur le DAX Query Plan c’est que le Formula Engine est sollicité. Cela signifie que vous pouvez améliorer la requête DAX.
  • Si le DAX query plan est trop compliqué à comprendre vous pouvez déjà identifier certains problèmes en étudiant les VertiPaq SE Query
  • Filtrez les données au plus tôt afin d’économiser les opérations effectuées et de réduire le plan d’exécution
  • Utilisez les relations car elles peuvent être poussées jusqu’au moteur VertiPaq (surtout si vous n’avez pas de relation au niveau du modèle et si vous pouvez raccourcir le chemin, car le moteur n’utilise par défaut que les relations déclarées sur le modèle)

Conclusion

Un super boulot de Marco Russo qui arrive à vulgariser et expliquer la mécanique interne du moteur VertiPaq.

Frédéric

[DQS] Concepts avancés DQS

Speakers : Rakesh Parida & Mathew Roche – Niveau : 300

Description : Résolution de problèmes complexes de qualité de données avec DQS (Data Quality Services)

  • Qu’est-ce que DQS ?

Service SQL Server 2012 qui permet de traiter de la qualité de données : mettre de la cohérence, de la complétude, exactitude, conformité, lisibilité dans les données.

Comprend un ensemble de processus, une base de connaissance et l’accès à des web services tiers de traitement de données spécialisés.

  • Terminologie dans DQS

KB : Base de connaissance

Domain : Champs avec des règles de gestions, des listes de valeurs sémantiques connues et des tables de correspondances

Composite Domain : Collection de domaines

Cleansing : Nettoyage de données

Matching : Mise en association de données

  • Composite « Domain »

Exemple une adresse est un « Composite Domain » : une adresse  avec les domaines Rue, Ville, CP

Quand pas assez d’adresse dans la KB, on fait du Reference Data pour utiliser un service tiers de Check Address (Melissa)

Sinon on peut utiliser la base de connaissance.

Les champs de la source à traiter seront comparés au champs du domaine composite, et si la KB est bien entrainée les corrections des données deviennent pertinentes.

  • Enrichissement  de la KB (« Knowledge Base »)

Translation de valeur ex MS Corp. en Microsoft Corp.

Correction de la casse

Correction des fautes d’orthographe

TBR : Term Based Relationships = tables de correspondances pour gérer les synonymes, erreurs et les valeurs invalides

Inclus les similarités sémantiques : Big Apple= New York

  • Au-delà du « Cleasing » simple
  • Résolution de « Matching » complexe

Sébastien

[TAB] Optimizing Your Semantic Model For Performance and Scale

Speaker : Akshai Mirchandani & Allan Folting – Niveau : 400

Il s’agit de comprendre et d’optimiser nos modèles tabulaires.

On commence avec le schéma classique de BISM incluant les moteurs OLAP et « xVelocity ».

Principe du moteur « xVelocity »

–          Performance, performance et toujours performance

–          Performance pour l’interrogation mais également pour le chargement des données

–          Accommoder les changements sans avoir à recharger toutes les données à chaque fois

Architecture du moteur

–          Stockage basé sur les colonnes

–          Structure séparée des colonnes

–          Compression : optimiser pour les requêtes d’analyse

Les données sont stockées dans des segments et des dictionnaires pour chaque colonne. Les colonnes calculées et les hiérarchies et les relations sont également stockées en colonne.

Première démonstration du stockage en colonne. On voit que pour chaque colonne on a un fichier.

Description des phases de chargement (« Process »)

–          Lecture et encodage des données dans un segment N

–          Compression du segment N, lecture et chargement du segment N+1

–          A la fin du chargement des données, construction du reste (colonnes calculées, etc.)

–          Un segment peut être lu et encodé puis découpé en 2 pour la compression

On poursuit avec le principe d’encodage des données qui transforme les données en identifiant afin d’être compressées par la suite. Il existe 2 mécanismes d’encodage (« Hash et Value Encoding ») qui dépend du nombre de valeurs différentes.

Deuxième démonstration montrant pour chaque colonne d’une table le type d’encodage en fonction de la distribution des données. Pour effectuer son choix, le moteur prend un jeu de valeurs de la colonne et y applique un algorithme.

Il existe des DMVs permettant d’analyser nos modèles tabulaires (le nombre de tables, le nombre lignes pour chaque table, l’utilisation de la mémoire pour chaque table, le nombre de segments, etc.). On peut même voir le type d’encodage (« Hash » ou « Value »)

On peut également contrôler la taille des segments (les gros segments ont une meilleure compression par exemple).

On peut analyser toutes les séquences de chargement dans le « SQL Server Profiler »

Il existe plusieurs types de chargement (comme avec un cube « OLAP »)

–          « Process Data »

–          « Process Recalc »

–          « Process Full » (« Data » + « Recalc »)

–          « Process Default »

–          « Process Clear »

–          « Process Add »

–          « Process Defrag »

On termine par un certain nombre d’astuces pour le chargement des tables.

En conclusion, cette session arrive assez bien à vulgariser l’architecture « xVelocity »

Julien

[SQL] – Real-Time Datawarehouse and Reporting Solutions

Speaker: Carlos Bossy – Durée : 75mn – Niveau : 200

Objectif de la session

  • Ne pas impacter les systèmes sources
  • Processer uniquement le données fraiches pas plus
  • Ce que SQL Server peut faire pour vous

Ne pas impacter les systèmes sources

Pour être le plus réactif et capter le changement de vos données sources utilisez :

  • Replication
  • CDC (Change Data Capture)
  • SSIS

Il existe d’autres solutions (Data Vault, Triggers, Caching, Procactive Caching, ROLAP ….) que nous ne verrons pas dans cette session.

Description du Pattern (Replication/CDC/ETL)

Utilisation de la Réplication et du CDC

  • Répliquez les données sources dans un ODS   => utilisez la réplication
  • Capter le changement des données sur votre ODS => activez le Change Data Capture
  • Alimentez l’entrepôt, via SSIS,  à partir de l’ODS et faire de l’incrémental sur les données fraichement modifiées en utilisant le CDC qui a capté les modifications effectuées
  • Traitez uniquement les partitions et les dimensions du cube pour lesquels des données ont été modifiées

Réplication

Les étapes de la réplication transactionnelle :

  • select des objets à répliquer,
  • Création du snpashot (on a la même table dans la base de réplication que la base source);
  • Continuité (afin de s’assurer que dans la base de réplication on a toujours les mêmes données qu’à la source)
  • Background process

La  réplication en quelques mots :

  • facile à maintenir,
  • très peu de latence,
  • pas de filtre,
  • changer la Souscrpitpeur à partir du Publisher,
  • Faites bien attention lorsque que vous voulez modifier le Subscriber

Faites attention sur des proc stocks qui mettre à jour plusieurs millions de lignes. Il est préférable d’exécuter la proc stock sur les deux bases (source et répliquée) en stoppant la réplication car une mise à jour massive de données source peut cascher la réplication.

Change Data Capture

Le CDC en quelques mots :

  • Capte toutes les opérations d’insertion, mise  à jour, suppression
  • Possède  son propre schéma
  • Fournit des fonctions SQL pour manipuler les données modifiées.

Intégration de Données

Les enjeux de l’intégration de données dans un Real-Time environnent

  • Exécutions en continue
  • Traiter les modifications
  • Combiner cd avec données statiques
  • Construire des check de validation de données

Démo

La démo est faite sur AdventureWorks2012

Mise en place de la réplications sous SSMS :

  • Création de publication : Replication -> Local Publication  (on déroule le wizard, le type de publication est Transcational et on sélectionne les objets qu’on veut répliquer, création du snapshot,)
  • Création de souscription  : Replication -> Local Subscription (on déroule le wizard, création de la base de réplication (ODS), la panifaction est paramétrée en Run continously pour avoir du temps réel)

Mise en place du CDC sur la base de replication:

  • sp_cdc_enable_table : on spécifie la  table pour laquelle on veut capter le changement
  • On créé une table fonction permettant de récupérer les données modifiées sur la table « espionnée »

On crée la base DWH

  • Création des dim/fact tables
  • Création des tables de staging (qui seront alimentées en truncate/insert)
  • Mise en place d’une proc stock avec un MERGE STATEMENT permettant d’alimenter la table de staging (oops une proc stock)

Création et excution d’un package SSIS permettant :

  • d’alimenter les tables de stating (via la proc stock)
  • De cleaner les tables de CDC
  • Puis de charger les tables de l’entrepôt
  • Le tout avec une boucle infinie

Création d’un report SSRS sur nos données (directement sur l’entrepôt) qui est rafraîchit toutes les 5 min.

Création d’un script TSQL permettant de créer des transactions dans notre base sources et on l’exécute.

Et wouahhh çà marche. Les données se rafraichissent  (la latence est de 1 à 2 sec)

Conclusion

Description très intéressante d’une architecture permettant de répondre à des problématique des DWH temps (ou quasi) réel.

Viable d’après le speaker sur les systèmes sources transactionnel de plusieurs milliers de transactions à la sec.

Frédéric

[SQL] Geospatial Data Types in SQL Server 2012 : Location Awareness in the Database

Speaker : Leonard Lobel – Niveau 300

Dans cette session on parle des données spatiales et leurs utilisations dans « SQL Server 2012 ».

Ce type de données est supporté depuis « SQL Server 2008 »

On trouve 2 types de modèles :

–          Geometry : Planaire (coordonnées cartésiennes projetées sur une carte de la terre à plat)

–          Geography : Géodésique (latitude et longitude)

Il existe un standard pour les données spatiales mis en place par l’OGC (Open Geospatial Consortium). Microsoft appartient au consortium.

Le format WKT permet de représenter sous format texte des objets géométriques. On peut trouver différents objets sous « SQL Server » : POINT, POLYGON, LINE, etc.

« SQL Server » propose de nombreuses méthodes pour utiliser les données spatiales.

Première démonstration d’affichage de polygones dans « Management Studio » sous format texte et sous format spatial. On voit également l’utilisation de certaines méthodes  (« STEntroid », « STEnvelope », « STIntersect », etc.)

Deuxième démonstration avec la conversion de données géographies au format WKT en type « Geometry » avec plusieurs méthodes (STGeomFromText, etc.), l’union, l’intersection, … d’objets spatials.

Troisième démonstration montrant la différence de résultat lorsque l’on utilise les types « Geography » et « Geometry » à partir d’un objet au format  WKT.

Quatrième démonstration plus compliquée. On crée une table avec 3 polygones dans une ville (un parc, un mur et un parcours sportif). On crée une autre table contenant les photos des polygones retournées précédemment. On assemble les 2 pour jouer avec. On peut par exemple calculer la distance en KM entre 2 photos, la plus grande région.

Cinquième démonstration avec une application C# utilisant des données géo spatiales et les affichant dans un fond de carte « BING » avec possibilité de zoomer, etc.

On termine avec les différentes améliorations « SQL Server 2012 »

–          Nouveaux objets : CIRCULARSTRING, etc.

–          Indicateur de référence spatiale

En conclusion, une session très technique permettant de comprendre le fonctionnement des données spatiales sous « SQL Server ».

Julien

[BIG] BI & Big Data

Speaker : Andrew J.Brust – Niveau : 300

Description: Qu’est-ce que le Big Data et comment faire de la Business Intelligence sur du « Big Data »

Qu’est-ce que Big Data ?

  • Volumétrie : 100s ou plus de TeraBits (TB)
  • Données : Financières, web logs, réseaux sociaux.., type non structurées
  • Technologie : Parallel processing, PDW, Hadoop, NoSQL ….

(Trop de volume pour que les « Big Data » soient stockées dans des bases OLTP, on analyse des interactions plutôt qu’effectuer des transactions)

  • 3V : Volume, Velocité, Variété

Qu’est-ce que MapReduce ?

  • Map et Reduce sont 2 fonctions Java
  • Chaine : Files Input–> Mapper (démultiplexeur de données/fichiers dans plusieurs noeuds)–>Output–>Input–> Reducer (agrégateur de ligne)

(Mapper s’exécute en parallèle)

Qu’est que DFS (Distributed File System) ?

Réplication de données, pas de partage

Utilise des nœuds dans un cluster

–> Hadoop ? = MapReduce +HDFS (Hadoop Distributed File System)

Qu’est-ce que le NoSQL ?

  • Bases non relationnelles
  • Langage objet de requetâge non SQL (Java, Python, C#..)

4 types :

  • Key-Value Store
  • Document store
  • Wide Column
  • Graph Databases

Qu’est-ce que Hbase ?

C’est une implémentation des bases de type Wide Column store

Composant de la Stack  Hadoop :

  • Hadoop (MapReduce, HDFS)
  • Hbase : Base de données non relationnel
  • Hive : Interpréteur du langage SQL : SQL-Like « DWH » system
  • Langage de transformation de données
  • Sqood (SQL 2 for Hadoop)
  • Flume : Log integration
  • Mahout : Data Mining

La solution Big Data Microsoft :

  • Microsoft HDInsight est un projet développé en collaboration avec Hortonworks pour inclure leur HDP (Hortonworks Data Platform)
  • Utilise le moteur PolyBase
  • Pour Azure et Windows Server
  • Inclus des Drivers ODBC pour Hive (Un Add-in Excel l’utilise)
  • JavaScript MapReduce Framework

Avant MS, Hadoop est utilisé par IBM, MapR, Cloudera…

Framework de développement dans VS (Visual Studio) /.NET qui intègre tous ces composants et fonctionnalités :

  • MaperReduce code pour C#
  • MRLib
  • Linq to Hive
  • Odbc client et Hive ODBC Driver
  • Déploiement
  • Débogage

Le driver ODBC Hive compatible avec :

  • Excel et PowerPivot
  • SSAS Tabular  mais pas SSAS Multi dimensionnel pour le moment
  • SQL Server DB Engine

Sébastien

[TAB] – Power (View)Ful Tabular BI Semantic Model Development

Speaker :  Mark Davis – Durée : 75 mn – Niveau : 300

Objectif de la session

Découvrir les propriétés du modèle tabular sur lesquelles ils faut être attentif afin de pouvoir profiter compléter de Power View.

Power View

Introduction

Outil de reporting ad-hoc permettant aux utilisateurs d’explorer, visualiser et présenter des données.

Simple d’utilisation, il s’adresse aux utilisateurs finaux qui peuvent réaliser des rapports presentation-ready en quelques clics.

Démo

On commence par une première démo sur un dahsboard de perfomance d’un groupe d’hôtel.

Comme d’habitude, on retourve du bubble char, des bargraph, des slicers, des tiles ….

On voit l’importance des propriétés mises en place sur le modèle tabular afin d’améliorer l’experience de navigation de l’utilisateur

  • Les libellés et images par défaut sont représentés par une card
  • Les données agrégeables sont représentées par un sigma
  • Les mesures calculées sont représentées par une calculatrice
  • Les noms des champs doivent avoir un nom métier

Considérations de modélisation « tabulaire »

Power View ne s’appui uniquement (actuellement) que sur des modèle tabular

Avantage SSAS Tabular Vs PowerPivot

  • Partionnement de table
  • Securité
  • Facilité d’évolutivité
  • DirectQuery pour accéder aux données de l’entrepôt en temps réel, permet d’accéder à des données retournées par du MDX par l’intermédiaire de proc stock qui pourrait stocker le résultat d’une requête MDX dans une table, etc…. Bref, avec du DirectQuery vous pouvez mettre en place plein de workaround.

Le modèle doit être conçu de façon à être le plus pour l’utilisateur et lui faciliter la visualisation des données pour l’utilisateur final :

  • Attention si vous travailler sur un serveur accessible à vos utilisateurs, vous devez faire attention dans SSDT de ne pas exposer vos modèles en cours de développements
  • Renommez les tables et les colonnes pour qu’elles aient un nom métier
  • Veillez à bien mettre en place les relations entre les table
  • Déclarez une dimension Temps afin d’ajouter des fonctionnalités Time Intelligence
  • Créez des mesures calculées
  • Masquer les colonnes, tables, mesures calculées dont l’utilisateur n’a pas besoin (ID, code, table techniques, mesures calculées intermédiaires) …
  • Triez les données (possibilité de trier les données d’une colonne à partir d’une autre colonne (ex : tri des libellés de jour en fonction du numéro du jour de la semaine)
  • Créez des hiérarchies
  • Créez des KPI
  • Formatez correctement vos colonnes
  • Ajoutez des images
  • Paramétrez la description des tables
  • Paramétrez les identifiant et les colonnes par défaut
  • Mettez en place la sécurité
  • Utilisez la puissance du DAX pour créer des mesures semi-additives, consolider/filtrer les données, faire du ranking/pourcentage, définir des hiérarchies parent/enfants

Conclusion

Si vous n’avez jamais pratiqué de PowerPivot ou AS Tabular + Power View, lisez ceci, sinon passer votre chemin.

A noter que la session a le mérite de fournir une check list à suivre lors de toute modélisation de modèle tabular.

Frédéric