ProHoster > Blog > Administration > Mehr Statistiken der Website in ihrem kleinen Speicher

Mehr Statistiken der Website in ihrem kleinen Speicher

Durch die Analyse der Website-Statistiken erhalten wir ein Bild davon, was mit ihr passiert. Die Ergebnisse vergleichen wir mit anderem Wissen ĂŒber das Produkt oder den Service und verbessern damit unser Erlebnis.

Nachdem die Analyse der ersten Ergebnisse abgeschlossen ist, die Informationen reflektiert wurden und Schlussfolgerungen gezogen wurden, beginnt die nÀchste Phase. Es kommen Ideen auf: Was wÀre, wenn wir die Daten von einer anderen Seite betrachten?

In dieser Phase gibt es EinschrĂ€nkungen bei den Analysetools. Das ist einer der GrĂŒnde, warum mir das Tool Google Analytics nicht ausreichte, insbesondere wegen der eingeschrĂ€nkten Möglichkeit, meine Daten zu sehen und zu manipulieren.

Ich wollte immer die grundlegenden Daten (Masterdaten) schnell hochladen, eine andere Aggregationsebene hinzufĂŒgen oder die vorhandenen Werte anders interpretieren.

Das ist einfach in ihrem kleinen Speicher basierend auf der Datei access.log und dafĂŒr benötigt man nur die SQL-Sprache.

Also, welche Fragen wollte ich beantworten?

Was und wann hat sich auf der Website geÀndert

Die Historie der Änderungen an den Basisdaten (Masterdaten) ist immer von Interesse.

Mehr Statistiken der Website in ihrem kleinen Speicher

SQL-Abfragebericht

SELECT
	1 as 'SideStackedBar: Inhaltsaktualisierungen nach Monaten',
	strftime('%m/%Y', datetime(UPDATE_DT, 'unixepoch')) AS 'Tag',
	COUNT(CASE WHEN PAGE_TITLE != 'n.a.' THEN DIM_REQUEST_ID END) AS 'Webseitenaktualisierungen',
	COUNT(CASE WHEN PAGE_DESCR = 'IMAGES' THEN DIM_REQUEST_ID END) AS 'Bild-Uploads',
	COUNT(CASE WHEN PAGE_DESCR = 'VIDEO' THEN DIM_REQUEST_ID END) AS 'Video-Uploads',
	COUNT(CASE WHEN PAGE_DESCR = 'AUDIO' THEN DIM_REQUEST_ID END) AS 'Audio-Uploads'
FROM DIM_REQUEST
WHERE PAGE_TITLE != 'n.a.' OR PAGE_DESCR != 'n.a.'
GROUP BY strftime('%m/%Y', datetime(UPDATE_DT, 'unixepoch'))
ORDER BY UPDATE_DT

Zum Beispiel wurde irgendwann eine Suchmaschinenoptimierung durchgefĂŒhrt oder neuer Inhalt auf die Website hinzugefĂŒgt, weshalb ein Anstieg des Traffics zu erwarten ist.

Benutzergruppen

Das einfachste Beispiel fĂŒr eine Gruppe kann der Benutzer-Agent oder der Name des Betriebssystems sein.

Die Messung der Benutzer-Agenten hat etwa tausend EintrÀge angesammelt, und ich war daran interessiert, die Dynamik der Verteilung der Agenten innerhalb der Gruppe zu sehen.

Mehr Statistiken der Website in ihrem kleinen Speicher

SQL-Abfragebericht

SELECT
	1 AS 'SideStackedBar: Benutzer-Agenten',
	AGENT_OS AS 'OS',
	SUM(CASE WHEN AGENT_BOT = 'n.a.' THEN 1 ELSE 0 END ) AS 'Benutzer-Agent der Benutzer',
	SUM(CASE WHEN AGENT_BOT != 'n.a.' THEN 1 ELSE 0 END ) AS 'Benutzer-Agent der Bots'
FROM DIM_USER_AGENT
WHERE DIM_USER_AGENT_ID != -1
GROUP BY AGENT_OS
ORDER BY 3 DESC

Die meisten verschiedenen Kombinationen von Agenten kommen aus der Welt von Windows auf die Website. Zu den Unbestimmten gehören z.B. WhatsApp, PocketImageCache, PlayStation, SmartTV usw.

Die AktivitÀt der Benutzergruppen nach Wochen

Durch die ZusammenfĂŒhrung bestimmter Gruppen kann die Verteilung ihrer AktivitĂ€t beobachtet werden.

Beispielsweise verbrauchen Benutzer des Linux-Clusters mehr Traffic auf der Website als alle anderen.

Mehr Statistiken der Website in ihrem kleinen Speicher

SQL-Abfragebericht

SELECT
1 AS 'StackedBar: Traffic Volume by User OS und by Week',
strftime('%W Woche', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Woche',
SUM(CASE WHEN USG.AGENT_OS IN ('Android', 'Linux') THEN FCT.BYTES ELSE 0 END) / 1000 AS 'Android/Linux Benutzer',
SUM(CASE WHEN USG.AGENT_OS IN ('Windows') THEN FCT.BYTES ELSE 0 END) / 1000 AS 'Windows Benutzer',
SUM(CASE WHEN USG.AGENT_OS IN ('Macintosh', 'iOS') THEN FCT.BYTES ELSE 0 END) / 1000 AS 'Mac/iOS Benutzer',
SUM(CASE WHEN USG.AGENT_OS IN ('n.a.', 'BlackBerry') THEN FCT.BYTES ELSE 0 END) / 1000 AS 'Andere'
FROM
  FCT_ACCESS_USER_AGENT_DD FCT,
  DIM_USER_AGENT USG,
  DIM_HTTP_STATUS HST
WHERE FCT.DIM_USER_AGENT_ID = USG.DIM_USER_AGENT_ID
  AND FCT.DIM_HTTP_STATUS_ID = HST.DIM_HTTP_STATUS_ID
  AND USG.AGENT_BOT = 'n.a.' /* nur Benutzer */
  AND HST.STATUS_GROUP IN ('Erfolgreich') /* gute Seiten */
  AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 Monate')
GROUP BY strftime('%W Woche', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DT

Intensiver Trafficverbrauch

Aus der Tabelle sind die aktivsten Gruppen von Benutzern und die Tage ihrer AktivitÀt ersichtlich.
Die aktivsten gehören zum Linux-Cluster.

Mehr Statistiken der Website in ihrem kleinen Speicher

SQL-Abfragebericht

SELECT
1 AS 'Tabelle: Benutzer Agent mit hohem Verbrauch',
strftime('%d.%m.%Y', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Tag',
ROUND(1.0 * SUM(FCT.BYTES) / 1000000, 1) AS 'Traffic MB',
ROUND(1.0 * SUM(FCT.IP_CNT) / SUM(1), 1) AS 'IPs',
ROUND(1.0 * SUM(FCT.REQUEST_CNT) / SUM(1), 1) AS 'Anfragen',
USA.DIM_USER_AGENT_ID AS 'ID',
MAX(USA.USER_AGENT_NK) AS 'Benutzer Agent',
MAX(USA.AGENT_BOT) AS 'Bot'
FROM
FCT_ACCESS_USER_AGENT_DD FCT,
DIM_USER_AGENT USA
WHERE FCT.DIM_USER_AGENT_ID = USA.DIM_USER_AGENT_ID
  AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-30 Tage')
GROUP BY USA.DIM_USER_AGENT_ID, strftime('%d.%m.%Y', datetime(FCT.EVENT_DT, 'unixepoch')) 
ORDER BY SUM(FCT.BYTES) DESC, FCT.EVENT_DT
LIMIT 10

Durch die Verwendung der Attribute Tag und ID des Agents wird es möglich, die Statistiken einzelner Benutzergruppen schnell nach Tagen zu finden und nachzuvollziehen. Wenn nötig, kann man schnell detaillierte Informationen in der Staging-Tabelle finden.

Wie erhÀlt man Informationen?

Informationen aus der Datei access.log kann noch effektiver gestaltet werden, wenn zusĂ€tzliche Datenquellen integriert, neue Aggregations- und Gruppierungsebenen eingefĂŒhrt werden.

Basisdaten und EntitÀten

Zu den Basisdaten gehören Informationen ĂŒber EntitĂ€ten: Webseiten, Bilder, Video- und Audioinhalte sowie Produkte im Falle eines GeschĂ€fts.

Die EntitĂ€ten selbst fungieren als Maßeinheiten, und der Prozess der Speicherung von AttributverĂ€nderungen wird als Historisierung bezeichnet. In Datenbanken wird dieser Prozess hĂ€ufig in Form von langsam Ă€ndernden Dimensionen (SCD) umgesetzt.

Die Quellen fĂŒr Basisdaten können sehr unterschiedlich sein, daher mĂŒssen sie fast immer integriert werden.

Langsam Àndernde Dimension

Die Messung DIM_REQUEST enthÀlt Informationen zu Anfragen auf der Website in historischer Form.

Tabelle SCD2

CREATE TABLE DIM_REQUEST ( /* scd-Tabelle fĂŒr Benutzeranfragen */
  DIM_REQUEST_ID      INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  DIM_REQUEST_ID_HIST INTEGER NOT NULL DEFAULT -1,
  REQUEST_NK          TEXT NOT NULL DEFAULT 'n.a.', /* Anfrage ohne ?Parameter */
  PAGE_TITLE          TEXT NOT NULL DEFAULT 'n.a.',
  PAGE_DESCR          TEXT NOT NULL DEFAULT 'n.a.',
  PAGE_KEYWORDS       TEXT NOT NULL DEFAULT 'n.a.',
  DELETE_FLAG         INTEGER NOT NULL DEFAULT 0,
  UPDATE_DT           INTEGER NOT NULL DEFAULT 0,
  UNIQUE (REQUEST_NK, DIM_REQUEST_ID_HIST)
);
INSERT INTO DIM_REQUEST (DIM_REQUEST_ID) VALUES (-1);

ZusÀtzlich werden wir eine Sicht erstellen, die immer alle EintrÀge im letzten Zustand anzeigt. Dies ist erforderlich, um die Messung selbst zu laden.

Mehr Statistiken der Website in ihrem kleinen Speicher

Aktuelle Sicht SCD2

/* Content: actual view on scd table */
SELECT HI.DIM_REQUEST_ID,
  HI.DIM_REQUEST_ID_HIST,
  HI.REQUEST_NK,
  HI.PAGE_TITLE,
  HI.PAGE_DESCR,
  HI.PAGE_KEYWORDS,
  NK.CNT AS HIST_CNT,
  HI.DELETE_FLAG,
  strftime('%d.%m.%Y %H:%M', datetime(HI.UPDATE_DT, 'unixepoch')) AS UPDATE_DT
FROM
  ( SELECT REQUEST_NK, MAX(DIM_REQUEST_ID) AS DIM_REQUEST_ID, SUM(1) AS CNT
    FROM DIM_REQUEST
    GROUP BY REQUEST_NK
  ) NK,
  DIM_REQUEST HI
WHERE 1 = 1
  AND NK.REQUEST_NK = HI.REQUEST_NK
  AND NK.DIM_REQUEST_ID = HI.DIM_REQUEST_ID;

Und eine Sicht, in der fĂŒr jeden Eintrag historische Informationen gesammelt wurden. Dies ist notwendig, um historisch korrekte Verbindungen zu den Fakten herzustellen.

Mehr Statistiken der Website in ihrem kleinen Speicher

Historische Sicht SCD2

/* Content: actual view on scd table */
SELECT SCD.DIM_REQUEST_ID,
  SCD.DIM_REQUEST_ID_HIST,
  SCD.REQUEST_NK,
  SCD.PAGE_TITLE,
  SCD.PAGE_DESCR,
  SCD.PAGE_KEYWORDS,
  SCD.DELETE_FLAG,
  CASE
    WHEN HIS.UPDATE_DT IS NULL
    THEN 1
    ELSE 0 END ACTIVE_FLAG,
  SCD.DIM_REQUEST_ID_HIST AS ID_FROM,
  SCD.DIM_REQUEST_ID AS ID_TO,
  CASE
    WHEN SCD.DIM_REQUEST_ID_HIST=-1
    THEN 3600
    ELSE IFNULL(SCD.UPDATE_DT,3600)
  END AS TIME_FROM,
  CASE
    WHEN HIS.UPDATE_DT IS NULL
    THEN 253370764800
    ELSE HIS.UPDATE_DT
  END AS TIME_TO,
  CASE
    WHEN SCD.DIM_REQUEST_ID_HIST=-1
    THEN STRFTIME('%d.%m.%Y %H:%M', DATETIME(3600, 'unixepoch'))
    ELSE STRFTIME('%d.%m.%Y %H:%M', DATETIME(IFNULL(SCD.UPDATE_DT,3600), 'unixepoch'))
  END AS ACTIVE_FROM,
  CASE
    WHEN HIS.UPDATE_DT IS NULL
    THEN STRFTIME('%d.%m.%Y %H:%M', DATETIME(253370764800, 'unixepoch'))
    ELSE STRFTIME('%d.%m.%Y %H:%M', DATETIME(HIS.UPDATE_DT, 'unixepoch'))
  END AS ACTIVE_TO
FROM
  DIM_REQUEST SCD
  LEFT OUTER JOIN DIM_REQUEST HIS
  ON SCD.REQUEST_NK = HIS.REQUEST_NK AND SCD.DIM_REQUEST_ID = HIS.DIM_REQUEST_ID_HIST;

Datenaggregation

Die Verdichtung (Aggregation) ermöglicht es, Daten auf einer höheren Ebene zu bewerten und Anomalien und Trends zu erkennen, die in detaillierten Berichten nicht sichtbar sind.

Zum Beispiel fĂŒgen wir in die Messung mit den Statuscodes der Anfragen DIM_HTTP_STATUS eine Gruppe hinzu:

STATUS / GRUPPE
0xx / n.a.
1xx / Informations
2xx / Erfolgreich
3xx / Umleitung
4xx / Client-Fehler
5xx / Server-Fehler

Die Messung der Benutzeragenten DIM_USER_AGENT wird die Attribute AGENT_OS und AGENT_BOT enthalten, die fĂŒr die Gruppen verantwortlich sind. Diese können im ETL-Prozess ausgefĂŒllt werden:

Laden von DIM_USER_AGENT

/* Propagate the user agent from access log */
INSERT INTO DIM_USER_AGENT (USER_AGENT_NK, AGENT_OS, AGENT_ENGINE, AGENT_DEVICE, AGENT_BOT, UPDATE_DT)
WITH CLS AS (
	SELECT BROWSER
	FROM STG_ACCESS_LOG WHERE LENGTH(BROWSER)>1
	GROUP BY BROWSER
)
SELECT
	CLS.BROWSER AS USER_AGENT_NK,
	CASE
	WHEN INSTR(CLS.BROWSER,'Macintosh')>0
		THEN 'Macintosh'
	WHEN INSTR(CLS.BROWSER,'iPhone')>0
			 OR INSTR(CLS.BROWSER,'iPad')>0
			 OR INSTR(CLS.BROWSER,'iPod')>0
			 OR INSTR(CLS.BROWSER,'Apple TV')>0
			 OR INSTR(CLS.BROWSER,'Darwin')>0
		THEN 'iOS'
	WHEN INSTR(CLS.BROWSER,'Android')>0
		THEN 'Android'
	WHEN INSTR(CLS.BROWSER,'X11;')>0 OR INSTR(CLS.BROWSER,'Wayland;')>0 OR INSTR(CLS.BROWSER,'linux-gnu')>0
		THEN 'Linux'
	WHEN INSTR(CLS.BROWSER,'BB10;')>0 OR INSTR(CLS.BROWSER,'BlackBerry')>0
		THEN 'BlackBerry'
	WHEN INSTR(CLS.BROWSER,'Windows')>0
		THEN 'Windows'
	ELSE 'n.a.' END AS AGENT_OS, -- OS
	CASE
	WHEN INSTR(CLS.BROWSER,'AppleCoreMedia')>0
		THEN 'AppleWebKit'
	WHEN INSTR(CLS.BROWSER,') ')>1 AND LENGTH(CLS.BROWSER)>INSTR(CLS.BROWSER,') ')
		THEN COALESCE(SUBSTR(CLS.BROWSER, INSTR(CLS.BROWSER,') ')+2, LENGTH(CLS.BROWSER) - INSTR(CLS.BROWSER,') ')-1), 'N/A')
	ELSE 'n.a.' END AS AGENT_ENGINE, -- Engine
	CASE
	WHEN INSTR(CLS.BROWSER,'iPhone')>0
		THEN 'iPhone'
	WHEN INSTR(CLS.BROWSER,'iPad')>0
		THEN 'iPad'
	WHEN INSTR(CLS.BROWSER,'iPod')>0
		THEN 'iPod'
	WHEN INSTR(CLS.BROWSER,'Apple TV')>0
		THEN 'Apple TV'
	WHEN INSTR(CLS.BROWSER,'Android ')>0 AND INSTR(CLS.BROWSER,'Build')>0
		THEN COALESCE(SUBSTR(CLS.BROWSER, INSTR(CLS.BROWSER,'Android '), INSTR(CLS.BROWSER,'Build')-INSTR(CLS.BROWSER,'Android ')), 'n.a.')
	WHEN INSTR(CLS.BROWSER,'Android ')>0 AND INSTR(CLS.BROWSER,'MIUI')>0
		THEN COALESCE(SUBSTR(CLS.BROWSER, INSTR(CLS.BROWSER,'Android '), INSTR(CLS.BROWSER,'MIUI')-INSTR(CLS.BROWSER,'Android ')), 'n.a.')
	ELSE 'n.a.' END AS AGENT_DEVICE, -- Device
	CASE
	WHEN INSTR(LOWER(CLS.BROWSER),'yandex.com')>0
		THEN 'yandex'
	WHEN INSTR(LOWER(CLS.BROWSER),'googlebot')>0
		THEN 'google'
	WHEN INSTR(LOWER(CLS.BROWSER),'bingbot')>0
		THEN 'microsoft'
	WHEN INSTR(LOWER(CLS.BROWSER),'ahrefsbot')>0
		THEN 'ahrefs'
	WHEN INSTR(LOWER(CLS.BROWSER),'jobboersebot')>0 OR INSTR(LOWER(CLS.BROWSER),'jobkicks')>0
		THEN 'job.de'
	WHEN INSTR(LOWER(CLS.BROWSER),'mail.ru')>0
		THEN 'mail.ru'
	WHEN INSTR(LOWER(CLS.BROWSER),'baiduspider')>0
		THEN 'baidu'
	WHEN INSTR(LOWER(CLS.BROWSER),'mj12bot')>0
		THEN 'majestic-12'
	WHEN INSTR(LOWER(CLS.BROWSER),'duckduckgo')>0
		THEN 'duckduckgo'
	WHEN INSTR(LOWER(CLS.BROWSER),'bytespider')>0
		THEN 'bytespider'
	WHEN INSTR(LOWER(CLS.BROWSER),'360spider')>0
		THEN 'so.360.cn'
	WHEN INSTR(LOWER(CLS.BROWSER),'compatible')>0 OR INSTR(LOWER(CLS.BROWSER),'http')>0
		OR INSTR(LOWER(CLS.BROWSER),'libwww')>0 OR INSTR(LOWER(CLS.BROWSER),'spider')>0
		OR INSTR(LOWER(CLS.BROWSER),'java')>0 OR INSTR(LOWER(CLS.BROWSER),'python')>0
		OR INSTR(LOWER(CLS.BROWSER),'robot')>0 OR INSTR(LOWER(CLS.BROWSER),'curl')>0 OR INSTR(LOWER(CLS.BROWSER),'wget')>0
		THEN 'other'
	ELSE 'n.a.' END AS AGENT_BOT, -- Bot
	STRFTIME('%s','now') AS UPDATE_DT
FROM CLS
LEFT OUTER JOIN DIM_USER_AGENT TRG
ON CLS.BROWSER = TRG.USER_AGENT_NK
WHERE TRG.DIM_USER_AGENT_ID IS NULL

Datenintegration

Umfasst die Organisation der DatenĂŒbertragung vom Betriebssystem in den Bericht. Dazu ist es notwendig, eine Staging-Tabelle mit einer Struktur Ă€hnlich der des Quellens zu erstellen.

Im Staging gelangen Informationen ĂŒber die Webseiten aus einem Backup der CMS in Form von EinfĂŒgeanfragen.

Das Laden der historischen Tabelle DIM_REQUEST mit grundlegenden Daten erfolgt in drei Schritten: Laden neuer SchlĂŒssel und Attribute, Aktualisieren vorhandener und Festhalten gelöschter EintrĂ€ge.

Laden neuer EintrÀge SCD2

/* Load request table SCD from master data */
INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT)
WITH CLS  AS ( -- prepare keys
	SELECT
	'/' || NAME AS REQUEST_NK,
	TITLE       AS PAGE_TITLE,
	CASE WHEN DESCRIPTION = '' OR DESCRIPTION IS NULL
	     THEN 'n.a.' ELSE DESCRIPTION
	END AS PAGE_DESCR,
	CASE WHEN KEYWORDS = '' OR KEYWORDS IS NULL
	     THEN 'n.a.' ELSE KEYWORDS
	END AS PAGE_KEYWORDS
	FROM STG_CMS_MENU
	WHERE CONTENT_TYPE != 'folder' -- only web pages
	  AND PAGE_TITLE != 'n.a.' -- master data which make sense
)
/* new records from stage: CLS */
SELECT
	-1 AS DIM_REQUEST_ID_HIST,
	CLS.REQUEST_NK,
	CLS.PAGE_TITLE,
	CLS.PAGE_DESCR,
	CLS.PAGE_KEYWORDS,
	0 AS DELETE_FLAG,
	STRFTIME('%s','now') AS UPDATE_DT
FROM CLS
LEFT OUTER JOIN
 (
	SELECT
	DIM_REQUEST_ID,
	REQUEST_NK,
	PAGE_TITLE,
	PAGE_DESCR,
	PAGE_KEYWORDS
	FROM DIM_REQUEST_V_ACT
) TRG ON CLS.REQUEST_NK = TRG.REQUEST_NK
WHERE TRG.REQUEST_NK IS NULL -- no such record in data mart

Aktualisierung der Attribute SCD2

/* Load request table SCD from master data */
INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT)
WITH CLS  AS ( -- prepare keys
	SELECT
	'/' || NAME AS REQUEST_NK,
	TITLE       AS PAGE_TITLE,
	CASE WHEN DESCRIPTION = '' OR DESCRIPTION IS NULL
	     THEN 'n.a.' ELSE DESCRIPTION
	END AS PAGE_DESCR,
	CASE WHEN KEYWORDS = '' OR KEYWORDS IS NULL
	     THEN 'n.a.' ELSE KEYWORDS
	END AS PAGE_KEYWORDS
	FROM STG_CMS_MENU
	WHERE CONTENT_TYPE != 'folder' -- only web pages
	  AND PAGE_TITLE != 'n.a.' -- master data which make sense
)
/* updated records from stage: CLS and build reference to history: HIST */
SELECT
	HIST.DIM_REQUEST_ID AS DIM_REQUEST_ID_HIST,
	HIST.REQUEST_NK,
	CLS.PAGE_TITLE,
	CLS.PAGE_DESCR,
	CLS.PAGE_KEYWORDS,
	0 AS DELETE_FLAG,
	STRFTIME('%s','now') AS UPDATE_DT
FROM CLS,
     DIM_REQUEST_V_ACT TRG,
     DIM_REQUEST HIST
WHERE CLS.REQUEST_NK = TRG.REQUEST_NK
  AND TRG.DIM_REQUEST_ID = HIST.DIM_REQUEST_ID
  AND ( CLS.PAGE_TITLE != HIST.PAGE_TITLE /* changes only */
     OR CLS.PAGE_DESCR != HIST.PAGE_DESCR
     OR CLS.PAGE_KEYWORDS != HIST.PAGE_KEYWORDS )

Gelöschte EintrÀge SCD2

/* Load request table SCD from master data */
INSERT INTO DIM_REQUEST (DIM_REQUEST_ID_HIST, REQUEST_NK, PAGE_TITLE, PAGE_DESCR, PAGE_KEYWORDS, DELETE_FLAG, UPDATE_DT)
WITH CLS  AS ( -- prepare keys
	SELECT
	'/' || NAME AS REQUEST_NK,
	TITLE       AS PAGE_TITLE
	FROM STG_CMS_MENU
	WHERE CONTENT_TYPE != 'folder' -- only web pages
	  AND PAGE_TITLE != 'n.a.' -- master data which make sense
)
/*  deleted records in data mart: TRG */
SELECT
	TRG.DIM_REQUEST_ID AS DIM_REQUEST_ID_HIST,
	TRG.REQUEST_NK,
	TRG.PAGE_TITLE,
	TRG.PAGE_DESCR,
	TRG.PAGE_KEYWORDS,
	1 AS DELETE_FLAG,
	STRFTIME('%s','now') AS UPDATE_DT
FROM (
	SELECT
	DIM_REQUEST_ID,
	REQUEST_NK,
	PAGE_TITLE,
	PAGE_DESCR,
	PAGE_KEYWORDS
	FROM DIM_REQUEST_V_ACT
	WHERE PAGE_TITLE != 'n.a.' -- track master data only
	  AND DELETE_FLAG = 0 -- not already deleted
) TRG
LEFT OUTER JOIN CLS ON TRG.REQUEST_NK = CLS.REQUEST_NK
WHERE CLS.REQUEST_NK IS NULL -- no such record in stage

Jede Datenquelle muss mit einer formalen Beschreibung begleitet werden, zum Beispiel in einer Datei readme.txt:

EmpfÀnger der Daten formell/technisch: Name, E-Mail-Adresse
Datenlieferant formell/technisch: Name, E-Mail-Adresse
Datenquelle: Pfad zur Datei, Namen der Dienste
Zugriffsinformationen: Benutzer und Passwörter

Das Flussdiagramm der Datenbewegung wird im Rahmen der Wartung und Aktualisierung hilfreich sein, zum Beispiel in Textform:

DateiĂŒbertragung. Quelle: ftp.domain.net: /logs/access.log Ziel: /var/www/access.log
Lesen im Staging. Ziel: STG_ACCESS_LOG
Laden und Transformation. Ziel: FCT_ACCESS_REQUEST_REF_HH
Laden und Transformation. Ziel: FCT_ACCESS_USER_AGENT_DD
Bericht. Ziel: /var/www/report.html

Ausgabe

Somit beschreibt der Artikel Mechanismen wie die Integration von Basisdaten und die EinfĂŒhrung neuer Aggregationsebenen. Diese sind notwendig beim Aufbau von Data Warehouses, um zusĂ€tzliche Erkenntnisse zu gewinnen und die QualitĂ€t der Informationen zu verbessern.

Quelle: habr.com

60GB SSD 8Gb DDR4