{"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\/sv\/blog\/administrirovanie\/bolshe-statistiki-sajta-v-svoyom-malenkom-hranilishhe","title":{"rendered":"Mer statistik om webbplatsen i sitt lilla f\u00f6rr\u00e5d","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Genom att analysera webbplatsstatistik f\u00e5r vi en f\u00f6rst\u00e5else f\u00f6r vad som h\u00e4nder med den. Resultaten j\u00e4mf\u00f6rs med annan kunskap om produkten eller tj\u00e4nsten, vilket f\u00f6rb\u00e4ttrar v\u00e5r erfarenhet.<\/p>\n

N\u00e4r analysen av de f\u00f6rsta resultaten \u00e4r klar, informationsbearbetning genomf\u00f6rd och slutsatser dragna, p\u00e5b\u00f6rjas n\u00e4sta steg. Id\u00e9er uppst\u00e5r: vad h\u00e4nder om vi betraktar datan fr\u00e5n ett annat perspektiv?<\/p>\n

I detta skede finns det begr\u00e4nsningar i analysverktygen. Detta \u00e4r en av orsakerna till att Google Analytics inte var tillr\u00e4ckligt f\u00f6r mig, p\u00e5 grund av den begr\u00e4nsade m\u00f6jligheten att se och manipulera sina data.<\/p>\n

Jag har alltid velat ladda ner grundl\u00e4ggande data (masterdata) snabbt, l\u00e4gga till en annan niv\u00e5 av aggregering eller p\u00e5 annat s\u00e4tt tolka befintliga v\u00e4rden.<\/p>\n

Det \u00e4r l\u00e4tt att g\u00f6ra i mitt lilla lagringsutrymme<\/a><\/noindex> baserat p\u00e5 access.log-filen, och f\u00f6r detta r\u00e4cker det med SQL.<\/a><\/noindex><\/p>\n

S\u00e5 vilka fr\u00e5gor ville jag hitta svar p\u00e5?<\/p>\n

Vad och n\u00e4r \u00e4ndrades p\u00e5 webbplatsen<\/h4>\n

\nHistoriken f\u00f6r \u00e4ndringar av grundl\u00e4ggande data (masterdata) \u00e4r alltid intressant.<\/p>\n

\"Mer
\n
\nSQL-fr\u00e5gan f\u00f6r rapporten<\/b><\/p>\n

SELECT\n\t1 as 'SideStackedBar: Inneh\u00e5llsuppdateringar per m\u00e5nad',\n\tstrftime('%m\/%Y', datetime(UPDATE_DT, 'unixepoch')) AS 'Dag',\n\tCOUNT(CASE WHEN PAGE_TITLE != 'n.a.' THEN DIM_REQUEST_ID END) AS 'Uppdateringar av webbsidor',\n\tCOUNT(CASE WHEN PAGE_DESCR = 'IMAGES' THEN DIM_REQUEST_ID END) AS 'Bilduppladdningar',\n\tCOUNT(CASE WHEN PAGE_DESCR = 'VIDEO' THEN DIM_REQUEST_ID END) AS 'Videouppladdningar',\n\tCOUNT(CASE WHEN PAGE_DESCR = 'AUDIO' THEN DIM_REQUEST_ID END) AS 'Ljuduppladdningar'\nFROM DIM_REQUEST\nWHERE PAGE_TITLE != 'n.a.' OR PAGE_DESCR != 'n.a.'\nGROUP BY strftime('%m\/%Y', datetime(UPDATE_DT, 'unixepoch'))\nORDER BY UPDATE_DT<\/code><\/pre>\n
Till exempel, vid en viss tidpunkt utf\u00f6rdes s\u00f6kmotoroptimering eller nytt inneh\u00e5ll lades till p\u00e5 webbplatsen, vilket f\u00f6rv\u00e4ntas leda till en \u00f6kning av trafiken.<\/p>\n
Anv\u00e4ndargrupper<\/h4>\n
\nDet enklaste exemplet p\u00e5 en grupp kan vara anv\u00e4ndaragenten eller namnet p\u00e5 operativsystemet. <\/p>\n
M\u00e4tningen av anv\u00e4ndaragenter har samlat in cirka tusen poster och jag var intresserad av att se dynamiken i f\u00f6rdelningen av agenter inom gruppen.<\/p>\n
\"Mer
\n
\nSQL-fr\u00e5gan f\u00f6r rapporten<\/b><\/p>\n
SELECT\n\t1 AS 'SideStackedBar: Anv\u00e4ndaragenter',\n\tAGENT_OS AS 'OS',\n\tSUM(CASE WHEN AGENT_BOT = 'n.a.' THEN 1 ELSE 0 END ) AS 'Anv\u00e4ndaragent f\u00f6r anv\u00e4ndare',\n\tSUM(CASE WHEN AGENT_BOT != 'n.a.' THEN 1 ELSE 0 END ) AS 'Anv\u00e4ndaragent f\u00f6r botar'\nFROM DIM_USER_AGENT\nWHERE DIM_USER_AGENT_ID != -1\nGROUP BY AGENT_OS\nORDER BY 3 DESC<\/code><\/pre>\n
De flesta olika kombinationerna av agenter kommer till webbplatsen fr\u00e5n Windowsv\u00e4rlden. Bland de ok\u00e4nda fanns s\u00e5dant som WhatsApp, PocketImageCache, PlayStation, SmartTV osv. <\/p>\n
Aktivitet hos anv\u00e4ndargrupper per vecka<\/h4>\n
\nGenom att sammanfoga vissa grupper kan vi observera f\u00f6rdelningen av deras aktivitet.<\/p>\n
Till exempel, anv\u00e4ndare av Linux-klustret f\u00f6rbrukar mer trafik p\u00e5 webbplatsen \u00e4n alla andra.<\/p>\n
\"Mer
\n
\nSQL-fr\u00e5gan f\u00f6r rapporten<\/b><\/p>\n
SELECT\n1 as 'StackedBar: Trafikvolym efter anv\u00e4ndar-OS och vecka',\nstrftime('%W vecka', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Vecka',\nSUM(CASE WHEN USG.AGENT_OS IN ('Android', 'Linux') THEN FCT.BYTES ELSE 0 END)\/1000 AS 'Android\/Linux-anv\u00e4ndare',\nSUM(CASE WHEN USG.AGENT_OS IN ('Windows') THEN FCT.BYTES ELSE 0 END)\/1000 AS 'Windows-anv\u00e4ndare',\nSUM(CASE WHEN USG.AGENT_OS IN ('Macintosh', 'iOS') THEN FCT.BYTES ELSE 0 END)\/1000 AS 'Mac\/iOS-anv\u00e4ndare',\nSUM(CASE WHEN USG.AGENT_OS IN ('n.a.', 'BlackBerry') THEN FCT.BYTES ELSE 0 END)\/1000 AS '\u00d6vriga'\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.' \/* anv\u00e4ndare endast *\/\n  AND HST.STATUS_GROUP IN ('Framg\u00e5ngsrik') \/* bra sidor *\/\n  AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month')\nGROUP BY strftime('%W vecka', datetime(FCT.EVENT_DT, 'unixepoch'))\nORDER BY FCT.EVENT_DT<\/code><\/pre>\n
Intensiv trafikf\u00f6rbrukning<\/h4>\n
\nTabellen visar de mest aktiva anv\u00e4ndargrupperna och deras aktivitetsdag.
\nDe mest aktiva tillh\u00f6r Linux-klustret.<\/p>\n
\"Mer
\n
\nSQL-fr\u00e5gan f\u00f6r rapporten<\/b><\/p>\n
SELECT\n1 AS 'Tabell: Anv\u00e4ndaragent med h\u00f6g anv\u00e4ndning',\nstrftime('%d.%m.%Y', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Dag',\nROUND(1.0*SUM(FCT.BYTES)\/1000000, 1) AS 'Trafik MB',\nROUND(1.0*SUM(FCT.IP_CNT)\/SUM(1), 1) AS 'IP-adresser',\nROUND(1.0*SUM(FCT.REQUEST_CNT)\/SUM(1), 1) AS 'F\u00f6rfr\u00e5gningar',\nUSA.DIM_USER_AGENT_ID AS 'ID',\nMAX(USA.USER_AGENT_NK) AS 'Anv\u00e4ndaragent',\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
Genom att anv\u00e4nda attributen dag och ID f\u00f6r agenten kan man snabbt hitta och f\u00f6lja statistiken dag f\u00f6r dag f\u00f6r specifika anv\u00e4ndargrupper. Vid behov kan detaljerad information snabbt hittas i staging-tabellen.<\/p>\n
Hur f\u00e5r man information?<\/h4>\n
\nInformationen fr\u00e5n filen access.log<\/a><\/noindex> kan g\u00f6ras \u00e4nnu mer effektiv genom att integrera ytterligare datak\u00e4llor, inf\u00f6ra nya aggregations- och grupperingsniv\u00e5er.<\/p>\n
Grunddata och enheter<\/h4>\n
\nTill grunddata h\u00f6r information om enheter: webbsidor, bilder, video och ljudinneh\u00e5ll, och i fallet med en butik \u2014 produkter. <\/p>\n
Sj\u00e4lva enheterna fungerar som m\u00e5tt, och processen f\u00f6r att spara f\u00f6r\u00e4ndringar i attributen kallas historisering. I databasen implementeras denna process ofta som l\u00e5ngsamt f\u00f6r\u00e4nderliga dimensioner (SCD).<\/p>\n
K\u00e4llan till grunddata kan vara olika system, s\u00e5 de beh\u00f6ver n\u00e4stan alltid integreras.<\/p>\n
L\u00e5ngsamt f\u00f6r\u00e4ndrande dimension<\/h4>\n
\nDimensionen DIM_REQUEST kommer att inneh\u00e5lla information om f\u00f6rfr\u00e5gningar p\u00e5 webbplatsen i historisk form.<\/p>\n
Tabell SCD2<\/b><\/p>\n
SKAPA 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
Dessutom kommer vi att skapa en vy som alltid visar alla poster i det senaste tillst\u00e5ndet. Detta \u00e4r n\u00f6dv\u00e4ndigt f\u00f6r att ladda sj\u00e4lva dimensionen.<\/p>\n
\"Mer
\n
\nAktuell vy 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
Och en vy d\u00e4r historisk information f\u00f6r varje post samlas. Detta \u00e4r n\u00f6dv\u00e4ndigt f\u00f6r att skapa en historiskt korrekt koppling till fakta.<\/p>\n
\"Mer
\n
\nHistorisk vy 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
Databearbetning<\/h4>\n
\nKompaktering (databearbetning) g\u00f6r det m\u00f6jligt att bed\u00f6ma data p\u00e5 en h\u00f6gre niv\u00e5 och uppt\u00e4cka anomalier och trender som inte syns i detaljrapporter.<\/p>\n
Till exempel, i dimensionen med statuskoder DIM_HTTP_STATUS kommer vi att l\u00e4gga till en grupp:<\/p>\n
STATUS \/ GRUPP
\n0xx \/ n.a.
\n1xx \/ Informativ
\n2xx \/ Framg\u00e5ngsrik
\n3xx \/ Omdirigering
\n4xx \/ Klientfel
\n5xx \/ Serverfel<\/p><\/blockquote>\n
 Dimensionen f\u00f6r anv\u00e4ndaragenter DIM_USER_AGENT kommer att inneh\u00e5lla attributen AGENT_OS och AGENT_BOT, som ansvarar f\u00f6r grupperna. Dessa kan fyllas i under ETL-processen:<\/p>\n
Ladda 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
Dataintegration<\/h4>\n
\nInkluderar organisationen av datatransfer fr\u00e5n operativsystemet till rapporteringen. F\u00f6r detta \u00e4ndam\u00e5l m\u00e5ste vi skapa en staging-tabell med en struktur som liknar k\u00e4llan.<\/p>\n
I staging f\u00e5r informationen om webbsidor fr\u00e5n backupen av CMS i form av ins\u00e4ttningsfr\u00e5gor.<\/p>\n
Laddningen av den historiska tabellen DIM_REQUEST med grunddata sker i tre steg: laddning av nya nycklar och attribut, uppdatering av befintliga och registrering av borttagna poster.<\/p>\n
Ladda nya poster 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
Uppdatera attribut 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
Borttagna poster 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
Varje datak\u00e4lla m\u00e5ste \u00e5tf\u00f6ljas av en formell beskrivning, till exempel i en fil readme.txt:<\/p>\n
Mottagare av data formellt \/ tekniskt: namn, e-postadress
\nLeverant\u00f6r av data formellt \/ tekniskt: namn, e-postadress
\nDatak\u00e4lla: s\u00f6kv\u00e4g till filen, tj\u00e4nsternas namn
\nInformation om \u00e5tkomst till data: anv\u00e4ndare och l\u00f6senord<\/p><\/blockquote>\n
\nDatafl\u00f6desschemat kommer att hj\u00e4lpa vid underh\u00e5ll och uppdateringar, till exempel i textformat:<\/p>\n
Flytt av fil. K\u00e4lla: ftp.domain.net: \/logs\/access.log M\u00e5l: \/var\/www\/access.log
\nL\u00e4sa i staging. M\u00e5l: STG_ACCESS_LOG
\nLaddning och transformering. Syfte: FCT_ACCESS_REQUEST_REF_HH
\nLaddning och transformering. Syfte: FCT_ACCESS_USER_AGENT_DD
\nRapport. Syfte: \/var\/www\/report.html<\/p><\/blockquote>\n
Utdata<\/h4>\n
\nArtikeln beskriver mekanismer som integration av grunddata och inf\u00f6rande av nya niv\u00e5er av aggregering. Dessa \u00e4r n\u00f6dv\u00e4ndiga vid byggandet av datalager f\u00f6r att f\u00e5 ytterligare insikter och f\u00f6rb\u00e4ttra informationskvaliteten.
\n
K\u00e4lla: 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\udd47Mer webbplatsstatistik i ditt lilla datalager | ProHoster","description":"Genom att analysera webbplatsstatistik f\u00e5r vi en inblick i vad som h\u00e4nder med den.","canonical_url":"https:\/\/prohoster.info\/sv\/blog\/administrirovanie\/bolshe-statistiki-sajta-v-svoyom-malenkom-hranilishhe","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sv_SE","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\/sv\/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\/sv\/wp-json\/wp\/v2\/posts\/37156","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/comments?post=37156"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/posts\/37156\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/media\/27858"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/media?parent=37156"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/categories?post=37156"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/tags?post=37156"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}