Journées SQL Server 2012 - DAX pour les fans de MDX

Edition 2012 – 10 et 11 décembre

Rejoignez la Communauté


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

http://www.hp.com/country/ch/de/welcome.html

https://myportal.itecor.com/SiteDirectory/ITpartners/Shared%20Documents/Forms/AllItems.aspx?RootFolder=/SiteDirectory/ITpartners/Shared%20Documents/CA&FolderCTID=0x0120004E6A38E584794C42A981C11A369B8FD1&View=%7BFFAC2F95-1FE9-486E-B756-F7C57D8F6AEE%7D

https://myportal.itecor.com/SiteDirectory/ITpartners/Pages/Shunra.aspx

http://www.turnkeysolutions.com/index.php


Sponsors Platinum

1. PRESENTATION D’ANALYSIS SERVICES 2012


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


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


Sponsors Platinum

2. PROBLEMATIQUES COMMUNES EN MULTIDIMENSIONNEL• Filtrage et Multisélection• Attribute relationships et overwriting…


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


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]

)


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]


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]


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 »


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!


Sponsors Platinum

DEMO : IMPACT DES ATTRIBUTE RELATIONSHIPS SUR LE MDX

- Relations v-shaped et comportements étranges


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


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(…)


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


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…


Sponsors Platinum

DEMO : FILTRAGE ET MULTISELECT

- Adaptation des calculs pour gérer le multiselect- Détection du contexte de cellule selon le

filtrage


Sponsors Platinum

3. RESOUDRE CES PROBLEMES ELEGAMMENT AVEC DAX


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• …


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


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)


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


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.


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


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, …


TIME INTELLIGENCE EN DAXFonctions renvoyant une seule date

• FIRSTDATE• LASTDATE• FIRSTNONBLANK• LASTNONBLANK

Axé sur une utilisation dans des mesures calculées

Exemple


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


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



• DATESMTD• DATESQTD• DATESYTD• SAMEPERIODLASTYEAR

Plutôt à destination de mesures calculées

Exemple



• DATEADD• DATESBETWEEN• DATESINPERIOD• PARALLELPERIOD

Fonctions permettant de faire des calculs sur les dates

Exemple


TIME INTELLIGENCE EN DAXFonctions évaluant une expressionSur une période temporelle

• TOTALMTD• TOTALQTD• TOTALYTD

Ces fonctions s’utilisent directement avec des mesures

Exemple


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


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


Sponsors Platinum

4. CHOISIR LE MDX POUR CERTAINES PROBLEMATIQUES


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• …


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 …


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


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


Sponsors Platinum

DEMO : POINTS FORTS DE L’OLAP

- Conversion de devises N-N- Calculs hiérarchiques- SCOPE Assignments


Sponsors Platinum

5. ET LES PERFORMANCES DES REQUETES?


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 ?


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


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


Sponsors Platinum

CONCLUSION


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


• 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



• 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


Merci à nos SponsorsRencontrez les dans l’espace partenaires

Sponsors Platinum

Sponsors Gold

Sponsors Silver


Continuez l’expérience onlineRejoignez la Communauté


Rejoignez la Communauté

Data & Analytics

Journées SQL Server 2012 - DAX pour les fans de MDX