Az excel power lekérdezés és a mysql használatával kapcsolatos buktatók a jelentéskészítés automatizálásához

Az Excel Power Query és a MySQL használatával kapcsolatos hibák a + 9-es automatizálására

• 06.01.16 05:19 •
• kpimaker •
• # 274615 •
• Habrahabr •
• 0 •
• 8400

- ugyanaz, mint Forbes, csak jobb.

Ha a jelentéseket frissíteni kell hetente vagy akár naponta, akkor ez a folyamat nagyon stresszes lesz még a legtájékosabb esetekben is. Az Excel Power Query bővítmény és az adatok írása a MySQL segítségével a jelentések többségét egyszerűen kattintva frissítheti:
1. Az összes forrásból származó adatokat az SQL-lekérdezésekből rendszeres Excel táblákba importálják.
2. Még egy nagy adatbázisból is csak az adatok egy kis részét írhatja az Excel-be (például a kívánt dátumtartomány összesített értékei, csak a szükséges oszlopok csoportosításával).
3. A jelentés módosítása egyszerűen az SQL lekérdezés megváltoztatásával végezhető el. Ezután elkészítjük a szükséges jelentést a szabványos Excel eszközök segítségével.

Ebben a cikkben megmutatom, hogyan konfigurálhatom és automatikusan feltölthetem az egyszerű MySQL adatbázisokat (például a Yandex Metrics összes kulcsszavának statisztikáinak feltöltésével), majd egy kattintással frissítheted a jelentéseket Excel-ben a Power Query bővítmény segítségével. A Power lekérdezésnek nagyon furcsa a munkája az SQL-lekérdezések (különösen a dinamikus lekérdezések) készítése során, amelyet a cikk második részében tárgyalunk.

A MySQL (vagy bármely más népszerű adatbázis) kiválasztása meglehetősen nyilvánvaló - ingyenes, viszonylag egyszerű, képes meglehetősen nagy adatbázisokkal dolgozni technikai trükkök nélkül. Példaként az Amazon Web Services-t használjuk: olcsó (a legtöbb esetben a használt példány 12 hónapon belül ingyenes lesz).

Kezdjük (ha már rendelkezik kész adatbázisokkal rendelkező adatbázisokkal, akkor azonnal megy az Excel részhez):
1. Regisztráljon az AWS-vel (ha még nincs számviteli), futtassa a t2.micro legegyszerűbb példányát, és menjen hozzá SSH-n keresztül. Láthatja a rövid utasításokat a múltban a habrahabr.ru/post/265383 weboldalon. Kérjük, vegye figyelembe, hogy az Amazon Linux AMI első fokozatára van szükségünk. Olyan szabályokat kell beállítani, amelyek lehetővé teszik a példány elérését a szükséges portokon:

2. Végre egy sor parancsot docs.aws.amazon.com/AWSEC2/latest/UserGuide/ec2-ug.pdf dokumentációt. Szükségünk van a "Tutorial: LAMP Web Server telepítése az Amazon Linuxon" fejezetre. Megjegyzés: a jelszó, hogy felvették a teljesítmény «sudo mysql_secure_installation» csapat. A kényelem érdekében telepítse a phpMyAdmin-ot a fejezet végén leírtak szerint. Ha a copy-paste a dokumentum vonal «sudo sed -i -e 's / 127.0.0.1 / your_ip_address / g' / etc / ht tpd / conf.d / phpmyadmin.conf», akkor veszi észre, hogy néha, amikor a másolás «httpd "Egy extra rés jelenik meg.

Ezen műveletek után nyissa meg az ilyen oldalt a példányban:

3. A felhasználó gyökér alá és a konfiguráció során megadott jelszó alá kerülünk. Az adatbázishoz "kívülről" történő eléréséhez (vagyis Excel-ből) szükségünk van egy másik felhasználóra, mint a gyökérre. Indítsa el a phpMyAdmin felületen a Felhasználók -> Felhasználó hozzáadása menüben. Adja hozzá a felhasználói statisztikákat, állítsa be a jelszót és rendelje hozzá a SELECT és INSERT kiváltságokat. Összesen kapunk:

4. Most hozza létre az adatbázisadatokat:

5. Ebben a példában feltöltjük az adatbázist a Yandex Metrics kulcsszavai látogatói statisztikáival. Ehhez létre kell hozni egy táblázatot seo-nak (vegye figyelembe, hogy az id oszlopot az A_I (automatikus növekmény) opcióval kell jelölni):

PHP kód az adatok feltöltéséhez Yandex Metrics

Ha hiba történik az adatbázishoz való csatlakozáskor, akkor a konzolban fog megjelenni, és a végrehajtás megszakad. Sikeres végrehajtás esetén a kiválasztott időszak kulcsszavainak statisztikáját kapjuk:

Kitűnő az adatok fogadása. Lássuk, hogyan lehet Excel-ben beszerezni őket.

Power Query használatával adatokat tölthet fel az Excel programba
A Power Query olyan bővítmény, amely kiterjeszti az Excel adatmentési képességét. Letöltheti itt: www.microsoft.com/en-us/download/details.aspx?id=39379. A MySQL használatához szükség lehet a MySQL Connector és a Visual Studio alkalmazására (felajánlott, ha telepítéskor elosztás történik).

1. A telepítés után válassza ki a MySQL-t:

2. Adja meg az adatbázisban a példányunk azonosítóját (ahogyan az a scriptben) ec2-. compute.amazonaws.com. Adatbázisadatok. Belépéshez adja meg az "Adatbázis" elemet:

3. A megnyíló ablakban kattintson duplán a seo asztalra és kap:

Ebben az ablakban a lekérdezéseket az oszlopok és a sorok számának módosításával kezelheti. Ha az adatbázis kicsi, akkor működik. Ha azonban az adatok mérete meghaladja a 20 MB-ot, az Excel a legtöbb számítógépen egyszerűen lefagy egy ilyen kérésről. Ezenkívül jó lenne módosítani a lekérdezési dátumokat vagy egyéb paramétereket.

Dinamikus lekérdezések a Power Query-ben a beépített nyelv MS msdn.microsoft.com/en-us/library/mt253322.aspx segítségével. Azonban a kérések rendkívül instabilak a paraméterek megváltoztatásában. A kérés "állandó" maradása érdekében a következőket tesszük:

1. Először összeállítunk egy táblázatot, amelyben meghatározzuk a szükséges paramétereket. Példánkban ez a kirakodás dátuma. Az értékekkel rendelkező cellák formátuma jobban beállítható a teszteléshez, mert az Excel úgy tetszik, hogy a sejtek formátumát a saját belátása szerint megváltoztatja:

2. Hozzon létre egy Power Query lekérdezést "Az asztaltól", amely egyszerűen megkétszerezi ezt a táblázatot:

3. A lekérdezési beállításoknál feltétlenül adja meg a második oszlop formátumát Szövegként, ellenkező esetben a következő SQL lekérdezés nem lesz helyes. Ezután kattintson a "Bezárás és letöltés" gombra.

Összesen egy Power Query lekérdezést kaptunk a normál táblázathoz, ahonnan a letöltés kezdetének és végének értéke megtörténik.

SQL lekérdezés végrehajtásához le kell tiltania az egyik beállítást: lépjen be a Beállítások és Beállítások -> Lekérdezési paraméterek -> Adatvédelem és válassza a "Jogosultsági szintek figyelmen kívül hagyása a lehetséges teljesítményjavításokhoz". Kattintson az OK gombra.

A paraméterek és startDate endDate megadott értékek a táblázatban bekezdés 3. Amikor a rendszer kéri, „Hogy ezt, a saját lekérdezést az adatbázis szükséges engedélyt” klikk „Engedélyek szerkesztése”, ellenőrizze, hogy minden paraméter helyesen meghúzni és lekérdezések futtatásához. Az SQL lekérdezésből kapott válasz a szokásos Excel képletekkel feldolgozható.

5. Fontos! Amikor a következő alkalommal frissíti a feltöltést, ezt a következő módon kell elvégeznie (a mások valahogy hibát jeleznek):
- változtassa meg a dátumokat a táblázatban az 1. tételből
- menjen a Data -> Connections menübe, és kattintson az "Összes frissítése" linkre:

Ebben az esetben minden lekérdezés végrehajtása helyesen történik, és a jelentések automatikusan frissülnek. Összességében a jelentés frissítéséhez csak a lekérdezési paramétereket kell módosítania, és kattintson az Összes frissítése gombra.

Segíthet és pénzt küldhet a fejlesztéshez