Excel : Utilisez le Business Intelligence comme un pro

Le noyau du jeu d'outils de Business Intelligence (BI) de Microsoft est Power Pivot. Le reste des outils, Power View, Power Query et Power Map, s'ajoutent au modèle tabulaire Power Pivot. Dans le cas de Power View, cela est évident parce que vous vous connectez explicitement au modèle. Dans le cas de Power Query et Power Map, cela peut ne pas être aussi évident que le modèle tabulaire Power Pivot. Pour tirer le meilleur parti de l'ensemble d'outils, vous avez besoin de savoir comment Power Pivot fonctionne. Ce chapitre fournit des informations générales sur les raisons pour lesquelles Power Pivot est un outil important et ce qui rend Power Pivot si performant. Il vous indique les conditions requises pour lancer Power Pivot et comment l'activer. Le chapitre vous donne également un aperçu de l'interface Power Pivot et vous fournit une certaine expérience en utilisant les différentes zones de l'interface. 

Après avoir suivi cette session, vous serez familiarisé avec ce qui suit: 

1. Pourquoi utiliser Power Pivot? 
2. Le moteur d'analyse en mémoire  
3. Activation de Power Pivot pour Excel 
4. Exploration de l'interface Data Model Management 

L'un des types de sources de données les plus courantes que vous rencontrerez est une base de données relationnelle. Les systèmes de gestion de bases de données relationnelles (SGBDR), tels que SQL Server, Oracle, DB2 et Access, sont constitués de tables et de relations entre les tables basées sur des clés. Par exemple, la Figure montre une table de détail de bon de commande et une table de produits.

Ils sont liés par la colonne ProductID. C'est un exemple d'une relation un-à-plusieurs. Pour chaque ligne d'une table de produits, il existe plusieurs lignes dans la table de détail de la commande d'achat. Les clés dans une table sont appelées primaires et Clés étrangères. Chaque table a besoin d'une clé primaire qui identifie de façon unique une ligne dans la table. Par exemple, ProductID est la clé primaire dans la table de produits. Le ProductID est considéré comme une clé étrangère dans la table de détail de la commande d'achat. Les clés étrangères renvoient à une clé primaire dans une table liée. Remarquez qu'une clé primaire peut consister en une combinaison de colonnes; Par exemple, la clé primaire de la table de détails de la commande d'achat est la combinaison de l'ID d'achat et de l'ID de commande d'achat.

 Bien que les relations un-à-plusieurs sont les plus courantes, vous rencontrerez un autre type de relation qui est assez répandue plusieurs -à-plusieurs . La figure montre un exemple d'une relation de plusieurs à plusieurs. Une personne peut avoir plusieurs numéros de téléphone de différents types. Par exemple, ils peuvent avoir deux numéros de fax. Vous ne pouvez pas relier ces tables directement. Vous devez plutôt utiliser une table de jonction contenant les clés primaires des tables. La combinaison des clés dans la table de jonction doit être unique.

Notez que la table de jonction peut contenir des informations relatives à l'association; Par exemple, le PhoneNumber est associé au type de numéro de client et de téléphone. Un client ne peut pas avoir le même numéro de téléphone répertorié comme deux types différents.

Un bon aspect de l'obtention de données à partir d'une base de données relationnelle est que le modèle est très similaire à un modèle que vous allez créer dans Power Pivot. En fait, si les relations sont définies dans la base de données, l'assistant d'importation Power Pivot peut les détecter et les configurer dans le modèle pour vous. La première étape pour obtenir des données à partir d'une base de données relationnelle est de créer une connexion. Dans l'onglet Accueil du Concepteur de modèles, il y a un regroupement de données externes (voir Figure).

Le menu déroulant De la base de données vous permet de vous connecter à SQL Server, Access, Analysis Services ou à partir d'un autre modèle Power Pivot. Si vous cliquez sur le bouton À partir d'autres sources, vous pouvez voir toutes les sources de données disponibles pour vous connecter (voir la Figure).

Comme vous pouvez le voir, vous pouvez vous connecter à un certain nombre de bases de données relationnelles. Si vous avez besoin de vous connecter à une base qui n'est pas répertorié, vous pouvez être en mesure d'installer un pilote à partir du fournisseur de base de données pour s'y connecter. Vous pouvez également être en mesure d'utiliser le générique ODBC (Open Database Connectivity) pilote pour vous connecter à elle.

Après avoir sélectionné une source de données, une fenêtre vous permet de saisir les informations de connexion. Les informations de connexion dépendent de la source de données à laquelle vous vous connectez. Pour la plupart des bases de données relationnelles, les informations requises sont très similaires. La Figure montre les informations de connexion pour la connexion à un serveur SQL.

N'oubliez pas de cliquer sur le bouton Test Connection pour vous assurer que tout est correctement saisi.

Après avoir configuré la connexion, l'étape suivante consiste à interroger la base de données pour récupérer les données. Vous avez deux choix à ce stade: Vous pouvez choisir d'importer les données à partir d'une liste de tables et de vues ou d'écrire une requête pour importer les données (voir Figure). Même si vous choisissez d'importer les données d'une table ou d'une vue sous les couvertures, une requête est créée et envoyée à la base de données pour récupérer les données.

Si vous choisissez d'obtenir les données d'une liste de tables et de vues, la liste s'affiche dans l'écran suivant. De votre point de vue, une vue et une table semblent les mêmes. En réalité, une vue est vraiment une requête stockée dans la base de données qui masque la complexité de la requête de votre part. Les vues sont souvent utilisées pour montrer un modèle conceptuel plus simple de la base de données que le modèle physique réel. Par exemple, vous pouvez avoir besoin de l'adresse d'un client. La Figure montre les tables que vous devez inclure dans une requête pour obtenir les informations. Au lieu d'écrire une requête complexe pour récupérer les informations, vous pouvez sélectionner dans une vue qui combine les informations dans une table d'adresse client virtuelle pour vous. Une autre utilisation courante d'une vue est de sécuriser les colonnes de la table sous-jacente.

Grâce à l'utilisation d'une vue, l'administrateur de la base de données peut masquer les colonnes de différents utilisateurs.

En sélectionnant une table et en cliquant sur le bouton Aperçu & Filtre (voir Figure), vous pouvez prévisualiser les données dans la table et filtrer les données sélectionnées.

Une façon de filtrer une table consiste à sélectionner uniquement les colonnes qui vous intéressent. L'autre moyen est de limiter le nombre de lignes en plaçant une condition de filtre sur la colonne. Par exemple, vous ne souhaitez que des ventes après une certaine année. En cliquant sur le menu déroulant à côté d'une colonne, vous pouvez entrer un filtre pour limiter les lignes. La figure montre la table SalesOrderHeader filtrée par date de commande.

Lorsque vous travaillez avec de grands ensembles de données, il est judicieux, pour des raisons de performance, d'importer uniquement les données qui vous intéressent. Il y a beaucoup de frais généraux pour mettre toutes les colonnes d'une table si vous êtes intéressé par quelques-unes. De même, si vous êtes seulement intéressé par les trois dernières années de ventes, ne pas apporter dans les 20 années entières de données de vente. Vous pouvez toujours revenir en arrière et mettre à jour l'importation des données pour y ajouter plus de données si vous en avez besoin. Après avoir filtré les données, vous cliquez sur Terminer dans l'écran Sélectionner les tables et les vues (voir Figure). À ce stade, les données sont introduites dans le modèle et vous voyez un écran indiquant la progression (voir Figure). S'il n'y a pas d'erreurs, vous pouvez fermer l'Assistant d'importation de table.

Si vous passez à la vue de diagramme du Concepteur de modèles, vous verrez les tables et si les relations de table ont été définies dans la base de données, vous verrez les relations entre les tables. Dans la Figure, vous pouvez voir les relations définies entre les tables de produits et celles définies entre les tables de vente, mais aucune définie entre la table SalesOrderDetail et la table Product. Vous pouvez créer une relation dans le modèle même si celle-ci n'a pas été définie dans la source de données (plus loin).

Bien que la sélection à partir de tables et de vues est un moyen facile d'obtenir des données dans le modèle sans avoir à explicitement Écrire une requête, ce n'est pas toujours possible. Parfois, vous devrez peut-être écrire vos propres requêtes; Par exemple, vous pouvez Pour combiner des données de plusieurs tables différentes si aucune vue n'est disponible. Un autre facteur est ce qui est soutenu par les données la source. Certaines sources de données n'autorisent pas les vues et peuvent vous obliger à fournir des requêtes pour extraire les données. Dans ces Cas, lorsque vous arrivez à l'écran qui vous demande comment vous voulez récupérer les données, sélectionnez l'option de requête (Figure).

Une fois que vous avez sélectionné l'option de requête, vous êtes présenté avec un écran où vous pouvez écrire dans une requête (voir Figure). Bien que vous ne puissiez pas écrire la requête à partir de zéro, c'est là que vous souhaitez coller dans une requête écrite pour vous ou un Que vous avez créé dans un autre outil tel que Microsoft Management Studio ou TOAD. N'oubliez pas de nommer la requête. Il deviendra une table dans le modèle avec le nom de la requête.

 Si la source de données le supporte, vous pouvez lancer un joli concepteur de requêtes en cliquant dans le coin inférieur droit de la fenêtre d'entrée de requête (voir Figure ci-dessus). Ce concepteur vous permet de sélectionner les colonnes que vous voulez dans les différentes tables et vues. Si les relations de table sont définies dans la base de données, elle ajoutera les jointures de table pour vous. Vous pouvez également appliquer des filtres et regrouper et agréger les données. Un aspect déroutant du concepteur de requête est la case à cocher paramètre.

Une fois que vous avez terminé de concevoir la requête, vous devez toujours l'exécuter pour vous assurer qu'elle fonctionne comme vous l'avez prévu (voir Figure).

Une fois que vous êtes satisfait de la requête, la sélection du bouton OK vous ramène à l'écran précédent avec le texte de requête entré. Vous pouvez modifier la requête dans cet écran et utiliser le bouton Valider pour vous assurer qu'il reste une requête valide (voir Figure). Cliquer sur Terminer apportera les données et la table dans le modèle.

Maintenant que vous savez comment importer des données à partir d'une base de données, voyons comment vous pouvez ajouter des données au modèle à partir d'un fichier texte.

Importation de données à partir de fichiers texte

Il y a plusieurs fois où vous devez combiner des données provenant de plusieurs sources différentes. L'une des sources de données les plus courantes est encore le fichier texte. Cela pourrait être le résultat de la réception de données en tant que sortie d'un autre système; Par exemple, vous pourriez avoir besoin d'informations provenant du système ERP (Enterprise Resource Planning) de votre entreprise, fourni sous forme de fichier texte. Vous pouvez également obtenir des données par le biais de services tiers qui fournissent les données dans un format CSV (valeurs séparées par des virgules). Par exemple, vous pouvez utiliser un service de notation pour évaluer les clients et les résultats peuvent être retournés dans un fichier CSV. L'importation de données dans votre modèle à partir d'un fichier texte est similaire à l'importation de données à partir d'une table de base de données relationnelle. Vous devez d'abord sélectionner l'option permettant d'obtenir des données externes provenant d'autres sources dans le menu d'accueil, ce qui ouvre l'option de connexion à une source de données. Faites défiler l'écran jusqu'au bas de la fenêtre et vous pouvez choisir d'importer des données à partir d'un fichier Excel ou d'un fichier texte.

La sélection du fichier texte fait apparaître un écran dans lequel vous entrez le chemin du fichier et le délimiteur de fichier. Chaque fichier texte est considéré comme une table et le nom conviviale de la connexion sera le nom de la table dans le modèle. Une fois que vous fournissez les informations de connexion, les données sont chargées pour la prévisualisation et le filtrage (voir Figure).

La sélection de la liste déroulante à côté de l'en-tête de colonne permet de limiter les rangées apportées en fonction d'un critère de filtrage.

La principale différence entre importer des données à partir d'un fichier texte et importer des données à partir d'un fichier Excel est que le fichier Excel peut contenir plus d'une table. Par défaut, chaque feuille est traitée comme une table (voir Figure). Une fois que vous avez sélectionné la table, vous avez la possibilité de prévisualiser et de filtrer les données comme vous l'avez fait pour un fichier texte.

En plus d'importer des données à partir d'un fichier texte, vous devrez peut-être compléter votre modèle de données à l'aide de données importées d'un flux de données. C'est devenu un moyen très commun d'échanger des données avec des partenaires commerciaux et vous verrez comment le faire maintenant.

Importation de données à partir d'un flux de données

Bien que les fichiers texte constituent l'un des moyens les plus populaires d'échanger des données, les flux de données deviennent une méthode de plus en plus répandue d'échange de données. Les flux de données fournissent les données via les services Web et, pour vous connecter au service Web, vous entrez l'adresse Web du service Web.

Dans la Figure, vous pouvez voir les connexions d'alimentation de données disponibles. Le plus souvent, vous connaissez l'adresse du service de données fourni par un partenaire ou un fournisseur de données. Dans ce cas, vous choisissez d'autres flux qui vous fournissent alors une fenêtre d'entrée d'informations de connexion, comme le montre la Figure.

Comme le flux de données contient non seulement les données mais aussi les métadonnées (description des données), une fois la connexion établie, Power Pivot vous permet de prévisualiser et de filtrer les données, comme le montre la Figure.

Un couple de flux de données intéressantes que vous pouvez consommer sont les données des rapports Reporting Services et des listes SharePoint. Ces applications peuvent facilement exposer leurs données en tant que flux de données que vous pouvez consommer en tant que source de données. De plus, de nombreux fournisseurs de bases de données tels que SAP supportent la possibilité d'exposer leurs données sous forme de flux de données.

Réutilisation des connexions existantes pour mettre à jour le modèle

Il existe deux scénarios où vous souhaitez réutiliser une connexion existante à une source de données. Vous devrez peut-être extraire des données supplémentaires d'une source de données; Par exemple, vous devez obtenir des données à partir de tables ou de vues supplémentaires ou émettre une nouvelle requête. Dans ce cas, vous devez choisir le bouton des connexions existantes situé sur l'onglet Accueil (voir Figure)

Dans la fenêtre Connexions existantes, sélectionnez la connexion et cliquez sur le bouton Ouvrir (voir Figure). Cela lancera les écrans (qui dépendent du type de connexion), couverts précédemment, où vous passez par le processus de sélection des données que vous souhaitez importer.

L'autre scénario consiste à modifier le filtrage ou à ajouter des colonnes à une table existante du modèle. Dans ce cas, vous devez sélectionner le tableau dans le mode de visualisation des données du concepteur et, dans l'onglet Conception, sélectionner Propriétés du tableau (voir Figure).

La fenêtre Modifier les propriétés du tableau vous permet de mettre à jour la requête utilisée pour remplir la table de données. Vous pouvez soit mettre à jour la table en mode Prévisualisation de tableau ou en mode Requête. En mode Éditeur de requêtes, vous pouvez également lancer le concepteur de requêtes pour mettre à jour la requête.

Maintenant que vous avez vu comment importer les données de diverses sources de données dans le modèle de données Power Pivot, il est temps d'acquérir une expérience pratique en important les données.

Téléchargez le carnet de Travaux dirigés et le classeur Excel.

Merci

Résumé

L'une des premières étapes de la création du modèle Power Pivot est l'importation de données. 

Dans ce chapitre, vous avez appris comment importer des données provenant de diverses sources de données. 

L'une des fonctionnalités intéressantes de l'importation de données dans Power Pivot est que l'expérience est similaire lorsque vous importez les données à partir des différentes sources de données. Vous créez une connexion, fournissez une requête ou sélectionnez des tables, prévisualisez et filtrez les données, puis importez-les dans le modèle. 

Maintenant que vous avez importé les données dans les différentes tables de votre modèle, vous devez établir des relations entre ces tables. Dans le chapitre suivant, vous apprendrez l'importance de la mise en place des relations ainsi que la façon d'augmenter le modèle avec des hiérarchies pour faciliter l'analyse des données.

Création du modèle de données

Maintenant que vous savez comment obtenir des données dans le modèle Power Pivot, la prochaine étape est de comprendre ce qui fait un bon modèle. Ceci est très important pour les données de Power Pivot. Un bon modèle permettra à Power Pivot d'effectuer rapidement des analyses de données de façon nouvelle et intéressante.

Un mauvais modèle entraînera un fonctionnement très lent du Power Pivot et, au pire, donnera des résultats trompeurs lors de l'analyse des données. Les tableaux croisés dynamiques Excel traditionnels sont basés sur une table unique contenue dans une feuille Excel. Les tables Power pivots sont basées sur plusieurs tables contenues dans le modèle de données.

Ce chapitre vous guide à travers le processus de création d'un modèle solide qui deviendra le fondement de votre analyse de données. En outre, vous verrez comment présenter un modèle convivial aux outils clients. Cela inclut le renommage des tables et des champs, la présentation des types de données appropriés et la dissimulation de champs étrangers.

Après avoir terminé ce chapitre, vous pourrez :

• Expliquer ce qu'est un modèle de données
• Créer des relations entre les tables du modèle
• Créer et utiliser un schéma en étoile
• Comprendre quand et comment dénormaliser les données
• Créer et utiliser des tables liées
• Créer et utiliser des hiérarchies
• Faire un modèle convivial

Qu'est-ce qu'un modèle de données?

Fondamentalement, un modèle de données est constitué de tables, de colonnes, de types de données et de relations de tables. Typiquement, les tables de données sont conçues pour contenir des données pour une entité commerciale; Par exemple, les données client sont contenues dans une table client et les données salariales sont contenues dans une table employée. Les tableaux se composent de colonnes qui définissent les attributs de l'entité. Par exemple, vous voudrez peut-être détenir des informations sur les clients telles que le nom, l'adresse, la date de naissance, la taille du ménage, etc. Chacun de ces attributs a un type de données qui dépend de quelles informations l'attribut contient le nom serait un type de données chaîne, la taille du ménage serait un entier et la date de naissance serait une date. Chaque ligne dans la table doit être unique. Prenez une table client, par exemple; Si vous aviez le même client dans plusieurs lignes avec des attributs différents, par exemple date de naissance, vous ne sauriez pas qui était correct. Dans l'exemple précédent, vous sauriez qu'une des lignes était incorrecte car la même personne ne pouvait pas avoir deux anniversaires différents.

Cependant, il y a des fois quand vous voulez suivre les changements dans les valeurs d'attribut pour une entité. Par exemple, le prix catalogue d'un produit va probablement changer avec le temps. Pour suivre la modification, vous devez ajouter un horodatage pour rendre la ligne unique. Ensuite, chaque ligne peut être identifiée par le numéro de produit et l'horodatage,

Comme le montre la figure

Une fois que vous avez les tableaux du modèle identifié, il est important pour vous de reconnaître si les tables sont configurées pour fonctionner efficacement. Ce processus est appelé normalisation du modèle. La normalisation est le processus d'organisation des données pour rendre l'interrogation des données plus facile et plus efficace. Par exemple, vous ne devez pas mélanger les attributs d'entités non liées dans la même table que vous ne souhaitez pas que les données de produit et les données d'employé dans la même table.

Un autre exemple de normalisation appropriée consiste à ne pas conserver plus d'un attribut dans une colonne. Par exemple, au lieu d'avoir une colonne d'adresse client, vous la diviseriez en rue, ville, état et zip. Cela vous permettrait d'analyser facilement les données par état ou par ville.

La feuille de calcul illustrée à la figure montre une table typiquement non normalisée. Si vous constatez que les données fournies ne sont pas suffisamment normalisées, vous devrez peut-être demander à quiconque fournit les données de diviser les données en plusieurs tables que vous pourrez ensuite relier ensemble dans votre modèle.

Une fois que vous êtes convaincu que les tableaux de votre modèle sont correctement normalisés, l'étape suivante consiste à déterminer comment les tables sont liées. Par exemple, vous devez relier la table client, la table des ventes et la table produit afin d'analyser la façon dont les différents produits sont vendus par groupe d'âge.

La façon dont vous reliez les différentes tables est à l'aide de touches. Chaque ligne d'une table a besoin d'une colonne ou d'une combinaison de colonnes qui identifie la ligne de façon unique. C'est ce qu'on appelle la clé primaire. La clé peut être facilement identifiée, comme un numéro de commande client ou un numéro de client qui a été attribué par l'entreprise lors de la saisie des données. Parfois, vous devrez faire une analyse d'une table pour trouver la clé primaire, surtout si vous obtenez les données d'une source externe. Par exemple, vous pouvez obtenir des données qui contiennent des clients potentiels. Les champs sont le nom, ville, état, zip, date de naissance, et ainsi de suite. Vous ne pouvez pas simplement utiliser le nom comme la clé, car il est très probable que vous avez plus d'un client avec le même nom. Si vous utilisez la combinaison de nom et de ville, vous avez moins de chances d'avoir plus d'un client identifié par la même clé. Comme vous l'utilisez plus de colonnes, telles que zip et date de naissance, vos chances sont encore mieux.

Lorsque vous allez associer des tables dans le modèle, la clé primaire d'une table devient une clé étrangère dans la table associée. Par exemple, pour lier un client à ses ventes, la clé du client doit être contenue dans la table des ventes où elle est considérée comme une clé étrangère. Lors de l'extraction des données, les clés sont utilisées pour obtenir les données associées.

De loin, le meilleur type de clé à utiliser pour des raisons de performance est un entier de colonne unique. Pour cette raison, un grand nombre de tables de base de données sont conçus avec une clé de substitution. Cette clé est un entier qui est affecté à l'enregistrement lorsqu'il est chargé. Au lieu d'utiliser la clé naturelle, la substitution est utilisée pour connecter les tables. La figure montre une table de base de données typique contenant à la fois la clé de substitution (CustomerKey) et la clé naturelle (CustomerAlternateKey).

Il est important que vous connaissiez les clés utilisées dans vos sources de données. Si vous pouvez récupérer les clés de la source, vous êtes beaucoup mieux lotis. Ce n'est généralement pas un problème lorsque vous récupérez des données d'une base de données relationnelle, mais si vous combinez des données de différents systèmes, assurez-vous d'avoir les clés appropriées. Une fois que vous avez les clés entre les tables identifiées, vous êtes prêt à créer les relations dans le modèle Power Pivot.

Création de relations de table

Il y a quelques règles à retenir lors de l'établissement des relations de table dans un modèle Power Pivot.

Premièrement, vous ne pouvez pas utiliser les clés composites dans le modèle. Si votre table utilise une clé composite, vous devrez créer une nouvelle colonne en concaténant les colonnes composées et en utilisant cette colonne comme clé.

Deuxièmement, vous ne pouvez avoir qu'un chemin de relation actif entre deux tables, mais vous pouvez avoir plusieurs relations inactives.

Troisièmement, les relations sont un à plusieurs; En d'autres termes, créer une relation entre la table client (une entrée) et la table des ventes (de nombreuses entrées) est ok. Créer une relation directe entre la table des clients et la table des produits n'est pas autorisé, cependant, parce que le client peut acheter de nombreux produits et le même produit peut être acheté par de nombreux clients. Dans ces cas, vous créez une table de jonction pour relier les tables.

Pour créer une relation entre deux tables dans le modèle Power Pivot,

• vous ouvrez le gestionnaire de modèle de données et passez à la vue de diagramme.
• Cliquez avec le bouton droit de la souris sur la table qui représente les nombreux aspects de la relation et sélectionnez Créer une relation.
• Ceci lance la fenêtre Créer une relation.
• Sélectionnez la table de recherche associée (un côté) et la colonne de clé. La figure montre la création d'une relation entre la table Sales et la table Store. Par défaut, la première relation créée entre les tables est marquée comme étant la relation active.

Vous pouvez créer plus d'une relation entre deux tables, mais n'oubliez pas qu'une seule peut être la relation active. Si vous essayez de faire deux relations actives, vous obtenez une erreur.

Parfois, la relation active n'est pas si évidente. La figure montre une relation active entre la table Sales et la table Date et une autre entre la table Sales et la table Store. Il existe également une relation inactive entre la table Stocker et la table Date. Si vous essayez de le rendre actif, vous obtenez le même message d'erreur. Cela est dû au fait que vous pouvez tracer un chemin actif de la table Date à la table Sales à la table Store.

Une autre erreur courante que vous pouvez rencontrer est lorsque vous essayez de créer une relation entre deux tables et la clé n'est pas unique dans au moins une des tables. La figure montre une table Product et une table Sales qui contiennent tous deux ProductNumbers en double.

Dans cet exemple, le ProductNumber est censé être unique dans la table Product mais il s'avère qu'il y a des doublons. Pour résoudre ce problème, vous devez modifier la requête pour les données de la table Product pour vous assurer que vous n'obtenez pas de doublons. Maintenant que vous savez comment créer des relations de table dans le modèle, vous êtes prêt à examiner les avantages de l'utilisation d'un schéma en étoile.

Création d'un schéma en étoile

Lors de la création d'un modèle de données, il est important de comprendre pour quoi le modèle est utilisé. Les deux principales utilisations des bases de données sont la saisie de données et le rapport l'analyse des données. Le problème est que lorsque vous créez un modèle pour une capture de données efficace, vous diminuez son efficacité pour analyser les données. Pour lutter contre ce problème, de nombreuses entreprises ont séparé la base de données de capture de données à partir de leur base de données de rapports d'analyse. Heureusement, lors de la création du modèle de données dans Power Pivot, nous avons seulement besoin de l'accorder pour les rapports. L'un des meilleurs modèles à utiliser lors de l'analyse de grands ensembles de données est le schéma en étoile. Le schéma en étoile se compose d'une table de faits centrale entourée de tables de dimensions, comme le montre la figure.

Le tableau des faits contient des données quantitatives relatives à l'entreprise ou au processus. Par exemple, le tableau Ventes de la figure contient des aspects mesurables d'une vente, tels que les coûts totaux, le montant des ventes et la quantité. Les tables de faits contiennent généralement plusieurs lignes et comportent un composant de date ou d'heure qui enregistre le moment où l'événement s'est produit.

Les tables de dimension contiennent des attributs sur l'événement. Par exemple, la table Date peut vous indiquer quand la vente s'est produite et vous permet d'enrôler les données au niveau du mois, du trimestre ou de l'année. La table des produits contient des attributs concernant le produit vendu et vous pouvez consulter les ventes par gamme de produits, par couleur et par marque. La table Store contient des attributs concernant le magasin impliqué dans la vente. Les tables de dimensions ne contiennent généralement pas autant de lignes que la table de faits, mais peuvent contenir plusieurs colonnes. Lorsque vous posez une question comme «Quels vélos a la meilleure vente dans les différents groupes d'âge?» Les mesures (valeurs en dollars des ventes) proviennent de la table des faits alors que les catégorisations (âge et modèle de vélo) proviennent des tables de dimension.

Le principal avantage du schéma en étoile est qu'il fournit une performance de requêtes rapide et un traitement d'agrégation. L'inconvénient est qu'il nécessite généralement beaucoup de prétraitement pour déplacer les données d'un système transactionnel hautement normalisé vers un système de reporting plus dénormalisé. La bonne nouvelle est que votre entreprise peut disposer d'un système de rapports alimentant une base de données de traitement analytique en ligne (OLAP) traditionnelle, comme Analysis Server de Microsoft ou Cognos d'IBM. Si vous pouvez accéder à ces systèmes, ils sont probablement la meilleure source pour vos données métier de base.

Pour créer un schéma en étoile à partir de vos systèmes de données source, vous devrez peut-être effectuer une dénormalisation des données, qui est abordée dans la section suivante.

Comprendre quand dénormaliser les données

Bien que les systèmes de bases de données transactionnelles aient tendance à être fortement normalisés, les systèmes de reporting sont dénormalisés dans le schéma en étoile. Si vous n'avez pas accès à un système de rapports où la dénormalisation est effectuée pour vous, vous devrez dénormaliser les données dans un schéma en étoile pour charger votre modèle Power Pivot. Par exemple, la figure montre les tables qui contiennent les données client dans la base de données des ventes transactionnelles Adventureworks.

Pour dénormaliser les données client dans votre modèle, vous devez créer une requête qui combine les données dans une seule table de dimension client. Si vous n'êtes pas familier avec la création de requêtes complexes, la façon la plus simple de le faire est de demander aux développeurs de base de données de créer une vue où vous pouvez extraire des données qui combinent les tables pour vous. Si la requête n'est pas trop complexe, vous pouvez probablement la créer vous-même lorsque vous importez les données. Par exemple, la Figure montre une table client et une table géographique.

Vous pouvez les combiner en une seule table de dimension client en utilisant la requête suivante.

SELECT c.CustomerKey, c.BirthDate, c.MaritalStatus, c.Gender, c.YearlyIncome, c.NumberChildrenAtHome, c.EnglishOccupation, c.HouseOwnerFlag, c.NumberCarsOwned, c.CommuteDistance, g.City, g.StateProvinceName, g.EnglishCountryRegionName, g.PostalCode

FROM Customer AS c INNER JOIN

Geography AS g ON c.GeographyKey = g.GeographyKey

Bien que vous n'ayez pas à être un expert de requêtes pour obtenir des données dans votre modèle Power Pivot, il est très bénéfique de connaître les bases de l'interrogation des sources de données, même si cela aide à poser les bonnes questions lorsque vous parlez aux développeurs de bases de données.

Création de tables liées

Une chose à retenir sur les données contenues dans le modèle PowerPivot est qu'il est en lecture seule. Pour la plupart, c'est une bonne chose. Vous voulez vous assurer que les données sont uniquement modifiées dans le système source. Parfois, cependant, dans le cadre de l'analyse des données, vous voulez faire des ajustements à la volée et voir comment cela affecte les résultats. Un exemple qui arrive assez souvent est l'analyse du bac à sable. Par exemple, disons que vous voulez regarder une mesure continue comme l'âge dans les bacs discrets (gammes) et vous devez ajuster les plages pendant l'analyse. C'est là que les tables liées brillent. Pour créer la table liée, vous devez tout d'abord configurer le tableau sur une feuille Excel. Après avoir saisi les données, vous les sélectionnez et cliquez sur le menu d'analyse rapide (voir Figure). Sous l'onglet Tables cliquez sur le bouton Tableaux.

Une fois la table créée, il est conseillé de la nommer avant de l'importer dans le modèle Power Pivot. Vous pouvez le faire en sélectionnant le tableau, puis en entrant le nom de la table sous l'onglet Création dans la fenêtre contextuelle Outils de tableau (voir Figure).

Une fois la table nommée, sélectionnez-la et, dans l'onglet powerPivot, sélectionnez Ajouter au modèle de données (voir Figure).

Une fois la table importée dans le modèle, si vous modifiez certaines valeurs de la table dans la feuille Excel et sélectionnez Mettre à jour tout dans l'onglet PowerPivot (voir Figure), la table liée dans le modèle sera mise à jour avec les nouvelles valeurs.

Création de hiérarchies

Lors de l'analyse des données, il est souvent utile d'utiliser des hiérarchies pour définir différents niveaux d'agrégations. Par exemple, il est courant d'avoir une hiérarchie basée sur les niveaux d'année, de trimestre et de mois. Un agrégat comme le montant des ventes est ensuite enroulé d'un mois à un trimestre à l'autre. Une autre hiérarchie commune pourrait être d'un ministère à l'autre. Vous pourriez alors rouler le coût à travers les différents niveaux.

La création de hiérarchies dans un modèle Power Pivot est très simple.

• Dans la vue de diagramme du concepteur de modèle,
• sélectionnez la table qui a les attributs (colonnes) que vous voulez dans la hiérarchie.
• Vous verrez alors un bouton Créer une hiérarchie dans le coin supérieur droit de la table (voir Figure).
• Cliquez sur le bouton pour ajouter la hiérarchie et la renommer.
• Vous pouvez ensuite faire glisser et déposer les champs dans la hiérarchie pour créer les différents niveaux. Assurez-vous d'arranger les champs pour qu'ils passent d'un niveau supérieur à un niveau inférieur.

Créer des hiérarchies est un moyen d'accroître la convivialité de votre modèle et d'aider les utilisateurs à gagner instinctivement plus de valeur dans leur analyse de données. Dans la section suivante, vous verrez d'autres choses que vous pouvez faire sur le modèle pour en accroître la convivialité.

Créer un modèle convivial

Quand vous créez votre modèle une chose à garder à l'esprit est de rendre le modèle facile et intuitif à utiliser. Les chances sont que le modèle peut être utilisé par d'autres pour l'analyse. Le modèle peut également être utilisé pour une grande variété d'outils de génération de rapports et d'analyse des clients, tels que PowerView, PowerMap et le Reporting Services. Il y a des propriétés et des paramètres que vous pouvez utiliser qui permettent à ces outils client d'obtenir plus de fonctionnalités à partir du modèle.

Un des ajustements les plus efficaces que vous pouvez faire est de renommer les tables et les colonnes. Utilisez des noms qui ont du sens pour les utilisateurs professionnels et non pas la convention de dénomination cryptique qui n'a de sens que pour les développeurs de bases de données.

Une autre bonne pratique est de s'assurer que les types de données et les formats des colonnes sont définis correctement. Un champ à partir d'un fichier texte peut être entré tapé comme une chaîne quand en réalité ce sont des données numériques. En outre, vous pouvez cacher des champs qui ne sont d'aucune utilité pour l'utilisateur, comme les clés de substitution utilisées pour lier les tables.

Une exigence commune est de changer l'ordre de tri d'une colonne de son tri naturel. L'exemple le plus courant est les mois de l'année. Puisqu'il s'agit de texte par défaut, ils sont triés par ordre alphabétique. En réalité, vous voulez les triés par numéro de mois. Pour corriger cela, vous pouvez trier une colonne par n'importe quelle autre colonne de la table (voir Figure). C'est une fonctionnalité intéressante et vous permet de créer votre propre tri personnalisé lié à l'entreprise.

Vous pouvez utiliser d'autres paramètres pour créer de bons modèles pour les différents outils clients. Vous reviendrez sur cette rubrique plus en détail, «Optimisation des modèles tabulaires pour Power View».

Téléchargez le carnet de Travaux dirigés et le classeur Excel.

Merci

Résumé

Lorsque vous travaillez dans Power Pivot, il est très important de comprendre ce qui fait un bon modèle. Un bon modèle fera que Power Pivot va effectuer rapidement et vous permettre d'analyser facilement de grandes quantités de données. 

Contrairement aux tableaux croisés dynamiques d'Excel traditionnelles, qui sont basées sur une table unique contenue dans une feuille Excel, les tables pivots Power Pivot sont basées sur plusieurs tables contenues dans le modèle de données.

Ce chapitre vous a guidé à travers le processus de création d'un modèle solide qui deviendra le fondement de votre analyse de données. En outre, vous avez vu comment présenter un modèle convivial aux outils clients. 

Maintenant que vous avez une base solide pour votre modèle, vous êtes prêt à étendre le modèle avec des calculs personnalisés. Le chapitre suivant présente le langage DAX (Data Analysis Expressions) et explique comment créer des colonnes calculées dans le modèle de données. Il comprend de nombreux exemples pour vous aider à créer des calculs communs dans le modèle.

Qu'est-ce que DAX?

DAX est un langage de formule utilisé pour créer des colonnes et des mesures calculées dans le modèle Power Pivot. Il s'agit d'une nouvelle langue développée spécifiquement pour le modèle de données tabulaires de Power Pivot. Si vous connaissez la syntaxe de la formule d'Excel, vous constaterez que la syntaxe DAX est très familière. En fait, certaines formules DAX ont la même syntaxe et la même fonctionnalité que leurs homologues Excel. La principale différence, et celle que vous devez garder dans votre tête, est que les formules d'Excel sont basées sur les cellules alors que DAX est basé sur une colonne. Par exemple, si vous souhaitez concaténer deux valeurs dans Excel, vous devez utiliser une formule comme suit:

= A1 & "" & B1

Où A1 est la cellule dans la première rangée et la première colonne et B1 est la cellule dans la deuxième colonne de la première rangée.

C'est très similaire à la formule DAX:

= [Prénom] & "" & [Nom de famille]

Où le nom et le prénom sont des colonnes d'un tableau du modèle.

La différence est que la formule DAX est appliquée à toutes les lignes dans la table tandis que la formule Excel fonctionne uniquement sur les cellules spécifiques. Dans Excel, vous devez recréer la formule dans chaque ligne. Ce que cela signifie est bien que vous pouvez faire quelque chose comme ceci dans Excel:

= A1 & "" & B2

Où vous prenez une cellule de la première ligne et concaténer avec une cellule de la deuxième ligne, cela ne peut pas être fait dans DAX.

Lors de la création de formules DAX, il est important de tenir compte des types de données et des conversions qui peuvent avoir lieu pendant les calculs. Si vous ne les prenez pas en compte, vous pouvez rencontrer des erreurs dans la formule ou des résultats inattendus.

Les types de données pris en charge dans le modèle sont le nombre entier, le nombre décimal, la devise, le caractère booléen, le texte et la date. DAX a également un type de données de table qui est utilisé dans de nombreuses fonctions qui prennent une table comme valeur d'entrée et renvoient une table. Lorsque vous essayez d'ajouter un type de données numériques avec un type de données texte, vous obtenez une conversion implicite. Si DAX peut convertir le texte en valeur numérique, il va les ajouter en tant que numéros, sinon vous obtiendrez une erreur. En revanche, si vous essayez de concaténer un type de données numériques avec un type de données texte, DAX convertira implicitement le type de données numériques en texte. Bien que la plupart du temps implicite conversions vous donner les résultats que vous recherchez, ils viennent à un coût de performance et doivent être évités si possible. Par exemple, si vous importez des données à partir d'un fichier texte et que la colonne est définie sur un type de données texte, mais que vous savez qu'il s'agit en fait d'un nombre, vous devez modifier le type de données dans le modèle. Lorsque vous créez des calculs dans DAX, vous devrez référencer les tables et les colonnes. Si le nom de la table ne contient pas d'espace, vous pouvez simplement vous y référer par son nom. Si le nom de la table contient des espaces, vous devez le placer entre guillemets simples. Les colonnes et les mesures sont placées entre crochets. Si vous venez de lister le nom de la colonne dans la formule, il est supposé que la colonne existe dans la même table. Si vous faites référence à une colonne d'une autre table, vous devez utiliser le nom complet, qui est le nom de la table suivi du nom de la colonne. Le code suivant illustre la syntaxe:

= [SalesAmount] - [TotalCost]

= Ventes [SalesAmount] - Ventes [TotalCost]

= 'Ventes Internet' [SalesAmount] - 'Ventes Internet' [TotalCost]

Voici quelques autres points à garder à l'esprit lorsque vous travaillez avec DAX:

• Les formules et expressions DAX ne peuvent pas modifier ou insérer des valeurs individuelles dans les tables.
• Vous ne pouvez pas créer de lignes calculées en utilisant DAX. Vous ne pouvez créer que des colonnes et des mesures calculées.
• Lorsque vous définissez des colonnes calculées, vous pouvez imbriquer des fonctions à n'importe quel niveau.

La première chose à comprendre lors de la création d'un calcul est ce que les opérateurs sont pris en charge et quelle est la syntaxe à utiliser. Dans la section suivante, vous examinerez les différents opérateurs DAX.

Implémenter les opérateurs DAX

DAX contient un ensemble robuste d'opérateurs qui comprend l'arithmétique, la comparaison, la logique et la concaténation de texte. La plupart d'entre eux doivent vous être familiers et figurent dans le tableau.

Comme exemple de l'opérateur arithmétique, le code suivant est utilisé pour diviser la colonne Marge par la colonne Coût total pour créer une nouvelle colonne, le pourcentage de marge.

= [Marge] / [TotalCost]

Il est très courant d'avoir plusieurs opérations arithmétiques dans le même calcul. Dans ce cas, vous devez être conscient de l'ordre des opérations. Les exposants sont évalués d'abord suivis de la multiplication / division puis de l'addition / soustraction. Vous pouvez contrôler l'ordre des opérations en utilisant des parenthèses pour les calculs de groupe; Par exemple, La formule suivante effectuera la soustraction avant la division.

= ([Montant des ventes] - [Coût total]) / [Coût total]

Les opérateurs de comparaison sont principalement pour les déclarations if. Par exemple, le calcul suivant vérifie si la taille d'une zone de vente d'un magasin est supérieure à 1000. Si elle est, elle est classée comme un grand magasin, sinon, elle est classée comme petite.

= IF ([Taille de la zone de vente]> 1000, "Grand", "Petit")

Les opérateurs logiques sont utilisés pour créer une logique de comparaison multiple. Le code suivant vérifie si la zone de taille de magasin est supérieure à 1000 ou si elle a plus de 35 employés pour la classer comme grande.

= IF([Taille de la zone de vente]> 1000 || [Nombre d'employés]> 35, "Grand", "Petit")

Lorsque vous démarrez ensemble une série de conditions logiques, il est judicieux d'utiliser des parenthèses pour contrôler l'ordre des opérations. Le code suivant vérifie si la zone de taille de magasin est supérieure à 1000 et s'il a plus de 35 employés pour le classer comme grand. Il sera également classer comme grand si elle a des ventes annuelles de plus de $ 1,000,000 indépendamment de sa superficie de taille ou le nombre d'employés.

= IF(([Taille de la zone de vente]> 1000 && [Nombre d'employés]> 35) || [Ventes annuelles]> 1000000, "Grand", "Petit")

Lorsque vous travaillez avec des calculs DAX, vous devrez peut-être placer une formule à l'intérieur d'une autre. Par exemple, le code suivant place une instruction IF dans la partie fausse d'une autre instruction IF. Si le nombre d'employés n'est pas supérieur à 35, il passe à l'instruction suivante si pour vérifier s'il est supérieur à 20.

= IF (Store [EmployeeCount]> 35, "Large", IF (Magasin [EmployeeCount]> 20, "Medium", "Small"))

DAX contient de nombreuses fonctions utiles pour créer des calculs et des mesures. Ces fonctions incluent des fonctions de texte, des fonctions de date et d'heure, des fonctions statistiques, des fonctions mathématiques et des fonctions d'information. Dans les prochaines sections, vous verrez comment utiliser les différents types de fonctions dans vos calculs.

Utilisation des fonctions de texte

Beaucoup de calculs impliquent une sorte de manipulation de texte. Vous devrez peut-être tronquer, analyser, rechercher ou formater les valeurs de texte que vous chargez à partir des systèmes source. DAX contient de nombreuses fonctions utiles pour travailler avec du texte. Les fonctions sont énumérées dans le tableau ainsi qu'une description de leur utilisation.

Comme exemple d'utilisation d'une fonction de texte dans un calcul, disons que vous avez une colonne de code de produit dans une table de produits où les deux premiers caractères représentent la famille de produits. Pour créer la colonne de famille de produits, utilisez la fonction Gauche comme suit:

= Gauche ([Code Produit], 2)

Vous pouvez utiliser la fonction FIND pour rechercher un sous-texte dans un texte. Vous pouvez utiliser un (?) Pour faire correspondre un caractère unique et un (*) pour correspondre à n'importe quelle séquence de caractères. Vous avez la possibilité d'indiquer la position de départ pour la recherche. La fonction FIND retourne la position de départ de la sous-chaîne trouvée. S'il ne trouve pas la sous-chaîne, il peut renvoyer 0, -1 ou une valeur vide. Le code suivant recherche dans la colonne de description du produit le mot montagne.

= FIND ("montagne", [Description], 1, -1)

La fonction FORMAT convertit une valeur en texte en fonction du format fourni. Par exemple, vous devrez peut-être convertir une date dans un format spécifique. Le code suivant convertit un type de données de date en une chaîne avec un format comme "Mon/ 02/Dec /2013".

= FORMAT ([StartDate], "ddd - MMM dd, yyyy")

Avec la possibilité de créer votre propre format, il existe également des formats prédéfinis que vous pouvez utiliser. Le code suivant illustre l'utilisation du format Date longue "lundi 2 décembre 2013".

= FORMAT ([StartDate], "Long Date")

Maintenant que vous avez vu comment utiliser certaines des fonctions de texte, le prochain type de fonction à examiner sont les fonctions intégrées de date et d'heure.

Utilisation des fonctions Date et heure DAX

Très probablement, vous trouverez que votre analyse des données a un composant de date qui lui est associé. Vous devrez peut-être examiner les ventes ou la consommation d'énergie et connaître le jour de la semaine où l'événement s'est produit. Vous devrez peut-être calculer l'âge ou les dates d'échéance. DAX a un certain nombre de fonctions de date et d'heure pour aider à créer ces types de calculs. Le tableau résume les différentes fonctions de date et d'heure disponibles.

Comme exemple d'utilisation des fonctions de date, vous devez calculer les années de service pour les employés. La première chose que vous devez faire est de trouver la différence entre l'année en cours et l'année où ils ont été embauchés. Le code suivant obtient l'année à partir de la date d'aujourd'hui.

=YEAR(Today())

Maintenant, vous pouvez soustraire l'année de leur date d'embauche. Remarquez que nous plaçons une fonction à l'intérieur d'une autre. Les fonctions d'imbrication sont une exigence commune pour de nombreux calculs.

=YEAR(TODAY()) - YEAR([HireDate])

Les lecteurs astucieux se rendront compte que ce calcul est seulement correct si le mois en cours est supérieur ou égal au mois où ils ont été embauchés. Vous pouvez ajuster pour cela en utilisant une instruction conditionnelle if comme suit:

= If (MONTH(TODAY())>=MONTH([HireDate]),YEAR(TODAY()) - YEAR([HireDate]),YEAR(TODAY()) -

YEAR([HireDate])-1)

Comme vous pouvez le voir, les calculs peuvent être assez compliqués assez rapidement. Le défi est de s'assurer que les parenthèses ouvertes et fermantes de chaque fonction s'alignent correctement. Une façon d'organiser le code est d'utiliser plusieurs lignes et d'indentation. Pour obtenir une nouvelle ligne dans la barre d'édition de formule, vous devez maintenir la touche Maj enfoncée tout en appuyant sur Entrée. Je trouve ce qui suit plus facile à comprendre.

= If (MONTH(TODAY())>=MONTH([HireDate]),

YEAR(TODAY()) - YEAR([HireDate]),

YEAR(TODAY()) - YEAR([HireDate])-1

)

Malheureusement, vous ne pouvez pas ajouter de commentaires à votre code dans Power Pivot. Il y a souvent plus d'une façon de créer un calcul. Vous pouvez trouver un moyen plus facile de faire le calcul ou celui qui fonctionne mieux. Ce qui suit calcule les années de service en utilisant la fonction YEARFRAC et la fonction TRUNC (une des fonctions mathématiques) pour supprimer la partie décimale du nombre.

=TRUNC(YEARFRAC([HireDate],TODAY()))

Le prochain groupe de fonctions que vous allez étudier sont les fonctions informatives et logiques. Ces fonctions sont importantes lorsque vous voulez déterminer si une condition existe comme une valeur vierge ou si une erreur se produit en raison d'un calcul. Ces fonctions vous permettent de piéger les conditions et d'y répondre de manière appropriée.

Utilisation des fonctions informatives et logiques

Lorsque vous commencez à construire des calculs plus complexes, vous devez souvent utiliser des fonctions informatives et logiques pour vérifier les conditions et répondre à diverses conditions. Un exemple courant est la nécessité de vérifier les valeurs vierges. La fonction ISBLANK renvoie TRUE si la valeur est vide et FALSE si elle n'est pas. Le code suivant utilise un calcul différent selon que le deuxième prénom est vide.

=IF(ISBLANK([MiddleName]),

[FirstName] & " " & [LastName],

[FirstName] & " " & [MiddleName] & " " & [LastName]

)

La fonction ISERROR est utilisée pour vérifier si un calcul ou une fonction renvoie une erreur. Le calcul suivant vérifie si une erreur de division par zéro survient au cours d'une division.

= IF (ISERROR ([TotalProductCost] / [SalesAmount]),

BLANK(),

[TotalProductCost] / [SalesAmount]

)

Une autre façon de créer ce calcul est d'utiliser la fonction IFERROR, qui retourne la valeur si aucune erreur ne se produit et une valeur de remplacement si une erreur se produit.

= IFERROR ([TotalProductCost] / [SalesAmount], BLANK ())

Le tableau listent les fonctions logiques et informationnelles disponibles dans DAX.

Lorsque vous analysez des données, vous devez souvent chercher les données correspondantes dans une table liée. Vous devrez peut-être obtenir des descriptions à partir d'un code associé ou résumer des données et les importer dans une table, telles que les ventes. La section suivante examine comment vous allez chercher des données associées à l'aide de DAX.

Obtenir des données dans des tableaux connexes

Il y a des moments où vous devez rechercher des valeurs dans d'autres tables pour terminer un calcul. S'il existe une relation établie entre les tables, vous pouvez utiliser la fonction RELATED. Cela vous permet de dénormaliser les tables et de faciliter la navigation des utilisateurs. Par exemple, vous pouvez avoir une table Client liée à une table Géographie (voir Figure).

Si vous devez examiner les ventes par pays du client, vous pouvez utiliser la fonction associée pour créer une colonne Pays dans la table Customers.

= RELATED (Géographie [CountryRegionName])

Vous pouvez alors masquer la table géographique des outils clients pour garder le modèle plus propre et moins déroutant pour les utilisateurs. Bien que la table associée renvoie une valeur unique, il y a des moments où vous souhaitez examiner un ensemble de données associées et l'agréger avant d'afficher la valeur dans la colonne. Par exemple, vous pouvez ajouter une colonne à la table Customers qui répertorie leur montant de ventes. Dans ce cas, vous utiliserez la fonction RELATEDTABLE pour obtenir les ventes associées puis les résumer pour chaque client.

= SUMX (RELATEDTABLE (Ventes), [SalesAmount])

 Remarque Le code précédent utilise la fonction SUMX qui est utilisée à la place de la fonction SUM parce que vous appliquez un filtre.

L'ensemble final de fonctions que nous considérons sont les fonctions mathématiques, trigonométriques et statistiques. Ces fonctions permettent d'effectuer des analyses courantes telles que les journaux, l'écart-type, l'arrondissement et la troncature.

Utilisation des fonctions Math, Trigonométriques et Statistiques

Avec les fonctions discutées jusqu'ici, DAX comprend également un certain nombre de fonctions mathématiques, de trigonométrie et de statistiques. Les fonctions mathématiques (voir le tableau) sont utilisées pour arrondir, tronquer et synthétiser les données. Elles contiennent également des fonctions que vous pouvez utiliser dans les calculs scientifiques, d'ingénierie et financiers; Par exemple, il peut être nécessaire de calculer le volume d'une sphère en fonction du rayon (voir le calcul DAX suivant):

= 4 * PI () * PUISSANCE ([Rayon], 3) / 3

Comme autre exemple, disons que vous voulez calculer l'intérêt composé d'un investissement. Le calcul DAX suivant détermine le taux de rendement composé pour un investissement.

=[Principal]*POWER(1+([IntRate]/[CompoundRate]),[CompoundRate]*[Years])

Lorsque vous analysez des données, vous voulez souvent regarder non seulement la relation entre les données, mais aussi la qualité des données et comment vous pouvez faire confiance à vos prédictions. C'est là que l'analyse statistique des données intervient. Avec les statistiques, vous pouvez faire des choses comme déterminer et tenir compte des valeurs aberrantes dans les données, examiner la volatilité des données et détecter la fraude. Par exemple, vous pouvez utiliser DAX pour déterminer et filtrer les valeurs aberrantes dans vos données en utilisant l'écart type. La fonction DAX suivante calcule l'écart-type du montant des ventes.

=STDEVX.P(RELATEDTABLE(Sales),Sales[SalesAmount])

Le tableau énumère certaines des fonctions statistiques disponibles dans DAX.

Maintenant que vous avez vu quelles fonctions que vous avez disponibles dans Power Pivot et DAX. Je veux passer en revue quelques conseils sur la création de fonctions en général.

Conseils pour la création de calculs dans le Power Pivot

Avant de vous lâcher sur un laboratoire pratique, je veux vous donner quelques conseils sur la création de ces calculs dans Power Pivot. Lorsque vous saisissez une formule pour une colonne calculée, cliquez avec le bouton droit de la souris sur une colonne de la table dans la fenêtre Vue de données du générateur de modèle. Dans le menu contextuel, sélectionnez Insérer une colonne (voir Figure ).

Renommez la colonne, puis entrez la formule dans la barre d'édition de formule. Les formules commencent par un signe égal (=) et les noms de table sont contenus entre guillemets simples (''), ce qui est facultatif si le nom de table ne contient pas d'espaces. Les colonnes des tableaux sont entre crochets ([]). La barre de l'éditeur de formule fournit une fonctionnalité de saisie semi-automatique que vous devriez utiliser (voir Figure). Sélectionnez la fonction, le tableau ou la colonne dans la liste déroulante et appuyez sur la touche Tab pour l'insérer dans la formule. Si vous ne voyez pas la liste déroulante de saisie automatique, il y a probablement une erreur dans votre formule.

Il y a trois boutons à côté de la barre de l'éditeur de formule: Le X est utilisé pour annuler les modifications que vous avez faites; La coche est utilisée pour valider les changements; Et le symbole de fonction est utilisé pour lancer une fenêtre Insérer une fonction (voir Figure). Vous pouvez consulter les différentes fonctions et obtenir des informations sur les paramètres attendus par les fonctions.

Lorsque vous créez un calcul incorrectement, vous pouvez obtenir un indicateur d'erreur. Vous devez cliquer sur la liste déroulante d'erreur et sélectionner Afficher l'erreur (voir Figure). Cela affichera un message d'erreur, qui vous donnera des informations utiles qui peuvent vous aider à corriger l'erreur.

Maintenant que vous avez vu comment créer des calculs avec DAX et vous familiariser avec les fonctions DAX disponibles, il est temps de gagner de l'expérience pratique.

Téléchargez le carnet de Travaux dirigés et le classeur Excel.

Merci

Résumé

Ce chapitre vous a présenté le langage DAX et les fonctions intégrées que vous pouvez utiliser pour créer des calculs. À ce stade, vous devriez être à l'aise avec la création de colonnes calculées et en utilisant les fonctions DAX.

Je vous recommande fortement de vous familiariser avec les différentes fonctions disponibles et comment les utiliser dans votre analyse.

Dans le chapitre suivant, vous allez continuer à travailler avec DAX pour créer des mesures. Les mesures sont l'une des parties les plus importantes de la construction de votre modèle dans Power Pivot; Les mesures sont la raison pour laquelle vous regardez vos données. Vous voulez répondre à des questions telles que la façon dont les ventes se produisent ou ce qui influence la consommation d'énergie.

Avec la création de mesures, vous verrez également comment les effets de contexte de filtre agissent. Le contexte de filtrage est l'un des concepts les plus importants que vous devez maîtriser pour tirer le meilleur parti de Power Pivot.

Création de mesures avec DAX

La création de mesures dans DAX est la compétence la plus importante nécessaire pour créer des modèles de données solides. Ce chapitre couvre les fonctions courantes utilisées pour créer des mesures dans le modèle de données. Il couvre également le sujet important du contexte des données et la façon de modifier ou de remplacer le contexte lors de la création des mesures.

Après avoir terminé ce chapitre, vous pourrez

• Comprendre la différence entre les mesures et les attributs.
• Comprendre comment le contexte affecte les mesures.
• Créer des agrégats communs.
• Savoir comment et quand modifier le contexte du filtre.
• Créer des KPI.

Différence entre Mesures et Attributs

Si vous regardez un modèle étoile typique pour un entrepôt de données, vous avez une table de faits entourée de tables de dimension. Par exemple, la figure montre une table de faits financiers entourée de plusieurs tables de dimension.

N'oubliez pas que la table des faits contient des nombres que vous devez agréger; Par exemple, dans la table des finances, vous avez le montant, qui est une valeur monétaire qui doit être agrégée. Dans une table de faits de vente, vous pouvez avoir un montant de ventes et un article compte. Dans un système de ressources humaines, vous pourriez avoir des heures travaillées. Les tables de dimension contiennent les attributs que vous utilisez pour catégoriser et redimensionner les mesures. Par exemple, les mesures financières sont classées en tant que bénéfices, pertes et prévisions. Vous souhaitez appliquer les valeurs au niveau département et organisation et vous souhaitez comparer les valeurs entre les mois et les années.

Lorsque vous commencez à découper les données dans un tableau croisé dynamique, les attributs deviennent les en-têtes de ligne et de colonne alors que les mesures sont les valeurs dans les cellules. Les attributs sont aussi couramment utilisés comme filtres dans un menu déroulant de filtre ou dans un segment. La figure présente un tableau croisé contenant les dépenses de recherche et développement, réelles et budgétisées, pour les mois de l'exercice 2006.

Si vous regardez le filtrage de chaque cellule, vous devriez réaliser qu'ils sont tous filtrés un peu différemment. Les deux mesures indiquées par les flèches vertes diffèrent par mois, alors que les mesures indiquées par les flèches rouges diffèrent par rapport à la valeur réelle par rapport au budget. Lorsque vous modifiez l'exercice, le service ou l'organisation, les valeurs des mesures doivent être recalculées car le contexte de la requête a changé. Dans la section suivante, vous verrez comment créer des mesures d'agrégation communes dans votre modèle Power Pivot.

Création d'agrégats communs

Il est très facile de créer des agrégats communs tels que la somme, le comptage ou la moyenne dans Power Pivot. Vous devez d'abord déterminer la table avec laquelle vous voulez associer la mesure. Si vous suivez le modèle de schéma en étoile, ce sera probablement la table des faits, mais il n'a pas à être. Dans le mode d'affichage des données du concepteur du modèle Power Pivot, sélectionnez l'onglet de la table associée à la mesure. La zone sous la grille de données est l'endroit où vous placez les formules de mesure (voir Figure). Si vous ne voyez pas de grille de mesures, vous devrez peut-être la reconnaître en sélectionnant le bouton Zone de calcul Sur l'onglet Accueil (voir Figure).

La méthode la plus simple pour créer un agrégat consiste à sélectionner la colonne que vous souhaitez agréger, puis à sélectionner la liste déroulante Somme automatique et à choisir l'agrégat dont vous avez besoin (voir la figure).

L'agrégat est placé dans la grille de mesure sous la colonne (voir Figure). Si vous sélectionnez la cellule, vous verrez la formule dans la barre de formule (voir Figure). Le nom de la mesure est placé avant la formule suivie d'un signe deux-points et égal (: =). Une fois que vous ajoutez la mesure, vous pouvez la renommer en l'éditant dans l'éditeur de formule.

L'autre façon de créer une mesure agrégée est de sélectionner n'importe quelle cellule dans la grille de mesures et d'entrer la formule dans la barre de formule. Bien que vous puissiez définir une mesure dans n'importe quelle cellule de la grille des mesures, il est judicieux d'organiser les mesures d'une certaine manière. J'essaie généralement d'organiser les mesures le long du côté gauche de la grille afin qu'elles ne soient pas étalées sur toute la grille (voir Figure). Vous pouvez facilement déplacer une définition d'une cellule à l'autre à l'aide de couper et coller.

Vous avez peut-être remarqué que les fonctions agrégées telles que SUM, AVE, MIN et MAX ont des fonctions SUMX, AVEX, MINX et MAXX correspondantes. Les fonctions X sont utilisées lorsque vous évaluez une expression pour chaque ligne dans la table et pas seulement une seule colonne. Par exemple, la fonction SUMX est définie comme suit:

SUMX (<table>, <expression>)

Où la table est la table contenant les lignes à évaluer et l'expression est ce qui sera évalué pour chaque ligne.

Par exemple, disons que vous avez une table de ventes qui contient une colonne Coût et une colonne de Brut. Pour calculer le total des ventes nettes, Vous pouvez prendre le montant brut moins le coût et additionner le résultat pour chaque rangée comme dans la formule suivante:

SumNet: = SUMX (Ventes, [Brut] - [Coût])

Une autre façon d'obtenir le même résultat est de créer une colonne nette calculée d'abord et ensuite utiliser la fonction SOMME sur La colonne nette. La différence est que les colonnes calculées sont pré-calculées et stockées dans le modèle. Les mesures sont calculées lorsque des filtres leur sont appliqués dans le tableau croisé dynamique et doivent être recalculés à chaque fois que le contexte des données change. Donc, les colonnes plus calculées que vous avez la plus grande de la taille de votre fichier Power Pivot. Les mesures plus vous avez et plus leur complexité augmente, plus la mémoire est nécessaire lorsque vous travaillez avec le fichier.

Comprendre comment le contexte des données change la valeur de mesure est très important lors de la création de mesures et Il est exploré dans la section suivante.

Utilisation des fonctions de filtre

Les fonctions de filtrage dans DAX vous permettent de créer des calculs complexes qui nécessitent d'interroger et de manipuler le contexte de données d'une ligne ou d'une cellule dans un tableau croisé dynamique. Le Tableau répertorie et décrit certaines des fonctions de filtrage disponibles dans DAX.

Vous avez déjà vu comment vous pouvez utiliser la fonction CALCULATE en combinaison avec la fonction ALL pour calculer le total des ventes de produits en ignorant tout filtrage de produit appliqué. Jetons un coup d'œil à quelques exemples de plus. La figure montre un modèle de pivot de puissance pour les ventes de revendeur. Dans le modèle, il existe une relation inactive entre les tables Employé et SalesTerritory. Vous pouvez utiliser cette relation pour calculer le nombre de vendeurs dans chaque pays.

Vous pouvez utiliser ceci pour calculer le nombre de représentants de ventes dans chaque pays en utilisant le code suivant:

Sales Rep Cnt:=CALCULATE(DISTINCTCOUNT(Employee[EmployeeNationalIDAlternateKey]),

USERELATIONSHIP(Employee[SalesTerritoryKey],SalesTerritory[SalesTerritoryKey]))

Dans ce cas, vous devez utiliser la fonction CALCULATE afin que vous puissiez appliquer la fonction de filtre USERELATIONSHIP pour indiquer au moteur de requête DAX quelle relation utiliser. La figure montre la table pivot correspondante. Il montre également le résultat que vous obtiendriez si vous venez d'utiliser la fonction DISTINCTCOUNT sans le filtre USERELATIONSHIP.

Dans l'exemple suivant, vous verrez la différence entre les fonctions ALL et ALLSELECTED. Vous pouvez créer trois mesures de montant de vente comme suit:

Ventes de revendeur: = SUM ([SalesAmount])

Revendeur Grand Total: = calculer (somme ([SalesAmount]), ALL ('ResellerSales'))

Revendeur Visual Total: = calculer (somme ([SalesAmount]), ALLSELECTED ())

Reseller Sales conserve tous les contextes de données appliqués à la mesure. Le revendeur Grand Total supprime tout le contexte associé à la table ResellerSales et à toute table liée. Revendeur Visual Total supprime le contexte de colonne et de ligne de la mesure. La figure montre les mesures qui en résultent dans un tableau croisé dynamique.

Voyons maintenant un exemple plus complexe. Dans cet exemple, vous voulez déterminer les meilleurs clients à ordre unique au cours d'une période donnée. Le tableau croisé final est illustré à la figure.

La première étape consiste à trouver les clients qui ont dépensé beaucoup d'argent pendant la période. Pour calculer les ventes client, vous utilisez la mesure suivante:

SumSales: = SUM ([SalesAmount])

Ensuite, vous ne voulez que regarder les gros dépensiers afin que vous puissiez filtrer des valeurs plus petites:

LargeSales: = IF ([SumSales]> = 30000, [SumSales], Blank ())

L'étape suivante consiste à trouver les montants de commande pour le client et à prendre la valeur maximale:

Top Sale: = MAXX (VALUES (Date [DateKey]), [SumSales])

Puisque vous voulez seulement énumérer les ventes supérieures pour des clients supérieurs, vous pouvez ajouter une déclaration de SI pour vous assurer que le client a de grandes ventes:

Top Vente: = IF (ISBLANK ([LargeSales]), Blank (), MAXX (VALUES (Date [DateKey]), [SumSales]))

Comme dernier exemple, disons que vous travaillez avec le département RH et que vous voulez créer un tableau croisé dynamique qui leur permettra de répertorier les effectifs pour les ministères à une date donnée. Il y a un tableau de l'état des employés qui énumère les employés, le ministère, la date de début et la date de fin. Il existe également un tableau Dates qui comporte une ligne pour chaque date couvrant les historiques du service. La figure montre le tableau croisé dynamique contenant le nombre d'employés pour chaque département.

Le Comme Date est utilisé comme un filtre et le Emp Cnt est la mesure. Lorsque la Date est modifiée, la valeur de l'Emp Cnt est recalculée pour indiquer le nombre d'employés à cette date. La figure montre les nouveaux comptages après la modification de la date.

La première étape pour créer le Emp Cnt est d'utiliser la fonction COUNT parce que vous souhaitez compter le EmployeeID dans la table:

Emp Cnt: = COUNT (EmpDepHist [BusinessEntityID]]

Puisque vous devez filtrer la table uniquement aux employés actifs à la date choisie, vous devez modifier cette fonction COUNTX:

Emp Cnt: = COUNTX (EmpDepHist, EmpDepHist [BusinessEntityID])

Pour filtrer la table EmpDepHist, utilisez la fonction FILTER:

FILTER (<table>, <filter>)

La fonction FILTER est une expression booléenne qui est évaluée à TRUE. Dans ce cas, vous devez avoir la date à laquelle l'employé a commencé dans le département :

EmpDepHist [StartDate] <= Dates [A partir de la date]

Maintenant, puisque l'utilisateur table pivot peut sélectionner plus d'une date et vous voulez vous assurer que vous ne le comparer à une seule date, vous pouvez utiliser la fonction MAX:

EmpDepHist [StartDate] <= MAX (Dates [À la date])

Vous voulez également vous assurer que la date à laquelle l'employé a quitté le département est plus grande que la date de la date de recherche :

EmpDepHist [EndDate]> Max (Dates [À partir de la date]

Si l'employé est actuellement dans le département de la EndDate sera vierge:

ISBLANK (EmpDepHist [EndDate])

Lorsque vous combinez ces conditions de filtre, vous obtenez la condition de filtre suivante:

EmpDepHist [StartDate] <= MAX (Dates [À la date])

&& (ISBLANK (EmpDepHist [EndDate]) || EmpDepHist [EndDate]> Max (Dates [A partir de la date]))

La fonction FILTER finale devient alors

FILTER (EmpDepHist,

EmpDepHist [StartDate] <= MAX (Dates [À la date])

&& (ISBLANK (EmpDepHist [EndDate]) || EmpDepHist [EndDate]> Max (Dates [À partir de la date])))

Et la mesure finale du nombre d'employés devient

Emp Cnt: = COUNTX (FILTER (EmpDepHist,

EmpDepHist [StartDate] <= MAX (Dates [À la date])

&& (ISBLANK (EmpDepHist [EndDate]) || EmpDepHist [EndDate]> Max (Dates [à la date]))),

EmpDepHist [BusinessEntityID])

Comme vous pouvez le voir, la création d'une mesure peut être assez complexe, mais si vous la divisez par étapes, elle devient très gérable. Repos assuré; Plus vous travaillez avec DAX et créez des mesures, plus intuitif et plus facile il devient.

Un type de mesure couramment utilisé dans les tableaux de bord de performance est l'indicateur de performance clé (KPI). Un KPI est utilisé pour montrer les performances et les tendances dans un format visuel. Dans la section suivante, vous allez étudier la création de KPI dans le modèle Power Pivot.

Création d'indicateurs de performance clés

Les indicateurs clés de performance (kPIs) sont un élément de base de nombreux tableaux de bord et constituent une excellente façon de voir rapidement les tendances et de localiser les zones qui nécessitent une analyse plus poussée. La valeur de base d'un KPI est basée sur une mesure; Par exemple, vous pouvez créer un KPI pour les ventes en cours. La valeur de base est alors comparée à une valeur cible. La valeur cible peut être une autre mesure; Par exemple, les ventes de l'année dernière ou une valeur numérique absolue, comme un ratio cible ventes-dépenses. Les seuils d'état établissent quelle plage est considérée comme bonne, neutre et mauvaise. Ces seuils sont alors utilisés pour déterminer la composante visuelle du KPI (c'est-à-dire, vert, jaune, rouge). La figure montre les ICP des ventes courantes par rapport aux ventes antérieures.

Pour créer un KPI, créez d'abord la mesure de base sur laquelle elle sera basée. Par exemple, la production conserve la trace des pièces mises au rebut, de sorte que vous pouvez baser un KPI sur la somme du nombre de pièces mises au rebut:

SumScrappedQty: = SUM ([ScrappedQty])

Vous pouvez comparer ceci au nombre de pièces traitées:

SumOrderQuantity: = SUM ([OrderQty])

Après avoir créé les mesures, cliquez avec le bouton droit sur la mesure de base dans le concepteur du modèle Power Pivot. Dans le menu contextuel, sélectionnez Créer KPI, qui lance la fenêtre de conception KPI (voir Figure).

Vous définissez alors la cible sur la mesure SumOrderQuantity et définissez les seuils d'état. Les icônes de style sont utilisées pour afficher l'état des KPI. Lorsque vous avez terminé la création du KPI, vous pouvez l'utiliser dans un tableau croisé dynamique dans Excel (voir Figure).

Maintenant que vous avez vu comment créer des mesures et modifier le contexte des données à l'aide DAX, il est temps de vous salir les mains et de créer des mesures dans le laboratoire suivant.

• Créer des mesures agrégées.

• Modifier le contexte de données dans une mesure.

• Utilisez une relation non active dans une mesure.

• Créer une mesure complexe.

• Créer un KPI.

1. Dans le dossier Lab Starters, ouvrez le fichier LabChapter5.xlsx. Ce fichier contient un modèle de données composé de données de vente, de produit et de stockage.

2. Affichez le modèle dans la fenêtre Power Pivot en utilisant la vue de diagramme (Figure).

3. Passez à l'affichage des données dans la fenêtre Power Pivot et sélectionnez la table Sales.

4. Sélectionnez la colonne SalesAmount. Dans la liste déroulante AutoSum, sélectionnez Somme (voir Figure). Vous devriez voir une mesure ajoutée en dessous de la colonne. Renommez la mesure en Somme Ventes.

5. Utilisez la même technique que dans l'étape 4, créez une quantité de vente maximum, une quantité de vente minimale et une mesure de quantité de ventes d'Ave.

6. Pour tester comment les mesures sont recalculées à mesure que le contexte du filtre change, cliquez sur le tableau croisé dynamique dans l'onglet Accueil et insérez le tableau croisé dynamique dans la cellule Sheet1 B2 (voir la figure).

7. Si vous ne voyez pas la liste des champs, cliquez sur le tableau croisé dynamique pour le montrer.

8. Dans le nœud Ventes, cochez la zone Ventes et Quantité de ventes max. Cela ajoutera les mesures à la zone de dépôt Valeurs. Sous le nœud Product, vérifiez l'attribut ProductCategory. Cela ajoutera l'attribut à la zone de suppression Etiquettes en ligne.

9. Dans l'onglet Insertion, cliquez sur le bouton Segment. Dans la fenêtre Insert segments, sélectionnez l'onglet All et sélectionnez ContinentName sous le nœud Geography (voir Figure).

10. Le tableau croisé devrait ressembler à celui montré sur la figure. Testez les mesures en cliquant sur les différents continents. Cela modifie le contexte de la requête. Notez comment les valeurs de mesure sont recalculées au fur et à mesure que le contexte de la requête change.

11. Supposons que nous voulons un ratio de ventes comparant les ventes aux ventes totales de tous les produits. Ouvrez la fenêtre Power Pivot en mode de visualisation des données. Sélectionnez la table Ventes. Ajoutez la mesure suivante à la table Ventes:

Toutes les ventes de produits:=CALCULATE(SUM([SalesAmount]);ALL('Product'))

12. La mesure Toutes les ventes de produit utilise la fonction CALCULER pour remplacer tout filtre de produit appliqué au contexte de la requête. Formatez la mesure comme devise.

13. Passer au tableau croisé. Remplacer la mesure Max Sales Quantity par les ventes Somme et les ventes Toutes les ventes de produits.

14. Testez le tableau croisé en cliquant sur les différents continents et notez que la mesure Toutes les ventes de produits est égale à la vente totale de produits pour chaque continent (voir la figure).

15. Retournez à la fenêtre Power Pivot et ajoutez la mesure suivante à la table Sales. Formatez la mesure en pourcentage.

Ratio de vente des produits: = [Somme des ventes] / [Toutes les ventes de produits]

16. Ouvrez la fenêtre Power Pivot en mode diagramme. Pour créer une relation entre la table Date et la table Store, faites glisser le champ OpenDate dans la table Store et déposez-le au-dessus de la DateKey dans la table Date (voir Figure). Notez que ce n'est pas la relation active entre les tables Store et Date comme indiqué par la ligne en pointillés. Cela est dû au fait que la relation active passe de la table Stock, à la table Ventes, puis à la table Date.

17. Passez au mode d'affichage des données et ajoutez la mesure suivante à la table Stocker. Puisque vous utilisez une relation non active, vous devez utiliser la fonction USERELATIONSHIP.

Nombre de magasins: = CALCULATE (DISTINCTCOUNT ([StoreKey]),USERELATIONSHIP (Store [OpenDate], 'Date' [Datekey]))

18. Pour tester la mesure du comptage de magasin, créez un tableau croisé dynamique sur Sheet2. Utilisez le ContinentName comme les étiquettes de colonne et le CalendarMonth comme les étiquettes de ligne. Insérez une trancheuse en utilisant CalendarYear. Votre tableau croisé dynamique doit ressembler à la figure.

19. Le tableau croisé dynamique représente le nombre de magasins ouverts pendant un mois. Cliquez sur les différentes années et observez les changements dans les données.

20. Pour connaître la meilleure journée de vente pour une catégorie de produits, créez une mesure de quantité de vente.

Quantité de vente: = SUM ([SalesQuantity])

21. Utilisez la mesure de quantité de vente pour créer une mesure de quantité de jour de vente supérieure. La fonction MAXX est utilisée pour briser toutes les attaches et retourne la DateKey la plus récente.

Date de la plus haute vente Quantité: = MAXX (valeurs ('Date' [Datekey]), [Quantité de vente])

22. Pour déterminer la date du jour de vente le plus élevé, vous devez d'abord créer une fonction de filtre qui renvoie les dates où la quantité de vente est égale à la quantité de la journée de vente supérieure pour la période.

Filtrer (VALUES ('Date' [Datekey]),[Quantité de vente] = CALCULE ([Quantité de la plus grande vente],VALUES ('Date' [Datekey])))

23. Ce filtre est ensuite inséré dans une fonction CALCULATE qui renvoie la date la plus récente.

Jour de la meilleure vente: = CALCULATE (MAX ('Date' [Datekey]),Filtre (VALUES ('Date' [Datekey]), [Quantité de vente] =CALCULEZ ([Quantité de la plus grande journée de vente], VALUES ('Date' [Datekey]))))

24. Créez un tableau pivot pour tester vos mesures.

25. Créez les mesures suivantes dans la table Ventes et formatez-les comme devise:

Ave Sales: = MOYENNE ([SalesAmount])

Total Ave Sales: = Si (ISBLANK ([Ave Sales]), BLANK (),CALCULEZ ([Ventes Ave, ALLSELECTED ()))

26. Choisir la mesure Ave Sales dans la grille de mesure. Faites un clic droit dessus et sélectionnez Créer KPI dans le menu contextuel. Dans la boîte de dialogue KPI, notez que la mesure de base est Ave Sales. Définissez la valeur cible sur Total Ave Sales. Réglez le seuil bas à 90% et le seuil haut à 110% (voir Figure).

27. Fermez la boîte de dialogue et notez que la mesure Ave Sales affiche une icône indiquant qu'elle est associée à un KPI.

28. Créez un tableau croisé dynamique pour tester le KPI. Vous pouvez comparer les ventes moyennes d'un magasin avec le total des ventes moyennes de tous les magasins affichés.

Téléchargez le carnet de Travaux dirigés et le classeur Excel.

Merci

Résumé

C'était un chapitre long et charnu. Vous avez maintenant une bonne compréhension de la façon de créer des mesures dans votre Power Pivot modèle.

Vous devez également comprendre le contexte des données et la façon dont elles affectent les mesures. Cela peut être un concept très déroutant lorsque vous commencez à développer des mesures plus complexes. Ne vous inquiétez pas; Plus vous travaillez avec elle plus il devient clair.

Le chapitre suivant étend les concepts de ce chapitre. L'un des types d'analyse de données les plus courants est la comparaison des valeurs au fil du temps. Le chapitre suivant vous montre comment implémenter correctement l'analyse temporelle dans Power Pivot. Il comprend la mise en place d'un tableau de dates et l'utilisation des différentes fonctions intégrées pour l'analyse des valeurs à ce jour, la comparaison des valeurs de différentes périodes, et l'exécution semi-additif d'agrégations.

Incorporer l'intelligence temporelle

L'un des types d'analyse de données les plus courants est la comparaison des valeurs au fil du temps. Ce chapitre montre au lecteur comment implémenter correctement l'analyse temporelle dans Power Pivot. Il comprend la mise en place d'un tableau de dates et l'utilisation des différentes fonctions intégrées pour l'analyse des valeurs à ce jour, la comparaison des valeurs de différentes périodes, et l'exécution semi-additive d’agrégations.

Après avoir terminé ce chapitre, vous pourrez

• Créer une table de dates.
• Utilisez DAX pour les évaluations basées sur la période.
• Modifiez le contexte de la date en utilisant les fonctions de filtrage.
• Créer des mesures semi-additives.

Analyse fondée sur la date

Une grande partie de l'analyse des données implique une sorte d'agrégation et de comparaison basée sur la date et l'heure. Par exemple, vous pourriez avoir besoin d'examiner l'utilisation ou les ventes pour le mois à ce jour (MTD) ou de l'année à ce jour (YTD) comme le montre la figure.

Un autre exemple courant consiste à examiner la performance d'une période à l'autre. Par exemple, vous pouvez comparer les ventes du mois précédent avec les ventes courantes (voir la figure) ou les ventes du mois courant au même mois l'année précédente.

En plus de ces analyses de données communes, il y a aussi des moments où vous devez baser vos agrégations sur des mesures qui sont non additifs, tels que les soldes de compte ou d'inventaire. Dans ces cas, vous devez déterminer la dernière valeur saisie et utiliser cette valeur pour agréger les différentes périodes (voir Figure).

DAX contient de nombreuses fonctions qui vous aident à créer les diverses analyses basées sur la date et l'heure dont vous aurez besoin. Dans la section suivante, vous verrez comment créer une table de dates qui est nécessaire pour utiliser la plupart des fonctions basées sur la date et l'heure.

Création d'une table de dates

Afin d'utiliser les fonctions de renseignement intégrées dans DAX, vous devez disposer d'une table de dates dans votre modèle pour les fonctions à référencer. La seule condition pour le tableau est qu'il a besoin d'une ligne distincte pour chaque jour dans la période à laquelle vous êtes intéressé à analyser. Chacune de ces lignes doit contenir la date complète de la journée. La table de dates peut, et a souvent, plus de colonnes.

Il existe plusieurs façons de créer la table de dates. Si vous récupérez des données d'un entrepôt de données, vous pouvez importer une dimension de date dans le modèle et l'utiliser. Si vous n'avez pas accès à un tableau de date, vous pouvez le créer dans une feuille Excel et l'importer dans le modèle. Si vous avez accès à une base de données, vous pouvez également écrire une requête SQL pour créer une table temporaire et charger les dates dans votre modèle à l'aide de la table temporaire. Le code suivant montre la requête SQL que vous pouvez utiliser sur une base de données Microsoft SQL Server. Il utilise une expression de table commune pour remplir une table de dates.

DECLARE

@BeginDate DATE,

@EndDate DATE

SET @BeginDate = '01/01/2010'

SET @EndDate = '12/31/2014'

;

WITH cteDate AS (

SELECT @BeginDate AS CalendarDate

UNION ALL

SELECT DATEADD(DAY,1,CalendarDate)

FROM cteDate

WHERE CalendarDate < @EndDate)

SELECT

(DATEPART(YEAR,CalendarDate) * 10000) + (DATEPART(MONTH,CalendarDate) * 100) +

DATEPART(DAY,CalendarDate) AS DateKey,

CalendarDate,

DATEPART(YEAR,CalendarDate) AS Year,

DATEPART(MONTH,CalendarDate) AS MonthNumber,

DATENAME(MONTH,CalendarDate) AS Month

FROM cteDate

OPTION (MAXRECURSION 0)

Une fois que vous avez le tableau dans le modèle, vous devez le marquer comme la table de date officielle (voir Figure) et indiquer quelle colonne est la clé unique (voir Figure). Cela indique au moteur de requête DAX d'utiliser cette table comme référence pour construire l'ensemble des dates nécessaires pour un calcul. Par exemple, si vous souhaitez consulter les ventes annuelles, le moteur de requêtes utilise cette table pour obtenir l'ensemble de dates dont il a besoin.

Actuellement, il y a 35 fonctions intelligentes intégrées dans DAX. Certaines de ces fonctions comme FIRSTNONBLANK renvoient une seule date. Certains renvoient un ensemble de dates telles que PREVIOUSMONTH. Et d'autres encore comme TOTALQTD évaluent les expressions au fil du temps. À ce stade, les fonctions de synchronisation de DAX intégrées prennent en charge le calendrier traditionnel se terminant le 31 décembre. Ils soutiennent également un calendrier fiscal qui a une date de fin d'année différente et contient quatre trimestres contenant trois mois chacun. Si vous devez utiliser un calendrier financier personnalisé, vous devez créer vos propres calculs personnalisés. Maintenant que vous comprenez comment créer et désigner la table de date dans votre modèle, il est temps de regarder la mise en œuvre de certaines des fonctions intelligente communes de temps pour analyser vos données.

Évaluations fondées sur des périodes de temps

Une analyse commune souvent employée dans l'analyse de données regarde des valeurs de la période à la date. Par exemple, vous voudrez peut-être examiner les ventes cumulées depuis le début de l'année ou la consommation d'énergie depuis le début du mois. DAX fournit les fonctions TOTALMTD, TOTALQTD et TOTALYTD qui rendent cette opération très simple. Par exemple, le total de l'année à ce jour est défini comme suit:

TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])

Où l'expression est une expression qui renvoie une valeur scalaire. Les dates correspondent à la colonne clé de la table de dates. Le filtre est une expression de filtre facultative et la date de fin d'année est également facultative; vous pouvez l'utiliser pour indiquer la fin d'année d'un calendrier fiscal. Les expressions suivantes servent à calculer la somme des ventes et des valeurs de l'exercice à ce jour.

Sum of Sales:=SUM(Sales[SalesAmount])YTD Sales:=TOTALYTD([Sum of Sales],'Date'[Datekey])

 Si vous souhaitez calculer les ventes de l'année pour tous les produits, utilisez l'expression suivante:

 YTD Sales ALL Products:=TOTALYTD([Sum of Sales],'Date'[Datekey],ALL('Product'))

 La figure montre les mesures dans un tableau croisé dynamique. Vous pouvez utiliser ces mesures de base pour calculer d'autres mesures, telles que le pourcentage de ventes cumulatives depuis le début de l'année et le pourcentage de toutes les ventes de produits.

Vous pouvez également utiliser un autre ensemble de fonctions - DATESMTD, DATESQTD et DATESYTD - pour créer les mêmes mesures. Tout comme avec les mesures antérieures à date, vous devez passer la clé de date de la table de date aux fonctions. L'expression suivante utilise la fonction CALCULATE avec le filtre DATESYTD pour obtenir la mesure de l'année de vente.

 YTD Sales 2:=CALCULATE([Sum of Sales],DATESYTD('Date'[Datekey]))

 L'utilisation de la fonction CALCULATE et des fonctions DATES est plus polyvalente que les fonctions totalement de jour car vous pouvez les utiliser pour n'importe quel type d'agrégation, pas seulement la somme. Les expressions suivantes servent à calculer la moyenne des ventes depuis le début de l'exercice. Les résultats sont présentés à la figure.

 Ave Sales:=AVERAGE([SalesAmount])

YTD Ave Sales:=CALCULATE([Ave Sales],DATESYTD('Date'[Datekey]))

Maintenant que vous savez comment créer des calculs basés sur la période, vous pouvez utiliser cette fonction pour comparer les performances passées aux performances actuelles. Mais d'abord, vous devez savoir comment modifier le contexte de la date pour calculer les performances passées.

Modifier le contexte de la date

Si vous voulez comparer la performance d'une période à la même période dans le passé, dire ventes pour le mois en cours aux ventes pour le même mois il y a un an, vous devez déplacer le contexte de la date. DAX contient plusieurs fonctions qui le font. L'une des fonctions les plus polyvalentes pour décaler le contexte de la date est la fonction PARALLELPERIOD. Comme pour les autres fonctions de renseignement temporel, vous devez passer la colonne de la table de dates à la fonction. Vous devez également indiquer le nombre d'intervalles et le type d'intervalle d'année, de trimestre ou de mois.

PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>)

 Une chose à retenir est que la fonction PARALLELPERIOD renvoie un ensemble de dates qui correspond au type d'intervalle. Si vous utilisez l'année, elle renvoie une année de dates; L'intervalle de mois renvoie la valeur d'un mois de dates. L'expression suivante calcule les totaux des ventes pour le mois de l'année précédente. La figure montre les résultats du calcul.

 Month Sales Last Year:=Calculate([Sum of Sales],

PARALLELPERIOD('Date'[Datekey],-12,Month))

Notez que si vous effectuez une exploration au niveau de la date (voir Figure), vous voyez toujours les totaux de mois pour le mois de la date d'il y a un an. Comme mentionné précédemment, c'est parce que le PARALLELPERIOD dans ce cas renvoie toujours l'ensemble des dates pour le même mois que la date de ligne pour l'année précédente.

Maintenant que vous pouvez calculer les ventes du mois de l'année précédente, vous pouvez le combiner avec les ventes en cours pour calculer la croissance mensuelle des ventes d'une année à l'autre. La figure montre les résultats.

YOY Monthly Growth:=([Sum of Sales]-[Month Sales Last Year])/[Month Sales Last Year]

Notez s'il n'y a pas de ventes de l'année précédente, vous obtenez une erreur dans le tableau croisé dynamique. Vous pouvez contrôler cela en vérifiant si les ventes mensuelles de l'année dernière sont vierges et si vous insérez une valeur vierge à la place.

YOY Monthly Growth:=IF(

ISBLANK([Month Sales Last Year]),

BLANK(),

([Sum of Sales]-[Month Sales Last Year])/[Month Sales Last Year])

Une autre fonction couramment utilisée pour modifier le contexte de la date est la fonction DATEADD. La fonction DATEADD permet d'ajouter un intervalle de date au contexte de la date courante. Vous pouvez ajouter des intervalles d'une année, d'un trimestre, d'un mois ou d'un jour.

DATEADD(<dates>,<number_of_intervals>,<interval>)

Le calcul suivant permet de calculer la somme des ventes du jour précédent en utilisant la fonction DATEADD comme filtre.

Prev Day Sales:=Calculate([Sum of Sales],DATEADD('Date'[Datekey],-1,day))

Maintenant que vous savez comment modifier le contexte de la date, il est temps de regarder les fonctions que vous pouvez utiliser dans vos filtres qui renvoient une seule date.

Utilisation des fonctions de date unique

DAX contient un ensemble de fonctions qui renvoient une seule date. Elles sont généralement utilisées pour filtrer le contexte de la date. Par exemple la fonction FIRSTDATE renvoie la première date dans la colonne des dates passées à elle. Par exemple, vous pouvez utiliser cette fonction en combinaison avec la fonction DATESBETWEEN pour obtenir la plage de dates du premier jour au 15ème jour de l'ensemble de dates de contexte actuel des dates.

DATESBETWEEN('Date'[FullDateAlternateKey]

,FIRSTDATE('Date'[FullDateAlternateKey])

,DATEADD(FIRSTDATE('Date'[FullDateAlternateKey]), 14, DAY))

Cela peut ensuite être utilisé comme un filtre dans la fonction CALCULATE pour obtenir les ventes pendant les 15 premiers jours de la période. La table croisé résultante est représentée sur la figure.

Si vous regardez simplement les périodes mensuelles, vous pouvez utiliser les fonctions STARTOFMONTH et ENDOFMONTH (il y en a aussi pour les années et les trimestres). L'expression suivante est utilisée pour calculer la somme des ventes pour les 15 derniers jours du mois.

Last 15 Day Sales:=CALCULATE(SUM(InternetSales[SalesAmount]),

DATESBETWEEN('Date'[FullDateAlternateKey]

, DATEADD(ENDOFMONTH('Date'[FullDateAlternateKey]), -14, DAY)

,ENDOFMONTH('Date'[FullDateAlternateKey])))

Bien que la majorité des mesures que vous devez agréger d'un niveau inférieur à un niveau supérieur (par exemple, de jours en mois) sont des extensions simples de l'agrégat de base, vous devez parfois utiliser des agrégations spéciales pour enrouler la mesure. Ce type de mesure est considéré comme semi-additif et est traité dans la section suivante.

Création de mesures semi-additifs

Vous rencontrez souvent des mesures semi-additif lors de l'analyse des données. Quelques exemples courants sont l'inventaire et les soldes des comptes. Par exemple, pour déterminer le montant total des stocks à un moment donné, vous ajoutez l'inventaire de tous les magasins. Cependant, pour trouver l'inventaire total d'un magasin à la fin du mois, vous ne rajoutez pas l'inventaire pour chaque jour.

Pour faire face à ces situations DAX contient les fonctions FIRSTNONBLANK et LASTNONBLANK. Ces fonctions renvoient la première ou la dernière date pour une condition non vierge. Par exemple, l'expression suivante détermine la dernière date non vierge pour les entrées d'inventaire produit.

LASTNONBLANK ('Date'[DateKey],CALCULATE (SUM(Inventory[UnitsInStock]))

Ceci est ensuite combiné avec la fonction CALCULATE pour déterminer le nombre total d'unités en stock.

Product Units In Stock:=CALCULATE(SUM(Inventory[UnitsInStock]),

LASTNONBLANK('Date'[DateKey],

CALCULATE(SUM(Inventory[UnitsInStock]))))

Maintenant, si vous souhaitez ajouter les unités en stock dans les produits, vous pouvez utiliser l'expression suivante.

Total Units In Stock:=SUMX(VALUES('Inventory'[ProductKey]),[Product Units In Stock])

La figure montre la table pivotante résultante. Remarquez que la mesure des unités en stock est additive dans tous les produits, mais non additive à travers les dates.

Un des avantages de DAX est que, une fois que vous apprenez un modèle, vous pouvez étendre cela à d'autres scénarios. Par exemple, vous pouvez utiliser les mêmes techniques utilisées dans ce calcul d'inventaire lors du calcul des mesures dans une analyse de flux de trésorerie. Le calcul suivant est utilisé pour calculer le solde final.

Balance:=CALCULATE ( SUM ( Finance[Amount]),

LASTNONBLANK ('Date'[DateKey],

CALCULATE(SUM ( Finance[Amount]))))

Dans les exemples précédents, l'inventaire et le solde n'ont été saisis que comme ligne dans le tableau lorsqu'une variation de l'inventaire ou du solde s'est produite. Souvent, le solde ou l'inventaire est entré chaque jour et la même valeur est répétée jusqu'à ce qu'il y ait un changement. Dans ces cas, vous pouvez utiliser les fonctions DAX CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER et CLOSINGBALANCYEAR. Ces fonctions examinent la dernière date de la période et l'utilisent comme valeur pour la période. En d'autres termes, quelle que soit la valeur est le dernier jour du mois est retourné par La fonction CLOSINGBALANCEMONTH.

À ce stade, vous devriez avoir une bonne compréhension de la façon dont les différentes fonctions de temps de travail. Dans le laboratoire suivant, vous aurez de l'expérience dans l'implémentation de certaines de ces fonctions.

Téléchargez le carnet de Travaux dirigés et le classeur Excel.

Merci

Résumé

Ce chapitre vous fournit les bases dont vous avez besoin pour intégrer avec succès l'analyse basée sur la date et l'heure à l'aide de Power Pivot et DAX. Vous devriez maintenant comprendre comment changer le contexte de la date pour comparer les mesures basées sur des périodes parallèles. Vous devriez également vous sentir à l'aise en agrégation des mesures à l'aide de la période à date des formules DAX.

Combinez les concepts que vous avez appris au chapitre 5 et au présent chapitre; Lorsque vous le faites, vous devriez commencer à reconnaître les modèles dans votre analyse. Sur la base de ces modèles, vous pouvez identifier le modèle DAX dont vous avez besoin pour résoudre le problème. Étant donné que vous n'êtes pas tous familiarisés avec la création de tableaux croisés dynamiques et de diagrammes croisés dans Excel, le chapitre suivant vous montre les bases de la création de tableaux de bord dans Excel en fonction de votre modèle. Si vous connaissez ces concepts, vous pouvez sauter le chapitre.

Analyse de données avec les tableaux et les graphiques croisés dynamiques

 Une fois le modèle de données créé dans Power Pivot, vous devez créer une interface permettant aux utilisateurs d'interagir avec le modèle de données et d'effectuer l'analyse des données à l'aide du modèle. Vous pouvez utiliser plusieurs programmes pour interfacer avec le modèle en fonction du type d'analyse en cours; Power View, Power Map, Performance Point et Reporting Services. Bien que tous ces outils soient des clients viables à utiliser, l'un des meilleurs outils client est Excel lui-même.

Excel est un environnement riche en fonctionnalités pour créer des tableaux de bord à l'aide de tableaux croisés dynamiques et de graphiques croisés dynamiques. De plus, il est très facile de partager des fichiers Excel avec des collègues ou d'héberger le classeur Excel sur SharePoint pour améliorer les performances et la sécurité. Ce chapitre traite des bases de la construction d'une interface d'analyse des données contenues dans un modèle Power Pivot à l'aide de tableaux croisés dynamiques et de graphiques croisés dynamiques dans Excel.

Après avoir terminé ce chapitre, vous pourrez

• Utilisez des tableaux croisés dynamiques pour explorer les données.
• Filtrer les données à l'aide de segments.
• Ajouter des visualisations à un tableau croisé dynamique.
• Utilisez des diagrammes dynamiques pour explorer les tendances.
• Utilisez plusieurs tableaux et tableaux reliés entre eux.
• Utiliser les fonctions du cube pour interroger le modèle de données

Principes fondamentaux du tableau croisé dynamique

L'un des outils les plus utilisés pour analyser les données est le tableau croisé dynamique. Le tableau croisé dynamique vous permet de détecter facilement les modèles et les relations à partir des données. Par exemple, vous pouvez déterminer quels produits se vendent mieux pendant certaines périodes de l'année. Ou vous pouvez voir comment les campagnes de marketing affectent les ventes de divers produits. La figure montre les différentes zones d'un tableau pivot.

Les champs de ligne et de colonne contiennent les attributs que vous souhaitez utiliser pour résumer les données. Par exemple, le tableau croisé dynamique de la figure regroupe les valeurs par catégories de produits et années. Le filtre est utilisé pour filtrer les valeurs dans le tableau croisé dynamique par un attribut. Le filtre de la figure limite les résultats au premier trimestre de chaque année. Le segment fonctionne de la même manière que le filtre dans ce cas, en limitant les valeurs aux grands revendeurs. Les segments ont l'avantage supplémentaire de filtrer des tableaux croisés dynamiques multiples et des diagrammes. Figure

Pour construire le tableau croisé dynamique, faites glisser et déposez les champs de la liste des champs dans les zones de dépôt situées sous la liste des champs, comme le montre la figure. Si vous cliquez simplement sur la case à cocher pour sélectionner les champs, il place les champs de texte dans les lignes et les valeurs numériques dans la zone Valeurs. Cela peut devenir très gênant car il va placer un champ comme des années dans la zone Valeurs et le traiter comme un ensemble de valeurs à additionner. Dans la suite, je vais vous montrer comment vous pouvez éviter cela.

Pour ajouter des segments pour filtrer le tableau croisé dynamique, vous devez aller dans l'onglet Insertion dans Excel et sélectionner le bouton Segment. Dans la section suivante, vous examinerez l'ajout de segments et le contrôle de plusieurs tableaux croisés dynamiques avec le même segment.

Découpage des données

Pour ajouter un segment pour filtrer un tableau croisé dynamique, cliquez sur le tableau croisé dynamique et, dans l'onglet Insertion, sélectionnez le bouton Segment. Vous devriez Voir une liste de sélection contenant les champs dans le modèle de données Power Pivot (voir Figure).

Puisque vous avez sélectionné le tableau croisé dynamique lorsque vous avez inséré le segment, celui-ci est automatiquement câblé vers le tableau croisé dynamique. Vous pouvez vérifier cela en cliquant sur le segment et en sélectionnant l'onglet Options des outils Segments (voir Figure). Vous pouvez utiliser cet onglet pour formater le segment et choisir les connexions de rapport pour le segment.

La figure montre la liste de sélection Connexions de rapport pour le segment. Notez que le tableau croisé ne doit pas nécessairement être sur la même feuille que le segment. Les noms des tableaux croisés dynamiques sont les noms génériques qui leur ont été attribués par Excel lors de leur création. À mesure que vous ajoutez des tableaux croisés dynamiques, il est judicieux de leur donner des noms plus significatifs. Si vous cliquez sur le tableau croisé dynamique et accédez à l'onglet Analyse des outils de tableau croisé dynamique, une zone de texte vous permet de modifier le nom du tableau croisé dynamique.

Si vous sélectionnez le bouton Paramètre du segment sous l'onglet Options outils segment, vous lancez la fenêtre Paramètre du segment (voir Figure) où vous pouvez changer le nom, la légende et l'ordre de tri du segment. Vous pouvez également choisir de montrer les éléments sans données.

Il y a des moments où vous avez besoin de filtres en cascade où un filtre limite ce qui peut être choisi dans le filtre suivant. C'est facile avec les segments qui sont basées sur des champs qui sont liés dans le modèle. Lorsque vous sélectionnez les champs associés, les segments sont liés automatiquement pour vous. Par exemple, la Figure montre une catégorie de produit et un filtre de sous-catégorie de produit. Si vous sélectionnez une catégorie de produit, les sous-catégories de produit correspondantes sont mises en surbrillance.

Ajout de visualisations à un tableau croisé dynamique

Afin d'aider à identifier les tendances, les valeurs aberrantes et fournir un aperçu des données, vous pouvez ajouter de nombreux types de visualisations à un tableau croisé dynamique. Cela inclut la mise en forme conditionnelle, les barres de données et les lignes de tendance. La figure 7-8 montre un tableau croisé dynamique avec barres de données et mise en forme conditionnelle.

Pour créer la mise en forme visuelle, sélectionnez les données que vous souhaitez formater dans le tableau croisé dynamique et, dans l'onglet Accueil, cliquez sur la liste déroulante Formatage conditionnel (voir Figure). Comme vous pouvez le voir, vous avez beaucoup d'options pour créer le formatage conditionnel. Dans la Figure, il y a une Règle de cellule de surbrillance qui affiche des nombres négatifs en rouge.

Une fois que vous avez établi une règle, vous pouvez la modifier en sélectionnant l'option Gérer les règles et en sélectionnant la règle à modifier. La Figure montre les différentes options que vous pouvez définir pour une règle de formatage de la Barre de données.

Vous pouvez produire des effets intéressants avec les règles de formatage. Par exemple, le tableau croisé de la figure montre une carte de chaleur utilisée pour déterminer rapidement les mois qui ont de bonnes ventes et qui sont mauvais.

Une autre caractéristique populaire associée à des tableaux croisés sont des lignes Sparkline, qui sont des mini graphiques qui montrent la tendance d'une série de données. La figure montre les lignes d'étincelles qui affichent la tendance des ventes sur les quatre trimestres de l'année.

Pour créer une ligne Sparkline, mettez en surbrillance les valeurs dans le tableau croisé dynamique qui contiennent les données et sélectionnez le bouton Sparklines dans l'onglet Insertion. Vous sélectionnez ensuite l'emplacement de la ligne Sparkline (voir Figure). Vous devez choisir parmi une ligne, une colonne ou une ligne Sparkline gagnant / perdant.

Bien que l'ajout de visualisations aux tableaux croisés dynamiques peut améliorer votre capacité à analyser les données, de nombreuses fois la meilleure façon de repérer les tendances et de comparer et de contraster les données est à travers l'utilisation de graphiques et de diagrammes. Dans la section suivante, vous verrez comment les graphiques et les diagrammes sont des outils d'analyse de données utiles et comment les ajouter à vos tableaux de bord d'analyse.

Travailler avec les tableaux croisés dynamiques

Avec les tableaux croisés dynamiques, Excel dispose d'un ensemble robuste de graphiques et de diagrammes disponibles pour vous permettre d'analyser vos données. L'ajout d'un graphique croisés est très similaire à l'ajout d'un tableau croisé dynamique. Sous l'onglet Insertion d'Excel, sélectionnez la liste déroulante Graphique croisé dynamique. Vous avez la possibilité de sélectionner un seul graphique croisé dynamique (PivotChart) ou un tableau croisé dynamique (PivotChart) et un tableau croisé dynamique (voir figure).

Après avoir sélectionné le graphique croisé, une fenêtre vous permet de sélectionner une source de données et de créer le graphique croisé (voir Figure).

Pour sélectionner le modèle Power Pivot comme source de données, choisissez d'utiliser une connexion externe et cliquez sur le bouton Choisir la connexion. Dans la fenêtre Connexions existantes, sélectionnez l'onglet Tables et sélectionnez le modèle de données Tables In Workbook (voir Figure).

Une fois que vous avez sélectionné la source de données, vous obtenez un tableau vide et la zone de sélection des champs dans laquelle vous pouvez glisser et déposer des champs pour les valeurs et l'axe du graphique (voir Figure).

Par défaut, le graphique est un graphique à colonnes, mais vous avez plusieurs types de graphiques à choisir. Si vous cliquez sur le graphique et sélectionnez l'onglet Création des outils de graphique croisé dynamique, vous pouvez sélectionner le bouton Modifier le type de graphique et choisir parmi plusieurs types (voir Figure)....

En plus de modifier le type de graphique, les onglets Outils de graphiques croisés vous permettent de disposer d'un vaste assortiment d'options de conception et de mise en forme que vous pouvez utiliser pour personnaliser l'apparence de vos graphiques et diagrammes. Il est certainement utile de votre temps à jouer avec les outils et de créer des graphiques différents pour avoir une meilleure idée des différentes options disponibles.

Utilisation de graphiques et de tableaux multiples

Lorsque vous créez des tableaux croisés et des graphiques pour afficher et comprendre les données, vous souhaitez souvent créer un tableau de bord qui vous permet de déterminer facilement les performances. Vous serez peut-être intéressé par les performances commerciales, les performances du réseau ou les performances de la chaîne d'assemblage. Les tableaux de bord combinent des représentations visuelles, telles que des indicateurs clés de performance (KPI), des graphiques et des graphiques, en une seule vue holistique du processus. Bien qu'ils ne soient pas techniquement considérés comme des outils de tableau de bord, vous pouvez créer des affichages de données très convaincants à l'aide d'Excel avec des tables Power Pivot et des graphiques qui peuvent ensuite être affichés et partagés dans SharePoint. Lorsque vous ajoutez plusieurs tableaux et graphiques à un tableau de bord, vous pouvez les associer afin qu'ils représentent les mêmes données de différentes façons. Vous voudrez probablement aussi les contrôler avec les mêmes segments afin qu'elles restent synchronisées. La manière la plus simple consiste à les ajouter à l'aide de l'onglet Insertion et en sélectionnant Tableau croisé dynamique et Graphiques croisé dynamique (voir Figure).

L'ajout du tableau croisé dynamique et du graphique croisé dynamique de cette façon crée un lien entre eux de sorte que lorsque vous ajoutez un champ à un, il ajoute le même champ à l'autre. En outre, lorsque vous ajoutez un saut de page, il raccorde automatiquement le segment au tableau croisé dynamique et au graphique croisé. La figure montre un tableau de bord simple composé d'un tableau croisé, d'un diagramme croisé et d'un segment.

Si vous devez connecter plusieurs diagrammes ensemble, ouvrez le Concepteur de modèles et sélectionnez la liste déroulante Tableau croisé dynamique dans l'onglet Accueil (voir Figure 7-21). Cela vous permet également d'insérer une table pivotante aplatie, ce qui est utile pour l'impression.

Utilisation des fonctions Cube

L'utilisation des modèles PivotChart et PivotTable intégrés dans Excel vous permet de créer des tableaux de bord convaincants et de fournir d'excellentes interfaces pour la navigation des données. Il y a des fois, cependant, quand vous pouvez vous retrouver frustré par certaines des limitations inhérentes à ces structures. Par exemple, vous ne pouvez pas insérer vos propres colonnes à l'intérieur du tableau croisé dynamique pour créer un calcul personnalisé. Vous pouvez également afficher les données dans un format non tabulaire pour un rapport personnalisé. C'est là que les fonctions du cube Excel sont vraiment utiles. Les fonctions de cube Excel vous permettent de vous connecter directement au modèle de données Power Pivot sans avoir besoin d'utiliser un tableau croisé. Les fonctions du cube sont des fonctions Excel (par opposition aux fonctions DAX) et peuvent être trouvées sous l'onglet Formules Excel dans la liste déroulante Autres fonctions (voir Figure).

La façon la plus simple de voir comment ces fonctions sont utilisées est de créer un tableau croisé dynamique et, dans l'onglet Analyse des outils de tableau croisé dynamique, sélectionnez la liste déroulante Outils OLAP, puis sélectionnez l'option Convertir en formules (voir Figure).

La figure montre la table après la conversion en fonctions de cube. Il utilise la fonction CUBEMEMBER pour renvoyer un membre ou un tuple (liste ordonnée) à partir du cube et la fonction CUBEVALUE pour renvoyer une valeur agrégée à partir du cube.

La formule suivante est utilisée pour renvoyer l'étiquette de colonne pour la cellule C3:

= CUBEMEMBER ("ThisWorkbookDataModel", "[Géographie]. [CountryRegionCode]. & [AU]")

Le premier paramètre est le nom de la connexion au modèle de données et le second paramètre est l'expression membre. La valeur dans la cellule C4 est renvoyée en utilisant la formule suivante:

= CUBEVALUE ("ThisWorkbookDataModel", $ B $ 2, $ B4, C $ 3)

Cette formule utilise des références de cellules, mais vous pouvez les remplacer par les fonctions de cube contenues dans ces cellules:

=CUBEVALUE

(

"ThisWorkbookDataModel",

CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Sum of SalesAmount]"),

CUBEMEMBER("ThisWorkbookDataModel",

"[Product].[ProductCategory].[Category].&[Accessories]"),

CUBEMEMBER("ThisWorkbookDataModel",

"[Geography].[CountryRegionCode].&[AU]")

)

Remarquez que l'un des paramètres est la fonction CUBEMEMBER renvoyant l'agrégation dont vous voulez la valeur; Les autres paramètres utilisent le CUBEMEMBER pour définir la portion du cube utilisée dans l'agrégation.

Vous pouvez maintenant réorganiser les valeurs et les étiquettes pour obtenir la mise en page et le formatage dont vous avez besoin (voir la figure).

Téléchargez le carnet de Travaux dirigés et le classeur Excel.

Merci

Résumé

Comme vous l'avez vu dans ce chapitre, Excel est un environnement riche en fonctionnalités pour créer des tableaux de bord à l'aide de tableaux croisés dynamiques et de diagrammes croisés dynamiques. À ce stade, vous devez vous sentir à l'aise en créant des tableaux croisés dynamiques et de diagrammes croisés dynamiques en utilisant votre modèle de classeur comme source de données.

Vous avez également utilisé les fonctions cube d’Excel pour interroger le modèle directement sans avoir besoin d'utiliser un tableau croisé dynamique. Bien que ce chapitre couvre les bases pour vous aider à démarrer, vous avez beaucoup plus à apprendre sur Excel et comment il peut vous aider à analyser vos données. Je vous encourage à approfondir ces caractéristiques. Le chapitre suivant est le premier de trois dans lequel vous allez regarder une autre interface utile pour votre Power Pivot modèle.

Power View fournit une interface interactive riche pour les utilisateurs d'explorer les données. Vous verrez d'abord l'optimisation des modèles d'analyse dans Power View, ensuite vous créerez des rapports standards à l'aide de Power View, puis vous verrez créer des rapports interactifs convaincants dans Power View.

Visualisation de données avec Power View

Comme nous l'avons vu au chapitre précédent, vous pouvez créer de puissantes visualisations de données et effectuer une analyse approfondie des données à l'aide des tableaux dynamiques et des graphiques Excel. Les chances sont que si vous êtes un utilisateur puissant d'Excel et effectuer une analyse détaillée des données dans le cadre de votre fonction de travail, ce seront vos outils de choix. D'autre part, si vous avez besoin d'un outil qui permet aux utilisateurs de créer des visualisations rapides et faciles à partir de vos modèles Power Pivot, Power View est une excellente option. Vous utilisez Power View pour créer des tableaux de bord convaincants qui contiennent des visualisations liées contextuellement qui peuvent être facilement filtrées, triées et imprimées. Les rapports Power View peuvent être hébergés sous forme de feuille de calcul dans Excel ou dans une bibliothèque SharePoint. Un avantage supplémentaire est qu'ils peuvent être exportés vers PowerPoint où ils peuvent être utilisés pour des présentations tout en conservant leur interactivité. La figure montre un exemple d'un rapport Power View avec un graphique et un graphe liés. Lorsqu'une barre est cliquée sur le graphique, le graphique est filtré par ce pays et cette année.

Avec Power View, vous pouvez créer des visualisations communes telles que des tableaux, des matrices, des graphiques et des diagrammes. En outre, vous pouvez créer des diagrammes à bulles qui incluent un axe de temps qui révèle la façon dont les données changent au fil du temps (voir Figure).

Puisque Power View utilise les mosaïques de Bing Maps, vous pouvez présenter vos données sur des cartes interactives que vous pouvez zoomer et déplacer jusqu'au niveau de l'adresse de la rue pendant que vous explorez les données. La figure montre une carte des précipitations par comté.

Deux autres visualisations de données disponibles dans Power View sont les cartes et les mosaïques. Les cartes affichent les données de chaque ligne disposée dans un style de carte d'index. Par exemple, vous pouvez consulter les coordonnées des employés, comme le montre la figure.

La visualisation de mosaïque vous permet de filtrer les données de façon interactive en utilisant une bande de tabulation qui peut contenir du texte et des images. Par exemple, la Figure illustre l'utilisation de mosaïque pour filtrer les ventes par région à l'aide d'une image cartographique de la région dans la bande d'onglets.

Maintenant que vous connaissez quelques-uns des types de visualisations disponibles dans Power View, examinons le processus de création de certains rapports de base.

Téléchargez le carnet de Travaux dirigés et le classeur Excel.

Merci