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.
