Contexte, déjà abordé dans mon article : Power Query & Sharepoint library
Un client a sur son Office 365 plusieurs librairies SharePoint pour les différentes filiales et la direction internationale du groupe. Chacune des filiales ne peut accéder qu’à leurs bibliothèques mais la direction internationale à tous les droits sur toutes les librairies, les filiales sont en fait des sous-sites SharePoint (détail non négligeable pour la suite).
Un cube tabulaire Power Pivot doit être implémenté permettant la consolidation des données provenant des différentes filiales ainsi que de la direction.
Pour simplifier l’alimentation, je pensai créer un Workflow SharePoint qui suite à une validation regrouperait les documents d’alimentation dans une seule librairie qui sera cachée aux utilisateurs et deviendra ma seule source d’alimentation.
Problème : Les Workflows SharePoint peuvent copier des documents entre plusieurs librairies seulement si elles sont sur un même site !
Solution : configurer le « Custom Send To Destination » dans les paramètres avancés de chacune des librairies pour référencer la librairie de destination
La validation est faite dans la librairie finale : elle reçoit les différents fichiers que les filiales souhaitent pousser, les fichiers reçus sont en statut « A valider », les requêtes Power Query sélectionnent uniquement les fichiers validés par la direction internationale.
Maintenant que toutes mes données sont présentes dans mon modèle Tabulaire Power Pivot, comment puis je planifier le rafraîchissement des données ?
Office 365 Power BI Refresh
Depuis la galerie des rapports et documents Power BI il est possible de configurer le rafraîchissement automatique des données d’un modèle. Celui-ci peut rafraîchir tout ou partie des sources de données, la fréquence des rafraîchissements peut être quotidien ou hebdomadaire et à une heure précise de la journée.
Plus d’informations : Schedule data refresh for workbooks in Power BI for Office 365, Technet Forum
Voici les différents Settings disponibles :
Après un premier test, voici l’erreur reçue par mail :
Et oui !!! Le Schedule Data Refresh dans Power BI permet d’actualiser des sources de données tels que Windows Azure SQL Database, OData et SQL Server lorsqu’elles sont configurées dans Power Pivot et non dans Power Query.
Aujourd’hui, il n’est pas possible de rafraîchir des requêtes Power Query 🙁
Le produit évolue vite, j’espère donc voir cette amélioration implémentée rapidement mais en attendant comment satisfaire les exigences de mon client ?
Je n’ai pas encore de réponse bien définie, j’ai cependant identifié plusieurs solutions de contournement :
- Demander au client d’ouvrir le Workbook une fois par mois et rafraîchir les sources de données manuellement !
- Utiliser une machine virtuelle pour exécuter une tache planifiée qui se connecte à SharePoint et actualise le Workbook
- Utiliser une machine virtuelle pour exécuter un Job SSIS rafraîchissant le Workbook via une tache Codeplex
Je ne suis pas pleinement satisfait par ces différentes solutions, en effet je n’ose pas voir la tête du client lorsqu’il faudra lui annoncer que le rafraîchissement s’effectue manuellement !!! Les deux autres solutions ont comme défaut la nécessité d’avoir une machine virtuelle exécutant une tache planifiée ce qui engendre des coûts d’infrastructure non envisagée !
Refresh Method
La solution de contournement s’appuie sur la méthode workbook.RefreshAll() permettant d’actualiser toutes les données externes d’un classeur Excel.
Le job doit être planifié par une tache Windows, voici son code :
private static void PVRefresh(string excelFilePath) {
Console.WriteLine("File Name :" + excelFilePath);
Application MyExcel = new Application();
try {
MyExcel.Visible = false;
MyExcel.DisplayAlerts = false;
Console.WriteLine("1. Open Excel");
Workbook myWB = MyExcel.Workbooks.Open(excelFilePath);
Console.WriteLine("2. Refresh Excel");
myWB.RefreshAll();
Console.WriteLine("3. Save Excel");
myWB.Save();
Console.WriteLine("4. Close Excel");
myWB.Close();
}
catch (Exception ex1) {
throw ex1;
}
}
SSIS Excel Refresh Task
La tache SSIS Excel Refresh permet de rafraîchir l’ensemble des données d’un classeur Excel.
Le composant ne renvoie pas de résultat, un timeout permet donc de définir le délai d’attente en fonction de la taille du fichier.
Pour le télécharger : https://ssisexcelrefresh.codeplex.com/
Après avoir mappé la librairie SharePoint à mon ordinateur, je peux y accéder via un chemin d’accès universel (UNC Path Naming for files stored on SharePoint) et donc exécuter simplement la tache SSIS sur celui ci.
Très bon article ! Ne pas pouvoir rafraîchir les requêtes Power Query depuis SharePoint est vraiment un manque à mon sens … en espérant qu’une mise à jour couvre ce manque sous peu !