{"id":15239,"date":"2021-05-30T11:50:41","date_gmt":"2021-05-30T11:50:41","guid":{"rendered":"https:\/\/timi.eu\/?page_id=15239"},"modified":"2021-05-31T09:10:28","modified_gmt":"2021-05-31T09:10:28","slug":"etl-gegevensbeheer","status":"publish","type":"page","link":"https:\/\/timi.eu\/nl\/timi\/anatella\/etl-gegevensbeheer\/","title":{"rendered":"ETL &#8211; Gegevensbeheer"},"content":{"rendered":"<div class=\"row  \" ><div class=\"container\"><div class=\"row row-edge\">\n<div class=\"col-xs-12 col-sm-12 col-md-12 col-lg-12 pageswitcher\">\n\n<div\n\t class=\"wpml-ls-statics-shortcode_actions wpml-ls wpml-ls-legacy-dropdown js-wpml-ls-legacy-dropdown\">\n\t<ul>\n\n\t\t<li tabindex=\"0\" class=\"wpml-ls-slot-shortcode_actions wpml-ls-item wpml-ls-item-nl wpml-ls-current-language wpml-ls-item-legacy-dropdown\">\n\t\t\t<a href=\"#\" class=\"js-wpml-ls-item-toggle wpml-ls-item-toggle\">\n                                                    <img\n            class=\"wpml-ls-flag\"\n            src=\"https:\/\/timi.eu\/wp-content\/plugins\/sitepress-multilingual-cms\/res\/flags\/nl.png\"\n            alt=\"\"\n            width=18\n            height=12\n    \/><span class=\"wpml-ls-native\">Nederlands<\/span><\/a>\n\n\t\t\t<ul class=\"wpml-ls-sub-menu\">\n\t\t\t\t\n\t\t\t\t\t<li class=\"wpml-ls-slot-shortcode_actions wpml-ls-item wpml-ls-item-en wpml-ls-first-item\">\n\t\t\t\t\t\t<a href=\"https:\/\/timi.eu\" class=\"wpml-ls-link\">\n                                                                <img\n            class=\"wpml-ls-flag\"\n            src=\"https:\/\/timi.eu\/wp-content\/plugins\/sitepress-multilingual-cms\/res\/flags\/en.png\"\n            alt=\"\"\n            width=18\n            height=12\n    \/><span class=\"wpml-ls-display\">Engels<\/span><\/a>\n\t\t\t\t\t<\/li>\n\n\t\t\t\t\n\t\t\t\t\t<li class=\"wpml-ls-slot-shortcode_actions wpml-ls-item wpml-ls-item-fr\">\n\t\t\t\t\t\t<a href=\"https:\/\/timi.eu\/fr\/\" class=\"wpml-ls-link\">\n                                                                <img\n            class=\"wpml-ls-flag\"\n            src=\"https:\/\/timi.eu\/wp-content\/plugins\/sitepress-multilingual-cms\/res\/flags\/fr.png\"\n            alt=\"\"\n            width=18\n            height=12\n    \/><span class=\"wpml-ls-display\">Frans<\/span><\/a>\n\t\t\t\t\t<\/li>\n\n\t\t\t\t\n\t\t\t\t\t<li class=\"wpml-ls-slot-shortcode_actions wpml-ls-item wpml-ls-item-es\">\n\t\t\t\t\t\t<a href=\"https:\/\/timi.eu\/es\/\" class=\"wpml-ls-link\">\n                                                                <img\n            class=\"wpml-ls-flag\"\n            src=\"https:\/\/timi.eu\/wp-content\/plugins\/sitepress-multilingual-cms\/res\/flags\/es.png\"\n            alt=\"\"\n            width=18\n            height=12\n    \/><span class=\"wpml-ls-display\">Spaans<\/span><\/a>\n\t\t\t\t\t<\/li>\n\n\t\t\t\t\n\t\t\t\t\t<li class=\"wpml-ls-slot-shortcode_actions wpml-ls-item wpml-ls-item-ru wpml-ls-last-item\">\n\t\t\t\t\t\t<a href=\"https:\/\/timi.eu\/ru\/\" class=\"wpml-ls-link\">\n                                                                <img\n            class=\"wpml-ls-flag\"\n            src=\"https:\/\/timi.eu\/wp-content\/plugins\/sitepress-multilingual-cms\/res\/flags\/ru.png\"\n            alt=\"\"\n            width=18\n            height=12\n    \/><span class=\"wpml-ls-display\">Russisch<\/span><\/a>\n\t\t\t\t\t<\/li>\n\n\t\t\t\t\t\t\t<\/ul>\n\n\t\t<\/li>\n\n\t<\/ul>\n<\/div>\n\n<\/div>\n<\/div><\/div><\/div>\n<div class=\"row custom-parallax-01 \" ><section class=\"parallax-box image-parallax-box \" style=\"min-height: 300px\"><div class=\"parallax-content\"><div class=\"container\"><div class=\"row row-edge\">\n<div class=\"col-xs-12 col-sm-12 col-md-12 col-lg-12 custom-parallax-text\">\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:150px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:100px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:50px;\"><\/div>\n<img decoding=\"async\" class=\"aligncenter size-full wp-image-1564\" style=\"position: relative;\" src=\"\/wp-content\/uploads\/2016\/07\/logo-anatellla-min-e1468012310881.png\" alt=\"Anatella\" width=\"75\"><\/p>\n<h2 style=\"text-align: center; color: #333;\"><strong>NOOIT MEER WACHTEN<\/strong><br \/>\nOP EEN DATATRANSFORMATIE<\/h2>\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:200px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:150px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:100px;\"><\/div>\n<\/div>\n<\/div><\/div><div class=\"clear\"><\/div><\/div><div class=\"parallax-bg\" data-parallax-type=\"image\" data-img-url=\"\/wp-content\/uploads\/2020\/02\/leopards2b.jpg\" data-speed=\"1.5\" data-invert=\"\" ><\/div><\/section><\/div>\n<div class=\"row custom-parallax-01 \" ><div class=\"cherry-box   box-976\"><div class=\"inner\"><div class=\"container\"><div class=\"row row-edge\">\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:100px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:50px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:30px;\"><\/div>\n<div class=\"col-xs-12 col-sm-12 col-md-6 col-lg-6 custom-parallax-text\">\n<h2 style=\"color: #333; text-align: center;\"><strong>SNEL SORTEREN<\/strong><br \/>\n<span style=\"color: #6c0; font-weight: 500;\">VOOR EEN TELECOMBEDRIJF<\/span><\/h2>\n<p style=\"color: #000; text-align: center;\">Anatella sorteert een grote CDR (Call Data Record) tabel met 1 miljard rijen en 8 kolommen. Deze 171 GB CDR tabel (als tekstbestand) is gesorteerd in 99 seconden met minder dan 300MB RAM.<\/p>\n<\/div>\n<div class=\"col-xs-12 col-sm-12 col-md-6 col-lg-6 custom-parallax-text\">\n<div class=\"row row-edge  \" >\n<div class=\"col-xs-0 col-sm-0 col-md-1 col-lg-1\">\n<\/div>\n<div class=\"col-xs-3 col-sm-3 col-md-3 col-lg-3\">\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:50px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:30px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:15px;\"><\/div>\n<div class=\"cherry-counter myclass1b myclass2\" data-delay=\"1\"  data-time=\"3000\"><span class=\"count\">1000<\/span><span class=\"after\">million rows<\/span><\/div>\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:25px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:10px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:5px;\"><\/div>\n<\/div>\n<div class=\"col-xs-3 col-sm-3 col-md-3 col-lg-3\">\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:50px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:30px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:15px;\"><\/div>\n<div class=\"cherry-counter myclass1b\" data-delay=\"2\"  data-time=\"6000\"><span class=\"count\">300<\/span><span class=\"after\">MB of ram<\/span><\/div>\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:25px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:10px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:5px;\"><\/div>\n<\/div>\n<div class=\"col-xs-3 col-sm-3 col-md-3 col-lg-3\">\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:50px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:30px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:15px;\"><\/div>\n<div class=\"cherry-counter myclass1b\" data-delay=\"1\"  data-time=\"3000\"><span class=\"count\">99<\/span><span class=\"after\">seconds<\/span><\/div>\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:25px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:10px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:5px;\"><\/div>\n<\/div>\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:100px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:50px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:30px;\"><\/div>\n<\/div>\n<\/div>\n<div class=\"col-xs-12 col-sm-12 col-md-12 col-lg-12 custom-parallax-text\">\n<div class=\"cherry-accordion  \">\n<div class=\"cherry-spoiler cherry-spoiler-style-default morevalue1  cherry-spoiler-closed\"><div class=\"cherry-spoiler-title\">Meer...<\/div><div class=\"cherry-spoiler-content cherry-clearfix\" style=\"display:none\">\n<a class=\"single-popup-image\" href=\"\/wp-content\/uploads\/2016\/09\/demo_sort.png\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/2016\/09\/demo_sort.png\" alt=\"demo_sort\" width=\"50%\" class=\"alignright size-full wp-image-6059\" srcset=\"https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/demo_sort.png 851w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/demo_sort-600x490.png 600w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/demo_sort-300x245.png 300w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/demo_sort-768x627.png 768w\" sizes=\"(max-width: 851px) 100vw, 851px\" \/><\/a>Een soortgelijke sortering zou 5 uur duren indien uitgevoerd tegen een bekende leidende database.<br \/>\nDe CDR-tabel is afkomstig van een telecombedrijf met 6 miljoen abonnees. Hij is opgeslagen in een database en weegt bijna 2 TB.<\/p>\n<p>De beginnende CDR tabel bestaat uit 42 verschillende bestanden (in .gel_anatella formaat), dat is 1 miljard rijen of 42 dagen gegevens in totaal. Anatella sorteert de CDR-tabel op kolom A (die meer dan 6 miljoen verschillende oproepnummers bevat). Dit is de datatransformatie grafiek die de sortering uitvoert op de laptop:<\/p>\n<p><img decoding=\"async\" class=\"alignleft size-full wp-image-4832\" src=\"\/wp-content\/uploads\/2016\/09\/sort-1.png\" alt=\"sort\" width=\"500\" srcset=\"https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/sort-1.png 803w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/sort-1-600x69.png 600w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/sort-1-300x34.png 300w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/sort-1-768x88.png 768w\" sizes=\"(max-width: 803px) 100vw, 803px\" \/><br \/>\n&nbsp;<br \/>\n<\/div><\/div>\n<\/div>\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:100px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:50px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:30px;\"><\/div>\n<\/div>\n<\/div><\/div><\/div><\/div><\/div>\n<div class=\"row custom-parallax-01 \" ><div class=\"cherry-box   box-399\"><div class=\"inner\"><div class=\"container\"><div class=\"row row-edge\">\n<div class=\"col-xs-12 col-sm-12 col-md-6 col-lg-6 custom-parallax-text\">\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:150px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:75px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:50px;\"><\/div>\n<div class=\"row row-edge  \" >\n<div class=\"col-xs-0 col-sm-0 col-md-1 col-lg-1\">\n<\/div>\n<div class=\"col-xs-3 col-sm-3 col-md-3 col-lg-3\">\n<div class=\"cherry-counter myclass1 myclass2\" data-delay=\"1\"  data-time=\"3000\"><span class=\"count\">1300<\/span><span class=\"after\">million rows<\/span><\/div>\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:25px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:10px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:5px;\"><\/div>\n<\/div>\n<div class=\"col-xs-3 col-sm-3 col-md-3 col-lg-3\">\n<div class=\"cherry-counter myclass1\" data-delay=\"2\"  data-time=\"6000\"><span class=\"count\">150<\/span><span class=\"after\">MB of ram<\/span><\/div>\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:25px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:10px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:5px;\"><\/div>\n<\/div>\n<div class=\"col-xs-3 col-sm-3 col-md-3 col-lg-3\">\n<div class=\"cherry-counter myclass1\" data-delay=\"1\"  data-time=\"3000\"><span class=\"count\">70<\/span><span class=\"after\">seconds<\/span><\/div>\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:25px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:10px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:5px;\"><\/div>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"col-xs-12 col-sm-12 col-md-6 col-lg-6 custom-parallax-text\">\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:70px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:50px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:30px;\"><\/div>\n<h2 style=\"color: #333; text-align: center;\"><strong>BEREKENING VAN AGGREGATEN<\/strong><br \/>\n<span style=\"color: #6c0; font-weight: 500;\">VOOR EEN SUPERMARKT<\/span><\/h2>\n<p style=\"color: #000; text-align: center;\">\nUit de ticket-tabel (1,3 miljard regels en 17 kolommen) berekent Anatella het volgende aggregaat: het percentage van de aankoop via het web. Deze KPI wordt berekend in 70 seconden met gebruik van minder dan 50MB RAM.<\/p>\n<\/div>\n<div class=\"col-xs-12 col-sm-12 col-md-12 col-lg-12 custom-parallax-text\">\n<div class=\"cherry-accordion  \">\n<div class=\"cherry-spoiler cherry-spoiler-style-default morevalue2  cherry-spoiler-closed\"><div class=\"cherry-spoiler-title\">Meer...<\/div><div class=\"cherry-spoiler-content cherry-clearfix\" style=\"display:none\">\n<a class=\"single-popup-image\" href=\"\/wp-content\/uploads\/2016\/09\/retail.png\"><img decoding=\"async\" src=\"\/wp-content\/uploads\/2016\/09\/retail.png\" alt=\"retail\" width=\"50%\" style=\"padding-bottom: 30px;\" class=\"alignleft size-full wp-image-6060\" srcset=\"https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/retail.png 1536w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/retail-600x361.png 600w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/retail-300x181.png 300w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/retail-768x463.png 768w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/retail-1024x617.png 1024w\" sizes=\"(max-width: 1536px) 100vw, 1536px\" \/><\/a>De ticket-tabel bevat alle aankopen van meer dan 1 miljoen klanten over een periode van 2 jaar op de fijnste schaal (\u00e9\u00e9n rij in de tabel vertegenwoordigt de aankoop van \u00e9\u00e9n product). We hebben eerst deze ticket-tabel uit Teradata gehaald (meer dan 2TB in de database) om een 450 GB .sas7bdat bestand te krijgen. Vervolgens hebben wij met behulp van Anatella dit .sas7bdat bestand geconverteerd naar een compacter .cgel_anatella bestand van 23 GB. Dit nieuwe bestand werd gebruikt om het percentage van aankopen op het web te berekenen. Dit is de Anatella datatransformatiegrafiek waarmee het gevraagde aggregaat wordt berekend:<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2016\/09\/aggr1.png\" alt=\"aggr1\" width=\"500\" class=\"aligncenter size-full wp-image-5028\" srcset=\"https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/aggr1.png 660w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/aggr1-600x89.png 600w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/aggr1-300x45.png 300w\" sizes=\"(max-width: 660px) 100vw, 660px\" \/><br \/>\n<\/div><\/div>\n<\/div>\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:70px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:50px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:30px;\"><\/div>\n<\/div>\n<\/div><\/div><\/div><\/div><\/div>\n<div class=\"row custom-parallax-01 \" ><div class=\"cherry-box   box-645\"><div class=\"inner\"><div class=\"container\"><div class=\"row row-edge\">\n<div class=\"col-xs-12 col-sm-4 col-md-4 col-lg-4\">\n<\/div>\n<div class=\"col-xs-12 col-sm-4 col-md-4 col-lg-4 custom-parallax-text\">\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:70px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:50px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:30px;\"><\/div>\n<h2 style=\"text-align: center;\"><strong>OPMERKING<\/strong><\/h2>\n<p style=\"text-align: center;\">Alle voorbeelden die op deze pagina worden genoemd, draaien op deze laptop:<\/p>\n<p><img decoding=\"async\" loading=\"lazy\" src=\"\/wp-content\/uploads\/2016\/09\/msi-1-1024x770.png\" alt=\"msi\" width=\"1024\" height=\"770\" class=\"aligncenter size-large wp-image-4830\" srcset=\"https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/msi-1-1024x770.png 1024w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/msi-1-600x451.png 600w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/msi-1-300x226.png 300w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/msi-1-768x578.png 768w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/msi-1-200x150.png 200w, https:\/\/timi.eu\/wp-content\/uploads\/2016\/09\/msi-1.png 1073w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><br \/>\n<div class=\"cherry-spacer   hidden-xs hidden-sm\" style=\"height:50px;\"><\/div><div class=\"cherry-spacer   visible-sm-block\" style=\"height:30px;\"><\/div><div class=\"cherry-spacer   visible-xs-block\" style=\"height:15px;\"><\/div>\n<\/div>\n<div class=\"col-xs-12 col-sm-4 col-md-4 col-lg-4\">\n<\/div>\n<\/div><\/div><\/div><\/div><\/div>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":15233,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":[],"_links":{"self":[{"href":"https:\/\/timi.eu\/nl\/wp-json\/wp\/v2\/pages\/15239"}],"collection":[{"href":"https:\/\/timi.eu\/nl\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/timi.eu\/nl\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/timi.eu\/nl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/timi.eu\/nl\/wp-json\/wp\/v2\/comments?post=15239"}],"version-history":[{"count":2,"href":"https:\/\/timi.eu\/nl\/wp-json\/wp\/v2\/pages\/15239\/revisions"}],"predecessor-version":[{"id":15242,"href":"https:\/\/timi.eu\/nl\/wp-json\/wp\/v2\/pages\/15239\/revisions\/15242"}],"up":[{"embeddable":true,"href":"https:\/\/timi.eu\/nl\/wp-json\/wp\/v2\/pages\/15233"}],"wp:attachment":[{"href":"https:\/\/timi.eu\/nl\/wp-json\/wp\/v2\/media?parent=15239"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}