Excel (Power Pivot) fájlok időzített frissítése

A múltkori cikkben megnéztük, hogy hogyan lehet egy Excel dashboadot megosztani böngészőn keresztül úgy, hogy interaktív maradjon. Most megnézzük hogyan tudunk gondoskodni az Excel fájlok automatikus (időzített) frissítéséről. 

Ahhoz, hogy a Power Pivotos Excel fájlunkat be tudjuk ütemezetten frissíteni (mondjuk minden nap, hajnali 5-kor), mindenképpen szükségünk lesz egy olyan szoftverre, amelyik nézi az órát, és ha elérkezett az adatfrissítés ideje, akkor elindítja azt.

Asztali környezetben erre nem ad megoldást a Microsoft, de felhős környezetben igen. Ha a Power Pivotos fájlunkat feltöltjük a OneDrive-ba, akkor a Power BI ütemezője képes lesz befrissíteni a dashboardjainkat. Így tehát biztosítani tudjuk azt, hogy

1. az Excel dashboardjaink interaktívan elérhetők legyenek böngészőből (erről szólt a múltkori cikk) és
2. az Excel fájlok automatikusan befrissüljenek mondjuk minden nap hajnali 5-kor (erről szól a mostani cikk)

Ahhoz tehát, hogy ütemezetten tudjuk frissíteni a Power Pivotos dashboardjainkat, először fel kell tölteni az Excel fájlt a OneDrive-ba. Így azonban a dashboardok vállalati tűzfalon kívülre kerülnek így előáll az a probléma, hogy a forrásadatok a tűzfalon belül, a dashboard és az ütemező a tűzfalon kívülre kerül. A tűzfal kintről befelé átjárhatatlan, így ha tűzfalon kívülről indítjuk az adatfrissítést (márpedig a Power BI ütemezője a tűzfalon kívül fut) akkor esélytelen, hogy az Excelben tárolt adatainkat be tudjuk frissíteni.

Két dolgot tehetünk:

1. Kitesszük a forrásadatainkat a tűzfalon kívülre (pl feltoljuk őket a OneDrive-ba, SQL Azure-ba, stb)
2. Telepítünk egy úgynevezett Gateway-t, amely megoldja a titkos kommunikációt a tűzfalon belül és kívül található szoftverek között.

Járjuk körbe mindkét alternatívát.

Minden forrásadat menjen a felhőbe

Ha minden forrásadatot feltolunk a Microsoft felhőjébe, akkor megoldódnak a tűzfal problémáink, hiszen így mind a dashboard, mind az adat, mind az ütemező a felhőbe kerül, és így a frissítéshez nem kell átjönni a tűzfalon. Ez Excel, vagy fájl alapú forrásoknál könnyen kivitelezhető, de adatbázisok esetén már korántsem egyszerű.

Gateway telepítése

Ha az adatok tűzfal mögött (saját gépen, vagy a vállalati hálózaton) vannak, akkor a tűzfalon belül az IT-nak telepítenie kell egy úgynevezett Gateway-t, amely megoldja a tűzfalon keresztüli kommunikációt.

A felhőben futó ütemező ugyanis nem lát be a tűzfalon keresztül, nem kezdeményezi az adatfrissítést sem. Csak egy listát vezet arról, hogy mikor melyik dashboardnak kell frissülnie és a tűzfalon belül telepített Gateway lesz az, aki erre a listára időről-időre ránéz, és ha talál frissítendőt, akkor áthozza a futtatandó lekérdezést, azt lefuttatja és a lekérdezés eredményét visszaküldi. Így tudták megoldani, hogy ne kelljen semmilyen bejövő portot nyitni a tűzfalon. A kommunikáció ugyanis így mindig csak kifelé irányú, és mindig mi (Gateway) kezdeményezzük.

Ütemezett adatfrissítéskor - ha a forrásadatok a vállalati tűzfalon belül vannak - a következő folyamatok zajlanak:

• A tűzfalon belül telepített gateway lekérdezi a felhőből, hogy melyik dashboardnak mikor kell frissülnie
• Ha talál olyan dashboardot, aminek frissülnie kell akkor
  • Lekéri az adatfrissítéshez szükséges felhasználó nevét
  • Lekéri az adatfrissítéshez szükséges Power Query lekérdezés szövegét
  • azt lefuttatja a felhasználó nevében
  • és a lekérdezés eredményét visszapumpálja a tűzfalon túlra

Tehát összességében mi vagyunk azok akik az adatokat feltöltik. A felhő csak a menetrendet tárolja, minden adatfrissítés tűzfalon belülről indul. Kívülről úgy néz ki mintha a Power BI indítaná a frissítést, de a valóságban mi indítjuk.

Az időzítés beállítása

Ha telepítettük a Gateway-t vagy minden forrásadatunk a OneDrive-on van akkor kezdődhet az időzítés beállítása.

• Az első lépés a Power Pivotos Excel fájl felmásolása a OneDrive-ra
• Excel fájl megnyitása (OneDrive-ról! Hiába szinkronizált a helyi meghajtó a OneDrive-val, ha helyi gépről nyitjuk meg a fájlt, akkor nem fogja tudni visszafrissíteni a Power BI a fájlt. 
• Ezt követően a Power Pivotos Excel fájlt publikálni kell a Power BI-ba (Fájl menü --> közzététel --> Power BI)
• Majd az Upload to Power BI opciót kell választani,  
• és végül Power BI-ban az Excel fájl neve mellett a 3 pöttyre kattintva be lehet állítani a frissítés időpontját, gyakoriságát

És voilá, a Power Pivotos Excel dashboardjaink automatikusan (időzítetten) frissülnek... 

További infó:

• Publish to Power BI from Microsoft Excel
• Refresh a dataset created from an Excel workbook on OneDrive, or SharePoint Online