La conversion de type de données dans Power Query affecte la modélisation des données dans Power BI
Dans mon expérience de conseil auprès de clients utilisant Power BI, de nombreux défis auxquels sont confrontés les développeurs Power BI sont dus à la négligence des types de données. Voici quelques défis courants qui sont les résultats directs ou indirects de types de données inappropriés et de la conversion de types de données :
- Obtenir des résultats incorrects alors que tous les calculs de votre modèle de données sont corrects.
- Modèle de données peu performant.
- Taille de modèle gonflée.
- Difficultés à configurer des agrégations définies par l’utilisateur (sensibilisation à l’agrégation).
- Difficultés à configurer l’actualisation incrémentielle des données.
- Obtention de visuels vierges après la première actualisation des données dans le service Power BI.
Dans cet article de blog, j’explique les pièges courants pour prévenir les défis futurs qui peuvent prendre du temps à identifier et à résoudre.
Contents
Arrière-plan
Avant de plonger dans le sujet de cet article de blog, je voudrais commencer par un peu de contexte. Nous savons tous que Power BI n’est pas seulement un outil de reporting. Il s’agit en effet d’une plate-forme de données prenant en charge divers aspects de l’intelligence d’affaires, de l’ingénierie des données et de la science des données. Il y a deux langages que nous devons apprendre pour pouvoir travailler avec Power BI : Requête de puissance (M) et DAX. Le but des deux langues est tout à fait différent. Nous utilisons Requête de puissance pour la transformation et la préparation des données, tandis que DAX est utilisé pour l’analyse des données dans le modèle de données tabulaire. Voici le point, les deux langages dans Power BI ont des types de données différents.
Les scénarios de développement Power BI les plus courants commencent par la connexion à la ou aux sources de données. Power BI prend en charge des centaines de sources de données. La plupart des connexions de source de données se produisent dans Power Query (la couche de préparation des données dans une solution Power BI), sauf si nous nous connectons en direct à une couche sémantique telle qu’une instance SSAS ou un jeu de données Power BI. De nombreuses sources de données prises en charge ont leurs propres types de données, et certaines n’en ont pas. Par exemple, SQL Server a ses propres types de données, mais pas CSV. Lorsque la source de données a des types de données, le moteur de mashup essaie d’identifier les types de données au type de données le plus proche disponible dans Power Query. Même si le système source a des types de données, les types de données peuvent ne pas être compatibles avec les types de données Power Query. Pour les sources de données qui ne prennent pas en charge les types de données, le moteur de correspondance tente de détecter les types de données en fonction des exemples de données chargés dans le volet d’aperçu des données dans la fenêtre de l’éditeur Power Query. Cependant, rien ne garantit que les types de données détectés sont corrects. Il est donc recommandé de valider les types de données détectés de toute façon.
Power BI utilise les types de données de modèle tabulaire lorsqu’il charge les données dans le modèle de données. Les types de données dans le modèle de données peuvent ou non être compatibles avec les types de données définis dans Power Query. Par exemple, Power Query a un type de données binaire, mais pas le modèle tabulaire.
Le tableau suivant montre les types de données de Power Query, leurs représentations dans l’interface utilisateur de l’éditeur Power Query, leurs types de données de mappage dans le modèle de données (DAX) et les types de données internes dans le moteur xVelocity (modèle tabulaire) :
Comme le montre le tableau ci-dessus, dans l’interface utilisateur de Power Query, Nombre entier, décimal, décimal fixe et Pourcentage sont tous de type nombre dans le moteur Power Query. Les noms de type dans l’interface utilisateur de Power BI diffèrent également de leurs équivalents dans le moteur xVelocity. Creusons plus profondément.
Types de données dans Power Query
Comme mentionné précédemment, dans Power Query, nous n’avons qu’un seul type de données numérique : nombre tandis que dans l’interface utilisateur de l’éditeur Power Query, dans le Transformer onglet, il y a un Type de données bouton déroulant affichant quatre types de données numériques, comme le montre l’image suivante :
Dans le langage de formule Power Query, nous spécifions un type de données numérique comme tapez le numéro ou Nombre.Type. Prenons un exemple pour voir ce que cela signifie.
L’expression suivante crée une table avec différentes valeurs :
#table({"Value"}
, {
{100}
, {65565}
, {-100000}
, {-999.9999}
, {0.001}
, {10000000.0000001}
, {999999999999999999.999999999999999999}
, {#datetimezone(2023,1,1,11,45,54,+12,0)}
, {#datetime(2023,1,1,11,45,54)}
, {#date(2023,1,1)}
, {#time(11,45,54)}
, {true}
, {#duration(11,45,54,22)}
, {"This is a text"}
})Les résultats sont affichés dans l’image suivante :
Maintenant, nous ajoutons une nouvelle colonne qui affiche le type de données des valeurs. Pour ce faire, utilisez le Type de valeur([Value]) La fonction renvoie le type de chaque valeur de la Valeur colonne. Les résultats sont affichés dans l’image suivante :
Pour voir le type réel, nous devons cliquer sur chaque cellule (pas sur les valeurs) du Type de valeur colonne, comme illustré dans l’image suivante :
Avec cette méthode, nous devons cliquer sur chaque cellule pour voir les types de données des valeurs qui ne sont pas idéales. Mais il n’y a actuellement aucune fonction disponible dans Power Query pour convertir un Taper valeur à Texte. Ainsi, pour afficher la valeur de chaque type sous forme de texte dans un tableau, nous utilisons une astuce simple. Il existe une fonction dans Power Query renvoyant les métadonnées de la table : Table.Schema(table as table). La fonction génère un tableau révélant des informations utiles sur le tableau utilisé dans la fonction, y compris nom de colonne, TypeName, Type, et ainsi de suite. Nous voulons montrer TypeName de la Type de valeur colonne. Ainsi, nous n’avons qu’à transformer chaque valeur dans un tableau en utilisant le Table.FromValue(value as any) fonction. On obtient alors les valeurs de Type colonne de la sortie de la Table.Schema() fonction.
Pour ce faire, nous ajoutons une nouvelle colonne pour obtenir les valeurs textuelles de la Type colonne. Nous avons nommé la nouvelle colonne Types de données. L’expression suivante répond à cela :
Table.Schema(
Table.FromValue([Value])
)[Kind]{0}
L’image suivante montre les résultats :
Comme le montrent les résultats, toutes les valeurs numériques sont de type nombre et la façon dont ils sont représentés dans l’interface utilisateur de l’éditeur Power Query n’affecte pas la façon dont le moteur Power Query traite ces types. Les représentations de type de données dans l’interface utilisateur de Power Query sont en quelque sorte alignées sur le type facettes dans PowerQuery. Une facette est utilisée pour ajouter des détails à un genre de type. Par exemple, nous pouvons utiliser des facettes pour un type de texte si nous voulons avoir un type de texte qui n’accepte pas null. Nous pouvons définir les types de valeur à l’aide de facettes de type à l’aide de Facet.Type syntaxe, comme utiliser In64.Type pour un nombre entier 64 bits ou en utilisant Percentage.Type pour afficher un nombre en pourcentage. Cependant, pour définir le type de la valeur, nous utilisons le type typename syntaxe telle que la définition d’un nombre à l’aide type number ou un texte utilisant type text. Le tableau suivant montre les types Power Query et la syntaxe à utiliser pour les définir :
Malheureusement, la documentation Power Query Language Specification n’inclut pas les facettes et il n’y a pas beaucoup de ressources ou de livres en ligne que je peux référencer ici autres que le blog de Ben Gribaudo qui a expliqué en détail les facettes que je recommande fortement de lire.
Bien que le moteur Power Query traite les valeurs en fonction de leurs types et non de leurs facettes, l’utilisation de facettes est recommandée car elles affectent les données lors de leur chargement dans le modèle de données, ce qui soulève une question : que se passe-t-il après le chargement des données dans le modèle de données ? ce qui nous amène à la section suivante de cet article de blog.
Types de données dans le modèle de données Power BI
Power BI utilise le xVitesse moteur de traitement de données en mémoire pour traiter les données. Le xVitesse utilisations du moteur magasin de colonnes technologie d’indexation qui comprime les données en fonction de la cardinalité de la colonne, ce qui nous amène à un point critique : bien que le moteur Power Query traite toutes les valeurs numériques comme le type nombre, ils sont compressés différemment en fonction de leur cardinalité de colonne après le chargement des valeurs dans le modèle Power BI. Par conséquent, le réglage correct facette typographique pour chaque colonne est important.
Les valeurs numériques sont l’un des types de données les plus couramment utilisés dans Power BI. Voici un autre exemple montrant les différences entre les quatre nombre facettes. Exécutez l’expression suivante dans une nouvelle requête vide dans l’éditeur Power Query :
// Decimal Numbers with 6 Decimal Digits
let
Source = List.Generate(()=> 0.000001, each _ <= 10, each _ + 0.000001 ),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Source"}}),
#"Duplicated Source Column as Decimal" = Table.DuplicateColumn(#"Renamed Columns", "Source", "Decimal", Decimal.Type),
#"Duplicated Source Column as Fixed Decimal" = Table.DuplicateColumn(#"Duplicated Source Column as Decimal", "Source", "Fixed Decimal", Currency.Type),
#"Duplicated Source Column as Percentage" = Table.DuplicateColumn(#"Duplicated Source Column as Fixed Decimal", "Source", "Percentage", Percentage.Type)
in
#"Duplicated Source Column as Percentage"Les expressions ci-dessus créent 10 millions de lignes de valeurs décimales entre 0 et dix. Le tableau résultant comporte quatre colonnes contenant les mêmes données avec des facettes. La première colonne, Sourcecontient les valeurs de type n’importe quelqui se traduit par taper texte. Les trois colonnes restantes sont dupliquées à partir du Source colonne avec différents taper facettes, comme suit :
- Décimal
- Décimal fixe
- Pourcentage
La capture d’écran suivante montre les exemples de données résultants de notre expression dans l’éditeur Power Query :
Cliquez maintenant Fermer et appliquer du Maison de l’éditeur Power Query pour importer les données dans le modèle de données. À ce stade, nous devons utiliser un outil communautaire tiers, Studio DAXtéléchargeable ici.
Après le téléchargement et l’installation, DAX Studio s’enregistre en tant qu’outil externe dans Power BI Desktop, comme le montre l’image suivante :
Cliquez sur DAX Studio dans le Outils externes qui le connecte automatiquement au modèle actuel de Power BI Desktop, et suivez ces étapes :
- Clique le Avancé languette
- Clique le Afficher les métriques bouton
- Cliquez sur Colonnes du Analyseur VertiPaq section
- Regarde le Cardinalité, Taille de colet % Tableau Colonnes
L’image suivante montre les étapes précédentes :
Les résultats montrent que le Décimal colonne et Pourcentage consommé la partie la plus importante du volume de la table. Leur cardinalité est également beaucoup plus élevée que la Décimal fixe colonne. Donc ici, il est maintenant plus évident que l’utilisation de la Décimal fixe Type de données (facette) pour les valeurs numériques peut faciliter la compression des données, réduire la taille du modèle de données et augmenter les performances. Par conséquent, il est sage de toujours utiliser Décimal fixe pour les valeurs décimales. Comme le Décimal fixe les valeurs se traduisent par Monnaie type de données dans DAX, nous devons changer le format des colonnes si Monnaie est inadapté. Comme le nom le suggère, Décimal fixe a fixé quatre décimales. Par conséquent, si la valeur d’origine a plus de chiffres décimaux après la conversion en Décimal fixeles chiffres après la quatrième décimale seront tronqués.
C’est pourquoi le Cardinalité colonne dans VertiPaq Analyzer dans DAX Studio montre une cardinalité beaucoup plus faible pour le Décimal fixe colonne (les valeurs de la colonne ne conservent que jusqu’à quatre décimales, pas plus).
Téléchargez l’exemple de fichier à partir d’ici.
Ainsi, le message est ici de toujours utiliser le type de données qui a du sens pour l’entreprise et qui est efficace dans le modèle de données. L’utilisation de VertiPaq Analyzer dans DAX Studio permet de comprendre les différents aspects du modèle de données, y compris les types de données de colonne. En tant que modélisateur de données, il est essentiel de comprendre comment Power Query les types et facettes traduire en types de données DAX. Comme nous l’avons vu dans cet article de blog, la conversion du type de données peut affecter le taux de compression et les performances du modèle de données.
