DAX pour les modèles multidimensionnels
S’applique à :
SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium
Cet article décrit comment Power BI utilise des requêtes DAX (Data Analysis Expressions) pour créer des rapports sur des modèles multidimensionnels dans SQL Server Analysis Services.
Historiquement, les applications de création de rapports utilisent MDX (expressions multidimensionnelles) comme langage de requête sur des bases de données multidimensionnelles. MDX est optimisé pour les modèles visuels courants tels que les tableaux croisés dynamiques dans Excel et d’autres applications de création de rapports qui ciblent la sémantique métier multidimensionnelle. À compter de SQL Server 2012 SP1, Analysis Services prend en charge l’utilisation de DAX et MDX sur des modèles multidimensionnels et tabulaires. Toutefois, DAX a été initialement conçu pour les modèles de données tabulaires. Bien que DAX soit considéré comme plus facile à utiliser, il est également plus axé sur des visualisations de données plus simples comme les tableaux, les graphiques et les cartes dans les rapports et les tableaux de bord. Power BI utilise DAX pour interroger les modèles tabulaires et multidimensionnels.
Étant donné que DAX est principalement conçu pour les modèles tabulaires, il existe des mappages et des contraintes intéressants et utiles qui doivent être compris lors de l’utilisation de DAX sur des modèles multidimensionnels.
Compatibilité
Power BI utilise DAX pour interroger les modèles multidimensionnels Analysis Services dans SQL Server éditions Enterprise ou Standard 2016 et ultérieures. SQL Server éditions 2012 et SQL Server 2014 Enterprise ou Business Intelligence sont également prises en charge. Toutefois, ces versions ne sont plus prises en charge.
Fonctionnalités
DAX n’est pas un sous-ensemble de MDX. DAX a été initialement conçu pour être similaire au langage de formule Excel. Dans les modèles tabulaires, DAX est utilisé par rapport à un magasin de données relationnelles composé de tables et de relations. DAX est également utilisé pour créer des mesures personnalisées, des colonnes calculées et des règles de sécurité au niveau des lignes.
En plus d’être un langage de calcul, DAX peut également être utilisé pour exécuter des requêtes. Cet article décrit comment les requêtes DAX fonctionnent sur un modèle multidimensionnel.
Interaction entre MDX et DAX
Les expressions DAX sont prises en charge uniquement dans les modèles tabulaires. Vous ne pouvez pas utiliser des mesures créées par une expression DAX dans un modèle multidimensionnel. Une requête DAX vers un modèle multidimensionnel peut référencer une mesure ou un autre calcul défini dans ce modèle, mais ces calculs doivent être créés à l’aide du langage MDX. Les expressions DAX ne peuvent pas être utilisées lorsqu’une expression MDX est requise et vice versa, et que certaines fonctions DAX, telles que PATH, ne sont pas du tout applicables dans la modélisation multidimensionnelle.
Syntaxe DAX
La syntaxe des formules DAX est très similaire à celle des formules Excel et combine fonctions, opérateurs et valeurs. Pour en savoir plus sur la syntaxe des fonctions individuelles, consultez la référence des fonctions DAX.
Mappage d'objets multidimensionnels/tabulaires
Analysis Services fournit une représentation de métadonnées de modèle tabulaire d'un modèle multidimensionnel. Les objets d’un modèle multidimensionnel sont ensuite représentés sous forme d’objets tabulaires dans Power BI. Ce mappage est exposé à Power BI à l’aide de l’ensemble de lignes de schéma DISCOVER_CSDL_METADATA .
Mappage d’objets
| Objet multidimensionnel | Objet tabulaire |
|---|---|
| Cube | Modèle |
| Dimension de cube | Table de charge de travail |
| Attributs de dimension (clé, nom) | Colonne |
| Groupe de mesures | Table de charge de travail |
| Measure | Measure |
| Mesure sans groupe de mesures | Dans un tableau nommé Mesures |
| Relation de dimension de cube de groupe de mesures | Relationship |
| Perspective | Perspective |
| KPI | KPI |
| Hiérarchies parent-enfant/utilisateur | Hierarchy |
| Afficher le dossier | Afficher le dossier |
Mesures, groupes de mesures et indicateurs de performance clés
Les groupes de mesures d’un cube multidimensionnel sont affichés dans la liste Champs Power BI sous forme de tables avec une icône de calculatrice.
Les mesures dans un groupe de mesures apparaissent sous forme de mesures. S’il existe des mesures calculées qui n’ont pas de groupe de mesures associé, elles sont regroupées sous une table spéciale appelée Mesures.
Pour simplifier les modèles dimensionnels plus complexes, les auteurs de modèle peuvent définir un ensemble de mesures ou d'indicateurs de performance clés dans un cube devant se trouver dans un dossier d'affichage. Power BI peut afficher les dossiers d’affichage ainsi que les mesures et les indicateurs de performance clés qu’ils contiennent.
Mesures et indicateurs de performance clés dans un groupe de mesures
Mesures en tant que variantes
Les mesures des modèles multidimensionnels sont des variantes. Cela signifie que les mesures ne sont pas fortement typées et qu'elles peuvent avoir des types de données différents. Par exemple, dans l’image ci-dessous, la mesure Amount dans la table Financial Reporting est par défaut de type de données Devise, mais a également une valeur de chaîne NA pour le sous-total de Comptes statistiques, qui est type de données Chaîne. Power BI reconnaît certaines mesures comme des variantes et affiche les valeurs et la mise en forme correctes dans les différentes visualisations.
Mesure en tant que variante
Mesures implicites
Les modèles tabulaires fournissent aux utilisateurs la possibilité de créer des mesures implicites, telles qu'un compte, une somme ou une moyenne sur des champs. Concernant les modèles multidimensionnels, étant donné que les données d'attribut de dimension sont stockées différemment, l'interrogation de mesures implicites peut durer un certain temps. Pour cette raison, les mesures implicites sur les modèles multidimensionnels ne sont pas disponibles dans Power BI.
Dimensions, attributs et hiérarchies
Les dimensions de cube sont exposées sous forme de tableaux dans les métadonnées tabulaires. Dans la liste Champs Power BI, les attributs de dimension sont affichés sous forme de colonnes dans les dossiers d’affichage. Attributs de dimension dont la propriété AttributeHierarchyEnabled a la valeur False ; par exemple : l’attribut Date de naissance dans la dimension Customer ou la propriété AttributeHierarchyVisible définie sur false n’apparaît pas dans la liste Champs Power BI. Hiérarchies à plusieurs niveaux ou hiérarchies d’utilisateurs ; Par exemple, la géographie du client dans la dimension Client est exposée en tant que hiérarchies dans la liste Champs Power BI. Les UnknownMembers masqués d’un attribut de dimension sont exposés dans les requêtes DAX et dans Power BI.
Dimension, attributs et hiérarchies dans SQL Server Data Tools (SSDT) et la liste des champs Power BI
Type d'attribut de dimension
Les modèles multidimensionnels prennent en charge l'association d'attributs de dimension avec des types d'attributs de dimension spécifiques. L’image ci-dessous montre la dimension Geography où les attributs de dimension City, State-Province, Country et Postal Code ont des types géographiques associés. Ceux-ci sont exposés dans les métadonnées tabulaires. Power BI reconnaît les métadonnées permettant aux utilisateurs de créer des visualisations cartographiques. Cela est indiqué par l’icône de carte en regard des colonnes Ville, Pays, Code postal et State-Province dans la table Geography de la liste des champs Power BI.
Dimension géographique dans la liste des champs SSDT et Power BI
Membres calculés de dimension
Les modèles multidimensionnels prennent en charge les membres calculés pour l’enfant de All avec un seul membre réel. Il existe des contraintes supplémentaires lors de l'exposition de ce type de membre calculé :
- Doit être un membre réel unique lorsque la dimension comporte plus d'un attribut.
- Un attribut contenant des membres calculés ne peut pas être l'attribut clé de la dimension, sauf s'il s'agit du seul attribut.
- Un attribut contenant des membres calculés ne peut pas être un attribut parent-enfant.
Les membres calculés des hiérarchies d’utilisateurs ne sont pas exposés dans Power BI. Toutefois, les utilisateurs peuvent toujours se connecter à un cube contenant des membres calculés sur des hiérarchies utilisateur.
Membres par défaut
Les modèles multidimensionnels prennent en charge les membres par défaut des attributs de dimension. Le membre par défaut est utilisé par Analysis Services lors de l'agrégation de données pour une requête. Le membre par défaut d'un attribut de dimension est exposé en tant que valeur par défaut ou filtre pour la colonne correspondante dans les métadonnées tabulaires.
Power BI se comporte de la même façon que les tableaux croisés dynamiques Excel lorsque des attributs sont appliqués. Lorsqu’un utilisateur ajoute une colonne à une visualisation Power BI (table, matrice ou graphique) qui contient une valeur par défaut, la valeur par défaut n’est pas appliquée et toutes les valeurs disponibles s’affichent. Si l’utilisateur ajoute la colonne à Filtres, la valeur par défaut est appliquée.
Sécurité des dimensions
Les modèles multidimensionnels prennent en charge la sécurité au niveau de la cellule et de la dimension au moyen de rôles. Un utilisateur qui se connecte à un cube à l’aide de Power BI est authentifié et évalué pour les autorisations appropriées définies par les rôles auxquels l’utilisateur appartient. Lorsque la sécurité de dimension est appliquée, les membres de dimension respectifs ne sont pas vus par l’utilisateur dans Power BI. Toutefois, si un utilisateur dispose d’une autorisation de sécurité de cellule définie lorsque certaines cellules sont restreintes, cet utilisateur ne peut pas se connecter au cube avec Power BI. Dans certains cas, les utilisateurs peuvent voir des données agrégées lorsque des portions de ces données sont calculées à partir de données sécurisées.
Hiérarchies/Attributs ne pouvant faire l'objet d'une agrégation
Dans les modèles multidimensionnels, la propriété IsAggregatable peut avoir la valeur False pour les attributs d’une dimension. Cela signifie que l’auteur du modèle a spécifié que les applications de création de rapports ne doivent pas agréger les données entre des hiérarchies (attribut ou à plusieurs niveaux) lorsqu’elles interrogent les données. Dans Power BI, cet attribut de dimension est exposé sous la forme d’une colonne pour laquelle les sous-totaux ne sont pas disponibles. Dans l’image suivante, vous voyez un exemple de hiérarchie non agrégable, Comptes. Le niveau le plus élevé de la hiérarchie parent-enfant Accounts ne peut pas faire l'objet d'une agrégation, contrairement aux autres niveaux. Dans une visualisation matricielle de la hiérarchie des comptes (deux premiers niveaux), vous voyez des sous-totaux pour le niveau de compte 02 , mais pas pour le niveau le plus élevé, niveau de compte 01.
Hiérarchie non agrégable dans Power BI
Images
Power BI offre la possibilité de restituer des images. Dans les modèles multidimensionnels, l’une des façons de fournir des images à afficher dans Power BI consiste à exposer des colonnes contenant des URL (Uniform Resource Locator) des images. Analysis Services prend en charge l’étiquetage des attributs de dimension en tant que type ImageURL. Ce type de données est ensuite fourni à Power BI dans les métadonnées tabulaires. Power BI peut ensuite télécharger et afficher les images spécifiées dans les URL dans les visualisations.
Type d'attribut de dimension ImageURL dans SSDT
Hiérarchies parent-enfant
Les modèles multidimensionnels prennent en charge les hiérarchies parent-enfant, qui sont exposées sous forme de hiérarchie dans les métadonnées tabulaires. Chaque niveau de la hiérarchie parent-enfant est exposé en tant que colonne masquée. L'attribut de clé de la dimension parent-enfant n'est pas exposé dans les métadonnées tabulaires.
Hiérarchies parent-enfant dans la liste des champs SSDT et Power BI
Perspectives et traductions
Les perspectives sont des vues de cubes dans lesquelles seules certaines dimensions ou seuls certains groupes de mesures sont visibles dans les outils clients. Vous pouvez spécifier un nom de perspective comme valeur à la propriété Cube chaîne de connexion. Par exemple, dans le chaîne de connexion suivant, « Direct Sales » est une perspective dans le modèle multidimensionnel :
Data Source=localhost;Initial Catalog=AdventureWorksDW-MD;Cube='Direct Sales'
Les cubes peuvent avoir des métadonnées et des traductions de données spécifiées pour différentes langues au sein du modèle. Pour afficher les traductions (données et métadonnées), une application peut ajouter la propriété Identificateur de paramètres régionaux facultative au chaîne de connexion, par exemple :
Data Source=localhost;Initial Catalog=AdventureWorksDW-MD;Cube='Adventure Works'; Locale Identifier=3084
Quand Power BI Desktop se connecte à un modèle multidimensionnel, il transmet automatiquement les paramètres régionaux utilisateur actuels identifiés au serveur. Toutefois, cela ne se produit pas pour un rapport publié sur le service Power BI.
Fonctionnalités non prises en charge
Sécurité au niveau de la cellule : n’est pas prise en charge dans les rapports Power BI.
Actions : ne sont pas prises en charge dans les rapports Power BI ou dans les requêtes DAX sur un modèle multidimensionnel.
Les jeux nommés : dans les modèles multidimensionnels, ne sont pas pris en charge dans Power BI ou dans les requêtes DAX par rapport à un modèle multidimensionnel.
Notes
Les actions et les jeux nommés non pris en charge n’empêchent pas les utilisateurs de se connecter à des modèles multidimensionnels et d’explorer ces modèles lors de l’utilisation de Power BI.
Annotations CSDLBI
Les métadonnées de cube multidimensionnelles sont exposées en tant que modèle conceptuel basé sur EDM (Entity Data Model) par les annotations CSDLBI (Conceptual Schema Definition Language avec Business Intelligence).
Les métadonnées multidimensionnelles sont représentées en tant qu'espace de noms de modèle tabulaire dans un document CSDLBI, ou CSDL en sortie, lorsqu'une requête DISCOVER_CSDL_METADATA est envoyée à l'instance Analysis Services.
Exemple : DISCOVER_CSDL_METADATA demande
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Body>
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>DISCOVER_CSDL_METADATA</RequestType>
<Restrictions>
<RestrictionList>
<CATALOG_NAME>"catalogname"<CATALOG_NAME>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
</PropertyList>
</Properties>
</Discover>
</Body>
</Envelope>
La demande DISCOVER_CSDL_METADATA présente les restrictions suivantes :
| Nom | Obligatoire | Description |
|---|---|---|
| CATALOG_NAME | Yes | Nom du catalogue\base de données. |
| PERSPECTIVE_NAME | Oui, si le cube contient plusieurs perspectives. Facultatif en présence d'un seul cube ou s'il existe une perspective par défaut. | Nom du cube ou de la perspective dans la base de données multidimensionnelle. |
| VERSION | Yes | Version CSDL demandée par le client. Les constructions et fonctionnalités multidimensionnelles sont prises en charge dans la version 2.0. |
Le document CSDL en sortie retourné représente le modèle en tant qu'espace de noms qui contient des entités, des associations et des propriétés.
Pour en savoir plus sur les annotations CSDLBI, consultez Informations de référence techniques sur les annotations BI dans CSDL et [MS-CSDLBI] : Format de fichier définitions de schéma conceptuel avec annotations Business Intelligence.
SuperDAXMD
À chaque version de SQL Server Analysis Services, les améliorations prennent en charge les fonctions et fonctionnalités DAX nouvelles et existantes. Dans SQL Server CU5 2019, une classe de fonctions DAX introduites pour la première fois pour les modèles tabulaires appelés « SuperDAX » est désormais activée pour les modèles multidimensionnels.
Bien que certains modèles de requête DAX existants devront être repensés, les fonctions SuperDAX apportent des améliorations significatives aux performances des requêtes. Les modèles de requête DAX modernes utilisant SuperDAX pour les modèles multidimensionnels offrent une forte incitation pour les organisations qui utilisent Power BI à mettre à niveau leurs serveurs de source de données multidimensionnels vers SQL Server 2019 avec CU5. Pour plus d’informations, consultez SuperDAX pour les modèles multidimensionnels.