70-466 – Implementing Data Models and Reports with Microsoft SQL Server 2012 – Novembre 2013

La préparation

J’ai passé les certifications liées au MCSA en me basant sur les excellent training kit, mon expérience et divers sites pouvant répondre à mes questions. Mais lorsque je me suis décidé à passer mon MCSE Business Intelligence, j’ai été surpris de ne pas trouver de training-kit officiel sur ce sujet.
En parcourant le programme de cet examen, trois parties se distinguent: l’administration multidimensionnel ( SSAS et SSRS), le tabulaire.
Pour le multidimensionnel, je me suis basé sur les le training kit 70-448. Concernant le tabulaire, j’ai regardé et pratiqué beaucoup de tutoriels ne pratiquant pas en mission ce nouveau mode.

L’examen

Ayant une forte expérience sur la partie multi-dimensionnel, j’abordais cet examen confiant. Lorsque l’on a un doute, il faut souvent se dire, quelle serait la réponse dans l’esprit Microsoft. En découvrant les 50 questions, l’examen est très complet avec comme d’habitude les questions à choix multiples sur 4 sujets différent, les drag& drop à mettre dans le bon sens.
L’examen s’étant bien déroulé et enchainant le 70-467, ma joie de réussite fut courte.

[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

[JSS2013] Session : SSAS Tabular : Modélisation

Speaker : Aurélien Koppel et Benoit Girard
Level: 300

Petit rappel :

SSAS 2012 C’est 2 moteurs :

  • Moteur Multi dimensionnel => MOLAP et ROLAP
  • Moteur Tabular => X Velocity et Direct Query

Dans cette session nous allons analyser le moteur XVelocity

But de ce nouveau moteur :

  • Moteur plus performant
  • Prioriser les perf. Requetage : Perf requetage > Perf Process
  • Many to Many plus performant
  • Modèle plus Agile – ex. ajout nouvel attribut => Multi Dim : il faut tout reprocess!
    Tabular : reprocess de la table de l’attribut uniquement
  • Minimum de config – ex. pas d’agrégation

STOCKAGE

Mode stockage en colonne, en mémoire et compressé.

Compression en moyenne X10 vs. BI Traditionnel principalement grâce à l’encoding qui peut se faire de 2 façons :

  • Value encoding – pour valeur arithmétique dense
  • Hash encoding – pour les strings

Que stock le moteur en mémoire ?

  • Dictionnaire de donnée – ex. pour l’encoding
  • Colonne par segment – segment de 8M de lignes
  • Colonne calculée – mais non compressé !
  • Hiérarchie
  • Liaison entre les tables

Les DMV

Ils existent plusieurs DMV qui permettent entre autre de voir le type d’encoding utilisé et si celui-ci a déjà été modifié :

  • $SYSTEM.Discover_Storage_table
  • $SYSTEM.Discover_Storage_table_column
  • $SYSTEM.Discover_Storage_table_column_segment

INFRA

Le plus important :

  1. RAM – Stockage des données
  2. Processeur – Compréssion / decompression
  3. Disque – Seulement au process et restart

Pour la RAM besoin de 2 fois la taille du modèle car lors du process l’instance crée un 2eme modèle en parallèle et remplace le 1er à la fin du process (comme en multidim.)

MODELISATION

Supporte le modèle étoile et flocon mais mieux vaux utiliser le modèle en étoile

  • Meilleur Lisibilité
  • Profiter de l’auto-Exist
  • Meilleur perf sur les Cross Join complexe
  • Hiérarchie plus simple (dans la même table donc pas besoin de calcul complexe en DAX)

*Astuce dans Excel – on peut afficher les attributs des ‘dimensions’ vide.  Clique droit => Pivot table Options => Displays => Show Item with no data on rows

Hierarchie parent – enfant

Pas d’UI comme en Multidim mais possible de le faire dans les mesures en DAX

2 cas possibles :

  • Fonction PATH => recrée toute l’arborescence de la hiérarchie
  • Fonction PATHITEM => récupère 1 niveau de la hiérarchie au choix

Many to Many

Pas d’UI comme en Multidim mais possible de le faire dans les mesures en DAX, c’est performant par contre la logique est à implémenter pour chaque mesure donc contraignant.

PROCESSING STEPS

  1. Connections + exécution de la requête => Minimiser les connections, 1 connection pour plusieurs table si même source (attention à l’utilisation du wizard donc !)
  2. Encodage => utiliser les DMV pour voir le type d’encoding
  3. Compression => lit le 1er segment, puis lit le 2eme et compresse le 1er en parallèle, puis lit le 3eme et compresse le 2eme en parallèle, …
    Défaut setting de 10 s. pour compression de 1M de lignes
  4. Recalc => Recalcul les colonnes calculées, attention les colonnes calculées sont stockées en mémoire mais non compressé.

STRATEGIE DE PROCESSING

Process Défaut : seulement la 1ere fois pour que le modèle soit disponible

Process Full : Process Data + Process Recalc

Process Data : requête base de données, encoding et compression

Process Recalc : toute la base est recalculée! + Attention aux colonnes calculées, recalcule par ligne donc beaucoup plus lent

Process Add : ajout des nouvelles lignes. A la fin du process Add il y a un process Recal automatiquement

OUTILS TIERS

Ces outils gratuits permettent l’ajout de fonctionnalités non présentes dans SSDT ou Excel par défaut

BIHELPER

Permet l’ajout d’action :

  • Tabular Action Editor
  • Tabular Display folder
  • Tabular Hidemember
  • Tabular Sync description
  • Tabular Translation Editor

BISM Normalizor

Permet de comparer et fusionner des modèles, ex. Excel PowerPivot VS. Tabular modèle sur instance SSAS donc pratique après import d’un modèle PowerPivot sur server ou SharePoint

DAX Studio

Excel Add-in. Plus pratique pour écrire du DAX et permet de lancer les requêtes DAX puis voir le détail des perf. Permet également d’analyser les traces du profiler.

BISM ServerMemoryReport

Fichier PowerPivot qui requête les DMVs de la base SSAS pour visualiser la mémoire utilisée.

Ludovic – Consultant décisionnel MCNEXT

[PASS SUMMIT 2013] Deep Dive into the Power Query Formula Language

Speakers : Faisal Mohamood – Lead Program Manager, Theresa Palmer-Borovski – Program Manager & Clement Szyperski

Niveau de la session : 400

Agenda :

  • Design Goals & Guidelines Principles
  • Lang Constructs & Type System
  • Demo, demo, Demo

La session démarre avec les objectifs de construction du langage (utlisateurs avancés d’Excel, Syntaxe simple, proximité avec DAX…)

1. Principes :

  • Dynamique
  • Fonctionnel
  • Hautement ordonné
  • Typage optionnel

2. Type System :

Ensemble de types restreint

Possibilité de définir des types personnalisés

3. Values :

  • Null
  • Logical
  • Number
  • Text
  • Date …
  • Duration

4. Complex Values :

  • List
  • Record
  • Table
  • Fonction
  • Type
  • Binary

5. Fonctions Unaires

Existence de forms syntaxiques spéciales (expressions conditionnelles, Let expression, Error Expression, Try expression)

Existence de types structures (List, Record, Table, Function)

 

Passé la partie théorique assez courte (il en faut bien un peu non), le reste de la présentation enchaine démo sur démo des fonctions du langage : c’est ce qu’il faut regarder de la session.

Conclusion

Une très bonne session avec un aperçu des possibilités offertes par le langage. Une alternative à SSIS dans certains cas ?

Pascal Rouzé

[PASS SUMMIT 2013] Enriching Your BI Semantic Tabular Models with DAX

Speaker : Kasper de Jonge – Program manager, Microsoft

Niveau de la session : 300

1. Présentation du DAX

2. Présentation des améliorations apportées en 2012

Démo :

  • Excel 2013 sur données d’aventureworks
  • Ajout d’une colonne calculée
  • TCD et explication du filtre de contexte
  • Ratio DAX to parent via fonctions CALCULATE ET ALL
  • Fonction DAX DIVIDE : à utiliser à la place de / car performances meilleures et gestion auto de la division par zéro (version PowerPivot de SQL 2012 SP1)
  • Fonction DISTINCTCOUNT
  • Fonction RANKX (avec ALL et IF et ISBLANK et BLANK)
  • Fonction TOPN
  • DAX avec gestion habituelle de current year ou next year ou previous year (utilisation de IF, SWITCH, HASONEVALUE, DATEADD + USERELATIONSHIP + une dimension DateType contenant les 3 valeurs (current year, prev year…))
  • Relation parent enfant : créer une colonne calculée contenant l’info des différents niveaux (avec la fonction PATH) et autant de colonnes level1, level2 que de  niveaux (fonction LOOKUPVALUE et PATHITEM) + une hiérarchie (employee : level1, level2…)
  • Many to Many via CALCULATE et SUMMARIZE sur Employees-àRegionßSales

3. Présentation du schéma (habituel) de l’architecture du modèle « BI Semantic Model »

4. Présentation des DAX Query (3 exemples avec EVALUATE + CROSSJOIN + VALUES + FILTER + GENERATE)

En fin de session : indication d’une astuce :

  • on peut importer dans Excel (via option table de l’import) le contenu d’un onglet du modèle de données de PowerPivot

puis éditer (ou copier : genre evaluate + summarize) du dax via le menu conceptuel (click droit dans Excel sur le tableau de données importée)

Conclusion :

Ouf : enfin une session très sympa (de niveau plus élevée).

Rien de nouveau non plus mais je conseille vivement cette session à tous ceux qui vont  devoir faire du dax : aperçu de toutes les possibilités.

Session avec un speaker très bon

Pascal Rouzé

[PASS SUMMIT 2013] PowerPivot Data Modeling Techniques Self-Services BI to Enables Users

Speaker : Susan O’connell – Principal Architect RevGen Partners

Niveau de la session : 200

Agenda :

  • Necessity of BI :

Current BI Focus Area and Needs

Microsoft Data Vizualisation Tools

Self-service BI

  • PowerPivot :

PowerPivot Results for Users

Data Mashup and organisation

Calculations and KPs with DAX

  • Besoin entre autre : bridge the gap between business and IT

Présentation d’un cas d’utilisation typique :

Analyse d’entreprise : complainte = formaliser besoin, attendre et ne même pas avoir accès a toutes les données sources de l’entreprise

Et maintenant ?

Data Visualisation tools :

Présentation des noms des outils par type d’utilisateur cible

SSRS, power pivot query geomap, Q&A, performance point …

Explication des problématiques différentes entre les environnements traditionnels (vielle BI) et les demandes dynamiques des utilisateurs.

Présentation de PowerPivot =

Results for Users (free addin, intégration des données de sources différentes, exploration/analyse de la donnée, publication dans SharePoint)

Démo :

Construction d’un modèle de données (via AdventureWorksDW2012 !, filtres, remove columns, détection des relations, masquer des champs…, intégration d’une source supplémentaire =table liée d’excel, ajout d’une colonne calculee avec related, création hiérarchie)

Démo :

Un tableau croisé dynamique reposant sur le modèle (tri des mois sur le numéro de mois via le modèle de données, formule DAX de marge en colonne calculée puis corrigée en formule dax globale, puis KPI)

Conclusion :

Session trop simple (même si démos parfaites et sarah is so cute) = j’espérais découvrir des méthodes de modélisation avancées (M to M, astuces, DAX) mais non…

Pascal Rouzé