Excel & Real Time Analysis

 

Voici un petit article sur une problématique connue : Comment puis-je rafraichir automatiquement un rapport dans Excel ?

Voici à travers un exemple, préparé pour une démonstration #JSS2014 comment rafraichir automatiquement un rapport Power View dans Excel à partir de données situées dans une base de données Azure SQL Database.

 

Les données

Les données sont issues de capteurs de températures générés. Ils envoient au service Event Hub des évènements concernant 10 thermomètres connectés, les évènements sont alors procéssés et agrégés par Azure Stream Analytics qui se charge de les sauvegarder dans une base SQL Database :

 

Voici à quoi ressemblent les données dans SQL Database :

 

Power Query

Power Query, l’ETL End-User est en charge de rapatrier les données issues de la base de données SQL Database :

 

Power Pivot

Power Pivot, l’outil de création de cubes tabulaires End-User est en charge d’apporter une couche sémantique aux données :

 

Visual Basic for Application VBA

Création de 2 boutons, le bouton 1 aura pour charge de rafraichir le Workbook actif toute les 5 secondes si la valeur dans le champ A1 est égale à 0. Le bouton 2 aura pour charge de remplacer la valeur du champ A1 par 1 ce qui aura pour effet d’arrêter le rafraichissement automatique.

Sub Buton1()
    Range("a1").Value = 0
            
    Do While True
        ActiveWorkbook.RefreshAll
        ActiveWorkbook.Connections.Item(1).Refresh
        x = Timer()
        Do While x + 5 > Timer()
            DoEvents
        Loop
        If Range("a1").Value = 1 Then GoTo quitnow
        
    Loop
        
quitnow:
End Sub

Sub Buton2()
    Range("a1").Value = 1
End Sub

Une autre version :

Dim SchedRecalc As Date

Sub StartTime()
    SchedRecalc = Now + TimeValue("00:00:5")
    Application.OnTime SchedRecalc, "UpdatePowerQueries"
End Sub

Public Sub UpdatePowerQueries()
    ActiveWorkbook.Model.Refresh
    Call StartTime
End Sub

Sub EndTime()
    On Error Resume Next
    Application.OnTime EarliestTime:=SchedRecalc, Procedure:="UpdatePowerQueries", Schedule:=False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    EndTime
End Sub

Résultat :

 

Résultat dans Power View