Dimension à variation lente (SCD) dans Power BI, partie 2, mise en œuvre de SCD 1
J’ai expliqué ce que signifie SCD dans une solution de Business Intelligence dans mon post précédent. Nous avons également expliqué que même si nous ne nous attendons pas à gérer SCD2 dans une implémentation Power BI, nous pouvons gérer des scénarios similaires à SCD1. Dans ce post, je vous explique comment faire.
Contents
Scénario
Nous avons une entreprise de vente au détail de produits. La société publie chaque année la liste des produits au format Excel, y compris le prix catalogue et le prix du revendeur. La liste des produits est publiée le premier jour de juillet au début de l’exercice. Nous devons implémenter une solution Power BI qui conserve les dernières données produit pour analyser les transactions de vente. L’image suivante montre la liste des produits pour 2013 :
Ainsi, chaque année, nous recevons un fichier Excel similaire à l’image ci-dessus. Les fichiers sont stockés sur un site SharePoint Online.
Scénario expliqué
Comme l’explique le post précédent, un SCD1 conserve toujours les données actuelles en mettant à jour les anciennes données avec les nouvelles données. Ainsi, un processus ETL lit les données de la source, identifie les données existantes dans la table de destination, insère les nouvelles lignes dans la destination, met à jour les lignes existantes et supprime les lignes supprimées.
Voici pourquoi notre scénario est similaire à SCD1, à une exception près :
- Nous ne mettons pas réellement à jour les données dans les fichiers Excel et ne créons pas de processus ETL pour lire les données des fichiers Excel, identifier les modifications et appliquer les modifications à un fichier Excel intermédiaire
- Nous devons lire les données des fichiers Excel source, conserver les dernières données tout en filtrant les anciennes et charger les données dans le modèle de données.
Comme vous le voyez, alors que nous adoptons une approche de mise en œuvre très différente, les résultats sont très similaires à une exception près : nous ne supprimons aucune ligne.
Mise en œuvre
Voici ce que nous devrions faire pour atteindre l’objectif :
- Nous obtenons les données dans Power Query Editor en utilisant le Dossier SharePoint connecteur
- Nous combinons les fichiers
- Nous utilisons le Numéro de produit colonne pour identifier les produits dupliqués
- Nous utilisons le Date de rapport colonne pour identifier les dernières dates
- Nous ne conservons que les dernières lignes
Comme nous obtenons les données de plusieurs fichiers stockés sur SharePoint Online, nous devons utiliser le Dossier SharePoint connecteur. Suivez ces étapes:
- Se connecter à SharePoint en ligne et accédez au site contenant les fichiers Excel de la liste de produits et copiez l’URL du site à partir du navigateur
- Du Obtenir des données dans Power BI Desktop, sélectionnez le Dossier SharePoint connecteur
- Cliquez sur Relier
- Collez le URL du site copié à l’étape 1
- Cliquez sur D’ACCORD
- Cliquez sur Transformer les données
- Cliquez sur le menu déroulant du filtre sur le Chemin du dossier colonne
- Trouvez le dossier Products List hébergeant les fichiers Excel et sélectionnez-le
- Cliquez sur D’ACCORD
- Renommez la requête en Produit
Jusqu’à présent, nous sommes connectés au dossier SharePoint Online dans Power Query Editor. L’étape suivante consiste à combiner les fichiers Excel.
Combiner des fichiers
Nous avons plusieurs options pour combiner des fichiers binaires dans une table à partir de l’éditeur Power Query. Dans cet article, nous utilisons la méthode la plus simple :
- Clique le Combiner des fichiers bouton de la Contenu colonne
- Sélectionnez le Liste de produits table
- Cliquez sur D’ACCORD
Le processus ci-dessus crée quelques requêtes regroupées dans des dossiers séparés, comme illustré dans l’image suivante :
Jusqu’à présent, nous avons combiné avec succès les fichiers Excel. L’étape suivante consiste à conserver uniquement les données les plus récentes.
Conserver les dernières données
Dans les prochaines étapes, nous examinons de plus près les données et nous implémentons un mécanisme pour identifier les dernières données, les conserver et les charger dans le modèle de données.
- L’examen des résultats des données combinées montre une Nom de la source colonne que nous devons maintenant conserver, nous la supprimons donc en la sélectionnant et en cliquant sur le Supprimer des colonnes bouton de la Maison languette
Jusqu’à présent, nous nous sommes connectés au dossier en ligne SharePoint et avons combiné les fichiers Excel contenus. Examinons les données et voyons ce que nous avons. J’ai trié les données par Numéro de produit pour mieux comprendre les changements de données. L’image suivante montre les résultats :
Comme le montre l’image ci-dessus, plusieurs produits apparaissent dans plusieurs listes. C’est exactement ce que nous nous attendions à voir. L’objectif est de conserver les données produit les plus récentes uniquement sur la base des Date de rapport. Nous devrions donc obtenir le Numéro de produit et le maximum de la Date de rapport. Pour y parvenir, nous utilisons le Par groupe fonctionnalité dans l’éditeur Power Query. L’utilisation de Regrouper par à partir de l’interface utilisateur dans l’éditeur Power Query utilise le Table.Group() fonction dans Power Query. Comme le processus Regrouper par n’a pas besoin que les données soient triées, nous supprimons le Lignes triées marcher. Sur ce, faisons le travail.
- Sélectionnez le Numéro de produit colonne
- Clique le Par groupe colonne de la Transformer languette
- Entrer Date de rapport pour le Nouveau nom de colonne
- Sélectionner Max du Opération menu déroulant
- Sélectionnez le Date de rapport du Colonne menu déroulant
- Cliquez sur D’ACCORD
L’image suivante montre les résultats :
Nous avons maintenant tous les numéros de produits avec leurs dernières dates de déclaration. La seule pièce restante du puzzle est de joindre les résultats de la Lignes groupées étape avec les données de son étape précédente. Pour cela on utilise le Fusionner les requêtes fonctionnalité qui exécute le Table.NestedJoin() fonction dans Power Query.
- Sélectionnez le Lignes groupées étape de la Étapes appliquées liste de la Paramètres de requête vitre
- Clique le Fusionner les requêtes bouton de la Maison languette
- Sélectionnez le tableau Produit (actuel) dans la liste déroulante. Notez que nous sélectionnons la requête actuelle (Produit)
- Sur le tableau du haut, appuyez sur la touche Ctrl de votre clavier et sélectionnez le Numéro de produit et le Date de rapport colonnes séquentiellement
- Faites de même pour le tableau du bas. Notez que la séquence de sélection des colonnes est importante
- Assurez-vous que le Rejoindre le genre est Extérieur gauche (tous à partir du premier, correspondant à partir du second)
- Cliquez sur D’ACCORD
Comme mentionné précédemment, l’opération de fusion utilise le Table.NestedJoin() fonction, qui accepte deux tables (mises en évidence dans jaune dans l’expression ci-dessous), une liste de leurs colonnes clés à utiliser sur la jointure (surlignées dans rouge dans l’expression ci-dessous), un nom pour la nouvelle colonne de tableau des types et le type de jointure. Dans l’opération ci-dessus, comme le Lignes groupées est la dernière étape de transformation, nous avons joint les résultats de la Lignes groupées étape de transformation en elle-même. Voici le code généré par Power Query Editor après avoir parcouru les étapes 21 à 27 :
Table.NestedJoin(#"Grouped Rows", {"ProductNumber", "Reporting Date"}, #"Grouped Rows", {"ProductNumber", "Reporting Date"}, "Grouped Rows", JoinKind.LeftOuter)Mais ce n’est pas ce que nous voulons, nous n’avons pas besoin de joindre les résultats de la Lignes groupées étape de transformation en elle-même. Nous devons joindre les résultats de la Lignes groupées étape de transformation par les résultats de la Colonnes supprimées marcher. Par conséquent, nous devons modifier l’expression ci-dessus comme suit :
Table.NestedJoin(#"Grouped Rows", {"ProductNumber", "Reporting Date"}, #"Removed Columns", {"ProductNumber", "Reporting Date"}, "Grouped Rows", JoinKind.LeftOuter)L’image suivante montre la modification apportée à l’expression et les résultats :
- Clique le Développer bouton sur le Lignes groupées colonne
- Désélectionnez le Numéro de produit et Date de rapport colonnes pour garder les autres colonnes sélectionnées
- Décochez la Utiliser le nom de la colonne d’origine comme préfixe option
- Cliquez sur D’ACCORD
Terminé! L’image suivante montre les résultats finaux :
Nous pouvons maintenant appliquer les modifications pour charger les données dans le modèle de données. Avec cette technique, lorsqu’un nouveau fichier Excel (une nouvelle liste de produits) atterrit dans SharePoint Online, Power BI passe par les étapes de transformation ci-dessus pour s’assurer que nous avons toujours les dernières données de produit chargées dans le modèle de données, ce qui est très similaire au comportement d’un SCD1.
Avez-vous déjà utilisé cette méthode ? Avez-vous une meilleure technique pour gérer un scénario similaire ? Vous pouvez partager vos réflexions dans la section des commentaires ci-dessous.
