Support formation Architecture Power BI
Pages
Modes de connexion
DirectQuery dans SQL Server 2016 Analysis Services
Traduction automatique du depuis la page
Date de publication : janvier 2017
S'applique à : Microsoft SQL Server 2016 Analysis Services, Tabulaire
Résumé : DirectQuery transforme le modèle tabulaire Microsoft SQL Server Analysis Services en une couche de métadonnées au-dessus d'une base de données externe. Pour SQL Server 2016, DirectQuery a été repensé pour améliorer considérablement la vitesse et les performances, mais il est également plus complexe à comprendre et à mettre en œuvre. Il existe de nombreux compromis à prendre en compte pour décider quand utiliser DirectQuery par rapport au mode en mémoire (VertiPaq). Envisagez d'utiliser DirectQuery si vous disposez d'une petite base de données qui est mise à jour fréquemment ou d'une base de données volumineuse qui ne tient pas en mémoire.
DirectQuery est une technologie introduite dans les modèles tabulaires Microsoft SQL Server Analysis Services 2012 (SSAS). Il transforme le modèle tabulaire en une couche de métadonnées au-dessus d'une base de données externe. En effet, en utilisant DirectQuery, le moteur tabulaire ne traite plus les données de sa propre base de données interne. Au lieu de cela, la requête est transformée en un ensemble de requêtes SQL qui sont envoyées à la base de données relationnelle sous-jacente. Une fois que la base de données SQL renvoie les données demandées, le moteur tabulaire traite ces données. Cela vous permet de créer des modèles de données en temps réel, car vous n'avez plus besoin de mettre à jour les structures de données internes. Chaque requête est exécutée sur la base de données relationnelle, et les données sont traitées dès qu'elles deviennent disponibles à partir de la requête SQL. À l'aide d'interfaces de cartouche, le moteur tabulaire peut se connecter à divers moteurs de base de données, notamment Microsoft SQL Server, Oracle, Teradata, etc.
Dans l'implémentation DirectQuery 2012/2014, le moteur tabulaire générait une seule requête SQL à partir de n'importe quelle
Requête DAX. Il en résultait un code SQL détaillé trop complexe pour la plupart des scénarios réels. Ainsi
DirectQuery n'était pas largement utilisé dans les versions précédentes de SSAS. En 2016, Microsoft a remanié la technologie DirectQuery. La mise en œuvre qui en résulte est beaucoup plus avancée et, par conséquent, à la fois plus rapide et plus complexe à comprendre.
Avant d'utiliser DirectQuery pour transformer votre modèle tabulaire SSAS 2016 en modèle en temps réel, vous devez d'abord comprendre le fonctionnement de DirectQuery. Il est important de comprendre les détails de la mise en œuvre ainsi que les avantages et les inconvénients de l'utilisation de la technologie.
Ce livre blanc vous fournit toutes les informations pertinentes nécessaires pour comprendre et utiliser DirectQuery dans votre environnement. Nous vous encourageons vivement à le lire d'un bout à l'autre avant de commencer votre mise en œuvre. DirectQuery s'accompagne de certaines limitations dans les options de modélisation et DAX qui affecteront la façon dont vous créez le modèle de données lui-même. DirectQuery nécessite un modèle de données différent de celui des modèles tabulaires standard qui importent des données à partir d'une base de données en mémoire.
Remarque : bien que le moteur tabulaire soit disponible dans l'édition Standard de SQL Server 2016, DirectQuery est une fonctionnalité avancée disponible uniquement dans l'édition Entreprise.
Avant de discuter des différences entre DirectQuery et un modèle tabulaire standard, il est important de comprendre comment fonctionnent le traitement et l'interrogation tabulaires. Ce document suppose que vous êtes familier avec les modèles tabulaires, mais donne un bref récapitulatif du traitement et de l'interrogation tabulaires. Pour plus d'informations sur les modèles tabulaires, consultez la section des ressources à la fin de ce document.
Une base de données tabulaire SQL Server Analysis Services (SSAS) est un modèle sémantique qui conserve généralement une copie des données dans une base de données en colonnes en mémoire, qui lit les données des sources de données, les traite dans sa structure de données interne et enfin répond aux requêtes en lisant son modèle de données interne. En utilisant
DirectQuery, la base de données tabulaire SSAS se comporte comme un modèle sémantique qui traduit les requêtes entrantes vers la source de données, sans conserver une copie des données dans une base de données en mémoire.
La base de données en colonnes utilisée par SSAS est un moteur en mémoire (VertiPaq). Les sources de données sont généralement des bases de données relationnelles, mais en raison des nombreuses sources de données différentes disponibles pour SSAS, vous pouvez charger des données dans SSAS à partir de pratiquement n'importe quelle source de données, y compris des fichiers texte, des services Web ou des classeurs Excel. Vous pouvez utiliser et mélanger n'importe quelle source de données disponible en raison de la phase de traitement intermédiaire.
En règle générale, vous créez une solution tabulaire SSAS de l'une des deux manières suivantes :
• Alimentez la solution SSAS à partir de l'entrepôt de données qui contient toutes les données pertinentes pour votre entreprise, qui est déjà préparée pour l'analyse. Dans ce cas, vous disposez généralement d'une seule source de données, et cette source de données est une base de données relationnelle (il peut s'agir de Microsoft SQL Server ou de toute base de données relationnelle prise en charge).
• Alimentez la solution SSAS à partir de plusieurs sources de données (et probablement de différents types de données) et utilisez SSAS pour intégrer des données provenant de différentes bases de données. Dans ce cas, vous disposez généralement de bases de données relationnelles, peut-être plusieurs, et d'autres sources de données telles que des fichiers texte, des fichiers Excel ou d'autres sources de données.
Dans les deux cas, les données sont lues à partir de la base de données source et transformées en une base de données en mémoire, hautement optimisée pour les requêtes et compressée pour utiliser moins de mémoire. N'oubliez pas que le moteur SSAS est la base de données en colonnes en mémoire qui stocke et héberge votre modèle BI, et que la compression des données est importante. Les données sont initialement enregistrées sur le disque et chargées lors du premier accès à la base de données après le redémarrage d'un service SSAS. Après cela, toutes les requêtes sont exécutées dans la RAM, sauf si la pagination est active, ce qui n'est pas recommandé pour un moteur en mémoire. Une fois les données traitées et stockées en mémoire, vous n'avez plus besoin de vous connecter à la ou aux bases de données sources.
La phase de traitement présente les avantages suivants :
• Les données sont compressées et stockées dans un format qui accélère considérablement les requêtes.
• Les données peuvent provenir de différentes sources de données et être transformées dans un format unique.
• Le moteur en mémoire contient plusieurs optimisations pour l'accès à la mémoire, car toutes les données sont stockées dans la RAM.
La phase de traitement présente les inconvénients suivants :
• Le traitement prend du temps, les requêtes en temps réel ne sont donc pas une option. En mettant en œuvre des techniques sophistiquées, il est possible de créer des modèles en temps quasi réel avec des temps de latence de l'ordre de quelques minutes. Toutefois, il n'existe aucun moyen de s'assurer que la requête exécutée par SSAS Tabulaire fait référence aux dernières modifications apportées à la source de données d'origine.
• Le traitement nécessite beaucoup de puissance CPU. Pendant le traitement des données, le serveur est occupé et ne dispose généralement pas des ressources nécessaires pour répondre efficacement aux requêtes.
• Comme il s'agit d'une base de données en mémoire, si la base de données sur laquelle vous travaillez ne tient pas en mémoire, vous devrez acheter plus de RAM (la meilleure option) ou optimiser l'utilisation de la mémoire du modèle de données, ce qui est une tâche complexe.
• Les données doivent être déplacées de la base de données source vers le stockage SSAS. Lorsqu'il s'agit de traiter de grandes quantités de données, le simple fait de déplacer les données sur le réseau peut prendre beaucoup de temps.
Lors de l'utilisation de DirectQuery, les avantages deviennent des inconvénients et vice versa. En fait, si vous créez un modèle qui est activé pour utiliser DirectQuery, votre modèle ne disposera pas de stockage en mémoire (VertiPaq) et n'aura pas besoin de traiter des données. Ainsi, les données sont toujours en temps réel : il n'y a pas de temps de traitement, pas de limitation de mémoire et il n'est pas nécessaire de déplacer les données de la base de données source vers la base de données SSAS. D'autre part, vous perdez l'énorme vitesse du moteur en mémoire. Vous ne pourrez pas intégrer de données provenant de différentes sources de données, et l'effort de réponse aux requêtes sera déplacé du SSAS vers le moteur de base de données hébergeant les informations.
Il n'y a pas de règle d'or pour vous dire si DirectQuery est meilleur pour votre modèle que le stockage en mémoire standard. Vous devrez soigneusement peser les avantages et les inconvénients et, une fois que vous avez décidé, travailler à l'optimisation de votre modèle, qui dépend de la technologie que vous avez utilisée.
Modèle en mémoire (VertiPaq)
Pendant le traitement, le moteur SSAS exécute une instruction SELECT sur l'ensemble de la table, en lisant toutes les lignes et en effectuant ses propres étapes de traitement. Cela signifie que vous devez optimiser votre source de données pour une énorme analyse unique de la table. Les index sont inutiles et le partitionnement, le cas échéant, doit être aligné sur les partitions définies dans votre solution SSAS.
Modèle DirectQuery
Si les mêmes données doivent être utilisées par DirectQuery, votre table sera consultée au moins une fois pour chaque requête et, sur l'ensemble de la table, seul un petit sous-ensemble de celle-ci peut être nécessaire pour la requête. Dans ce cas, vous devez optimiser le modèle SQL pour répondre rapidement aux requêtes générées par DirectQuery. Cela inclut la création des index corrects sur la table et probablement son partitionnement pour réduire l'activité d'E/S lors de l'exécution de la requête.
Quel est l'impact du modèle sur les décisions ?
Si vous prévoyez d'utiliser un moteur en mémoire, alors un index columnstore sur une table stockée dans
Microsoft SQL Server est loin d'être idéal. Si vous envisagez d'utiliser DirectQuery, le même index columnstore est une option indispensable. Comme vous le voyez, il est important de concevoir votre base de données en fonction de votre cas d'utilisation.
L'exemple précédent a montré le type de décisions que vous devrez prendre lors de l'implémentation de DirectQuery dans votre solution. Dans la section suivante, nous allons explorer plus en détail les différences subtiles entre les modèles DirectQuery et en mémoire.
Maintenant que vous avez vu comment les modèles DirectQuery et en mémoire (VertiPaq) traitent les données, nous allons voir comment ils gèrent les requêtes.
Chaque requête envoyée à un modèle tabulaire est exécutée par deux couches de calcul, appelées moteur de stockage (SE) et moteur de formule (FE). Le moteur de stockage est chargé d'extraire des données de la base de données, tandis que le moteur de formules utilise les informations renvoyées par le moteur de stockage et effectue des calculs plus avancés. Par exemple, si vous souhaitez récupérer les trois premiers produits par montant des ventes, SE accède à la base de données source et calcule la liste de tous les produits ainsi que le montant des ventes pour chaque produit, tandis que FE trie l'ensemble de données résultant et récupère les trois premiers produits. Ainsi, SE lit les données de la base de données source tandis que FE lit les données de SE.
Pour aller plus loin, Analysis Services analyse les requêtes DAX et MDX, puis les transforme en plans de requête exécutés par le moteur de formules. Le moteur de formules est capable d'exécuter n'importe quelle fonction et opération pour les deux langues. Afin de récupérer les données brutes et d'effectuer des calculs, le moteur de formules effectue plusieurs appels au moteur de stockage. Dans SSAS 2016, le moteur de stockage peut être le choix entre le moteur d'analyse en mémoire (VertiPaq) et la base de données relationnelle externe (DirectQuery). Vous choisissez lequel des deux moteurs de stockage utiliser au niveau du modèle de données. Cela signifie qu'un modèle de données ne peut utiliser qu'un seul des deux moteurs, mais pas les deux dans le même modèle.
Comme vous le voyez à la figure 1, la base de données VertiPaq contient une copie en cache des données qui ont été lues à partir de la source de données lors de la dernière actualisation du modèle de données. En revanche, DirectQuery transfère les demandes à la source de données externe si nécessaire, ce qui permet d'effectuer des requêtes en temps réel. Le moteur VertiPaq accepte les requêtes dans des structures binaires internes (décrites en externe à l'aide d'un format lisible par l'homme appelé xmSQL), tandis que les cartouches DirectQuery acceptent les requêtes utilisant le langage SQL, dans le dialecte pris en charge par la cartouche elle-même.
Figure 1 Architecture DirectQuery dans SQL Server 2016 Analysis Services pour les modèles tabulaires
C'est différent de la façon dont les choses fonctionnaient dans SSAS 2012/2014. En fait, dans les versions précédentes de SSAS, vous aviez la possibilité de créer des modèles hybrides, où l'outil client avait la possibilité d'exécuter une requête en mode VertiPaq standard ou en mode DirectQuery, comme vous le voyez à la figure 2.
Figure 2 Ancienne architecture DirectQuery dans SQL Server Analysis Services 2012/2014 pour les modèles tabulaires
Dans SSAS 2016, le mode hybride n'est plus disponible, mais ce n'est pas une grande limitation. Comme nous l'avons dit précédemment, l'utilisation de DirectQuery dans SSAS 2012/2014 était limitée à quelques scénarios spécifiques. Le reste de ce livre blanc couvrira uniquement DirectQuery dans SSAS 2016.
DirectQuery interagit avec les bases de données relationnelles et, afin d'utiliser au mieux la base de données, il utilise des cartouches pour prendre en charge le dialecte spécifique du serveur qu'il utilise. Au moment de la rédaction de cet article, les cartouches disponibles sont les suivantes :
• Microsoft SQL Server (version 2008 ou ultérieure)
• Base de données Microsoft SQL Azure
• Entrepôt de données SQL Microsoft Azure
• Système de plate-forme Microsoft Analytics (APS)
• Oracle (version 9i ou ultérieure)
• Teradata (V2R6, V12)
Pour obtenir la liste actuelle des bases de données et des versions prises en charge, rendez-vous sur :
Comme expliqué précédemment :
• Un moteur de base de données en mémoire (VertiPaq) peut stocker des données.
• DirectQuery est une interface vers une base de données existante. Il ne peut fournir que des données qui existent déjà dans le système source.
La différence entre VertiPaq et DirectQuery est importante lorsque l'on considère les tables et les colonnes calculées :
• Tables calculées : DirectQuery ne prend pas en charge les tables calculées, principalement parce qu'il n'y a pas d'emplacement pour les stocker.
• Colonnes calculées : vous pouvez utiliser des colonnes calculées avec DirectQuery, mais avec certaines limitations que nous décrirons plus loin.
Certaines fonctions DAX ont une sémantique différente, car elles sont converties en expressions SQL correspondantes au lieu d'être exécutées par le moteur en mémoire. Par conséquent, vous pouvez observer un comportement incohérent entre les plateformes lors de l'utilisation des fonctions Time Intelligence et des fonctions statistiques. Il existe également des fonctions DAX qui ne sont pas prises en charge dans DirectQuery, et le concepteur SSDT le signale lorsque vous basculez un modèle vers DirectQuery.
MDX présente certaines limitations dans DirectQuery qui n'affectent que le style de codage MDX. Vous ne pouvez pas utiliser :
• Noms relatifs
• Instructions MDX de portée de session
• Tuples avec des membres de différents niveaux dans les clauses de sous-sélection MDX
D'autres limitations affectent la conception du modèle de données. Par exemple, vous ne pouvez pas référencer des hiérarchies définies par l'utilisateur dans une requête MDX envoyée à un modèle à l'aide de DirectQuery. Cela a un impact sur la convivialité de DirectQuery à partir d'Excel, car la fonctionnalité fonctionne sans problème lorsque vous utilisez un moteur de stockage en mémoire.
Dans cette section, nous fournissons une description plus complète de l'architecture globale de DirectQuery et expliquons plus en détail le fonctionnement de la technologie DirectQuery.
Comme nous l'avons décrit dans l'introduction, DirectQuery est une alternative à un moteur de stockage en mémoire (VertiPaq). Dans SSAS, il existe un moteur à formule unique et deux moteurs de stockage différents, comme déjà illustré à la figure 1.
N'oubliez pas que le moteur de stockage est chargé de récupérer les ensembles de données de la base de données, tandis que le moteur de formules effectue des calculs sur les données renvoyées par le moteur de stockage. Les deux moteurs doivent travailler en étroite collaboration afin d'offrir les meilleures performances. En fait, une séparation claire entre les deux produirait des plans de requête sous-optimaux. En voici un exemple.
SalesAmt := SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )
La mesure fait référence à deux colonnes de la base de données : Quantité et Prix unitaire. Si les deux moteurs étaient complètement séparés, voici comment SSAS résoudrait la requête :
• Le moteur de formules demande au moteur de stockage un ensemble de données avec la quantité et le prix unitaire.
• Une fois l'ensemble de données renvoyé, le moteur de formules itère dessus, effectue le calcul (une simple multiplication, dans ce cas) et fournit enfin le résultat.
En SQL, ce plan se traduirait par une seule requête SQL comme celle illustrée ici :
SELECT
SalesQuantity,
UnitPrice
FROM
Sales
Comme vous pouvez l'imaginer, pour une table Sales avec des centaines de millions de lignes, cette opération nécessiterait que le moteur de stockage renvoie une grande quantité de données. Le temps nécessaire pour allouer la RAM et transférer les données de la base de données serait important. En réalité, dire que le moteur de stockage ne peut récupérer que des données de la base de données, laissant tout le calcul au moteur de formules, est une affirmation trop forte. En fait, le moteur de stockage est capable d'effectuer certains calculs par lui-même, même s'il ne prend pas en charge toutes les fonctions DAX. Étant donné que le moteur de stockage peut facilement gérer une multiplication simple, la requête ci-dessus sera résolue comme suit :
• Le moteur de formules demande au moteur de stockage un ensemble de données contenant la somme de la multiplication de la quantité par le prix net.
• Le moteur de stockage analyse la base de données, puis calcule le résultat et renvoie une seule ligne.
• Le moteur de formule prend le résultat et l'empaquette dans le jeu de résultats final.
En SQL, une requête exécutée par le moteur de base de données serait très différente de la précédente :
SELECT
SUM ( SalesQuantity * UnitPrice ) AS Result
FROM
Sales
Cette dernière requête analyse les tables et effectue des multiplications en même temps, renvoyant immédiatement le résultat à l'appelant. En tant que tel, il est plus rapide et utilise moins de RAM, ce qui le rend plus efficace que la requête précédente.
Une séparation claire entre les deux moteurs n'est pas conseillée, car la coordination entre les deux moteurs donne de meilleurs résultats. Dans le même temps, si le moteur de stockage était capable de résoudre la plupart (sinon la totalité) des fonctions DAX, il pourrait devenir inefficace : il gérerait un niveau élevé de complexité, étant donné que DAX et MDX sont tous deux des langages puissants et que la sémantique de leurs fonctions est plutôt complexe. Ainsi, SSAS équilibre désormais la complexité et la vitesse, fournissant au moteur en mémoire suffisamment de puissance pour calculer les fonctions courantes, tout en laissant les calculs les plus complexes au moteur de formules.
Cependant, avec DirectQuery, le scénario est beaucoup plus complexe, car DirectQuery n'est pas un moteur unique, c'est une technologie qui connecte le moteur de formules à de nombreuses bases de données SQL différentes en utilisant leurs propres dialectes par le biais de cartouches. Différentes bases de données peuvent avoir une sémantique différente pour la même fonction, différents ensembles de fonctionnalités et des performances différentes pour le même type de requête. En outre, le langage SQL est beaucoup plus puissant que xmSQL (utilisé par le modèle en mémoire), de sorte que DirectQuery a généralement la possibilité d'envoyer plus de calculs au serveur SQL. Par conséquent, le plan de requête généré pour un modèle en mémoire est différent du plan de requête généré pour un modèle à l'aide de DirectQuery, et chaque cartouche peut fournir des performances différentes. En règle générale, SQL est puissant et permet d'alléger la charge de travail du moteur de formules en mode DirectQuery. Cependant, chaque implémentation SQL a ses propres comportements typiques que vous devez tester avant de prendre vos décisions de modélisation.
Utilisation des sources de données prises en charge
Au moment de la rédaction de cet article, le mode DirectQuery prend en charge les bases de données relationnelles et les fournisseurs répertoriés dans le tableau suivant. Pour une liste à jour, visitez
Utilisation des sources de données prises en charge
Source des données
Versions
Fournisseurs
Serveur Microsoft SQL
2008 et versions ultérieures
Fournisseur OLE DB pour SQL Server,
SQL Server Native Client OLE DB, fournisseur
Fournisseur de données .NET Framework pour le client SQL
Microsoft Azure SQL
Base de données
Tout
Fournisseur OLE DB pour SQL Server,
SQL Server Native Client OLE DB, fournisseur
Fournisseur de données .NET Framework pour le client SQL
Données SQL Microsoft Azure Entrepôt
Tout
Fournisseur de données .NET Framework pour le client SQL
Analytique Microsoft SQL
Système de plate-forme (APS)
Tout
Fournisseur OLE DB pour SQL Server,
SQL Server Native Client OLE DB, fournisseur
Fournisseur de données .NET Framework pour le client SQL
Oracle
bases de données relationnelles
Oracle 9i et versions ultérieures
Fournisseur Oracle OLE DB
Teradata
bases de données relationnelles
Teradata V2R6 et
plus tard
Fournisseur de données .Net pour Teradata
There are no rows in this table
Dans cette section, nous allons identifier et analyser les différences supplémentaires entre les solutions basées sur les modèles en mémoire (VertiPaq) et DirectQuery.
Toutes les tables d'un modèle donné doivent être basées sur une base de données source unique. Vous ne pouvez pas avoir plusieurs sources de données pour le même modèle dans DirectQuery. La raison en est que le code SQL généré par le moteur pour récupérer les données contiendra des jointures entre des tables, des filtres et d'autres codes SQL complexes qui ne peuvent pas fonctionner sur plusieurs serveurs ou plusieurs bases de données. De plus, toutes les tables doivent être connectées à une instruction SQL, qui ne peut pas être une procédure stockée. Dans T-SQL, vous pouvez utiliser des tables, des vues et des fonctions tableaux. La seule limitation concerne les procédures stockées.
Si vous utilisez Microsoft SQL Server, vous pouvez utiliser des vues référençant des tables dans d'autres bases de données et/ou serveurs. Ceci est transparent pour Analysis Services et les performances résultantes dépendent de la configuration de SQL Server.
Il est recommandé de toujours créer des vues dans la source SQL pour alimenter le modèle SSAS, afin de découpler le modèle tabulaire de la structure physique de la base de données. DirectQuery ne fait pas exception, et les vues sont très utiles ici aussi. N'oubliez pas que le code SQL utilisé pour sourcer vos tables sera utilisé plus d'une fois. Contrairement aux modèles de données en mode mémoire qui n'utilisent le code SQL qu'une seule fois, DirectQuery utilise le même code SQL plusieurs fois, car chaque requête SSAS récupère de petits sous-ensembles de données. Pour de meilleures performances, nous vous recommandons d'éviter les jointures complexes et le code SQL alambiqué.
Comme mentionné précédemment, un modèle tabulaire dans DirectQuery ne peut pas avoir de tables calculées. En outre, il existe plusieurs limitations dans les expressions DAX que vous pouvez utiliser dans les colonnes calculées et dans les expressions pour la sécurité au niveau des lignes (RLS). D'une manière générale, dans les colonnes calculées et la sécurité au niveau des lignes, vous pouvez utiliser des fonctions DAX renvoyant une valeur scalaire et fonctionnant dans un contexte de ligne, alors que vous ne pouvez pas utiliser de fonctions d'agrégation, de fonctions de table et d'itérateurs. Le fait est que l'expression DAX d'une colonne calculée ou d'un filtre de sécurité au niveau des lignes doit être traduite dans une expression SQL correspondante, ce qui n'est pas possible (ou du moins pas assez efficace) lorsque la fonction dépend de concepts spécifiques au langage DAX (tels que le contexte de filtre et la transition de contexte).
Les hiérarchies sont une autre modélisation. Les hiérarchies d'utilisateurs ne sont pas prises en charge dans les requêtes MDX envoyées à un modèle en mode DirectQuery. Ainsi, même si vous pouvez créer des hiérarchies d'utilisateurs dans le modèle tabulaire, ces hiérarchies ne sont pas visibles dans un client utilisant MDX (tel qu'Excel), alors qu'elles sont disponibles dans un client utilisant DAX (tel que Power BI).
Chaque fois que DirectQuery envoie une requête à SQL, il récupère uniquement un nombre maximal prédéfini de lignes, qui est, par défaut, de 1 million. Il s'agit de limiter les requêtes qui pourraient s'exécuter trop longtemps. Si vous demandez trop de mémoire sur Analysis Services, il peut être difficile de stocker un résultat intermédiaire lors d'une requête plus complexe.
Par exemple, considérez la requête DAX suivante :
EVALUATE
ROW ( "Rows", COUNTROWS ( Sales ) )
Il génère une requête SQL correspondante qui ne renvoie qu'une seule ligne :
SELECT COUNT_BIG (*) AS [a0]
FROM (
SELECT *
FROM Sales
) AS t1
Dans la requête ci-dessus, une seule ligne de données a été déplacée de SQL Server vers SSAS. SQL a exécuté la requête complète et a renvoyé un petit ensemble de données, ce qui a permis d'obtenir de bonnes performances. Toutefois, d'autres requêtes DAX peuvent transférer un grand nombre de lignes vers Analysis Services pour une évaluation plus approfondie. Prenons l'exemple de cette requête DAX :
EVALUATE
ROW (
"Orders",
COUNTROWS (
ALL (
Sales[Order Number],
Sales[Order Line Number]
)
)
)
La requête SQL générée n'exécute pas l'opération COUNT sur SQL Server. Au lieu de cela, il transfère une liste combinée de valeurs de numéro de commande et de numéro de ligne de commande à Analysis Services, afin que le moteur de formules puisse les compter. Cependant, afin d'éviter le transfert d'une énorme quantité de données entre les deux moteurs, une clause TOP limite le nombre de lignes renvoyées par cette requête à 1 million, comme le montre le code suivant :
SELECT TOP ( 1000001 )
t1.[Order Number], t1.[Order Line Number]
FROM (
SELECT *
FROM Sales
) AS [t1] GROUP BY
t1.[Order Number], t1.[Order Line Number]
Remarque : L'instruction renvoie le numéro de commande et le numéro de ligne de commande, et non toutes les colonnes de la table.
Si le résultat est supérieur à 1 million de lignes, le nombre de lignes transférées est exactement d'un million et un (1 000 001), qui est un ensemble de données tronqué. Lorsque cela se produit, SSAS suppose que d'autres lignes n'ont peut-être pas été transférées et renvoie l'erreur suivante :
L'ensemble de résultats d'une requête vers une source de données externe a dépassé la taille maximale autorisée de '1000000' lignes.
Cette limite par défaut de 1 million de lignes est la même que celle utilisée pour les modèles créés par Power BI Desktop. Cette limite est présente pour éviter qu'une énorme quantité de données ne soit déplacée entre les moteurs. Il s'agit d'une fonctionnalité de sécurité, mais elle peut entraîner des requêtes qui ne peuvent pas être exécutées. Pour cette raison, vous pouvez augmenter ce paramètre sur votre instance SSAS. Pour ce faire, vous devez modifier manuellement le fichier de configuration msmdsrv.ini, en spécifiant une limite différente pour le paramètre MaxIntermediateRowsetSize, qui doit être ajouté au fichier à l'aide de la syntaxe suivante, car il n'est pas présent par défaut :
<ConfigurationSettings>
. . .
<DAX>
<DQ>
<MaxIntermediateRowsetSize>1000000
</MaxIntermediateRowsetSize>
</DQ> </DAX>
. . .
Vous trouverez plus de détails à ce sujet et d'autres paramètres pour DAX dans la documentation MSDN en ligne à l'adresse
Tips ! Si vous disposez d'un serveur tabulaire SSAS doté d'une bonne quantité de mémoire et d'une bonne bande passante pour la connexion à la source de données en mode DirectQuery, vous souhaiterez probablement augmenter ce nombre à une valeur plus élevée. En règle générale, ce paramètre doit être supérieur à la dimension plus grande dans un modèle de schéma en étoile. Par exemple, si vous avez 4 millions de produits et 8 millions de clients, vous devez augmenter le paramètre MaxIntermediateRowsetSize à 10 millions. De cette façon, toute requête agrégeant les données au niveau du client continuerait à fonctionner. L'utilisation d'une valeur trop élevée (par exemple, 100 millions) peut épuiser la mémoire et/ou expirer la requête avant que la limite ne soit atteinte, de sorte qu'une limite inférieure permet d'éviter une condition aussi critique.
Comme mentionné précédemment, l'utilisation de DAX présente des limites. DirectQuery peut fournir deux types différents de prise en charge des fonctions DAX dans les mesures et les expressions de requête :
• Fonctions DAX non optimisées pour DirectQuery : ces fonctions ne sont pas converties dans les expressions SQL correspondantes, elles sont donc exécutées à l'intérieur du moteur de formules. Par conséquent, ils peuvent nécessiter le transfert de grandes quantités de données entre la base de données source et le moteur SSAS.
• Fonctions DAX optimisées pour DirectQuery : ces fonctions sont converties dans une syntaxe correspondante en langage SQL, de sorte que leur exécution soit prise en charge de la cartouche DirectQuery spécifique. Ils offrent de bonnes performances, car ils utilisent le dialecte SQL natif, évitant ainsi le transfert de grandes quantités de données entre la base de données source et le moteur de formules.
Chaque fonction DAX optimisée pour DirectQuery appartient à l'un des deux groupes suivants :
Groupe 1 : fonctions DAX qui sont également disponibles dans les colonnes calculées et dans les expressions de filtre de sécurité au niveau des lignes. Ces fonctions sont optimisées pour DirectQuery et sont prises en charge dans toutes les formules DAX.
Il s'agit notamment de :
ABS, ACOS, ACOT, AND, ASIN, ATAN, BLANK, CEILING, CONCATENATE, COS, COT, CURRENCY, DATE, DATEDIFF, DATEVALUE, DAY, DEGREES, DIVIDE, EDATE, EOMONTH, EXACT, EXP, FALSE, FIND, HOUR, IF, INT, ISBLANK, ISO.CEILING, KEEPFILTERS, LEFT, LEN, LN, LOG, LOG10, LOWER, MAX, MID, MIN, MINUTE, MOD, MONTH, MROUND, NOT, NOW, OR, PI, POWER, QUOTIENT, RADIANS, RAND, RELATED, REPT, RIGHT, ROUND, ROUNDDOWN, ROUNDUP, SEARCH, SECOND, SIGN, SIN, SQRT, SQRTPI, SUBSTITUTE, SWITCH, TAN, TIME, TIMEVALUE, TODAY, TRIM, TRUE, TRUNC, UNICODE, UPPER, USERNAME, USERELATIONSHIP, VALUE, WEEKDAY, WEEKNUM, YEAR.
Groupe 2 : fonctions DAX qui ne peuvent pas être utilisées dans des colonnes calculées ou des expressions de filtre, mais qui peuvent être utilisées dans des mesures et des requêtes. Ce groupe comprend des fonctions DAX optimisées pour DirectQuery et prises en charge uniquement dans les mesures et les formules de requête, mais ne peuvent pas être utilisées dans les colonnes calculées et les filtres de sécurité au niveau des lignes :
Il s'agit notamment de :
ALL, ALLEXCEPT, ALLNOBLANKROW, ALLSELECTED, AVERAGE, AVERAGEA, AVERAGEX, CALCULATE, CALCULATETABLE, COUNT, COUNTA, COUNTAX, COUNTROWS, COUNTX, DISTINCT, DISTINCTCOUNT, FILTER, FILTERS, HASONEFILTER, HASONEVALUE, ISCROSSFILTERED, ISFILTERED, MAXA, MAXX, MIN, MINA, MINX, RELATEDTABLE, STDEV.P, STDEV.S, STDEVX.P, STDEVX.S, SUM, SUMX, VALUES, VAR.P, VAR.S, VARX.P, VARX.S.
Pour obtenir la liste actuelle des fonctions DAX prises en charge, consultez la page suivante :
Toutes les autres fonctions DAX qui ne sont pas incluses dans ces deux listes sont disponibles uniquement pour les formules de mesure et de requête DirectQuery, mais elles ne sont pas optimisées. Par conséquent, le calcul peut être implémenté dans le moteur de formules sur Analysis Services, qui récupère la granularité requise de la base de données source pour effectuer le calcul. Outre le ralentissement des performances, l'exécution de la requête peut nécessiter la matérialisation d'un résultat de requête SQL volumineux dans la mémoire Analysis Services. Pour cette même raison, si vous avez des calculs complexes sur des tables volumineuses, veillez à examiner attentivement le paramètre MaxIntermediateRowsetSize décrit précédemment.
Parfois, une expression DAX produit des résultats différents dans DirectQuery par rapport aux modèles en mémoire. Cela est dû à des différences de sémantique entre DAX et SQL pour :
• Comparaisons (chaînes de caractères et nombres, texte avec booléen et valeurs nulles)
• Conversions (chaîne en booléen, chaîne en date/heure et nombre en chaîne)
• Fonctions mathématiques et opérations arithmétiques (ordre d'addition, utilisation de la fonction POWER, dépassement numérique, fonctions LOG avec blancs et division par zéro)
• Plages numériques et date-heure
• Monnaie
• Fonctions de texte
Voir l'annexe A pour plus de détails.
Division par 0 et division par blanc
En mode DirectQuery, la division par zéro (0) ou la division par BLANK entraîne toujours une erreur. SQL Server ne prend pas en charge la notion d'infini et, comme le résultat naturel de toute division par 0 est l'infini, le résultat est une erreur. Toutefois, SQL Server prend en charge la division par les valeurs null, et le résultat est égal à null.
Plutôt que de renvoyer des résultats différents pour ces opérations, en mode DirectQuery, les deux types d'opérations (division par zéro et division par null) renvoient une erreur.
Dans Excel et dans les modèles PowerPivot, la division par zéro renvoie également une erreur. Cependant, la division par BLANK renvoie un BLANK.
Par exemple, les expressions suivantes sont valides pour les modèles en mémoire, mais échouent en mode DirectQuery :
1/BLANK
1/0
0.0/BLANK
0/0
L'expression BLANK/BLANK est un cas particulier qui renvoie BLANK dans les modes en mémoire et DirectQuery.
Fonctions statistiques sur une table avec une seule ligne
Les fonctions statistiques d'une table comportant une ligne renvoient des résultats différents. Les fonctions d'agrégation sur des tables vides se comportent également différemment dans les modèles en mémoire qu'en mode DirectQuery. Si la table utilisée comme argument contient une seule ligne, en mode DirectQuery, les fonctions statistiques telles que STDEV et VARx return null.
Dans un modèle en mémoire, une formule qui utilise STDEV ou VARx sur une table avec une seule ligne renvoie une erreur de division par zéro.
Les mesures DAX sont traduites soit en code SQL, soit en requêtes SQL récupérant des données brutes ainsi qu'un plan de requête de moteur de formules, en fonction du type de fonction que vous utilisez dans la mesure elle-même.
Par exemple, la requête suivante définit la mesure Sales[Amt] et récupère le montant des ventes pour chaque couleur.
DEFINE
MEASURE Sales[Amt] =
SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )
EVALUATE
SUMMARIZECOLUMNS ( Product[Color], "Amt", [Amt] )
Ce problème est résolu par la requête SQL unique suivante :
SELECT TOP (1000001)
t0.ProductColor,
SUM ( t0.PriceMultipliedByQuantity ) AS PriceMultipliedByQuantity FROM
(SELECT
Sales.[Quantity] AS Quantity,
Sales.[Unit Price] AS [Unit Price],
Product.[Color] AS ProductColor,
(Sales.[Quantity] * Sales.[Unit Price]) AS PriceMultipliedByQuantity FROM
Sales
LEFT OUTER JOIN Product
ON Sales.[ProductKey] = Product.[ProductKey]
) AS [t0] GROUP BY
t0.ProductColor
Comme vous pouvez le voir, l'intégralité du calcul a été transmise à SQL Server. Les données déplacées entre SSAS et SQL ne sont que le résultat réel, tandis que le calcul est poussé au niveau le plus bas de la source de données.
Cependant, à partir de ce seul code, vous ne pouvez pas déduire qu'aucune matérialisation n'a lieu. En fait, DirectQuery utilise SQL pour demander à la base de données relationnelle de produire le résultat de la multiplication. À l'intérieur du moteur relationnel, SQL peut encore faire une certaine matérialisation. Dans la mesure du possible, DirectQuery évite de transférer de grandes quantités de données entre SQL et SSAS en demandant d'effectuer le calcul de la manière la plus efficace. Ensuite, c'est à la base de données relationnelle de calculer la valeur. C'est important, car cela montre clairement que les performances du système dépendent principalement de la qualité de l'optimisation de la base de données relationnelle. DirectQuery n'a aucun moyen de savoir comment le modèle est structuré en SQL. Il peut se trouver dans une table de tas ou dans un index columnstore en cluster dans Microsoft SQL Server, et il peut avoir les bons index en place ou non. DirectQuery génère uniquement une requête. L'administrateur de base de données est chargé de garantir que les requêtes générées par DirectQuery seront exécutées à la vitesse maximale.
Dans les mesures, il existe une différence importante entre l'utilisation optimisée et non optimisée des fonctions. En fait, si vous modifiez la mesure précédente et, au lieu de SUMX, utilisez une fonction non optimisée comme MEDIANX, le scénario change considérablement. Par exemple:
DEFINE
MEASURE Sales[Amt] =
MEDIANX ( Sales, Sales[Quantity] * Sales[Unit Price] )
EVALUATE
SUMMARIZECOLUMNS ( Product[Color], "Amt", [Amt] )
Dans la troisième ligne, la mesure utilise MEDIANX, qui n'est pas optimisé pour DirectQuery. Par conséquent, le moteur doit récupérer, à partir de la base de données, les valeurs des colonnes Quantité et Prix unitaire pour toutes les lignes de la table Sales, comme indiqué dans la requête SQL suivante :
SELECT TOP (1000001)
Sales.[Quantity] AS Quantity,
Sales.[Unit Price] AS [Unit Price],
Product.[Color] AS ProductColor
FROM
Sales
LEFT OUTER JOIN Product
ON Sales.[ProductKey] = Product.[ProductKey]
Non seulement cela entraînerait des performances médiocres, mais cela produirait également une erreur, car nous interrogeons la table de faits dans notre base de données de test qui contient 12 millions de lignes. Comme expliqué dans la section Présentation des limites de requête ci-dessus, DirectQuery dispose d'un paramètre qui empêche la récupération de plus de 1 million de lignes. Même si le paramètre MaxIntermediateRowsetSize est configurable, vous ne souhaitez probablement pas interroger plusieurs millions de lignes chaque fois que les utilisateurs évaluent une requête ou un tableau croisé dynamique. Ce paramètre permet d'éviter les requêtes de longue durée et d'améliorer l'expérience utilisateur.
Au démarrage d'un projet, il peut être difficile de prédire les fonctions dont vous aurez besoin et le niveau de matérialisation optimal pour les requêtes. Faites de votre mieux pour choisir les fonctions avec soin et évitez de matérialiser de grands ensembles de données. Il est préférable d'avoir une connaissance approfondie de DAX avant d'adopter DirectQuery, afin de pouvoir évaluer l'impact des calculs en mode DirectQuery. Vous devrez vous appuyer sur votre propre expérience pour déterminer si vous avez besoin de fonctions non optimisées.
Le mode en mémoire (VertiPaq) et les modèles de données DirectQuery gèrent les colonnes calculées de manières sensiblement différentes. Le mode en mémoire calcule les colonnes calculées au moment du traitement et stocke les résultats dans la base de données. DirectQuery, cependant, est une couche de requête et non une base de données, de sorte que les colonnes calculées ne peuvent pas être calculées pendant le temps de traitement (car il n'y a pas de temps de traitement) et les résultats ne peuvent pas être stockés (car il n'y a pas d'espace de stockage).
Lorsque vous utilisez DirectQuery, les colonnes calculées doivent être calculées chaque fois que vous les utilisez. Pour cette raison, vous ne pouvez utiliser que le premier sous-ensemble de fonctions optimisées. N'oubliez pas que certaines fonctions optimisées peuvent être utilisées partout, et que d'autres ne peuvent être utilisées que dans des mesures et des requêtes. En d'autres termes, dans les colonnes calculées DirectQuery, vous pouvez utiliser uniquement des fonctions optimisées qui peuvent être calculées pour chaque requête et qui n'ont pas besoin d'être stockées dans le modèle.
Par exemple, une colonne calculée dans la table Product contenant une simple SUM comme celle illustrée ici ne peut pas être implémentée dans DirectQuery :
Product[TotalQuantity] = CALCULATE ( SUM ( Sales[Quantity] ) )
En fait, la colonne calculée calcule la quantité vendue pour le produit donné, mais, pour plus d'efficacité, les résultats sont stockés dans la table Product. Essayer de calculer cette valeur chaque fois qu'elle est nécessaire affectera négativement les performances de la requête, de sorte qu'un modèle tabulaire en mode DirectQuery ne vous permet pas de définir une colonne calculée de cette manière.
D'autre part, des calculs simples qui ne dépendent que de la ligne actuelle peuvent être facilement mis en œuvre.
Ainsi, une colonne calculée comme la suivante fonctionne correctement :
Sales[LineTotal] = Sales[Quantity] * Sales[Unit Price]
Néanmoins, gardez à l'esprit que le calcul aura lieu à chaque fois que vous exécuterez une requête. Par exemple:
EVALUATE
SUMMARIZECOLUMNS (
Product[Color],
"Amt", CALCULATE ( SUM ( Sales[LineTotal] ) ) )
Comme vous pouvez le voir, chaque ligne est calculée chaque fois que vous interrogez cette colonne dans le modèle. Les calculs simples ne sont pas un problème. Toutefois, pour des calculs plus complexes, envisagez de matérialiser les résultats dans la source de données, ce qui évitera la complexité du calcul de chaque requête.
Notez que les colonnes calculées dans les agrégations ont l'effet secondaire d'être calculées encore et encore, mais, selon la taille de la base de données, les performances peuvent être assez bonnes. Toutefois, si vous prévoyez d'utiliser des colonnes calculées comme filtres, le scénario devient plus complexe. Par exemple, voici une variante simple de la requête :
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
Product[Color],
"Amt", CALCULATE ( SUM ( Sales[LineTotal] ) )
),
Sales[LineTotal] >= 100 )
Cette fois, la requête n'agrège pas seulement LineTotal (comme nous l'avons fait précédemment), mais applique également un filtre à LineTotal exigeant qu'il soit supérieur ou égal à 100. Étant donné que LineTotal est une colonne calculée, elle sera calculée au moment de la requête. Voici la requête SQL résultante :
SELECT TOP (1000001)
Product.[Color],
SUM ( [LineTotal] ) AS [a0]
FROM
( ( SELECT
Sales.[ProductKey] AS [ProductKey],
( Sales.[Quantity] * Sales.[Unit Price] ) AS [LineTotal]
FROM
Sales
) AS A
LEFT OUTER JOIN Product
ON (Sales.[ProductKey] = Product.[ProductKey])
)
WHERE
( A.[LineTotal] >= CAST ( N'100' AS MONEY ) )
GROUP BY
Product.[Color];
Si vous regardez le code, vous remarquerez que le filtre sur LineTotal sera appliqué après un balayage complet de la table de faits. En d'autres termes, la seule façon pour SQL de résoudre la condition est d'analyser l'intégralité de la table, de calculer la valeur de LineTotal, puis de supprimer les lignes qui ne satisfont pas à la condition. Ce n'est pas intrinsèquement mauvais, mais cela montre que le filtrage avec une colonne calculée ne se produit qu'après une analyse complète, au lieu que le filtre soit appliqué avant que les données ne soient récupérées. En d'autres termes, il est peu probable que le filtre soit optimisé et, quel que soit le nombre de lignes qu'il récupérera, il doit probablement effectuer une analyse complète.
Le même scénario avec un modèle de données en mode mémoire entraînerait un plan de requête plus optimisé, car la valeur de LineTotal serait connue à l'avance et stockée dans le modèle. Ainsi, un filtre dans la requête réduit le temps passé à analyser la table.
Bien sûr, si vous matérialisez LineTotal dans la base de données source et construisez les index appropriés, le plan de requête sera également optimisé en SQL, mais au prix d'un espace de stockage supplémentaire pour la valeur de colonne calculée.
Lors de la création de colonnes calculées dans un modèle DirectQuery, vous devez comprendre les implications d'une colonne calculée et la manière dont le moteur va implémenter leur utilisation. Ne pas le faire peut entraîner de mauvaises performances.
Dans un modèle tabulaire, vous devez utiliser DAX pour définir des mesures et des colonnes calculées, mais les requêtes peuvent être écrites à l'aide de DAX et de MDX. De cette façon, un modèle tabulaire est compatible avec n'importe quel client MDX existant, tel que le tableau croisé dynamique dans Excel, et n'importe quel client DAX, comme Power BI. En mode DirectQuery, les fonctionnalités MDX disponibles sont limitées. En règle générale, le MDX généré par Excel fonctionne dans DirectQuery, mais ces limitations peuvent affecter d'autres produits clients ou requêtes MDX créées manuellement dans des rapports ou d'autres outils. Les limitations sont les suivantes :
• Vous ne pouvez pas utiliser de noms d'objet relatifs. Tous les noms d'objet doivent être entièrement qualifiés.
• Aucune instruction MDX d'étendue de session (ensembles nommés, membres calculés, cellules calculées, totaux visuels, membres par défaut, etc.) n'est autorisée, mais vous pouvez utiliser des constructions d'étendue de requête, telles que la commande
WITH.
• Vous ne pouvez pas utiliser de tuples avec des membres de différents niveaux dans les clauses de sous-sélection MDX.
• Les hiérarchies définies par l'utilisateur ne peuvent pas être utilisées.
Pour obtenir la liste actuelle des restrictions, consultez :
Lorsque vous disposez d'un modèle tabulaire en mode DirectQuery, vous pouvez définir la sécurité à deux endroits :
• Vous pouvez utiliser les rôles de sécurité définis dans Analysis Services, comme vous le faites dans les modèles utilisant le mode en mémoire.
• Vous pouvez définir la sécurité sur la source de données relationnelle en demandant à Analysis Services d'emprunter l'identité de l'utilisateur actuel lorsqu'il envoie les requêtes SQL à la source de données.
Habituellement, vous choisissez soit l'une ou l'autre technique, mais rien ne vous empêche de combiner les deux, même si ce n'est généralement pas nécessaire.
Si vous souhaitez vous appuyer sur la sécurité standard basée sur les rôles fournie par Analysis Services, vous devez savoir que toutes les requêtes SQL incluront les prédicats et les jointures nécessaires pour récupérer uniquement les données requises. Cela peut entraîner des requêtes complexes (et potentiellement lentes), à moins que vous ne prêtiez une attention particulière aux détails de la définition de la sécurité. De plus, lorsque vous utilisez DirectQuery, il existe des restrictions dans les expressions DAX utilisées dans les filtres de rôle qui sont les mêmes restrictions appliquées aux colonnes calculées.
Si vous avez déjà implémenté la sécurité au niveau des lignes dans la base de données relationnelle et que vous prenez en charge la sécurité intégrée Windows, vous pouvez configurer Analysis Services pour emprunter l'identité de l'utilisateur actuel. Ce faisant, SSAS exécutera des requêtes SQL en se faisant passer pour l'utilisateur connecté à SSAS, afin que la base de données relationnelle puisse reconnaître la personne et appliquer la configuration de sécurité nécessaire. Par exemple, vous pouvez définir la sécurité au niveau des lignes (RLS) dans Microsoft SQL Server 2016 et vous appuyer sur celle-ci en empruntant l'identité de l'utilisateur actuel dans Analysis Services.
Pour emprunter l'identité de l'utilisateur actuel alors que SSAS et la base de données relationnelle s'exécutent sur des serveurs différents, vous devez également configurer la délégation Kerberos pour permettre au jeton (de l'utilisateur emprunté par SSAS) d'être transféré à la base de données relationnelle. Cela élimine le problème bien connu du « double saut » des jetons de sécurité Windows.
Comme mentionné précédemment, DirectQuery est un système d'interrogation qui ne peut pas stocker d'informations. Par nature, DirectQuery est un système en temps réel, ce qui signifie qu'il interroge les données au moment où la requête est exécutée.
DirectQuery, par lui-même, n'effectue aucun type de mise en cache entre différentes requêtes.
Si vous exécutez la même requête à différents moments dans le temps, vous pouvez récupérer des résultats différents. Peu importe qu'une heure ou un dixième de seconde sépare les deux points dans le temps, les résultats peuvent être différents, car, entre-temps, les données peuvent changer dans un système en temps réel.
Au moment de la publication, les deux principaux clients SSAS sont Excel et Power BI. Ces deux clients utilisent des modèles différents pour interroger le modèle, ce qui peut renvoyer des résultats différents (et potentiellement déroutants) selon le client que vous utilisez.
Power BI
Power BI génère des requêtes DAX et dispose de son propre système de mise en cache. Cela permet d'éviter d'envoyer à plusieurs reprises la même requête au serveur chaque fois que l'utilisateur interagit avec les composants visuels. En fait, lors de la création de rapports, les utilisateurs essaieront probablement différents visuels. Pour éviter d'interroger le serveur SSAS chaque fois qu'un utilisateur clique sur une visualisation, Power BI met en cache les résultats des visuels sur le client et les réutilise pendant un certain temps. Cela fonctionne bien pour les modèles de données en mode mémoire, car les données restent statiques jusqu'à ce qu'elles soient explicitement actualisées avec un processus. Cependant, dans un système en temps réel, les données changent de manière continue.
Par exemple, imaginez qu'un utilisateur souhaite créer un visuel affichant les ventes totales et que la requête initiale renvoie 1 000 ventes. Quelques secondes plus tard, l'utilisateur crée une nouvelle matrice avec les ventes divisées par couleur, mais le total général affiche maintenant 1 001. Cela se produit parce que, dans le temps entre la première et la deuxième requête, une nouvelle vente a été ajoutée à la base de données. Tant que le rapport n'est pas actualisé ou que le cache n'expire pas, les visuels ne sont pas mis à jour. Ce comportement peut prêter à confusion, car différents visuels peuvent afficher des totaux différents, mais il s'agit d'un comportement normal pour les systèmes de mise en cache standard.
Deux options s'offrent à vous :
• Évitez le cache, qui diminue les performances, mais augmente la précision.
• Utilisez le cache, qui est plus rapide en raison de la latence réduite, mais peut renvoyer des données obsolètes.
Les modèles de données en mode mémoire ne renvoient des données obsolètes que si le serveur traite la base de données entre deux requêtes. Cela se produit rarement. Remarque : vous pouvez vider le cache Power BI en cliquant sur le bouton Actualiser.
Excel
Excel utilise MDX pour interroger le modèle et ne dispose d'aucune mise en cache côté client. Chaque fois que vous mettez à jour un tableau croisé dynamique, l'intégralité de la requête est exécutée. Aucune donnée mise en cache n'est utilisée et les résultats sont très précis, mais les données sources peuvent changer d'une requête à l'autre, ce qui peut entraîner des visualisations déroutantes.
Par exemple : Les tableaux croisés dynamiques Excel contiennent des informations détaillées et agrégées. Ici, nous avons mis en évidence quatre zones différentes dans le tableau croisé dynamique :
1. Données brutes sur les ventes pour chaque catégorie et chaque année.
2. Total général au niveau de la rangée, qui est le total de la catégorie, quelle que soit l'année.
3. Total général au niveau de la colonne, qui est le total de l'année, quelle que soit la catégorie.
4. Total général du tableau croisé dynamique, qui comprend toutes les années et toutes les catégories.
Figure 3 : Exemple de tableau croisé dynamique avec les différentes requêtes mises en évidence.
Il n'existe aucun moyen efficace d'exécuter une seule requête SQL renvoyant toutes ces informations en une seule étape. En fait, pour remplir le tableau croisé dynamique, le code MDX Excel est transformé en un ensemble de requêtes qui sont envoyées au serveur SQL, chacune d'entre elles récupérant l'un des résultats. Au total, DirectQuery enverra au moins quatre requêtes SQL pour remplir ce tableau croisé dynamique. Évidemment, dans un rapport plus complexe, le nombre de requêtes est beaucoup plus élevé.
Si la première requête prend une seconde, la deuxième requête est exécutée une seconde plus tard, ce qui correspond à un point différent dans le temps. Il en va de même pour la troisième et la quatrième requête. En tant que système en temps réel sans cache, DirectQuery renvoie, pour chaque requête, les données disponibles à ce moment-là. Les données peuvent changer d'une requête à l'autre, ce qui entraîne un tableau croisé dynamique où le total n'est pas la somme des lignes individuelles.
La fréquence à laquelle cela se produira est imprévisible et dépend de :
• La base de données — À quelle fréquence est-elle mise à jour ?
• Les données — Dans quelle mesure les données sont-elles récentes ?
• Les requêtes : à quelle vitesse chaque requête SQL s'exécute-t-elle ?
• Remarque : si vous choisissez d'utiliser DAX, une seule requête DAX peut également entraîner l'exécution de nombreuses requêtes SQL différentes à différents moments dans le temps. Par exemple, regardez cette requête simple :
EVALUATE
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL ( 'Product'[Color], "IsSubtotal" ),
"Amt", SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ) )
En utilisant le même exemple de tableau croisé dynamique que celui de la figure 3, étant donné que la requête utilise la fonction ROLLUPADDISSUBTOTAL, vous obtenez à la fois les lignes individuelles et le total général. Le total général est généré par le biais d'une requête SQL différente exécutée à un moment différent, de sorte que les résultats peuvent ne pas correspondre.
Remarque : parfois, de simples calculs additifs peuvent aboutir à une seule requête SQL. Néanmoins, en règle générale, vous devez supposer que différentes granularités dans le résultat génèrent des requêtes différentes vers la base de données source.
Toute discussion sur l'architecture DirectQuery est incomplète si l'on ne comprend pas les systèmes en temps réel et analytiques. Comme nous l'avons déjà vu, les systèmes en temps réel (comme DirectQuery) souffrent de plusieurs problèmes :
• Il y a des limites dans les capacités de modélisation.
• Il n'y a pas de point de traitement et de consolidation dans le temps.
• L'utilisation du cache est limitée et, lorsqu'elle est présente, peut causer des problèmes.
• Les résultats des rapports sont volatils et peuvent changer à chaque actualisation.
• Un seul rapport peut renvoyer des chiffres incohérents pour les lignes individuelles et les totaux, ou pour différents visuels dans la même page.
Ces limitations présentent deux grands avantages :
• Il n'y a pas de traitement, ce qui élimine la nécessité de répliquer les données dans différentes bases de données.
• Les données sont toujours à jour, ce qui élimine le besoin de gérer la complexité des systèmes en temps quasi réel.
Chaque environnement est différent. Questions à considérer :
• Ai-je besoin de résultats en temps réel ?
• Ai-je besoin de résultats en temps réel pour l'ensemble du modèle de données ?
• Ai-je besoin d'un modèle de données unique en temps réel ?
Dans la plupart des cas, les exigences en temps réel n'affectent que les jours les plus récents et n'impliquent que des calculs simples sur cette période limitée. Par exemple, vous pouvez créer un tableau de bord indiquant le nombre de réclamations résolues au cours de la dernière semaine, du dernier jour et de la dernière heure. Cependant, il est peu probable que vous ayez besoin d'un modèle de données unique contenant à la fois les données sur les sinistres des 10 dernières années et les données sur les sinistres actuels, y compris ceux faits aujourd'hui. Cela est particulièrement vrai dans les cas où les exigences en temps réel des indicateurs clés de performance (KPI) des sinistres actuels imposent des restrictions à l'ensemble du modèle de données.
Nous recommandons de séparer les exigences en temps réel (derniers jours) des exigences analytiques (plusieurs années) en construisant deux modèles :
• Un modèle en temps réel pour les données récentes (les derniers jours) : cette base de données sera plus petite et présentera toutes les limitations des modèles en temps réel, mais aura également des calculs beaucoup plus simples. Ce modèle est basé sur la technologie DirectQuery.
• Modèle en mode en mémoire pour les données historiques (toutes les données des 10 dernières années) : le modèle standard en mode en mémoire dispose d'une puissance SSAS complète avec peu de limitations sur les calculs complexes.
Le modèle historique en mode mémoire nécessitera probablement du code DAX complexe, des calculs d'intelligence temporelle, des colonnes calculées complexes, une segmentation de la clientèle et les formules complexes habituelles observées dans les solutions d'aide à la décision. Le modèle en temps réel a tendance à être moins complexe, car il se compose généralement de simples comptes, de pourcentages et d'informations de base qui mettent en lumière ce qui se passe en ce moment.
La gestion de plusieurs modèles de données peut sembler fastidieuse, mais choisir le(s) bon(s) modèle(s) dès le départ peut vous faire gagner beaucoup de temps par la suite. Nous vous recommandons d'évaluer soigneusement les avantages et les inconvénients des différentes technologies SSAS par rapport à vos besoins. N'hésitez pas à utiliser plus d'un modèle si c'est la bonne chose pour votre mise en œuvre.
Maintenant que nous avons une image claire de DirectQuery (son architecture globale, ses principales limitations et les différentes façons dont les requêtes sont résolues), il est temps d'introduire des rubriques plus pratiques et de voir comment créer un modèle DirectQuery. Il ne s'agit pas d'un guide étape par étape sur la création d'une solution SSAS. Au lieu de cela, nous allons vous montrer comment configurer les paramètres DirectQuery d'une solution simple existante que vous pouvez télécharger ici :
<https://go.microsoft.com/fwlink/?linkid=841809>.
L'activation de DirectQuery est un paramètre unique au niveau du modèle de données, qui affecte l'ensemble du projet. Vous pouvez activer DirectQuery de deux manières :
• Pendant le développement : activez le paramètre Mode DirectQuery dans les propriétés du modèle à l'aide de SQL Server Data Tools (SSDT).
• Après le déploiement : définissez la propriété Mode par défaut du modèle sur DirectQuery à l'aide de SQL Server Management Studio (SSMS) ou appliquez une modification équivalente à l'aide de PowerShell, TMSL ou XMLA.
Lorsque vous créez un modèle de données à l'aide de SSDT, le paramètre Mode DirectQuery est défini par défaut sur Off (voir la figure 3). Cela signifie que les données de toutes les tables sont importées dans un modèle de données en mode mémoire. Un aperçu des données s'affiche en mode Grille.
Figure 3 : La propriété DirectQuery Mode est disponible au niveau du modèle dans Visual Studio
Pour basculer le modèle de données vers DirectQuery, définissez la propriété Mode DirectQuery sur On (voir la figure 4). Dans la vue Grille, les tables n'affichent aucune donnée. Dans l'aperçu de la grille de données, toutes les mesures sont vides, car le modèle n'a plus de données en mémoire.
Mode DirectQuery
Figure 4 Basculement de la propriété sur On ; l'aperçu des données n'est plus visible dans la vue Grid
Nous vous recommandons vivement de définir le mode DirectQuery sur On avant d'importer la table en mémoire. Si le mode DirectQuery est désactivé lors de l'importation des tables, toutes ces données seront perdues dès que vous basculerez le mode DirectQuery sur Activé.
Pour parcourir les données dans la base de données de l'espace de travail :
1. Dans le menu Excel, sélectionnez Modéliser/Analyser
2. Dans la boîte de dialogue Analyser dans Excel, recherchez la section Mode de connexion DirectQuery, puis choisissez Vue complète des données (voir la figure 5). Par défaut, cette option est définie sur Exemple de vue de données, mais vous n'avez pas encore défini d'exemple de données.
Figure 5 : définition du mode de connexion DirectQuery
Une fois dans Excel, vous pouvez parcourir le modèle de données à l'aide d'un tableau croisé dynamique, comme vous le faites avec un modèle de données en mode mémoire. Vous remarquerez que les performances sont médiocres, car la solution n'a pas été optimisée pour
DirectQuery. Par exemple, l'actualisation du tableau croisé dynamique illustré à la figure 6 peut prendre plusieurs secondes (environ 30 secondes est normale, mais vous pouvez attendre plus d'une minute, en fonction de votre configuration SQL Server).
Figure 6 Tableau croisé dynamique utilisant la vue complète des données du mode de connexion DirectQuery
Ce premier exemple se comporte comme prévu. Il est lent, car le moteur Analysis Services nécessite que SQL Server gère la plupart des calculs. L'optimisation du calcul devient une question d'optimisation de la base de données SQL Server pour la charge de travail typique produite par DirectQuery, ce qui est très différent du type de requêtes envoyées par un modèle de données en mode mémoire à traiter dans une base de données en mémoire. Un index columnstore peut être utile dans cette situation, et nous fournirons plus de détails à ce sujet plus loin dans ce livre blanc
En outre, il n'existe pas de hiérarchie d'utilisateurs dans DirectQuery. Par exemple, le modèle tabulaire d'origine utilisé dans cet exemple dispose d'une hiérarchie Products dans la table Product. La hiérarchie n'est pas supprimée lorsque vous activez DirectQuery, mais elle n'est pas disponible lorsque vous parcourez les données à l'aide d'Excel, en raison des limitations décrites précédemment. Toutefois, les hiérarchies sont disponibles dans Power BI.
L'utilisation de DirectQuery dans l'environnement de développement peut s'avérer difficile si vous ne disposez pas de données et si toutes les requêtes exécutées pour tester le modèle sont particulièrement lentes. (Par exemple, vous pouvez avoir une source de données pour le développement qui ne fonctionne pas aussi bien que les données de l'environnement de production.) Pour cette raison, vous pouvez définir des partitions supplémentaires dans le modèle de données à utiliser comme exemples de données. Si vous fournissez des exemples de partitions de données, Analysis Services utilisera les partitions chargées en mémoire avec des exemples de données et affichera uniquement ce contenu à l'utilisateur. C'est à vous de définir le contenu à utiliser comme exemples de données dans chaque table. Par défaut, aucune table d'un modèle tabulaire ne contient d'exemples de données. Par conséquent, si vous essayez de vous connecter à nouveau à un modèle à l'aide de Model/Analyze dans le menu Excel et que vous choisissez l'exemple de vue de données pour le mode de connexion DirectQuery au lieu de la vue de données complètes dans la boîte de dialogue Analyser dans Excel, vous obtiendrez un tableau croisé dynamique avec uniquement une liste de mesures, de tables et de colonnes, mais sans aucune donnée. Dans la section suivante, vous allez apprendre à ajouter des exemples de données pour DirectQuery à vos tables.
Création d'exemples de données pour DirectQuery
Pour chaque table, vous pouvez définir une ou plusieurs partitions avec des exemples de données. Les partitions peuvent aider à réduire le temps de traitement. Les données de la table sont la somme de toutes ses partitions. Toutefois, un modèle s'exécutant en mode DirectQuery n'utilise qu'une seule partition pour identifier l'objet table correspondant au sein de la base de données relationnelle utilisée comme source de données. Cette table est marquée DirectQuery et une seule partition peut être marquée comme DirectQuery. Dans un modèle activé pour DirectQuery, toutes les autres partitions d'une table contiennent uniquement des exemples de données.
1. Ouvrez la fenêtre Gestionnaire de partitions en sélectionnant l' élément de menu Table/Partitions.
2. Sélectionnez la partition à copier, puis cliquez sur le bouton Copier.
3. Dans le champ Nom de la partition, renommez le suffixe de Copier en Exemple (l'exemple est une suggestion ; vous pouvez utiliser le nom).
4. Facultatif : Pour les tables volumineuses, appliquez un filtre à l'instruction SQL en cliquant sur le bouton SQL et en appliquant une condition WHERE. Cela charge uniquement un sous-ensemble de lignes dans la partition d'échantillonnage, ce qui réduit les besoins en mémoire et le temps de traitement des données.
Dans notre exemple, nous copions les données de toutes les tables dans une nouvelle partition exemple en suivant les trois premières étapes ci-dessus, et uniquement pour la table Sales, nous ajoutons une condition WHERE pour filtrer uniquement le premier jour de chaque mois (voir la figure 7).
Figure 7 Boîte de dialogue Gestionnaire de partitions avec un exemple de partition dans un modèle activé pour DirectQuery
Si vous souhaitez désactiver le mode DirectQuery, vous devez d'abord supprimer tous les exemples de partitions. Un modèle de données en mode mémoire ne peut pas contenir d'exemples de données.
1. Traitez les tables de la base de données de l'espace de travail en cliquant sur Modèle/Traiter/Traiter tout dans le menu.
2. Une fois les tables traitées, allez dans le menu Excel et sélectionnez Modéliser/Analyser.
3. Dans la boîte de dialogue Analyser dans Excel, accédez à la section Mode de connexion DirectQuery, puis choisissez à nouveau Vue complète des données.
4. Parcourez les données à l'aide d'un tableau croisé dynamique.
Les temps de réponse devraient être plus rapides qu'auparavant. En outre, les nombres sont plus petits que ceux observés dans l' affichage complet des données (voir la figure 8), car vous interrogez uniquement les partitions avec des exemples de données et vous n'utilisez pas DirectQuery dans ce tableau croisé dynamique.
Figure 8 Tableau croisé dynamique connecté au modèle tabulaire en mode DirectQuery
Une fois votre test terminé, vous pouvez déployer la base de données sur un serveur tabulaire. Cette action mettra simplement à jour les métadonnées, sans effectuer d'importation de données en mémoire. Dans ce cas, une opération de processus n'est pas nécessaire. Tous les utilisateurs utiliseront la base de données en mode DirectQuery, quel que soit le client qu'ils utilisent (Excel, Power BI ou autres).
Si vous n'avez qu'une seule partition par table dans votre modèle de données, vous pouvez activer et désactiver le mode DirectQuery après le déploiement. L'utilisation d'un modèle de données en mode mémoire est un moyen plus rapide de tester vos données et constitue une bonne alternative à l'utilisation d'exemples de partitions.
Savoir comment basculer entre DirectQuery et un modèle en mémoire pour tester vos données vous offre de la flexibilité. Par exemple, vous pouvez basculer un modèle tabulaire en mémoire vers DirectQuery, ou vous pouvez basculer un modèle créé à l'origine pour DirectQuery vers un modèle en mémoire. Dans ce dernier cas, tous les exemples de partitions existants doivent être supprimés afin de désactiver le mode DirectQuery.
Dans les sections suivantes, nous allons décrire comment définir DirectQuery après un déploiement à l'aide de SSMS de manière interactive ou en exécutant un script.
Vous pouvez modifier le mode DirectQuery de la base de données en ouvrant la boîte de dialogue Propriétés de la base de données : cliquez avec le bouton droit sur le nom de la base de données dans l'Explorateur d'objets et choisissez l'élément de menu Propriétés. La figure 9 montre les valeurs disponibles dans la propriété Mode par défaut :
• Importer : correspond au mode DirectQuery défini sur Off dans SSDT. Toutes les tables sont importées en mémoire et vous devez traiter la base de données pour afficher les données après avoir défini cette propriété.
• DirectQuery : correspond au mode DirectQuery défini sur On dans SSDT. Toutes les données chargées en mémoire pour les tables sont déchargées, la mémoire est libérée et toutes les requêtes suivantes s'exécutent en mode DirectQuery. Il n'est pas nécessaire de traiter la base de données une fois que vous avez activé le mode DirectQuery.
Figure 9 Tableau croisé dynamique connecté au modèle tabulaire en mode DirectQuery
La figure 9 montre une propriété appelée DataView par défaut. Ce paramètre définit la propriété DataView par défaut pour les partitions. La modification de cette propriété n'est pas utile si vous avez créé des partitions avec SSDT, car ces partitions ont toujours l' option DataView définie sur Complet ou Échantillon.
Si vous souhaitez modifier le paramètre du mode DirectQuery par le biais d'un script XMLA, vous pouvez utiliser le code décrit dans l'exemple suivant, où le nœud Mode par défaut peut être 0 ou 1 :
• 0 : correspond au mode par défaut défini sur Importer (le mode DirectQuery est désactivé)
• 1 : correspond au mode par défaut défini sur DirectQuery (le mode DirectQuery est activé)
<Alter xmlns="http://schemas.microsoft.com/analysisservices/2014/engine">
<DatabaseID>First Step DQ - no sample data</DatabaseID> <Model>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<xs:element>
<xs:complexType>
<xs:sequence>
<xs:element type="row"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:complexType name="row">
<xs:sequence>
<xs:element name="DefaultMode" type="xs:long" sql:field="DefaultMode" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:schema>
<row xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
<DefaultMode>1</DefaultMode>
</row>
</Model>
</Alter>
Si vous souhaitez modifier le paramètre du mode DirectQuery via un script PowerShell, vous pouvez utiliser le code décrit dans l'exemple suivant. Habituellement, vous mettrez ce code dans un script auquel vous passerez trois paramètres :
• Nom de l'instance SSAS
• Nom de la base de données
• Paramètre de mode par défaut ( Import ou DirectQuery)
param (
[ValidateNotNullOrEmpty()][string]$ssasInstanceName,
[ValidateNotNullOrEmpty()][string]$databaseName,
[ValidateSet('Import','DirectQuery')][string]$defaultMode="" )
[System.Reflection.Assembly]::LoadWithPartialName(
"Microsoft.AnalysisServices.Tabular")
$svr = new-Object Microsoft.AnalysisServices.Tabular.Server
$svr.Connect($ssasInstanceName)
$database = $svr.databases
$db = $database.GetByName($databaseName)
$db.Model.DefaultMode = $defaultMode
$db.Model.SaveChanges()
Par exemple, vous pouvez définir le mode DirectQuery pour la base de données First Step DQ sur l' instance TAB16 du serveur local à l'aide de la commande suivante :
.\Set-DirectQueryMode.ps1 $ssasInstanceName'LOCALHOST\TAB16' $databaseName'First Step DQ' $defaultMode'DirectQuery'
Pour revenir à un modèle de données en mode mémoire, il suffit de changer le dernier paramètre en Importer :
.\Set-DirectQueryMode.ps1 $ssasInstanceName'LOCALHOST\TAB16' $databaseName'Première étape DQ' $defaultMode'Importer'
Lorsque vous utilisez DirectQuery, vous pouvez définir la sécurité à deux endroits. Tout d'abord, vous pouvez utiliser les rôles de sécurité définis dans Analysis Services, comme vous le faites avec les modèles de données en mode mémoire. Vous pouvez également appliquer la sécurité à la source de données relationnelle en demandant à Analysis Services d'emprunter l'identité de l'utilisateur actuel lorsqu'il envoie des requêtes SQL à la source de données. Habituellement, vous choisissez l'une ou l'autre technique, mais rien ne vous empêche de combiner les deux techniques.
Si vous souhaitez vous appuyer sur la sécurité standard basée sur les rôles fournie par Analysis Services, vous devez savoir que toutes les requêtes SQL incluront les prédicats et les jointures nécessaires pour récupérer uniquement les données requises. De plus, n'oubliez pas que, lors de l'utilisation de DirectQuery, il existe des restrictions aux expressions DAX qui peuvent être utilisées dans les filtres de rôle. Ces mêmes restrictions s'appliquent aux colonnes calculées.
Si vous avez déjà implémenté la sécurité au niveau des lignes dans la base de données relationnelle et que vous utilisez également la sécurité intégrée Windows, vous devez configurer Analysis Services pour emprunter l'identité de l'utilisateur actuel, comme décrit dans la section suivante.
Un modèle tabulaire en mode DirectQuery peut se connecter à SQL Server de deux manières :
• En utilisant toujours le même utilisateur (défini dans la connexion).
• En empruntant l'identité de l'utilisateur qui interroge Analysis Services.
Cette dernière option nécessite qu'Analysis Services soit configuré pour la délégation contrainte Kerberos, comme expliqué à l'adresse
La section suivante se concentre sur la configuration du comportement souhaité sur Analysis Services.Lorsque vous utilisez DirectQuery, le modèle dispose d'une seule connexion, qui a une configuration particulière pour l'emprunt d'identité (il s'agit de la propriété impersonationMode dans le fichier JSON, mais elle est appelée Informations d'emprunt d'identité dans SSMS, et simplement Emprunt d'identité dans SSDT).
Ce paramètre de sécurité spécifie l'utilisateur dont Analysis Services empruntera l'identité lors de la connexion à la source de données. L'emprunt d'identité détermine quel utilisateur Windows exécutera le code dans Analysis Services lors de la connexion à la source de données. Si la base de données source prend en charge la sécurité intégrée de Windows, l'utilisateur dont l'identité a été usurpée s'affiche comme l'utilisateur qui accède aux données de la source de données relationnelle et les consomme. Si la base de données source ne prend pas en charge la sécurité intégrée Windows, ce paramètre n'est pas pertinent.
Par exemple, la base de données source peut être une base de données SQL Server à l'aide de la sécurité intégrée. Dans ce cas, si Analysis Services emprunte l'identité de l'utilisateur actuel, SQL Server reçoit des requêtes de différents utilisateurs et peut fournir des résultats différents pour les mêmes demandes en fonction de l'utilisateur lui-même. Si vous disposez de SQL Server 2016, il s'agit d'une fonctionnalité disponible avec la sécurité au niveau des lignes sur la base de données relationnelle, comme décrit à l'adresse
• Dans le volet Explorateur d'objets, cliquez avec le bouton droit de la souris sur la connexion à la base de données.
• Dans la boîte de dialogue Propriétés de connexion, modifiez les propriétés de connexion SSMS (voir Figure 10).
• Dans la section Paramètres de sécurité, cliquez sur le menu déroulant à droite de
Usurper l'identité d'un ServiceAccount.
• Dans la boîte de dialogue Informations d'emprunt d'identité, sélectionnez Utiliser les informations d'identification de l'utilisateur actuel (voir Figure 11).
• Cliquez sur OK pour fermer chaque boîte de dialogue.
Figure 10 Informations d'emprunt d'identité dans les paramètres de sécurité des propriétés de connexion SSMS.
Figure 11 Options disponibles dans la boîte de dialogue Informations sur l'emprunt d'identité
Votre instance Analysis Services utilisera désormais un utilisateur différent pour chaque connexion établie à SQL Server. Il emprunte l'identité de l'utilisateur connecté à Analysis Services pour chaque requête. Vous pouvez vérifier ce comportement en vérifiant la colonne NTUserName dans SQL Profiler lors de la surveillance des requêtes SQL reçues par SQL Server.
Si vous utilisez une version de SQL Server antérieure à SQL Server 2016, vous pouvez utiliser une autre technique pour implémenter la sécurité au niveau des lignes basée sur les autorisations attribuées aux schémas. Cette méthode crée des vues identiques pour les schémas qui sont utilisés par défaut par plusieurs utilisateurs.
Ces étapes supposent que vous utilisez la table de modèle d'origine créée dans le schéma dbo (schéma par défaut pour chaque nouvelle base de données SQL Server). Si vous utilisez un schéma différent, utilisez le nom de votre schéma pour remplacer « dbo » dans les instructions suivantes :
1. Définissez un schéma sur SQL Server pour chaque groupe d'utilisateurs.
2. Définissez un utilisateur sur SQL Server pour qu'il corresponde à chaque utilisateur que vous autoriserez sur Analysis Services à accéder au modèle tabulaire publié en mode DirectQuery.
3. Attribuez à chaque utilisateur de SQL Server créé de cette manière le schéma correspondant (du groupe auquel il appartient) en tant que schéma par défaut. Chaque utilisateur appartient à un groupe. Pour chaque utilisateur, attribuez le schéma du groupe en tant que schéma par défaut.
4. Pour chaque schéma, accordez l'autorisation SELECT à tous les utilisateurs du groupe (c'est-à-dire aux utilisateurs qui voient les mêmes lignes de données).
5. Pour chaque table du schéma dbo que vous référencez dans le modèle tabulaire, créez une vue SQL portant le même nom dans chaque schéma. Cette vue doit inclure une condition WHERE qui filtre uniquement les lignes qui doivent être visibles par ce groupe d'utilisateurs.
6. Dans le modèle tabulaire, attribuez une instruction SQL au lieu d'une liaison de table directe à chaque partition DirectQuery et supprimez toutes les références aux schémas dans vos requêtes SQL.
Le résultat est le suivant :
• Analysis Services utilise les informations d'identification de l'utilisateur pour les requêtes SQL Server.
• L'utilisateur dispose d'un schéma par défaut qui utilise des vues portant le même nom que les tables ou les vues d'origine.
• Le schéma dispose d'une condition WHERE supplémentaire qui filtre uniquement les lignes que l'utilisateur est autorisé à voir.
• La requête SQL générée par Analysis Services utilise ces vues personnalisées pour retourner uniquement les lignes que l'utilisateur peut voir.
• Pour en savoir plus sur une autre implémentation de la sécurité au niveau des lignes (basée sur une approche plus dynamique basée sur les données), consultez :
Optimisation de DirectQuery
Comme expliqué précédemment, l'optimisation de DirectQuery implique le réglage de la source de données relationnelle pour répondre rapidement aux requêtes générées par DirectQuery. Afin d'optimiser un modèle DirectQuery, vous devez :
• Comprenez la charge de travail imposée à la source de données relationnelle par votre code DAX.
• Optimisez la base de données relationnelle pour la charge de travail spécifique.
Avant d'utiliser DirectQuery dans votre projet :
• Prenez le temps de construire un prototype.
• Rassemblez les requêtes générées pour vos formules DAX.
• Prenez le temps de comprendre les requêtes.
Comprendre vos requêtes et la charge de travail générée par vos calculs spécifiques vous aidera à décider comment créer un modèle de données relationnelles optimal.
Les exemples des sections suivantes utilisent Microsoft SQL Server comme source de données cible, mais ils sont également pertinents pour d'autres bases de données relationnelles prises en charge.
Présentation de la gestion des types de données dans DirectQuery
Notre premier exemple est un calcul simple pour montrer à quel point il est important de comprendre DirectQuery.
Imaginez que vous ayez une table de faits avec quelques milliards de lignes. Dans la table de faits, vous disposez d'une simple colonne Quantité contenant la quantité vendue de produits spécifiques. Étant donné que les quantités, prises individuellement, sont de petits nombres, vous stockez la colonne Quantité en SQL à l'aide d'un INT, qui est une colonne entière de 32 bits. Ensuite, vous créez une mesure DAX simple qui calcule les éléments suivants :
SumOfQuantity := SUM ( Sales[Quantity] )
Cette mesure fonctionne sans problème sur une petite table de faits, mais pas sur une grande. Pour calculer la somme, le moteur SSAS enverra une requête SQL comme celle-ci :
SELECT SUM ( Sales.Quantity ) AS A0 FROM Sales
Étant donné que la colonne Quantité est un entier, SQL Server utilise des routines d'entiers pour calculer la somme, ce qui, sur une table volumineuse, est susceptible d'entraîner un dépassement de capacité. En fait, sur un grand modèle, cette mesure ne parvient généralement pas à calculer le résultat. (Remarque : lors de l'utilisation du mode en mémoire (VertiPaq), l'exemple de modèle de données et le calcul ci-dessus fonctionnent très bien. En fait, les modèles de données en mode mémoire stockent généralement les valeurs à l'aide d'un système de type de données plus simple conçu pour éviter les erreurs de dépassement de capacité. Par exemple, le seul type de données du mode en mémoire pour stocker un entier est un entier 64 bits, tandis que l'enregistrement et le stockage de nombres plus petits sont gérés à l'aide de techniques de compression au lieu d'utiliser un type de données plus petit.)
SQL Server, en revanche, dispose de différents types de données pour optimiser le stockage et utilise des routines mathématiques conçues pour fournir les meilleures performances à l'aide des colonnes des tables qu'il analyse. Cette petite différence sémantique rend le système moins utile, car vous ne pouvez rien calculer par-dessus.
Options de gestion du scénario décrit ci-dessus :
• Stockez la colonne Quantité dans SQL Server à l'aide d'une colonne BIGINT. Cela enfreint clairement certaines bonnes pratiques en matière de base de données relationnelle, car cela implique l'utilisation d'un type de données volumineux pour stocker une valeur qui, en réalité, n'a pas besoin d'être aussi grande.
• Utilisez une vue pour convertir la quantité de INT en BIGINT. Cela évite de gaspiller de l'espace de stockage, tout en montrant au modèle un BIGINT.
Cette dernière option peut sembler la plus raisonnable, mais elle comporte beaucoup de complexité. Le problème lié à l'utilisation d'une vue pour effectuer la conversion de type de données est que la vue est appelée plusieurs fois pour chaque évaluation de la mesure. Si les utilisateurs parcourent le modèle à l'aide d'Excel ou de Power BI, vous pouvez vous attendre à ce que ces évaluations se produisent des milliers de fois par jour. Cela impose une lourde charge de travail à SQL Server pour effectuer les conversions de types de données.
La première option (utiliser un BIGINT pour stocker la quantité) élimine le problème des conversions de types de données au moment de la requête et produit de bien meilleurs plans de requête. Cependant, ce modèle n'est pas optimal du point de vue SQL.
Aucune des deux options n'est parfaite. Pour les tables SQL Server, nous vous suggérons d'utiliser des types de données suffisamment volumineux pour contenir les résultats des calculs au lieu de la valeur individuelle, car cela produit généralement de meilleurs plans de requête et un chemin d'exécution plus rapide. Afin de réduire l'espace de stockage requis par SQL Server, vous pouvez toujours envisager des techniques de compression et des index columnstore en cluster.
Si vous comprenez le fonctionnement de DirectQuery, vous pouvez optimiser la configuration de votre base de données relationnelle pour l'utilisation de DirectQuery. Prendre des décisions correctes sur des éléments tels que les types de données à utiliser peut vous faire gagner beaucoup de temps et d'itérations sur l'ensemble du processus ETL.
Requête simple sur un schéma en étoile et sur des schémas en flocon de neige
Cet exemple montre des requêtes SQL exécutées pour résoudre une requête DAX simple qui calcule la somme du montant des ventes, le découpe par année civile et ajoute un filtre sur une colonne. Il utilise deux dimensions et une table de faits dans un schéma en étoile de base :
EVALUATE
CALCULATETABLE (
ADDCOLUMNS (
ALL ( 'Date'[Calendar Year] ),
"Sales", [Sales Amount]
),
Currency[Currency Code] = "USD"
)
Cette requête génère deux requêtes SQL :
• Le premier récupère simplement la liste des années.
• La seconde renvoie les résultats réels.
SELECT TOP (1000001)
[t0].[Calendar Year]
FROM
( SELECT * FROM Date ) AS [t0]
GROUP BY
[t0].[Calendar Year]
SELECT TOP ( 1000001 )
[t0].[Calendar Year],
SUM ( [t1].[Line Amount] ) AS [a0]
FROM
( SELECT * FROM Sales ) AS [t1]
LEFT OUTER JOIN ( SELECT * FROM Date ) AS [t0]
ON [t1].[OrderDateKey] = [t0].[DateKey]
LEFT OUTER JOIN ( SELECT * FROM Currency ) AS [t2]
ON [t1].[CurrencyKey] = [t2].[CurrencyKey]
WHERE
[t2].[Currency Code] = N'USD'
GROUP BY
[t0].[Calendar Year]
Deux requêtes sont nécessaires, car le moteur doit renvoyer les années pour lesquelles il n'y a pas de ventes, qui ne sont pas incluses dans la deuxième requête.
Le type JOIN utilisé dans les requêtes varie selon que vous avez créé ou non les relations dans le modèle de données tabulaires en faisant confiance à l'intégrité référentielle. En règle générale, il est toujours judicieux d'utiliser des modèles de données avec l'intégrité référentielle activée, afin que la jointure devienne une jointure interne. Cela fournit au moteur de meilleures options pour définir le meilleur plan d'exécution.
Remarque : SSAS peut supposer qu'une relation a une intégrité référentielle lorsque la propriété relyOnReferentialIntegrity est définie sur true dans l'objet de relation du modèle tabulaire. Depuis décembre 2016, SQL Server Data Tools (SSDT) ne prend pas en charge cette propriété. Vous pouvez modifier le fichier model.bim en ajoutant la propriété relyOnReferentialIntegrity, comme vous pouvez le voir dans le code suivant. La modification des relations dans SSDT remplace le paramètre relyOnReferentialIntegrity, vous devez donc appliquer à nouveau ce paramètre si vous modifiez la relation dans SSDT. Ce problème disparaîtra dès que SSDT prendra en charge le paramètre relyOnReferentialIntegrity dans la boîte de dialogue Modifier la relation.
"relationships": [
{
"name": "84f32bea-9e5f-4c0b-9421-d8e6ef30ef10",
"fromTable": "Sales",
"fromColumn": "ProductKey",
"toTable": "Product",
"toColumn": "ProductKey",
"relyOnReferentialIntegrity": true }, {
... {
...
Remarque : la dernière version du moteur implémente l'élimination redondante de JOIN, qui vise à réduire le nombre de requêtes dans la mesure du possible. En effet, en réécrivant la requête DAX pour tirer parti de la nouvelle fonction SUMMARIZECOLUMNS, vous pouvez éviter la requête qui récupère les années :
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
'Date'[Calendar Year],
"Sales", [Sales Amount]
),
Currency[Currency Code] = "USD"
)
Ce comportement est également observé lors de l'exécution des mêmes requêtes sur le modèle de données en mode mémoire. Il n'y a pas de différences notables entre la requête exécutée sur le moteur de stockage en mode mémoire et celles sur SQL.
Si, au lieu d'utiliser un schéma en étoile simple, vous disposez d'un schéma en flocon de neige (qui comporte deux dimensions ou plus liées l'une à l'autre), la requête devient légèrement plus complexe avec des jointures supplémentaires entre les dimensions. Dans l'exemple suivant, au lieu de découper par année civile, qui est un attribut d'une dimension directement liée à la table de faits, nous découpons par catégorie, qui est une dimension liée à la sous-catégorie de produit, et enfin à Produit, qui, à son tour, se rapporte à la table de faits des ventes :
EVALUATE
CALCULATETABLE (
ADDCOLUMNS (
ALL ( 'Product Category'[Category] ),
"Sales", [Sales Amount]
),
Currency[Currency Code] = "USD" )
Le schéma est très similaire au précédent. La différence réside dans des jointures supplémentaires avec la sous-catégorie de produit et la catégorie de produit, comme indiqué dans le code SQL suivant :
SELECT TOP (1000001)
[t6].[Category],
SUM([t1].[Line Amount]) AS [a0]
FROM
(SELECT * FROM Sales ) AS [t1]
LEFT OUTER JOIN (SELECT * FROM Currency ) AS [t2]
ON ([t1].[CurrencyKey] = [t2].[CurrencyKey])
LEFT OUTER JOIN (SELECT * FROM Product ) AS [t5]
ON ([t1].[ProductKey] = [t5].[ProductKey])
LEFT OUTER JOIN (SELECT * FROM [Product Subcategory] ) AS [t7]
ON ([t5].[ProductSubcategoryKey] = [t7].[ProductSubcategoryKey])
LEFT OUTER JOIN (SELECT * FROM [Product Category] ) AS [t6]
ON ([t7].[ProductCategoryKey] = [t6].[ProductCategoryKey]) WHERE
[t2].[Currency Code] = N'USD'
GROUP BY
[t6].[Category]
Comme vous pouvez le voir à partir de ces exemples, une bonne base de données SQL doit répondre rapidement à ces requêtes et, afin d'optimiser son comportement, vous pouvez simplement suivre les meilleures pratiques bien connues pour la modélisation d'un entrepôt de données dans une base de données relationnelle.
Comme décrit dans les exemples précédents, l'application d'un filtre à une colonne qui est directement mappée à une colonne SQL entraîne l'application du filtre à la colonne de base de données SQL. Il est intéressant de voir ce qui se passe si, au lieu de filtrer une colonne physique, vous appliquez le filtre à une colonne calculée. Dans DAX, une colonne calculée n'est pas différente d'une colonne physique, mais nous avons appris dans les sections précédentes que les colonnes calculées en mode DirectQuery sont gérées en injectant le code SQL qui calcule la colonne dans la requête.
Ainsi, vous définissez une colonne calculée avec le code suivant :
Sales[Calc Line Amount] = Sales[Net Price] * Sales[Quantity]
Ensuite, vous pouvez appliquer un filtre à la colonne d'une requête :
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS ( 'Date'[Calendar Year], "Amount", [Sales Amount] ),
Sales[Calc Line Amount] > 100,
Currency[Currency Code] = "USD" )
Voici le code SQL résultant :
SELECT TOP (1000001)
[t0].[Calendar Year],
SUM([t1].[Line Amount]) AS [a0]
FROM
(SELECT
[t1].[CurrencyKey] AS [CurrencyKey],
[t1].[OrderDateKey] AS [OrderDateKey],
[t1].[Line Amount] AS [Line Amount],
[t1].[Net Price] * [t1].[Quantity] AS [Calc Line Amount] FROM
(SELECT * FROM Sales ) AS [t1]
) AS [t1]
LEFT OUTER JOIN (SELECT * FROM Date ) AS [t0]
ON [t1].[OrderDateKey] = [t0].[DateKey]
LEFT OUTER JOIN (SELECT * FROM Currency ) AS [t2]
ON [t1].[CurrencyKey] = [t2].[CurrencyKey]
WHERE
[t2].[Currency Code] = N'USD'
AND [t1].[Calc Line Amount] > CAST(N'100' AS MONEY)
GROUP BY
[t0].[Calendar Year];
L'optimiseur SQL Server place le filtre aussi près que possible de l'analyse de la table de faits. Vous pouvez voir ce comportement dans un modèle implémenté à l'aide d'index columnstore en cluster en examinant l'extrait suivant du plan de requête :
Figure 12 Le filtrage se produit à proximité de la table de faits
Néanmoins, étant donné que le filtre est effectué sur une expression au lieu d'une colonne physique, le moteur SQL doit analyser l'intégralité de la table de faits pour récupérer les lignes satisfaisant à la condition. La liste de sortie de l'analyse d'index Columnstore inférieure contient toutes les colonnes nécessaires à l'exécution de la requête, y compris les clés nécessaires à l'exécution de la jointure avec Devise et Date, et les colonnes nécessaires au calcul de la condition de filtre (qui est une multiplication de deux colonnes physiques : Quantité et Prix net).
Le moteur analyse le columnstore, qui récupère toutes les colonnes nécessaires afin de pouvoir appliquer le filtre et utiliser les colonnes pour les jointures suivantes. Ce comportement est très différent de ce que vous voyez avec le mode en mémoire.
En fait, si vous exécutez la même requête en mode en mémoire, le résultat est calculé par cette requête xmSQL :
WITH
$Expr0 := 'Sales'[Net Price] * 'Sales'[Quantity] SELECT
'Date'[Calendar Year],
SUM ( @$Expr0 ) AS $Measure0
FROM 'Sales'
LEFT OUTER JOIN 'Date' ON 'Sales'[OrderDateKey]='Date'[DateKey]
LEFT OUTER JOIN 'Currency' ON 'Sales'[CurrencyKey]='Currency'[CurrencyKey] WHERE
'Currency'[Currency Code] = 'USD' VAND
'Sales'[Calc Line Amount] > 100
La principale différence, ici, est que le moteur en mode en mémoire analyse la table de faits et que le filtre est appliqué à une colonne physique, au lieu d'une expression. De plus, la jointure entre les dimensions et la table de faits est exécutée directement dans le moteur en mode mémoire, sans qu'il soit nécessaire de renvoyer les colonnes impliquées dans la jointure. De plus, comme le filtre se trouve sur une colonne physique, moins de lignes sont réellement analysées.
Résumons les sujets abordés jusqu'à présent concernant les filtres sur une colonne calculée :
• L'expression de la colonne calculée est évaluée à chaque exécution de la requête, pour l'ensemble de la table. Ainsi, des calculs complexes peuvent devenir coûteux.
• La table complète doit être analysée afin de calculer la colonne calculée, quelle que soit la restriction du filtre.
• Toutes les colonnes nécessaires pour les jointures ultérieures sont renvoyées à partir de l'analyse de la table de faits, car le filtrage des autres dimensions se produit plus tard.
Bien sûr, le coût de l'analyse de la table de faits est quelque peu constant, tandis que le coût supplémentaire de l'exécution des jointures et de l'évaluation du reste de la requête dépend strictement de la restriction du filtre. Par exemple, nous avons exécuté la requête précédente en changeant la valeur du filtre de 100 à 100 000 en quatre étapes, et nous avons exécuté la même requête sans filtre sur la colonne. Plus la valeur du paramètre est élevée, plus le nombre de lignes survivantes du filtre est petit. De plus, nous avons testé la même requête avec la colonne calculée et avec une colonne contenant les mêmes valeurs, mais préalablement calculée et stockée, de sorte qu'elle devienne une colonne physique. Les résultats sont intéressants, comme vous pouvez le voir dans le tableau suivant (le temps est en millisecondes) :
Table 3
Filtre
Calculé Colonne
Physique Colonne
Perf
Pas de filtre
202
190
1,06 fois
100
140
87
1,61x
1,000
92
33
2,78 fois
10,000
75
10
7,50 fois
100,000
73
10
7,30 fois
There are no rows in this table
Comme vous pouvez le constater, le filtrage d'une colonne physique est toujours plus rapide que le filtrage d'une colonne calculée, et, plus le filtre est restrictif, plus le gain de performances est important. Le temps fixe nécessaire à l'analyse de la table de faits, que vous payez toujours dans le cas d'une colonne calculée, devient un addendum plus important dans le filtre plus restrictif.
En conclusion, les colonnes calculées sont parfaites pour calculer les résultats (comme c'est très souvent le cas), mais elles ont un prix de performance beaucoup plus élevé lorsque vous superposez un filtre par-dessus. Dans ce cas, le précalcul de la colonne calculée dans la base de données relationnelle devient une bien meilleure alternative, même s'il augmente les besoins de stockage.
Utilisation de fonctions d'intelligence temporelle avec des mesures additives
Parmi les nombreuses techniques de filtrage, l'intelligence temporelle joue un rôle important, car elle est présente dans presque toutes les solutions analytiques. Il est donc important de comprendre comment le moteur va se comporter lors de l'utilisation de calculs d'intelligence temporelle. De plus, cet exemple nous permet de mieux analyser certaines des optimisations présentes dans le moteur concernant les calculs additifs.
Cet exemple simple utilise la fonction SAMEPERIODLASTYEAR :
DEFINE
MEASURE Sales[Sales PY] =
CALCULATE ( [Sales Amount], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
'Date'[Calendar Year Month],
'Date'[Calendar Year Month Number],
"Sales", [Sales Amount],
"Sales PY", [Sales PY]
),
'Date'[Calendar Year] = "CY 2008"
)
ORDER BY 'Date'[Calendar Year Month Number]
La requête renvoie les ventes et les ventes de l'année précédente, découpées par mois, pour l'année civile 2008. Au total, il renvoie 12 lignes. Dès que vous commencez à utiliser les fonctions d'intelligence temporelle, la requête devient beaucoup plus complexe à analyser et à comprendre ; pourtant, il fournit de bonnes informations sur la façon dont le moteur DirectQuery traite la requête. En fait, la requête précédente génère quatre requêtes SQL différentes, dont deux ont une certaine pertinence. Le premier récupère la granularité des ventes à la journée avec une très longue liste de dates comme filtre, qui inclut toute l'année 2007. Le second est identique, mais il récupère l'année 2008.
SELECT TOP (1000001)
[t0].[Date],
SUM ( [t1].[Line Amount] ) AS [a0]
FROM
(SELECT * FROM Sales ) AS [t1]
LEFT OUTER JOIN (SELECT * FROM Date ) AS [t0]
ON [t1].[OrderDateKey] = [t0].[DateKey]
WHERE
[t0].[Date] IN (
CAST(N'2007-01-19 00:00:00' AS DATETIME),
CAST(N'2007-09-02 00:00:00' AS DATETIME),
...
...
...
CAST(N'2008-12-07 00:00:00' AS DATETIME),
CAST(N'2008-04-20 00:00:00' AS DATETIME)
)
GROUP BY
[t0].[Date]
Note:
• La liste des dates est fournie sous forme de filtre directement dans la requête, à l'aide de CAST à partir d'une chaîne. Cela génère des requêtes très longues et, bien sûr, nécessitent un certain temps pour être analysées et optimisées. La durée de la requête dépend essentiellement du nombre de jours inclus dans la requête complète.
• Le filtre dans CALCULATETABLE filtre l'année 2008, mais, en raison de la présence de SAMEPERIODLASTYEAR, la liste des dates dans l'une des requêtes inclut l'année entière 2007. En fait, nous avons deux requêtes, une par an.
• SUMMARIZECOLUMNS valeurs demandées regroupées par mois, mais la requête récupère les données au niveau du jour.
Pourquoi la requête SQL est-elle si différente de la requête DAX d'origine ? Parce que le moteur a détecté que la mesure à calculer est additive. En fait, le calcul ne nécessite qu'un SUM, ce qui aboutit à un calcul complètement additif. En d'autres termes, le moteur sait qu'il peut calculer la somme d'un mois en additionnant les dates individuelles de ce mois. Cette hypothèse serait incorrecte si vous utilisez une mesure non additive comme un décompte distinct. Nous examinerons ce scénario plus tard, après avoir discuté des mesures additives, qui sont les plus courantes.
Le moteur a récupéré la valeur de la mesure à la granularité du jour pendant deux ans, 2007 et 2008, avec deux requêtes SQL différentes. C'est ce dont il a besoin de la part du moteur de stockage. Le moteur de formule, plus tard, agrégera les jours en mois en 2007 et en 2008, ce qui donnera le résultat qui inclut, en janvier 2008, les ventes de la même période de l'année précédente (qui est janvier 2007).
Il s'agit d'un comportement intéressant, car il montre que le moteur analyse l'additivité de la mesure et génère des requêtes, qui sont intelligentes en collectant des données à une granularité différente de celle demandée, en effectuant l'étape suivante d'agrégation dans le moteur de formules.
D'autres requêtes SQL peuvent compléter entièrement la résolution de la requête DAX, mais elles sont moins intéressantes.
Le point important est que si vous utilisez des fonctions d'intelligence temporelle avec des mesures additives, le moteur a récupéré 365 lignes de SQL Server pour produire un résultat avec 12 lignes pour la mesure PY des ventes. Ce comportement
(en allant au niveau le plus granulaire de la table Date ) semble se produire uniquement avec les fonctions d'intelligence temporelle prédéfinies. En fait, si vous redéfinissez la mesure à l'aide d'un calcul Time Intelligence personnalisé qui s'appuie uniquement sur des fonctions DAX standard, les requêtes SQL résultantes sont différentes. Par exemple, considérez la requête DAX suivante, qui fonctionne uniquement au niveau du mois et ne s'appuie pas sur des fonctions d'intelligence temporelle prédéfinies :
DEFINE
MEASURE Sales[Sales PY] =
SUMX (
VALUES ( 'Date'[Calendar Year Month Number] ),
CALCULATE (
[Sales Amount],
ALL ( 'Date' ),
FILTER (
ALL ( 'Date'[Calendar Year Month Number] ),
'Date'[Calendar Year Month Number]
= EARLIER ( 'Date'[Calendar Year Month Number] ) - 100
)
)
)
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
'Date'[Calendar Year Month],
'Date'[Calendar Year Month Number],
"Sales", [Sales Amount],
"Sales PY", [Sales PY]
),
'Date'[Calendar Year] = "CY 2008"
)
ORDER BY 'Date'[Calendar Year Month Number]
Bien que le code soit plus complexe, le moteur détecte toujours que la mesure est additive et utilise l'optimisation de la collecte, avec deux requêtes, des valeurs dans les années 2007 et 2008, en effectuant une nouvelle jonction entre les deux ensembles plus tard dans le moteur de formule.
Cette fois, le code SQL pour les ventes de l'année 2017 est le suivant :
SELECT TOP (1000001)
[t0].[Calendar Year Month Number],
SUM ( [t1].[Line Amount] ) AS [a0]
FROM
(SELECT * FROM Sales ) AS [t1]
LEFT OUTER JOIN (SELECT * FROM Date ) AS [t0]
ON [t1].[OrderDateKey] = [t0].[DateKey]
WHERE [t0].[Calendar Year Month Number] IN (
200707, 200708, 200712, 200701, 200702, 200705,
200709, 200710, 200711, 200703, 200704, 200706 )
GROUP BY
[t0].[Date]
Comme vous pouvez le voir, cette fois-ci, la requête ne renvoie que 12 lignes pour l'année 2017. Ainsi, il récupère 24 lignes pour produire un résultat contenant douze lignes (la requête pour 2018 est similaire dans les deux cas, car la mesure Sales n'utilise aucune fonction d'intelligence temporelle). Cette dernière requête semble plus optimisée que la précédente, avec le problème potentiel que les valeurs calculées pourraient ne pas être utiles pour effectuer différents niveaux d'agrégation. En fait, si le moteur obtient les valeurs au niveau le plus granulaire (qui est la date dans ce cas), il pourra utiliser le résultat pour effectuer une agrégation à une granularité inférieure, ce qu'il ne peut pas faire si les valeurs sont renvoyées à la granularité du mois.
Utilisation de fonctions d'intelligence temporelle avec des mesures non additives
Comme vous l'avez vu dans l'exemple précédent, si le moteur détecte qu'une mesure est additive, il rassemble les données à la granularité clé et effectue l'agrégation dans le moteur de formules, ce qui ressemble à un plan très intelligent. Que se passe-t-il si nous le forçons à recalculer la valeur en utilisant une mesure non additive comme, par exemple, un décompte distinct ?
Dans ce cas, le moteur ne peut pas s'appuyer sur les données au niveau le plus granulaire, il doit donc exécuter une requête pour chaque ligne du résultat. Vous pouvez utiliser des fonctions d'intelligence temporelle standard et personnalisées. La requête suivante utilise la fonction standard d'intelligence temporelle SAMEPERIODLASTYEAR :
DEFINE
MEASURE Sales[Customers] =
DISTINCTCOUNT ( Sales[CustomerKey] )
MEASURE Sales[Customers PY] =
CALCULATE ( [Customers], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
EVALUATE
SUMMARIZECOLUMNS (
'Date'[Calendar Year Number],
"Customers", [Customers],
"Customers PY", [Customers PY]
)
ORDER BY 'Date'[Calendar Year Number]
Avec un calcul Time Intelligence personnalisé, vous pouvez limiter la granularité de la requête au niveau de l'année :
DEFINE
MEASURE Sales[Customers] =
DISTINCTCOUNT ( Sales[CustomerKey] )
MEASURE Sales[Customers PY] =
CALCULATE (
[Customers],
ALL ( 'Date'[Calendar Year Number] ),
VAR PrevYear =
VALUES ( 'Date'[Calendar Year Number] ) - 1
RETURN
FILTER (
ALL ( 'Date'[Calendar Year Number] ),
'Date'[Calendar Year Number] = PrevYear
)
)
EVALUATE
SUMMARIZECOLUMNS (
'Date'[Calendar Year Number],
"Customers", [Customers],
"Customers PY", [Customers PY]
)
ORDER BY 'Date'[Calendar Year Number]
Dans les deux cas, la présence de DISTINCTCOUNT, qui n'est pas additif, oblige le moteur à exécuter plusieurs requêtes. Pour cette dernière requête DAX, il existe cinq requêtes SQL, une pour chaque année, qui ont toutes le même format :
SELECT
COUNT_BIG ( DISTINCT [t1].[CustomerKey] )
+ MAX (
CASE WHEN [t1].[CustomerKey] IS NULL THEN 1
ELSE 0
END
) AS [a0]
FROM
(SELECT * FROM Sales ) AS [t1]
LEFT OUTER JOIN ( SELECT * FROM Date ) AS [t0]
ON [t1].[OrderDateKey] = [t0].[DateKey]
WHERE
[t0].[Calendar Year Number] IN (2007)
AND [t0].[Calendar Year Number] IN (2006, 2010, 2005, 2009, 2007, 2008)
Dans ce cas, DirectQuery est similaire au mode en mémoire (VertiPaq), car les deux moteurs nécessitent l'exécution de plusieurs analyses de la table de faits pour collecter les nombres distincts. Si la table de faits se trouve sur un index columnstore en cluster, les performances sont également très bonnes dans SQL Server. Ce type de calcul devient plus coûteux lorsque le nombre de lignes incluses dans le résultat est très élevé, en raison du nombre élevé de requêtes SQL exécutées (une pour chaque ligne du résultat).
Dans DAX, vous utilisez généralement l'un des deux modèles suivants pour les mesures semi-additives : LASTDATE et LASTNONBLANK. LASTDATE renvoie toujours la dernière date de l'ensemble, et il peut renvoyer des valeurs vides pour un mois incomplet, tandis que LASTNONEMPTY renvoie toujours les données pour la dernière date pour laquelle il y a une valeur à afficher. Il est bien connu que LASTNONEMPTY est plus lourd que LASTDATE. Comment DirectQuery se comporte-t-il avec ces deux modèles ?
L'exemple suivant montre une requête qui calcule le modèle LASTDATE :
DEFINE
MEASURE Sales[Sales Last Day] =
CALCULATE ( [Sales Amount], LASTDATE ( 'Date'[Date] ) ) EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
'Date'[Calendar Year Month Number],
"Sales", [Sales Amount],
"Sales Last Day", [Sales Last Day]
),
'Date'[Calendar Year Number] = 2008 )
ORDER BY 'Date'[Calendar Year Month Number]
Dans ce cas, le code SQL généré est très efficace, puisque la LASTDATE est calculée avant d'envoyer la requête à SQL, de sorte que le scan de la table de faits ne récupère que les données du dernier jour du mois dans la sélection :
SELECT TOP (1000001)
[t0].[Date],
SUM ( [t1].[Line Amount] ) AS [a0]
FROM
(SELECT * FROM Sales ) AS [t1]
LEFT OUTER JOIN (SELECT * Date ) AS [t0]
ON [t1].[OrderDateKey] = [t0].[DateKey]
WHERE
[t0].[Date] IN (
CAST(N'2008-06-30 00:00:00' AS DATETIME),
CAST(N'2008-08-31 00:00:00' AS DATETIME),
CAST(N'2008-03-31 00:00:00' AS DATETIME),
CAST(N'2008-04-30 00:00:00' AS DATETIME),
CAST(N'2008-01-31 00:00:00' AS DATETIME),
CAST(N'2008-02-29 00:00:00' AS DATETIME),
CAST(N'2008-05-31 00:00:00' AS DATETIME),
CAST(N'2008-09-30 00:00:00' AS DATETIME),
CAST(N'2008-10-31 00:00:00' AS DATETIME),
CAST(N'2008-07-31 00:00:00' AS DATETIME),
CAST(N'2008-11-30 00:00:00' AS DATETIME),
CAST(N'2008-12-31 00:00:00' AS DATETIME)
)
GROUP BY
[t0].[Date];
Lorsque vous utilisez LASTNONBLANK, en revanche, vous devez faire attention aux petits détails, pour accélérer l'exécution de la requête. Lorsque vous utilisez le mode en mémoire, vous utilisez généralement une SUM pour vérifier les blancs. Par exemple, dans le code suivant, nous mettons [Montant des ventes] pour vérifier le vide.
DEFINE
MEASURE Sales[Sales Last Day] =
CALCULATE (
[Sales Amount],
LASTNONBLANK ( 'Date'[Date], [Sales Amount] )
)
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
'Date'[Calendar Year Month Number],
"Sales", [Sales Amount],
"Sales Last Day", [Sales Last Day]
),
'Date'[Calendar Year Number] = 2008
)
ORDER BY 'Date'[Calendar Year Month Number]
Le moteur doit d'abord analyser la table de faits et calculer la mesure afin de recueillir la dernière date pour laquelle la mesure n'est pas vide. Cela se traduit par une requête supplémentaire qui renvoie la valeur de la mesure (Somme du montant de la ligne), qui est ensuite évaluée par le moteur de formules pour déterminer laquelle des nombreuses dates renvoyées est la dernière non vide :
SELECT TOP ( 1000001 )
[t0].[Date],
SUM ( [t1].[Line Amount] ) AS [a0]
FROM ( SELECT * FROM Sales ) AS [t1]
LEFT OUTER JOIN ( * FROM Date ) AS [t0]
ON [t1].[OrderDateKey] = [t0].[DateKey]
WHERE
[t0].[Date] IN (
CAST(N'2008-03-15 00:00:00' AS DATETIME),
CAST(N'2008-02-07 00:00:00' AS DATETIME),
CAST(N'2008-06-20 00:00:00' AS DATETIME),
--
-- all days for year 2008 included here
--
CAST(N'2008-07-27 00:00:00' AS DATETIME),
CAST(N'2008-12-25 00:00:00' AS DATETIME)
)
GROUP BY [t0].[Date]
Cette dernière requête SQL est lourde, car elle doit scanner deux colonnes (Date et Montant de la ligne) le long de l'ensemble de la table de faits, après avoir appliqué un filtre à la colonne de date. En réécrivant le code DAX pour éviter d'utiliser la mesure et l'effet de levier, et en utilisant à la place la fonction ISEMPTY, vous obtenez le modèle suivant :
DEFINE
MEASURE Sales[Sales Last Day] =
CALCULATE (
[Sales Amount],
LASTNONBLANK ( 'Date'[Date], CALCULATE ( NOT ISEMPTY ( Sales ) ) )
)
EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS (
'Date'[Calendar Year Month Number],
"Sales", [Sales Amount],
"Sales Last Day", [Sales Last Day]
),
'Date'[Calendar Year Number] = 2008
)
ORDER BY 'Date'[Calendar Year Month Number]
Désormais, au lieu d'utiliser [Montant des ventes] pour vérifier la présence de VIDE, nous utilisons NOT ISEMPTY, ce qui génère le code SQL suivant :
SELECT TOP (1000001)
[t0].[Date]
FROM
(SELECT * FROM Sales ) AS [t1]
LEFT OUTER JOIN (SELECT * FROM Date ) AS [t0]
ON ([t1].[OrderDateKey] = [t0].[DateKey])
WHERE
[t0].[Date] IN (
CAST(N'2008-03-15 00:00:00' AS DATETIME),
CAST(N'2008-02-07 00:00:00' AS DATETIME),
CAST(N'2008-06-20 00:00:00' AS DATETIME),
--
-- all days for year 2008 included here
--
CAST(N'2008-07-27 00:00:00' AS DATETIME),
CAST(N'2008-12-25 00:00:00' AS DATETIME)
)
GROUP BY
[t0].[Date]
Cette requête est plus rapide et plus facile à optimiser dans SQL Server, car elle utilise uniquement la colonne Date et n'a pas besoin d'analyser le montant de la ligne. Cette requête s'appuie sur la relation existante entre Date et Sales, ce qui permet d'obtenir un meilleur plan d'exécution.
Comme prévu, lors de l'utilisation d'un modèle DirectQuery, vous devez prêter attention aux détails du code DAX afin de permettre au moteur de générer des requêtes qui peuvent être mieux optimisées en SQL.
Afin de tester le comportement des relations plusieurs-à-plusieurs, nous utilisons un modèle différent qui contient les soldes de comptes. Chaque compte peut appartenir à plusieurs clients et, en même temps, chaque client peut avoir plusieurs comptes.
Du point de vue de la modélisation des données, cela se traduit par une table de pont entre les comptes et les clients, comme dans le modèle suivant :
La relation entre la table de pont et la table de compte est définie comme bidirectionnelle, afin de faciliter le flux du filtre du client au solde. La mesure qui en résulte est, bien sûr, non additive, selon la nature des relations plusieurs-à-plusieurs.
Dans ce scénario, une requête simple comme la suivante aboutit à une requête SQL complexe, car le filtre doit être propagé de Customer à AccountCustomer, puis à Account, et enfin à Balance :
ÉVALUER
SUMMARIZECOLUMNS ( Client[NomduClient], « Montant », [Montant de la somme] )
En fait, cette simple requête est traduite dans le code SQL suivant :
La requête est un peu longue, mais vous pouvez facilement la diviser en deux sous-requêtes plus simples :
1. La première partie sélectionne la somme des montants répartis par compte
2. La deuxième partie renvoie les noms des clients et leurs comptes
En joignant les deux sous-requêtes, vous obtenez la somme du solde de tous les comptes d'un client donné, ce qui est le résultat souhaité.
La question, en soi, n'est pas complexe et est raisonnable compte tenu de l'exigence. Néanmoins, il est important de noter que la complexité des requêtes augmente dès que vous marquez certaines relations comme bidirectionnelles. En effet, lorsque vous avez une relation bidirectionnelle, le modèle devient non additif chaque fois que vous appliquez un filtre qui traverse la relation bidirectionnelle. Lors de l'utilisation du mode en mémoire, le scénario est similaire. Le filtrage bidirectionnel s'accompagne de beaucoup de puissance, mais aussi de beaucoup de complexité.
Sur les petites bases de données, la requête est censée être très rapide. Sur les modèles plus grands, la taille de la table de pont (qui entraîne la complexité de la deuxième sous-requête de la sous-requête complète) peut poser problème. Encore une fois, l'objectif ici n'est pas de discuter des performances en détail, mais plutôt de vous donner une idée du type de requêtes générées pour résoudre le code DAX dans DirectQuery.
Dans les sections précédentes, nous avons analysé plusieurs requêtes afin de donner un aperçu du fonctionnement du moteur DirectQuery. Bien sûr, les situations du monde réel sont plus complexes, et nous vous recommandons d'effectuer une analyse complète de votre système à l'aide de nos exemples à titre de référence.
Il est important de comprendre comment DirectQuery et le mode en mémoire se comparent en termes de performances.
Pour tester cela, nous avons créé une base de données avec une table de faits contenant 4 milliards de lignes et exécuté plusieurs requêtes sur le même modèle en mode DirectQuery et en mode en mémoire. La table de faits est stockée sur un columnstore en cluster dans SQL Server, et les colonnes ont les types de données corrects, afin d'éviter toute interférence provenant de la conversion des types de données. De plus, les tests ont été exécutés sur la même machine.
Avis de non-responsabilité : ces tests n'ont pas été exécutés dans un environnement parfait et tous les scénarios possibles n'ont pas été exécutés, de sorte que vos résultats peuvent varier. Toutefois, ces chiffres peuvent aider à définir les attentes en matière de performances pour DirectQuery.
• Général : Nous avons remarqué que DirectQuery s'exécute deux à trois fois plus lentement que le mode en mémoire. En fonction de la requête individuelle et du nombre de jointures, la dégradation des performances due à l'adoption de DirectQuery variait de 1,5 à 2,9 fois. DirectQuery n'a jamais été plus rapide que le mode en mémoire, ce qui est normal.
• Le filtrage bidirectionnel a un coût énorme. Toutes les requêtes avec le filtrage bidirectionnel activé (dans le code ou dans le modèle) entraînaient une baisse des performances. Les requêtes DirectQuery s'exécutent dans une plage comprise entre 3x et 30x, en fonction de la complexité de la requête.
• L'intégrité référentielle est très importante. Dans la plupart des requêtes, nous avons pu les rendre plus rapides en indiquant simplement pour les relations que le moteur peut faire confiance à l'intégrité référentielle. En effet, le moteur utilise INNER JOIN au lieu de LEFT OUTER JOIN, ce qui donne à l'optimiseur SQL beaucoup plus d'options pour rapprocher les filtres de la table de faits, réduisant ainsi le nombre de lignes qu'il doit traiter.
Si vous viviez dans un monde parfait où la base de données relationnelle est suffisamment rapide pour fournir un résultat à n'importe quelle requête en moins d'une seconde, le choix d'utiliser DirectQuery ou non serait facile. En utilisant DirectQuery, il n'est pas nécessaire de copier et de traiter les données sur un moteur en mode mémoire (VertiPaq),
Analysis Services n'est qu'une couche sémantique au-dessus de la base de données relationnelle, et les performances sont garanties par le serveur qui exécute la seule copie des données. Temps de traitement zéro, latence zéro, données toujours à jour et moins de mémoire nécessaire pour Analysis Services.
Malheureusement, la réalité est loin d'être parfaite. Même en utilisant un index columnstore sur SQL Server, qui est basé sur la même technologie qu'un modèle de données en mode en mémoire, vous n'obtiendrez pas les mêmes performances qu'avec un modèle en mémoire dans Analysis Services. Ainsi, le choix entre DirectQuery et le mode en mémoire est une question de compromis et de priorités.
La principale raison d'utiliser DirectQuery est de réduire la latence entre les mises à jour sur la base de données relationnelle et la disponibilité des mêmes données dans le modèle tabulaire. DirectQuery supprime cette latence, mais il utilise le même moteur relationnel que celui utilisé pour mettre à jour les données. Cela signifie que la même base de données relationnelle gérera les mises à jour et les requêtes simultanées, de sorte que vous devez déterminer si le serveur sera en mesure de prendre en charge la charge de travail simultanée. L'utilisation du mode en mémoire est également un moyen de réduire la pression sur une base de données relationnelle, car un nombre croissant d'utilisateurs ne modifie pas la charge de travail sur la source de données. Si vous importez des données à l'aide du mode en mémoire, vous ne les lisez qu'une seule fois, quelles que soient les requêtes suivantes exécutées par les utilisateurs. De plus, gardez à l'esprit que la suppression de la latence (en temps réel) pose le problème de la cohérence du rapport, qui peut être difficile à expliquer aux utilisateurs.
Un autre scénario possible d'utilisation de DirectQuery est lorsque vous disposez d'une base de données trop volumineuse pour tenir dans la mémoire d'un seul serveur Analysis Services. Par exemple, les tables de plus de 100 milliards de lignes peuvent être difficiles à gérer en mode en mémoire, donc si vous ne pouvez pas réduire la cardinalité des données chargées en mémoire, vous pouvez utiliser des architectures particulières pour gérer le volume, par exemple à l'aide de Microsoft SQL Server Analytics Platform System (APS) ou de Microsoft Azure SQL Data Warehouse. Dans ces conditions, vous pouvez utiliser un modèle tabulaire Analysis Services en mode DirectQuery pour créer une couche sémantique afin d'accéder à une base de données très volumineuse. Vous ne vous attendez peut-être pas au même niveau de performance que celui auquel vous êtes habitué dans un scénario interactif, mais cette approche peut être plus rapide que toute autre option disponible, même si certaines requêtes nécessitent encore plusieurs secondes, voire quelques minutes.
Enfin, voici les deux principaux scénarios dans lesquels vous pouvez envisager d'utiliser DirectQuery :
• Une petite base de données qui est mise à jour fréquemment : la notion de « petite » dépend de la performance et de l'optimisation de la base de données relationnelle. Par exemple, une base de données SQL Server utilisant des index columnstore en cluster peut gérer beaucoup plus de données qu'une base de données basée sur des index classiques.
• Une base de données volumineuse qui ne tient pas en mémoire : même si ce scénario ne garantit pas une expérience utilisateur interactive lors de la navigation dans les données, comme vous pouvez vous y attendre avec la plupart des modèles en mode mémoire que vous pouvez créer dans Tabular, il peut être suffisant pour prendre en charge des scénarios où une requête peut s'exécuter en plusieurs secondes, voire minutes. La valeur sémantique fournie par Tabular est une valeur ajoutée importante pour la solution analytique.
Dans SQL 2016, DirectQuery est une option utile pour certains besoins très spécifiques. Il n'est pas destiné à résoudre tous les problèmes liés aux systèmes analytiques complexes, et il nécessite une connaissance approfondie de ses composants internes. Il ne s'agit pas d'un remplacement du moteur en mode inmemory, mais il constituera la base de nombreux systèmes d'analyse en temps réel.
Plus d'informations
Site Web de SQLBI
Site Web de SQL Server
Centre technique SQL Server
Centre de développement SQL Server https://msdn.microsoft.com/en-us/library/gg413422.aspx Référence DAXAnnexe A : Différences sémantiques dans DAX
Cette annexe répertorie les types de différences sémantiques auxquelles vous pouvez vous attendre et décrit les limitations qui peuvent s'appliquer à l'utilisation des fonctions ou aux résultats des requêtes. Une liste mise à jour de ces différences est disponible à l'adresse
Comparaisons
DAX dans les modèles en mémoire prend en charge les comparaisons de deux expressions qui se résolvent en valeurs scalaires de types de données différents. Toutefois, les modèles déployés en mode DirectQuery utilisent les types de données et les opérateurs de comparaison du moteur relationnel et peuvent donc renvoyer des résultats différents.
Les comparaisons suivantes renvoient toujours une erreur lorsqu'elles sont utilisées dans un calcul sur une source de données DirectQuery :
• Type de données numérique comparé à n'importe quel type de données de chaîne
• Type de données numérique par rapport à une valeur booléenne
• Tout type de données de chaîne par rapport à une valeur booléenne
En général, DAX est plus indulgent envers les incompatibilités de type de données dans les modèles en mémoire et tente une conversion implicite de valeurs jusqu'à deux fois, comme décrit dans cette section. Toutefois, les formules envoyées à un magasin de données relationnelles en mode DirectQuery sont évaluées de manière plus stricte, conformément aux règles du moteur relationnel, et sont plus susceptibles d'échouer.
Comparaisons de chaînes et de nombres
EXEMPLE : “2” < 3
La formule compare une chaîne de texte à un nombre. L'expression est true en mode DirectQuery et dans les modèles en mémoire.
Dans un modèle en mémoire, le résultat est vrai car les nombres sous forme de chaînes sont implicitement convertis en un type de données numérique pour les comparaisons avec d'autres nombres. SQL convertit également implicitement les numéros de texte en nombres pour la comparaison avec les types de données numériques.
Il s'agit d'un changement de comportement par rapport à la première version de PowerPivot, qui retournait false, car le texte « 2 » était toujours considéré comme supérieur à n'importe quel nombre.
EXEMPLE : “VERDADERO” = VRAI
Cette expression compare une chaîne de texte avec une valeur booléenne. En général, pour les modèles DirectQuery ou en mémoire, la comparaison d'une valeur de chaîne à une valeur booléenne entraîne une erreur. Les seules exceptions à la règle sont lorsque la chaîne contient le mot true ou le mot false ; si la chaîne contient des valeurs true ou false, une conversion en booléenne est effectuée et la comparaison a lieu donnant le résultat logique.
Comparaison des valeurs nulles
EXAMPLE: EVALUATE ROW("X", BLANK() = BLANK())
Cette formule compare l'équivalent SQL d'une valeur NULL à une valeur NULL. Il renvoie true dans les modèles en mémoire et DirectQuery ; une disposition est faite dans le modèle DirectQuery pour garantir un comportement similaire au modèle en mémoire.
Remarque : dans Transact-SQL, une valeur null n'est jamais égale à une valeur nulle. Toutefois, dans DAX, un blanc est égal à un autre blanc. Ce comportement est le même pour tous les modèles en mémoire. Le mode DirectQuery utilise la plupart de la sémantique SQL Server ; mais, dans ce cas, il s'en sépare en donnant un nouveau comportement aux comparaisons NULL.
Casts
Il n'existe pas de fonction de conversion en tant que telle dans DAX, mais des conversions implicites sont effectuées dans de nombreuses opérations de comparaison et d'arithmétique. C'est l'opération de comparaison ou d'arithmétique qui détermine le type de données du résultat.
Par exemple, les valeurs booléennes sont traitées comme numériques dans les opérations arithmétiques, telles que TRUE + 1, ou la fonction MIN appliquée à une colonne de valeurs booléennes. Une opération NOT renvoie également une valeur numérique.
Les valeurs booléennes sont toujours traitées comme des valeurs logiques dans les comparaisons et lorsqu'elles sont utilisées avec EXACT, AND, OR, &&, ou ||.
Lancer de la chaîne vers le booléen
Dans les modèles en mémoire et DirectQuery, les conversions sont autorisées en valeurs booléennes à partir des chaînes suivantes uniquement : « » (chaîne vide), « true », « false » ; où une chaîne vide est convertie à une valeur fausse.
La conversion vers le type de données booléen d'une autre chaîne entraîne une erreur.
Conversion de la chaîne en date/heure
En mode DirectQuery, les conversions de représentations de chaînes de dates et d'heures en valeurs datetime réelles se comportent de la même manière que dans SQL Server.
Les modèles qui utilisent le magasin de données en mémoire prennent en charge une plage plus limitée de formats de texte pour les dates que les formats de chaîne pour les dates pris en charge par SQL Server. Toutefois, DAX prend en charge les formats de date et d'heure personnalisés.
Conversion de la chaîne en d'autres valeurs non booléennes
Lors de la conversion de chaînes en valeurs non booléennes, le mode DirectQuery se comporte de la même manière que SQL Server. Pour plus d'informations, consultez CAST et CONVERT (Transact-SQL).
Conversion de nombres en chaîne non autorisée
EXEMPLE : CONCATÉNER(102, »,345 »)
La conversion de nombres en chaînes n'est pas autorisée dans SQL Server.
Cette formule renvoie une erreur dans les modèles tabulaires et en mode DirectQuery ; cependant, la formule produit un résultat dans PowerPivot.
Pas de prise en charge des conversions à deux essais dans DirectQuery
Les modèles en mémoire tentent souvent une deuxième conversion lorsque la première échoue. Cela ne se produit jamais en mode DirectQuery.
EXEMPLE : TODAY() + “13:14:15”
Dans cette expression, le premier paramètre est de type datetime et le second de type string. Cependant, les distributions lors de la combinaison des opérandes sont gérées différemment. DAX effectuera un cast implicite de string à double. Dans les modèles en mémoire, le moteur de formules tente de convertir directement la chaîne en double et, en cas d'échec, il tente de convertir la chaîne en datetime.
En mode DirectQuery, seule la conversion directe de la chaîne en double sera appliquée. Si ce cast échoue, la formule renverra une erreur.
Fonctions mathématiques et opérations arithmétiques
Certaines fonctions mathématiques renvoient des résultats différents en mode DirectQuery en raison de différences dans le type de données sous-jacent ou les conversions qui peuvent être appliquées dans les opérations. En outre, les restrictions décrites ci-dessus sur la plage de valeurs autorisée peuvent affecter le résultat des opérations arithmétiques.
Ordre d'addition
Lorsque vous créez une formule qui ajoute une série de nombres, un modèle en mémoire peut traiter les nombres dans un ordre différent de celui d'un modèle DirectQuery. Par conséquent, lorsque vous avez de nombreux nombres positifs et négatifs de grande taille, vous pouvez obtenir une erreur dans une opération et entraîner une autre opération.
Utilisation de la fonction POWER
EXEMPLE : PUISSANCE(-64, 1/3)
En mode DirectQuery, la fonction POWER ne peut pas utiliser de valeurs négatives comme base lorsqu'elle est élevée à un exposant fractionnaire . Il s'agit du comportement attendu dans SQL Server.
Dans un modèle en mémoire, la formule retourne -4.
Opérations de débordement numérique
Dans Transact-SQL, les opérations qui entraînent un dépassement numérique renvoient une erreur de dépassement ; Par conséquent, les formules qui entraînent un dépassement de capacité lèvent également une erreur en mode DirectQuery.
Cependant, la même formule, lorsqu'elle est utilisée dans un modèle en mémoire, renvoie un entier de huit octets. C'est parce que le moteur de formules n'effectue pas de vérifications pour les dépassements numériques.
SQL Server gère les valeurs null et les cases vides différemment du moteur xVelocity. Par conséquent, la formule suivante renvoie une erreur en mode DirectQuery, mais renvoie infinity (–inf) en mode en mémoire.
EXEMPLE : LOG(blank())
Les mêmes limitations s'appliquent aux autres fonctions logarithmiques : LOG10 et LN.
En mode DirectQuery, la division par zéro (0) ou la division par BLANK entraîne toujours une erreur. SQL Server ne prend pas en charge la notion d'infini et, comme le résultat naturel de toute division par 0 est l'infini, le résultat est une erreur. Toutefois, SQL Server prend en charge la division par des valeurs nulles, et le résultat doit toujours être égal à null.
Plutôt que de renvoyer des résultats différents pour ces opérations, en mode DirectQuery, les deux types d'opérations (division par zéro et division par null) renvoient une erreur.
Dans Excel et dans les modèles PowerPivot, la division par zéro renvoie également une erreur. La division par un BLANK renvoie un BLANK.
Les expressions suivantes sont toutes valides dans les modèles en mémoire, mais échouent en mode DirectQuery :
1/BLANK
1/0
0.0/BLANK
0/0
L'expression BLANK/BLANK est un cas particulier qui renvoie BLANK pour les modèles en mémoire et le mode DirectQuery.
Les formules dans le modèle tabulaire en mémoire sont soumises aux mêmes limitations qu'Excel en ce qui concerne les valeurs maximales autorisées pour les nombres réels et les dates. Toutefois, des différences peuvent survenir lorsque la valeur maximale est renvoyée à partir d'un calcul ou d'une requête, ou lorsque les valeurs sont converties, converties, arrondies ou tronquées.
Si les valeurs des types Currency et Real sont multipliées et que le résultat est supérieur à la valeur maximale possible, en mode DirectQuery, aucune erreur n'est générée et une valeur null est renvoyée.
Dans les modèles en mémoire, aucune erreur n'est levée, mais la valeur maximale est renvoyée.
En général, étant donné que les plages de dates acceptées sont différentes pour Excel et SQL Server, les résultats ne peuvent être garantis que lorsque les dates se trouvent dans la plage de dates commune, qui inclut les dates suivantes :
Date la plus ancienne : 1er mars 1990
Date la plus récente : 31 décembre 9999
Si des dates utilisées dans les formules se situent en dehors de cette plage, soit la formule entraînera une erreur, soit les résultats ne correspondront pas.
Valeurs en virgule flottante prises en charge par CEILING
EXEMPLE : ÉVALUER ROW(« x », CEILING(-4.398488E+30, 1))
L'équivalent Transact-SQL de la fonction DAX CEILING ne prend en charge que les valeurs d'amplitude de 10^19 ou moins. En règle générale, les valeurs à virgule flottante doivent pouvoir tenir dans bigint.
Datepart avec des dates qui sont hors plage
Il est garanti que les résultats en mode DirectQuery correspondent à ceux des modèles en mémoire uniquement lorsque la date utilisée comme argument se trouve dans la plage de dates valide. Si ces conditions ne sont pas remplies, soit une erreur est générée, soit la formule renvoie des résultats différents dans DirectQuery et en mode en mémoire.
EXEMPLE : MONTH(0) ou YEAR(0)
En mode DirectQuery, les expressions renvoient respectivement 12 et 1899.
Dans les modèles en mémoire, les expressions renvoient respectivement 1 et 1900.
EXEMPLE : EOMONTH(0.0001, 1)
Les résultats de cette expression ne correspondent que lorsque les données fournies en tant que paramètre se trouvent dans la plage de dates valide.
EXEMPLE : EOMONTH(blank(), blank()) ou EDATE(blank(), blank())
Les résultats de cette expression doivent être les mêmes en mode DirectQuery et en mode en mémoire.
Troncature des valeurs temporelles
EXEMPLE : SECOND(1231.04097222222)
En mode DirectQuery, le résultat est tronqué, conformément aux règles SQL Server, et l'expression est évaluée à 59.
Dans les modèles en mémoire, les résultats de chaque opération intermédiaire sont arrondis ; Par conséquent, la valeur de l'expression est égale à 0.
L'exemple suivant montre comment cette valeur est calculée :
1. La fraction de l'entrée (0,04097222222) est multipliée par 24.
2. La valeur horaire résultante (0,98333333328) est multipliée par 60.
3. La valeur minute résultante est 58,9999999968.
4. La fraction de la valeur minute (0,9999999968) est multipliée par 60.
5. La deuxième valeur résultante (59,999999808) est arrondie à 60.
6. 60 équivaut à 0.
Type de données SQL Time non pris en charge
Les modèles en mémoire ne prennent pas en charge l'utilisation du nouveau type de données SQL Time . En mode DirectQuery, les formules qui font référence à des colonnes avec ce type de données renvoient une erreur. Les colonnes de données temporelles ne peuvent pas être importées dans un modèle en mémoire.
Toutefois, il arrive que le moteur convertisse la valeur time en un type de données acceptable et que la formule renvoie un résultat.
Ce comportement affecte toutes les fonctions qui utilisent une colonne de date comme paramètre.
Monnaie
En mode DirectQuery, si le résultat d'une opération arithmétique est de type Currency, la valeur doit être comprise dans la plage suivante :
Minimum : -922337203685477.5808
Prix maximum : 922337203685477.5807
Combinaison de types de données monétaires et réelles
EXEMPLE : Échantillon de devise 1
Si les types Devise et Réel sont multipliés et que le résultat est supérieur à 9223372036854774784 (0x7ffffffffffffc00), le mode DirectQuery ne génère pas d'erreur.
Dans un modèle en mémoire, une erreur est générée si la valeur absolue du résultat est supérieure à 922337203685477.4784.
EXEMPLE : Exemple de devise 2
Si les opérations sur deux valeurs monétaires aboutissent à une valeur en dehors de la plage spécifiée, une erreur est générée dans les modèles en mémoire, mais pas dans les modèles DirectQuery.
La division des valeurs monétaires par des valeurs d'autres types numériques peut aboutir à des résultats différents.
Fonctions d'agrégation
Les fonctions statistiques d'une table comportant une ligne renvoient des résultats différents. Les fonctions d'agrégation sur des tables vides se comportent également différemment dans les modèles en mémoire qu'en mode DirectQuery.
Si la table utilisée comme argument contient une seule ligne, en mode DirectQuery, les fonctions statistiques telles que STDEV et VARx renvoient null.
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.