Comment effectuer une requête distribuée SQL Server avec le serveur OLAP

Cet article explique comment effectuer une requête distribuée SQL Server avec le serveur OLAP.

Version du produit d’origine : SQL Server
Numéro de la base de connaissances d’origine : 218592

Résumé

Cet article explique comment effectuer une requête distribuée SQL Server pour récupérer des données à partir d’un cube OLAP Services (ou Analysis Services). Avec Microsoft SQL Server, vous pouvez effectuer des requêtes sur des fournisseurs OLE DB. Pour ce faire, vous pouvez utiliser l’une des options suivantes :

• Utilisez les OPENQUERY fonctions Transact-SQL ou OPENROWSET .
• Utilisez une requête avec des noms en quatre parties, y compris un nom de serveur lié.

Par exemple :

sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source','location', 'provider_string', 'catalog'
SELECT *
FROM OPENQUERY(mylinkedserver, 'select * from table1')

Vous pouvez utiliser la OPENROWSETOPENQUERY fonction ou dans une instruction SQL Server SELECT pour passer des requêtes au serveur OLAP lié. La requête est limitée à la syntaxe abrégée SELECT prise en charge par les services OLAP. Toutefois, la requête peut inclure la syntaxe MDX (Multidimensional Expressions). Une requête qui inclut MDX retourne des ensembles de lignes aplaties , comme décrit dans la documentation OLE DB. Pour plus d’informations sur la SELECT syntaxe prise en charge par SQL Server services OLAP, consultez la rubrique Syntaxe SQL SELECT prise en charge dans la documentation en ligne des services OLAP.

Pour interroger une base de données de serveur OLAP locale ou distante à partir de SQL Server, vous devez installer le fournisseur OLE DB MSOLAP sur l’ordinateur qui exécute SQL Server. Le fournisseur MSOLAP OLE DB est installé lorsque vous installez les composants clients OLAP à partir du SQL Server.

Exemple OPENROWSET et OPENQUERY

L’exemple de code Transact-SQL suivant montre comment configurer et utiliser des requêtes distribuées avec un serveur OLAP avec les OPENQUERYOpenRowset fonctions et . Vous devez modifier les noms de la source de données et le nom du catalogue le cas échéant.

------------------------------------------
--OPENROWSET for OLAP Server
------------------------------------------

SELECT a.*
FROM OpenRowset('MSOLAP','DATASOURCE=myOlapServer; Initial Catalog=FoodMart;',
'SELECT Measures.members ON ROWS,
[Product Category].members ON COLUMNS
FROM [Sales]') as a
go

-- Example of MDX with slicing --

SELECT a.*
FROM OpenRowset('MSOLAP','DATASOURCE=myOlapServer; Initial Catalog=FoodMart;',
'SELECT
 { Time.Year.[1997] } ON COLUMNS,
NON EMPTY Store.MEMBERS ON ROWS
FROM Sales
WHERE ( Product.[Product Category].[Dairy] )') as a

--------------------------------------------------
-- Linked Server Examples with OPENQUERY
--------------------------------------------------

EXEC sp_addlinkedserver
    @server='olap_server',
    @srvproduct='',
    @provider='MSOLAP',
    @datasrc='server',
    @catalog='foodmart'

go

-- MDX in OPENQUERY --

SELECT *
FROM OPENQUERY(olap_server,
'SELECT
{ Time.Year.[1997] } ON COLUMNS,
NON EMPTY Store.MEMBERS ON ROWS
FROM Sales
WHERE ( Product.[Product Category].[Dairy])' )

SELECT *
FROM OPENQUERY(olap_server, 'SELECT [customer], [quantity] FROM sales')

Seule une forme limitée de SQL est prise en charge, et seuls les noms de niveau ou de mesure peuvent être spécifiés. Lorsque vous exécutez la requête, vous recevez ce message d’erreur :

Serveur : Msg 7399, Niveau 16, État 1, Ligne 1 Fournisseur OLE DB « MSOLAP » a signalé une erreur. [Message: retourné par le fournisseur OLE/DB Le nom de colonne 'customer' n’est pas valide. Seuls les noms de niveau ou de mesure peuvent être spécifiés.]

Une façon de corriger la requête consiste à utiliser les éléments suivants :

SELECT *
FROM OPENQUERY(olap_server, 'SELECT [unit sales] FROM sales')

Toutefois, la transmission d’instructions SQL sous cette forme au serveur OLAP peut être lente et vous pouvez recevoir une erreur de délai d’expiration sur certains ordinateurs :

Le fournisseur OLE DB « MSOLAP » a signalé une erreur. [Le fournisseur OLE/DB a retourné le message : Impossible d’ouvrir la base de données 'foodmart'] [Le fournisseur OLE/DB a retourné le message : Erreur du serveur OLAP : L’opération demandée a échoué en raison d’un délai d’expiration.]

Exemples de serveurs liés avec des noms en quatre parties

L’exemple de code Transact-SQL de cette section illustre l’utilisation d’un serveur lié avec un nom en quatre parties pour interroger un cube OLAP. Dans le code, le serveur lié nommé Olap_server a été créé dans l’exemple précédent :

Select [Store:Store Name]
from Olap_server.FoodMart..[sales]
WHERE [Store:Store State]='WA'
go
Select [Product:Product Category], count ([Store:Store Name])
from Olap_server.FoodMart..[sales]
WHERE [Store:Store State]='WA'
GROUP BY [Product:Product Category]

Bien que les exemples de serveurs liés avec un nom en quatre parties fonctionnent correctement, ils peuvent prendre beaucoup de temps pour retourner un résultat au client. La syntaxe de nom en quatre parties est un concept SQL Server ; elle est utilisée dans une commande Transact-SQL pour faire référence à une table dans un serveur lié, et elle a une syntaxe limitée pour les requêtes OLAP. SQL Server peut déterminer qu’il doit lire la table de faits entière à partir du serveur OLAP et effectuer le GROUP BY lui-même, ce qui peut prendre beaucoup de temps et de ressources.

Microsoft vous recommande d’envoyer une instruction MDX par le biais d’une OPENROWSET fonction ou , OPENQUERY comme indiqué dans les exemples précédents. Cette méthode permet SQL Server d’envoyer la commande directement au fournisseur OLAP lié, sans essayer de l’analyser. La commande peut être MDX ou le sous-ensemble de SQL pris en charge par le fournisseur OLAP. Vous pouvez utiliser l’ensemble de lignes retourné par la OPENQUERY fonction dans d’autres opérateurs SQL. Pour les requêtes MDX de base et GROUP BY les requêtes qui retournent une quantité relativement faible de données (comme un écran), le jeu de résultats doit toujours être créé en moins de 10 secondes, généralement en 5 secondes, quelle que soit la taille du cube. Si les requêtes prennent plus de temps, vous pouvez créer davantage d’agrégations à l’aide de l’Assistant Analyse basée sur l’utilisation.

Conseils sur les performances

Voici quelques conseils sur les performances :

• SQL Server ouvre deux connexions au fournisseur OLAP pour chaque requête. L’une d’elles est réutilisée pour les requêtes ultérieures ; Par conséquent, si vous réexécutez la commande, la deuxième requête peut s’exécuter plus rapidement.

• Pour augmenter la vitesse, regroupez par une autre dimension (car vous obtenez moins de données).

• Le pire des scénarios serait lorsque le cube est stocké via OLAP relationnel (ROLAP) et qu’il n’y a pas d’agrégation. Ensuite, le serveur OLAP ouvre une connexion à SQL Server pour obtenir les lignes de la table de faits. Dans ce cas, n’utilisez pas de requête distribuée SQL Server.

• Si vous avez simplement besoin d’un jeu de résultats à partir d’un serveur OLAP ou d’un fichier cube, essayez d’exécuter le SQL Server ou la requête multidimensionnelle directement sur un serveur OLAP, ou sur n’importe quel fichier cube, à l’aide d’une application OLE DB C++ ou d’une application ADO(ADO*MD).

• SQL Server installe certains fournisseurs OLE DB et les configure pour les charger in-process. Étant donné que le fournisseur MSOLAP n’est pas installé par SQL Server, il est configuré pour charger hors processus. Microsoft vous recommande vivement de modifier les options de chargement in-process du fournisseur OLAP, car cette configuration améliore les performances de vos requêtes OLAP. Pour apporter la modification, procédez comme suit :

  1. Dans le dossier Sécurité, cliquez avec le bouton droit sur Serveurs liés, puis cliquez sur Nouveau serveur lié.
  2. Pour le nom du fournisseur, cliquez pour sélectionner Fournisseur OLE DB pour les services OLAP.
  3. Cliquez sur Options.
  4. Cliquez pour sélectionner Autoriser inProcess.
  5. Cliquez sur OK.

References

• Pour obtenir une description détaillée des paramètres de procédure sp_addlinkedserver stockée, consultez SQL Server documentation en ligne.

• Pour plus d’informations sur la configuration et l’utilisation de requêtes distribuées, recherchez sur sp_addlinkedserver , OPENQUERY, OPENROWSETet les rubriques connexes, dans SQL Server documentation en ligne.

• Pour en savoir plus sur la technologie OLAP et la syntaxe MDX, consultez la documentation en ligne des services OLAP.