Optimisation import csv quotidien en masse

Optimisation import csv quotidien en masseFollow
Guillaume Ragues
Guillaume RaguesJan 10, 2022 09:30 AM
Bonjour à tous, J’exporte tous les jours de SAP des tables en fichier .csv afin de pouvoir garder un …
1. Optimisation import csv quotidien en masse

Guillaume Ragues
Posted Jan 10, 2022 09:30 AM
Bonjour à tous,

J’exporte tous les jours de SAP des tables en fichier .csv afin de pouvoir garder un historique et faire des courbes d’évolution et d’analyse dans Power BI.
Une connexion directe ne me permettrait que d’avoir un état instantané, et la fonction n’est de toute façon pas encore disponible chez nous.

Chaque export de chaque table est rangé dans un dossier particulier.

J’utilise l’importation par dossier et ma problématique est que cela représente une centaine de fichiers csv de chaque type dans plusieurs dossiers

Chaque jour PowerBI réimporte donc les mêmes fichiers + les nouveaux du jour, ce qui prend tous les jours de plus en plus de temps pour des taches inutiles.
Je précise qu’une fois exporté, les csv ne sont que très rarement modifiés à posteriori.

Auriez vous des idées afin d’optimiser tout ça ?

N’y aurait il pas une méthode pour importer les csv dans une table, les archivés puis rajouté tous les jours uniquement les nouveaux fichiers ?
Ou de n’importé dans le dossier que les nouveaux fichiers ou ceux qui auraient été modifié un peu comme dans un direct query ?

A l’heure actuelle, ma seule idée serait de créer un fichier Excel avec une macro pour importer les csv, et déplacer les csv déjà importé dans un dossier d’archive, un onglet par type de csv, et de connecter PowerBI à ce fichier Excel… l’importation d’un fichier Excel étant bien plus rapide que l’importation de plusieurs centaines de csv à nombre de ligne équivalent…

Nous disposons d’un sharepoint d’entreprise et de PowerBI prémium

——————————
Guillaume Ragues
Responsable amélioration continue
——————————

2. RE: Optimisation import csv quotidien en masse

Gold Contributor
Tristan Malherbe
Posted Jan 10, 2022 09:36 AM
Bonjour Guillaume,

Je vous conseille de mettre en place un rafraîchissement incrémental en vous basant sur la date de création ou la date de modification de vos fichiers (propriétés disponibles en utilisant le connecteur Site SharePoint).

Cela fonctionne parfaitement et ne nécessite même pas Power BI Premium.
Bon courage !

——————————
Tristan Malherbe
Co-Fondateur du Club Power BI
Expert/Formateur Power BI – Microsoft MVP
——————————

3. RE: Optimisation import csv quotidien en masse

Bronze Contributor
Bertrand d’Arbonneau
Posted Jan 11, 2022 08:07 AM
A titre d’exemple, voici un code qui s’inspire d’un article très instructif de Miguel Escobar sur ce sujet (Incremental refresh for files in a Folder or SharePoint). Un des points importants (cf commentaire) est de prévoir le cas où la combinaison de dates d’une partition ne ramène aucun résultat. Afin d’éviter une erreur, la dernière étape permet de retourner une table vide ayant le même schema que lorsqu’il y a des resultats.

Après avoir examiné les traces http dans Fiddler, j’ai opté pour Sharepoint.Contents plutot que Sharepoint.Files. Il m’a semblé que le Query folding fonctionnait mieux ainsi.

// Events
let
Source = SharePoint.Contents(myOneDriveURL, [ApiVersion = 15]),
Documents = Source{[Name=”Documents”]}[Content],
#”Power BI Audit events” = Documents{[Name=”Power BI Audit events”]}[Content],
#”Filtered Rows” = Table.SelectRows(#”Power BI Audit events”, each [Date created] >= DateTimeZone.From(RangeStart) and [Date created] <= DateTimeZone.From(RangeEnd)),
BufferBinaries = List.Transform(#”Filtered Rows”[Content],Binary.Buffer),
TransformEachFile = List.Transform(BufferBinaries, f_TransformCSV),
CombineIntoTable = Table.Combine(TransformEachFile),
TryError = try CombineIntoTable otherwise Emptytable
in
TryError

// RangeStart
#datetime(2021, 1, 1, 0, 0, 0) meta [IsParameterQuery=true, Type=”DateTime”, IsParameterQueryRequired=true]

// RangeEnd
#datetime(2021, 1, 5, 0, 0, 0) meta [IsParameterQuery=true, Type=”DateTime”, IsParameterQueryRequired=true]

// f_TransformCSV
let
Source = (Content as binary) => let
#”Imported CSV” = Csv.Document(Content,[Delimiter=”,”, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#”Promoted Headers” = Table.PromoteHeaders(#”Imported CSV”, [PromoteAllScalars=true]),
#”Removed Other Columns” = Table.SelectColumns(#”Promoted Headers”,{“CreationTime”, “Operation”, “WorkSpaceName”, “DatasetName”, “ReportName”, “WorkspaceId”, “ObjectId”, “DatasetId”, “ReportId”}),
#”Changed Type” = Table.TransformColumnTypes(#”Removed Other Columns”,{{“CreationTime”, type datetime}, {“Operation”, type text}, {“WorkSpaceName”, type text}, {“DatasetName”, type text}, {“ReportName”, type text}, {“WorkspaceId”, type text}, {“ObjectId”, type text}, {“DatasetId”, type text}, {“ReportId”, type text}})
in
#”Changed Type”
in
Source

// Emptytable
let
Source = #table (type table
[
#”CreationTime” = datetime,
#”Operation” = text,
#”WorkSpaceName” = text,
#”DatasetName” = text,
#”ReportName” = text,
#”WorkspaceId” = text,
#”ObjectId” = text,
#”DatasetId” = text,
#”ReportId” = text
] ,{})
in
Source

——————————
Bertrand d’Arbonneau
——————————

4. RE: Optimisation import csv quotidien en masse

Guillaume Ragues
Posted Feb 18, 2022 07:37 AM
Bonjour à tous les deux,

Déjà un immense MERCI, je ne connaissais pas du tout l’actualisation incrémentielle, ni le Query folding. Rien qu’avec le Query folding en mixant votre de code et celui de Miguel l’actualisation est devenue beaucoup beaucoup plus rapide (et suffisante à l’heure actuelle même sans l’incrémentielle)

En revanche pour aller plus loin avec l’actualisation incrémentielle j’ai encore quelques soucis…
Avec une table ça fonctionne parfaitement (qqs secondes au lieu de plusieurs minutes), par contre ce n’est pas prévu pour 3 tables comme dans mon cas…
(Je ne vois pas comment ni l’intérêt de combiner les 3 tables)

Ma dernière option était de faire 3 flux de données avec chacune l’actualisation incrémentielle, mais le power query et le paramétrage du rafraichissement incrémental n’est pas le même que dans la version Desktop et cela ne fonctionne pas du tout !

Pbi Desktop :
Pbi Desktop
PQuery_flux_données :
PQuery_flux_données
Dans le flux de données il m’oblige à sélectionner une colonne de type datetime alors que dans la version desktop non.
N’ayant pas de colonne Datetime dans le résultat final de la table, cela ne fonctionne pas…

code type actuel d’une des 3 tables :
// fCSV_libé
// Fonction de transformation des fichiers csv

(Content as binary) => let
#”Imported CSV” = Csv.Document(Binary.Buffer(Content),[Delimiter=”;”, Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#”Promoted Headers” = Table.PromoteHeaders(#”Imported CSV”, [PromoteAllScalars=true]),
#”Type modifié” = Table.TransformColumnTypes(#”Promoted Headers”,{{“Article”, type text}, {“Lot”, type text}, {“Jour de libération”, Int64.Type}, {“Nb Pal”, Int64.Type}}),
#”Lignes filtrées” = Table.SelectRows(#”Type modifié”, each [Article] <> null and [Article] <> “”)
in
#”Lignes filtrées”

//myFX_libé
//Fonction principal inpiré de votre code et celui de Miguel

(StartDate as datetime, EndDate as datetime) =>

let

start = Date.From(StartDate),
end = Date.From(EndDate),
Fichiers = List.Transform(List.Dates(start,Number.From(end)-Number.From(start)+1,#duration(1, 0, 0, 0)), each “Lib_” & Date.ToText(Date.From(_), “yyyyMMdd”) & “.csv”),
Source = SharePoint.Contents(“https://xxxxx.sharepoint.com/sites/xxxxx”, [ApiVersion=15]),
#”Documents partages” = Source{[Name=”Documents partages”]}[Content],
#”05 – SLR” = #”Documents partages”{[Name=”05 – SLR”]}[Content],
#”05 – 04 – Extraction SAP” = #”05 – SLR”{[Name=”05 – 04 – Extraction SAP”]}[Content],
#”Libération” = #”05 – 04 – Extraction SAP”{[Name=”Libération”]}[Content],
#”Type modifié1″ = Table.TransformColumnTypes(#”Libération”,{{“Date created”, type datetime}, {“Date modified”, type datetime}}),
#”Filtered Rows” = Table.SelectRows(#”Type modifié1″, each List.Contains( List.Transform( Fichiers, (x)=> Text.Contains( [Name],x) ), true)),

#”Fonction personnalisée appelée” = Table.AddColumn(#”Filtered Rows”, “Content.2″, each fCSV_libé([Content])),

AddFileNameToTable = Table.AddColumn(#”Fonction personnalisée appelée”, “Custom.1”, each Table.AddColumn([Content.2], “File Name”, (R)=> [Name],type text)),
RemoveContentCol = Table.SelectColumns(AddFileNameToTable,{“Custom.1″}),
#”Custom 1″ = Table.Combine(RemoveContentCol[Custom.1]),
#”Plage de texte insérée” = Table.AddColumn(#”Custom 1″, “Date”, each Text.Middle([File Name], 4, 8), type text),
#”Type modifié” = Table.TransformColumnTypes(#”Plage de texte insérée”,{{“Date”, type date}}),
#”Colonnes supprimées” = Table.RemoveColumns(#”Type modifié”,{“File Name”}),
#”Personnalisée ajoutée” = Table.AddColumn(#”Colonnes supprimées”, “Jour de libération (texte)”, each Text.End(“00″ & Text.From([Jour de libération]),3),type text),

Schema = #table( type table [Date = date, #”Article” = text, #”Lot” = text, #”Jour de libération” = text, #”Nb Pal” = Int64.Type, #”Jour de libération (chiffre)” = Int64.Type], {})
in
try #”Personnalisée ajoutée” otherwise Schema

//Libération
//La table
let
Source = fx_libé(RangeStart,RangeEnd)
in
Source

//RangeStart
#datetime(2021, 8, 13, 13, 0, 42) meta [IsParameterQuery=true, Type=”DateTime”, IsParameterQueryRequired=true]

//RangeEnd
#datetime(2022, 2, 15, 8, 0, 0) meta [IsParameterQuery=true, Type=”DateTime”, IsParameterQueryRequired=true]​

Si vous avez une idée pour pouvoir faire une actualisation incrémentale avec 3 tables ?

PS : désolé pour le temps de réponse, grosse surcharge j’ai pu démarrer que cette semaine à travailler le sujet …

——————————
Guillaume Ragues
Responsable amélioration continue
——————————

5. RE: Optimisation import csv quotidien en masse

Bronze Contributor
Bertrand d’Arbonneau
Posted Mar 04, 2022 03:46 AM
La démarche pour un dataflow est légèrement différente. Lorsque l’on veut mettre en place la politique de rafraichissement, il faut impérativement une colonne de type datetime. Et le dataflow ajoute automatiquement une étape de filtrage à la fin, que l’on ne peut pas supprimer ou modifier (elle est recrée à chaque sauvegarde). L’astuce consiste à neutraliser cette étape en fournissant une colonne datetime bidon contenant la valeur du paramètre RangeStart. Il faut bien entendu que préalablement on applique le filtrage par RangeStart et RangeEnd au plus près de la source pour assurer le query folding. On ajoute alors une colonne fixe datetime avec la valeur RangeStart, et on peut appliquer alors la politique de rafraichissement incrémental au niveau de l’entité du dataflow.

Pour simplifier cette démarche et tester chaque étape, je commence à mettre en place le query de base avec des paramètres Range_Start et Range_End. J’applique la politique de rafraichissement sans effectuer de rafraichissement: cela crée les ‘vrais’ RangeStart et RangeEnd ainsi que la dernière étape de filtrage. Je n’ai plus alors qu’à modifier les références à Range_Start et Range_End et retirer le caractère ‘_’.

A noter que lorsqu’on veut rééditer le query, il faut réécraser les valeurs de RangeStart et RangeEnd avec un range de test afin d’avoir des données visibles.

——————————
Bertrand d’Arbonneau
——————————