Материал опубликован с разрешения компании
Автор оригинала: Ральф Кимбал (все
статьи)
Перевод на русский язык: Константин Лисянский
Оригинальный документ располагается здесь.
Общей проблемой загрузки хранилища данных является проверка правильности
данных. Является ли хранилище точным образом производственной системы?
Прошла ли до конца сегодняшняя утренняя загрузка? Могли ли повредиться
некоторые значения?
Не существует единого метода оценки правильности загрузки данных,
поскольку источники данных очень разнообразны. Если вы выгружаете
неизменённый образ данных из производственной системы, сохраняя
первоначальный уровень детализации, то вы, возможно, можете выполнить
"отчёт по контрольным суммам" на производственной системе
с выводом итоговых данных на текущий момент, а затем повторить тот
же самый отчёт на данных хранилища. В этом случае вы "знаете"
ответ заранее, и два результата должны сойтись с точностью до последней
цифры после запятой.
Но более типичной является ситуация, когда нет известных базовых
данных. Может быть, вы каждую ночь получаете данные о розничных
продажах из 600 магазинов. Естественно, вы можете выполнить подсчёт
общей суммы по отчётам из магазинов, однако как же оценить то, что
данные "наверное, правильные"?
Давайте, используя пример с 600 магазинами, посмотрим на суммарные
данные по продажам каждого отдела каждого магазина за каждое утро,
и зададимся вопросом, являются ли цифры, полученные сегодня утром
"разумными". Мы примем решение о том, то сегодняшние данные
об общей сумме продаж являются разумными, если они попадают в пределы
трёх стандартных отклонений от средних суммарных продаж за предыдущие
периоды того же самого отдела в том же магазине. Уф. Статистика.
Но, подождите: всё не так уж плохо. Мы выбираем три стандартных
отклонения, поскольку в случае "нормального" распределения
99% значений лежат в пределах 3 стандартных отклонений выше или
ниже среднего значения.
Я опишу процесс словами. После этого я приведу несколько операторов
SQL. Если вы хотите уловить основной смысл, прочитайте только описание.
Для того чтобы сделать так, чтобы процесс выполнялся быстро, необходимо
избежать просмотра полной истории старых данных при подсчёте стандартного
отклонения. Вы можете избежать просмотра всей истории, если будете
накапливать данные по каждому отделу каждого магазина в специальной
таблице, которая будут использоваться только на этапе проверки правильности
данных. Вам нужно хранить количество дней, за которое собираются
данные, объём продаж на каждый день (для каждого отдела каждого
магазина) и сумму КОРНЯ КВАДРАТНОГО объёма продаж на каждый день
(для каждого отдела каждого магазина). Это всё можно было бы хранить
в отдельной маленькой таблице. Степень детализации этой таблицы
- отдел магазина, а три числовых поля КОЛИЧЕСТВО ДНЕЙ, ОБЪЁМ ПРОДАЖ
и КОРЕНЬ КВАДРАТНЫЙ ОБЪЁМА ПРОДАЖ являются атрибутами типа 1, которые
каждый день обновляются путём полной перезаписи содержимого. Вы
можете обновить все три поля, просто прибавив значения за следующий
день к уже имеющимся значениям. Таким образом, если у вас 600 магазинов
и по 20 отделов в каждом из них, в этой таблице будет 12000 строк,
и она не будет расти со временем. В каждой строке таблицы также
хранятся названия магазинов и названия отделов.
Теперь, используя эту аккумулирующую таблицу отделов, вы смотрите
на 12000 итоговых сумм по отделам, которые вы загрузили сегодня
утром, и выбрасываете из них те, которые лежат за пределами трёх
стандартных отклонений от средней величины.
Вы можете принять решение проанализировать отдельные необычные
величины, если их не очень много, или можете отвергнуть все загруженные
данные, если увидите, что количество величин, отклонившихся от среднего
значения объёма продаж более чем на три стандартных отклонения,
превышает определённый порог.
Если данные, загруженные сегодняшним утром, прошли проверку, то
вы даёте к ним доступ вашим конечным пользователям, а затем обновляете
аккумулирующую таблицу отделов чтобы приготовиться к завтрашней
загрузке.
Вот фрагменты не протестированных операторов SQL, которые могут
сработать. Помните, что стандартное отклонение равно корню квадратному
из дисперсии. Дисперсия же равна сумме квадратов разности между
каждым значением выборки и средним значением всей выборки, делённой
на N-1, где N - количество дней, за которые мы собрали данные. К
сожалению, эта формулировка заставляет нас обратиться ко всей истории
продаж, что, хотя и возможно, делает вычисления непривлекательными.
Но, если же мы отслеживаем значения СУММА ОБЪЁМА ПРОДАЖ и СУММА
КОРНЯ КВАДРАТНОГО ОБЪЁМА ПРОДАЖ, мы можем записать дисперсию как
(1/(N-1))*(SUM_SQUARE_SALES -(1/N)*SUM_SALES*SUM_SALES). Проверьте
мои выкладки.
Итак, если мы обозначим нашу формулу для вычисления дисперсии как
VAR, то наша проверка правильности будет выглядеть следующим образом:
SELECT s.storename, p.departmentname, sum(f.sales)
FROM fact f, store s, product p, time t, accumulatingdept a
WHERE
(сначала соединяем таблицы…)
f.storekey =s.storekey and
f.productkey =p.productkey and
f.timekey =t.timekey and
s.storename =a.storename and
p.departmentname =a.departmentname and
(затем ограничиваем время сегодняшним днём для того, чтобы получить
только что загруженные данные…)
t.full_date =#July 13,2000#and
(наконец, вводим ограничение на стандартное отклонение…)
HAVING
ABS(sum(f.sales)-(1/a.N)*a.SUM_SALES)>3*SQRT(a.VAR)
где мы раскрываем формулу для вычисления значения VAR до выражения,
приведённого в предыдущем объяснении, и используем префикс "a."
перед N, SUM_SALES и SUM_SQUARE_SALES. Мы предположили, что отделы
представляют группы продуктов и, следовательно, доступны как свёртки
вдоль измерения "Продукт".
Украшением для этой схемы могло бы послужить выполнение двух запросов:
один для вычисления объёмов продаж, ПРЕВЫШАЮЩИХ средний объём более
чем на три стандартных отклонения, и второй - для вычисления значений,
которые МЕНЬШЕ чем средний объём более чем на три стандартных отклонения.
Может быть, для этих двух ситуаций существуют разные объяснения.
Это также поможет избавиться от функции ABS, если ваш диалект SQL
не поддерживает её в предложении HAVING.
Если у вас обычно день ото дня наблюдаются значительные флуктуации
объёма продаж (например, понедельник и вторник - очень медленные
в сравнении с субботой), вы можете добавить значение ДЕНЬ_НЕДЕЛИ
к аккумулирующей таблице отделов и ввести ограничение на день. Таким
образом, вы не примешиваете обычные дневные флуктуации к нашему
тесту стандартного отклонения.
Если вы нашли в сети интересные ссылки на ресурсы по технологиям
хранилищ данных, OLAP, CRM или data mining, и хотите поделиться
ими с другими, присылайте их.
Я с удовольствием размещу их на этом сайте.
RSS