BizTalk – BAM : Engrenages dans Power BI pour EXCEL

Comment exploiter et mettre en forme aisément les données de votre BAM ?

PRÉCÉDEMMENT…

Dans la première saison (Le BAM sans le Boum), on a mis en place le BAM pour alimenter les tables génériques qui contiennent les informations fonctionnelles et techniques pertinentes pour chaque flux.

Les informations collectées pour chaque demi-flux entrant sont :

• Start_Date
• End_Date
• Object_Type
• InputHalfFlow
• Action
• Object_ TechnicalId
• Object_FunctionalId
• Error_Message

Dans notre exemple, elles sont identiques aux informations  collectées pour chaque demi-flux sortant :

• Start_Date
• End_Date
• Object_Type
• OutputHalfFlow
• Action
• Object_ TechnicalId
• Object_FunctionalId
• Error_Message

SAISON 2

Dans cette saison, nous allons rendre ces données exploitables par n’importe quel intervenant via le module PowerBI pour Excel.

Voici le mode opératoire :

Episode 1

Une vue va contenir toutes les informations que l’on veut avoir dans nos futurs graphiques.
Pour notre cas, une catégorisation par erreurs fonctionnelles ou techniques est très intéressante. Pour cela, on va créer deux tables de références afin de répertorier toutes les erreurs possibles et les classer par type (fonctionnel, technique ou autre). Pour chaque erreur une chaîne de caractères à rechercher dans la colonne Error_Message des tables du BAM sera définie.
Ceci est en fait une base de connaissances qui sera enrichie au fur et à mesure du projet.

Ainsi la vue à créer (Bam_HALF_FLOW_Extract dans notre cas) fera une jointure sur ces tables et sur le message d’erreur dans les vues des activités du BAM :

Episode 2

La vue Bam_HALF_FLOW_Extract sera la base de notre fichier d’export à consommer par Excel. Un job va exécuter une commande bcp afin de générer des fichiers CSV à intervalles réguliers.
Dans notre cas, la commande exécutée par le job sera celle-là :

DECLARE @runtime AS varchar(14), @cmd varchar(500), @currentDate datetime
set @runtime =  convert(varchar(20),getdate(),112) + right('00' +convert(varchar(2),datepart(hh, getdate())),2) +right('00' +convert(varchar(2),datepart(mi, getdate())),2)+ right('00' +convert(varchar(2),datepart(ss, getdate())),2);
set @currentDate = getutcdate()
set @cmd='bcp "SELECT YearError,MonthError,DayError,HourError,MinuteError,SecondError,Notif_Application,HalfFlow,ReferenceErrorType,ReferenceErrorCategoryDescription,Object_Type,Object_Id, NbOccurence FROM BAMPrimaryImport.dbo.bam_HALF_FLOW_Extract where LastModified>= (SELECT HalfFlowExtractLastRun FROM BAMPrimaryImport.dbo.bam_HALF_FLOW_EXTRACT_LAST_RUN) order by ORD" queryout D:\BAM_Export\' + @runtime +'_bam_HALF_FLOW_EXTRACT.csv -c -w -t; -T -S'
exec master..xp_cmdshell @cmd

UPDATE BAMPrimaryImport.dbo.bam_HALF_FLOW_EXTRACT_LAST_RUN SET HalfFlowExtractLastRun = @currentDate

Avec le paramètre HalfFlowExtractLastRun, nous sommes sûrs d’avoir tous les événements du BAM.

Episode 3

Pour pouvoir utiliser PowerBI, on a besoin des modules pour Excel PowerQuery et PowerPivot.
Note : La version d’Excel utilisée dans cet exemple est 2013.

Le module PowerQuery est à télécharger et installer manuellement :

https://www.microsoft.com/fr-fr/download/details.aspx?id=39379&CorrelationId=85f847dd-369e-4417-b604-6a2f3c673084

Le module PowerPivot est déjà inclus dans Excel, il suffit de l’activer :

Nous sommes maintenant prêts pour créer une PowerQuery qui consomme les fichiers CSV générés.

Remarque : On pourrait brancher Powerquery directement sur la base de données, mais cela voudrait dire que toute personne voulant visualiser les graphes aura un accès direct à la base de données. Le choix des fichiers est plus sécurisé.

Via le menu POWER QUERY/From File/From Folder, on sélectionne le dossier dans lequel se trouvent les fichiers CSV créés :

En cliquant sur la double flèche de la colonne Content, les données des fichiers sont chargées.
C’est le moment de faire la transformation de ces données selon le besoin, par exemple, formatage des colonnes Date et Heure, et remplacement des valeurs nulles de la colonne erreurDescription par Succes, afin de pouvoir faire le tri dans les graphes plus facilement.

Une fois la transformation finie, ces lignes sont dirigées dans une feuille via le bouton « Close & Load » en haut à droite.
C’est là que PowerPivot prend le relais.
On sélectionne toutes les données de notre feuille pour créer le modèle via le menu PowerPivot/Ajouter au modèle de données.

Si certaines colonnes n’ont pas à être sur les graphes, ou une jointure à faire avec une autre source de données, c’est ici que cela va se faire. Dans notre exemple, nous avons tout ce qu’il nous faut.

Episode 4

Via le menu Tableau croisé dynamique/Deux graphiques (à l’horizontale) nous chargeons ce modèle dans Excel :

Il ne nous reste plus qu’à se familiariser avec filtres pour créer les représentations voulues dans Excel 😉 :

Bingo, l’énigme du BAM via PowerBI dans Excel est résolue !!

Tous les intervenants avec qui vous partagez ce fichier peuvent maintenant exploiter de manière aisée les données du BAM et créer leurs propres graphes.
Rendez-vous dans la prochaine saison pour des nouvelles aventures ! 🙂