Аналитика по остаткам в SSAS: MDX, Snapshot и Many-To-Many. Часть третья.

добавлено: 04 фев 13
понравилось:0
просмотров: 3447
комментов: 0

теги:

Автор: McCar

Часть третья. Реализация через Many-To-Many
(Начало - здесь:http://www.sql.ru/blogs/dklmnmsbi/1450) и здесь: http://www.sql.ru/blogs/dklmnmsbi/1454)
  Немного порассуждаем.
  Изначальная таблица фактов с проводками содержала у нас 1 454 673 записей.
Таблица фактов с остатками, "расширенная" до каждого дня в измерении,- 2 146 853 записей.
Разница не слишком большая, но дает повод задуматься над вопросом: "От чего может зависеть размер таблицы фактов с остатками"? Каждый факт в этой таблице фиксирует наличие ненулевого остатка в определенный день для определенного сочетания "Товар- Склад..". Стало быть, количество строк в этой таблице фактов равно сумме ряда, где каждый член ряда соответствует одному сочетанию "Товар-Склад" и равен числу дней, в течение которых для данного сочетания был ненулевой остаток.
  Рассмотрим вырожденный случай. В некий час X три года назад в учетной системе разом появилось 10 тысяч товаров, распределенные по ста складам. Итого - миллион комбинаций товар склад. (Напоминаю, что мы рассматриваем упрощенный случай, в реально жизни гранулярность учета обычно включает дополнительные измерения, например номер приходной накладной, и тогда это число еще увеличивается).
И дальше все три года товар двигался туда-суда, но ни на одном из складов не было такого, что какой то товар обнулялся. Стало быть, число строк таблиц фактов за трехлетний период в этом случае равно 10000*100 *(365*3)=.. чуть больше миллиарда. Многовато даже для SSAS.
  Можно ли как то избежать этого? Попробуем.
Для этого нужно подойти к задаче несколько с другой стороны. До сих пор мы рассматривали остаток как некую цифирь, которую нужно запихнуть в запрос для таблицы фактов и построить на нем меру. Но его можно рассмотреть и как некое состояние, характеризующее "Товар-Склад", и это состояние характеризуется неким периодом времени, в течении которого оно действительно.
  Из этого следует логика дальнейших действий:
  а)Мы выделяем в предметной области - "Период" как отдельную аналитическую сущность и соответственно отдельное измерение.
Ключом этого измерения логично сделать два поля - Дату начала периода и Дату конца периода.
Приятным бонусом к этому измерению будет приложен атрибут "Длительность периода", который логично и удобно использовать для аналитики по длительности любых бизнес процессов.
  б)Таблицу фактов мы строим только для изменений остатка. С учетом того, что для некоторых сочетаний "Товар-Склад" бывает несколько движений в течении дня, а нас интересует остаток только на конец дня (ну или начало), эта таблица фактов будет содержать не больше строк, чем исходная таблица с проводками.
Для связи с измерением "Период" у нас в этой таблице фактов будут два поля. Их физический смысл - "Дата появления остатка с таким значением на складе" и "Дата изменения этого значения". Например, если 1 января 2011 г на склад X попал товар Y в количестве 10 штук, а 10 января этот товара со склада был израсходован в кол-ве 5 штук, то для связи с измерение "Период" мы используем две даты - 1 января и 9 января. Если же больше движений по этому товару на этом складе не было, второй датой мы берем дату - сегодня (GETDATE()).
  в)но нам нужна связь с нашим обычным измерение "Дата". Здесь вполне очевидно напрашивается Many-To-Many, так как одному элементу измерения "Периоды" может соотв. более одного элемента измерения "Даты". Для этой связи нам нужна Bridge группа мер, в которой каждой дате мы соотносим те периоды, в которые эта дата попадает.
  г)Увязав все это в кучу, мы получаем снова полуаддитивную меру с остатками, но на этот раз без катастрофического разбухания таблицы фактов.
  д)Так как красоту подхода в бухгалтерии не оценят, а Альфред Нобель жил в докомпьютерную эпоху.. придется довольствоваться моральным удовлетворением... :-)
--
  Итак, поехали.
  а)Строим измерение "Период".
Именованный запрос в .dsv для этого измерения выглядит так:
/*Попарная комбинации всех дат из диапазона от начала аналитики до текущей даты*/
SELECT dbo.udfDateToInteger(d1.DATE_CURRENT) AS DATE_BEGIN_PERIOD_KEY/*Дата начала периода*/,
dbo.udfDateToInteger(d2.DATE_CURRENT) AS DATE_END_PERIOD_KEY /*дата конца периода*/,
 DATEDIFF([day], d1.DATE_CURRENT, d2.DATE_CURRENT) AS INTERVAL, 
 CONVERT(char(10), d1.DATE_CURRENT, 103) + '-' + CONVERT(char(10), d2.DATE_CURRENT, 103) AS N_PERIOD
FROM   dbo.udf_GetDateRange('20110801', GETDATE()) d1 
JOIN dbo.udf_GetDateRange('20110801', GETDATE()) AS d2 ON d1.DATE_CURRENT <= d2.DATE_CURRENT

а само измерение - так:
Картинка с другого сайта.

  б)Таблица фактов для группы мер с остатками.
select mr.C_MOVE, mr.C_PRODUCT, mr.C_WARE_HOUSE,  dbo.udfDateToInteger(mr.DATE_BEGIN) as DATE_BEGIN_KEY,  dbo.udfDateToInteger(mr.DATE_END) as DATE_END_KEY , mr.MOVE_REM from vOlapMoveRemByPeriods mr


  в)Таблица фактов F_BRIDGE_DATE_VALUES_IN_DATE_RANGES для промежуточной - Bridge группы мер , необходимой для Many-To-Many связи с измерением Даты.
SELECT         dbo.udfDateToInteger(d1.DATE_CURRENT) AS DATE_BEGIN_PERIOD_KEY,  dbo.udfDateToInteger(d2.DATE_CURRENT) AS DATE_END_PERIOD_KEY,  dbo.udfDateToInteger(dates_in_period.DATE_CURRENT) AS DATE_IN_PERIOD_KEY
FROM dbo.udf_GetDateRange('20110801', GETDATE()) AS d1 
JOIN  dbo.udf_GetDateRange('20110801', GETDATE()) AS d2 ON d1.DATE_CURRENT <= d2.DATE_CURRENT 
CROSS APPLY[dbo].[udf_GetDateRange](d1.DATE_CURRENT, d2.DATE_CURRENT) dates_in_period


  г)Связываем все это в структуре куба в dsv, строим нужны меры:
Картинка с другого сайта.

и прописываем Many-to-many связи
Картинка с другого сайта.

Деплоим, процессим, тестим. Теперь у нас есть три варианта реализации мер с остатками.
Идентичность значений можно проверить например таким запросом:
select 
{[cmRemByINCOME_OUTCOME],[REM_FOR_EVERY_DAY],[REM_BY_PERIODS]} on 0,
non empty [DimDate].[Hierarchy].[Month].&[201211].Children on 1 
from [Inventory-Cube]
--where{[DimWareHouses].[WareHouseParent].&[110119643]}


Картинка с другого сайта.

   д)Таким образом, мы реализовали одну задачу тремя способами.
Вопрос, в каких случаях от этого может быть практическая польза (ну кроме уважительного "Месье знает толк в извращениях" от специалистов MS BI :-) ), я постараюсь обсудить в следующей статье..
  Замечу, что этот подход хорошо работает если у нас есть дофига чего дофига где, но двигается оно не слишком часто.
Если у нас каждый день по каждому сочетанию "Товар-Склад" происходят изменения, то уменьшения таблицы фактов - не будет. А уменьшение производительности из за M-2-M обязательно произойдет.
Продолжение - следует.
(SSAS проект и скрипт для создания метаданных в реляционной базе можно взять здесь:https://www.dropbox.com/s/e42ueesskoexk83/Inventory-SSAS-Project.zip )
----------------
Кальманович Дмитрий.

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии