Créer un tableau de bord Excel Services à l'aide de données SQL Server Analysis Services

S’APPLIQUE À :2013 2016 2019 Édition d’abonnement SharePoint dans Microsoft 365

Cet article décrit, étape par étape, comment utiliser Excel 2016 pour créer un tableau de bord de vente de base qui contient plusieurs rapports et un filtre à l’aide d’une connexion de données externe. L'exemple de tableau de bord décrit dans cet article ressemble à l'image suivante :

Figure : exemple de tableau de bord de vente de base

Cet article explique également comment publier le tableau de bord sur SharePoint Server 2013 où d’autres utilisateurs peuvent l’afficher et l’utiliser. En suivant les étapes de cet article, vous apprendrez à créer et à organiser différents rapports dans une feuille de calcul et à connecter un filtre à ces rapports.

Avant de commencer

Avant de commencer cette opération, lisez les informations suivantes sur les éléments prérequis :

• Excel doit être installé sur l’ordinateur que vous utilisez pour créer et publier le tableau de bord.

• Ce scénario utilise les exemples de données Adventure Works et un site de Centre d'aide à la décision dans SharePoint Server 2013. Si vous ne disposez pas des exemples de données et d'un site de Centre d'aide à la décision, demandez à un administrateur informatique de les configurer pour vous à l'aide des instructions fournies dans Configurer AdventureWorks pour des solutions d'aide à la décision.

• Excel Services doit être configuré dans l'environnement SharePoint que vous utilisez.

• Vous devez disposer d'informations sur les paramètres d'authentification utilisés pour Excel Services. L'accès utilisateur peut être fourni en utilisant l'authentification Windows avec délégation Kerberos, le Service Banque d'informations sécurisée ou, avec des sources de données OLAP, la fonctionnalité EffectiveUserName

  Pour plus d’informations sur la configuration du Magasin sécurisé, voir Planifier le service Banque d’informations sécurisé dans SharePoint Server et Configurer le service Banque d’informations sécurisé dans SharePoint Server. Pour plus d’informations sur la configuration de la fonctionnalité EffectiveUserName pour les sources de données OLAP, consultez Utiliser Analysis Services EffectiveUserName dans SharePoint Server.

Concevoir le tableau de bord

Avant de créer un tableau de bord, nous vous recommandons de créer un plan de tableau de bord. Le plan ne doit pas nécessairement être exhaustif ou complexe. Toutefois, il doit évoquer ce que vous voulez inclure dans le tableau de bord. Pour vous aider à préparer un plan de tableau de bord, posez-vous des questions du type suivant :

• Qui utilisera le tableau de bord ?

• Quel type d’informations voudront-ils voir ?

• Existe-t-il des données pouvant servir à créer le tableau de bord ?

Notre exemple de tableau de bord doit être un prototype pour apprendre à créer et publier des tableaux de bord Excel Services. Pour savoir comment créer un plan de tableau de bord pour ce type de tableau de bord, voir le tableau suivant.

Tableau : Plan de base pour notre exemple de tableau de bord

À présent que nous avons créé notre plan de tableau de bord, nous pouvons commencer à créer le tableau de bord.

Créer le tableau de bord

Pour créer le tableau de bord, nous commençons par créer une connexion de données. Ensuite, nous utilisons cette connexion de données pour créer les rapports et le filtre que nous voulons utiliser. Après cela, nous publions le classeur dans SharePoint Server 2013.

Partie 1 : créer une connexion de données

Notre exemple de tableau de bord utilise une seule connexion pour accéder aux données qui sont stockées dans SQL Server 2012 Analysis Services. Nous utiliserons cette connexion de données pour créer les rapports et le filtre pour le tableau de bord.

Pour créer une connexion aux données Analysis Services

1. Ouvrez Microsoft Excel.

2. Pour créer un classeur, sélectionnez Classeur vide.

3. Sous l’onglet Données , sélectionnez Obtenir le groupe Données externes , sélectionnez À partir d’autres sources, puis à partir d’Analysis Services.

   L'Assistant Connexion de données s'ouvre.

4. Dans la page Connexion à un serveur de base de données, dans la zone Nom du serveur, spécifiez le nom du serveur où résident les données Analysis Services que vous voulez utiliser.

5. Dans la section Références de connexion, procédez d'une des manières suivantes :

• Si votre organisation utilise l’authentification Windows, sélectionnez Utiliser l’authentification Windows, puis suivant.

• Si votre organisation utilise des informations d’identification utilisateur spécifiques, sélectionnez Utiliser le nom d’utilisateur et le mot de passe suivants, spécifiez un nom d’utilisateur et un mot de passe appropriés, puis sélectionnez Suivant.

  Conseil

  Si vous ne savez pas quelle option choisir, contactez un administrateur SharePoint.

6. Dans la page Sélectionner une base de données et une table , sélectionnez la base de données AdventureWorksDW2012Multidimensional-EE , sélectionnez le cube Adventure Works , puis sélectionnez Suivant.

7. Dans la page Enregistrer le fichier de connexion de données et terminer , sélectionnez Terminer.

8. Dans la page Importer des données , sélectionnez l’option Créer une connexion uniquement , puis sélectionnez OK.

9. Gardez Excel ouvert.

À ce stade, nous avons créé une connexion vers le cube Adventure Works dans Analysis Services. Par défaut, cette connexion de données est enregistrée dans un dossier Mes sources de données de la bibliothèque Documents sur l'ordinateur et est incorporée dans le classeur. Nous utiliserons la connexion ODC qui est incorporée dans le classeur pour le tableau de bord.

L’étape suivante consiste à créer des rapports pour le tableau de bord.

Partie 2 : créer des rapports

Pour notre exemple de tableau de bord, nous allons créer quatre rapports, comme décrit dans le tableau suivant :

Tableau : Rapports de tableau de bord

Nous commençons par créer le rapport VentesProduit.

Pour créer le rapport ProductSales

1. Dans Excel, sous l’onglet Insertion , dans la section Graphiques , sélectionnez Graphique croisé dynamique.

   La boîte de dialogue Créer un graphique croisé dynamique s’affiche.

2. Dans la section Choisir les données à analyser , sélectionnez l’option Utiliser une source de données externe , puis sélectionnez Choisir une connexion.

   La boîte de dialogue Connexions existantes s’affiche.

3. Dans la section Connexions de ce classeur , sélectionnez la connexion de données AdventureWorksDW2012Multidimensional-EE , puis sélectionnez Ouvrir.

4. Dans la boîte de dialogue Créer un graphique croisé dynamique , sélectionnez l’option Feuille de calcul existante , puis sélectionnez OK.

   Graphique1 s’ouvre pour modification.

5. Dans la liste Champs de graphique croisé dynamique, spécifiez les options suivantes :

• Dans la section Récapitulatif des ventes , sélectionnez Montant des ventes.

  Sales Amount s’affiche dans la section Valeurs et le rapport est mis à jour pour afficher une seule barre.

• Dans la section Produit , sélectionnez Catégories de produits.

  Les catégories de produits s’affichent dans la section Axe , et le rapport est mis à jour pour afficher les montants des ventes dans différentes catégories de produits.

6. Pour trier les barres dans l'ordre décroissant, prenez les mesures suivantes :

7. Dans la liste Champs de graphique croisé dynamique , sélectionnez la dimension Catégories de produits, puis sélectionnez la flèche vers le bas qui s’affiche.

   La boîte de dialogue Sélectionner un champ s’affiche.

8. Pour ouvrir la boîte de dialogue Trier (catégorie), sélectionnez Autres options de tri....

9. Dans la section Options de tri , sélectionnez l’option Décroissante (Z à A) par , puis utilisez la liste pour sélectionner Montant des ventes.

10. Sélectionnez OK.

11. Pour veiller à ce qu'il reste de la place pour d'autres rapports, nous allons rapprocher le rapport de graphique croisé dynamique du coin supérieur gauche de la feuille de calcul. Pour ce faire, faites glisser le rapport de sorte que le coin supérieur gauche s'aligne sur le coin supérieur gauche de la cellule B10 dans la feuille de calcul.

12. Pour éviter toute confusion ultérieure sur les noms de rapport, nous allons spécifier un nouveau nom pour le rapport. Sous l’onglet Analyser, dans le groupe Graphique croisé dynamique, dans la zone Nom du graphique, supprimez le texte indiquant Chart1, tapez ProductSales et, sur votre clavier, appuyez< sur Entrée>.

    Conseil

    Veillez à ce que le nom que vous spécifiez contienne uniquement des caractères alphanumériques (sans espace).

13. Pour ne rencontrer aucun problème de dimensionnement ultérieur avec le rapport, nous allons spécifier des paramètres de taille pour le rapport. Pour cela, procédez comme suit :

14. Dans une section vide du rapport, par exemple dans le coin supérieur droit du rapport, cliquez avec le bouton droit, puis sélectionnez Mettre en forme la zone du graphique.

    La liste Format de la zone de graphique apparaît.

15. Sous options de graphique, sélectionnez la commande de barre d’outils Taille et propriétés .

16. Développez la section Taille , puis sélectionnez l’option Verrouiller les proportions .

17. Développez la section Propriétés , sélectionnez l’option Ne pas déplacer ou ne pas dimensionner avec des cellules , puis vérifiez que l’option Verrouillé est sélectionnée.

18. Pour spécifier éventuellement un texte de remplacement pour le rapport, développez la section Texte de remplacement , puis entrez le texte que vous souhaitez utiliser pour le rapport.

19. Fermez la liste Format de la zone de graphique.

20. Enregistrez le classeur à l'aide d'un nom de fichier de type Rapports sur les ventes Adventure Works.

21. Laissez le classeur ouvert.

À ce stade, nous avons créé un rapport de graphique croisé dynamique. La prochaine étape consiste à créer un autre rapport de graphique croisé dynamique que nous allons nommer VentesGéo, pour illustrer les chiffres de ventes par région géographique.

Pour créer le rapport GeoSales

1. Dans Excel, dans la même feuille de calcul que celle utilisée pour créer le rapport ProductSales, sélectionnez la cellule K10.

2. Sous l’onglet Insertion , sélectionnez Graphique croisé dynamique.

3. Dans la section Choisir les données à analyser , sélectionnez l’option Utiliser une source de données externe , puis sélectionnez Choisir une connexion.

   La boîte de dialogue Connexions existantes s’affiche.

4. Dans la section Connexions de ce classeur , sélectionnez la connexion de données AdventureWorksDW2012Multidimensional-EE , puis sélectionnez Ouvrir.

5. Dans la boîte de dialogue Créer un graphique croisé dynamique , sélectionnez l’option Feuille de calcul existante , puis sélectionnez OK.

   Graphique2 s'ouvre pour modification.

6. Déplacez le graphique de sorte que son coin supérieur gauche s'aligne sur le coin supérieur gauche de la cellule J10 de la feuille de calcul.

7. Dans la liste Champs de graphique croisé dynamique, spécifiez les options suivantes :

• Dans la section Récapitulatif des ventes , sélectionnez Montant des ventes.

• Dans la section Secteur de vente, faites glisser Secteur de vente jusqu'à la section Légende.

  Le rapport se met à jour pour afficher un graphique en barres illustrant les montants pour l'Europe, l'Amérique du Nord et le Pacifique.

8. Spécifiez les paramètres de taille pour le rapport en procédant comme suit :

9. Dans une section vide du rapport, cliquez avec le bouton droit, puis sélectionnez l’option Mettre en forme la zone de graphique .

   La liste Format de la zone de graphique apparaît.

10. Sous options de graphique, sélectionnez la commande de barre d’outils Taille et propriétés .

11. Développez la section Taille , puis sélectionnez l’option Verrouiller les proportions .

12. Développez la section Propriétés , sélectionnez l’option Ne pas déplacer ou ne pas dimensionner avec des cellules , puis vérifiez que Verrouillé est sélectionné.

13. Pour spécifier éventuellement un texte de remplacement pour le rapport, développez la section Texte de remplacement , puis entrez le texte que vous souhaitez utiliser pour le rapport.

14. Fermez la liste Format de la zone de graphique.

15. Spécifiez un nouveau nom pour le rapport. Sous l’onglet Analyser, dans le groupe Graphique croisé dynamique, dans la zone Nom du graphique, supprimez le texte de Chart2, tapez GeoSales, puis, sur votre clavier, appuyez< sur Entrée>.

16. Sous l’onglet Fichier , sélectionnez Enregistrer.

17. Laissez le classeur ouvert.

À ce stade, nous avons créé deux rapports. L'étape suivante consiste à créer le rapport VentesCanaux.

Pour créer le rapport ChannelSales

1. Dans Excel, dans la même feuille de calcul que celle utilisée pour créer les rapports précédents, sélectionnez la cellule B26.

2. Sous l’onglet Insertion , sélectionnez Tableau croisé dynamique.

3. Dans la section Choisir les données à analyser , sélectionnez l’option Utiliser une source de données externe , puis sélectionnez Choisir une connexion.

   La boîte de dialogue Connexions existantes s’affiche.

4. Dans la section Connexions de ce classeur , sélectionnez la connexion de données AdventureWorksDW2012Multidimensional-EE , puis sélectionnez Ouvrir.

5. Sélectionnez l’option Feuille de calcul existante , puis sélectionnez OK.

   TableauCroiséDynamique3 s'ouvre pour modification.

6. Dans la liste Champs de tableau croisé dynamique, spécifiez les options suivantes :

• Dans la section Commandes client, sélectionnez Nombre de commandes.

• Dans la section Récapitulatif des ventes , sélectionnez Montant des ventes.

• Dans la section Canal de vente , sélectionnez Canal de vente.

  Le rapport se met à jour pour afficher un tableau montrant le nombre de commandes et les chiffres de vente pour les canaux Internet et Détaillant.

7. Sélectionnez la cellule B26, puis, dans la barre de formule , supprimez le texte des étiquettes de ligne, puis entrez « Channel Sales ». Ensuite, sur votre clavier, appuyez< sur Entrée>.

8. Spécifiez un nouveau nom pour le rapport. Sous l’onglet Analyser, dans le groupe Tableau croisé dynamique, dans la zone Nom du tableau croisé dynamique, supprimez le texte du tableau croisé dynamique3, tapez ChannelSales et, sur votre clavier, appuyez< sur Entrée>.

9. Sous l’onglet Fichier , sélectionnez Enregistrer.

10. Laissez le classeur Excel ouvert.

À ce stade, nous avons créé trois rapports à partir de la même source de données. L'étape suivante consiste à créer le rapport CommandeVentes.

Pour créer le rapport OrderSales

1. Dans Excel, dans la même feuille de calcul que celle utilisée pour créer les rapports précédents, sélectionnez la cellule H26.

2. Sous l’onglet Insertion , sélectionnez Tableau croisé dynamique.

3. Dans la section Choisir les données à analyser , sélectionnez l’option Utiliser une source de données externe , puis sélectionnez Choisir une connexion.

   La boîte de dialogue Connexions existantes s’affiche.

4. Dans la section Connexions de ce classeur , sélectionnez la connexion de données AdventureWorksDW2012Multidimensional-EE , puis sélectionnez Ouvrir.

5. Sélectionnez l’option Feuille de calcul existante , puis sélectionnez OK.

   TableauCroiséDynamique4 s'ouvre pour modification.

6. Dans la liste Champs de tableau croisé dynamique, spécifiez les options suivantes :

• Dans la section Commandes client, sélectionnez Nombre de commandes.

• Dans la section Récapitulatif des ventes , sélectionnez Montant des ventes.

• Dans la section Produit , sélectionnez Catégories de produits.

  Le rapport se met à jour pour afficher un tableau montrant le nombre de commandes et les chiffres de ventes par catégorie de produits.

7. Sélectionnez la cellule H26, puis, dans la barre de formule , supprimez le texte par défaut des étiquettes de lignes, puis tapez Produits. Ensuite, sur votre clavier, appuyez< sur Entrée>.

8. Spécifiez un nouveau nom pour le rapport. Sous l’onglet Analyser, dans le groupe Tableau croisé dynamique, dans la zone Nom du tableau croisé dynamique, supprimez le texte du tableau croisé dynamique4, tapez OrderSales et, sur votre clavier, appuyez< sur Entrée>.

9. Sous l’onglet Fichier , sélectionnez Enregistrer.

10. Laissez le classeur ouvert.

À ce stade, nous avons créé nos quatre rapports pour notre tableau de bord de base. L’étape suivante consiste à créer un filtre.

Partie 3 : créer un filtre

Avec Excel, il est possible de créer différents types de filtres. Par exemple, nous pouvons créer un simple filtre en plaçant un champ dans la section Filtre de la liste Champs. Nous pouvons créer un segment, ou, si nous utilisons une source de données multidimensionnelle comme Analysis Services, nous pouvons créer un contrôle de chronologie. Pour cet exemple de tableau de bord, nous allons créer un contrôle de chronologie. Ce filtre permettra aux utilisateurs d'afficher les informations d'une période particulière.

Pour créer un contrôle de chronologie

1. Dans Excel, dans la même feuille de calcul que celle utilisée pour créer les rapports, sélectionnez la cellule B1.

2. Sous l’onglet Insertion , dans le groupe Filtre , sélectionnez Chronologie.

   La boîte de dialogue Connexions existantes s’affiche.

3. Dans la section Connexions de ce classeur , sélectionnez la connexion de données AdventureWorksDW2012Multidimensional-EE , puis sélectionnez Ouvrir.

4. La boîte de dialogue Insérer des chronologies s’affiche.

5. Sélectionnez l’option Date , puis ok.

   Un contrôle de chronologie apparaît.

6. Déplacez le contrôle de chronologie de sorte que son coin supérieur gauche s'aligne sur le coin supérieur gauche de la cellule B1.

7. Pour agrandir le contrôle de chronologie, utilisez ses curseurs de redimensionnement, et faites glisser la poignée de dimensionnement à droite du contrôle jusqu'à la colonne M.

8. Sélectionnez le contrôle de chronologie et, sous l’onglet Options , dans le groupe Chronologie , sélectionnez la commande de barre d’outils Connexions aux rapports .

   La boîte de dialogue Connexions aux rapports s’affiche.

9. Sélectionnez ChannelSales, GeoSales, OrderSales et ProductSales, puis ok.

10. Sous l’onglet Fichier , sélectionnez Enregistrer.

11. Laissez le classeur Excel ouvert.

À ce stade, nous avons créé un tableau de bord. L’étape suivante consiste à le publier sur SharePoint Server 2013, où il peut être utilisé par d’autres utilisateurs.

Publier le tableau de bord

Pour publier le classeur dans SharePoint Server 2013, nous allons suivre un processus en quatre étapes. Tout d’abord, nous effectuons des ajustements qui affectent la façon dont le classeur s’affiche. Ensuite, nous spécifions les paramètres d'authentification des données d'Excel Services pour la connexion de données externe que nous utilisons. Ensuite, nous spécifions les options de publication du classeur. Enfin, nous publions le classeur dans SharePoint Server 2013.

Modifions d’abord le classeur. Par défaut, notre exemple de tableau de bord affiche un quadrillage sur la feuille de calcul qui contient notre tableau de bord. De plus par défaut, la feuille de calcul s'appelle Feuille1. Nous pouvons apporter quelques ajustements mineurs pour améliorer l’apparence du tableau de bord.

Pour améliorer l’apparence du classeur

1. Dans Excel, sélectionnez l’onglet Affichage .

2. Pour supprimer le quadrillage, sous l’onglet Affichage, dans le groupe Afficher/Masquer, désactivez la case à cocher Quadrillage.

3. Pour supprimer les titres de ligne et de colonne, sous l’onglet Affichage, dans le groupe Afficher/Masquer, désactivez la case à cocher Titres.

4. Pour renommer la feuille de calcul, cliquez avec le bouton droit sur son onglet sheet1, puis sélectionnez Renommer. Entrez un nouveau nom pour la feuille de calcul, par exemple SalesDashboard, puis, sur votre clavier, appuyez< sur Entrée>.

5. Sous l’onglet Fichier , sélectionnez Enregistrer.

6. Laissez le classeur ouvert.

Le classeur que nous avons créé utilise une connexion de données externe que nous voulons garder active lorsque nous publions le classeur. Pour veiller à ce que la connexion de données demeure active afin que l'actualisation des données soit prise en charge dans Excel Services, nous devons spécifier des paramètres d'authentification.

Pour spécifier les paramètres d’authentification pour la connexion de données externes

1. Dans Excel, sous l’onglet Données , sélectionnez la commande de barre d’outils Connexions .

   La boîte de dialogue Connexions de classeur s’affiche et affiche la connexion de données AdventureWorksDW2012Multidimensional-EE .

2. Sélectionnez Propriétés.

3. Dans la boîte de dialogue Propriétés de connexion, sous l’onglet Définition, en regard de Excel Services, sélectionnez Paramètres d’authentification...

4. Dans la boîte de dialogue Paramètres d’authentification Excel Services, effectuez l’une des étapes suivantes :

• Si Excel Services est configuré pour utiliser l’authentification Windows ou la fonctionnalité EffectiveUserName, sélectionnez Utiliser le compte de l’utilisateur authentifié, puis sélectionnez OK.

• Si Excel Services est configuré pour utiliser Service Banque d'informations sécurisée, sélectionnez Utiliser un compte stocké. Dans la zone ID d’application , spécifiez l’ID de l’application cible banque d’informations sécurisée, puis sélectionnez OK.

• Si Excel Services est configuré pour utiliser le compte de service sans assistance, sélectionnez Aucun, puis OK.

  Importante

  Si vous ne savez pas quelle option sélectionner, contactez un administrateur SharePoint.

5. Pour fermer la boîte de dialogue Propriétés de connexion , sélectionnez OK.

6. Si vous voyez un message indiquant que la connexion dans le classeur ne sera plus identique à la connexion définie dans le fichier externe, sélectionnez Oui.

7. Pour fermer la boîte de dialogue Connexions au classeur , sélectionnez Fermer.

Lorsque nous avons créé les rapports pour le tableau de bord, nous avons donné à chacun un nom unique et nous l’avons défini comme un élément nommé dans Excel. En plus de publier le classeur sur SharePoint Server 2013, nous devons publier les éléments nommés que nous avons définis. Cela permet d’afficher un élément nommé dans son propre composant WebPart SharePoint ultérieurement. Pour ce faire, nous spécifions des options de publication pour le classeur.

Pour spécifier les options de publication du classeur

1. Sous l’onglet Fichier , sélectionnez Informations, puis Options d’affichage du navigateur.

2. Sélectionnez Options d’affichage du navigateur.

3. Sous l'onglet Affichage, utilisez la liste pour sélectionner Éléments du classeur.

4. Sélectionnez Tous les graphiques, tous les tableaux croisés dynamiques, puis OK.

5. Sous l’onglet Fichier , sélectionnez Enregistrer.

6. Laissez le classeur ouvert.

L’étape suivante consiste à publier le classeur dans SharePoint Server 2013.

Pour publier le classeur sur SharePoint Server

1. Dans Excel, sous l’onglet Fichier , sélectionnez Enregistrer sous, puis Ordinateur, puis Parcourir.

   La boîte de dialogue Enregistrer sous s’affiche .

2. Dans la ligne d’adresse, entrez l’adresse SharePoint d’un Excel Services emplacement de fichier approuvé.

3. Sélectionnez Enregistrer.

   Le classeur est publié dans la bibliothèque SharePoint que vous avez spécifiée.

4. Fermez le classeur Excel.

À présent que nous avons créé et publié le tableau de bord, nous pouvons l’utiliser pour explorer les données.

Utiliser le tableau de bord

Une fois le tableau de bord publié sur SharePoint Server 2013, il est disponible pour que les utilisateurs l’affichent et l’utilisent.

Ouverture du tableau de bord

1. Ouvrez un navigateur web.

2. Dans la barre d’adresses, entrez l’adresse du site Business Intelligence Center où le tableau de bord a été publié.

3. Sélectionnez Contenu du site, puis Documents.

4. Sélectionnez le tableau de bord Adventure Works Sales .

   Le tableau de bord s'ouvre pour consultation.

À présent que le tableau de bord est ouvert pour affichage, nous pouvons l'utiliser pour obtenir des réponses à des questions spécifiques, telles que celles qui sont décrites dans le tableau suivant.

Tableau : Utilisation du tableau de bord pour obtenir des réponses à des questions spécifiques

Voir aussi

Concepts

Fonctionnalités d’aide à la décision dans un service Excel (SharePoint Server 2013)

Créer un tableau de bord Excel Services à l’aide d’un modèle de données (SharePoint Server 2013)