{"id":37156,"date":"2019-10-31T22:16:03","date_gmt":"2019-10-31T19:16:03","guid":{"rendered":"https:\/\/prohoster.info\/blog\/bolshe-statistiki-sajta-v-svoyom-malenkom-hranilishhe\/"},"modified":"2019-10-31T22:16:03","modified_gmt":"2019-10-31T19:16:03","slug":"bolshe-statistiki-sajta-v-svoyom-malenkom-hranilishhe","status":"publish","type":"post","link":"https:\/\/prohoster.info\/nl\/blog\/administrirovanie\/bolshe-statistiki-sajta-v-svoyom-malenkom-hranilishhe","title":{"rendered":"Meer website-statistieken in jouw kleine opslagruimte","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Door de website-statistieken te analyseren, krijgen we inzicht in wat er met de site gebeurt. We vergelijken de resultaten met andere kennis over het product of de dienst en verbeteren hiermee onze ervaring.<\/p>\n

Wanneer de analyse van de eerste resultaten is voltooid, het informatieoverzicht is gemaakt en conclusies zijn getrokken, begint de volgende fase. Idee\u00ebn ontstaan: wat gebeurt er als we de gegevens vanuit een ander perspectief bekijken?<\/p>\n

Op dit stadium zijn er beperkingen aan de analysetools. Dit is een van de redenen waarom de tool Google Analytics niet voldoende voor mij was, juist vanwege de beperkte mogelijkheden om mijn gegevens te zien en ermee te manipuleren.<\/p>\n

Ik heb altijd snel de basisgegevens (master-data) willen uploaden, een ander niveau van aggregatie willen toevoegen of de bestaande waarden anders willen interpreteren.<\/p>\n

Dit is gemakkelijk te doen in jouw kleine opslagruimte<\/a><\/noindex> op basis van het bestand access.log en hiervoor is SQL genoeg.<\/a><\/noindex><\/p>\n

Dus, op welke vragen wilde ik antwoord vinden?<\/p>\n

Wat en wanneer is er veranderd op de site<\/h4>\n

\nDe geschiedenis van de wijzigingen in de basisgegevens (master-data) is altijd interessant.<\/p>\n

\"Meer
\n
\nSQL-query voor rapport<\/b><\/p>\n

SELECT\n\t1 AS 'SideStackedBar: Inhoudsupdates per Maand',\n\tstrftime('%m\/%Y', datetime(UPDATE_DT, 'unixepoch')) AS 'Dag',\n\tCOUNT(CASE WHEN PAGE_TITLE != 'n.a.' THEN DIM_REQUEST_ID END) AS 'Webpagina-updates',\n\tCOUNT(CASE WHEN PAGE_DESCR = 'IMAGES' THEN DIM_REQUEST_ID END) AS 'Afbeelding uploads',\n\tCOUNT(CASE WHEN PAGE_DESCR = 'VIDEO' THEN DIM_REQUEST_ID END) AS 'Video uploads',\n\tCOUNT(CASE WHEN PAGE_DESCR = 'AUDIO' THEN DIM_REQUEST_ID END) AS 'Audiouploads'\nFROM DIM_REQUEST\nWHERE PAGE_TITLE != 'n.a.' OF PAGE_DESCR != 'n.a.'\nGROUP BY strftime('%m\/%Y', datetime(UPDATE_DT, 'unixepoch'))\nORDER BY UPDATE_DT<\/code><\/pre>\n
Bijvoorbeeld, op een bepaald moment werd er zoekmachineoptimalisatie uitgevoerd of werd er nieuwe content op de site toegevoegd, waardoor een toename van het verkeer werd verwacht.<\/p>\n
Gebruikersgroepen<\/h4>\n
\nHet eenvoudigste voorbeeld van een groep kan een gebruikersagent of de naam van het besturingssysteem zijn. <\/p>\n
De meting van gebruikersagents heeft ongeveer duizend records verzameld en ik was benieuwd naar de dynamiek van de verdeling van agents binnen de groep.<\/p>\n
\"Meer
\n
\nSQL-query voor rapport<\/b><\/p>\n
SELECT\n\t1 AS 'SideStackedBar: Gebruikersagents',\n\tAGENT_OS AS 'OS',\n\tSUM(CASE WHEN AGENT_BOT = 'n.a.' THEN 1 ELSE 0 END) AS 'Gebruikersagent van Gebruikers',\n\tSUM(CASE WHEN AGENT_BOT != 'n.a.' THEN 1 ELSE 0 END) AS 'Gebruikersagent van Bots'\nFROM DIM_USER_AGENT\nWHERE DIM_USER_AGENT_ID != -1\nGROUP BY AGENT_OS\nORDER BY 3 DESC<\/code><\/pre>\n
De meeste verschillende combinaties van agents komen op de site vanuit de wereld van Windows. Onder de onbepaalde bevinden zich dingen zoals WhatsApp, PocketImageCache, PlayStation, SmartTV, enz. <\/p>\n
Activiteit van gebruikersgroepen per week<\/h4>\n
\nDoor sommige groepen samen te voegen, kan de verdeling van hun activiteit worden waargenomen.<\/p>\n
Bijvoorbeeld, gebruikers van het Linux-cluster verbruiken meer verkeer op de site dan alle andere.<\/p>\n
\"Meer
\n
\nSQL-query voor rapport<\/b><\/p>\n
SELECT\n1 as 'StackedBar: Verkeersvolume per gebruikersbesturingssysteem en per week',\nstrftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Week',\nSUM(CASE WHEN USG.AGENT_OS IN ('Android', 'Linux') THEN FCT.BYTES ELSE 0 END) \/ 1000 AS 'Android\/Linux Gebruikers',\nSUM(CASE WHEN USG.AGENT_OS IN ('Windows') THEN FCT.BYTES ELSE 0 END) \/ 1000 AS 'Windows Gebruikers',\nSUM(CASE WHEN USG.AGENT_OS IN ('Macintosh', 'iOS') THEN FCT.BYTES ELSE 0 END) \/ 1000 AS 'Mac\/iOS Gebruikers',\nSUM(CASE WHEN USG.AGENT_OS IN ('n.a.', 'BlackBerry') THEN FCT.BYTES ELSE 0 END) \/ 1000 AS 'Overig'\nFROM\n  FCT_ACCESS_USER_AGENT_DD FCT,\n  DIM_USER_AGENT USG,\n  DIM_HTTP_STATUS HST\nWHERE FCT.DIM_USER_AGENT_ID = USG.DIM_USER_AGENT_ID\n  AND FCT.DIM_HTTP_STATUS_ID = HST.DIM_HTTP_STATUS_ID\n  AND USG.AGENT_BOT = 'n.a.' \/* alleen gebruikers *\/\n  AND HST.STATUS_GROUP IN ('Successful') \/* goede pagina's *\/\n  AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month')\nGROUP BY strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch'))\nORDER BY FCT.EVENT_DT<\/code><\/pre>\n
Intensief verkeer verbruik<\/h4>\n
\nUit de tabel zijn de meest actieve gebruikersgroepen en hun activiteitendag zichtbaar.
\nDe meest actieve behoren tot het Linux-cluster.<\/p>\n
\"Meer
\n
\nSQL-query voor rapport<\/b><\/p>\n
SELECT\n1 AS 'Table: User Agent met hoog gebruik',\nstrftime('%d.%m.%Y', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Dag',\nROUND(1.0 * SUM(FCT.BYTES) \/ 1000000, 1) AS 'Verkeer MB',\nROUND(1.0 * SUM(FCT.IP_CNT) \/ SUM(1), 1) AS 'IP's',\nROUND(1.0 * SUM(FCT.REQUEST_CNT) \/ SUM(1), 1) AS 'Verzoeken',\nUSA.DIM_USER_AGENT_ID AS 'ID',\nMAX(USA.USER_AGENT_NK) AS 'User Agent',\nMAX(USA.AGENT_BOT) AS 'Bot'\nFROM\nFCT_ACCESS_USER_AGENT_DD FCT,\nDIM_USER_AGENT USA\nWHERE FCT.DIM_USER_AGENT_ID = USA.DIM_USER_AGENT_ID\n  AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-30 day')\nGROUP BY USA.DIM_USER_AGENT_ID, strftime('%d.%m.%Y', datetime(FCT.EVENT_DT, 'unixepoch')) \nORDER BY SUM(FCT.BYTES) DESC, FCT.EVENT_DT\nLIMIT 10<\/code><\/pre>\n
Door gebruik te maken van de attributen dag en ID van de agent, ontstaat de mogelijkheid om snel statistieken per dag voor afzonderlijke gebruikersgroepen te vinden en te volgen. Indien nodig kan gedetailleerde informatie snel worden gevonden in de staging-tabel.<\/p>\n
Hoe krijg ik informatie?<\/h4>\n
\nInformatie uit het bestand access.log<\/a><\/noindex> kan nog effici\u00ebnter worden gemaakt door aanvullende gegevensbronnen te integreren en nieuwe niveaus van aggregatie en groepering in te voeren.<\/p>\n
Basisgegevens en entiteiten<\/h4>\n
\nOnder basisgegevens valt informatie over entiteiten: webpagina's, afbeeldingen, video's en audio-inhoud; in het geval van een winkel zijn dit producten. <\/p>\n
De entiteiten fungeren als dimensies, en het proces van het opslaan van wijzigingen in attributen wordt historisering genoemd. In databases wordt dit proces vaak gerealiseerd in de vorm van langzaam veranderende dimensies (SCD).<\/p>\n
Bronnen van basisgegevens kunnen heel divers zijn, daarom moeten ze vrijwel altijd worden ge\u00efntegreerd.<\/p>\n
Langzaam veranderende dimensie<\/h4>\n
\nDe meting DIM_REQUEST zal informatie over verzoeken op de site in historische vorm bevatten.<\/p>\n
Tabel SCD2<\/b><\/p>\n
CREATE TABLE DIM_REQUEST ( \n  DIM_REQUEST_ID      INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n  DIM_REQUEST_ID_HIST INTEGER NOT NULL DEFAULT -1,\n  REQUEST_NK          TEXT NOT NULL DEFAULT 'n.a.', \n  PAGE_TITLE          TEXT NOT NULL DEFAULT 'n.a.',\n  PAGE_DESCR          TEXT NOT NULL DEFAULT 'n.a.',\n  PAGE_KEYWORDS       TEXT NOT NULL DEFAULT 'n.a.',\n  DELETE_FLAG         INTEGER NOT NULL DEFAULT 0,\n  UPDATE_DT           INTEGER NOT NULL DEFAULT 0,\n  UNIQUE (REQUEST_NK, DIM_REQUEST_ID_HIST)\n);\nINSERT INTO DIM_REQUEST (DIM_REQUEST_ID) VALUES (-1);<\/code><\/pre>\n
Daarnaast zullen we een weergave cre\u00ebren die altijd alle records in de laatste staat weergeeft. Dit is nodig voor het laden van de meting zelf.<\/p>\n
\"Meer
\n
\nActuele weergave SCD2<\/b><\/p>\n
\/* Content: actual view on scd table *\/\nSELECT HI.DIM_REQUEST_ID,\n  HI.DIM_REQUEST_ID_HIST,\n  HI.REQUEST_NK,\n  HI.PAGE_TITLE,\n  HI.PAGE_DESCR,\n  HI.PAGE_KEYWORDS,\n  NK.CNT AS HIST_CNT,\n  HI.DELETE_FLAG,\n  strftime('%d.%m.%Y %H:%M', datetime(HI.UPDATE_DT, 'unixepoch')) AS UPDATE_DT\nFROM\n  ( SELECT REQUEST_NK, MAX(DIM_REQUEST_ID) AS DIM_REQUEST_ID, SUM(1) AS CNT\n    FROM DIM_REQUEST\n    GROUP BY REQUEST_NK\n  ) NK,\n  DIM_REQUEST HI\nWHERE 1 = 1\n  AND NK.REQUEST_NK = HI.REQUEST_NK\n  AND NK.DIM_REQUEST_ID = HI.DIM_REQUEST_ID;<\/code><\/pre>\n
En de weergave waar historische informatie voor elk record is verzameld. Dit is nodig om historisch accurate relaties met feiten op te bouwen.<\/p>\n
\"Meer
\n
\nHistorische weergave SCD2<\/b><\/p>\n
\/* Content: actual view on scd table *\/\nSELECT SCD.DIM_REQUEST_ID,\n  SCD.DIM_REQUEST_ID_HIST,\n  SCD.REQUEST_NK,\n  SCD.PAGE_TITLE,\n  SCD.PAGE_DESCR,\n  SCD.PAGE_KEYWORDS,\n  SCD.DELETE_FLAG,\n  CASE\n    WHEN HIS.UPDATE_DT IS NULL\n    THEN 1\n    ELSE 0 END ACTIVE_FLAG,\n  SCD.DIM_REQUEST_ID_HIST AS ID_FROM,\n  SCD.DIM_REQUEST_ID AS ID_TO,\n  CASE\n    WHEN SCD.DIM_REQUEST_ID_HIST=-1\n    THEN 3600\n    ELSE IFNULL(SCD.UPDATE_DT,3600)\n  END AS TIME_FROM,\n  CASE\n    WHEN HIS.UPDATE_DT IS NULL\n    THEN 253370764800\n    ELSE HIS.UPDATE_DT\n  END AS TIME_TO,\n  CASE\n    WHEN SCD.DIM_REQUEST_ID_HIST=-1\n    THEN STRFTIME('%d.%m.%Y %H:%M', DATETIME(3600, 'unixepoch'))\n    ELSE STRFTIME('%d.%m.%Y %H:%M', DATETIME(IFNULL(SCD.UPDATE_DT,3600), 'unixepoch'))\n  END AS ACTIVE_FROM,\n  CASE\n    WHEN HIS.UPDATE_DT IS NULL\n    THEN STRFTIME('%d.%m.%Y %H:%M', DATETIME(253370764800, 'unixepoch'))\n    ELSE STRFTIME('%d.%m.%Y %H:%M', DATETIME(HIS.UPDATE_DT, 'unixepoch'))\n  END AS ACTIVE_TO\nFROM\n  DIM_REQUEST SCD\n  LEFT OUTER JOIN DIM_REQUEST HIS\n  ON SCD.REQUEST_NK = HIS.REQUEST_NK AND SCD.DIM_REQUEST_ID = HIS.DIM_REQUEST_ID_HIST;<\/code><\/pre>\n
Gegevensaggregatie<\/h4>\n
\nComprimeren (aggregatie) stelt je in staat om gegevens op een hoger niveau te evalueren en afwijkingen en trends te ontdekken die niet zichtbaar zijn in gedetailleerde rapporten.<\/p>\n
Bijvoorbeeld, in de meting met statuscodes DIM_HTTP_STATUS voegen we een groep toe:<\/p>\n
STATUS \/ GROEP
\n0xx \/ n.a.
\n1xx \/ Informatief
\n2xx \/ Succesvol
\n3xx \/ Redirectie
\n4xx \/ Clientfout
\n5xx \/ Serverfout<\/p><\/blockquote>\n
 De meting van gebruikersagenten DIM_USER_AGENT zal de attributen AGENT_OS en AGENT_BOT bevatten, die verantwoordelijk zijn voor de groepen. Deze kunnen tijdens het ETL-proces worden ingevuld:<\/p>\n
Laden van DIM_USER_AGENT<\/b><\/p>\n
\/* Propagate the user agent from access log *\/\nINSERT INTO DIM_USER_AGENT (USER_AGENT_NK, AGENT_OS, AGENT_ENGINE, AGENT_DEVICE, AGENT_BOT, UPDATE_DT)\nWITH CLS AS (\n\tSELECT BROWSER\n\tFROM STG_ACCESS_LOG WHERE LENGTH(BROWSER)>1\n\tGROUP BY BROWSER\n)\nSELECT\n\tCLS.BROWSER AS USER_AGENT_NK,\n\tCASE\n\tWHEN INSTR(CLS.BROWSER,'Macintosh')>0\n\t\tTHEN 'Macintosh'\n\tWHEN INSTR(CLS.BROWSER,'iPhone')>0\n\t\t\t OR INSTR(CLS.BROWSER,'iPad')>0\n\t\t\t OR INSTR(CLS.BROWSER,'iPod')>0\n\t\t\t OR INSTR(CLS.BROWSER,'Apple TV')>0\n\t\t\t OR INSTR(CLS.BROWSER,'Darwin')>0\n\t\tTHEN 'iOS'\n\tWHEN INSTR(CLS.BROWSER,'Android')>0\n\t\tTHEN 'Android'\n\tWHEN INSTR(CLS.BROWSER,'X11;')>0 OR INSTR(CLS.BROWSER,'Wayland;')>0 OR INSTR(CLS.BROWSER,'linux-gnu')>0\n\t\tTHEN 'Linux'\n\tWHEN INSTR(CLS.BROWSER,'BB10;')>0 OR INSTR(CLS.BROWSER,'BlackBerry')>0\n\t\tTHEN 'BlackBerry'\n\tWHEN INSTR(CLS.BROWSER,'Windows')>0\n\t\tTHEN 'Windows'\n\tELSE 'n.a.' END AS AGENT_OS, -- OS\n\tCASE\n\tWHEN INSTR(CLS.BROWSER,'AppleCoreMedia')>0\n\t\tTHEN 'AppleWebKit'\n\tWHEN INSTR(CLS.BROWSER,') ')>1 AND LENGTH(CLS.BROWSER)>INSTR(CLS.BROWSER,') ')\n\t\tTHEN COALESCE(SUBSTR(CLS.BROWSER, INSTR(CLS.BROWSER,') ')+2, LENGTH(CLS.BROWSER) - INSTR(CLS.BROWSER,') ')-1), 'N\/A')\n\tELSE 'n.a.' END AS AGENT_ENGINE, -- Engine\n\tCASE\n\tWHEN INSTR(CLS.BROWSER,'iPhone')>0\n\t\tTHEN 'iPhone'\n\tWHEN INSTR(CLS.BROWSER,'iPad')>0\n\t\tTHEN 'iPad'\n\tWHEN INSTR(CLS.BROWSER,'iPod')>0\n\t\tTHEN 'iPod'\n\tWHEN INSTR(CLS.BROWSER,'Apple TV')>0\n\t\tTHEN 'Apple TV'\n\tWHEN INSTR(CLS.BROWSER,'Android ')>0 AND INSTR(CLS.BROWSER,'Build')>0\n\t\tTHEN COALESCE(SUBSTR(CLS.BROWSER, INSTR(CLS.BROWSER,'Android '), INSTR(CLS.BROWSER,'Build')-INSTR(CLS.BROWSER,'Android ')), 'n.a.')\n\tWHEN INSTR(CLS.BROWSER,'Android ')>0 AND INSTR(CLS.BROWSER,'MIUI')>0\n\t\tTHEN COALESCE(SUBSTR(CLS.BROWSER, INSTR(CLS.BROWSER,'Android '), INSTR(CLS.BROWSER,'MIUI')-INSTR(CLS.BROWSER,'Android ')), 'n.a.')\n\tELSE 'n.a.' END AS AGENT_DEVICE, -- Device\n\tCASE\n\tWHEN INSTR(LOWER(CLS.BROWSER),'yandex.com')>0\n\t\tTHEN 'yandex'\n\tWHEN INSTR(LOWER(CLS.BROWSER),'googlebot')>0\n\t\tTHEN 'google'\n\tWHEN INSTR(LOWER(CLS.BROWSER),'bingbot')>0\n\t\tTHEN 'microsoft'\n\tWHEN INSTR(LOWER(CLS.BROWSER),'ahrefsbot')>0\n\t\tTHEN 'ahrefs'\n\tWHEN INSTR(LOWER(CLS.BROWSER),'jobboersebot')>0 OR INSTR(LOWER(CLS.BROWSER),'jobkicks')>0\n\t\tTHEN 'job.de'\n\tWHEN INSTR(LOWER(CLS.BROWSER),'mail.ru')>0\n\t\tTHEN 'mail.ru'\n\tWHEN INSTR(LOWER(CLS.BROWSER),'baiduspider')>0\n\t\tTHEN 'baidu'\n\tWHEN INSTR(LOWER(CLS.BROWSER),'mj12bot')>0\n\t\tTHEN 'majestic-12'\n\tWHEN INSTR(LOWER(CLS.BROWSER),'duckduckgo')>0\n\t\tTHEN 'duckduckgo'\n\tWHEN INSTR(LOWER(CLS.BROWSER),'bytespider')>0\n\t\tTHEN 'bytespider'\n\tWHEN INSTR(LOWER(CLS.BROWSER),'360spider')>0\n\t\tTHEN 'so.360.cn'\n\tWHEN INSTR(LOWER(CLS.BROWSER),'compatible')>0 OR INSTR(LOWER(CLS.BROWSER),'http')>0\n\t\tOR INSTR(LOWER(CLS.BROWSER),'libwww')>0 OR INSTR(LOWER(CLS.BROWSER),'spider')>0\n\t\tOR INSTR(LOWER(CLS.BROWSER),'java')>0 OR INSTR(LOWER(CLS.BROWSER),'python')>0\n\t\tOR INSTR(LOWER(CLS.BROWSER),'robot')>0 OR INSTR(LOWER(CLS.BROWSER),'curl')>0 OR INSTR(LOWER(CLS.BROWSER),'wget')>0\n\t\tTHEN 'other'\n\tELSE 'n.a.' END AS AGENT_BOT, -- Bot\n\tSTRFTIME('%s','now') AS UPDATE_DT\nFROM CLS\nLEFT OUTER JOIN DIM_USER_AGENT TRG\nON CLS.BROWSER = TRG.USER_AGENT_NK\nWHERE TRG.DIM_USER_AGENT_ID IS NULL<\/code><\/pre>\n
Gegevensintegratie<\/h4>\n
\nBevat de organisatie van de gegevensoverdracht van het besturingssysteem naar het rapport. Hiervoor moet een stagingtabel met een structuur die gelijk is aan de bron worden gemaakt.<\/p>\n
In de staging komt informatie over webpagina's uit de back-up van de CMS in de vorm van invoegverzoeken.<\/p>\n
Het laden van historische tabel DIM_REQUEST met basisgegevens gebeurt in drie stappen: laden van nieuwe sleutels en attributen, bijwerken van bestaande en vastleggen van verwijderde records.<\/p>\n
Laden van nieuwe records SCD2<\/b><\/p>\n
\/* Load request table SCD from master data *\/\nINSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT)\nWITH CLS  AS ( -- prepare keys\n\tSELECT\n\t'\/' || NAME AS REQUEST_NK,\n\tTITLE       AS PAGE_TITLE,\n\tCASE WHEN DESCRIPTION = '' OR DESCRIPTION IS NULL\n\t     THEN 'n.a.' ELSE DESCRIPTION\n\tEND AS PAGE_DESCR,\n\tCASE WHEN KEYWORDS = '' OR KEYWORDS IS NULL\n\t     THEN 'n.a.' ELSE KEYWORDS\n\tEND AS PAGE_KEYWORDS\n\tFROM STG_CMS_MENU\n\tWHERE CONTENT_TYPE != 'folder' -- only web pages\n\t  AND PAGE_TITLE != 'n.a.' -- master data which make sense\n)\n\/* new records from stage: CLS *\/\nSELECT\n\t-1 AS DIM_REQUEST_ID_HIST,\n\tCLS.REQUEST_NK,\n\tCLS.PAGE_TITLE,\n\tCLS.PAGE_DESCR,\n\tCLS.PAGE_KEYWORDS,\n\t0 AS DELETE_FLAG,\n\tSTRFTIME('%s','now') AS UPDATE_DT\nFROM CLS\nLEFT OUTER JOIN\n (\n\tSELECT\n\tDIM_REQUEST_ID,\n\tREQUEST_NK,\n\tPAGE_TITLE,\n\tPAGE_DESCR,\n\tPAGE_KEYWORDS\n\tFROM DIM_REQUEST_V_ACT\n) TRG ON CLS.REQUEST_NK = TRG.REQUEST_NK\nWHERE TRG.REQUEST_NK IS NULL -- no such record in data mart<\/code><\/pre>\n
Bijwerken van attributen SCD2<\/b><\/p>\n
\/* Load request table SCD from master data *\/\nINSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT)\nWITH CLS  AS ( -- prepare keys\n\tSELECT\n\t'\/' || NAME AS REQUEST_NK,\n\tTITLE       AS PAGE_TITLE,\n\tCASE WHEN DESCRIPTION = '' OR DESCRIPTION IS NULL\n\t     THEN 'n.a.' ELSE DESCRIPTION\n\tEND AS PAGE_DESCR,\n\tCASE WHEN KEYWORDS = '' OR KEYWORDS IS NULL\n\t     THEN 'n.a.' ELSE KEYWORDS\n\tEND AS PAGE_KEYWORDS\n\tFROM STG_CMS_MENU\n\tWHERE CONTENT_TYPE != 'folder' -- only web pages\n\t  AND PAGE_TITLE != 'n.a.' -- master data which make sense\n)\n\/* updated records from stage: CLS and build reference to history: HIST *\/\nSELECT\n\tHIST.DIM_REQUEST_ID AS DIM_REQUEST_ID_HIST,\n\tHIST.REQUEST_NK,\n\tCLS.PAGE_TITLE,\n\tCLS.PAGE_DESCR,\n\tCLS.PAGE_KEYWORDS,\n\t0 AS DELETE_FLAG,\n\tSTRFTIME('%s','now') AS UPDATE_DT\nFROM CLS,\n     DIM_REQUEST_V_ACT TRG,\n     DIM_REQUEST HIST\nWHERE CLS.REQUEST_NK = TRG.REQUEST_NK\n  AND TRG.DIM_REQUEST_ID = HIST.DIM_REQUEST_ID\n  AND ( CLS.PAGE_TITLE != HIST.PAGE_TITLE \/* changes only *\/\n     OR CLS.PAGE_DESCR != HIST.PAGE_DESCR\n     OR CLS.PAGE_KEYWORDS != HIST.PAGE_KEYWORDS )<\/code><\/pre>\n
Verwijderde records SCD2<\/b><\/p>\n
\/* Load request table SCD from master data *\/\nINSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT)\nWITH CLS  AS ( -- prepare keys\n\tSELECT\n\t'\/' || NAME AS REQUEST_NK,\n\tTITLE       AS PAGE_TITLE\n\tFROM STG_CMS_MENU\n\tWHERE CONTENT_TYPE != 'folder' -- only web pages\n\t  AND PAGE_TITLE != 'n.a.' -- master data which make sense\n)\n\/*  deleted records in data mart: TRG *\/\nSELECT\n\tTRG.DIM_REQUEST_ID AS DIM_REQUEST_ID_HIST,\n\tTRG.REQUEST_NK,\n\tTRG.PAGE_TITLE,\n\tTRG.PAGE_DESCR,\n\tTRG.PAGE_KEYWORDS,\n\t1 AS DELETE_FLAG,\n\tSTRFTIME('%s','now') AS UPDATE_DT\nFROM (\n\tSELECT\n\tDIM_REQUEST_ID,\n\tREQUEST_NK,\n\tPAGE_TITLE,\n\tPAGE_DESCR,\n\tPAGE_KEYWORDS\n\tFROM DIM_REQUEST_V_ACT\n\tWHERE PAGE_TITLE != 'n.a.' -- track master data only\n\t  AND DELETE_FLAG = 0 -- not already deleted\n) TRG\nLEFT OUTER JOIN CLS ON TRG.REQUEST_NK = CLS.REQUEST_NK\nWHERE CLS.REQUEST_NK IS NULL -- no such record in stage<\/code><\/pre>\n
Elke gegevensbron moet worden vergezeld van een formele beschrijving, bijvoorbeeld in een bestand readme.txt:<\/p>\n
Ontvanger van gegevens formeel\/technisch: naam, e-mailadres
\nLeverancier van gegevens formeel\/technisch: naam, e-mailadres
\nGegevensbron: bestandspad, namen van services
\nInformatie over toegang tot gegevens: gebruikers en wachtwoorden<\/p><\/blockquote>\n
\nHet schema van gegevensbeweging zal helpen in het proces van onderhoud en updates, bijvoorbeeld in tekstvorm:<\/p>\n
Bestand verplaatsen. Bron: ftp.domain.net: \/logs\/access.log Doel: \/var\/www\/access.log
\nLezen in de staging. Doel: STG_ACCESS_LOG
\nUploaden en transformeren. Doel: FCT_ACCESS_REQUEST_REF_HH
\nUploaden en transformeren. Doel: FCT_ACCESS_USER_AGENT_DD
\nRapport. Doel: \/var\/www\/report.html<\/p><\/blockquote>\n
Uitslag<\/h4>\n
\nDit artikel beschrijft mechanismen zoals de integratie van basisgegevens en de invoering van nieuwe aggregatieniveaus. Deze zijn nodig bij het opbouwen van datawarehouses om meer inzichten te verkrijgen en de kwaliteit van informatie te verbeteren.
\n
Bron: habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u0410\u043d\u0430\u043b\u0438\u0437\u0438\u0440\u0443\u044f \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0443 \u0441\u0430\u0439\u0442\u0430, \u043c\u044b \u043f\u043e\u043b\u0443\u0447\u0430\u0435\u043c \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u0435 \u043e \u0442\u043e\u043c, \u0447\u0442\u043e \u043f\u0440\u043e\u0438\u0441\u0445\u043e\u0434\u0438\u0442 \u0441 \u043d\u0438\u043c. \u0420\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u044b \u043c\u044b \u0441\u043e\u043f\u043e\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u043c \u0441 \u0434\u0440\u0443\u0433\u0438\u043c\u0438 \u0437\u043d\u0430\u043d\u0438\u044f\u043c\u0438 \u043e \u043f\u0440\u043e\u0434\u0443\u043a\u0442\u0435 \u0438\u043b\u0438 \u0441\u0435\u0440\u0432\u0438\u0441\u0435 \u0438 \u044d\u0442\u0438\u043c \u0443\u043b\u0443\u0447\u0448\u0430\u0435\u043c \u043d\u0430\u0448 \u043e\u043f\u044b\u0442. \u041a\u043e\u0433\u0434\u0430 \u0430\u043d\u0430\u043b\u0438\u0437 \u043f\u0435\u0440\u0432\u044b\u0445 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u043e\u0432 \u0437\u0430\u0432\u0435\u0440\u0448\u0451\u043d, \u043f\u0440\u043e\u0448\u043b\u043e \u043e\u0441\u043c\u044b\u0441\u043b\u0435\u043d\u0438\u0435 \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u0438 \u0438 \u0441\u0434\u0435\u043b\u0430\u043d\u044b \u0432\u044b\u0432\u043e\u0434\u044b, \u043d\u0430\u0447\u0438\u043d\u0430\u0435\u0442\u0441\u044f \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0439 \u044d\u0442\u0430\u043f. \u0412\u043e\u0437\u043d\u0438\u043a\u0430\u044e\u0442 \u0438\u0434\u0435\u0438: \u0430 \u0447\u0442\u043e \u0431\u0443\u0434\u0435\u0442, \u0435\u0441\u043b\u0438 \u043f\u043e\u0441\u043c\u043e\u0442\u0440\u0435\u0442\u044c \u043d\u0430 \u0434\u0430\u043d\u043d\u044b\u0435 \u0441 \u0434\u0440\u0443\u0433\u043e\u0439 \u0441\u0442\u043e\u0440\u043e\u043d\u044b? \u041d\u0430 \u044d\u0442\u043e\u043c […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":27858,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-37156","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Meer website-statistieken in je kleine opslag | ProHoster","description":"Door de website-statistieken te analyseren, krijgen we inzicht in wat er gebeurt.","canonical_url":"https:\/\/prohoster.info\/nl\/blog\/administrirovanie\/bolshe-statistiki-sajta-v-svoyom-malenkom-hranilishhe","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"nl_NL","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0411\u043e\u043b\u044c\u0448\u0435 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438 \u0441\u0430\u0439\u0442\u0430 \u0432 \u0441\u0432\u043e\u0451\u043c \u043c\u0430\u043b\u0435\u043d\u044c\u043a\u043e\u043c \u0445\u0440\u0430\u043d\u0438\u043b\u0438\u0449\u0435 | ProHoster","og:description":"\u0410\u043d\u0430\u043b\u0438\u0437\u0438\u0440\u0443\u044f \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0443 \u0441\u0430\u0439\u0442\u0430, \u043c\u044b \u043f\u043e\u043b\u0443\u0447\u0430\u0435\u043c \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u0435 \u043e \u0442\u043e\u043c, \u0447\u0442\u043e \u043f\u0440\u043e\u0438\u0441\u0445\u043e\u0434\u0438\u0442 \u0441 \u043d\u0438\u043c.","og:url":"https:\/\/prohoster.info\/nl\/blog\/administrirovanie\/bolshe-statistiki-sajta-v-svoyom-malenkom-hranilishhe","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T19:16:03+00:00","article:modified_time":"2019-10-31T19:16:03+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"37156","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-22 06:19:20","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-03-01 01:32:14","updated":"2026-01-22 06:19:20","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/posts\/37156","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/comments?post=37156"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/posts\/37156\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/media\/27858"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/media?parent=37156"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/categories?post=37156"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/nl\/wp-json\/wp\/v2\/tags?post=37156"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}