Analýza kvality výroby podniku v automobilovém průmyslu

Hnedle ze začátku na rovinu. Chtělo se nám vytvořit projekt reálný. Takový, co by zanechal stopu a ukázal nám, jak to ve světě datové analýzy chodí. Přišla nám do cesty data mezinárodní firmy z automobilového průmyslu. Ta data byla výrobní, taková “echt tech”, celkem strukturovaná, vedená v živé databázi a bylo jich hodně. Je cca 1 milionů řádků denně hodně? No zkrátka, nám se zdálo, že ano a že taková nabídka se ve světě dat neodmítá…, a tak jsme po ní skočily. 🐸

Autovýroba? 🚗 Auta znám, auto mám, jo to by šlo... Mezinárodní firma? No ale to by bylo fajn - zanechat mezinárodní stopu! Proč nemířit vysoko, že jo 😍... Už nás vidím, jak jsme chváleny a opěvovány a přebíráme za zásluhy diplom ve stříbrném rámu. Nablejskanej, samejma autoritama podepsanej diplom !🏅Takovej skvost se mně osobně bude hodit nad schodiště, tam, kam bych si pověsila diplom z medicíny (kdybych nějakej měla) - tam nahoru, do pravýho rohu, tam by se hodil!

  1. Zadejte se! 👭

Jestli bychom měly povyprávět, jak se to stalo, že jsme se potkaly právě my dvě, referát by to byl velmi stručný - bylo to psem. A jak to tak u pejskařů bývá, od psa se hovor stočil ke koníčkům, k cestování… co člověk dělá se psem, co člověk dělá bez psa, co by člověk rád dělal… jaké má nejradši kafe… na co kouká na Netflixu… a data z výrobního podniku - ta by tě taky zajímala? 😎

Slovo dalo slovo a…


2. …a zadán byl projekt 👯‍♀️💻

Naším úkolem bylo vytvoření přelomového BI reportingu kvality výroby firmy v automobilovém průmyslu na základě výpočtu ukazatele FPY. Ten, jako jeden ze zásadních KPI ukazatelů, popisuje, jaké procento výrobků projde napoprvé výrobním procesem bez neshody.

Převedeme-li to do řeči běžného smrtelníka, naším úkolem bylo sledovat kvalitu výroby. Výstupem pak měl být pěkně názorný report v podobě dashboardů, který by užívali nejen kvalitáři výroby, ale víceméně kdokoliv napříč firmou tak, aby byl okamžitě a přehledně schopen reportovat, jak si jaká směna či linka (anebo skupina linek, výrobní projekt, výroba konkrétního produktu a tak mnohonásobně dále) za daný časový úsek stála. Požadované zobrazení FPY se opírá vždy o několik údajů, které budou diferencovat styl výpočtu a též zdrojovou oblast dat.

Naší zaručenou cestou do kvalitářského data analysis nebe by bylo poskytnutí podrobného infa o proběhlých chybách. S vidinou toho diplomu nad schodištěm na to kývnem, že není problém, všechno bude! 💪

Jde vám z toho hlava kolem? Nám taky! Následující GIF budiž toho důkazem! A to teprva začínáme!
Zdroj: Trickartt.com

3. Co nám bylo dáno? 🎁

K dispozici nám byly dva CSV soubory se zdrojovými daty, první soubor obsahoval údaje o výrobcích, které prošly linkou na první dobrou, ve druhém souboru se nacházelo info o výrobcích, které na své cestě do krabice k odeslání utrpěly nějakou újmu. K tomuto druhému souboru se pak pojila tabulka s podrobnějšími údaji o této újmě. Tabulky v realitě každým dnem bobtnají o 1 000 000 řádků dat, jedná se tedy o živá data. Data se v databázi uchovávají po dobu 20 dnů a poté se mažou.

Sloupce databáze nám poskytnuté vycházely z reálné databáze, ale data byla náhodně vygenerována.


4. Pasujeme se s tím… ✌️

Z logiky věci bylo jasné, že bude nutné provést zásadní agregaci dat. I tak se nám však zdálo nemožné poprat se s takovým množstvím – živým, nekonečně se množícím, neustále se valícím množstvím daaaat!!! 😱

Proto jsme se dohodly a rozhodly, že projekt rozdělíme na dvě části:

  1. v té první budeme pracovat na prototypu projektu se vzorkem dat
  2. a v té druhé uplatníme ověřený postup na živá data a reporting nasadíme tak, aby s ním ve firmě mohli hned začít pracovat.

Ono je nutné zmínit, že “náš klient”” pracuje s Oracle databází a preferuje PL/SQL skripty a Microstrategy vizualizaci. Prostě trošku jiný rybníček pro nás kačenky Keboola-Snowflakové, co se zakoukaly do Tableau. Došlo tedy k dalšímu upřesnění – na pilotu budeme pracovat s nástroji nám známými a dostupnými. A až pak budeme, již s plnou flotilou dat, táhnout hejno směr Oracle a Microstrategy. Kvák! 🦆🦆


5. Stále se s tím pasujeme... ✌️✌️

Na řadu přišlo seznamování se s daty:

  • s jejich rozsahem (kam oko nedohlédne)
  • s jejich formou (výrobní data, tedy téměř značka ideál, co se čištění týče)
  • a s informacemi, které nesou (AJAJ!!! 🤕)

Seznamujeme se se samotnou výrobou, zjišťujeme, co je term_id, co je wo_id, co je produktové číslo a co sériové. Zjišťujeme, za jakých podmínek vůbec mluvíme o FPY (First Pass Yield), kdy se jedná o SPY (Second Pass Yield), kdy se jedná o neúplná data a kdy je to vlastně ok, že toto info chybí. A kdy není ok, že info chybí, ale my ho stejně nedostaneme.

Příklad ujasňování si pojmů a dojmů

Se zadavatelem jsme vedly čilou diskuzi i ohledně minimálního časového rozmezí, za které se bude FPY ukazatel posuzovat. Z původních 5 minut se po pár dnech slevilo na 1 hodinu, za což jsme byly upřímně rády. 😅

Mordor tohlecto! Možná ne pro ty, co někdy v podobné fabrice pracovali a v tom systému linek, stanic a oblastí a buhvíčeho ještě, se vyznají, ale být v tomto oboru nováčkem? A rovnou do toho spadnout takto rovnýma nohama?! MORDOR! ☠️

6. Akce! 👊

Poté, co jsme si tak nějak prohlédly zdrojová data, jsme vypracovaly seznam údajů, které nám v doposud poskytnutých datech chyběly. Tématicky jsme je rozdělily do třech tabulek a poeticky (a pragmaticky) je nazvaly

  • Line_def
  • Product_def
  • Směna
Ukázka našeho prvního datového modelu

Některé údaje nám byly doplněny, jiné jen částečně (“Jde přece jen o pilot!”), jiných se nám nedostalo (“Ty směny si nějak sepište!”).


7. Fáze č. 1 👩‍🔬⚗️👩‍🔬

Zbývalo vymyslet nějaké hustokrutopřísné SQL selecty, které nám všechny tabulky magicky scucnou v jednu velkou giga tabulku. S tou pak budeme na hackathonu pracovat v Tableau, kde si budeme bravurně vizualizovat jedno FPY vedle druhého a krása bude střídat nádheru.

První select bral počty kusů z tabulky Events (obsahuje informace pouze o dobrých kusech, neboli pass) v hodinových oknech, ty spočítal a nahrál je do naší výsledné agregované tabulky Projekt1, kde do sloupce fail dosadil nulu.

Druhý select loadoval data z tabulky Failures (obsahuje informace pouze o špatných kusech, neboli fail) do tabulky Projekt1F, který dělal to stejné jako první select s tím rozdílem, že pass již nevyplňoval a dodával pouze údaje do sloupce fail.

Nakonec jsme vytvořily update, který vzal data z naší Projekt1F a nahrál je do tabulky Projekt1 a tím updatoval Failures na základě splnění podmínky toho, že term_id a product_id a časové okno se v obou tabulkách rovná.

Tento postup se však záhy ukázal nevyhovujícím, protože v případě nulového výskytu dobrých kusů (tzn. výskytu pouze kusů špatných) tyto kusy neměl čím spojit a data to celé přeskočilo. Tím nám docházelo ke ztrátě dat. Select jsme tudíž celý přepsaly a použily union s tím, že jsme vytvořily dvě virtuální tabulky a ty naplnily daty a poté pospojovaly.

Psaní selectu ovšem nebyla žádná práce ve věži ze slonoviny, jak se může takhle z blogu zdát. Během jeho vytváření jsme totiž musely se zadavatelem konzultovat zákonitosti průchodu produktu linkou, abychom byly schopny definovat podmínky selectu jako třeba term_id not in 'TRACEINFO' (záznam změny sériového čísla) nebo test_way = 1 (podmínka pro hodnocení průchodu produktu stanicí jako FPY).

Select SQL pro nahrávání dat ze zdrojových tabulek do námi vytvořené tabulky Projekt1 s agregovanými daty

Po tomto výkonu, zbrocené krví, potem a slzami jsme se nemohly dočkat toho, jak to na hackathonu dofinišujem a pak už budeme jenom sedět a nechávat na odiv, jak jsme pěkné. Možná popřemýšlíme, jestli by tento graf nebyl hezčí modrý a nebo tamten oranžový. Ale to jenom možná! 😂

Tududum. Tum. 🎼 (aka RISK tón)

Na hackathon jsme dorazily natěšené (ta lepší z nás stihla dokonce napéct báječné listové šneky, ta horší se cestou do Holešovické tržnice stavila pro něco na zub v Tescu) a plné optimismu. Krásně jsme si rozjely ve Snowflaku naše vymazlené SQL, abychom na zbytek dne tak trochu ztroskotaly na Tableau. 🗻⛵️

Ano, FPY produktu šlo v Tableau vypočítat docela jednoduše. Ano, k FPY stanic jsme taky prokousaly (kdybyste to náhodou někdy někdo potřeboval, tak do Googlu zadejte “tableau running product”). Ale FPY linky je tak složitý (protože dynamický) vzorec, že to v Tableau prostě udělat nešlo. Nadto jsme zjistily, že směny taky nevytvoříme jen tak mrknutím oka. Musely jsme překopat celý koncept.


8. Fáze č. 2. 👩‍🔬🔬👩‍🔬

Z hackathonu jsme odcházely mírně rozčarované, ale s plánem! Ne, pozor, musíme se přece chválit - s EPESNÍM plánem! Jedna naprogramuje v Pythonu skriptík na směny, druhá vyšvihne opět v Pythonu FPY linky.

Směnář v Pythonu vychází ze směnáře na webu Smenar.cz (firma používá podobný směnový model). Ve firmě pracují ve 4směnném provozu, což znamená, že se směny v 28denním cyklu opakují. Potřebovaly jsme vymyslet skript, který se orchestrací nastaví na spuštění vždy v 6:00 a v 18:00, tedy v čase střídání směn, a přesně v tomto čase do giga tabulky zapíše do nového sloupce SMENA písmeno A, B, C, D — vždy podle toho, jaká směna bude zrovna u linek.

Vysoce sofistikovaný technický nákres směnáře s poznámkami, jak by měl finální skript fungovat

Skript potřebuje přístup do databáze. Proto je v něm nejdříve napsáno spojení s databází (poučení: Ne vždy musí být dokumentace napsána jasně a srozumitelně.), pak následuje funkce, která vytváří tabulku ve Snowflaku (poučení: Z nějakého důvodu Pythonu nejde zavolat si ze Snowflaku tabulku vytvořenou pomocí SQL přímo ve Snowflaku.). Tabulka má celkem 4 sloupce:

  • DEN (kolikátý den cyklu)
  • DEN_NOC (šlo o denní nebo noční směnu?)
  • SMENA (A, B, C, D)
  • a START_DATE_SMENA (čas a datum začátku směny).

Následuje funkce s vložením dat do tabulky po spuštění skriptu a funkce, která přidá právě vytvoření řádek do tabulky. Celý skript je zakončený podmínkou pro 28denní cyklus, kde je definovanáno, co se má který den cyklu do tabulky vkládat.

Náhled finálního skriptu směnáře v Pythonu
Náhled na zápis skriptu směnáře Pythonem do Snowflaku
A takto vypadá sloupec Směna zapsaný ve finální tabulce

Směnář bychom měly, tak už jenom to FPY na linku a máme hotovo, ne?

Dámy a pánové, tak takhle to taky nefunguje! Protože:

Vzorec výpočtu FPY pro linku, skupinu linek, všech produktů focus factory či všech linek focus factory
Vodítko pro interpretaci vzorce

Načtení procesové dokumentace a porozumění uvedenému vzorci výpočtu FPY byla jedna věc. Ale převést to celé do reálu byla věc druhá. Naše přelomová vize pro zobrazení FPY linky byla totiž taková, že si koncový uživatel na dashboardu v Tableau navolí linku a na té den a hodinu a směnu, pro které chce vidět údaje, Tableau si zavolá Python, kde se bude počítat vzorec (ten se bude dynamicky měnit v závislosti na navolené lince a časovém rozsahu, neboli na počtu produktů a množství průchodů přes jednotlivé stanice linky), Python sáhne do Snowflaku, vezme si z giga tabulky, co bude potřebovat, a vrátí to do Tableau, které to promítne do dashboardu. Prostě easy!!!

Nakráčet si jako totální sofťák do světa technického, datového, takového hmatatelného pro mne bylo jako objevení nového světa. Co světa! Galaxie!! Co galaxie! Vesmíru!!! Jak mě pak ale překvapilo doporučení, že pokud něco nutného k výpočtu nemáš, tak si to prostě vymyslíš! Na základě toho najdeš cestu, vymyslíš pilot a pak to nasadíš na realitu… jako chápete! “Prostě si to vymyslíš!!” No tak jooo. 🤔 (Nám chyběly údaje o tom, která stanice se objevuje na lince jako poslední, což je podstatný detail pro možnost výpočtu FPY dle výše uvedeného vzorce).

Kdyby tak Medium umělo a mohlo vyjádřit to množství času, který zabralo podívat se na věc zleva, zprava, a poté ještě jednou zleva, aby člověk chca nechca došel k závěru, že celý ten plán generovat skript dynamicky dle volby filtru koncovým uživatelem je nad momentální schopnosti a možnosti jeho i všech v jeho okolí!

Zjištění to bylo nemilé, ale že jsme holky světa znalé, všemi mastmi mazané, i tentokrát jsme našly jinou cestu. Holt když tě vyhodí dveřmi, vlezeš k FPY výpočtu oknem. 💪


9. Fáze č. 3. 👩‍🔬💣👩‍🔬

V této fázi nám už začaly vařit hlavy, vybuchovat mozky a tak…

Z hodin Pythonu mi pro složité věci utkvěl výraz “zavařovačka”. Mám pocit, že v tomto momentě ze mně byly zavařené meruňky plovající si v hutné sladké šťávě, které marně už třetí sezonu ve spíži čekají, než někdo přijde a udělá “MŇUK TSÁÁÁÁÁÁÁ!”. 🍑🍑 Ale nebojte, ten moment přišel.

V SQL selectu teď máme agregaci dat pro hodinový slot vymazlený časovou proměnnou, která musí být vždy definována. My však musíme zajistit, aby tento select fungoval automaticky a zahrnoval vždy aktuální hodinu. Jak na to? Odpovědí nám byl opět jen milovaný Python. Sestavily jsme proto skript v cyklu a loadovaly tak data ze vstupních dat do agregované podoby. Tento proces je automatizovaný a umožňuje využití živé databáze pro účely dashboardu a výpočtu FPY v rozmezí aktuální (uzavřené) hodiny.

Automatizovaný loader dat v Pythonu

A teď ještě k tomu výpočtu, tomu prostě neutečeme. 🤯 Na oko to sice nevypadá, ale v reálu troufáme si říci komplikovaný matematický vzorec nám bezesporu zpestřil jinak celkem plynulou (a nebojme se toho označení - spanilou) jízdu tvorbou projektu a nezbylo nám tedy než opět konzultovat se zadavatelem a zkusit najít ještě neobjevenou pěšinku ke kýženému výsledku. Probrali jsme procedury, stávající praxi, porovnali pro a proti… a našli jsme možnost, jak zobrazovat to, co by se mělo tak, jak by se mělo — teď to ještě dotáhnout v Pythonu a pak už se jen těšit na ten diplom nad schody. 🤗


10. Klička

Pro velký úspěch jsme si tedy celým procesem víceméně prošly ještě jednou a začaly úpravou datového modelu.

Nový datový model podle překopaného zadání

Jak je vidno v mistrném náčrtu výše, bylo nutné sepsat skript pro vytvoření potřebných tabulek, se skupinou linek hodných našeho zájmu, respektive zájmu koncového uživatele, v každé z nich. K tabulkám se též pojilo ETL právě pro ně samotné.

Ukázka skriptu tvorby tabulek

A jak to dopadlo?


11. Vizualizujeeem! 🎨

S cílem lahodit oku a neubrat odbornosti, Tableau bylo naše volba! (Taky to je krásný drag-and-drop nástroj, má hezký barvičky a vůbec - název Tableau má francouzský šmrnc a my rády makronky.😋)

Nešlo to ovšem tak hladce jako na ukázkových hodinách Tableau, kdy si člověk do Tableau naházel potřebné tabulky a mohl začít vesele šmrdlat grafy. Kdepak. Naše čerstvě nabyté SQL skilly bylo potřeba ověřit znovu. Tabulky jsme si pěkně najoinovaly luxusním customizovaným SQL příkazem:

Ale pak už jsme začaly vytvářet epesní grafy a epické dashboardy. Nekecáme, opravdu jsme měly radost (náležitě oslavenou Studentskou pečetí 🍫), protože jsme konečně viděly to, za čím jsme se posledních X týdnů pachtily! ⬇️

Dashboard č. 1

Dashboard č. 2

Druhé manažerské overview zobrazuje FPY konkrétní linky za poslední týdny, dny, směny a také za uplynulých 24 hodin. Uživatel si může filtrovat výsledky podle směn, dnů a hodin.

12. Třešnička — překvapivá, ale o to šťavnatější 🍒

Se slzou v oku jsme se PROZATÍM vzdaly zobrazování konkrétních chyb v procesu a svou třešničkovou lačnost jsme musely ukojit někde jinde. Jelikož ETL musí nejen běžet, ale i být monitorováno, jestli se vše nahrává, jak má, rozhodly jsme se vrhnout na tento malý skriptík, aby měl koncový uživatel k ruce přehled, kolik řádků a v kolik hodin bylo nahráno.

Do loaderu v Pythonu, viz výše, jsme dodaly skript pro kontrolu běhu ETL

A nedalo nám to, musely jsme to hned krásně vizualizovat!

Monitoring ETL, tedy toho, zda řádky do databáze přibývají tak, jak mají

13. To je všechno, přátelé… je to opravdu všechno? 😳

Pozornému čtenáři jistě neuniklo, že v našem případě, více než kdy jindy, platí rčení “i cesta může být cíl” (asi tak na 120 %). Ano, je tomu tak. Cesta to byla samá zatáčka (a chvílemi i slepé střevo, kdy to nikam nevede a bolí to), o to více námi dosažený výsledek určuje směr, kterým se dále ubírat, a snad i dokazuje, že data výrobního podniku nejsou žádná nuda, my nejsme žádná nuda, Python není žádná nuda a SQL, to už je vůbec bžunda k pohledání! Mety vytyčeny zadavatelem se tyčí před námi a jede se dál! 😉


14. A co jsme se na projektu naučily?

  • Ne každý vymyšlený postup má řešení.
  • Python 🐍 je kámoš, ne obluda.
  • K SQL naštěstí existuje dokumentace.
  • Existuje dokumentace! 📚
  • Milujeme dokumentaci! 👩‍🔬👩‍🔬 + 📚 = ❤️
  • Propojit různé technologie může trvat i několik dnů, stay tuned a trpělivá a dáš to!