Il existe un fossé entre Microsoft Excel et les plates-formes décisionnelles de premier plan depuis de nombreuses années. Les améliorations apportées au tableau croisé dynamique de Microsoft Excel 2010, ainsi que quelques autres fonctionnalités de BI, en ont fait un réel concurrent pour la BI d'entreprise. Excel a traditionnellement été utilisé pour l'analyse autonome et l'outil standard vers lequel tout le monde exporte ses rapports finaux. La Business Intelligence professionnelle a traditionnellement été réservée aux sociétés comme SAS, Business Objects et SAP.
Résultat final
Microsoft Excel 2010 (avec le tableau croisé dynamique Excel 2010) ainsi que SQL Server 2008 R2, SharePoint 2010 et l'add-on gratuit de Microsoft Excel 2010 "PowerPivot" ont donné naissance à une solution de business intelligence et de reporting haut de gamme.
Ce didacticiel couvre un scénario simple avec un tableau croisé dynamique Excel 2010 connecté à une base de données SQL Server 2008 R2 à l'aide d'une requête SQL simple.
Continuer la lecture ci-dessous
02 sur 15Insérer un tableau croisé dynamique
Vous pouvez insérer un tableau croisé dynamique dans un classeur Excel nouveau ou existant. Vous voudrez peut-être envisager de placer votre curseur au bas de quelques lignes du haut. Cela vous laissera de la place pour un en-tête ou des informations sur la société si vous partagez la feuille de calcul ou imprimez-la.
- Ouvrez un classeur Excel 2010 nouveau ou existant et cliquez sur la cellule dans laquelle vous souhaitez placer le coin supérieur gauche du tableau croisé dynamique.
- Cliquez sur l'onglet Insertion et cliquez sur la liste déroulante de tableau croisé dynamique dans la section Tables. Choisissez tableau croisé dynamique. Cela ouvrira le formulaire de dialogue Créer un tableau croisé dynamique.
Continuer la lecture ci-dessous
03 sur 15Connecter un tableau croisé dynamique à SQL Server
Excel 2010 peut récupérer les données de tous les principaux fournisseurs de SGBDR (Système de gestion de base de données relationnelle). Les pilotes SQL Server doivent être disponibles pour la connexion par défaut. Consultez leur site Web si vous devez télécharger les pilotes ODBC.
Dans le cas de ce didacticiel, nous nous connectons à SQL Server 2008 R2 (version gratuite de SQL Express).
- Ouvrez le formulaire Créer un tableau croisé dynamique. Sélectionnez "Utiliser une source de données externe" et cliquez sur le bouton Choisir une connexion. Laissez l’emplacement de la table croisée dynamique.
- Ouvrez le formulaire Connexions existantes. Cliquez sur le bouton Parcourir pour plus.
- Cliquez sur le bouton Nouvelle source pour lancer l'assistant de connexion de données.
- Choisissez Microsoft SQL Server et cliquez sur Suivant.
- Entrez le nom du serveur et les identifiants de connexion. Choisissez la méthode d'authentification appropriée:
- Utiliser l'authentification Windows: Cette méthode utilise votre connexion réseau pour accéder aux bases de données SQL Server.
- Utilisez le nom d'utilisateur et le mot de passe suivants: Cette méthode est utilisée lorsque SQL Server a été configuré avec des utilisateurs autonomes pour accéder à des bases de données.
- Remplacez la table par un code SQL personnalisé qui fournira exactement les données souhaitées dans notre classeur Excel:
- Sélectionnez la base de données à laquelle vous allez vous connecter. Dans cet exemple, nous nous connectons à l'exemple de base de données AdventureWorks fourni par Microsoft. Cochez la case Se connecter à une table spécifique et choisissez la première table. N'oubliez pas que nous n'allons pas récupérer les données de cette table.
- Cliquez sur Terminer pour fermer l'assistant et vous ramener au classeur. Nous allons échanger la table d’espace réservé pour notre requête SQL personnalisée.
Vous serez redirigé vers le formulaire Créer un tableau croisé dynamique (A). Cliquez sur OK.
04 sur 15Tableau croisé dynamique temporairement connecté à la table SQL
À ce stade, vous êtes connecté à la table des paramètres de substitution et vous avez un tableau croisé dynamique vide. Vous pouvez voir à gauche où sera le tableau croisé dynamique, et à droite, une liste des champs disponibles.
Continuer la lecture ci-dessous
05 sur 15Propriétés de la connexion ouverte
Assurez-vous que vous êtes sur l'onglet Options et cliquez sur la liste déroulante Modifier la source de données dans la section Données. Choisissez Propriétés de la connexion.
Cela fait apparaître le formulaire Propriétés de connexion. Cliquez sur l'onglet Définition. Cela vous montre les informations de connexion pour la connexion actuelle à SQL Server. Bien qu'il fasse référence à un fichier de connexion, les données sont en réalité incorporées dans la feuille de calcul.
06 sur 15Mettre à jour les propriétés de la connexion avec une requête
Modifiez le type de commande de Table en SQL et remplacez le texte de commande existant par votre requête SQL. Voici la requête créée à partir de l'exemple de base de données AdventureWorks:
SELECT Sales.SalesOrderHeader.SalesOrderID,Sales.SalesOrderHeader.OrderDate,Sales.SalesOrderHeader.ShipDate,Sales.SalesOrderHeader.Status,Sales.SalesOrderHeader.SubTotal,Sales.SalesOrderHeader.TaxAmt,Sales.SalesOrderHeader.Freight,Sales.SalesOrderHeader.TotalDue,Sales.SalesOrderDetail.SalesOrderDetailID,Sales.SalesOrderDetail.OrderQty,Sales.SalesOrderDetail.UnitPrice,Sales.SalesOrderDetail.LineTotal,Nom.produit.produit,Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,Sales.Customer.CustomerType,Production.Produit.ListePrix,Production.Product.ProductLine,Production.ProductSubcategory.Name AS ProductCategoryFROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ONSales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderIDINNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =Production.Product.ProductID INNER JOIN JOIN.Client ONSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID ANDSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOINSales.vIndividualCustomer ON Sales.Customer.CustomerID =Sales.vIndividualCustomer.CustomerID INNER JOINProduction.ProductSubcategory ON Production.Product.ProductSubcategoryID =Production.ProductSubcategory.ProductSubcategoryIDCliquez sur OK.
Continuer la lecture ci-dessous
07 sur 15Recevoir un avertissement de connexion
Vous recevrez une boîte de dialogue Avertissement Microsoft Excel. C'est parce que nous avons changé les informations de connexion. Lors de la création initiale de la connexion, les informations étaient enregistrées dans un fichier .ODC externe (connexion de données ODBC). Les données du classeur étaient identiques à celles du fichier .ODC jusqu'à ce que nous passions d'un type de commande de table à un type de commande SQL à l'étape 6. L'avertissement vous indique que les données ne sont plus synchronisées et que la référence au fichier externe du classeur sera supprimée. C'est acceptable. Cliquez sur oui.
08 sur 15Tableau croisé dynamique connecté à SQL Server avec une requête
Cela ramène au classeur Excel 2010 avec un tableau croisé dynamique vide. Vous pouvez voir que les champs disponibles sont maintenant différents et correspondent aux champs de la requête SQL. Nous pouvons maintenant commencer à ajouter des champs au tableau croisé dynamique.
Continuer la lecture ci-dessous
09 sur 15Ajouter des champs au tableau croisé dynamique
Dans la liste des champs de tableau croisé dynamique, faites glisser ProductCategory dans la zone Etiquettes de ligne, la zone Date de commande en étiquettes de colonne et la zone TotalDue en valeurs. Comme vous pouvez le constater, le champ de date comporte des dates individuelles. Le tableau croisé dynamique a donc créé une colonne pour chaque date unique. Excel 2010 possède des fonctions intégrées pour nous aider à organiser les champs de date.
10 sur 15Ajouter un regroupement pour les champs de date
La fonction Regroupement nous permet d’organiser les dates en années, mois, trimestres, etc. Cela aidera à résumer les données et facilitera l’interaction de l’utilisateur avec celles-ci. Cliquez avec le bouton droit de la souris sur l'un des en-têtes de colonne de date et choisissez Groupe, qui affiche le formulaire de regroupement.
Continuer la lecture ci-dessous
11 sur 15Choisir le regroupement par valeurs
Selon le type de données que vous regroupez, le formulaire sera légèrement différent. Excel 2010 vous permet de regrouper des dates, des chiffres et des données de texte sélectionnées. Nous regroupons OrderDate dans ce didacticiel afin que le formulaire affiche les options relatives aux regroupements de dates.
Cliquez sur Mois et années, puis sur OK.
12 sur 15Tableau croisé dynamique groupé par années et mois
Les données sont regroupées par année, puis par mois. Chacune a un signe plus et moins qui vous permet de développer et de réduire en fonction de la façon dont vous souhaitez voir les données.
À ce stade, le tableau croisé dynamique est très utile. Chacun des champs peut être filtré, mais le problème est qu’il n’ya pas d’indice visuel quant à l’état actuel des filtres. Il faut plusieurs clics pour changer la vue.
13 sur 15Insérer un segment (Nouveau dans Excel 2010)
Les segments sont nouveaux dans Excel 2010. Les segments sont essentiellement l'équivalent de la définition visuelle des filtres des champs existants et de la création de filtres de rapport dans le cas où l'élément que vous souhaitez filtrer ne figure pas dans la vue de tableau croisé dynamique en cours. L’avantage de Slicers est qu’il devient très facile pour l’utilisateur de modifier l’affichage des données dans le tableau croisé dynamique et de fournir des indicateurs visuels de l’état actuel des filtres.
Pour insérer des segments, cliquez sur l'onglet Options, puis sur Insérer un segment dans la section Trier et filtrer. Choisissez Insérer un segment qui ouvre la fiche Insérer un segment. Cochez autant de champs que vous voulez avoir à disposition.
14 sur 15Tableau croisé dynamique avec trancheurs conviviaux
Comme vous pouvez le constater, les segments affichent toutes les données sélectionnées. Il est très clair pour l'utilisateur quelles données se trouvent exactement dans la vue actuelle du tableau croisé dynamique.
15 sur 15Choisir des valeurs dans les segments qui met à jour le tableau croisé dynamique
Cliquez sur différentes combinaisons de valeurs et observez comment la vue du tableau croisé dynamique change. Vous pouvez utiliser un clic typique de Microsoft dans Slicers, ce qui signifie que si vous pouvez utiliser les touches Ctrl + Clic pour sélectionner plusieurs valeurs ou Maj + Clic pour sélectionner une plage de valeurs.
Chaque segment affiche les valeurs sélectionnées, ce qui rend très évident l'état du tableau croisé dynamique en termes de filtres. Vous pouvez modifier les styles des trancheurs si vous le souhaitez en cliquant sur la liste déroulante Styles rapides de la section Trancheur de l'onglet Options.
