Cette publication ne fait pas partie de la bibliothèque YouScribe
Elle est disponible uniquement à l'achat (la librairie de YouScribe)
Achetez pour : 19,90 € Lire un extrait

Lecture en ligne + Téléchargement

Format(s) : PDF

sans DRM

Partagez cette publication

Sommaire
Table des matières I : Introduction et vue d’ensemble................................................................................14Que signifie MDX ?.........................................................................................................................................15 Pourquoi utiliser le langage MDX ?.................................................................................................................15 Notions fondamentales..................................................................................................................................15 Mesure ......................................................................................................................................................15 Dimension..................................................................................................................................................16 Membre de dimension ...............................................................................................................................17 Hiérarchies d’attributs...............................................................................................................................18 Hiérarchies utilisateur ................................................................................................................................19 Notation des membres de dimension.........................................................................................................19 Notations abrégées : ..................................................................................................................................21 Tuple..........................................................................................................................................................21 Notion de tuple valide................................................................................................................................22 Tuple par plage de membres : ....................................................................................................................23 Tuples par fonctions ...................................................................................................................................24 Tuples de tuples : .......................................................................................................................................24 Set .............................................................................................................................................................24 Manipulations de base ...................................................................................................................................26 Syntaxe de base d’une requête MDX........................................................7...2............................................... ère Notre 1 requête MDX..............................................................................................................................27 Une requête à oublier : ..............................................................................................................................28 Restrictions par slicing................................................................................................................................28 Détail des données en lignes ......................................................................................................................29 Observation de la symétrie des requêtes....................................................................................................30 Obtention de sets de mesure .....................................................................................................................30 Limites de la clause Where.........................................................................................................................31 Combinaison de membres sur un des axes .................................................................................................32 Produit cartésien........................................................................................................................................32 Eliminer les NULL lors des Cross join...........................................................................................................33 Utilisation de NonEmpty en ligne et en colonnes........................................................................................35 Pratique 1 ..................................................................................................................................................38 Pratique 1 : les solutions ............................................................................................................................40 SQL Server 2008R2-20125: Pratique du langage MDX par Dominique Verrière
Sommaire
II : Navigation dans les hiérarchies ................................................................................43Types de fonction de navigation :...................................................................................................................44 Fonctions remontant dans les hiérarchies ......................................................................................................45 Parent ........................................................................................................................................................46 Ancestor ....................................................................................................................................................47 Ancestors ...................................................................................................................................................48 Ascendants : ..............................................................................................................................................49 Fonctionsaccédant à un niveau d’une hiérarchie........0..5.................................................................................CurrentMember .........................................................................................................................................51 Prevmember ..............................................................................................................................................51 NextMember .............................................................................................................................................52 Siblings.......................................................................................................................................................52FirstSibling .................................................................................................................................................53 LastSibling..................................................................................................................................................53 Lag .............................................................................................................................................................54 Lead ...........................................................................................................................................................55 Cousin........................................................................................................................................................55 Levels.........................................................................................................................................................56 Fonctions descendant dans une hiérarchie donnée ........................................................................................57 Children .....................................................................................................................................................57 Members ...................................................................................................................................................58 All members...............................................................................................................................................59 Descendants ..............................................................................................................................................60 FirstChild....................................................................................................................................................62 LastChild ....................................................................................................................................................63 LinkMember...............................................................................................................................................63Combinaison de fonctions ..............................................................................................................................65 Comment obtenir une fratrie d’un ancêtre commun? ...............................................................................65 Obtenir le niveau dans une hiérachie .........................................................................................................65 Navigation par montée et descente ...............................................................................................................66 DrillDownLevel...........................................................................................................................................66 DrillDownLevelBottom ...............................................................................................................................69 DrillDownLevelTop .....................................................................................................................................71
SQL Server 2008R2-2012: Pratique du langage MDX par Dominique Verrière
6
Sommaire
DrillUpLevel ...............................................................................................................................................72 DrillDownMember .....................................................................................................................................73 DrillDownMemberBottom..........................................................................................................................74DrillDownMemberTop ...............................................................................................................................76 DrillUpMember ..........................................................................................................................................77 Pratique 2 : ................................................................................................................................................80 Pratique 2 Les solutions .............................................................................................................................83 III : Membres calculés et sets ........................................................................................88Les membres calculés ....................................................................................................................................89 Pourquoi des membres calculés ? ..............................................................................................................89 Syntaxe générique de la clause WITH .........................................................................................................89 Membres calculés dans des ordres select ...................................................................................................90 Les sets nommés............................................................................................................................................94 Pourquoi des sets nommés ?......................................................................................................................94 Définition de set nommé dans la clause WITH ............................................................................................94 Combinaison de membres calculés et sets nommés ...................................................................................95 Intégration dans le cube.................................................................................................................................95 Les membres calculés.................................................................................................................................95 Les sets nommés : ......................................................................................................................................96 Pratique 3 ..................................................................................................................................................98 Pratique 3 Les solutions ...........................................................................................................................100 IV : Contrôler le flux de programme ............................................................................104Introduction.................................................................................................................................................105Tester les valeurs avec iif .............................................................................................................................105 Gérer les cas multiples avec l’instruction case.................................................01....6......................................... Syntaxe du case simple :...........................................................................................................................106 Syntaxe du case recherché .......................................................................................................................106 Vérifier qu’une expression est vide..............................................................................................................107 Ecarter les valeurs NULL au moyen du test IsEmpty :....................................................................................108 Signaler des erreurs .....................................................................................................................................109 Conclusion ...................................................................................................................................................111 Pratique 4 ................................................................................................................................................112 Pratique 4 Les solutions ...........................................................................................................................114
SQL Server 2008R2-2012: Pratique du langage MDX par Dominique Verrière
7
Sommaire
VFiltrerlesdonnées....................................................................................................117Possibilités et limites de la clause where ......................................................................................................118 Critère de filtrage simple : ........................................................................................................................118 Critère de filtrage multiple .......................................................................................................................119 Limites de la clause where :......................................................................................................................120 Sous-requête et sous-cube...........................................................................................................................121
Sous requête............................................................................................................................................122 Utilisation d’un sous-cube : ......................................................................................................................123 Filter ............................................................................................................................................................125 Filtrer sur des valeurs de mesure..............................................................................................................125 Filtrer sur des valeurs de membre de dimension ......................................................................................127 Fonctions en VBA .........................................................................................................................................128 Récupérer la date et heure du système : ..................................................................................................128 Fonctions disponibles pour le type date/heure.........................................................................................129 Fonctions disponibles pour le type chaine de caractères ..........................................................................129 Fonctions disponibles pour le type numérique .........................................................................................130 Fonctions de conversion...........................................................................................................................130 Exemples d’appel de fonctions.................................310................................................................................ Autres fonctions système : ...........................................................................................................................131 Approche ensembliste .................................................................................................................................132 Exists........................................................................................................................................................132 Distinct ....................................................................................................................................................136 Except ......................................................................................................................................................137 Intersect ..................................................................................................................................................138 Union.......................................................................................................................................................139 Generate..................................................................................................................................................140 Pratique 5 ................................................................................................................................................144 Pratique 5 Les solutions ...........................................................................................................................147 VI Ordonner les résultats............................................................................................. 152Order ...........................................................................................................................................................153 Utiliser le tri par défaut de hierarchize .........................................................................................................155 Extraire des parties de sets triés...................................................................................................................157 Topcount .................................................................................................................................................157 SQL Server 2008R2-2012: Pratique du langage MDX par Dominique Verrière 8
Sommaire
Toppercent ..............................................................................................................................................158 Topsum....................................................................................................................................................159 Bottomcount............................................................................................................................................159Bottompercent ........................................................................................................................................161 Bottomsum ..............................................................................................................................................162 Head ........................................................................................................................................................163 Tail...........................................................................................................................................................164 Subset......................................................................................................................................................165 Pratique 6 ................................................................................................................................................168 Pratique 6 Les solutions :..........................................................................................................................171 VII Fonctions d’agrégation...............................671............................................................Introduction.................................................................................................................................................177Count...........................................................................................................................................................177 Limiter le comptage au contexte ..................................................................................................................180 Autres possibilités de comptage...................................................................................................................180 Distinctcount ...............................................................................................................................................183 Sum .............................................................................................................................................................185 Minimums et maximums .............................................................................................................................186 Min ..........................................................................................................................................................186 Max .........................................................................................................................................................188 Avg ..............................................................................................................................................................188 Median ........................................................................................................................................................189 Rank ............................................................................................................................................................190 Aggregate ....................................................................................................................................................193 Autres fonctions d’agrégation: ....................................................................................................................193 Pratique 7 ................................................................................................................................................195 Pratique 7 Les solutions ...........................................................................................................................197 VIII Utiliser les dimensions de temps ..........................................................................201Introduction.................................................................................................................................................202Parallelperiod ..............................................................................................................................................204 Cousin..........................................................................................................................................................207 Début et fin de période ................................................................................................................................208 OpeningPeriod .........................................................................................................................................208
SQL Server 2008R2-2012: Pratique du langage MDX par Dominique Verrière
9
Sommaire
ClosingPeriod ...........................................................................................................................................209 PeriodsToDate .............................................................................................................................................210 LastPeriods ..................................................................................................................................................212 Périodes de la fratrie....................................................................................................................................214 Wtd..........................................................................................................................................................214 Mtd..........................................................................................................................................................214 Qtd ..........................................................................................................................................................215 Ytd ...........................................................................................................................................................216 Pratique 8 ................................................................................................................................................219 Pratique 8 Les solutions ...........................................................................................................................221 IX Accès aux méta données .........................................................................................224Accès aux propriétés ....................................................................................................................................225 Accès aux propriétés dans le contexte ......................................................................................................226 Autres utilisations de name : ....................................................................................................................228 Autres utilisations de uniquename : .........................................................................................................228 Accès aux propriétés à l’aide de properties..............................................................................................228 Niveaux d’une hiérarchie.............................................................................................................................231 Conversion de/vers du texte ........................................................................................................................231 Conversion depuis du texte..........................................................................................................................232 StrToMember...........................................................................................................................................232StrToTuple ...............................................................................................................................................233 StrToSet ...................................................................................................................................................234 Conversion vers du texte..............................................................................................................................236 MemberToStr...........................................................................................................................................236TupleToStr ...............................................................................................................................................236 SetToStr ...................................................................................................................................................237 Generate..................................................................................................................................................238 Exemple de Reporting Services.................................................................................................................239 Pratique 9 ................................................................................................................................................241 Pratique 9 Les solutions ...........................................................................................................................242 X Mise en pratique ......................................................................................................244Le cube à utiliser pour les pratiques .............................................................................................................245 Contenu du projet des pratiques : ............................................................................................................246 SQL Server 2008R2-201210: Pratique du langage MDX par Dominique Verrière
Sommaire
Création de KPI Indicateurs de Performances Clés........................................................................................249 Qu’est-ce qu’un KPI? ...............................................................................................................................249 Méthode de mise au point de KPI :...........................................................................................................250 Pratique 10 ..............................................................................................................................................254 Pratique 10 Les solutions .........................................................................................................................255 Utiliser MDX dans Excel................................................................................................................................257 MEMBRECUBE .........................................................................................................................................261 VALEURCUBE ...........................................................................................................................................262 JEUCUBE ..................................................................................................................................................262 NBJEUCUBE..............................................................................................................................................263 RANGMEMBRECUBE ................................................................................................................................264 PROPRIETEMEMBRECUBE ........................................................................................................................264 MEMBREKPICUBE ....................................................................................................................................265 Conclusion : .............................................................................................................................................265 Utiliser MDX dans Reporting Services...........................................................................................................266
Mettre en place une sécurité dynamique .....................................................................................................273 Données préparatoires.............................................................................................................................273 Validation de cette mise en place : ...........................................................................................................276 Conclusion : .............................................................................................................................................280 Mettre à jour directement le cube avec MDX ...............................................................................................281 Mise à jour par update .............................................................................................................................281 Avantages inconvénients du write back....................................................................................................282 Conclusion : .............................................................................................................................................283 Contacter l’auteur: ......................................................................................................................................284 Présentation de cet ouvrage ........................................................................................................................285
SQL Server 2008R2-2012: Pratique du langage MDX par Dominique Verrière
11
Introduction et vue d’ensemble
Chapitre 1
I :Introduction et vue d’ensemble
Nous allons introduire ici les concepts fondamentaux…il faut comprendre ce chapitre avant de passer à la suite.
En effet, les notions fondamentales detuples,membresetsetsétant utilisées en permanence, il serait illusoire de vouloir poser des requêtes sans avoir intégré ces notions.
SQL Server 2008R2-2012: Pratique du langage MDX par Dominique Verrière
14
Introduction et vue d’ensemble
Que signifie MDX ? MDXveut direMulti Dimensionnal Expressions: c’est l’un des 3 langages disponibles avec les cubesd’AnalysisServices.
Il ne faut pas le confondre avec leDMX(Data Mining Expressions) qui sert à l’analyse des données à des fins de prospections : le Data Mining.
Le dernier langage utilisé est leXMLAqui sert plutôt àdes tâches d’administration; on voit donc que, par rapport au SQL, les parties du langage sont plus séparées par rôle.
Pourquoi utiliser le langage MDX ?
Pour définir descalculs embarqués:
Desmembrescalculés Dessetsnommés Des scripts dynamiques
Jeconsidère qu’il est de bonne pratique d’embarquer des calculs dans lecubede la même manière qu’il est bon d’utiliser des vues dans une base relationnelle: cela permet une certaine indépendance par rapport à des règles de gestion qui sont cachées pour la plupart des utilisateurs.
Pour créer desIndicateurs de performances clés(KeyPerformanceIndicator) : même remarque au sujet de l’encapsulation que l’onpeut en obtenir : il est préférable que certaines règles sensibles ne soient pas à portée des utilisateurs.
Pour utiliserReporting Servicesau niveau avancé : en effet un générateur MDX est à votre disposition, mais il ne vous permettra de faire que des choses assez basiques lors de recherches.
Afin de créer des tableaux de bord personnalisés dansExcel: de nouveau, l’assistant tableaucroisé dynamique ne vous permettra pas de faire des choses évoluées et vous devrez donc ‘y passer’ à un moment ou un autre…
Notions fondamentales Un cube est constitué de mesures et de dimensions.
Mesure
Lesmesuressont des valeurs numériques (dans 99 % des cas) sur lesquelles on cherche à faire desagrégats; c’est là tout l’intérêt d’un cube: je vois souvent des projets BI échouer car les développeurs veulent utiliser le cube sans faire des agrégats.
Parmi les agrégats classiques, citons : la somme (sum), le maximum (max), le comptage (count) et le comptage sans doublons (count distinct). SQL Server 2008R2-2012: Pratique du langage MDX par Dominique Verrière 15
Introduction et vue d’ensemble
Nous pouvons ici donner un exemple de cube avec ses mesures :
Une société de négoce souhaite suivre son activité ; elle identifiera donc les mesures suivantes :
Montant des ventes par revendeur Montant des remises accordées aux revendeurs
Voici un exemple réel de mesures :
Ces mesures sont organisées pargroupe de mesure(en pratique, un groupe de mesures est associé à une table de la source de données).
Les mesures disponibles dans le groupe‘Ventes revendeur’sont :
Quantité Commandée Revendeur Prix Unitaire Revendeur Etc. En pratique, ces mesures n’auront de sens que par rapport à des axes d’analyse: ces mesures pourront s’agréger selon différents axes,c’est ce que l’on appelle lesdimensions.
Dimension
Lesdimensionspermettent de qualifier les valeurs des mesures à des fins d’analyses.
Dans cet exemple, on pourrait utiliser :
Les produits Les revendeurs La date de la vente Le commercial qui a permis la vente
Les dimensions sont constituées d’attributs:
L’un d’eux a un rôle important: c’est l’attribut clé de la dimensionTous les autres attributsdépendent implicitement de l’attribut cléoMais on peut faire des dépendances indirectes (Ex Produit -> Sous-catégorie -> Catégorie)
SQL Server 2008R2-2012: Pratique du langage MDX par Dominique Verrière
16
Un pour Un
Permettre à tous d'accéder à la lecture
Pour chaque accès à la bibliothèque, YouScribe donne un accès à une personne dans le besoin