Échec linguistique épique, ou Comment entasser tout le dictionnaire de la langue russe dans Power BI

D'une manière ou d'une autre, en résolvant le problème de l'analyse linguistique dans Power BI et en recherchant en même temps des exemples pour mon article précédent , je me suis souvenu du problème que j'ai essayé de résoudre dans Excel il y a plusieurs années: il était nécessaire d'implémenter un dictionnaire de langue russe dans le système analytique pour l'analyse linguistique d'un grand nombre de requêtes en langage naturel. Et il était souhaitable d'utiliser des outils bureautiques standard. La grande majorité des gens entreprendraient immédiatement cette tâche dans Excel, et j'ai une fois suivi le même chemin. J'ai utilisé le corpus ouvert de la langue russe ( http://opencorpora.org/ ) comme dictionnaire.

Mais la déception m'attendait - le dictionnaire comprenait 300 000 formes de mots, plus de 5 millions d'entrées, et pour Excel c'est, en principe, un montant impossible. Même si vous y introduisez «juste» 1 million de lignes, seule une personne très patiente qui ne sera jamais pressée pourra effectuer des manipulations avec elle ou, Dieu nous en préserve, des calculs. Mais cette fois, j'ai décidé de définir un outil plus approprié pour la tâche - Power BI.

Qu'est-ce que Power BI?

Je trouve ce produit largement sous-estimé par la communauté professionnelle. Power BI est un ensemble d'outils d'analyse commerciale qui a été créé pour les utilisateurs qui possèdent Excel à un niveau légèrement supérieur à «diviser le montant en colonnes». Si une personne est capable d'écrire des formules de complexité moyenne dans Excel, elle maîtrisera Power BI en quelques soirées.

Ce n'est pas un produit unique avec une sorte de logique de programmation interne, mais un système de trois composants:

• Requête de puissance Il s'agit d'un ETL, dans lequel il est nécessaire d'écrire des requêtes en utilisant son propre langage de programmation entièrement fonctionnel - M. En toute honnêteté, il convient de noter que, très probablement, un utilisateur ordinaire est peu susceptible d'avoir à le programmer: la plupart des fonctions sont disponibles directement via le menu ou l'assistant dans l'interface du composant. Le langage M est complètement différent du langage de requête DAX (PowerPivot). Cependant, Microsoft les a réunis. Cela a du sens du point de vue du développement: ETL est conçu pour la réception et la saturation initiale des données (pas rapide), et DAX - pour les calculs qui nous aident à visualiser ces données (rapidement). Autrement dit, DAX est pour le frontal, et Power Query pour le backend, pour le processus d'extraction et de formatage des données.
• PowerPivot . Un module de traitement en mémoire basé sur le moteur xVelocity. Utilise le langage de requête DAX, très similaire au langage de formule Excel.
• Composant de visualisation . Il est très utile pour une utilisation dans des systèmes où vous devez visualiser des données: sur le site Web d'une entreprise, ou sur un portail de support technique (par exemple, un nuage de requêtes), ou sur une ressource interne de l'entreprise. Il existe des outils qui peuvent le faire sans Power BI, mais beaucoup d'entre eux n'aideront pas lorsque le nombre d'enregistrements est en millions et que les données doivent être agrégées d'une manière ou d'une autre. Et avec d'autres outils de ce type, Power BI fait concurrence en raison de sa simplicité et de son faible coût de traitement en mémoire. Il est clair que si nous parlons de téraoctets de données, une approche différente sera nécessaire. Et pour de tels cas, Microsoft a déjà quelque chose à offrir, mais c'est un sujet pour un article séparé.

La courbe d'apprentissage à la première étape augmente très fortement: si vous êtes bon dans Excel, alors 80% des fonctionnalités de Power BI s'ouvriront à vous après une courte étude. C'est un outil très puissant, assez facile à utiliser, mais - jusqu'à un certain point. Pour l'utiliser à pleine capacité, vous aurez déjà besoin d'expérience et d'une connaissance approfondie des langages M et DAX.

À quoi sert Power BI Desktop?

À qui cela peut-il être utile? Tout d'abord, tous les utilisateurs professionnels qui doivent traiter et analyser de grandes quantités de données lorsque Excel n'est plus en mesure de faire face ou de gonfler à la limite. J'insiste - Power BI Desktop est conçu pour un large éventail d'utilisateurs qui résolvent une gamme très variée de tâches . Par exemple, dans mon cas, il s'agissait de normaliser 5 millions d'entrées de texte pour la détermination ultérieure de la fréquence des mots clés.

Ceci est demandé lors du traitement des questionnaires, des requêtes des moteurs de recherche, des publicités, des dictées / essais, des tableaux statistiques, etc. Ou pour la résolution de mots croisés ...

Un autre cas et une option de mise en œuvre sont examinés dans un article sur le «reconnaissant» de Dmitry Tumaikin. Implémenté sur Excel classique, mais en utilisant des macros ...

Un autre scénario populaire pour cette application de Power BI consiste à calculer le ratio d'indicateurs pour la période actuelle et précédente. Par exemple, nous avons des données de revenus pré-agrégées et vous devez les comparer en jours avec le trimestre, l'année ou la période précédente. Et je veux / dois insérer le résultat de la comparaison dans la colonne suivante sous forme de valeurs, pas de formules. Il semblerait que pour Excel, la tâche la plus simple consiste à écrire une formule de comparaison simple et à l'étirer sur toutes les cellules de la colonne. Mais pas si vous avez plusieurs millions de lignes dans le tableau. Dans DAX lui-même, cette tâche est encore plus facile que dans Excel, mais aussi uniquement à l'aide de post-calculs.

De nombreux autres scénarios pratiques pour l'utilisation de Power BI peuvent être proposés, mais vous, je pense, avez déjà compris l'essentiel. Bien sûr, toutes ces tâches ne sont pas un problème pour un programmeur qui possède, par exemple, Python ou R, mais ces spécialistes sont a priori plus petits par ordre de grandeur que les experts Excel. Excel n'a que des possibilités limitées, mais pas avec Power BI, qui utilise le langage de formule DAX, qui est très similaire au langage de formule Excel, et est capable de traiter des millions et des dizaines de millions d'enregistrements à la volée. Et puis vous devez augmenter la RAM (au moins jusqu'à 100, au moins jusqu'à 300 Go).

Nous aidons les demandes de processus de support technique

Mais revenons à ma tâche. Il était nécessaire de trouver comment la ligne zéro du support technique évaluerait automatiquement le sujet des demandes des utilisateurs. Pour commencer, j'ai décidé d'isoler certaines formes de mots et de déterminer les sujets les plus importants que les utilisateurs soulèvent le plus souvent par la fréquence de leur apparition dans les messages.

Le dictionnaire source est un simple fichier texte qui a une structure régulière et ressemble à ceci:



À des fins statistiques, il a été nécessaire de déterminer la forme initiale pour chaque forme de mot: pour les noms - un seul numéro du cas nominatif, pour les verbes - une forme indéfinie, etc. Pour les programmeurs, cette tâche était plus simple que simple: pour chaque mot dans la colonne de gauche, recherchez la correspondance avec le formulaire qui suit immédiatement le numéro de ce mot dans le dictionnaire.

C'est juste l'utilisateur professionnel moyen qui ne possède pas Python, des outils spécialisés et des compétences en développement, ne sera pas en mesure de résoudre ce problème sans utiliser l'auto-analyse BI ou des outils conviviaux similaires. De plus, si les données doivent être traitées pour leurs besoins internes ou s'il n'y a aucune information confidentielle qui nécessite une protection, Power BI dans ce cas sera également gratuit *.


Pour analyser les données, j'avais besoin dans Power Query, dans un tableau de 5 millions d'enregistrements, d'ajouter une nouvelle colonne, décalée d'une position. Au début, j'ai essayé d'appliquer l'approche classique à l'aide de Power Query, qui est décrite sur le portail de la communauté Power BI par Marcel Beug, l'auteur de la référence en ligne originale de Power Query en ligne (également écrite en Power Query). Deux algorithmes différents sont proposés dans l'article: l'un est inspiré par les idées de Matt Elington, un gourou célèbre et formateur Power BI, et la seconde approche est l'idée originale de Marcel lui-même, en utilisant une fonction supplémentaire. Malgré le fait que pour augmenter la productivité, j'ai complètement mis en cache les données source, les deux approches ont nécessité un temps gigantesque - elles étaient déjà passées le huitième jour et le processus n'était pas terminé. La taille du fichier source était de 270 Mo et la taille actuelle des données traitées était proche de 17 To. Je suis sûr que peu d'utilisateurs Power BI ont vu de tels nombres dans la fenêtre de chargement des données à partir d'une source de fichiers.



Pourquoi le volume est si gonflé, ce n'est pas clair; même le produit cartésien de tous les enregistrements est bien inférieur à 16 To. Ici, l'optimiseur interne n'était clairement pas à la hauteur. Et, par exemple, DAX-Studio n'autorise pas le traçage des requêtes Power Query, uniquement DAX. Peut-être que quelqu'un partagera son expérience avec PQ Troubleshipping?

Sans attendre la fin du premier processus, j'ai décidé sur une autre machine d'essayer de résoudre le problème en utilisant DAX via une requête auto-écrite. La demande a été satisfaite ... en 180 secondes environ, et la consommation de mémoire a légèrement augmenté.



Code source d'une demande DAX:

KeyWord =
CALCULATE(
TOPN(1;
CALCULATETABLE(
VALUES(ShiftedList[Word])
;ALLEXCEPT(ShiftedList;ShiftedList[Word Nr])
)
)//TOPN
)//CALCULATE



C'est-à-dire que pour chaque ligne de la nouvelle colonne [KeyWord], la toute première valeur de la colonne [Word] est recherchée, contenant toutes les variantes de formes de mots ayant le même numéro de forme de mot de base (colonne [Word Nr]). Tant que le format du fichier source reste inchangé, la demande doit être exécutée sans erreur sur toutes les versions ultérieures du dictionnaire.

Le code de requête dans Power Query, qui forme la table source au format requis, a été généré «automatiquement» et terminé en moins d'une minute:



Après qu'une colonne de mots clés a été formée dans l'interface PowerPivot en trois minutes, la recherche des formes de mots dans l'interface Power BI ne prend pas plus de 4 secondes. De plus, une recherche de contrôle pour les mêmes données dans votre Notepad ++ x64 préféré pourrait prendre 20 secondes ou plus. Mais ce n'est pas une pierre dans le jardin de la centrale nucléaire - il est plus difficile (et plus long) de rechercher l'ensemble du tableau de données qu'en fonction de données déjà balisées.

Soit dit en passant, la demande DAX ci-dessus n'est pas née la première fois et les options intermédiaires ont consommé toute la mémoire disponible, ont fonctionné pendant une longue période et se sont terminées par une erreur de données ou un résultat non pertinent.



En conséquence, la taille du fichier PBIX enregistré est devenue 60% (112 Mo) plus petite que le dictionnaire de texte d'origine, mais plus de 4 fois la taille de l'archive ZIP avec le même dictionnaire.

Revenons à la bataille entre Power Query et DAX: la différence de durée d'une même opération dans différents composants suggère que Power BI n'est pas un pied-de-biche contre lequel il n'y a pas de réception. Il a son propre caractère et les caractéristiques de l'application, qui doivent être pris en compte dans son travail. En fait, comme tout outil. Et les recommandations de gourous même reconnus doivent être traitées avec prudence.

Il semble que le lauréat du prix Nobel Richard Smalley disait, paraphrasant la première loi de Clark: «Lorsque les experts disent que quelque chose est faisable, ils ont probablement raison (ils ne savent tout simplement pas quand). Quand ils disent que cela est impossible, ils se trompent probablement. "

Caractéristiques de la machine d'essai:
Processeur: Intel Core i7 4770 @ 3,4 GHz (4 cœurs)
RAM: 16 Go
Système d'exploitation: Windows 7 Enterprise SP1 x64

Le dictionnaire prêt à l'emploi au format Power BI peut être téléchargé ici .

Et ici, une version en ligne modifiée du dictionnaire est disponible. Vous pouvez résoudre les mots croisés à votre guise :)



... Soit dit en passant, il y a plusieurs années, la tâche a néanmoins été résolue dans Excel, mais pas à 100%. Juste pour l'analyse des textes, pas tout le corpus de la langue russe a été utilisé, mais le dictionnaire des fréquences. Pour le nettoyage de texte de base, n'importe laquelle des listes de fréquences top100 disponibles ici pour plusieurs dizaines de kilo-octets est tout à fait appropriée.

Yuri Kolmakov, expert, Département des systèmes de consolidation et de visualisation des données, Jet Infosystems ( McCow )