Mode DirectQuery (SSAS Tabulaire)

Analysis Services vous permet de créer des rapports et des modèles tabulaires qui récupèrent des données et des agrégats directement à partir d'un système de base de données relationnelle, à l'aide du mode DirectQuery. Les avantages généralement cités en faveur du mode DirectQuery incluent la possibilité d'interroger des jeux de données très volumineux qui ne peuvent pas tenir en mémoire, ainsi que de disposer de données actualisées en temps réel.

Cette rubrique présente les différences entre les modèles tabulaires standard qui résident uniquement en mémoire et les modèles tabulaires qui peuvent interroger une source de données relationnelle, et explique comment créer et déployer un modèle utilisable en mode DirectQuery.

Sections de cette rubrique :

• Présentation du mode DirectQuery

• Création de modèles utilisables en mode DirectQuery

  • Sources de données pour les modèles DirectQuery

  • Restrictions de validation et de création pour le mode DirectQuery

  • Compatibilité des formules

  • Connexion aux modèles DirectQuery

  • Sécurité

• Propriétés DirectQuery

• Tâches et rubriques connexes

Présentation du mode DirectQuery

Par défaut, les modèles tabulaires utilisent un cache mémoire pour stocker et interroger les données. Étant donné que les modèles tabulaires utilisent des données qui résident dans la mémoire, même les requêtes complexes peuvent être extrêmement rapides. Toutefois, l'utilisation de données en mémoire cache présente des inconvénients :

• Les données ne sont pas actualisées lorsque des modifications sont apportées aux données sources. Vous devez traiter le modèle pour obtenir les mises à jour des données.

• Lorsque vous arrêtez l'ordinateur qui héberge le modèle, le cache est enregistré sur le disque et doit être rouvert lorsque vous chargez le modèle ou ouvrez le fichier PowerPivot. Les opérations de sauvegarde et de chargement peuvent être longues.

En revanche, un modèle tabulaire en mode DirectQuery utilise des données stockées dans une base de données SQL Server ou dans un entrepôt de données SQL Server PDW. Au moment de la conception, vous importez toutes les données ou un petit échantillon de ces dernières dans le cache et générez votre modèle comme à l'accoutumée. Lorsque vous êtes prêt à déployer le modèle, vous définissez le mode de fonctionnement sur DirectQuery. Après avoir modifié le mode de fonctionnement, toutes les requêtes sur le modèle utilisent la source de données relationnelle spécifiée (SQL Server ou SQL Server PDW), et non les données mises en cache.

Lorsque vous créez des rapports ou des requêtes sur le modèle, vous utilisez DAX, mais les requêtes DAX sont converties par Analysis Services en instructions Transact-SQL équivalentes sur la source de données relationnelle spécifiée.

Le déploiement d'un modèle à l'aide du mode DirectQuery présente de nombreux avantages :

• Il permet d'utiliser un modèle pour les ensembles de données qui sont trop volumineux pour tenir dans la mémoire du serveur Analysis Services.

• Les données sont à jour, et il n'y a aucune charge de gestion supplémentaire liée à la conservation d'une copie distincte des données. Les modifications apportées aux données sources sous-jacentes peuvent être immédiatement reflétées dans des requêtes sur le modèle de données.

• DirectQuery peut tirer parti de l'accélération des requêtes côté fournisseur, telle que celle fournie par les index de colonne optimisés en mémoire xVelocity. Les index columnstore xVelocity sont fournis à la fois dans SQL Server 2012 et SQL Server PDW, afin de prendre en charge les performances améliorées de DirectQuery.

• Toute sécurité imposée par la base de données principale est appliquée, à l'aide de la sécurité au niveau des lignes. En revanche, si vous utilisez des données en mémoire cache, il peut être difficile de garantir que le cache est sécurisé exactement comme sur le serveur.

• Si le modèle contient des formules complexes qui peuvent nécessiter plusieurs requêtes, Analysis Services peut procéder à une optimisation pour garantir que le plan de requête pour la requête exécutée sur la base de données principale est aussi efficace que possible.

Conception de modèles pour le mode DirectQuery

Tous les modèles tabulaires sont créés à l'aide du concepteur de modèles dans Outils de données SQL Server (SSDT), qui crée toujours les modèles en mémoire par défaut. Cela signifie que si vous utilisez des données qui sont trop volumineuses pour tenir en mémoire, vous devez envisager de travailler avec un sous-ensemble de données plus petit au moment de la conception, puis accéder à la source de données complète une fois le modèle déployé. Il existe plusieurs méthodes que vous pouvez utiliser avec un sous-ensemble de données plus petit :

• Lorsque vous ajoutez des données au modèle à l'aide de l'Assistant Importation de table, utilisez un filtre pour importer uniquement un sous-ensemble de données dans le cache utilisé par la base de données de l'espace de travail. Après avoir déployé le modèle, vous pouvez modifier la définition de la requête afin de supprimer ou de modifier le filtre.

• Utilisez une source de données intermédiaire et concevez le modèle tandis que les tables intermédiaires contiennent uniquement un sous-ensemble de données.

• Ajoutez des partitions à vos tables, afin de gérer la quantité de données à traiter simultanément.

Lorsque vous êtes prêt à passer en mode DirectQuery, vous pouvez modifier une propriété qui active le mode DirectQuery. Pour plus d'informations, consultez Activer le mode Création DirectQuery (SSAS Tabulaire).

Lorsque vous effectuez cette opération, le concepteur de modèles configure automatiquement la base de données de l'espace de travail pour qu'elle s'exécute en mode hybride, ce qui vous permet de continuer à utiliser les données en mémoire cache. Le concepteur de modèles vous informera également de toutes les fonctionnalités de votre modèle qui ne sont pas compatibles avec le mode DirectQuery. La liste suivante résume les principales conditions requises à garder à l'esprit :

• Sources de données : les modèles DirectQuery peuvent uniquement utiliser les données d'une source de données relationnelle. La source peut être une instance de SQL Server ou une instance de SQL Server PDW. Une fois que le mode DirectQuery a été activé pour le modèle, vous ne pouvez utiliser aucun autre type de données dans le concepteur de modèles, y compris les tables ajoutées par les opérations de copier-coller. Toutes les autres options d'importation sont désactivées. De plus, toute table incluse dans une requête doit faire partie de la source de données spécifiée. Pour plus d'informations, consultez Sources de données.

• Prise en charge des colonnes calculées : les colonnes calculées ne sont pas prises en charge pour les modèles DirectQuery. Toutefois, vous pouvez créer des mesures et des indicateurs de performance clés, qui s'exécutent sur des ensembles de données. Pour plus d'informations, consultez la section relative à la validation.

• Utilisation limitée des fonctions DAX : certaines fonctions DAX ne peuvent pas être utilisées en mode DirectQuery. Vous devez donc les remplacer par d'autres fonctions ou créer des valeurs à l'aide de colonnes dérivées dans la source de données. Le concepteur de modèles assure la validation au moment de la conception pour toutes les erreurs qui surviennent lorsque vous créez des formules qui ne sont pas compatibles avec le mode DirectQuery. Pour plus d'informations, consultez les sections suivantes : Validation.

• Compatibilité des formules : dans certains cas connus, la même formule peut retourner différents résultats dans un modèle mis en cache ou hybride comparé à un modèle DirectQuery qui utilise uniquement la banque de données relationnelle. Ces différences sont une conséquence des différences sémantiques entre le moteur d'analyse en mémoire xVelocity (VertiPaq) et SQL Server. Pour plus d'informations sur ces différences, consultez la section suivante : Compatibilité des formules.

• Sécurité : vous pouvez utiliser différentes méthodes pour sécuriser les modèles en fonction de la manière dont ils sont déployés. Les données mises en cache pour les modèles tabulaires sont sécurisées à l'aide du modèle de sécurité de l'instance Analysis Services. Les modèles DirectQuery peuvent être sécurisés à l'aide de rôles, mais vous pouvez également utiliser la sécurité définie dans la banque de données relationnelle. Le modèle peut être configuré de sorte que les utilisateurs qui ouvrent un rapport basé sur un modèle DirectQuery puissent uniquement voir les données que leurs autorisations dans SQL Server leur permettent de voir. Pour plus d'informations, consultez la section suivante : Sécurité.

• Restrictions client : lorsqu'un modèle est en mode DirectQuery, il ne peut être interrogé qu'à l'aide de DAX. Vous ne pouvez pas utiliser MDX pour créer des requêtes. Cela signifie que vous ne pouvez pas utiliser le client de tableau croisé dynamique Excel, car Excel utilise MDX.

  Toutefois, vous pouvez créer des requêtes sur un modèle DirectQuery dans SQL Server Management Studio si vous utilisez une requête de table DAX dans le cadre d'une instruction XMLA EXECUTE. Pour plus d'informations, consultez Référence syntaxique des requêtes DAX.

Lorsque vous avez résolu tous les problèmes de conception et testé votre modèle, vous êtes prêt pour le déploiement. À ce stade, vous pouvez définir la méthode recommandée pour répondre aux requêtes sur le modèle. Voulez-vous que les utilisateurs aient accès au cache, ou utilisent toujours uniquement la source de données relationnelle ?

Si vous déployez le modèle en mode hybride, le cache reste disponible et peut être utilisé pour les requêtes. Un mode hybride vous fournit de nombreuses options :

• Lorsque le cache et la source de données relationnelle sont disponibles, vous pouvez définir la méthode de connexion recommandée par défaut, mais en fin de compte le client contrôle la source utilisée, à l'aide de la propriété de chaîne de connexion DirectQueryMode.

• Vous pouvez également configurer des partitions du cache de telle façon que la partition principale utilisée pour le mode DirectQuery ne soit jamais traitée et doive toujours faire référence à la source relationnelle. Il existe plusieurs façons d'utiliser des partitions pour optimiser l'expérience de conception de modèle et de rapports. Pour plus d'informations, consultez Partitions et mode DirectQuery (SSAS Tabulaire).

• Une fois le modèle déployé, vous pouvez modifier la méthode de connexion par défaut. Par exemple, vous pouvez utiliser un mode hybride pour le test, et basculer le modèle en mode DirectQuery uniquement seulement après avoir testé complètement tous les rapports ou requêtes qui utilisent le modèle. Pour plus d'informations, consultez Définir ou modifier la méthode de connexion par défaut pour DirectQuery.

Sources de données pour les modèles DirectQuery

Dès que vous modifiez l'environnement de conception pour activer le mode DirectQuery, les sources de données pour la base de données de l'espace de travail sont validées pour garantir qu'elles proviennent d'une seule source de données relationnelle. Les données issues d'autres sources, notamment les données copiées-collées, ne sont pas autorisées dans les modèles DirectQuery.

Si vous avez l'intention d'utiliser le modèle en mode DirectQuery, vous devez vous assurer que toutes les données dont vous avez besoin pour les rapports sont stockées dans la source de données spécifiée. Si les données dont vous avez besoin pour la modélisation ne sont pas disponibles dans cette source, envisagez d'utiliser Integration Services ou d'autres outils de stockage des données pour importer les données dans la base de données ou l'entrepôt de données qui sert de source de données DirectQuery.

Restrictions de validation et de création pour le mode DirectQuery

Lorsque vous créez un modèle utilisable en mode DirectQuery, vous devez initialement charger une partie des données dans le cache. Si les données que vous allez utiliser par la suite sont trop volumineuses pour tenir en mémoire, vous pouvez utiliser l'option Afficher un aperçu et filtrer dans l'Assistant Importation de table pour sélectionner un sous-ensemble de données, ou écrire un script SQL pour obtenir les données souhaitées. Une autre option consiste à effectuer une copie intermédiaire d'un sous-ensemble des données d'un entrepôt de données, puis à augmenter la banque de données à l'issue de la phase de conception.

Attention
Étant donné que le mode DirectQuery ne prend pas en charge l'utilisation de colonnes calculées, vous devez supprimer ces dernières. Vous pouvez convertir des colonnes calculées en mesures, mais dans d'autres cas, vous pouvez générer des colonnes dérivées dans le cadre de votre requête ou script d'importation de données, ou les ajouter dans la source de données relationnelle.

Pour afficher et résoudre les erreurs de validation, ouvrez la Liste d'erreurs dans Outils de données SQL Server. Les erreurs critiques qui empêchent l'utilisation du mode DirectQuery sont affichées sous l'onglet Erreurs. Vous devez corriger ces erreurs avant de passer en mode DirectQuery. Les erreurs de validation les plus difficiles à résoudre sont généralement liées aux formules qui ne sont pas prises en charge en mode DirectQuery. Consultez la section Compatibilité des formules, pour obtenir une vue d'ensemble des erreurs liées aux formules et aux colonnes calculées.

La liste suivante décrit d'autres considérations à garder à l'esprit lorsque vous créez un modèle pour l'accès DirectQuery :

• Lorsque vous êtes en mode DirectQuery uniquement, les résultats d'un rapport peuvent varier selon le contexte de sécurité de l'utilisateur qui affiche les résultats. Vous devez tester les modèles avec des informations d'identification différentes pour garantir que les utilisateurs reçoivent les résultats attendus.

• Si vous configurez un modèle pour qu'il fonctionne en mode hybride, ce qui permet l'utilisation du cache ou des données de la source relationnelle, tenez compte de la possibilité que les clients voient des résultats différents en fonction du mode spécifié dans la chaîne de connexion. Si vous devez vous assurer que les utilisateurs de rapports voient uniquement les données de la source relationnelle, vous devez effacer le cache ou changer le modèle en DirectQueryOnly.

Compatibilité des formules pour les modèles DirectQuery

Certains modèles peuvent contenir des formules qui ne sont pas prises en charge en mode DirectQuery, et le modèle doit être refondu pour éviter des erreurs de validation. Les restrictions sur les formules qui sont prises en charge en mode DirectQuery sont les suivantes :

• Les colonnes calculées ne sont prises en charge dans aucun modèle tabulaire qui a le mode DirectQuery activé, même pas les modèles hybrides. Si vous avez besoin de colonnes calculées pour un modèle, envisagez de les convertir en colonnes dérivées à l'aide de Transact-SQL dans votre définition de l'importation.

• Les modèles DirectQuery prennent en charge l'utilisation de formules DAX pour une utilisation dans des mesures, qui sont converties en opérations basées sur un ensemble par rapport à la banque de données relationnelle. Les mesures que vous pouvez créer à l'aide de mesures implicites sont également prises en charge.

• Toutes les fonctions ne sont pas prises en charge. Étant donné qu'Analysis Services convertit toutes les formules DAX et définitions de mesure en instructions SQL en interrogeant un modèle DirectQuery, toute formule qui contient des éléments qui ne peuvent pas être convertis en Transact-SQL déclenche des erreurs de validation sur le modèle. Par exemple, les fonctions Time Intelligence ne sont pas prises en charge. Même les fonctions prises en charge peuvent se comporter différemment, comme les fonctions statistiques. Pour obtenir une liste complète des problèmes de compatibilité, consultez Compatibilité des formules en mode DirectQuery.

• Certaines formules du modèle peuvent se valider lorsque vous basculez le modèle en mode DirectQuery, mais retournent des résultats différents une fois exécutées sur le cache et sur la banque de données relationnelle. Cela est dû au fait que les calculs sur le cache utilisent la sémantique du moteur d'analyse en mémoire xVelocity (VertiPaq), qui contient de nombreuses fonctionnalités pour émuler le comportement d'Excel, alors que les requêtes sur des données stockées dans la banque de données relationnelle utilisent obligatoirement la sémantique de SQL Server. Pour obtenir la liste des fonctions DAX qui peuvent retourner des résultats différents lorsque le modèle est déployé en temps réel, consultez Compatibilité des formules en mode DirectQuery.

Connexion aux modèles DirectQuery

Les clients qui utilisent le langage de requête MDX ne peuvent pas se connecter aux modèles qui utilisent le mode DirectQuery. Si vous essayez de créer une requête MDX sur un modèle DirectQuery, vous obtiendrez une erreur indiquant que le cube est introuvable, ou n'a pas été traité. Vous pouvez créer diverses requêtes sur les modèles DirectQuery à l'aide de Power View, de formules DAX ou de requêtes XMLA. Pour plus d'informations sur l'exécution de requêtes ad hoc sur les modèles tabulaires, consultez Accès aux données de modèle tabulaire.

Si vous utilisez un modèle hybride, vous pouvez spécifier si les utilisateurs se connectent au cache ou utilisent les données de DirectQuery en spécifiant la propriété de chaîne de connexion DirectQueryMode.

Sécurité en mode DirectQuery

Pendant le processus de création de modèle, vous spécifiez les autorisations utilisées pour récupérer les données source. Il s'agit souvent de vos propres informations d'identification, ou d'un compte utilisé pour le développement. Toutefois, lorsque vous basculez le modèle pour qu'il utilise le mode DirectQuery, le contexte de sécurité est plus complexe :

• Déterminez si les utilisateurs disposent du niveau d'accès nécessaire aux données de la banque de données relationnelle.

• Les utilisateurs qui consultent le même modèle ou rapport peuvent voir des données différentes, selon le contexte de sécurité de l'utilisateur.

• Si le cache des modèles a été conservé, il est sécurisé à l'aide du modèle de sécurité (de rôles)Analysis Services. Le cache peut contenir des données que le concepteur de modèles est autorisé à consulter contrairement à l'utilisateur. Les concepteurs de modèles et de rapports doivent effacer le cache, ou sécuriser ces données en contrôlant l'accès par le biais de rôles.

• Un modèle qui répond aux requêtes du cache ne peut pas emprunter l'identité de l'utilisateur actuel lors de la connexion à la source de données. Si vous souhaitez emprunter l'identité de l'utilisateur actuel lors de la connexion à la source de données, vous devez utiliser le mode DirectQuery.

• Si votre modèle de rapport nécessite la mise en place d'une sécurité, vous avez deux options : utiliser des rôles Analysis Services, ou définir des autorisations au niveau des lignes sur la source de données. La sécurité dans la source de données relationnelle est utilisée pour contrôler l'accès aux tables, et la sécurité au niveau des colonnes n'est pas prise en charge. Par conséquent, si les utilisateurs dans une région n'ont pas l'autorisation d'afficher les chiffres des ventes de différentes régions, un rapport qui comprend une mesure en fonction de la table Sales retourne des espaces ou une erreur.

La propriété de paramètres d'emprunt d'identité spécifie les informations d'identification utilisées lorsque vous vous connectez à un modèle à l'aide de DirectQuery, que ce soit pour un modèle DirectQuery uniquement ou pour un modèle hydride répondant aux requêtes à l'aide de DirectQuery. La propriété possède les valeurs suivantes :

• Par défaut
  Utilise les informations d'identification spécifiées dans l'Assistant Importation pour se connecter à la source de données. Il peut s'agir d'un utilisateur Windows spécifique ou du compte de service.

• ImpersonateCurrentUser
  Utilise les informations d'identification de l'utilisateur actuel pour se connecter à la source de données.

Pour plus d'informations sur la définition de ces propriétés, consultez Scénarios de déploiement DirectQuery (SSAS Tabulaire).

Propriétés DirectQuery

Le tableau suivant répertorie les propriétés que vous pouvez définir dans Outils de données SQL Server et dans SQL Server Management Studio pour activer DirectQuery et contrôler la source de données utilisée pour les requêtes sur le modèle.

En outre, si votre modèle inclut des partitions, vous devez choisir une partition à utiliser comme source pour les requêtes en mode DirectQuery. Pour plus d'informations, consultez Partitions et mode DirectQuery (SSAS Tabulaire).

Tâches et rubriques connexes

Voir aussi

Concepts

Partitions (SSAS Tabulaire)

Projets de modèles tabulaires (SSAS Tabulaire)

Analyser dans Excel (SSAS Tabulaire)