Arrière plan
Microsoft Excel est une solution de feuille de calcul populaire et préférée pour les rapports d’utilisation quotidienne rapide par la majorité des sociétés et des entreprises dans le monde. Souvent, les utilisateurs de l’entreprise ont besoin d’accéder aux données de l’organisation dans Excel pour poursuivre le développement des rapports MIS. Power Query est un outil puissant intégré à Excel qui peut se connecter au serveur de base de données interne, aux services CRM/ERP en ligne, aux fichiers Excel, etc. hébergés sur le réseau d’une organisation ou sur le cloud. En dehors de cela, les utilisateurs utilisent également un lien hypertexte vers d’autres fichiers Excel en réutilisant les données source existantes déjà préparées par d’autres personnes.
Défis / Problèmes
De nombreux défis ou problèmes surviennent lorsqu’un utilisateur tente de se référer à des données stockées dans un autre fichier Excel (via des formules) ou un serveur de base de données / ERP (via Power Query) comme ci-dessous :
- Il y a une forte probabilité que le fichier Excel source d’origine auquel les références sont faites soit supprimé par le propriétaire, ou que la structure soit modifiée, brisant éventuellement les références de cellule. Cela conduirait certainement à des erreurs dans les rapports.
- Les données importées via Power Query arrivent sous forme de tableau. Les tableaux ne conviennent généralement pas aux rapports, car diverses autorités déclarantes prescrivent un format fixe et prédéfini dans lequel préparer et soumettre un rapport.
- Power Query est un peu technique et tous les utilisateurs ne sont peut-être pas en mesure de le comprendre.
- La connexion à la base de données nécessite une adresse IP de serveur, des informations d’identification, etc. L’exposition de telles informations critiques peut entraîner des failles de sécurité, permettant un accès facile aux données internes de l’organisation.
Solutions
Microsoft propose 2 façons de se connecter aux données publiées Power BI à partir d’Excel
- Connectivité directe à l’ensemble de données publié Power BI via Pivot de puissancequi prend également en charge la création de mesures DAX.
- Accès aux tables désignées des jeux de données Power BI, qui peuvent être utilisées comme Types de données organisationnelles.
PIVOT ÉLECTRIQUE
Un jeu de données est essentiellement une collection de tables corrélées à l’aide de la fonctionnalité de relation de Power BI. Excel manque d’un moyen simple et direct d’analyser les données relationnelles. Il faut utiliser VLOOKUP, XLOOKUP, MATCH, INDEX comme fonctions pour corréler les données. Power Pivot offre un moyen simple de réutiliser l’ensemble de données publié Power BI, ayant déjà défini des relations pour concevoir des rapports sous forme de tableau croisé dynamique ou de graphiques croisés dynamiques directement dans Excel lui-même.
Les étapes pour analyser l’ensemble de données publié Power BI à l’aide de Power PIVOT sont les suivantes
- Dans Excel, accédez à l’onglet Données > Obtenir des données > À partir de Power BI
- La fenêtre du volet de droite apparaîtra. Sélectionnez le jeu de données cible dans la liste
- Excel créera une nouvelle feuille de calcul avec un concepteur de tableau croisé dynamique vierge. Faites glisser et déposez les champs dans Pivot, tout comme Pivot normal.
Types de données organisationnelles
Excel prend en charge nativement 3 types de types de données :
- Texte
- Numéro (y compris la date et l’heure)
- Logique (Vrai/Faux)
La formule n’est pas un type de données mais correspond à l’un de ces 3 types de données. La version Office 365 d’Excel a introduit la prise en charge d’un nouveau type de données appelé Type de données lié, qui est de type record. Le type de données lié contient une référence à un enregistrement (ou une ligne) contenant plusieurs champs. Il s’agit donc pratiquement d’une cellule qui peut contenir plusieurs valeurs en interne (voir la capture d’écran ci-dessous).
(les types de données liés ont une icône comme préfixe dans la valeur de la cellule)
La valeur d’une cellule de type de données liée peut être extraite dans une autre cellule en référençant la cellule de type de données liée à l’aide de la formule =cell_reference suivie d’un signe de point, qui énumère en outre les noms de champ dans cette cellule de type d’enregistrement (voir capture d’écran ci-dessous)
(capture d’écran évaluée ci-dessous)
Excel inclut quelques sources de types de données liées intégrées telles que Bourse, Devise, Géographie, etc. En dehors de cela, toute table de jeu de données Power BI peut être promue pour s’inclure en tant que type de données lié personnalisé, qui n’est disponible que pour les utilisateurs Excel de cette organisation. Ces types de données sont disponibles en tant que types de données organisationnelles.
Les types de données liés à l’organisation peuvent être créés à l’aide de Power BI, en définissant une table en tant que table sélectionnée, puis en la publiant (capture d’écran ci-dessous).
Avantages de l’approche des types de données liés à l’organisation :
- Cette approche empêche l’exposition du fichier Excel source ou de la base de données à l’utilisateur final, renforçant ainsi la confidentialité.
- Le service Power BI prend en charge un système élégant de contrôle d’accès en désignant l’accès à l’espace de travail à un groupe d’utilisateurs spécifié, qui s’applique également aux types de données organisationnelles.
- L’utilisateur n’a pas besoin d’importer à nouveau les données source dans une feuille de travail distincte pour configurer VLOOKUP afin de récupérer les valeurs d’autres champs. Ainsi, le fichier Excel résultant est léger avec une taille plus petite et moins de formules (expliqué dans l’étude de cas).
- Les types de données organisationnelles fonctionnent de manière transparente dans Excel Online (Excel basé sur un navigateur). L’utilisateur n’a même pas besoin d’être sur le VPN d’une organisation pour accéder aux données source. Power Query ou les références de fichiers Excel externes nécessitent que l’utilisateur se trouve sur le réseau local d’une organisation, ce qui est un inconvénient.
Étude de cas : RECHERCHEV vs types de données d’organisation
Scénario:
Les RH d’une organisation doivent préparer un fichier Excel dans lequel nous devons effectuer une analyse pour chaque employé. Il exporte un fichier Excel de Maître des employés à partir de l’ERP, puis copiez et collez manuellement Maître des employés données de ce fichier Excel chaque mois. Actuellement, il référence et relie ces données de base à l’aide de la fonction VLOOKUP (comme illustré ci-dessous). Il maintient cette feuille de calcul principale dans de nombreux fichiers Excel et doit la mettre à jour manuellement.
Dans l’approche ci-dessus, si le RH ne parvient pas à mettre à jour Maître des employés feuille de calcul, cela peut entraîner des rapports et une prise de décision incorrects. De plus, si une colonne est ajoutée ou supprimée de Employee Master à l’avenir, la fonction VLOOKUP nécessitera une modification manuelle des références de colonne.
En tant que Consultant BI, quelle solution pouvez-vous proposer ?
La solution:
Power BI prend en charge la connectivité aux ERP, bases de données, fichiers Excel populaires, etc. Nous allons simplement créer un jeu de données dans Power BI, en extrayant ces données de l’ERP (via des transformations Power Query si nécessaire). Et puis, sans créer de visualisation, nous publierons simplement l’ensemble de données, en définissant la table en vedette dans la fenêtre de modélisation de Power BI, dans l’espace de travail souhaité des RH. Cela permettra aux RH d’afficher les tableaux des ensembles de données Power BI partagés avec lui. Ensuite, nous supprimerons simplement VLOOKUP et le remplacerons par des références de cellule, comme illustré ci-dessous :
Compatibilité
Toutes les choses expliquées et démontrées dans le blog sont compatibles sur une version Office 365 d’Excel (Desktop + Web). L’utilisateur doit être sur un abonnement Office 365 Business ou Enterprise. L’abonnement Office 365 Personnel / Famille ou les éditions perpétuelles d’Office comme 2013, 2016, 2019, 2021, etc. ne prennent pas en charge toutes ces fonctionnalités car elles nécessitent un domaine associé, qui manque dans ces éditions.
Source link