Хранилище данных: работа с накопленными данными
Наше хранилище данных использует накопленные данные (и нет способа инвертировать накопление) из источника данных для создания схемы снежинки. Требование, которое мы должны выполнить, заключается в том, что наша схема должна быть пригодна для создания отчетов на основе диапазонов дат.
Наша схема выглядит так (упрощенно):
+------------------------------------------+
| fact |
+-------+-----------------+----------------+
| id | statisticsDimId | dateRangeDimId |
+-------+-----------------+----------------+
| 1 | 1 | 10 |
| 2 | 2 | 11 |
| 3 | 3 | 12 |
| 4 | 4 | 13 |
| 5 | 5 | 14 |
| 6 | 5 | 15 |
| 7 | 5 | 16 |
| ... | ... | ... |
| 10001 | 9908 | 11 |
| 10002 | 9909 | 11 |
+-------+-----------------+----------------+
+-------------------------------------------------+
| date_range_dimension |
+-------+-----------------------------------------+
| id | startDateTime | endDateTime |
+-------+--------------------+--------------------+
| 10 | '2012-01-01 00:00' | '2012-01-01 23:59' |
| 11 | '2012-01-01 00:00' | '2012-01-02 23:59' |
| 12 | '2012-01-01 00:00' | '2012-01-03 23:59' |
| 13 | '2012-01-01 00:00' | '2012-01-04 23:59' |
| 14 | '2012-01-01 00:00' | '2012-01-05 23:59' |
| 15 | '2012-01-01 00:00' | '2012-01-06 23:59' |
| 16 | '2012-01-01 00:00' | '2012-01-07 23:59' |
| 17 | '2012-01-01 00:00' | '2012-01-08 23:59' |
| 18 | '2012-01-01 00:00' | '2012-01-09 23:59' |
| ... | ... | ... |
+-------+--------------------+--------------------+
+-----------------------------------------------------+
| statistics_dimension |
+-------+-------------------+-------------------+-----+
| id | accumulatedValue1 | accumulatedValue2 | ... |
+-------+-------------------+-------------------+-----+
| 1 | [not relevant] | [not relevant] | ... |
| 2 | [not relevant] | [not relevant] | ... |
| 3 | [not relevant] | [not relevant] | ... |
| 4 | [not relevant] | [not relevant] | ... |
| 5 | [not relevant] | [not relevant] | ... |
| 6 | [not relevant] | [not relevant] | ... |
| 7 | [not relevant] | [not relevant] | ... |
| ... | [not relevant] | [not relevant] | ... |
| ... | [not relevant] | [not relevant] | ... |
| 10001 | [not relevant] | [not relevant] | ... |
| 10002 | [not relevant] | [not relevant] | ... |
+-------+-------------------+-------------------+-----+
Мы хотим создать наш набор данных отчета примерно так:
SELECT *
FROM fact
INNER JOIN statistics_dimension
ON (fact.statisticsDimId = statistics_dimension.id)
INNER JOIN date_range_dimension
ON (fact.dateDimId = date_range_dimension.id)
WHERE
date_range_dimension.startDateTime = [start]
AND
date_range_dimension.endDateTime = [end]
Проблема в том, что данные в нашем статистическом измерении уже накоплены, и мы не можем инвертировать накопление. Мы вычислили приблизительное количество строк в нашей таблице фактов и получили 5 250 137 022 180. Для наших данных существует около 2,5 миллионов перестановок диапазонов дат, и нам необходимо рассчитать их в нашем измерении даты и таблице фактов из-за накопления. Функция SUM в SQL не работает для нас из-за накопления (вы не можете добавить два значения, которые принадлежат не отличимым наборам).
Есть ли лучшая практика, которой мы могли бы следовать, чтобы сделать ее вычислительной? Что-то не так с нашей схемой?
Нам нужно сообщить данные об онлайн-тренингах. Источник данных - это унаследованный поставщик данных с частями старше 10 лет, поэтому никто не может восстановить внутреннюю логику. Измерение статистики содержит, например, прогресс (в%), выполненный пользователем в веб-тренинге (WBT), количество вызовов на страницу WBT, состояние WBT (для пользователя, например, "выполнено"). также Важная вещь о поставщике данных: он просто дает нам снимок текущего состояния. У нас нет доступа к историческим данным.
2 ответа
Я предполагаю, что вы используете довольно сильное оборудование для этого. У вашего дизайна есть один существенный недостаток - соединение таблицы фактов с измерением "статистика".
Как правило, таблица фактов содержит измерения и меры. Мне кажется, что между вашим измерением "статистика" и таблицей фактов, скорее всего, есть связь 1-1. Поскольку таблицы фактов по сути являются таблицей отношений "многие-многие", не имеет смысла хранить статистику в отдельной таблице. Кроме того, вы говорите, что таблица статистики содержит информацию "по пользователям".
Каждый раз, когда вы говорите "По X" на складе, вы почти всегда можете быть уверены, что X должно быть измерением.
Я бы посмотрел на создание вашей таблицы фактов с мерами прямо на ней. Я не уверен, что вы пытаетесь сделать с "инвертированием" накопления в таблице статистики? Вы имеете в виду, что он накапливается в разных диапазонах дат? Пользователи? Если данные не являются атомарными, лучшее, что вы можете сделать, это дать то, что у вас есть...
Вы можете уменьшить количество измерений, необходимых для расчета этой задачи:
- добавление измерения времени с ежедневной детализацией и без использования текущего дизайна
- объединение статистического измерения с таблицей фактов
В нашем текущем хранилище данных мы используем следующий подход:
time_dimension
time_key (bigint)
time_date (date)
(other time related columns)
fact_table
(keys to other dimensions)
time_key_start (bigint) /* reference to time_dimension, time_key */
time_key_end (bigint) /* reference to time_dimension, time_key */
value_1
value_2
Кроме того, ключи в time_dimension являются "умными". Я знаю, что многие люди не согласятся с таким дизайном, но когда нужно улучшить производительность, мы можем уменьшить количество измерений, используемых в запросе, напрямую запрося time_key, с условием вроде:
time_key_start = to_char('2012-01-01','J')::bigint
and
time_key_end = to_char('2012-01-02','J')::bigint
С таким дизайном вы можете избежать всех объединений в вашем запросе. Затем вы должны сосредоточиться на разделах и индексах таблиц, чтобы повысить производительность.
Возможно, нет необходимости анализировать всю историю данных, и вы можете перенести некоторые данные в архив.