[JSS 2013] Session : SSAS ROLAP sur Column Store Index (CSI)

Avec les possibilités offertes par SQL Server 2012, il est peut-être temps de repenser la place des bases SSAS dans les systèmes d’informations. Nous vous proposons de détailler les modes de conception et les performances de deux architectures : le ROLAP sur du XVelocity SQL Server d’un côté et du Tabular de l’autre.

 Level : 400
Speaker(s) : Patrice Harel, Charles-Henri Sauget

Le principal problème du ROLAP est un problème de performance car ce mode attaque directement la base relationnelle.

L’idée est d’utilise ici le CSI permettant d’améliorer grandement les performances des requêtes sur une base relationnelle.

– Pour le test : a été récupérée la base Wikipedia. La plus grosse table de faits contient 250 millions de lignes. La base fait 120 Go.

– Le CSI : la donnée est stockée en colonne et pas en ligne. Elle est compressée. Chaque requête ramène moins de données : on ne sélectionne qu’une partie de la table (le reste des colonnes n’est pas ramené).

Les tables sont séparées en partitions, les partitions en segments. Chaque segment étant rempli par un thread, les partitions peuvent être remplies en multithreading : gain de performance.

3 ajouts sur la version 2014 de SQL Server :dictionnaire secondaire et le delta store. Le plus important pour nous : insertion, suppression et mise à jour sur les tables avec le CSI !

Best practice : si plusieurs champs sont présents dans le CSI, il faut toujours partir du champ qui a le moins de données vers celui qui en a le plus.

Les avantages du CSI :

  • Cluster CSI en lecture/écriture dorénavant
  • BLOB
  • Segment Elimination
  • Batch Mode
  • Compression Archive.

Le CSI permet un gain de place énorme sur le disque : facteur 17 sur la base de démo des speakers.

Les intérêts du ROLAP :

  • Pas ou peu de temps de process
  • Temps réel
  • Partitionnement côté SQL

Les optimisations principales à apporter sur le ROLAP :

  • Eviter le Spill (quand il n’y a pas assez de mémoire sur le poste, la requête s’exécute en tempDB.
  • Création de statistiques
  • Dans le plan d’exécution, on doit être en batch et pas en row sur le CSI. L’option OPTION HASH JOIN sur une requête permet de supprimer les Nested Loop.
  • Optimiser les many to many

Les speakers ont ensuite présenté un tableau comparatif entre ROLAP, MOLAP et Tabular.

Dans 2 tests sur 3, le ROLAP s’est montré le plus rapide. Le tabular étant dernier, ce qui peut s’expliquer par la quantité de données chargées en mémoire provoquant des accès disque.

Pour être honnêtes, les 2 speakers ont aussi annoncé que le MOLAP  n’était pas optimisé.

Les limites du CSI :

  • Des types non supportés (varchar(max), XML,…)
  • 2 types de CSI : Clustered et non clustered. Le clustered doit être unique dans la table.

Conclusion :

2 super speakers qui maitrisent leur sujet sur le bout des doigts.

Le ROLAP reprend ici toutes ses lettres de noblesse grâce au CSI.

Il était tout simplement quasi inutilisable en l’état à cause de ses lenteurs. D’ailleurs la dernière fois que je l’avais vu « en vrai », c’était en 2009.

Couplé à la rapidité du CSI, la solution redevient viable en termes de performance.

Frédéric – Consultant décisionnel MCNEXT

[JSS 2013] Session : SSAS : Test de montée en charge avec Visual Studio

Speaker : Arnaud Voisin
Level : 300-400

Cette session a pour but de nous montrer comment réaliser des tests de montée en charge sur Visual Studio.

Pourquoi en réaliser ?
Pour valider les exigences de performance de la solution, que ce soit l’architecture, le design, l’usage de l’application ou encore la qualité du code.

La session a été découpée en 4 parties :

  1. Déploiement de projets SSAS
  2. Stress tests
  3. Méthodologie des tests de performance
  4. Architecture de test

1/ Déploiement de projets SSAS

Dans cette partie, on va voir qu’il faut être vigilant sur certains points avant le déploiement d’un projet SSAS :

  • Meilleure optimisation SSAS sur les schémas en étoile
  • Parent child : il faut limiter les agrégations et ne les faire que sur les attributs clés, dans le meilleur des cas
  • Distinct count : créer une clé de partitionnement
  • Stockage : ne pas avoir des partitions trop petites (< 200Mo) ou trop grosses (> 3Go)

2/ Stress tests

Voici une liste de pré-requis avant de pouvoir réaliser les stress tests. Il faut avoir :

  • ASQueryPlan (outil de génération de Workload)
  • ASLoadSim (injecteur de charge)
  • Visual Studio Ultimate (Team System)
  • SQL Server
  • Solution SSAS OLAP / Tabular

Il faut aussi choisir son Workload (un Report RS, Excel, Power Pivot, …) et le variabiliser avec ASQueryPlan.

>> DEMO : Workload + Variabilisation

  • Il faut commencer par ouvrir SQL Profiler pour superviser les traitements qui seront lancés. Ensuite, à l’aide d’un refresh sur un fichier Excel, on regarde ce qui remonte dans SQL Profiler
  • Avec l’aide de ASLoadSim, on va pouvoir récupérer les requêtes qui ont été variabilisées dans l’ASQueryPlan >> C’est du code C#

3/ Méthodologie des tests de performance

On commence par définir nos objectifs/cas de test et la longueur des stress tests dont la formule est :

(Nb requêtes/fichiers) * nb fichiers * (tps de réponse estimé + thinktime (??)) Il faut ensuite choisir le modèle de charge voulu (par étape, en fonction des objectifs ou constant). On peut mixer différents tests. Enfin, on choisit les compteurs à monitorer (CPU, I/O, mémoire, requêtes).

>> DEMO : Run d’un load test

Pour cette démo, il nous a montré son code et l’a exécuté. Vous pouvez voir un résultat ci-dessous :

4/ Architecture de test

  • ASLoadSim est monobase c’est-à-dire qu’il ne peut être associé qu’à un cube. Il est possible de le lancer sur plusieurs cubes mais il faudra le dupliquer autant de fois qu’il y a de cube.
  • Architecture controleur-agent : il est conseillé d’avoir un CPU utilisé inférieur à 70% et une RAM supérieure à 10%.
  • Les tests de montée en charge peuvent être dispo sur le cloud via Visual Studio Online. Il faut néanmoins mettre à disposition les fichiers de Workload, le cube et les fichiers de conf associés.

Conclusion :

Les tests de montée en charge se font grâce à Visual Studio. On utilise le provider ADOMDNet pour les cubes MultiDim et Tabular (MDX, DMX et DAX), et d’autres providers pour du SAP, Oracle.
Session assez technique.

David – Consultant décisionnel MCNEXT

Gestion des rôles avec Analyses Services (SSAS)

Cet article a pour but de présenter les différentes phases de la mise en place des droits d’accès aux dimensions et aux mesures d’un cube. Ceci en fonction du rôle et de la place occupée dans l’entreprise.

Cas d’étude :

Snap1

 

                       

Schéma technique de la solution :

 Snap2

Besoin :

La Direction Générale a fait la demande suivante au service décisionnel:

v  Pour la division COMMERCIALE

  • Afficher la mesure Budget Euros
  • Masquer les mesures Budget Effectif et Effectif Disponible (de la division uniquement)

v  Pour La Division RH

  • Accès aux mesures Budget Effectif et Effectif Disponible de l’entreprise

 

 Conseil :

Utiliser des noms explicites pour les rôles afin de cibler le périmètre et les droits d’accès des membres de ce rôle.
Administrer les droits d’accès au niveau du domaine.

Solution technique :

Créer pour  chaque division un GROUPE dans l’Active Directory.
Ces groupes serviront à restreindre l’accès aux données par division.

Récapitulatif :

Snap3

En  pratique lorsqu’un utilisateur appartient à plusieurs rôles, ces rôles se complètent.

Le rôle administrateur est en règle générale appliqué aux DBAs de l’entreprise.
Le rôle lecteur peut être attribué aux développeurs de la solution et ou aux Power Users.

 

 

Tutoriel:

Ouvrir le projet SSAS dans Visual Studio.
Sous l’onglet Role de votre projet SSAS, faire un clic-droit, New Role :

Snap4

Première étape, on va créer le Role Admin.
On définit le nom du rôle et les autorisations attribuées aux membres du groupe sur le cube en cochant les cases Full Control, Process Database, Read definition dans l’onglet général :Snap5

Sous l’onglet Membership, cliquer sur le bouton Add pour rechercher dans l’Active Directory le groupe ADMINISTRATORS :

Snap6

Deuxième étape, on passe à la création du rôle lecteur dont les membres seront les personnes habilitées à voir toutes les mesures du cube sans exception :

Snap7

Au niveau de l’onglet Data Sources, cocher la case Read Definition et laisser  la colonne Acces à None.

Snap8

L’accès se fait en lecture (Read).
La colonne Local Cube/drillthrough Access à None pour interdire le rapatriement du cube en local.
La case Process est décochée afin d’interdire le traitement du cube.

Snap9

3eme étape :

Même procédé que lors de l’étape précédente.
Nous allons détailler dans notre exemple la création du rôle  dont le nom sera «  RoleCommerce ».

Sous l’onglet Cell Data, sélectionner le cube résultat, puis cocher la case Enable  read-contingent permissions.

La requête à taper en MDX correspondante est:
(NOT [Measures].CurrentMember IS [Measures].[Effectif Disponible]) AND(NOT[Measures].CurrentMember IS [Measures].[Effectif Budget])

Snap10

Par défaut, dans l’onglet dimensions, cocher les cases Read Definition afin que les dimensions soient accessibles  en lecture. Dans un second temps, nous allons rendre visible uniquement le code et le libellé de la division 54195 de la Dimension Division pour le rôle concerné (COMMERCE)

Sous l’onglet Dimension Data -> Attribute : déplier le cube Résultat et sélectionner la dimension cube (Dim Division) La dimension division dont le code de division commerciale est 54195.

Deux modes disponibles, un mode Basic (graphique) et un mode Avancé (code MDX) :

Snap12

Après déploiement et traitement du cube, via SSMS ou Visual Studio, repérer le bouton qui permet de choisir l’utilisateur se connectant au cube :

Snap13

Sélectionner le rôle à tester (COMMERCE dans notre cas), puis cliquer sur OK :

Snap14

En glissant,  la mesure Effectif Disponible, Budget Effectif et Budget Euros :

Snap15

Constats et Résultats :
Les cellules relatives aux mesures Effectif Disponible et Effectif Budget sont indisponibles (#N/A).
La mesure Budget Euros est visible pour les membres de la division concernée.
Un dernier détail qui a son importance, le Grand Total de cette mesure ne doit pas faire apparaître le budget des autres divisions. Pour cela, il faut cocher la case Enable Visual Totals dans l’onglet Dimension Data du rôle  :

Snap16

Pour aller plus loin :

Microsoft SQL SERVER 2008 Ebook Analyses Services Step by Step (free e-book)

SSAS – Processer un cube Analysis Services de 8 manières différentes

SSAS-cube
Dans cet article, nous allons voir ensemble huit méthodes nous permettant de traiter un cube OLAP.
Avant toute chose, faisons un petit rappel sur les différents types de traitement de cube suivants :
Process Default, Process Full, Process Clear, Process Data, Process Update, Process Add, Process Index, Process Structure, Process Clear Structure.

[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] From Data to Insight : Views from Microsoft Finance

Speaker : Marc Reguera – Directeur dinancier chez MS Corp

Niveau de la session : 200

 

Vue d’un business analyste qui est un des directeurs financier de MS Corp. sur la nouvelle technologie Power BI.

L’utilisation de Excel 2013 a « changé sa vie »

Il  fait des démos et montre comment il créé lui-même ses rapports, notamment  financier.

 

La Révolution BI en libre-service a commencé depuis 2 ans.

On part sur le fait qu’il y a une frustration qui peut exister chez un analyste envers l’IT pour ne pas avoir ses rapports en temps et en heure.

Solution : Laisser l’analyste créer lui-même ses rapports sans avoir de connaissances techniques.

Agenda :

  1. Créer un dashboard en quelques minutes
  2. Mettre en place sécurité et formules en quelques heures
  3. Cas d’usage : AirFare Cost (frais d’avion)
  4. Publier l’information et construire des graphiques en 3D
  5. L’outil révolutionnaire : Q&A

 

1. Créer un dashboard en quelques minutes

Démo :

Rapprochement entre budget et la VTB sur une année fiscal donnée

Plusieurs axes d’analyse : produit, ville..

VTB : Variance to budget

2. Mettre en place la sécurité et les formules en quelques heures

Démo :

Utilise une base de 400 millions de lignes qui est stocké dans SSAS Tabular.

Il accède à un rapport dont il n’a pas les droits sur un pays en particulier : aucunes données ne s’affichent mais le rapport se lance.

3. Cas d’usage : AirFare Cost (frais d’avion)

Démo :

Exemple : Impact des couts selon la date à laquelle le billet a été commandé

4. Publier l’information et construire des graphiques en 3D

Démo :

  • Unpivot dans Power Query
  • Rapprocher des données publiques et privées dans Power Query
  • Carto avec Power Map
  • Données démographique avec Scatter

5. L’outil révolutionnaire : Q&A

Démo sur le championnat espagnol de foot

Conclusion :

Session très vivante, les démos sont très interactives, il fait parler les données autant d’un point de vue professionnel, la finance, mais aussi personnel avec le foot et le réal de Madrid où il exprime au passage sa passion.

Son pays natal est la France, il y fait allusion souvent dans ses démos comme l’ami de son père qui est artisan à Noisy le grand où il a vécu.

Sébastien Madar