Когда дело доходит до нарезки и агрегирования данных (по времени или чему-то еще), звездная схема (звезда Кимбалла) является довольно простым, но мощным решением. Предположим, что для каждого клика мы сохраняем время (с точностью до секунды), информацию о пользователе, идентификатор кнопки и местоположение пользователя. Чтобы упростить нарезку и нарезку кубиками, я начну с предварительно загруженных таблиц поиска для свойств объектов, которые редко меняются, - так называемых таблиц измерений в мире DW.
В таблице dimDate есть одна строка для каждого дня с количеством атрибутов (полей), описывающих конкретный день. Таблица может быть предварительно загружена на несколько лет вперед и должна обновляться один раз в день, если она содержит такие поля, как DaysAgo, WeeksAgo, MonthsAgo, YearsAgo; иначе это может быть «загрузил и забыл». dimDate позволяет легко нарезать атрибуты по дате, например
WHERE [YEAR] = 2009 AND DayOfWeek = 'Sunday'
За десять лет данных в таблице всего ~ 3650 строк.
Таблица dimGeography предварительно загружена с географическими регионами интереса - количество строк зависит от «географического разрешения», требуемого в отчетах, это позволяет срезать данные, например
WHERE Continent = 'South America'
После загрузки его редко меняют.
Для каждой кнопки сайта есть одна строка в таблице dimButton, поэтому запрос может иметь
WHERE PageURL = 'http://…/somepage.php'
В таблице dimUser есть одна строка для каждого зарегистрированного пользователя, она должна быть загружена с новой информацией о пользователе, как только пользователь зарегистрируется, или, по крайней мере, новая информация о пользователе должна быть в таблице до того, как любая другая пользовательская транзакция будет записана в таблицы фактов.
Чтобы записывать нажатия кнопок, я добавлю таблицу factClick.
В таблице factClick есть одна строка для каждого нажатия кнопки определенным пользователем в определенный момент времени. Я использовал TimeStamp (второе разрешение), ButtonKey и UserKey в составном первичном ключе, чтобы отфильтровать клики быстрее, чем один в секунду от определенного пользователя. Обратите внимание на поле Hour, оно содержит часовую часть TimeStamp, целое число в диапазоне от 0 до 23, чтобы можно было легко разрезать по часам, например
WHERE [HOUR] BETWEEN 7 AND 9
Итак, теперь мы должны учитывать:
- Как загрузить стол? Периодически - может быть, каждый час или каждые несколько минут - из веб-журнала с помощью инструмента ETL или решения с низкой задержкой с использованием какого-либо процесса потоковой передачи событий.
- Как долго хранить информацию в таблице?
Независимо от того, хранит ли таблица информацию только день или несколько лет - она должна быть секционирована; ConcernedOfTunbridgeW объяснил разбиение в своем ответе, поэтому я пропущу его здесь.
Теперь несколько примеров нарезки и нарезки кубиками по разным атрибутам (включая день и час).
Чтобы упростить запросы, я добавлю представление для сглаживания модели:
/* To simplify queries flatten the model */
CREATE VIEW vClicks
AS
SELECT *
FROM factClick AS f
JOIN dimDate AS d ON d.DateKey = f.DateKey
JOIN dimButton AS b ON b.ButtonKey = f.ButtonKey
JOIN dimUser AS u ON u.UserKey = f.UserKey
JOIN dimGeography AS g ON g.GeographyKey = f.GeographyKey
Пример запроса
/*
Count number of times specific users clicked any button
today between 7 and 9 AM (7:00 - 9:59)
*/
SELECT [Email]
,COUNT(*) AS [Counter]
FROM vClicks
WHERE [DaysAgo] = 0
AND [Hour] BETWEEN 7 AND 9
AND [Email] IN ('[email protected]', '[email protected]')
GROUP BY [Email]
ORDER BY [Email]
Предположим, меня интересуют данные для User = ALL. dimUser - это большая таблица, поэтому я сделаю представление без нее, чтобы ускорить запросы.
/*
Because dimUser can be large table it is good
to have a view without it, to speed-up queries
when user info is not required
*/
CREATE VIEW vClicksNoUsr
AS
SELECT *
FROM factClick AS f
JOIN dimDate AS d ON d.DateKey = f.DateKey
JOIN dimButton AS b ON b.ButtonKey = f.ButtonKey
JOIN dimGeography AS g ON g.GeographyKey = f.GeographyKey
Пример запроса
/*
Count number of times a button was clicked on a specific page
today and yesterday, for each hour.
*/
SELECT [FullDate]
,[Hour]
,COUNT(*) AS [Counter]
FROM vClicksNoUsr
WHERE [DaysAgo] IN ( 0, 1 )
AND PageURL = 'http://...MyPage'
GROUP BY [FullDate], [Hour]
ORDER BY [FullDate] DESC, [Hour] DESC
Предположим, что для агрегирования нам не нужно хранить конкретную информацию о пользователе, а нас интересуют только дата, час, кнопка и география. Каждая строка в таблице factClickAgg имеет счетчик для каждого часа, когда была нажата определенная кнопка в определенной географической области.
Таблица factClickAgg может загружаться ежечасно или даже в конце каждого дня - в зависимости от требований к отчетности и аналитике. Например, предположим, что таблица загружается в конце каждого дня (после полуночи), я могу использовать что-то вроде:
/* At the end of each day (after midnight) aggregate data. */
INSERT INTO factClickAgg
SELECT DateKey
,[Hour]
,ButtonKey
,GeographyKey
,COUNT(*) AS [ClickCount]
FROM vClicksNoUsr
WHERE [DaysAgo] = 1
GROUP BY DateKey
,[Hour]
,ButtonKey
,GeographyKey
Чтобы упростить запросы, я создам представление для сглаживания модели:
/* To simplify queries for aggregated data */
CREATE VIEW vClicksAggregate
AS
SELECT *
FROM factClickAgg AS f
JOIN dimDate AS d ON d.DateKey = f.DateKey
JOIN dimButton AS b ON b.ButtonKey = f.ButtonKey
JOIN dimGeography AS g ON g.GeographyKey = f.GeographyKey
Теперь я могу запрашивать агрегированные данные, например, по дням:
/*
Number of times a specific buttons was clicked
in year 2009, by day
*/
SELECT FullDate
,SUM(ClickCount) AS [Counter]
FROM vClicksAggregate
WHERE ButtonName = 'MyBtn_1'
AND [Year] = 2009
GROUP BY FullDate
ORDER BY FullDate
Или еще несколько вариантов
/*
Number of times specific buttons were clicked
in year 2008, on Saturdays, between 9:00 and 11:59 AM
by users from Africa
*/
SELECT SUM(ClickCount) AS [Counter]
FROM vClicksAggregate
WHERE [Year] = 2008
AND [DayOfWeek] = 'Saturday'
AND [Hour] BETWEEN 9 AND 11
AND Continent = 'Africa'
AND ButtonName IN ( 'MyBtn_1', 'MyBtn_2', 'MyBtn_3' )
person
Damir Sudarevic
schedule
16.01.2010
