[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