{"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\/et\/blog\/administrirovanie\/bolshe-statistiki-sajta-v-svoyom-malenkom-hranilishhe","title":{"rendered":"Rohkem veebisaidi statistikat oma v\u00e4ikeses hoidlates","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Anal\u00fc\u00fcsides saidi statistikat, saame \u00fclevaate sellest, mis sellel toimub. Tulemusi seostatakse teiste teadmistega tootest v\u00f5i teenusest ning see parandab meie kogemust.<\/p>\n

Kui esimese anal\u00fc\u00fcsi tulemused on l\u00f5ppenud, on teave m\u00f5istetud ja j\u00e4reldused tehtud, algab j\u00e4rgmine etapp. Tekivad ideed: mis juhtuks, kui vaadata andmeid teisest k\u00fcljest?<\/p>\n

Sellel etapil on anal\u00fc\u00fcsit\u00f6\u00f6riistadega piirangud. See on \u00fcks p\u00f5hjus, miks Google Analytics ei olnud mulle piisav t\u00f6\u00f6riist, kuna sellel on piiratud v\u00f5imalus n\u00e4ha oma andmeid ja nendega manipuleerida.<\/p>\n

Olen alati soovinud kiiresti laadida p\u00f5hiteavet (master-datas), lisada teisest tasemest koondust v\u00f5i t\u00f5lgendada olemasolevaid v\u00e4\u00e4rtusi muul moel.<\/p>\n

Seda on lihtne teha oma v\u00e4ikestes andmehoidlas<\/a><\/noindex> access.log faili p\u00f5hjal ja selleks on piisav SQL keel.<\/a><\/noindex><\/p>\n

Nii et millistele k\u00fcsimustele ma tahtsin vastuseid leida?<\/p>\n

Mis ja millal saidil muutus<\/h4>\n

\nP\u00f5hiteabe (master-data) muudatuste ajalugu on alati huvitav.<\/p>\n

\"Rohkem
\n
\nSQL aruanne<\/b><\/p>\n

SELECT\n\t1 as 'SideStackedBar: Sisu uuendamise kuudega',\n\tstrftime('%m\/%Y', datetime(UPDATE_DT, 'unixepoch')) AS 'P\u00e4ev',\n\tCOUNT(CASE WHEN PAGE_TITLE != 'n.a.' THEN DIM_REQUEST_ID END) AS 'Veebilehe uuendused',\n\tCOUNT(CASE WHEN PAGE_DESCR = 'IMAGES' THEN DIM_REQUEST_ID END) AS 'Pildi \u00fcleslaamised',\n\tCOUNT(CASE WHEN PAGE_DESCR = 'VIDEO' THEN DIM_REQUEST_ID END) AS 'Video \u00fcleslaamised',\n\tCOUNT(CASE WHEN PAGE_DESCR = 'AUDIO' THEN DIM_REQUEST_ID END) AS 'Heli \u00fcleslaamised'\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
N\u00e4iteks, m\u00f5nel hetkel tehti saidi otsingumootori optimeerimist v\u00f5i lisati uusi sisu ning seet\u00f5ttu oodatakse liikluse suurenemist.<\/p>\n
Kasutajagruppide<\/h4>\n
\nLihtsaimaksemiseks kasutajagrupiks v\u00f5ib olla kasutajaagent v\u00f5i operatsioonis\u00fcsteemi nimetus. <\/p>\n
Kasutajaagentide m\u00f5\u00f5tmine on kogunud umbes tuhat salvestust ning mind huvitab, kuidas on agentide jaotus grupi piires.<\/p>\n
\"Rohkem
\n
\nSQL aruanne<\/b><\/p>\n
SELECT\n\t1 AS 'SideStackedBar: Kasutajaagendid',\n\tAGENT_OS AS 'OS',\n\tSUM(CASE WHEN AGENT_BOT = 'n.a.' THEN 1 ELSE 0 END ) AS 'Kasutajaagentide kasutajad',\n\tSUM(CASE WHEN AGENT_BOT != 'n.a.' THEN 1 ELSE 0 END ) AS 'Kasutajaagentide robotid'\nFROM DIM_USER_AGENT\nWHERE DIM_USER_AGENT_ID != -1\nGROUP BY AGENT_OS\nORDER BY 3 DESC<\/code><\/pre>\n
Suurim arv erinevaid agentide kombinatsioone tuleb saidile Windowsi maailmast. M\u00e4\u00e4ratlemata on sellised, nagu WhatsApp, PocketImageCache, PlayStation, SmartTV jne. <\/p>\n
Kasutajagruppide aktiivsus n\u00e4dalate l\u00f5ikes<\/h4>\n
\nM\u00f5ned gruppide \u00fchendamisega on v\u00f5imalik j\u00e4lgida nende aktiivsuse jaotust.<\/p>\n
N\u00e4iteks kasutavad Linuxi klusteri kasutajad veebisaiti rohkem kui k\u00f5ik teised.<\/p>\n
\"Rohkem
\n
\nSQL aruanne<\/b><\/p>\n
SELECT\n1 AS 'StackedBar: K\u00fclastuste maht kasutaja operatsioonis\u00fcsteemi ja n\u00e4dala kohta',\nstrftime('%W n\u00e4dal', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'N\u00e4dal',\nSUM(CASE WHEN USG.AGENT_OS IN ('Android', 'Linux') THEN FCT.BYTES ELSE 0 END) \/ 1000 AS 'Androidi\/Linuxi kasutajad',\nSUM(CASE WHEN USG.AGENT_OS IN ('Windows') THEN FCT.BYTES ELSE 0 END) \/ 1000 AS 'Windowsi kasutajad',\nSUM(CASE WHEN USG.AGENT_OS IN ('Macintosh', 'iOS') THEN FCT.BYTES ELSE 0 END) \/ 1000 AS 'Mac\/iOS kasutajad',\nSUM(CASE WHEN USG.AGENT_OS IN ('n.a.', 'BlackBerry') THEN FCT.BYTES ELSE 0 END) \/ 1000 AS 'Muud'\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.' \/* ainult kasutajad *\/\n  AND HST.STATUS_GROUP IN ('Edulood') \/* head lehed *\/\n  AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month')\nGROUP BY strftime('%W n\u00e4dal', datetime(FCT.EVENT_DT, 'unixepoch'))\nORDER BY FCT.EVENT_DT<\/code><\/pre>\n
Intensiivne liikluse tarbimine<\/h4>\n
\nTabelist on n\u00e4ha k\u00f5ige aktiivsemad kasutajagruppide r\u00fchmad ja nende aktiivsuse p\u00e4ev.
\nK\u00f5ige aktiivsemad kuuluvad Linuxi klastrisse.<\/p>\n
\"Rohkem
\n
\nSQL aruanne<\/b><\/p>\n
SELECT\n1 AS 'Tabel: Kasutajaagent k\u00f5rge kasutusega',\nstrftime('%d.%m.%Y', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'P\u00e4ev',\nROUND(1.0 * SUM(FCT.BYTES) \/ 1000000, 1) AS 'Liiklus MB',\nROUND(1.0 * SUM(FCT.IP_CNT) \/ SUM(1), 1) AS 'IP-d',\nROUND(1.0 * SUM(FCT.REQUEST_CNT) \/ SUM(1), 1) AS 'P\u00e4ringud',\nUSA.DIM_USER_AGENT_ID AS 'ID',\nMAX(USA.USER_AGENT_NK) AS 'Kasutajaagent',\nMAX(USA.AGENT_BOT) AS 'Robot'\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
Kasutades p\u00e4eva ja agendi ID atribuute, on v\u00f5imalik kiiresti leida ja j\u00e4lgida statistikat p\u00e4eva j\u00e4rgi eraldi kasutajagruppide kohta. Vajadusel saab kiiresti leida detailsed andmed stseenitabelist.<\/p>\n
Kuidas infot saada?<\/h4>\n
\nTeavet failist access.log<\/a><\/noindex> saab teha veelgi efektiivsemaks, kui integreerida t\u00e4iendavad andmeallikad, luua uusi tasemeid agregatsiooni ja gruppeerimise jaoks.<\/p>\n
P\u00f5hiandmed ja entiteedid<\/h4>\n
\nP\u00f5hiandmed h\u00f5lmavad teavet entiteetide kohta: veebilehed, pildid, videod ja heli sisu, kaupluse puhul - tooted. <\/p>\n
Entiteedid toimivad m\u00f5\u00f5tmetena, samas kui atribuutide muutuste salvestamise protsessi nimetatakse ajalooliseks. Andmebaasis rakendatakse seda protsessi sageli aeglaselt muutuva m\u00f5\u00f5tmena (SCD).<\/p>\n
P\u00f5hiandmete allikad v\u00f5ivad olla v\u00e4ga erinevad s\u00fcsteemid, seega on peaaegu alati vajalikud nende integreerimist.<\/p>\n
Aeglaselt muutuva m\u00f5\u00f5tme<\/h4>\n
\nM\u00f5\u00f5de DIM_REQUEST sisaldab teavet veebisaidi p\u00e4ringute kohta ajaloolises vormis.<\/p>\n
SCD2 tabel<\/b><\/p>\n
Loo tabel DIM_REQUEST ( \n  DIM_REQUEST_ID      INTEGER EI OLEKUL PRIMARY KEY AUTOINCREMENT,\n  DIM_REQUEST_ID_HIST INTEGER EI OLEKUL DEFAULT -1,\n  REQUEST_NK          TEXT EI OLEKUL DEFAULT 'n.a.', \n  PAGE_TITLE          TEXT EI OLEKUL DEFAULT 'n.a.',\n  PAGE_DESCR          TEXT EI OLEKUL DEFAULT 'n.a.',\n  PAGE_KEYWORDS       TEXT EI OLEKUL DEFAULT 'n.a.',\n  DELETE_FLAG         INTEGER EI OLEKUL DEFAULT 0,\n  UPDATE_DT           INTEGER EI OLEKUL DEFAULT 0,\n  UNIQUE (REQUEST_NK, DIM_REQUEST_ID_HIST)\n);\nINSERT INTO DIM_REQUEST (DIM_REQUEST_ID) VALUES (-1);<\/code><\/pre>\n
Lisaks sellele loome \u00fche vaate, mis alati kuvab k\u00f5ik kirjed viimases olekus. See on vajalik m\u00f5\u00f5tmise laadimiseks.<\/p>\n
\"Rohkem
\n
\nAktuaalne SCD2 vaade<\/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
Ja vaade, kus iga kirje jaoks on kogutud ajalooline teave. See on vajalik ajalooliselt t\u00e4pse seose loomiseks faktidega.<\/p>\n
\"Rohkem
\n
\nAjalooline SCD2 vaade<\/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
Andmete aggregeerimine<\/h4>\n
\nAjamise (aggregeerimise) abil on v\u00f5imalik hinnata andmeid k\u00f5rgemal tasemel ja avastada anomaaliaid ning suundi, mis ei ole detailses aruandes n\u00e4htavad.<\/p>\n
N\u00e4iteks lisame m\u00f5\u00f5tmisse DIM_HTTP_STATUS staatuste koodide grupi:<\/p>\n
STATUS \/ GROUP
\n0xx \/ n.a.
\n1xx \/ Informatiivne
\n2xx \/ Edutav
\n3xx \/ Suunamine
\n4xx \/ Kliendi viga
\n5xx \/ Serveri viga<\/p><\/blockquote>\n
 Kasutajaagentide m\u00f5\u00f5tmine DIM_USER_AGENT sisaldab atribuute AGENT_OS ja AGENT_BOT, mis vastutavad gruppide eest. Need saab t\u00e4ita ETL protsessi k\u00e4igus:<\/p>\n
DIM_USER_AGENTi laadimine<\/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
Andmete integreerimine<\/h4>\n
\nSee h\u00f5lmab andmete edastamise korraldamist operatsioonis\u00fcsteemist aruandesse. Selleks tuleb luua etapi tabel struktuuriga, mis on sarnane allikale.<\/p>\n
Etapis satub teave veebilehtede kohta CMS-i varukoopiast sisestamissoovide vormis.<\/p>\n
Ajaloolise tabeli DIM_REQUEST laadimine p\u00f5hiteabega toimub kolmes etapis: uute v\u00f5tmete ja atribuutide laadimine, olemasolevate v\u00e4rskendamine ja kustutatud kirje s\u00e4ilitamine.<\/p>\n
Uute kirjete laadimine 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
SCD2 atribuutide v\u00e4rskendamine<\/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
Kustutatud kirjed 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
Iga andmeallika juurde tuleb lisada ametlik kirjeldus, n\u00e4iteks failis readme.txt:<\/p>\n
Andmete saaja ametlikult\/tehniliselt: nimi, e-posti aadress
\nAndmete pakkumise ametlikult\/tehniliselt: nimi, e-posti aadress
\nAndmeallikas: faili tee, teenuste nimed
\nTeave andmetele juurdep\u00e4\u00e4su kohta: kasutajad ja paroolid<\/p><\/blockquote>\n
\nAndmete liikumise skeem aitab hooldamise ja v\u00e4rskendamise protsessis, n\u00e4iteks tekstivormingus:<\/p>\n
Faili liikumine. Allikas: ftp.domain.net: \/logs\/access.log Siht: \/var\/www\/access.log
\nLugemine etapis. Siht: STG_ACCESS_LOG
\nLaadimine ja transformatsioon. Eesm\u00e4rk: FCT_ACCESS_REQUEST_REF_HH
\nLaadimine ja transformatsioon. Eesm\u00e4rk: FCT_ACCESS_USER_AGENT_DD
\nAruanne. Eesm\u00e4rk: \/var\/www\/report.html<\/p><\/blockquote>\n
Kokkuv\u00f5te<\/h4>\n
\nSeega kirjeldab artikkel selliseid mehhanisme nagu baasandmete integreerimine ja uute agregatsioonitasemete rakendamine. Need on vajalikud andmehoidlate loomisel, et saada lisateavet ja parandada teabe kvaliteeti.
\n
Allikas: 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\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\udd47Rohkem veebisaidi statistikat oma v\u00e4ikeses andmehoidlas | ProHoster","description":"Anal\u00fc\u00fcsides veebisaidi statistikat, saame \u00fclevaate sellest, mis temaga toimub.","canonical_url":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/bolshe-statistiki-sajta-v-svoyom-malenkom-hranilishhe","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"et_EE","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\/et\/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\/et\/wp-json\/wp\/v2\/posts\/37156","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/comments?post=37156"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/37156\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media\/27858"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media?parent=37156"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/categories?post=37156"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/tags?post=37156"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}