Пример быстрого развертывания системы аналитики на предприятии
Итак, как развернуть систему аналитики на предприятии за максимально короткий промежуток времени? Для этого воспользуемся связкой MS Excel + MS Access. В Access развертываем систему хранения детальных данных, а MS Excel используем как систему визуализации при помощи сводных таблиц.
Вопросы, на которые должна давать ответы аналитическая система:
- Анализ выручки по предприятию в разрезе групп и месяцев.
- Проведение ABC и XYZ анализов.
- Мониторинг акций, проходящих в магазине.
План действий такой:
- Предварительный анализ размера системы, подсчет количества строк и размера БД.
- Разработка ТЗ в отдел ИТ для проектирования выгрузки данных из системы управления в нашу систему аналитики.
- Под ТЗ проектируем БД в Access для дальнейшего автоматического импорта выгрузки под наши нужды.
- Ну, и самый, пожалуй, легкий, для знатоков Excel - это создание сводной таблицы на основе среза из Access.
Произведем оценку возможности развертывания такой системы на предприятии. Пусть имеется предприятие, продающее бытовую технику. Оцениваемый ежемесячный объем продаваемых товаров через магазин в день составляет 52 товара. Пусть у предприятия имеется 30 магазинов. Тогда оценочный годовой прирост БД составит порядка 52*30*365= 569 400 строк для хранения информации о продажах с каждодневной разбивкой (далее будем называть это Детальным Слоем). Итак, полмиллиона строк - это много или мало? На самом деле, я работал и с 8 000 000 строк в БД Access, и это не составляло особой трудности. То есть БД Access можно использовать в течении 14 лет для хранения детального слоя. Далее мы можем свободно перейти и на другие БД, главное - суметь доказать, что такие проекты приносят реальную пользу и могут быть развернуты в принципиально короткие сроки.
Проектирование ТЗ для выгрузки из системы управления
Начинаем писать ТЗ на выборку данных для загрузки в детальный слой:
- Составить запрос информации в разрезе:
- Магазин (для рассмотрения динамики в разрезе магазинов).
- Дата (для выявление сезонных и ежедневных колебаний в предпраздничные дни).
- Название товара (для рассмотрения АВС анализа по товарам).
- Производитель товара (для исследования распределения брендов).
- Количество товара, сумма реализации (сами данные).
- Номер дисконтной карты (проникновение акции среди покупателей).
- Продавец-консультант (по возможности, для отслеживания наиболее успешных продавцов).
- Номер сертификата (для отслеживания продвижения акций с сертификатами).
- Номер заказа или чека (для отслеживания взаимосвязанных товаров).
- Тип магазина: интернет или обычный (отслеживание доли интернет продаж).
- Группы товаров (для ABC и XYZ анализа).
- Покупатель (для отслеживания продаж по покупателям).
- Направление продаж (B2B, B2C).
- Название акции, по которой продается товар.
- Организовать выгрузку в файл, который сможет принять большой объем данных (можно и в CSV, или в dBASE). При экспорте в CSV файл получается большой и надо правильно выбрать разделитель полей (его не должно быть в названии товара!)
Прописываем каталог для обмена данными с IT и приступаем к проектированию БД в системе Access.
Проектирование БД
Пока готовятся выборки, можно пить кофе и думать, как жить дальше. Хорошо спроектированная БД должна в минимальные сроки ответить на вопросы, которые будут возникать в процессе эксплуатации БД. Приведем пару важных вопросов, которые точно встретятся:
- Что добавилось (способна ли ваша БД отследить добавление данных и принять их)?
- Как разработать процедуру добавления данных в БД?
При проектировании БД для аналитических систем будем придерживаться схемы звездочка (предпочтительно) или снежинка. Более подробно по схемам развертывания можно ознакомиться здесь (Организация хранилища). Ниже я привел пример БД, который использовался при развертывания системы на реальном предприятии. Не все поля из ТЗ оказалось возможным выгрузить. На том, что удалось выгрузить, спроектировали БД для аналитики.
Здесь выбрана система снежинка для развертывания БД. Хотя, при удалении таблицы city схема превратится в обычную звездочку. Таблицы, расположенные в центре, являются ключевыми в проекте. Первая таблица cube_sale - это детализированный слой хранения данных, вторая tmp_pr5 таблица со входными данными будет любезно предоставляться для нашей системы отделом ИТ.
Соответствие tmp_pr5 с консольными таблицами осуществляется по названиям (товара, групп товара, бренду и т.д.), тогда как cube_sale связывается с консольными таблицами по ключу для экономии места размещения и скорости работы БД. На основании cube_sale будем строить отчеты и проектировать кубы для дальнейшего анализа. Параллельно можно спроектировать другие детальные слои при необходимости.
Для вставки данных в cube_sale требуется проверка на соответствие всех данных во входной таблице tmp_pr5 с таблицами измерений. Как пример можно предложить следующий SQL запрос для проверки начала продаж нового бренда:
SELECT DISTINCT tmp_prd5.TOVAR_SVST FROM tmp_prd5 WHERE tmp_prd5.TOVAR_SVST not in (SELECT DISTINCT tovar_brend_name.tovar_brend_name FROM tovar_brend_name);
Однако, такой простой запрос очень долго работает. Для ускорения обработки запроса можно разработать более сложный:
SELECT DISTINCT "tovar_brend_error" as table_error, tmp_prd5.TOVAR_SVST as missing_value FROM tmp_prd5 LEFT JOIN tovar_brend_name ON tmp_prd5.TOVAR_SVST = tovar_brend_name.tovar_brend_name where isnull(tovar_brend_name.tovar_brend_name);
Есть один небольшой момент: нельзя в данном случае допускать пустые названия брендов.
Аналогично поступаем для каждой таблицы. Можно написать и один запрос, который будет отслеживать все новые значения в таблицах измерений.
В качестве примера привожу разработанный запрос для отслеживания новых данных в таблице:
SELECT DISTINCT "1sklad_error" as table_error, tmp_prd5.SKLAD as missing_value FROM tmp_prd5 LEFT JOIN sklad ON tmp_prd5.SKLAD = sklad.sklad_name where isnull(sklad.sklad_name) union SELECT DISTINCT "2tovar_group_error" as table_error, tmp_prd5.TOVAR_GRUP as missing_value FROM tmp_prd5 LEFT JOIN tovar_group_name ON tmp_prd5.TOVAR_GRUP = tovar_group_name.tovar_group_name where isnull(tovar_group_name.tovar_group_name) union SELECT DISTINCT "3tovar_brend_error" as table_error, tmp_prd5.TOVAR_SVST as missing_value FROM tmp_prd5 LEFT JOIN tovar_brend_name ON tmp_prd5.TOVAR_SVST = tovar_brend_name.tovar_brend_name where isnull(tovar_brend_name.tovar_brend_name) union SELECT DISTINCT "4kontr_group_error" as table_error, tmp_prd5.KONTR_GRUP as missing_value FROM tmp_prd5 LEFT JOIN kontr_group ON tmp_prd5.KONTR_GRUP = kontr_group.kontr_group_name where isnull(kontr_group.kontr_group_name) union SELECT DISTINCT "5kontr_error" as table_error, tmp_prd5.KONTR as missing_value FROM tmp_prd5 LEFT JOIN kontr ON tmp_prd5.KONTR = kontr.KONTR_name where isnull(kontr.KONTR_name) union SELECT DISTINCT "6prodavec_error" as table_error, tmp_prd5.PRODAVEC as missing_value FROM tmp_prd5 LEFT JOIN prodavec ON tmp_prd5.PRODAVEC = prodavec.prodavec_name where isnull(prodavec.prodavec_name) UNION SELECT DISTINCT "7tovar_error" as table_error, tmp_prd5.TOVAR as missing_value FROM tmp_prd5 LEFT JOIN tovar ON tmp_prd5.TOVAR = tovar.tovar_name where isnull(tovar.tovar_name);
Получилось 7 запросов, которые отслеживают изменения в каждой таблице. Далее нужно вставить недостающие данные в таблицы измерений. Если не произвести вставку данных в таблицы измерений, то часть данных, которых прислал отдел ИТ, будет утеряна при вставке данных в cube_sale из tmp_prd5.
Для вставки данных из tmp_prd5 в cube_sale я использовал следующий запрос:
INSERT INTO cube_sale (time_dem, id_sklad, id_tovar, id_tovar_group, id_tovar_brend, id_kontr_group,id_kontr, ANALITPRIZ, DINAM_SV, STAT_SV, id_prodavec, KOL_PROD, SUM_PROD, KOL_OBMEN, SUM_OBMEN, KOL_VOZV, SUM_VOZV, SUM_PRVZ, SUM_PROBM, SKIDKA ) SELECT CDate(tmp_prd5.PERIOD) AS time_dem, sklad.id_sklad, IIf(IsNull(tmp_prd5.TOVAR),1,tovar.id_tovar) AS id_tovar, IIf(IsNull(tmp_prd5.TOVAR_GRUP),1,tovar_group_name.id_tovar_group) AS id_tovar_group, IIf(IsNull(tmp_prd5.TOVAR_SVST),1,tovar_brend_name.id_tovar_brend) AS id_tovar_brend, IIf(IsNull(tmp_prd5.KONTR_GRUP),1,kontr_group.id_kontr_group) AS id_kontr_group, IIf(IsNull(tmp_prd5.KONTR),1,kontr.id_kontr) AS id_kontr, tmp_prd5.ANALITPRIZ, tmp_prd5.DINAM_SV, tmp_prd5.STAT_SV, IIf(IsNull(tmp_prd5.PRODAVEC),1,prodavec.id_prodavec) AS id_prodavec, tmp_prd5.KOL_PROD, tmp_prd5.SUM_PROD, tmp_prd5.KOL_OBMEN, tmp_prd5.SUM_OBMEN, tmp_prd5.KOL_VOZV, tmp_prd5.SUM_VOZV, tmp_prd5.SUM_PRVZ, tmp_prd5.SUM_PROBM, tmp_prd5.SKIDKA FROM ((((((tmp_prd5 LEFT JOIN tovar_group_name ON tmp_prd5.TOVAR_GRUP=tovar_group_name.tovar_group_name) LEFT JOIN tovar_brend_name ON tmp_prd5.TOVAR_SVST=tovar_brend_name.tovar_brend_name) LEFT JOIN tovar ON tmp_prd5.TOVAR=tovar.tovar_name) INNER JOIN sklad ON tmp_prd5.SKLAD=sklad.sklad_name) LEFT JOIN kontr ON tmp_prd5.KONTR=kontr.KONTR_name) LEFT JOIN kontr_group ON tmp_prd5.KONTR_GRUP=kontr_group.kontr_group_name) LEFT JOIN prodavec ON tmp_prd5.PRODAVEC=prodavec.prodavec_name;
Использовать для вставки пришлось по некоторым полям функцию IIf, так как в консольных таблицах были пустые названия (групп товара, брендов и т.д.). Пустые названия в таблицах измерений находились с ключевым значением 1, поэтому использовалась следующая связка IIf(IsNull(tmp_prd5.TOVAR_GRUP),1,tovar_group_name.id_tovar_group), где 1 – это пустая группа товара.
В качестве первой версией куба можно рассмотреть запрос, при котором будет создаваться куб для анализа данных в Excel.
Проект запроса будет следующий в SQL:
SELECT sklad.sklad_name, time_dem.Year, time_dem.Month, time_dem.quarter, time_dem.week, tovar_brend_name.tovar_brend_name, Sum(cube_sale.KOL_PROD) AS kol_tovara, Sum(cube_sale.SUM_PROD) AS sum_prod, tovar_group_name.tovar_group_name FROM time_dem INNER JOIN (tovar_group_name INNER JOIN (tovar_brend_name INNER JOIN (sklad INNER JOIN cube_sale ON sklad.id_sklad = cube_sale.id_sklad) ON tovar_brend_name.id_tovar_brend = cube_sale.id_tovar_brend) ON tovar_group_name.id_tovar_group = cube_sale.id_tovar_group) ON time_dem.time_dem = cube_sale.time_dem GROUP BY sklad.sklad_name, time_dem.Year, time_dem.Month, time_dem.quarter, time_dem.week, tovar_brend_name.tovar_brend_name, tovar_group_name.tovar_group_name;
Этот куб способен отвечать на следующие группы вопросов. Какова реализация по складам (магазин), по годам, месяцам, кварталам, брендам, группам товаров, выручки товаров и количеству товаров в любом разрезе.
Далее остается только соединить этот запрос с Excel и строить любые графики и сводные таблицы.
Алгоритм соединения очень прост:
- Данные -> Получить данные из Access.
- Выбираем подготовленный запрос.
- Вставляем сводную таблицу и диаграмму.
- Далее работаем как со сводной таблицей.
На реализацию проекта ушло 2 рабочих дня. Была собрана детальная статистика за период с марта 2009 по март 2013 года. Загрузка данных осуществлялась за годовой промежуток времени. Возникающие сложности в проекте носили лишь технический характер. Такие сложности могут решаться специалистами очень оперативно, главное - не допускать ошибок при проектировании и вставке значений в целевые таблицы.
В конце проделанной работы нужно ответить на вопрос, правильно ли мы поставили цели по развертыванию системы и требуется ли доработка системы в целом. 2 дня, которые ушли на разработку и проектирование системы, помогли понять пути дальнейшего совершенствования системы и постановки задач на будущее. (Это принципиально короче, чем развертывание полноценной системы аналитики на предприятии).
Схема-алгоритм
Дополнительные материалы и ссылки.
Файлы для скачивания (БД Access + Excel файлы + Презентация). Здесь, в качестве примера, приведена входная таблица, основанная на данных БД foodmart (так как я не собираюсь разглашать данные своей компании по товарам). Далее планируется создать видеоурок о том, как развернуть на данной БД статистику. Ссылка на видео урок.
Ссылка по работе со сводными таблицами http://planetaexcel.ru/techniques/8/
- Подробности
- Опубликовано: 06 Май 2013
- Просмотров: 6421