Importer des données à partir d'Analysis Services ou de PowerPivot
Dans PowerPivot pour Excel, vous pouvez utiliser une base de données Analysis Services comme source de données pour un classeur PowerPivot. Cette base de données peut soit être un cube traditionnel, construit à l'aide de SQL Server Analysis Services, ou un autre classeur PowerPivot, publié sur un Serveur SharePoint.
Cette rubrique contient les sections suivantes :
Configuration requise
Choisir une approche d'importation
Importer des données depuis un cube
Importer des données d'un classeur PowerPivot
Se connecter à un classeur PowerPivot en tant que source de données externe
Interaction de PowerPivot avec les cubes Analysis Services
[!REMARQUE]
PowerPivot arrête les requêtes de traitement longues après une heure (3600 secondes). Le délai d'expiration est intégré à l'application et ne peut pas être modifié. Bien que cette limite s'applique à toutes les sources de données, vous serez davantage susceptible de la rencontrer si vous importez des données Analysis Services. Vous pouvez contourner cette limite en important peu de lignes à la fois, puis en les fusionnant ultérieurement. Pour plus d'informations, consultez la section L'importation PowerPivot s'arrête après 3600 secondes (ou 1 heure).
Configuration requise
Les cubes Analysis Services doivent avoir la version SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 ou SQL Server 2012. Vous devez accéder au cube sur un serveur. Vous ne pouvez pas utiliser un cube local comme source de données pour un classeur PowerPivot.
Les classeurs PowerPivot que vous utilisez comme sources de données doivent être publiés sur un site SharePoint 2010. Le site SharePoint doit s'exécuter sur un ordinateur différent de celui vous utilisez pour importer des données.
Vous devez avoir des autorisations d'affichage sur le site SharePoint pour importer des données à partir des classeurs PowerPivot.
Choisir une approche d'importation
Vous pouvez utiliser chacune des approches suivantes pour utiliser Analysis Services ou des données PowerPivot dans un classeur Excel.
Application |
Approche |
Lien |
PowerPivot pour Excel |
Cliquez sur À partir d'Analysis Services ou de PowerPivot pour importer des données à partir d'un cube Analysis Services. |
Comment... |
PowerPivot pour Excel |
Cliquez sur À partir d'Analysis Services ou de PowerPivot pour importer des données à partir d'un classeur PowerPivot publié sur un serveur SharePoint. |
Comment... |
Excel |
Cliquez sur À partir d'autres sources dans le groupe Obtenir des données externes pour définir une connexion à un classeur PowerPivot publié sur un serveur SharePoint. |
Comment... |
Importer des données depuis un cube
Toutes les données contenues dans une base de données SQL Server Analysis Services peuvent être importées dans un classeur PowerPivot. Vous pouvez extraire tout ou partie d'une dimension ou obtenir des coupes et des agrégats à partir du cube, tel que le cumul des ventes, mois par mois, pour l'année actuelle. Toutefois, vous ne devez pas oublier les restrictions suivantes :
Toutes les données que vous importez à partir d'un cube ou d'un autre classeur PowerPivot sont aplaties. Par conséquent, si vous définissez une requête qui récupère des mesures le long de plusieurs dimensions, les données sont importées avec chaque dimension dans une colonne distincte.
Les données sont statiques après avoir été importées. Elles ne sont pas mises à jour à partir du serveur Analysis Services à la demande. Si vous souhaitez actualiser un classeur pour rapatrier les modifications dans la base de données Analysis Services, vous devez planifier des dates d'actualisation de données après que le classeur a été publié sur SharePoint. Vous pouvez également actualiser manuellement des données dans PowerPivot pour Excel. Pour plus d'informations, consultez Différents moyens de mettre à jour des données dans PowerPivot.
La procédure suivante montre comment obtenir un sous-ensemble de données d'un cube traditionnel sur une instance Analysis Service. Cette procédure utilise l'exemple de base de données Adventure Works DW Multidimensional 2012 pour expliquer comment importer un sous-ensemble d'un cube. Si vous avez accès à un serveur Analysis Services qui dispose de l'exemple de base de données Adventure Works DW Multidimensional 2012 , vous pouvez suivre ces étapes pour apprendre à importer des données à partir d'Analysis Services.
Dans la fenêtre PowerPivot, dans le groupe Données externes, cliquez sur À partir de la base de données, puis sélectionnez À partir d'Analysis Services ou de PowerPivot.
L'Assistant Importation de table démarre.
Dans la page Connexion à Microsoft SQL Server Analysis Services, pour Nom convivial de la connexion, tapez un nom descriptif pour la connexion de données.
Pour Nom de fichier ou de serveur, tapez le nom de l'ordinateur qui héberge l'instance, ainsi que le nom de l'instance : par exemple, Contoso-srv\CONTOSO.
[!REMARQUE]
Vous ne pouvez pas utiliser un cube local comme source de données ; le cube doit être accessible depuis une instance Analysis Services.
Éventuellement, cliquez sur Avancé pour ouvrir une boîte de dialogue dans laquelle vous pouvez configurer des propriétés qui sont spécifiques au fournisseur. Cliquez sur OK.
Cliquez sur la flèche Bas à droite de la liste Nom de la base de données et sélectionnez une base de données Analysis Services dans la liste. Par exemple, si vous avez accès à l'exemple de base de données Adventure Works DW Multidimensional 2012 , vous pouvez sélectionner Adventure Works DW Multidimensional 2012.
Cliquez sur Tester la connexion pour vérifier que le serveur Analysis Services est disponible.
Cliquez sur Suivant.
Dans la page Spécifier une requête MDX, cliquez sur Conception pour ouvrir un générateur de requêtes MDX.
Dans cette étape, vous faites glisser dans la grande zone réservée à la conception de la requête toutes les mesures, attributs de dimension, hiérarchies et membres calculés que vous souhaitez importer dans le classeur PowerPivot.
Si vous disposez d'une instruction MDX existante que vous souhaitez utiliser, collez l'instruction dans la zone de texte et cliquez sur Valider pour vous assurer que l'instruction fonctionnera. Pour plus d'informations sur la façon de créer des requêtes MDX, consultez Concepteur de requêtes MDX Analysis Services (PowerPivot).
Pour cette procédure, à l'aide de l'exemple de cube Adventure Works, procédez comme suit :
Dans le volet Métadonnées, développez Mesures, puis Récapitulatif des ventes.
Faites glisser le Montant moyen des ventes dans le grand volet de conception.
Dans le volet Métadonnées, développez la dimension Produit.
Faites glisser Catégories de produits à gauche de Montant moyen des ventes dans la grande zone de conception.
Dans le volet Métadonnées, développez la dimension Date, puis développez Calendrier.
Faites glisser Date.Année civile à gauche de la Catégorie dans la grande zone de conception.
Éventuellement, ajoutez un filtre pour importer un sous-ensemble de données. Dans le volet situé dans le coin supérieur droit du concepteur, pour Dimension, faites glisser Date dans le champ de dimension. Dans Hiérarchie, sélectionnez Date.Année civile ; pour Opérateur, sélectionnez Plage (limites exclues) ; pour Expression de filtre, cliquez sur la flèche bas et sélectionnez Année 2005.
Cela crée un filtre sur le cube qui vous permet d'exclure les valeurs pour 2005.
Cliquez sur OK et examinez la requête MDX qui a été créée par le concepteur de requêtes.
Tapez un nom convivial pour le jeu de données. Ce nom sera utilisé comme nom de table dans le classeur. Si vous n'affectez pas un nouveau nom, par défaut, les résultats de la requête sont enregistrés dans une nouvelle table appelée Query.
Cliquez sur Terminer.
Lorsque les données ont fini de se charger, cliquez sur Fermer.
Après avoir importé les données dans la fenêtre PowerPivot, vous pouvez vérifier le type de données en sélectionnant chaque colonne et en consultant Type de données dans le groupe Mise en forme sur le ruban. Veillez à vérifier le type de données des colonnes qui contiennent des données numériques ou financières. PowerPivot modifie parfois le type de données en Texte s'il rencontre des valeurs vides. Vous pouvez utiliser l'option Type de données pour corriger le type de données si vos données numériques ou financières ne sont pas au format souhaité.
Importer des données d'un classeur PowerPivot
Dans la fenêtre PowerPivot, dans le groupe Obtenir des données externes, cliquez sur À partir de la base de données, puis sélectionnez À partir d'Analysis Services ou de PowerPivot.
L'Assistant Importation de table démarre.
Dans Connexion à Microsoft SQL Server Analysis Services, pour Nom convivial de la connexion, tapez un nom descriptif pour la connexion de données. L'utilisation de noms descriptifs pour la connexion peut vous aider à vous souvenir de la façon dont la connexion est utilisée.
Dans Nom de fichier ou de serveur, tapez l'adresse URL du fichier .xlsx publié. Par exemple, http://Contoso-srv/Shared Documents/ContosoSales.xlsx.
[!REMARQUE]
Vous ne pouvez pas utiliser un classeur PowerPivot local comme source de données ; le classeur PowerPivot a dû être publié sur un site SharePoint.
Éventuellement, cliquez sur Avancé pour ouvrir une boîte de dialogue dans laquelle vous pouvez configurer des propriétés qui sont spécifiques au fournisseur. Cliquez sur OK.
Cliquez sur Tester la connexion pour vérifier que le classeur PowerPivot est disponible.
Cliquez sur Suivant.
Cliquez sur Conception.
Générez la requête en faisant glisser des mesures, des attributs de dimension ou des hiérarchies dans la grande zone de conception. Éventuellement, utilisez le volet de filtre situé dans le coin supérieur droit pour sélectionner un sous-ensemble de données pour l'importation. Reportez-vous aux étapes dans la section précédente pour obtenir un exemple de comment générer la requête.
Cliquez sur OK.
Cliquez sur Valider.
Cliquez sur Terminer.
Les données PowerPivot sont copiées vers le classeur et stockées dans un format compressé, à l'écart du classeur d'origine. Après avoir importé les données, la connexion au classeur est fermée. Pour ré-interroger les données d'origine, vous pouvez actualiser le classeur. Pour plus d'informations, consultez Différents moyens de mettre à jour des données dans PowerPivot.
Se connecter à un classeur PowerPivot en tant que source de données externe
Vous pouvez utiliser des données PowerPivot en tant que source de données externe dans Excel sans incorporer les données dans le classeur. Vous n'avez pas besoin de PowerPivot pour Excel pour ce scénario, mais vous devez disposer de la version correcte du Fournisseur OLE DB Analysis Services. Pour obtenir la version la plus récente du fournisseur, téléchargez et installez le fournisseur OLE DB pour Microsoft SQL Server 2008 R2 Analysis Services à partir de la page SQL Server 2008 R2 Feature Pack sur le site Web Microsoft.
Sous l'onglet Données dans Excel, dans le groupe Obtenir des données externes, cliquez sur À partir d'autres sources.
Cliquez sur À partir d'Analysis Services.
Dans la zone Nom du serveur, tapez l'adresse du classeur PowerPivot. L'adresse doit inclure le fichier .xlsx qui contient les données (par exemple, http://constoso-srv/team site/partagé sales.xlsx trimestriel de documents/contoso).
[!REMARQUE]
Si vous obtenez l'erreur « Échec de l'analyse XML à la ligne 1, colonne 1 », vous ne disposez probablement pas de la bonne version du fournisseur OLE DB pour Analysis Services. Vous pouvez installer PowerPivot pour Excel ou télécharger et installer le fournisseur OLE DB pour Microsoft SQL Server Analysis Services à partir de la page SQL Server 2008 R2 Feature Pack sur le site Web Microsoft.
Cliquez sur Suivant.
Dans Sélectionner une base de données et une table, cliquez sur Terminer.
Dans Importer des données, spécifiez comment vous souhaitez que les données apparaissent (par exemple, choisissez le Rapport de tableau croisé dynamique).
Cliquez sur Propriétés puis ouvrez l'onglet Définition pour vérifier que la chaîne de connexion spécifie Provider=MSOLAP .4. Cette étape vérifie que vous avez le fournisseur OLE DB correct.
Cliquez sur OK, puis sur Terminer pour configurer la connexion.
Une liste de champs de Tableau croisé dynamique s'affiche dans l'espace de travail qui contient des champs du classeur PowerPivot.
Interaction de PowerPivot avec les cubes Analysis Services
Lorsque vous utilisez l'Assistant pour vous connecter à une source de données Analysis Services, le moteur d'analyse en mémoire xVelocity interne (VertiPaq) compose une requête MDX sur la base de données multidimensionnelle, puis télécharge les données dans le classeur. Les données ne peuvent pas être actualisées et ne sont pas mises à jour automatiquement lorsque les données dans le cube changent.
Les données que vous importez vers un classeur PowerPivot sont autonomes après la phase d'importation de données. Plutôt que de considérer un classeur PowerPivot comme une surface de navigation pour un cube existant, vous devez le considérer comme un espace de travail dans lequel vous pouvez obtenir des sous-ensembles utiles de données du cube et à partir duquel vous pouvez lancer de nouvelles analyses indépendamment du cube et des autres sources de données.
Si vous souhaitez consulter les instructions MDX générées par le classeur PowerPivot pendant l'importation, vous pouvez créer un fichier de trace. Pour plus d'informations sur la création d'un fichier de trace, consultez Boîte de dialogue Options & diagnostics PowerPivot.
Si vous maîtrisez l'utilisation de cubes Analysis Services dans Excel, vous devez savoir que certaines fonctionnalités Excel ne peuvent pas être utilisées avec les classeurs PowerPivot. Lorsque vous êtes connecté à un cube PowerPivot, les fonctionnalités Excel suivantes ne sont pas prises en charge :
cubes hors connexion ;
regroupement dans les tableaux croisés dynamiques ;
commande d'extraction.
Voir aussi
Concepts
Sources de données prises en charge dans les classeurs PowerPivot