ABC-анализ в SQL Server Analysis Services. Практическое руководство.

ABC-анализ – достаточно распространённый метод, который позволяет определить степень важности ресурса. Чаще всего он применяется к продаваемым товарам или предлагаемым услугам. Подробнее о самой концепции можно прочитать статью на Wikipedia. Нас же интересует практическая реализация данной методологии на базе SQL Server Analysis Services (далее SSAS). Итак, приступим. Практическое руководство будет показано на примере БД розничной торговой сети.

Во-первых нам нужно определиться что же мы будем анализировать. Анализировать мы будем товары, которые продаем. Соответственно у нас уже есть некий базовый SSAS-проект в котором присутствует измерение Номенклатура и показатели розничных продаж: Количество, Сумма, Сумма со скидкой, Маржа и % маржи. Теперь, выбрав нужный показатель, мы должны категорировать Номенклатуру по критериям 80% — A, 15% — B, 5% — C. Как же это реализовать в SSAS и при этом добиться приемлемой производительности? После нескольких вариантов реализации мы пришли к наиболее оптимальному решению (по нашему мнению), о котором сейчас и расскажем.

В интернете можно найти следующую реализацию ABC-анализа. Создаем именованный динамический набор, который содержит список элементов Номенклатуры с 80% — A и 15% — B. Используется функция TopPercent. Выглядит это примерное так:

CREATE HIDDEN DYNAMIC SET CURRENTCUBE.[A,80%] AS
  TopPercent([Номенклатура].[Номенклатура].[Номенклатура], 80, [Measures].[Сумма]);
CREATE HIDDEN DYNAMIC SET CURRENTCUBE.[B,15%] AS
  TopPercent([Номенклатура].[Номенклатура].[Номенклатура], 95, [Measures].[Сумма]);

Соответственно, вычисляемая мера ABC может выглядеть следующим образом:

CREATE MEMBER CURRENTCUBE.[Measures].[ABC] AS
CASE

                WHEN [Measures].[Сумма] >= (Tail([A,80%]).ITEM(0), [Measures].[Сумма]) THEN "A"
                WHEN [Measures].[Сумма] >= (Tail([B,15%]).ITEM(0), [Measures].[Сумма]) THEN "B"
                ELSE "C"
END;

Тут всё просто. Динамический набор содержит в себе отсортированные в обратном порядке по Сумме элементы Номенклатуры для определенной категории. Последний элемента набора A,80% содержит элемент Номенклатуры, которая бы попала в категорию A, поэтому мы можем взять значение Суммы этого элемента как граничное значение. Метод прост, понятен, достаточно быстро работает (с некоторыми оговорками, см. ниже) и широко применяется. Из недостатков следует выделить прежде всего использование динамических наборов, которые сильно тормозят UI, например фильтры в Excel при работе с кубом, т.к. наборы постоянно пересчитываются даже когда это не требуется и тот факт, что этот метод работает только на нижнем уровне иерархии Номенклатуры. Попытаемся исправить оба недостатка.

Начнем с уровней иерархии. Чтобы метод заработал на любом уровне нам нужно создать по два набора (A, B) для каждого уровня, а с учетом “притормаживания” этот метод не наш выбор. Кроме того хотелось бы динамически подставлять показатель, для которого рассчитывается категория ABC. С этого и начнем. Создадим новое измерение, которое назовем Показатели. Но для этого нам потребуется создать именованный запрос в нашем DSV.

abc1

И само измерение:

Measures Dimension

Теперь нужно добавить вычисляемую меру Текущий показатель. По-умолчанию она будет представлять собой Сумму, а при выборе в фильтре элемента измерения Показатели динамически подменяться:

Current Measure

Теперь создадим два именованных набора C,5% и B,15%. Решено было использовать функцию BottomPercent вместо TopPercent, т.к. более точно определяется попадание в категории C и B.

ABC dynamic sets

И, собственно, сама вычисляемая мера ABC:

ABC measure

По нижнему уровню иерархии используется метод с динамическими наборами, в остальных вариантах используется другой метод без наборов, который по текущему уровню вычисляет принадлежность к категории. На нижем уровне из-за кол-ва элементов Номенклатуры он существенно проигрывает методу с динамическими наборами, но выигрывает в производительности на верхних уровнях.

В общем и целом практически всё готово. Осталось только “победить” существенные задержки в UI из-за использования динамических наборов. Наше решение хоть и не выглядит изящным, но зато работает в полной мере. Потребуется библиотека ASSP. На момент написания статьи реализуемый в библиотеке функционал версии 1.3.6 немного не соответствовал нашим потребностям, поэтому за основу была взята версия из репозитория (предварительная 1.3.7 с поддержкой GetCurrentCommand) и немного доработана. Нами был изменен файл StringFilters.cs и добавлена функция RegexIsMatch, которая в отличие от стандартной IsLike принимает в качестве шаблона паттерн регулярного выражения. Компилируем сборку, подключаем к инстансу SSAS и редактируем именованные наборы следующим образом.

ABC dynamic sets with optimization

Суть всего вышенаписанного заключается в том, что при использовании фильтров в Excel генерируется запрос, который возвращает список элементов измерения. В этом случае нет никакого смысла вычислять именованные наборы, которые должны рассчитываться только, если в текущем запросе встречается название меры ABC и измерение Номенклатура.

Полный текст скрипта куба, реализующего ABC-анализ можно скачать здесь.

Один ответ на ABC-анализ в SQL Server Analysis Services. Практическое руководство.

  1.  

    Дмитрий, выложи если не затруднит измененный файл StringFilters.cs и добавленаю функцию RegexIsMatch

leave your comment


+ 8 = четырнадать