[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] Advanced Analysis with Excel 2013

Speaker : Dejan Sarka, SolidQ

La session parle de data mining et de l’utilisation des outils Microsoft associés (Excel + PowerPivot)

Agenda :

  • Excel Data preparation and Data mining
  • Table analysis with Excel
  • Introducing PowerPivot
  • Combining data mining with PowerPivot

Introduction :

L’Addin Data Mining est à chercher avec la version SQL 2012

Il utilise en source une  connection à SSAS en multidim ou une Range Excel ou une Table Excel

Concrètement on peut utiliser 3 addins :

  • Le client Data mining pour Excel
  • Table Analysis Tool pour Excel
  • Les modèles de Data Mining pour Visio (pas encore pour excel 2013, pas montré par le speaker)

Le client Data mining pour Excel :

Tout se trouve sur la base (Ribbon)

La préparation des données se fait avec Explore Data tool + Clean Data Tool (nettoyage basique = re-nommage de colonne, suppression des valeurs aberrantes = outliers) + Sample Data tool (échantillonnage pour training du modèle)

Pour le modèle de données on peut utiliser :

  • Classify tool -> utilise Decision tree
  • Estimate tool -> utilize Regression tree
  • Cluster tool -> utilize Clustering
  • Associate tool -> utilize des règles d’association
  • Forecast tool -> utilize Times Series
  • Options avancées : créer une nouvelle mining structure ou pour ajoutter un modèle à une mining structure existante

Pour la précision et la validation des données on utilise dans la section correspondante du Ribbon :

  • Accuracy Chart
  • Classification Matrix
  • Profit Chart
  • Cross Validation tool

Les modèles que l’on veut valider doivent partager la même structure de mining

Le client de data mining sert aussi pour :

  • Model usage
  • Management
  • Connections

Démo :

  • analyser des données via Excel (connection a une bd sql 2012 adventureworksdw2012)
  • utiliser le Data mining client : explorer les données à partir d’une table de données d’Excel data et affichage de la distribution continue d’un champ (par ex du revenue)
  • Convertir la table en range puis classify (uses decision tree) pour analyser les achats de vélos : cela crée un  modèle et sa structure dans SSAS, on peut voir alors l’arbre de décision (par exemple depuis ssms ou excel : idem)
  • On peut aussi utiliser document model via Excel (option qui existe) et copier une des représentation graphique (image statique)

Table analysis tool :

Il apporte une vision orientée plus business et peut être utilisé pour :

  • Analyze key influencers (Native Bayes mining model)
  • Detect categories (Clustering mining model) and Fill From Example (Logistic Regression mining model)
  • Surligner les Exceptions categories (Clustering mining model)  et analyser le panier du consommateur (Règles d’association)
  • Outil de prévision (Forecast tool via Times Series model et Prediction calculator via Logistic Regression)
  • Goal seek (que modifier pour arriver à l’objectif) et What if (quell est l’impact d’une modification)

Démo :

  • Gool seek (target bike buyer) pas très utile car chiffre utilise en source pas top
  • What if (target bike buyer avec distance — > renvoie un niveau de confiance)
  • Hihlight Exceptions (lent c’est pour cela qu’on échantillonne) : marque les lignes sources et produit un rapport

Combining data mining with PowerPivot :

L’introduction sur PowerPivot décrit BISM puis explique différentes implémentations du modèle tabulaire de SSAS :

  • Personal BI  = Excel PowerPivot
  • Team BI = BI avec SharePoint
  • BI d’entreprise = SSAS en mode tabulaire

On peut utiliser PowerPivot pour du datamining. Il faut :

  1. Créer un rapport de table pivot à plat
  2. Supprimer les sous totaux et totaux
  3. Formater la zone de données en tant que table
  4. Utiliser l’outil Table Analysis pour explorer les données

On peut faire l’inverse, utiliser du datamining en tant que source de PowerPivot. Pas d’éditeur DMX dans PowerPivot mais voici la solution de contournement :

  1. Définir SSAS multidim database en source de données
  2. Ecrire dans un éditeur MDX (genre SSMS) la requête DMX manuellement (bonne chance)
  3. Importer les données
  4. Créer les relations

Démo :

Première démo

  • import de donnes dans PowerPivot via query sur source sql + measure total of bike buyer (sum) + count of bike byer + moyenne via sum/counta
  • création pivot table report
  • exclure subtotals et total pour avoir une table
  • convertir  en formules
  • analyser via tool d’excel

Deuxième démo

  • création dans Visual Studio d’un data mining model
  • Création  requête sql (en dmx avec prediction join) pour avoir des clusters par customerkey
  • Import du modèle dans PowerPivot via la requête préparée avant en dmx
  • Connexion dans le modelé de PowerPivot entre vTargetMail et la Query
  • Création d’un graphique (histogramme de prédiction des buyers by cluster)

Conclusion :

Bonne session si on aime le Data Mining.

Encore une utilisation de la self-service BI (on oublie un peu que le data mining existe ?).

Avantage Microsoft : la gratuité des outils que l’on a déjà pour du data mining (Excel + SSAS) quand on fait  de la BI.

Oui Microsoft n’est pas le standard pour le data mining …

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é

Comment passer du mode multidimensionnel au mode tabulaire sous Sql Server 2012

Lors de l’installation d’une instance Analysis Services, on a le choix entre 3 modes :

1. Le Multidimensionnel
2. Tabulaire
3. Powerpivot (pour Sharepoint)

Pour voir lequel de ces trois modes est installé, en se connectant à Analysis Services dans SSMS, on peut voir quelle icone apparaît.

Les differents mode dans SSM

Les differents modes dans SSMS

Malheureusement, nous ne pouvons avoir qu’un seul mode par instance AS.
Pour utiliser plusieurs modes, il faut donc installer des instances différentes.

Il y a tout de même une solution simple qui nous évitera d’installer une autre instance.

Lire la suite