{"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\/pt\/blog\/administrirovanie\/bolshe-statistiki-sajta-v-svoyom-malenkom-hranilishhe","title":{"rendered":"Mais estat\u00edsticas do site em seu pequeno armazenamento","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Analisando as estat\u00edsticas do site, obtemos uma vis\u00e3o do que est\u00e1 acontecendo com ele. Comparamos os resultados com outros conhecimentos sobre o produto ou servi\u00e7o e assim melhoramos nossa experi\u00eancia.<\/p>\n

Quando a an\u00e1lise dos primeiros resultados \u00e9 conclu\u00edda, refletimos sobre as informa\u00e7\u00f5es e tiramos conclus\u00f5es, iniciando a pr\u00f3xima etapa. Surge a pergunta: o que aconteceria se olh\u00e1ssemos os dados de outra perspectiva?<\/p>\n

Nesta etapa, h\u00e1 limita\u00e7\u00f5es nas ferramentas de an\u00e1lise. Esta \u00e9 uma das raz\u00f5es pelas quais a ferramenta Google Analytics n\u00e3o era suficiente para mim, devido \u00e0 sua capacidade limitada de visualizar e manipular os dados.<\/p>\n

Sempre quis carregar rapidamente os dados b\u00e1sicos (dados mestres), adicionar um novo n\u00edvel de agrega\u00e7\u00e3o ou interpretar os valores existentes de outra forma.<\/p>\n

\u00c9 f\u00e1cil fazer isso em seu pequeno armaz\u00e9m<\/a><\/noindex> com base no arquivo access.log e para isso, basta usar a linguagem SQL.<\/a><\/noindex><\/p>\n

Ent\u00e3o, a quais perguntas eu gostaria de encontrar respostas?<\/p>\n

O que e quando mudou no site<\/h4>\n

\nA hist\u00f3ria das mudan\u00e7as nos dados b\u00e1sicos (dados mestres) sempre \u00e9 interessante.<\/p>\n

\"Mais
\n
\nSQL consulta de relat\u00f3rio<\/b><\/p>\n

SELECT\n\t1 as 'SideStackedBar: Atualiza\u00e7\u00f5es de Conte\u00fado por M\u00eas',\n\tstrftime('%m\/%Y', datetime(UPDATE_DT, 'unixepoch')) AS 'Dia',\n\tCOUNT(CASE WHEN PAGE_TITLE != 'n.a.' THEN DIM_REQUEST_ID END) AS 'Atualiza\u00e7\u00f5es de p\u00e1ginas da web',\n\tCOUNT(CASE WHEN PAGE_DESCR = 'IMAGES' THEN DIM_REQUEST_ID END) AS 'Uploads de imagens',\n\tCOUNT(CASE WHEN PAGE_DESCR = 'VIDEO' THEN DIM_REQUEST_ID END) AS 'Uploads de v\u00eddeo',\n\tCOUNT(CASE WHEN PAGE_DESCR = 'AUDIO' THEN DIM_REQUEST_ID END) AS 'Uploads de \u00e1udio'\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
Por exemplo, em algum momento, foi feita uma otimiza\u00e7\u00e3o para motores de busca ou novo conte\u00fado foi adicionado ao site, e por isso espera-se um aumento no tr\u00e1fego.<\/p>\n
Grupos de usu\u00e1rios<\/h4>\n
\nO exemplo mais simples de um grupo pode ser o agente do usu\u00e1rio ou o nome do sistema operacional. <\/p>\n
A medi\u00e7\u00e3o dos agentes de usu\u00e1rio acumulou cerca de mil registros e eu estava interessado em ver a din\u00e2mica da distribui\u00e7\u00e3o dos agentes dentro do grupo.<\/p>\n
\"Mais
\n
\nSQL consulta de relat\u00f3rio<\/b><\/p>\n
SELECT\n\t1 AS 'SideStackedBar: Agentes de Usu\u00e1rio',\n\tAGENT_OS AS 'SO',\n\tSUM(CASE WHEN AGENT_BOT = 'n.a.' THEN 1 ELSE 0 END ) AS 'Agente de Usu\u00e1rio de Usu\u00e1rios',\n\tSUM(CASE WHEN AGENT_BOT != 'n.a.' THEN 1 ELSE 0 END ) AS 'Agente de Usu\u00e1rio de Bots'\nFROM DIM_USER_AGENT\nWHERE DIM_USER_AGENT_ID != -1\nGROUP BY AGENT_OS\nORDER BY 3 DESC<\/code><\/pre>\n
A maioria das combina\u00e7\u00f5es diferentes de agentes que acessam o site v\u00eam do mundo Windows. Entre os agentes indefinidos est\u00e3o, por exemplo, WhatsApp, PocketImageCache, PlayStation, SmartTV, etc. <\/p>\n
Atividade dos grupos de usu\u00e1rios por semanas<\/h4>\n
\nCombinando alguns grupos, podemos observar a distribui\u00e7\u00e3o de sua atividade.<\/p>\n
Por exemplo, os usu\u00e1rios do cluster Linux consomem mais tr\u00e1fego no site do que todos os outros.<\/p>\n
\"Mais
\n
\nSQL consulta de relat\u00f3rio<\/b><\/p>\n
SELECT\n1 AS 'StackedBar: Volume de Tr\u00e1fego por Sistema Operacional do Usu\u00e1rio e por Semana',\nstrftime('%W semana', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Semana',\nSUM(CASE WHEN USG.AGENT_OS IN ('Android', 'Linux') THEN FCT.BYTES ELSE 0 END) \/ 1000 AS 'Usu\u00e1rios Android\/Linux',\nSUM(CASE WHEN USG.AGENT_OS IN ('Windows') THEN FCT.BYTES ELSE 0 END) \/ 1000 AS 'Usu\u00e1rios Windows',\nSUM(CASE WHEN USG.AGENT_OS IN ('Macintosh', 'iOS') THEN FCT.BYTES ELSE 0 END) \/ 1000 AS 'Usu\u00e1rios Mac\/iOS',\nSUM(CASE WHEN USG.AGENT_OS IN ('n.a.', 'BlackBerry') THEN FCT.BYTES ELSE 0 END) \/ 1000 AS 'Outro'\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.' \/* apenas usu\u00e1rios *\/\n  AND HST.STATUS_GROUP IN ('Bem-sucedido') \/* boas p\u00e1ginas *\/\n  AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 meses')\nGROUP BY strftime('%W semana', datetime(FCT.EVENT_DT, 'unixepoch'))\nORDER BY FCT.EVENT_DT<\/code><\/pre>\n
Consumo intenso de tr\u00e1fego<\/h4>\n
\nA tabela mostra os grupos de usu\u00e1rios mais ativos e os dias de sua atividade.
\nOs mais ativos pertencem ao cluster Linux.<\/p>\n
\"Mais
\n
\nSQL consulta de relat\u00f3rio<\/b><\/p>\n
SELECT\n1 AS 'Tabela: Agente de Usu\u00e1rio com Alto Uso',\nstrftime('%d.%m.%Y', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Dia',\nROUND(1.0 * SUM(FCT.BYTES) \/ 1000000, 1) AS 'Tr\u00e1fego MB',\nROUND(1.0 * SUM(FCT.IP_CNT) \/ SUM(1), 1) AS 'IPs',\nROUND(1.0 * SUM(FCT.REQUEST_CNT) \/ SUM(1), 1) AS 'Solicita\u00e7\u00f5es',\nUSA.DIM_USER_AGENT_ID AS 'ID',\nMAX(USA.USER_AGENT_NK) AS 'Agente de Usu\u00e1rio',\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 dias')\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
Usando os atributos dia e ID do agente, \u00e9 poss\u00edvel encontrar e rastrear rapidamente as estat\u00edsticas por dias de grupos espec\u00edficos de usu\u00e1rios. Se necess\u00e1rio, pode-se rapidamente encontrar informa\u00e7\u00f5es detalhadas na tabela de est\u00e1gio.<\/p>\n
Como obter informa\u00e7\u00f5es?<\/h4>\n
\nInforma\u00e7\u00e3o do arquivo access.log<\/a><\/noindex> pode ser feita ainda mais eficaz se forem integradas fontes de dados adicionais, introduzidos novos n\u00edveis de agrega\u00e7\u00e3o e agrupamento.<\/p>\n
Dados e entidades b\u00e1sicas<\/h4>\n
\nOs dados b\u00e1sicos incluem informa\u00e7\u00f5es sobre entidades: p\u00e1ginas da web, imagens, conte\u00fados de v\u00eddeo e \u00e1udio, e no caso de lojas \u2014 produtos. <\/p>\n
As pr\u00f3prias entidades funcionam como dimens\u00f5es, e o processo de salvar as altera\u00e7\u00f5es dos atributos \u00e9 chamado de historiza\u00e7\u00e3o. Na base de dados, esse processo \u00e9 frequentemente implementado na forma de dimens\u00f5es lentamente mut\u00e1veis (SCD).<\/p>\n
A fonte de dados b\u00e1sicas pode ser a mais variada, portanto, quase sempre elas precisam ser integradas.<\/p>\n
Dimens\u00e3o lentamente mut\u00e1vel<\/h4>\n
\nA dimens\u00e3o DIM_REQUEST conter\u00e1 informa\u00e7\u00f5es sobre solicita\u00e7\u00f5es no site em forma hist\u00f3rica.<\/p>\n
Tabela SCD2<\/b><\/p>\n
CRIE UMA TABELA DIM_REQUEST ( \n  DIM_REQUEST_ID      INTEIRO N\u00c3O NULO CHAVE PRIM\u00c1RIA AUTOINCREMENT,\n  DIM_REQUEST_ID_HIST INTEIRO N\u00c3O NULO DEFAULT -1,\n  REQUEST_NK          TEXTO N\u00c3O NULO DEFAULT 'n.a.', \n  PAGE_TITLE          TEXTO N\u00c3O NULO DEFAULT 'n.a.',\n  PAGE_DESCR          TEXTO N\u00c3O NULO DEFAULT 'n.a.',\n  PAGE_KEYWORDS       TEXTO N\u00c3O NULO DEFAULT 'n.a.',\n  DELETE_FLAG         INTEIRO N\u00c3O NULO DEFAULT 0,\n  UPDATE_DT           INTEIRO N\u00c3O NULO DEFAULT 0,\n  \u00daNICO (REQUEST_NK, DIM_REQUEST_ID_HIST)\n);\nINSERIR NA DIM_REQUEST (DIM_REQUEST_ID) VALORES (-1);<\/code><\/pre>\n
Al\u00e9m disso, criaremos uma vis\u00e3o que sempre exibe todos os registros em seu estado mais recente. Isso \u00e9 necess\u00e1rio para carregar a pr\u00f3pria medida.<\/p>\n
\"Mais
\n
\nVis\u00e3o atual 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
E a vis\u00e3o, onde para cada registro, foi coletada informa\u00e7\u00e3o hist\u00f3rica. Isso \u00e9 necess\u00e1rio para construir uma rela\u00e7\u00e3o historicamente correta com os fatos.<\/p>\n
\"Mais
\n
\nVis\u00e3o hist\u00f3rica 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
Agrega\u00e7\u00e3o de dados<\/h4>\n
\nA compress\u00e3o (agrega\u00e7\u00e3o) permite avaliar os dados em um n\u00edvel mais alto e identificar anomalias e tend\u00eancias que n\u00e3o s\u00e3o vis\u00edveis em relat\u00f3rios detalhados.<\/p>\n
Por exemplo, na medida com c\u00f3digos de status de solicita\u00e7\u00f5es DIM_HTTP_STATUS adicionaremos um grupo:<\/p>\n
STATUS \/ GRUPO
\n0xx \/ n.a.
\n1xx \/ Informativo
\n2xx \/ Bem-sucedido
\n3xx \/ Redirecionamento
\n4xx \/ Erro do Cliente
\n5xx \/ Erro do Servidor<\/p><\/blockquote>\n
 A medida de agentes de usu\u00e1rio DIM_USER_AGENT conter\u00e1 os atributos AGENT_OS e AGENT_BOT, respons\u00e1veis pelos grupos. Eles podem ser preenchidos durante o processo ETL:<\/p>\n
Carregamento 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
Integra\u00e7\u00e3o de Dados<\/h4>\n
\nInclui a organiza\u00e7\u00e3o da transfer\u00eancia de dados do sistema operacional para o relat\u00f3rio. Para isso, \u00e9 necess\u00e1rio criar uma tabela de est\u00e1gio com uma estrutura semelhante \u00e0 do fonte.<\/p>\n
Na etapa de est\u00e1gio, a informa\u00e7\u00e3o sobre p\u00e1ginas web chega a partir do backup do CMS na forma de consultas de inser\u00e7\u00e3o.<\/p>\n
O carregamento da tabela hist\u00f3rica DIM_REQUEST com dados b\u00e1sicos ocorre em tr\u00eas etapas: carregamento de novas chaves e atributos, atualiza\u00e7\u00e3o dos existentes e registro dos registros removidos.<\/p>\n
Carregamento de novos registros 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
Atualiza\u00e7\u00e3o de atributos 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
Registros removidos 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
Cada fonte de dados deve ser acompanhada de uma descri\u00e7\u00e3o formal, por exemplo, em um arquivo readme.txt:<\/p>\n
Destinat\u00e1rio dos dados formal\/t\u00e9cnico: nome, endere\u00e7o eletr\u00f4nico
\nFornecedor de dados formal\/t\u00e9cnico: nome, endere\u00e7o eletr\u00f4nico
\nFonte de dados: caminho para o arquivo, nomes dos servi\u00e7os
\nInforma\u00e7\u00e3o sobre acesso aos dados: usu\u00e1rios e senhas<\/p><\/blockquote>\n
\nUm esquema de movimenta\u00e7\u00e3o de dados ajudar\u00e1 no processo de manuten\u00e7\u00e3o e atualiza\u00e7\u00e3o, por exemplo, em formato de texto:<\/p>\n
Movimento do arquivo. Origem: ftp.domain.net: \/logs\/access.log Destino: \/var\/www\/access.log
\nLeitura na etapa. Destino: STG_ACCESS_LOG
\nCarregamento e transforma\u00e7\u00e3o. Objetivo: FCT_ACCESS_REQUEST_REF_HH
\nCarregamento e transforma\u00e7\u00e3o. Objetivo: FCT_ACCESS_USER_AGENT_DD
\nRelat\u00f3rio. Objetivo: \/var\/www\/report.html<\/p><\/blockquote>\n
Sa\u00edda<\/h4>\n
\nAssim, o artigo descreve mecanismos como a integra\u00e7\u00e3o de dados b\u00e1sicos e a introdu\u00e7\u00e3o de novos n\u00edveis de agrega\u00e7\u00e3o. Eles s\u00e3o necess\u00e1rios para construir armaz\u00e9ns de dados com o objetivo de obter conhecimentos adicionais e melhorar a qualidade da informa\u00e7\u00e3o.
\n
Fonte: 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\udd47Mais estat\u00edsticas do site no seu pequeno armaz\u00e9m | ProHoster","description":"Analisando as estat\u00edsticas do site, obtemos uma vis\u00e3o do que est\u00e1 acontecendo com ele.","canonical_url":"https:\/\/prohoster.info\/pt\/blog\/administrirovanie\/bolshe-statistiki-sajta-v-svoyom-malenkom-hranilishhe","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"pt_PT","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\/pt\/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\/pt\/wp-json\/wp\/v2\/posts\/37156","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/pt\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/pt\/wp-json\/wp\/v2\/comments?post=37156"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/pt\/wp-json\/wp\/v2\/posts\/37156\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/pt\/wp-json\/wp\/v2\/media\/27858"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/pt\/wp-json\/wp\/v2\/media?parent=37156"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/pt\/wp-json\/wp\/v2\/categories?post=37156"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/pt\/wp-json\/wp\/v2\/tags?post=37156"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}