journées sql server 2012 - dax pour les fans de mdx
TRANSCRIPT
Edition 2012 – 10 et 11 décembre
Rejoignez la Communauté
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
DAX POUR LES FANS DE MDXLE TABULAIRE C’EST PAS AUTOMATIQUE
David JOUBERT Itecor
François JEHLInfinite Square
Société de conseil, expertise, réalisation, et formation, exclusivement sur les technologies Microsoft de développement d’applications et de la plateforme applicative
25+ collaborateurs spécialisés sur les techno MS dont 10 MVP…
GOLD Certified Partnersur 4 domaines de compétences
Agréé CIR Centre de formation
agréé
• Fondé en 1992 • 130 employés • 25 nationalités • 5 bureaux en Europe:
Vevey, Genève, Zürich, Paris, Prague
Nos Partenaires
• 4 Practices• Architecture & Développement• Testing• Project Management• IT Organisation
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
1. PRESENTATION D’ANALYSIS SERVICES 2012
Edition 2012 – 10 et 11 décembre
AMELIORER ANALYSIS SERVICES
Se baser sur les forces et le succès de la plateforme, tout en lui permettant d’atteindre une base plus grande d’utilisateurs
Ajouter au mode multidimensionnel un mode relationnel, plus connu des développeurs
Fournir ces deux possibilités dans une seule et même plateforme, permettant de réunir les points forts des deux mondes.
Disposer d’une plateforme pouvant servir de base à toute application décisionnelle.
Objectifs d’Analysis Services à long
termeBI Semantic Model
Edition 2012 – 10 et 11 décembre
BI SEMANTIC MODEL?
6
One Model for All End User Experiences
Outils ClientsAd-hoc, Rapports,
Scorecards, Dashboards,
Applications…
Sources de donnéesBases de données, OData,
Excel, CSV…
BI Semantic Model
Modèle de conception
Logique métier
Stockage des données
BI d’EquipePowerPivot
pour SharePoint
BI PersonnellePowerPivot
pour Excel
BI d’EntrepriseAnalysis
Services
Edition 2012 – 10 et 11 décembre7
BI Semantic Model
DAX MDX
Déploiement
Stockage des Données
Logique métier
Outil de développement
Mode de conception
Type de projet PowerPivot Tabular Multidimensionnel
Excel
DAX
In-MemoryN/A
SharePoint / Analysis Services
PowerPivot
Tabulaire
SSDT
DAX
In-MemoryDirectQuery
Analysis ServicesTabulaire
Tabulaire
SSDT
MDX
MOLAPROLAP
Analysis ServicesMultidimensionnel
Multidimensionnel
Applications Tierces
ReportingServices Excel PowerPivot SharePoint
InsightsPowerView
LOB Fichiers OData SQL Azure
Bases dedonnées
BI Semantic Model
Support du DAX dans
MD avec CTP
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
2. PROBLEMATIQUES COMMUNES EN MULTIDIMENSIONNEL• Filtrage et Multisélection• Attribute relationships et overwriting…
Edition 2012 – 10 et 11 décembre
QUELS SONT-ILS?•On peut en citer deux emblématiques
• La méconnaissance des attribute relationships et de leur impact sur la résolution des requêtes
• L’hétérogénéité des modes de filtrage et leur gestion dans les membres calculés
Edition 2012 – 10 et 11 décembre
RELATIONS D’ATTRIBUTS•Soit la relation
• Couleur est lié à Produit (related attribute)•Lorsqu’une expression MDX est évaluée, la sélection d’un membre sur un attribut modifie les attributs liés
• On appelle cela de l’attribute overwriting
Produit
Couleur
WITH MEMBER [Measures].[Red Products Net Income]AS(
[Measures].[Net Income],[Product].[Color].&[Red]
)
Edition 2012 – 10 et 11 décembre
RÈGLES D’OVERWRITING
•La sélection d’une couleur remet les produits à (All)
•Toute action sur un attribut parent remet à (All) la sélection des enfants• On dit que les attributs enfants sont implicitement surchargés à All
Sélection explicite de produit Impact[Couleur].[All] [Couleur].[All] [Produit].[All][Couleur].[Rouge] [Couleur].[All] [Produit].[All]<Toute sélection> [Couleur].[Rouge] [Produit].[All]
Edition 2012 – 10 et 11 décembre
RÈGLES D’OVERWRITING
•En revanche la sélection d’un produit impacte directement la couleur
•On dit que les attributs enfants sont implicitement surchargés à une valeur dépendant de la sélection des parents
Sélection explicite de couleur Impact sur le produit[Produit].[All] [Produit].[All] La sélection reste
identique[Produit].[Vélo Rouge]
[Produit].[All] [Couleur].[All]
<Toute sélection> [Produit].[Vélo Rouge]
Exists( [Couleur].Members, [Produit].[Vélo Rouge]) i.e. [Couleur].[Rouge]
Edition 2012 – 10 et 11 décembre
ATTRIBUTE RELATIONSHIPS
Région Pays Continent
• La chaîne d’attributs est linéaire• L’application des règles précédentes est complètement intuitive• Si je sélectionne « USA », Région est à (All) et Continent
à « Amérique du Nord »
Edition 2012 – 10 et 11 décembre
ATTRIBUTE RELATIONSHIPS
Date MoisTrimestre Année
Mois de l’année
• Si en revanche on complexifie un peu…• On peut obtenir une « v-shaped » relationship• Dont le comportement obéit aux règles dictées
précédemment seulement pour les attributs directement reliés à la sélection!
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
DEMO : IMPACT DES ATTRIBUTE RELATIONSHIPS SUR LE MDX
- Relations v-shaped et comportements étranges
Edition 2012 – 10 et 11 décembre
FILTRAGES ET MULTISÉLECTION•Le MDX permet deux moyens de faire de la sélection• Via une sous-requête• Via la clause WHERE que l’on appelle aussi slicer
• Il est important de comprendre la différence fondamentale entre ces deux moyens
Edition 2012 – 10 et 11 décembre
FILTRAGE AVEC SLICER•Met à jour le contexte de requête avec les coordonnées courantes (détectable par Existing sur les SETS)
•Support nativement uniquement des tuples• Pas de sets• Emulation historique de la multisélection à travers des membres calculés (Proclarity, Excel 2003)
WITH MEMBER [Filter]ASAggregate(…)
Edition 2012 – 10 et 11 décembre
FILTRAGE AVEC DES SUBQUERIES•Appliquent un Exists() sur les axes de la requête suivi de VisualTotals()• On requête vraiment un sous-cube
•Mais aucune mise à jour du contexte de requête • Existing inopérant sur les données filtrées dans la sous-requête)
• En revanche l’autoexists des jeux nommés permet de les détecter
Edition 2012 – 10 et 11 décembre
ET LA MULTISELECTION?•Elle pose traditionnellement des problèmes…• CurrentMember inopérant
•La détection du contexte de cellule est très complexe et dépend des modes de filtrage…
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
DEMO : FILTRAGE ET MULTISELECT
- Adaptation des calculs pour gérer le multiselect- Détection du contexte de cellule selon le
filtrage
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
3. RESOUDRE CES PROBLEMES ELEGAMMENT AVEC DAX
Edition 2012 – 10 et 11 décembre
POURQUOI APPRENDRE LE DAX•Parce que c’est le langage de conception dans PowerPivot
•Pour son accessibilité (langage à vocation des utilisateurs)
•Pour la facilité de création de mesures à l’aide de fonctions simples• Distinct Count• Year To Date• …
Edition 2012 – 10 et 11 décembre
CONTEXTE EN DAXLe contexte vous permet d'effectuer une analyse dynamique dans laquelle les résultats d'une formule peuvent changer pour refléter la sélection de ligne ou de cellule actuelle, ainsi que toutes les données associées
Les contextes sont primordiaux pour :• Créer des analyses dynamiques performantes• Résoudre les problèmes dans les formules
Il en existe de 3 formes : ligne, requête, filtre
Edition 2012 – 10 et 11 décembre
CONTEXTE DE LIGNEC’est le contexte qui paraît le plus intuitif : il inclut toutes les valeurs des colonnes dans la ligne actuelle d’une table (plus les valeurs des tables reliées)
Edition 2012 – 10 et 11 décembre
CONTEXTE DE REQUÊTEIl correspond au sous-ensemble de données qui est récupéré dans une formule. Lorsqu'on crée un tableau croisé dynamique, les entêtes de lignes et les entêtes de colonnes agissent sur notre mesure. L’ensemble de ces intersections constitue le contexte de requête.
Ici le contexte de requête de la cellule surligné est :North America, 2007 et Bikes
Edition 2012 – 10 et 11 décembre
CONTEXTE DE FILTREIl correspond à la possibilité de spécifier des contraintes sur l’ensemble des valeurs autorisées dans une colonne ou une table à travers une formule. Il prédomine les contextes de ligne et de requête.
Ici la fonction « ALL » contourne le contexte de ligne et spécifie un contexte de filtre comprenant toutes les lignes de la table de faits.
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
DEMO : CONTEXTE EN DAX
- Focus sur contexte de lignes multiples avec la fonction EARLIER
- Focus sur contexte de filtre avec les fonctions FILTER, ALL et ALLEXCEPT
Edition 2012 – 10 et 11 décembre
TIME INTELLIGENCE EN DAXDAX a introduit 35 fonctions d’analyse temporelle intégrées
• Fonctions qui renvoie une date simpleEx : FIRSTDATE, LASTDATE, STARTOFMONTH, ENDOFQUARTER, …
• Fonctions qui retournent une table de datesEx : PREVIOUSDAY, DATESMTD, PARALLELPERIOD, …
• Fonctions qui évaluent une expression sur une période
Ex : TOTALYTD, OPENINGBALANCEYEAR, …
Edition 2012 – 10 et 11 décembre
TIME INTELLIGENCE EN DAXFonctions renvoyant une seule date
• FIRSTDATE• LASTDATE• FIRSTNONBLANK• LASTNONBLANK
Axé sur une utilisation dans des mesures calculées
Exemple
Edition 2012 – 10 et 11 décembre
TIME INTELLIGENCE EN DAXFonctions renvoyant une seule date
• STARTOFMONTH• STARTOFQUARTER• STARTOFYEAR• Et leurs équivalents ENDOF…
Peuvent être utilisées dans des mesures, mais ont également un sens en attribut.
Exemple
Edition 2012 – 10 et 11 décembre
TIME INTELLIGENCE EN DAXFonctions renvoyant une table de dates
• PREVIOUSDAY• PREVIOUSMONTH• PREVIOUSQUARTER• PREVIOUSYEAR• Et leurs équivalents en NEXT
Plutôt à destination de mesures calculées
Exemple
Edition 2012 – 10 et 11 décembre
TIME INTELLIGENCE EN DAXFonctions renvoyant une table de dates
• DATESMTD• DATESQTD• DATESYTD• SAMEPERIODLASTYEAR
Plutôt à destination de mesures calculées
Exemple
Edition 2012 – 10 et 11 décembre
TIME INTELLIGENCE EN DAXFonctions renvoyant une table de dates
• DATEADD• DATESBETWEEN• DATESINPERIOD• PARALLELPERIOD
Fonctions permettant de faire des calculs sur les dates
Exemple
Edition 2012 – 10 et 11 décembre
TIME INTELLIGENCE EN DAXFonctions évaluant une expressionSur une période temporelle
• TOTALMTD• TOTALQTD• TOTALYTD
Ces fonctions s’utilisent directement avec des mesures
Exemple
Edition 2012 – 10 et 11 décembre
TIME INTELLIGENCE EN DAXFonctions évaluant une expressionSur une période temporelle
• OPENINGBALANCEMONTH• OPENINGBALANCEQUARTER• OPENINGBALANCEYEAR• Et leurs équivalents en
CLOSING
Ces fonctions s’utilisent directement avec des mesures
Exemple
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
DEMO : TIME INTELLIGENCE EN DAX
- Exemple simple d’utilisation des fonctions temporelles
- Création d’un STARTOFWEEK- Exemple de création d’une dimension Période
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
4. CHOISIR LE MDX POUR CERTAINES PROBLEMATIQUES
Edition 2012 – 10 et 11 décembre
POURQUOI ENCORE FAIRE DU MDX•Parce que le Script MDX est puissant
• SCOPE Assignments • Membres calculés sur les dimensions
•Parce que les hiérarchies servent de support à des calculs usuels
•Parce que l’on gère en natif des scénarios courants• Traduction• Conversion de devises• …
Edition 2012 – 10 et 11 décembre
FONCTIONNALITÉS AVANCÉES•La séparation des KeyColumns et NameColumn est parfois embêtante…
• Mais elle permet la traduction transparente des cubes
•La conversion de devise est gérée dans élégamment avec des Many To Many et des Measure Expressions
Duplicate attribute key found in …
Edition 2012 – 10 et 11 décembre
HIERARCHIES•Le concept de hiérarchie dans Tabular est purement cosmétique• Comme une user hierarchy sans attribute relationships
•Des concepts comme les parents, enfants, frères, cousins, sont difficilement exploitables en DAX• Et pourtant tellement utiles…• Ratio To Parent
Edition 2012 – 10 et 11 décembre
SCRIPT MDX•Tabular permet seulement deux types d’éléments calculés• Colonnes calculées (MeasureExpression en plus
puissant)• Mesures calculées
•Multidimensional permet de créer des membres calculés sur tous les axes
•Mais aussi de réaffecter tout sous-cube de manière ultra-performante grâce aux SCOPEs.• Ex: Pattern « Date Tool » de Marco Russo pour le
contrôle de gestion
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
DEMO : POINTS FORTS DE L’OLAP
- Conversion de devises N-N- Calculs hiérarchiques- SCOPE Assignments
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
5. ET LES PERFORMANCES DES REQUETES?
Edition 2012 – 10 et 11 décembre
COMPARAISON PERFORMANCE DES REQUÊTES
Problématique Métier connue :
• Mon client veut un cube SSAS, mais il ne travaille qu’à des niveaux fins sur les dimensions
• Un cube SSAS en mode multidimensionnel est surtout efficace à des niveaux agrégés
• Qu’en est-il du mode tabulaire ?
Edition 2012 – 10 et 11 décembre
COMPARAISON PERFORMANCE DES REQUÊTES
SSAS Mode Tabulaire• Langage de conception : DAX
• Langage d’interrogation : MDX
• Possibilité d’utiliser le DAX pour interroger le cube
SSAS Mode Multidimensionnel
• Langage de conception : MDX
• Langage d’interrogation :
MDX
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
DEMO : COMPARAISON PERFORMANCE DES REQUÊTES
Rapport SSRS de listing
Hypothèses de départ :- 3 dimensions de 1000 membres (clients, produits, et magasins)- 1 table de fait d’un million de lignes
Hypothèse d’arrivée :- 1 rapport SSRS qui liste les ventes globales
Edition 2012 – 10 et 11 décembre
Sponsors Platinum
CONCLUSION
Edition 2012 – 10 et 11 décembre
CHOISIR LE TYPE DE PROJET•Cela dépend des choix faits pour chaque couche• Modèle de conception : Multidim ou tabulaire• Logique métier : DAX ou MDX• Stockage des données : InMemory, DirectQuery, ROLAP, MOLAP
•En prenant en compte• Les fonctionnalités fournies par les différents types de projets
• Les performances• Les compétences des développeurs • Le temps nécessaire pour finaliser le projet
48
Edition 2012 – 10 et 11 décembre 49
CHOISIR LE TYPE DE PROJET
• Difficile à appréhender• Les modélisations
avancées (parent-child, many-to-many, relations d’attributs, KeyColumn/ NameColumn, etc.) sont supportées nativement.
• Idéal pour des solutions OLAP (ex: planning, budgeting, forecasting) qui ont besoin de concepts multidimensionnels.
• Modèle plus familier pour démarrer
• Les fonctionnalités de modélisation plus avancées nécessitent d’être émulées
• Facile à générer depuis un modèle en étoile
Tabulaire
Multidimensionnel
Edition 2012 – 10 et 11 décembre 50
CHOISIR LE TYPE DE PROJET
• Nécessite la compréhension des concepts multidimensionnels. Plus complexe à prendre en main.
• Les solutions avancées sont encore plus complexes.
• Idéal pour des solutions purement OLAP, mettant en jeu des calculs complexes (SCOPE, jeux nommés, assignments, membres calculés…)
• Basé sur des formules Excel. Relativement simple à appréhender.
• L’appréhension des concepts de contexte de ligne, de filtre, de CALCULATE prend plus de temps.
• Colonnes calculées, mais il n’existe pas d’équivalents strict aux membres calculés ou aux jeux nommés.
DAX MDX
Edition 2012 – 10 et 11 décembre
CHOISIR LE TYPE DE PROJET
• In-Memory• Stockage en mémoire qui
compresse classiquement 10x les données
• Excellentes performances par défaut sans besoin de tuning
• Volume de données limité par la mémoire du serveur
• DirectQuery• Les requêtes DAX sont
transcrites en SQL• Ne supporte que SQL
Server
• MOLAP• Stockage sur disque avec une
compression 3x• Du tuning est requis pour gérer
la montée en cache et les agrégations
• Le support du paging permet de gérer jusqu’à plusieurs TB
• ROLAP• Les requêtes MDX sont
transcrites en SQL• Support de la plupart des bases
de données relationnelles accessibles en OLE DB
51
Edition 2012 – 10 et 11 décembre
Merci à nos SponsorsRencontrez les dans l’espace partenaires
Sponsors Platinum
Sponsors Gold
Sponsors Silver
Edition 2012 – 10 et 11 décembre
Continuez l’expérience onlineRejoignez la Communauté
Edition 2012 – 10 et 11 décembre
Rejoignez la Communauté