{"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\/ro\/blog\/administrirovanie\/bolshe-statistiki-sajta-v-svoyom-malenkom-hranilishhe","title":{"rendered":"Mai multe statistici ale site-ului \u00een propriul s\u0103u mic depozit","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Analiz\u00e2nd statisticile site-ului, ob\u021binem o imagine despre ce se \u00eent\u00e2mpl\u0103 cu acesta. Rezultatele sunt corelate cu alte cuno\u0219tin\u021be despre produs sau serviciu, \u00eembun\u0103t\u0103\u021bind astfel experien\u021ba noastr\u0103.<\/p>\n

Dup\u0103 ce analiza primelor rezultate este finalizat\u0103, informa\u021bia este procesat\u0103 \u0219i se fac concluzii, \u00eencepe urm\u0103toarea etap\u0103. Apar idei: cum ar fi dac\u0103 am privi datele dintr-o alt\u0103 perspectiv\u0103?<\/p>\n

\u00cen aceast\u0103 etap\u0103 exist\u0103 limit\u0103ri ale instrumentelor de analiz\u0103. Aceasta este una dintre motivele pentru care instrumentul Google Analytics nu mi-a fost suficient, \u0219i anume, din cauza capacit\u0103\u021bii limitate de a vedea datele mele \u0219i de a le manipula.<\/p>\n

\u00centotdeauna mi-am dorit s\u0103 \u00eencarc rapid datele de baz\u0103 (meta-date), s\u0103 adaug un alt nivel de agregare sau s\u0103 interpretez diferit valorile existente.<\/p>\n

Este u\u0219or de realizat \u00een micile tale depozite<\/a><\/noindex> pe baza fi\u0219ierului access.log, \u0219i este suficient s\u0103 folose\u0219ti limbajul SQL.<\/a><\/noindex><\/p>\n

Deci, la ce \u00eentreb\u0103ri mi-ar pl\u0103cea s\u0103 g\u0103sesc r\u0103spuns?<\/p>\n

Ce \u0219i c\u00e2nd s-a schimbat pe site<\/h4>\n

\nIstoria modific\u0103rilor datelor de baz\u0103 (meta-datelor) este \u00eentotdeauna interesant\u0103.<\/p>\n

\"Mai
\n
\nInterogarea SQL a raportului<\/b><\/p>\n

SELECT\n\t1 as 'SideStackedBar: Actualiz\u0103ri de Con\u021binut pe Luni',\n\tstrftime('%m\/%Y', datetime(UPDATE_DT, 'unixepoch')) AS 'Luna',\n\tCOUNT(CASE WHEN PAGE_TITLE != 'n.a.' THEN DIM_REQUEST_ID END) AS 'Actualiz\u0103ri de pagini web',\n\tCOUNT(CASE WHEN PAGE_DESCR = 'IMAGES' THEN DIM_REQUEST_ID END) AS '\u00cenc\u0103rc\u0103ri de imagini',\n\tCOUNT(CASE WHEN PAGE_DESCR = 'VIDEO' THEN DIM_REQUEST_ID END) AS '\u00cenc\u0103rc\u0103ri de video',\n\tCOUNT(CASE WHEN PAGE_DESCR = 'AUDIO' THEN DIM_REQUEST_ID END) AS '\u00cenc\u0103rc\u0103ri de audio'\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
De exemplu, la un moment dat, a fost efectuat\u0103 o optimizare a motorului de c\u0103utare sau a fost ad\u0103ugat un nou con\u021binut pe site, ca urmare, se a\u0219teapt\u0103 o cre\u0219tere a traficului.<\/p>\n
Grupurile de utilizatori<\/h4>\n
\nCel mai simplu exemplu de grup poate fi agentul utilizator sau numele sistemului de operare. <\/p>\n
M\u0103surarea agen\u021bilor utilizatori a acumulat aproximativ o mie de \u00eenregistr\u0103ri, iar eu eram curios s\u0103 v\u0103d dinamica distribu\u021biei agen\u021bilor \u00een cadrul grupului.<\/p>\n
\"Mai
\n
\nInterogarea SQL a raportului<\/b><\/p>\n
SELECT\n\t1 AS 'SideStackedBar: Agen\u021bi de Utilizatori',\n\tAGENT_OS AS 'OS',\n\tSUM(CASE WHEN AGENT_BOT = 'n.a.' THEN 1 ELSE 0 END) AS 'Agent utilizator al Utilizatorilor',\n\tSUM(CASE WHEN AGENT_BOT != 'n.a.' THEN 1 ELSE 0 END) AS 'Agent utilizator al Bo\u021bilor'\nFROM DIM_USER_AGENT\nWHERE DIM_USER_AGENT_ID != -1\nGROUP BY AGENT_OS\nORDER BY 3 DESC<\/code><\/pre>\n
Cea mai mare diversitate de combina\u021bii de agen\u021bi vine pe site din lumea Windows. Printre cele nedefinite se num\u0103r\u0103 aplica\u021bii precum WhatsApp, PocketImageCache, PlayStation, SmartTV etc. <\/p>\n
Activitatea grupurilor de utilizatori pe s\u0103pt\u0103m\u00e2ni<\/h4>\n
\nPrin unificarea unor grupuri, se poate observa distribu\u021bia activit\u0103\u021bii lor.<\/p>\n
De exemplu, utilizatorii clusterului Linux consum\u0103 mai mult trafic pe site dec\u00e2t to\u021bi ceilal\u021bi.<\/p>\n
\"Mai
\n
\nInterogarea SQL a raportului<\/b><\/p>\n
SELECT\n1 as 'StackedBar: Volumul de trafic dup\u0103 OS-ul utilizatorilor \u0219i s\u0103pt\u0103m\u00e2n\u0103',\nstrftime('%W s\u0103pt\u0103m\u00e2na', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'S\u0103pt\u0103m\u00e2n\u0103',\nSUM(CASE WHEN USG.AGENT_OS IN ('Android', 'Linux') THEN FCT.BYTES ELSE 0 END) \/ 1000 AS 'Utilizatori Android\/Linux',\nSUM(CASE WHEN USG.AGENT_OS IN ('Windows') THEN FCT.BYTES ELSE 0 END) \/ 1000 AS 'Utilizatori Windows',\nSUM(CASE WHEN USG.AGENT_OS IN ('Macintosh', 'iOS') THEN FCT.BYTES ELSE 0 END) \/ 1000 AS 'Utilizatori Mac\/iOS',\nSUM(CASE WHEN USG.AGENT_OS IN ('n.a.', 'BlackBerry') THEN FCT.BYTES ELSE 0 END) \/ 1000 AS 'Altele'\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.' \n  AND HST.STATUS_GROUP IN ('Successful') \n  AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month')\nGROUP BY strftime('%W s\u0103pt\u0103m\u00e2na', datetime(FCT.EVENT_DT, 'unixepoch'))\nORDER BY FCT.EVENT_DT<\/code><\/pre>\n
Consumul intens de trafic<\/h4>\n
\nDin tabel se observ\u0103 cele mai active grupuri de utilizatori \u0219i ziua activit\u0103\u021bii lor.
\nCele mai active apar\u021bin clusterului Linux.<\/p>\n
\"Mai
\n
\nInterogarea SQL a raportului<\/b><\/p>\n
SELECT\n1 AS 'Table: Agent de utilizator cu utilizare intens\u0103',\nstrftime('%d.%m.%Y', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Zi',\nROUND(1.0 * SUM(FCT.BYTES) \/ 1000000, 1) AS 'Trafic MB',\nROUND(1.0 * SUM(FCT.IP_CNT) \/ SUM(1), 1) AS 'IP-uri',\nROUND(1.0 * SUM(FCT.REQUEST_CNT) \/ SUM(1), 1) AS 'Cereri',\nUSA.DIM_USER_AGENT_ID AS 'ID',\nMAX(USA.USER_AGENT_NK) AS 'Agent de utilizator',\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
Folosind atributele zi \u0219i ID-ul agentului, exist\u0103 posibilitatea de a g\u0103si rapid \u0219i de a urm\u0103ri statisticile pe zile pentru grupurile individuale de utilizatori. Dac\u0103 este necesar, se poate g\u0103si rapid informa\u021bia detaliat\u0103 \u00een tabelul de stadiu.<\/p>\n
Cum ob\u021binem informa\u021bii?<\/h4>\n
\nInforma\u021bia din fi\u0219ierul access.log<\/a><\/noindex> poate deveni \u0219i mai eficient\u0103 dac\u0103 se integreaz\u0103 surse de date suplimentare, se introduce noi niveluri de agregare \u0219i grupare.<\/p>\n
Datele \u0219i entit\u0103\u021bile de baz\u0103<\/h4>\n
\nDatele de baz\u0103 includ informa\u021bii despre entit\u0103\u021bi: pagini web, imagini, con\u021binut video \u0219i audio, iar \u00een cazul unui magazin - produse. <\/p>\n
Entit\u0103\u021bile \u00een sine joac\u0103 rolul de dimensiuni, iar procesul de p\u0103strare a modific\u0103rilor atributelor se nume\u0219te istorizare. \u00cen baza de date, acest proces se realizeaz\u0103 adesea sub forma dimensiunilor cu modific\u0103ri lente (SCD).<\/p>\n
Sursa datelor de baz\u0103 poate proveni din sisteme foarte diverse, a\u0219a c\u0103 aproape \u00eentotdeauna este necesar\u0103 integrarea lor.<\/p>\n
Dimensiune cu modific\u0103ri lente<\/h4>\n
\nDimensiunea DIM_REQUEST va con\u021bine informa\u021bii despre cererile pe site \u00een form\u0103 istoric\u0103.<\/p>\n
Tabelul SCD2<\/b><\/p>\n
CREA\u021aI TABELUL 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
\u00cen plus, vom crea o vedere care afi\u0219eaz\u0103 \u00eentotdeauna toate \u00eenregistr\u0103rile \u00een starea cea mai recent\u0103. Acest lucru este necesar pentru a \u00eenc\u0103rca dimensiunea \u00een sine.<\/p>\n
\"Mai
\n
\nVederea actualizat\u0103 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
\u0218i o vedere \u00een care pentru fiecare \u00eenregistrare a fost adunat\u0103 informa\u021bia istoric\u0103. Este necesar\u0103 pentru a construi o leg\u0103tur\u0103 istoric corect\u0103 cu faptele.<\/p>\n
\"Mai
\n
\nVederea istoric\u0103 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
Agregarea datelor<\/h4>\n
\nCompresia (agregarea) permite evaluarea datelor la un nivel mai \u00eenalt \u0219i detectarea anomaliilor \u0219i tendin\u021belor care nu sunt vizibile \u00een rapoartele detaliate.<\/p>\n
De exemplu, \u00een dimensiunea cu codurile de statut pentru cereri DIM_HTTP_STATUS vom ad\u0103uga un grup:<\/p>\n
STATUS \/ GROUP
\n0xx \/ n.a.
\n1xx \/ Informa\u021bional
\n2xx \/ Succes
\n3xx \/ Redirectare
\n4xx \/ Eroare client
\n5xx \/ Eroare server<\/p><\/blockquote>\n
 Dimensiunea agen\u021bilor de utilizator DIM_USER_AGENT va con\u021bine atributele AGENT_OS \u0219i AGENT_BOT, care sunt responsabil pentru grupuri. Acestea pot fi completate \u00een timpul procesului ETL:<\/p>\n
\u00cenc\u0103rcarea 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
Integrarea datelor<\/h4>\n
\nInclude organizarea transferului de date din sistemul de operare \u00een raport. Pentru aceasta, este necesar s\u0103 crea\u021bi un tabel de stadiu cu o structur\u0103 similar\u0103 sursei.<\/p>\n
\u00cen stadiu, informa\u021bia despre paginile web ajunge din backup-ul CMS sub form\u0103 de cereri de inserare.<\/p>\n
\u00cenc\u0103rcarea tabelului istoric DIM_REQUEST cu date de baz\u0103 se face \u00een trei etape: \u00eenc\u0103rcarea de noi chei \u0219i atribute, actualizarea celor existente \u0219i fixarea \u00eenregistr\u0103rilor \u0219terse.<\/p>\n
\u00cenc\u0103rcarea noilor \u00eenregistr\u0103ri 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
Actualizarea atributelor 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
\u00cenregistr\u0103ri \u0219terse 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
Fiecare surs\u0103 de date trebuie \u00eenso\u021bit\u0103 de o descriere formal\u0103, de exemplu, \u00eentr-un fi\u0219ier readme.txt:<\/p>\n
Destinatarul datelor formal\/tehnic: nume, adres\u0103 de e-mail
\nFurnizorul de date formal\/tehnic: nume, adres\u0103 de e-mail
\nSursa datelor: cale c\u0103tre fi\u0219ier, denumirile serviciilor
\nInforma\u021bii despre accesul la date: utilizatori \u0219i parole<\/p><\/blockquote>\n
\nSchema de mi\u0219care a datelor va ajuta \u00een procesul de \u00eentre\u021binere \u0219i actualizare, de exemplu, \u00een form\u0103 de text:<\/p>\n
Mutarea fi\u0219ierului. Sursa: ftp.domain.net: \/logs\/access.log Scop: \/var\/www\/access.log
\nCitirea \u00een stadiu. Scop: STG_ACCESS_LOG
\n\u00cenc\u0103rcare \u0219i transformare. Scop: FCT_ACCESS_REQUEST_REF_HH
\n\u00cenc\u0103rcare \u0219i transformare. Scop: FCT_ACCESS_USER_AGENT_DD
\nRaport. Scop: \/var\/www\/report.html<\/p><\/blockquote>\n
Ie\u0219ire<\/h4>\n
\nAstfel, articolul descrie mecanisme precum integrarea bazelor de date \u0219i introducerea unor noi niveluri de agregare. Acestea sunt necesare \u00een construirea depozitelor de date pentru a ob\u021bine cuno\u0219tin\u021be suplimentare \u0219i a \u00eembun\u0103t\u0103\u021bi calitatea informa\u021biei.
\n
Sursa: 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\udd47Mai multe statistici ale site-ului \u00een micu\u021bul s\u0103u depozit | ProHoster","description":"Analiz\u00e2nd statistica site-ului, ob\u021binem o imagine despre ceea ce se \u00eent\u00e2mpl\u0103 cu acesta.","canonical_url":"https:\/\/prohoster.info\/ro\/blog\/administrirovanie\/bolshe-statistiki-sajta-v-svoyom-malenkom-hranilishhe","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"ro_RO","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\/ro\/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\/ro\/wp-json\/wp\/v2\/posts\/37156","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/comments?post=37156"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts\/37156\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/media\/27858"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/media?parent=37156"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/categories?post=37156"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/tags?post=37156"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}