882 читателя, 38 постов
Администрация
Модераторы
Блог о том, как правильно писать запросы, процедуры и другие функциональные вещи для различных СУБД.
olap для маленькой компании
В посте Многомерные кубы, OLAP и MDX Vitko написал: «тема очень интересная и с каждым днем становится все более актуальной». К сожалению, это заклинание произносится уже очень давно (по крайней мере я его слышу с 2004 года ), но olap проектов до сих пор очень мало. Возможно, потому что традиционно считается, что всё, что связанно с olap нужно только для крупных компаний с большими объемами накопленных данных и стоит очень дорого. Но это не совсем так. Я хочу рассказать о проекте, который внедрен в одной относительно небольшой компании.
Проект очень древний, начинался ещё в 2003 году. Про некоторые вещи можно сказать «так исторически сложилось». Но, мне кажется общая идея, может быть полезной.
Итак. Компания занимается оптовой торговлей кондитерскими изделиями. Опт кондитерки достаточно специфичный бизнес. При относительно небольших оборотах приходится иметь дело с большими объемами данных. Клиентами компании являются как крупные торговые сети, так и небольшие магазинчики в деревнях области. Плюс огромный ассортимент продукции. Причем клиент может купить любой объем товара – от одного сникерса до вагона печенья (были прецеденты, когда на склад возврата товара поступало половинка зефиринки (история умалчивает какой было причина возврата) ).
Основная учетная система — 1С «Торговля и склад» 7.0, причем dbf версия. Она достаточно успешно справляется с задачами учета товара. Но получить в ней отчеты за большие периоды времени практически нереально. Подобные попытки создают серьезную нагрузку на сервер, начинаются проблемы у операторов 1с, жалобы в It отдел.
Потребность в таких отчетах была постоянная. Сложилась идеальная ситуация для реализации bi проекта: большой объём информации + люди заинтересованные в её анализе.
Для начала, небольшой ролик, демонстрирующий, как пользователь может сам получать информацию.
Посмотреть avi в нормальном качестве можно скачав отсюда 5,25Mb ( 6 минут )
Поработать с локальным кубом можно скачав пример 2,64Mb
или тут 8Mb
Синие стрелки — пути, которыми информация попадает в систему, зеленными – как информация в дальнейшем используется.
Информация из хранилища используется не только кубами, но и внешними приложениями, например эти данные нужны для расчета зарплаты, для учета оплат-поставок, для планирования работы менеджера. В тоже время данные из этих внешних программ также попадают в кубы.
С кубами работают сотрудники в офисе – руководство, менеджеры, маркетинг, бухгалтерия. Так же информация отправляется поставщикам и торговым представителям в разных городах области.
Иногда пользователю нужно периодически получать отчеты, содержащие большие объемы данных. Например, отдел маркетинга отправлял отчеты поставщикам в виде екселевских файлов содержащих по несколько десятков страниц.
Olap не «заточен» для получение такой информации – отчеты формировались очень долго.
Как правило, поставщику тоже неудобно работать с большими отчетами. Поэтому большая часть, попробовав работать с локальными кубами, согласилась получать отчетность в таком виде. Список отчетов, которые формировал отдел маркетинга, значительно сократился. Оставшиеся тяжелые отчеты были реализованы в SSRS, созданы подписки (отчеты формируются автоматически и рассылаются поставщикам по расписанию)
процессор: 2xAMD Opteron 280
память: 4Gb
дисковые массивы:
операционная система: RAID 1 (зеркало) 2xSCSI 15k
данные: RAID 0+1 4xSCSI 10k
Согласитесь, такую машинку сложно назвать «мощным» сервером
хранилище 10Гб, данные с 2002 года
агрегация 30%
Размер многомерной базы 350М
кол-во членов «больших измерений»: товары 25 тыс., адреса – 20 тыс.
кол-во документов в день — 400. среднее кол-во строк в документе — 30
Проект очень древний, начинался ещё в 2003 году. Про некоторые вещи можно сказать «так исторически сложилось». Но, мне кажется общая идея, может быть полезной.
Итак. Компания занимается оптовой торговлей кондитерскими изделиями. Опт кондитерки достаточно специфичный бизнес. При относительно небольших оборотах приходится иметь дело с большими объемами данных. Клиентами компании являются как крупные торговые сети, так и небольшие магазинчики в деревнях области. Плюс огромный ассортимент продукции. Причем клиент может купить любой объем товара – от одного сникерса до вагона печенья (были прецеденты, когда на склад возврата товара поступало половинка зефиринки (история умалчивает какой было причина возврата) ).
Основная учетная система — 1С «Торговля и склад» 7.0, причем dbf версия. Она достаточно успешно справляется с задачами учета товара. Но получить в ней отчеты за большие периоды времени практически нереально. Подобные попытки создают серьезную нагрузку на сервер, начинаются проблемы у операторов 1с, жалобы в It отдел.
Потребность в таких отчетах была постоянная. Сложилась идеальная ситуация для реализации bi проекта: большой объём информации + люди заинтересованные в её анализе.
Для начала, небольшой ролик, демонстрирующий, как пользователь может сам получать информацию.
Посмотреть avi в нормальном качестве можно скачав отсюда 5,25Mb ( 6 минут )
Поработать с локальным кубом можно скачав пример 2,64Mb
или тут 8Mb
Как это реализовано:
Синие стрелки — пути, которыми информация попадает в систему, зеленными – как информация в дальнейшем используется.
- Информация о заказах заносится в систему 1с – dbf версия.
- Загрузка данных «автообмен». Вообще – то это лишний шаг. Данные можно получать напрямую из dbf базы. Но программисты 1с решили что стандартный (для 1с ) механизм выгрузки данных, принесет меньше вреда.
- Раз в сутки изменения за прошедший день выгружаются в специально подготовленную базу MsSql – хранилище. Выгружается не вся информация, а только то, что нужно для кубов.
В принципе необязательно строить «хранилище». Данные для куба можно получать напрямую из базы 1с ( MsSQL или dbf ). Но в моем случае из 1с данные прошлых периодов периодически удаляются и очищаются справочники. Кроме того перед загрузкой в хранилище данные немного «чистятся».
- Происходит пересчет куба – данные попадают в куб.
Информация из хранилища используется не только кубами, но и внешними приложениями, например эти данные нужны для расчета зарплаты, для учета оплат-поставок, для планирования работы менеджера. В тоже время данные из этих внешних программ также попадают в кубы.
С кубами работают сотрудники в офисе – руководство, менеджеры, маркетинг, бухгалтерия. Так же информация отправляется поставщикам и торговым представителям в разных городах области.
Любой пользователь может получить информацию разными путями:
- Построить отчет самостоятельно на web-странице или в excel
Сначала использовался только excel, но возникало много проблем с тем, что екселевские файлы «разбредались», нужно было получить одну «точку входа» для выбора информации.
Поэтому был создан локальный сайт, на котором опубликованы страницы с PivotTable. Сотрудник, который хочет получить пару цифр «здесь и сейчас» заходит на этот сайт и строит отчет в нужной ему форме. Если человеку нужно использовать этот отчет в дальнейшем – он может написать заявку, чтобы его отчет опубликовали в SSRS или сам сохраняет его в excel.
- Посмотреть стандартный отчет, опубликованный в SQL Server Reporting Services ( SSRS )
- Получить локальный куб – и вне офиса «вращать» данные с помощью excel
- Подписаться на рассылку и получать стандартные отчеты из SSRS на e-mail
- Отдел маркетинга кроме того использует программу CubeSlice. В ней можно создавать локальные кубы самостоятельно и гораздо удобнее, чем в excel
Локальные кубы
Иногда пользователю нужно периодически получать отчеты, содержащие большие объемы данных. Например, отдел маркетинга отправлял отчеты поставщикам в виде екселевских файлов содержащих по несколько десятков страниц.
Olap не «заточен» для получение такой информации – отчеты формировались очень долго.
Как правило, поставщику тоже неудобно работать с большими отчетами. Поэтому большая часть, попробовав работать с локальными кубами, согласилась получать отчетность в таком виде. Список отчетов, которые формировал отдел маркетинга, значительно сократился. Оставшиеся тяжелые отчеты были реализованы в SSRS, созданы подписки (отчеты формируются автоматически и рассылаются поставщикам по расписанию)
Основные параметры системы
Конфигурация сервера:
процессор: 2xAMD Opteron 280
память: 4Gb
дисковые массивы:
операционная система: RAID 1 (зеркало) 2xSCSI 15k
данные: RAID 0+1 4xSCSI 10k
Согласитесь, такую машинку сложно назвать «мощным» сервером
Объем данных:
хранилище 10Гб, данные с 2002 года
агрегация 30%
Размер многомерной базы 350М
кол-во членов «больших измерений»: товары 25 тыс., адреса – 20 тыс.
кол-во документов в день — 400. среднее кол-во строк в документе — 30
Что в итоге получила компания:
Плюсы
Для руководства предприятия
Позволяет посмотреть на ситуацию «сверху», выявить общие закономерности развития бизнеса.
Помогает проследить динамику изменения основных показателей работы организации в целом и оперативно оценивать показатели эффективности работы подчиненных.
Для менеджера
Возможность самостоятельно и в короткие сроки получить информацию необходимую для принятия решения.
Простота работы. Все действия интуитивно понятны
Для поставщиков
Возможность интерактивной работы с информацией
С точки зрения it-специалиста
Уменьшение рутинной работы. Большую часть отчетов пользователь получает самостоятельно.
Минусы:
- Стоимость внедрения. Необходимо дополнительное оборудование и программное обеспечение.
- Нехватка подготовленных специалистов. Расходы на обучение сотрудников it-отдела.
комментарии (60)
И это отличный способ незаметно втащить новую технологию в жизнь компании.
Увы, таких «живых» примеров мало, в основном это набор статей «ах, мы возьмем вот этот продукт oracle… и тут у нас еще business Intelligence… И датамайнинг».
Тем и ценно.
Но это не умаляет ценности примера. =)
— агрегация 30% (особенно если построена встроенным визардом) может быть не самое лучшее решение. Уменьшив количество агрегаций, но сделав целенаправленный, продуманный дизайн может в разы увеличить производительность и уменьшить необходимое дисковое пространство.
— собственно, абсолютно согласен с автором, что OLAP решения могут вполне подходить небольшим компаниям. Например, можно использовать Analysis Services, которые включены в MS SQL Server Standard Edition, или использовать open source-ый Mondrian.
— можно также рассмотреть возможность использования Data Mining и сделать анализ данных еще «более умным!»
— было бы интересно узнать как у вас организованы ETL процессы, используете ли вы MS SQL Server Integration Services (ваша иконка для ETL вроде бы именно оттуда)?
>>решение. Уменьшив количество агрегаций, но сделав целенаправленный, продуманный дизайн
>>может в разы увеличить производительность и уменьшить необходимое дисковое пространство.
Согласна. Но именно в этом проекте объем кубов минимален и нет проблем с производительность. До тех пор, конечно, пока кому-нибудь не захочется построить отчет на 200 страниц: в столбцах все клиенты, а в строках все товары.
>>можно также рассмотреть возможность использования Data Mining
>>и сделать анализ данных еще «более умным!»
с Data Mining сложные взаимоотношения. Конечно технология интересная, но, мне кажется, в данной организации использование стандартных алгоритмов не принесет какой либо пользы. Хотя возможно это иллюзия. Было бы замечательно, если бы кто-нибудь написал о своем примере использования Data Mining в таких небольших компаниях.
>>было бы интересно узнать как у вас организованы ETL процессы,
>> используете ли вы MS SQL Server Integration Services
>>(ваша иконка для ETL вроде бы именно оттуда)?
Да, иконка оттуда :)
Но в этом проекте очень простые etl-алгоритмы, поэтому всё реализовано на процедурах t-sql.
SSIS используется только для обновления измерений и создания / пересчета партиций: “cобираются” и выполняются нужные xmla скрипты.
Я всегда считал что в олапе быстрота агрегирования достигается за счет хранения ненормированных данных, что влечет за собой избыточность и как следствие бльшой размер базы?
хранилище пришлость делать только после того как в 1с решили справочники почистить
Проект действительно может вести один человек. Человек, начинавший проект, был чуть ли не единственным it-шником в компании (в одном лице и 1с-ник и админ ). Я пришла на его место через год примерно.
По большому счету, за пару дней можно сделать куб, так чтобы пользователь мог попробовать работать. А потом, если понравится, «наращивать мясо”
сам я занимался этим 4 года в Ozon.ru, про нас даже на сайте Microsoft писали: Кейс про OLAP в Ozon.ru
Здесь есть еще информация: Business Intelligence в электронной коммерции
Я работал с кубами объемом в десятки гигабайт и столкнулся с проблемой их медленной работы, т.к. все наши кубы содержат как правило не меньше 16 измерений и активно используются distinct count. Пришлось для этого применять партицирование. В итоге скорость работы возросла в десятки раз, вместо 10 минут ожидания, пользователи делали запросы за 10-30 секунд. Хотел статью про это написать, да все времени нет.
А как вы справляетесь с вопросами производительности?
PS: Очень интересно также, применять OLAP кубы в веб-аналитике. Система Sitecatalyst похоже так и работает.
такие отчеты сейчас либо переведенные в SSRS и формируются по подпискам, либо заменены локальными кубами
были проблемы, когда остатки были вычисляемой мерой, когда перевела расчет в хранилище — стало гораздо быстрее
партиции были изначально. Дело в том что хранилища не было, запросы строились на view. А данные в базе 1с лежали по разным базам ( так называемым «обрезкам»). Так что использование партиций было естественным решением
В итоге в кубе получилось порядка 50 партиций, т.к. еще были меры distinct count.
Простое агрегирование по мере ditinct count не работает :-(
У меня сейчас как раз похожая проблема с кубом для билинговой компании ( ditinct count по номерам телефонов ).
Никакие олапы у вас просто не нужны. :0)
Конечно хорошо знать олап, но в данном случае это слишком несоразмерно.
например, тот же отчет в виде куба поставщикам — очень удобная штука
гораздо проще дать куб, чем огромный отчет
«обрезку» делают два раза в год примерно
Я как раз и хотела показать, что на таких небольших объемах очень удобно использовать olap
Задача была снизить нагрузку на учетную систему и дать возможность пользователям самим выбирать информацию
Конечно, можно сделать просто хранилище, и строить отчеты в SSRS на нём.
А в том же екселе работать со сводными таблицами выбирая данные select'ами из хранилища
Но следующий, достаточно естественный шаг — построить olap куб. При этом получаем гораздо большую производительность.
Дополнительного софта для этого не нужно: Analysis Services входит MS SQL Server Standard Edition
Конечно городить такую систему ради даже 77 отчетов не имеет смысла
Но дело в том что данные нужны в разных разрезах — это даже не отчеты как таковые. Можно сказать интерактивный анализ данных.
Маркетологи за день могут строить десятки разных «отчетов» по-разному выбирая данные.
Очередная попытка изобретения искусственного интеллекта для робота по тех.анализу продаж?
В кубах сейчас данные по остаткам, дебеторка, планирование, просрочка и т.п
И при чем тут «искусственный интеллект»? Интеллект как раз естественный. :)
Основные пользователи — маркетологи и менеджеры. Живые люди, которым для работы нужно «понимать что происходит». А для этого получать данные в разных срезах.
Попробуйте покрутить локальный куб в екселе, может быть тогда станет понятно зачем всё это затевалось :)
А «искусственный интеллект» — это скорее Data Mining
И как менеджер увидит в кубе, который построен вчера, что недавно была закрыта просрочка по платежам? :0)
> Основные пользователи — маркетологи и менеджеры. Живые люди, которым для работы нужно «понимать что происходит». А для этого получать данные в разных срезах.
Если у вас 1с просто для красоты и маркетологов и вы с помощью 1с не управляете процессами, то да, главное чтобы данные в разных разрезах. :0)
>> что недавно была закрыта просрочка по платежам? :0)
1. Кстати куб по просрочке считается чаще, раз в час. :)
Кроме того «специальные люди» могут нажав одну кнопку — пересчитать куб ( но вообще то это атавизм, сейчас есть более гибкие механизмы обновления данных )
2. В кубе менеджер скорее получить ответы на вопросы: как менялась просрочка этого клиента? Насколько оперативно клиент в прошлом гасил задолженность? Сколько дней в среднем была просрочка в прошлом?
>> вы с помощью 1с не управляете процессами
Приведите пожалуйста примеры управления процессами с помощью 1с
1С у нас — учетная система, которую долгое время было совершенно не удобно использовать для анализа собранных данных ( хотя в 8-ке, насколько я знаю, появились какие-то механизмы похожие на олап решение )
Задержка обратной связи в час — это просто самоубийство.
> 2. В кубе менеджер скорее получить ответы на вопросы: как менялась просрочка этого клиента? Насколько оперативно клиент в прошлом гасил задолженность? Сколько дней в среднем была просрочка в прошлом?
Прежде всего менеджеру нужно видеть в реальном времени сколько текущая просрочка. Потом, возможно он и захочет проанализировать просрочки за последние два года, но и то это будет скорее простое любопытство, в реальных взаиморасчётах прогнозами на олап кубах как бы не занимаются. :0)
если у Вас нет задач анализа, то конечно олап Вам не нужен
у нас такие задачи есть
о чем спор то?
>>Дело в том что олап на подобных
>>объёмах просто не нужен.
>>Конечно хорошо знать олап,
>>но в данном случае это слишком несоразмерно.
так всё таки «инструмент оперативного учета»
или «на таких объемах». Почему Вы уверенны что «на таких объемах» только оперативный учет?
> или «на таких объемах». Почему Вы уверенны что «на таких объемах» только оперативный учет?
Требования по оперативности учёта и оперативности получения отчётов (сюрприз!) — взаимосвязанные. :0)
А олап — вынужденная мера на огромных объёмах. Да и то олап уместен только с кучей оговорок, например проверять просрочки платежей на олапе — жесть.
А точность до секунд, при анализе информации за несколько лет, совершенно не важна.
Да я и не спорю что олап может дать хорошие возможности для анализа прошлого. Однако реальность такова, что реальный учёт просто не возможен на только олапе. В реальности нужно чтобы вот человек изменил данные, и тут же нажал кнопку и в течении трёх секунд отчёт показал обновлённые данные.
> А точность до секунд, при анализе информации за несколько лет, совершенно не важна.
Если бы всё было так просто… :0)
Для «пары» отчетов действительно нужно, чтобы «человек изменил данные, и тут же нажал кнопку и в течении трёх секунд отчёт показал обновлённые данные».
Такие отчеты можно замечательно сделать на 1С.
Но, уж поверьте :) есть и другие задачи.
Посмотрите ролик — сколько бы отчетов вам пришлось сделать в 1С для того чтобы дать пользователю информацию в таком же виде?
Правда очень интересное занятие «ваять» такие отчеты? Может быть всё таки проще дать пользователю возможность самому получать информацию?
Мы говорим про учёт и анализ. Все обсуждаемые «задачи» лежат в этих рамках.
> Для «пары» отчетов действительно нужно, чтобы «человек изменил данные, и тут же нажал кнопку и в течении трёх секунд отчёт показал обновлённые данные».
>
Но, уж поверьте :) есть и другие задачи.
>
> Посмотрите ролик — сколько бы отчетов вам пришлось сделать в 1С для того чтобы дать пользователю информацию в таком же виде?
Посмотрел я ролик. Всё что я увидел в ролике можно получить из типовой «Торговли и склад» без программирования. Что тут сказать ты не только не знаешь 1с, но и почему-то считаешь что твоя поделка заменяет 20 отчётов 1с. :0)
> Правда очень интересное занятие «ваять» такие отчеты? Может быть всё таки проще дать пользователю возможность самому получать информацию?
Кто мешает пользователю самому получать информацию из 1с? :0)
>> программирования.
В 1с 7.7 без программирования, агрегированные данные за несколько лет, в разных разрезах, за пару секунд – мне кажется Вы либо лукавите, либо мы живем в параллельных вселенных ;D
Возможно, Вы не обратили внимание, но у нас есть sql версия 1C. На схеме это не отраженно, но в эту базу имеют доступ руководители отделов и маркетологи (как раз для оперативной отчетности).
Но почему то они предпочитают пользоваться «поделкой». Дикие люди ;)
Конечно, если учетная система позволяет получить информацию, которая нужна пользователям совершенно бессмысленно делать какую-то надстройку. Но если, всё таки, есть люди заинтересованные в более гибком способе работы с информацией — такое решение наиболее естественно.
ЗЫ: Каждый уверен, что именно он источник огня. И это тема для новой войны ;)
Ничо я не лукавлю, на ваших объёмах — легко. :0)
> Возможно, Вы не обратили внимание, но у нас есть sql версия 1C. На схеме это не отраженно, но в эту базу имеют доступ руководители отделов и маркетологи (как раз для оперативной отчетности).
> Но почему то они предпочитают пользоваться «поделкой». Дикие люди ;)
Тут дело не в руководстве, а в тебе, ты не понимаешь как пользоваться 1с.
> Конечно, если учетная система позволяет получить информацию, которая нужна пользователям совершенно бессмысленно делать какую-то надстройку. Но если, всё таки, есть люди заинтересованные в более гибком способе работы с информацией — такое решение наиболее естественно.
Учётная система то позволяет, твоя квалификация не позволяет. :0)
«Картина мира» не изменится от того что произошло ещё несколько отгрузок
Вкусы потребителей меняются порой очень быстро.
Очень сильно разгрузило сервер основной сервер (особенно по утрам, когда раньше запускали аналитические отчеты) и многие сотрудники говорят, что работать стало удобнее. Плюс автоматизировали некоторые специфические расчеты, например бонусов :)
OLAP беспорно полезен, если его правильно и к месту применять
Возможно потому, что пользователи знают предметную область.
т.е. пользователь хочет что то получить, а что для этого нужно сделать как правило интуитивно понятно.
Например если человек хочет посмотреть продажи конкретного товара за определенный период в конкретном городе, интуитивно понятно что нужно вытащить города и товар фильтры,
дату — в столбцы, сумму — в показатели.
На конференциях видела несколько «готовых» решений на базе 1с + Analysis Services.
По сути специфики, как правило, немного. Разработав решение для одной оптовой компании, можно его же внедрять в другой компании.
Для маленькой такой компании
Для маленькой такой компании
Огромный такой OLAP!
Тра-ля-ля-ля-ля-ля ля-ля-ля-ля яяяя
Под грустное мычание
Под бодрое рычание
Под дружеское ржание рождается на свет
Дата-майнинг в кубах.
ролик в Camtasia Studio 6
сделанная ещё в 2000 году с первыми версиями AS и Excel 2000
данные забираются из 1С, RS баланса, RS фин. Кубы перерассчитываются за последний день-два каждую ночь. И иногда по запросу за пол года, если произошла операция задним числом.
Для удобства пользователей в Excel добавлен плагин, который позволяет загрузить уже готовые отчёты в виде темплейтов документа и показывает только «разрешённые» кубы и позволяет хранить отчёты централизованно и позволяет дорабатывать их. И ещё пара вкусностей, напомню в Excel 2000 работы с кубами была не очень удобной.
Радует, что не мы одни думаем в сторону дешёвых и эффективных решений.
This file is neither allocated to a Premium Account, or a Collector's Account, and can therefore only be downloaded 10 times.
This limit is reached.
Дайте, пожааааааалуйста :)