Support formation Rapports Power BI
3 | Modéliser (+ DAX)
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.
image.gif failed to upload
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.
image.gif failed to upload
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 ( Ventes, Ventes[Quantité] * Ventes[Prix unitaire] )
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 :
CHOISIR
SalesQuantity,
Prix unitaire
DE
Ventes
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 :
CHOISIR
SOMME ( SalesQuantity * UnitPrice ) AS Résultat
DE
Ventes
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.
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
Table 2
Source des données
Versions
Fournisseurs
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).
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.