PI Services

Dans cet article, un exemple de modification customisé des champs d’une colonne en fonction de la valeur d’une autre colonne.

Dans cet exemple, celui d’une liste d’utilisateurs et de leur pays, on souhaite ajouter un préfixe au nom de l’utilisateur, en fonction de leur pays.

On importe les données (dans notre exemple, celui d’un fichier Texte/CSV)

Une fois les données importées, on sélectionne Transformer les données

On sélectionne Utiliser la première ligne pour les en-têtes pour changer le nom des colonnes 

On sélectionne Editeur avancé pour afficher la requête complète

AVANT :

let

    source = csv.document(file.contents("c:\users\cjourdan\documents\test_data.csv"),[delimiter=";", columns=2, encoding=1252, quotestyle=quotestyle.none]),

    #"type modifie" = table.transformcolumntypes(source,{{"column1", type text}, {"column2", type text}}),

    #"en-tetes promus" = table.promoteheaders(#"type modifie", [promoteallscalars=true]),

    #"type modifie1" = table.transformcolumntypes(#"en-tetes promus",{{"name", type text}, {"country", type text}})

 in

    #"type modifié1"

On ajoute à la requête les lignes ci-dessous

APRES :

Requête complète :

let

    Source = Csv.Document(File.Contents("C:\Users\cjourdan\Documents\Test_Data.csv"),[Delimiter=";", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),

    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),

    #"En-têtes promus" = Table.PromoteHeaders(#"Type modifié", [PromoteAllScalars=true]),

    #"Type modifié1" = Table.TransformColumnTypes(#"En-têtes promus",{{"Name", type text}, {"Country", type text}}),

    #"Custo Replace value" = Table.ReplaceValue(#"Type modifié1" ,each [Name],each

        if ([Country]="United State") then "US_" & Text.From([Name])

        else if ([Country]="France") then "FR_" & Text.From([Name])

        else if ([Country]="Brazil") then "BR_" & Text.From([Name])

        else if ([Country]="England") then "UK_" & Text.From([Name])

        else if ([Country]="Germany") then "GER_" & Text.From([Name])

        else if ([Country]="Spain") then "SP_" & Text.From([Name])

        else if ([Country]="Italia") then "IT_" & Text.From([Name])

        else if ([Country]="Japan") then "JP_" & Text.From([Name])

        else if ([Country]="Norway") then "NW_" & Text.From([Name])

        else if ([Country]="Russia") then "RU_" & Text.From([Name])

        else [Name],Replacer.ReplaceValue,{"Name"})

in

    #"Custo Replace value"

Apres application, les noms sont modifiés

Derrière le terme un peu technique "Coloration conditionnel" se cache simplement le fait, dans un dashboard PowerBI d'afficher un changement de couleur dans le champ d'un tableau, en fonction de condition.

En plus des fonctions natives de mise en forme dans PowerBI Desktop, il est possible de creer une mesure (code DAX) , avec des conditions, que l'on va utiliser pour afficher une valeur dans un champ. Et cette valeur peut etre un code couleur.

Dans l'exemple du code ci-dessous, on a une application "MyApp" pour laquelle, dans PowerBI nous avons trois datasets (deux correspondrait par exemple a deux tables de la base de donnée associée a "MyApp", et un troisième qui est une connexion vers un outil qui reference la criticité de mes machines (ex: outil de gestion des asset ou cmdb)):

- MyAppAgents, qui contiens des infos sur les agents de mon application

- MyAppVersions, qui contiens un historique des numeros de version de mon application

-  MyServers qui est donc la table qui liste la criticité des mes serveurs.

Objectif: On veux dans un dashboard, colorer un champ en rouge ou jaune (exemple, le nom du serveur), pour lequel la version ((MyAppAgents[MyApp_agent_version]) serai differente de la dernière version, ET dont le serveur serait un serveur de Production ou de test.

Color_MyApp_agent = 

   VAR Result1 = SELECTEDVALUE(MyAppAgents[MyApp_agent_version])
   VAR Result2 = SELECTEDVALUE(MyAppVersions[MyApp_Last_version_available])
   VAR Result3 = SELECTEDVALUE(MyServers[Server_Criticity])

-- SI MyApp_agent EST DIFFERENT DE MyApp_Last_version_available ET SI Server_Criticity = "PROD" alors on renvoi "CRITICAL"
   VAR Critical = IF(AND(Result1<>Result2, Result3="PROD"),"CRITICAL")
   
-- SI MyApp_agent EST DIFFERENT DE MyApp_Last_version_available ET SI Server_Criticity <> "TEST" alors on renvoi "WARNING"  
   VAR Warning = IF(AND(Result1<>Result2, Result3<>"TEST"),"WARNING")
   
-- SI MyApp_agent EST EGALE A MyApp_Last_version_available   
   VAR OK = IF(Result1=Result2,"OK")

   
 -- SELON LE CAS ON RENVOI UN CODE COULEUR DIFFERENT
RETURN
SWITCH(
True(),
 Critical="CRITICAL" , "#FEA19E",
Warning="WARNING" , "#F9FF28",
 OK="OK" , "#96FF93" 
)

On crée donc une mesure (measure) dans laquelle on va coller le code ci-dessus

Apres validation la mesure apparait dans le dataset ou elle a été crée

On selectionne ensuite le champ que l'on veut colorer puis Conditional formatting/Background color

On selectionne Format by "Field Value", et on va venir selectionner notre mesure (Color_MyApp_agent)

Apres validation, Les valeurs de la colonne seront formatée selon la condition rencontrée (Les code couleurs '#FEA19E' peuvent bien sur etre modifié dans le code pour choisir une autre coloration):

Problématique: Nous voulons a travers une requete Dax compter un nombre de machines possedant une application 'MyApp' dans une table nommée MY_HOSTS_AND_APPS

Mais une partie des machines n'a pas cette info renseignée dans la colonne [AppName]. Cette info est inscrite sous la forme d'un texte libre dans une autre colonne [HostUsage]

Il est possible dans ce cas d'utiliser un OR (preferer l'alias '||') dans la clause FILTER.

On compte donc (DISTINCTCOUNT), les HostName où [AppName] est égale a "MyApp" ou bien (||) la chaine "MyApp" est presente dans [HostUsage]  (SEARCH)

WithMyApp = 
VAR Result = CALCULATE(
    DISTINCTCOUNT(MY_HOSTS_AND_APPS[HostName]),
        FILTER(MY_HOSTS_AND_APPS,MY_HOSTS_AND_APPS[AppName]="MyApp"
        ||
        SEARCH("MyApp",MY_HOSTS_AND_APPS[HostUsage],1,0)
        )               
)
RETURN(
    IF (Result=BLANK(),0,Result))

Problématique :  Un champ texte apparenté au contenu d’un log, melange de texte et de chiffre, contiens une date que l’on veut récupérer.

• On veut récupérer la date de création (Creation Date) et la mettre dans une nouvelle colonne
• On remarque que la date est situé entre deux chaines de delimitation : ‘Creation Date : "’ et ‘"’

De ce fait on selectionne la colonne et dans le menu ‘Add Column’ on clique sur Extract/Text Between Delimiters

Dans le champ ‘Start delimiter’ on renseigne notre premiere chaine (ici Creation Date : ") (NB : attention aux espaces)

Dans le champ ‘End delimiter’ on renseigne notre deuxième chaine (ici ")

On valide.

Une colonne est crée automatiquement au format chaine (ABC) a la fin du tableau avec le nom ‘Text Between Delimiters’

On fait un clic droit sur la colonne et on selectionne Change Type\Date/Time/Timezone

Attention. Dans cet exemple la conversion fonctionne car le système detecte que le pattern de la chaine est de type Date/Time/Timezone.  Dans d’autres cas il peut être nécessaire de tester d’autres format et/ou bien de sélectionner a l’origine, une portion differente de texte dans la colonne d’origine.

La conversion est OK :

Il ne reste plus qu’a renommer la colonne de manière plus explicite. (Ex : Custo_Creation_Date)

Le script ci-dessous prend en paramètre un fichier contenant une liste de dashboard a exporter au format pbix.

#####################################################################
###### PwBI_Export_Dashboards.ps1 - EXPORT DE RAPPORTS EN PBIX ######
#####################################################################


<# 

    .SYNOPSIS 
        CONNEXION A L'API SQL POWERBI REPORTING SERVICES ET EXPORT DE UN OU PLUSIEURS DASHBOARD
 

    .PARAMETER  
        webServiceUrl : Url Racine du serveur de rapport
        CatalogUrl : Url du catalogue des items
        DashboardListFile : Fichier des noms de dashboard a exporter
        BackupFolder : Dossier d'export des dashboards
        LogFolder : Chemin du dossier où creer le log du script

 
    .EXAMPLE 
        .\PwBI_Export_Dashboards.ps1 -webServiceUrl "http://MyServer/Reports"  -DashboardListFile ".\PwBI_Export_Dashboards_List.txt" -BackupFolder ".\BACKUP" -LogFolder ".\BACKUP" 
     
#>


[CmdletBinding()]
param(
[Parameter(Mandatory,HelpMessage="Url Racine du serveur de rapport")]
[string]$webServiceUrl,

[Parameter(Mandatory=$false,HelpMessage="Url du catalogue des items")]
[string]$CatalogUrl = "$webServiceUrl/api/v2.0/CatalogItems",


[Parameter(Mandatory,
HelpMessage="Fichier des noms de dashboard a exporter")]
[ValidateScript({
if( -Not ($_ | Test-Path) ){
                throw "File does not exist"
            }
            return $true 
})]
[string]$DashboardListFile,

[Parameter(Mandatory,HelpMessage="Dossier d'export des dashboards")]
[string]$BackupFolder,

[Parameter(Mandatory,HelpMessage="Chemin du dossier où creer le log du script")]
[string]$LogFolder = "D:\Indicateurs_Securité"

)




# GET DASHBOARD LIST CONTENT
[array]$DashboardList = Get-Content -Path $DashboardListFile



# SCRIPT NAME
$ScriptName = "PwBI_Export_Dashboards.ps1"

# LogName = ScriptName without extension
$Log = $ScriptName.Split('.')[0]

# GET CREDENTIALS TO CONNECT TO REPORT SERVER
$cred = $(Get-Credential -Credential "$env:USERDOMAIN\$env:USERNAME")



function Write-Log 
{ 
    <# 
    .SYNOPSIS 
        This function creates or appends a line to a log file. 
 
    .PARAMETER  Message 
        The message parameter is the log message you'd like to record to the log file. 
 
    .EXAMPLE 
        PS C:\> Write-Log -Message 'Value1' 
        This example shows how to call the Write-Log function with named parameters. 
    #> 
    [CmdletBinding()] 
    param ( 
        [Parameter(Mandatory)] 
        [string]$Message,
        [Parameter(Mandatory)] 
        [string]$LogPath, 
        [Parameter(Mandatory)] 
        [string]$LogName
        
    ) 
     
    try 
    { 
        $DateTime = Get-Date -Format ‘MM-dd-yy HH:mm:ss’ 
        Add-Content -Value "$DateTime - $Message" -Path "$LogPath\$LogName.log" 
    } 
    catch 
    { 
        Write-Error $_.Exception.Message 
    } 
} 




# TABLEAU DES CATALOG ITEMS
$Message = "Recuperation du catalogue des items..."
write-host $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log


            try
            {
            $CatalogItems = Invoke-RestMethod -Uri $CatalogUrl -ContentType 'application/json' -UseDefaultCredentials -Method get
            }
            catch
            {
            $Message = "Error during query of Catalog Items"
            Write-Log -Message "$Message - $($Error[0].Exception)" -LogPath $LogFolder -LogName $Log
            Write-Host -F Yellow $Message
            exit 1
            }



# COMPARAISON DE $CatalogItems ET $ReportList POUR DETERMINER LA LISTE DES DASHBOARDS A EXPORTER

$FoundReport = $CatalogItems.value | Where-Object {$_.name -in $DashboardList}

if (!($FoundReport))
{
$Message = "The required Dashboards have not been found in the catalog of the report server - END OF SCRIPT"
write-host -f Yellow $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
Exit 1
}



# EXPORT DES DASHBOARDS
try
{

    foreach ($Report in $FoundReport)
    {
    $url = "$CatalogUrl($($Report.Id))/Content/`$value"
    Invoke-WebRequest -UseDefaultCredentials -Uri $url -OutFile "$BackupFolder\$($Report.name).pbix"
    }

}
catch
{
$Message = "KO - The required Dashboards have not been found in the catalog of the report server - END OF SCRIPT"
write-host -f Yellow $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
Exit 1
}


$Message = "OK - The required Dashboards have been Exported to $BackupFolder - END OF SCRIPT"
write-host -f Green $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log
Exit 0

Le script ci-dessous se connecte sur l'api de Reporting Services (Power BI) pour executer le rafraichissement des données d'un rapport spécifique.

###### PWBI_REFRESH_PLAN.PS1 ######

<# 

    .SYNOPSIS 
        CONNEXION A L'API SQL POWERBI REPORTING SERVICES ET EXECUTION DU PLAN DE RAFRAICHISSEMENT D'UN RAPPORT SPECIFIQUE
 

    .PARAMETER  
        webServiceUrl : Url Racine du serveur de rapport
        CatalogUrl : Url du catalogue des items
        ReportFolderPath : Chemin du dossier contenant le rapport a rafraichir (NB: apres la racine du serveur de rapport)
        ReportName : Nom du dashboard a rafraichir
        RefreshPlan : nom du plan de Refresh
        LogFolder : Chemin du dossier où creer le log du script

 
    .EXAMPLE 
        .\PwBI_Refresh_Plan.ps1 -webServiceUrl "http://MyServer/Reports" -ReportFolderPath "/MySpecFolder/" -ReportName "MySpecDashboard" -RefreshPlan "Refresh_Every_2H" -LogFolder "C:\Temp"   
     
#>


[CmdletBinding()]
param(
[Parameter(Mandatory,HelpMessage="Url Racine du serveur de rapport")]
[string]$webServiceUrl,

[Parameter(Mandatory=$false,HelpMessage="Url du catalogue des items")]
[string]$CatalogUrl = "$webServiceUrl/api/v2.0/CatalogItems",

[Parameter(Mandatory,HelpMessage="Chemin du dossier contenant le rapport a rafraichir (NB: apres la racine du serveur de rapport)")]
[string]$ReportFolderPath,

[Parameter(Mandatory,HelpMessage="Nom du dashboard a rafraichir")]
[string]$ReportName,

[Parameter(Mandatory,HelpMessage="nom du plan de Refresh")]
[string]$RefreshPlan,

[Parameter(Mandatory,HelpMessage="Chemin du dossier où creer le log du script")]
[string]$LogFolder = "D:\Indicateurs_Securité"

)

# FULL REPORT PATH
$FullPath = "`'$ReportFolderPath$ReportName`'"  # Backtick to escape "'" character.

# REFRESH PLAN URL
$RefreshPlanUrl = "$webServiceUrl/api/v2.0/PowerBIReports(Path=$FullPath)/CacheRefreshPlans"


# SCRIPT NAME
$ScriptName = "PwBI_Refresh_Plan.ps1"

# LogName = ScriptName without extension
$Log = $ScriptName.Split('.')[0]

# GET CREDENTIALS TO CONNECT TO REPORT SERVER
$cred = $(Get-Credential -Credential "$env:USERDOMAIN\$env:USERNAME")



function Write-Log 
{ 
    <# 
    .SYNOPSIS 
        This function creates or appends a line to a log file. 
 
    .PARAMETER  Message 
        The message parameter is the log message you'd like to record to the log file. 
 
    .EXAMPLE 
        PS C:\> Write-Log -Message 'Value1' 
        This example shows how to call the Write-Log function with named parameters. 
    #> 
    [CmdletBinding()] 
    param ( 
        [Parameter(Mandatory)] 
        [string]$Message,
        [Parameter(Mandatory)] 
        [string]$LogPath, 
        [Parameter(Mandatory)] 
        [string]$LogName
        
    ) 
     
    try 
    { 
        $DateTime = Get-Date -Format ‘MM-dd-yy HH:mm:ss’ 
        Add-Content -Value "$DateTime - $Message" -Path "$LogPath\$LogName.log" 
    } 
    catch 
    { 
        Write-Error $_.Exception.Message 
    } 
} 



            




# TABLEAU DES CATALOG ITEMS
$Message = "Recuperation du catalogue des items..."
write-host $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log


            try
            {
            $CatalogItems = Invoke-RestMethod -Uri $CatalogUrl -ContentType 'application/json' -UseDefaultCredentials -Method get
            }
            catch
            {
            $Message = "Error during query of Catalog Items"
            Write-Log -Message "$Message - $($Error[0].Exception)" -LogPath $LogFolder -LogName $Log
            Write-Host -F Yellow $Message
            exit 1
            }




# RECUPERATION DU RAPPORT SPECIFIQUE
$Message = "Recuperation du rapport $ReportName"
write-host $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log


            $CatalogItem = $CatalogItems.value | Where-Object Name -eq $ReportName
            if (!($CatalogItem))
            {
            $Message = "Unable to find $ReportName"
            Write-Log -Message "$Message - $($Error[0].Exception)" -LogPath $LogFolder -LogName $Log
            Write-Host -F Yellow $Message
            exit 1
            }
 


# RECUPERATION DU PLAN DE RAFRAICHISSEMENT
$Message = "Recuperation du plan de rafraichissement"
write-host $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log


            try
            {
            $refreshplans = 
            
            Invoke-RestMethod -Uri $RefreshPlanUrl -Method get -UseDefaultCredentials
            
            }
            catch
            {
            $Message = "Error during query of Refresh Plan"
            Write-Log -Message "$Message - $($Error[0].Exception)" -LogPath $LogFolder -LogName $Log
            Write-Host -F Yellow $Message
            exit 1
            }



# RECUPERATION DE L'ID DU PLAN DE RAFRAICHISSEMENT
$refreshplanId = $($refreshplans | Where-Object {$_.value.Description -eq $refreshplan}).value.Id




# EXECUTION DU PLAN DE RAFRAICHISSEMENT
$Message = "Execution du plan de rafraichissement"
write-host $Message
Write-Log -Message $Message -LogPath $LogFolder -LogName $Log


            try
            {
            
            Invoke-RestMethod -Uri "$webServiceUrl/api/v2.0/CacheRefreshPlans($refreshplanId)/Model.Execute" -Method post -UseDefaultCredentials
            
            }
            catch
            {
            $Message = "Error during execution of Refresh Plan"
            Write-Log -Message "$Message - $($Error[0].Exception)" -LogPath $LogFolder -LogName $Log
            Write-Host -F Yellow $Message
            exit 1
            }

Dans Power BI, l’info du dernier rafraichissement d’un dashboard ne fait pas l’objet d’une fonctionnalité directe identifiée comme telle.

Il s’agit d’une info bien sur importante. Pour l’implémenter, la procédure n’est pas très compliquée.

Dans l’editeur de requete, creer une nouvelle requete vide.

Renommer la requête avec un nom explicite.

Cliquer sur Advanced Editor pour éditer la nouvelle requête.

Ecrire la requete comme ci-dessus (Source = #table(type table[Date Last Refreshed=datetime], {{DateTime.LocalNow()}})

La valeur de la date et heure actuelle s’affiche.

Valider en cliquant Close & Apply

Sur la page du dashboard dans la liste des champs (Fields) Faite un clic droit et selectionner New measure

Dans la barre éditeur de la mesure, tapez le texte ci-dessus (Last Refreshed = VALUES(‘Date Last Refreshed’[Date Last Refreshed])

la mesure est crée et va pouvoir etre utilisé dans une visualization.

Selectionner une Visualization de type Card

Associer a cette visualization la mesure crée.

Voila. Vous pouvez bien sur changer l’apparence de la visualization (par ex la couleur de fond) dans ses propriétés.