#sqlserver

[PASS SUMMIT 2013] Fast Performing SSAS hierarchies : tips and tricks

Speaker : Jose Chinchilla – President & CE AgileBay Inc.

Niveau de la session : 200

1. Types and classification of hierarchies :

* Natural and unnatural hierarchies :

  • natural : existe physiquement sur le disque. Offre de meilleure performance (exemple : year / quarter / month / day),
  • unnatural : non performante car relations logiques entre les attributs –> à éviter au maximum et attention aux problèmes de déploiement à cause des duplicate keys,

* Attribute and user hierarchies :

  • attribute : les attributs de la dimension sont des hiérarchies par défaut (All / liste des valeurs),
  • user : créé par l’utilisateur via des drags & drops,
  • IsAggregatable = False –> pour cacher le ALL dans la hiérarchie d’attributs,
  • Il faut définir les relations entre les attributs dans les user hierarchies afin d’optimiser le temps de réponse des requêtes. La définition de la relation se fait en glissant l’enfant vers le parent (Follow my daddy),

* Values Discretization :

  • Permet de créer des intervalles de valeur (ex. size range – size),
  • Mettre la propriété « DiscretizationMethod » du champ à Automatic puis spécifier le nombre d’intervalles à générer dans la propriété « DiscretizationBucketCount »,

* Bonne pratique : spécifier la propriété « Type » de la dimension et de ses attributs (ex. Type = ‘Time’ pour la dimension date, ‘Geography’ pour la dimension géographie, etc.),

2. Performance Optimizations :

* Modeling : un bon modèle multidimensionnel garantit une bonne performance du cube,

* Aggregations : Pré-aggrégation d’un groupe de mesures selon la granularité des dimensions avec lesquelles il est en relation (via des clés étrangères ou des références),

* Partitions : La stratégie classique de partitionnement  est de la faire en fonction du temps puis de processer uniquement celle relative au mois en cours, au 2 derniers mois, etc. (tout dépend du besoin métier),

* SSAS QueryLog : option disponible via clic droit sur l’instance SSAS, Propriétés, Général et activer l’option Log / Query Log / CreateQueryLogTable qui va créer une table de journalisation dans la base de données spécifiée. Cette table permettra de voir toutes les requêtes exécutées sur le cube avec des statistiques (Start Time, Duration, etc.),

* Usage-Based Optimisation Wizard,

Conclusion :

Session qui reprend les bases de mise en place des hiérarchies dans un cube OLAP et présente quelques astuces d’optimisation. Elle reste peu intéressante si on maîtrise déjà le sujet mais assez pédagogique sinon…

Ismaïl Ben Lamine

[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] Why is SQL Server slow right now ?

Speaker : Brent Ozar – MCM, MVP Brent Ozar Unlimited

Niveau de la session : 200

Troubleshooting :

1. sp_whoisactive :

Procédure stockée qui permet de voir la ou les requêtes en cours d’exécution sur le serveur –> disponible par défaut dans SQL Server,

2. sp_Blitz :

Procédure stockée qui priorise la liste des problèmes sur le serveur SQL par ordre décroissant (situation en général et non à l’instant T) –> disponible en téléchargement gratuit,

3. sp_AskBrent :

Procédure stockée qui liste tous les éléments qui ont mis plus de 5 secondes (durée paramétrable) à s’exécuter. Dans le résultat retourné, on retrouve également :

  • la consommation (CPU, disque, I/O) des requêtes,
  • liens URL vers la description du problème,
  • recommandation d’optimisation,
  • Query Plan (plus besoin de trace),

En rajoutant le paramètre @ExpertMode = 1 à la procédure, on peut voir tout ce qui se passe sur le serveur regroupé par activité.

Il est possible d’exporter le résultat de la procédure dans une table (en renseignant les paramètres @OutputDatabaseName, @OutputSchemaName et @OutputTableName) et de planifier auquel cas un job derrière –> penser quand même à vider la table périodiquement.

En rajoutant le paramètre @AsOf, on peut suivre les requêtes à plus ou moins 15 minutes de cette date.

4. OpServer :

Application web open source qui permet de monitorer l’activité serveur (jobs, espace disque occupé, espace mémoire, etc.). Elle doit être installée sur le serveur web interne de l’entreprise. Elle est sécurisée et les données récoltées restent internes.

Conclusion :

Excellente session. Peu de slides, beaucoup de démos. Speaker dynamique, drôle et qui maîtrise parfaitement son sujet. Les outils et procédures présentés sont intéressants. À tester / approfondir…

Ismaïl Ben Lamine

[PASS SUMMIT 2013] Squeezing Top Performance from your Virtualize SQL Server

Speaker : David Klee

Niveau de la session : 300

Agenda :

  • What does virtualization mean for DBAs
  • Environnement Design and Performance Impact
  • SQL server virtual machine Construction
  • Demo :  Tuned versus non Tuned VM performance
  • Real World Performance Killers (and How to Fixed them)

1. What is virtualization :

Le speaker explique ce qu’est la virtualisation. Historiquement cela est du à la volonté de consolider le matériel.

La contention des ressources détermine le ratio de consolidation.

Ne pas oublier que tout le reste de la session est également lié à l’utilisation de SQL server.

2. Hypervisor Options (Q3 2013) :

Vmware vsphere 5.5 vs Microsoft Hyper V-2012 R2

Coût de Hyper V (inclus dans Windows Server) = bonne nouvelle par rapport à celui de Vmware

3. Environnement :

(underneath your data)

Storage présenté en tant que partage aux VM (modèle physique puis modèle logique).

4. SQL server virtual machine Construction :

La session donne des conseils de configuration pour chaque composant de la virtualisation (pour le détail il faudra lire les slides de la session) :

  • Host – Serveur Physique
  • (Shared) Storage
  • Réseau
  • Configuration de la machine virtuelle
  • Dimensionnement de la machine virtuelle
  • Configuration des disques la machine virtuelle (8 niveaux logiques puis placement des disques virtuels sur le système physique)
  • Configuration du système d’exploitation Windows Server
  • Configuration d’une instance SQL Server

5. Performance Benchmark :

  • Attention c’est la performance c’est subjectif sauf si vous avez des métriques
  • Le faire pour chaque composant
  • Tools : perfmon / SCOM / VMware vCenter , SQL DMos, Tierce
  • Conseil : open source de benchmarking super = DVDStore (http://linux.dell/dvdstore) : le test donne la VM non tunée 31% plus lente

6. Real World Performance Killers :

4 cas sont abordés en décrivant les symptômes et les remèdes (storage performance, Host overcommitement, noisy neighbors, …)

La conclusion est un encouragement à tout virtualiser. Penser 1 Application par SQL server VM (mieux pour la maintenance car sinon c’est pas top quand on doit arrêter une VM sur laquelle il y a 10 appli).

Conclusion :

Session recommandée (à tous) si vous souhaiter améliorer vos performances rapidement (dès lors que vous virtualisez) et connaître la bonne démarche à adopter.

Rien de magique non plus (car le travail reste à faire !) mais la session a le mérite d’être très bien faite (slides et répartition des sujets parfaits).

Pour les geek et les geekettes c’est le moment de jouer avec SQLIO, IOMETER, CrystalDiskMark, Iperf … et collecter périodiquement vos métriques (débit MB/S, I/Os per seconde, Latency in ms : inf a 10 ms !).

Pascal Rouzé

[PASS SUMMIT 2013] CAT : From question to insight with HDInsight and BI

Speaker : Cindy Gross, Microsoft

Niveau de la session : 400

L’itinéraire de la session est le suivant :

On part d’une question : comment améliorer les SLA autour de SQL Server ?

On s’intéresse au Design d’une solution possible dont les enjeux majeurs sont les suivant :

  • La capacité à avoir une bonne bande passante
  • La mise en place de Scale Out
  • La réduction des temps de réponse

La solution : Windows Azure SQL Data Services

 

Si maintenant les données correspondent à des « Big Data », la solution est HD Insight

HD Insight est l’implémentation de Hadoop, framework pour manipuler du « Big Data », coté Microsoft et dans Windows Azure.

 

Pour la couche stockage on utilise HDFS (Hadoop Distributed File System) et WASB (Windows Azure storage Blob)

Les commandes Hadoop Map & Reduce  sont overridées et reste transparentes pour l’utilisateur.

Pour le Data cleansing on utilise une interface et un langage de script appelée « Pig »

Pour créer des tables structurées, les partitionner et les requêter on  utilise une interface et un langage de script appelé « Hive »

Ces tables sont créées dans une zone de staging appelé HCatalog

 

Une fois les données stockées dans le HCatalog pour les déplacer dans SQL Server on utilise une interface appelé Sqoop.

Pour la partie Ordonnancement de batch il existe une interface et un langage de script  appelé Oozie  qui ressemble aux commandes xmla.

Pour se  connecter notamment depuis  Excel au « Big Data » on utilise un connecteur ODBC Hive.

Une fois les données exposé on peut faire de la BI sur du « Big Data »

Des démonstrations ont été faites :

  • Unstructured log stocké dans Azure
  • Script Pig pour faire du nettoyage dessus
  • Hive script pour créer une table structurée et partitionnée et requêter les données

Ensuite on présente toute la partie déploiement et configuration dans WA, puis la création de cluster

Enfin, on fini par présenter WebHCat (Templeton), une interface pour la programmation autour de HDInsight avec des langages .NET, LINQ, Powershell

Cela s’appuie sur l’API REST.

Conclusion :

Session très bien menée.

HD insight est une plateforme qui a l’air inachevée, c’est probablement une solution transitoire qui est née d’une solution open source appelée Hadoop,  qui a été exploitée et simplifiée par une société appelé HortonWorks mais qui semble converger vers cette solution Microsoft qui masquera probablement toute la complexité de Hadoop et HortonWorks.

Sébastien Madar

[PASS SUMMIT 2013] Power BI Self-Service Information Management

Speakers : Matthew Roche & Ofer Ashkenazi – Microsoft Corporation – Power BI Team

Niveau de la session : 200

1. Power BI review :

  • 1 milliard d’utilisateurs Office,
  • 1/4 des entreprises clientes sont sur Office 365,

Démo SSIM dans Power BI:

– US Airports Weather Data et Arrival Delays,
– Sources : Windows Azure HDInsight, Excel,
– Share Query : il est important de rajouter la description de la requête afin de faciliter l’indexation de la Query et donc la retrouver en top list dans online search,
– Power MAP (nécessite la version 64 bits de l’os et d’Excel),

2. Power BI SSIM main modules :

  • Data Catalog : contient notamment les métadonnées des différentes sources internes et externes (les Query y sont publiées suite au Share),
  • Power Query in Excel,
  • Admin Center,
  • Data Management Portal,

3. Delivering tangible benefits for customers :

  • Data Steward : s’assure que les métiers ont les données dont ils ont besoin quand ils en ont besoin (création et annotation des tueries, identification des besoins en données, suivi des statistiques d’utilisation des données (usage analytics par date / utilisateur / requête)),
  • IT Admin : se charge de fournir les sources de données, de gérer les droits d’accès et du Data Management Gateway,

Conclusion :

Session assez intéressante pour découvrir et comprendre l’écosystème de Power BI en termes d’outils et d’acteurs.

Ismaïl Ben Lamine

[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é