Информация об OLAP примеры реализации, основы

http://habrahabr.ru/blogs/sql/66920/
На хабре еще поищи, в коментах много инфы

Спасибо

Спс)

ну там ведь общий пример.
вот например хочу анализировать продажи.
на SQL сервере создаю таблицу со следующими колонками:

Дата, Контрагент, Торговая точка, Вес, Количество, Количество позиций, Прибыль

что дальше?

А дальше заполняешь её из 1с , только таблицы клиентов , товаров и дат/месяцев/годов и данных надо разносить. Затам рисуешь в Analysis Services кубик, настраиваешь измерения и данные, рассчитываешь кубик. Просматриваешь через Excel - для пользователей или прямо в Analysis Services - пока делаешь кубик. При обновлении данных в таблице - кубик необходимо перерасчитывать.

то есть если я правильно понял:
таблиц должно быть несколько:

ДатаИД, Торговая точкаИД, ТоварИД, Вес, Количество, Количество позиций, Прибыль

к ней присоединятся

Таблица дат:
ДатаИД, Год, Квартал, Месяц, Число, День Недели

Таблица номенклатуры:
НоменклатураИД, Наименование, Группа1, Группа2, ... всякие характеристики номенклатуры

Таблица клиентов:
Торговая точкаИД, Контрагент, Группа1, Группа2, ... остальные характеристики клиента

я правильно понимаю?

Да, правильно. И затем на основании таблиц строится куб, в котором прописанно , какие данные он берет для измерений, какие для фактов. Лучше пользоваться 2008 SQL , там больше возможностей и человечней интерфейс

Основная фича OLAP технологии - это так называемый Drill Down. Тоесть есть сначала укрупненные данные (в разрезе например обороты по всему товару за весь текущий год) потом более детальные в любом разрезе - хотим по группам товаров, хотим по какому-то свойству товаров, или в разрезе кварталов, недель, дней, или одновременно в разрезах и товаров (групп) и периодов. Причем за счет того, что в OLAP кубе все эти данные предрасчитаны, тоесть хранятся изначально в удобном для такого быстрого анализа виде - получается фантастическая скорость формирования всех этих аналитических отчетов, что несказанно радует пользователей.

Для организации структуры под OLAP хранилище можно использовать прямые SQl запросы к базе 1C 8.х

Из такого вот запроса:
Код
ВЫБРАТЬ ПЕРВЫЕ 1
ХозрасчетныйДвиженияССубконто.СубконтоДт1,
ХозрасчетныйДвиженияССубконто.Сумма,
ХозрасчетныйДвиженияССубконто.МоментВремени,
ХозрасчетныйДвиженияССубконто.НомерСтроки
ИЗ
РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто КАК ХозрасчетныйДвиженияССубконто

ХозрасчетныйДвиженияССубконто - виртуальная таблица создаваемая внутренними средствами платформы на основе РегистрБухгалтерии.Хозрасчетный которая содержит таблицу проводок

Например, запрос по регистру Хозрасчетный, чтобы произошло 1:1 отображение такого запроса на MS SQL

Получается вот это:
Код
SELECT
_AccntReg10398_R._LineNo _LineNo,
_AccntRegED10425_TEDDt1._Value_TYPE _ValueDt1_TYPE,
_AccntRegED10425_TEDDt1._Value_RTRef _ValueDt1_RTRef,
_AccntRegED10425_TEDDt1._Value_RRRef _ValueDt1_RRRef,
_AccntReg10398_R._Fld10401 _Fld10401,
_AccntReg10398_R._Period _Period,
_AccntReg10398_R._RecorderTRef _RecorderTRef,
_AccntReg10398_R._RecorderRRef _RecorderRRef
FROM
_AccntReg10398 _AccntReg10398_R WITH(NOLOCK)
LEFT OUTER JOIN _Acc1_ExtDim10345 _Acc1_ExtDim10345_AccTEDDt1 WITH(NOLOCK)
ON _AccntReg10398_R._AccountDtRRef = _Acc1_ExtDim10345_AccTEDDt1._Acc1_IDRRef AND _Acc1_ExtDim10345_AccTEDDt1._LineNo = CAST(1 AS NUMERIC(1,0))
LEFT OUTER JOIN _AccntRegED10425 _AccntRegED10425_TEDDt1 WITH(NOLOCK)
ON _AccntReg10398_R._RecorderTRef = _AccntRegED10425_TEDDt1._RecorderTRef AND _AccntReg10398_R._RecorderRRef = _AccntRegED10425_TEDDt1._RecorderRRef AND _AccntReg10398_R._LineNo = _AccntRegED10425_TEDDt1._LineNo AND _AccntRegED10425_TEDDt1._Period = _AccntReg10398_R._Period AND _AccntRegED10425_TEDDt1._Correspond = CAST(0 AS NUMERIC(1,0)) AND _AccntRegED10425_TEDDt1._KindRRef = _Acc1_ExtDim10345_AccTEDDt1._DimKindRRef
) #V8TmpTable1_Q_000_T_001
SELECT @@TRANCOUNT

Т.е. таблица РегистрБухгалтерии.Хозрасчетный + таблица РегистрБухгалтерии.Субконто. Если добавится еще субконто, добавится и таблица РегистрБухгалтерии.Субконто

а обратно из куба данные получить в СКД через внешние источники, я так понимаю, можно?

olap для маленькой компании
В посте Многомерные кубы, OLAP и MDX Vitko написал: «тема очень интересная и с каждым днем становится все более актуальной». К сожалению, это заклинание произносится уже очень давно (по крайней мере я его слышу с 2004 года ), но olap проектов до сих пор очень мало. Возможно, потому что традиционно считается, что всё, что связанно с olap нужно только для крупных компаний с большими объемами накопленных данных и стоит очень дорого. Но это не совсем так. Я хочу рассказать о проекте, который внедрен в одной относительно небольшой компании.

Проект очень древний, начинался ещё в 2003 году. Про некоторые вещи можно сказать «так исторически сложилось». Но, мне кажется общая идея, может быть полезной.

Итак. Компания занимается оптовой торговлей кондитерскими изделиями. Опт кондитерки достаточно специфичный бизнес. При относительно небольших оборотах приходится иметь дело с большими объемами данных. Клиентами компании являются как крупные торговые сети, так и небольшие магазинчики в деревнях области. Плюс огромный ассортимент продукции. Причем клиент может купить любой объем товара – от одного сникерса до вагона печенья (были прецеденты, когда на склад возврата товара поступало половинка зефиринки (история умалчивает какой было причина возврата) ).

Основная учетная система — 1С «Торговля и склад» 7.0, причем dbf версия. Она достаточно успешно справляется с задачами учета товара. Но получить в ней отчеты за большие периоды времени практически нереально. Подобные попытки создают серьезную нагрузку на сервер, начинаются проблемы у операторов 1с, жалобы в It отдел.

Потребность в таких отчетах была постоянная. Сложилась идеальная ситуация для реализации bi проекта: большой объём информации + люди заинтересованные в её анализе.

Для начала, небольшой ролик, демонстрирующий, как пользователь может сам получать информацию.

Как это реализовано:



Синие стрелки — пути, которыми информация попадает в систему, зеленными – как информация в дальнейшем используется.

1.Информация о заказах заносится в систему 1с – dbf версия.
2.Загрузка данных «автообмен». Вообще – то это лишний шаг. Данные можно получать напрямую из dbf базы. Но программисты 1с решили что стандартный (для 1с ) механизм выгрузки данных, принесет меньше вреда.
3.Раз в сутки изменения за прошедший день выгружаются в специально подготовленную базу MsSql – хранилище. Выгружается не вся информация, а только то, что нужно для кубов.

В принципе необязательно строить «хранилище». Данные для куба можно получать напрямую из базы 1с ( MsSQL или dbf ). Но в моем случае из 1с данные прошлых периодов периодически удаляются и очищаются справочники. Кроме того перед загрузкой в хранилище данные немного «чистятся».


4.Происходит пересчет куба – данные попадают в куб.

Информация из хранилища используется не только кубами, но и внешними приложениями, например эти данные нужны для расчета зарплаты, для учета оплат-поставок, для планирования работы менеджера. В тоже время данные из этих внешних программ также попадают в кубы.

С кубами работают сотрудники в офисе – руководство, менеджеры, маркетинг, бухгалтерия. Так же информация отправляется поставщикам и торговым представителям в разных городах области.


Любой пользователь может получить информацию разными путями:


1.Построить отчет самостоятельно на web-странице или в excel

Сначала использовался только excel, но возникало много проблем с тем, что екселевские файлы «разбредались», нужно было получить одну «точку входа» для выбора информации.
Поэтому был создан локальный сайт, на котором опубликованы страницы с PivotTable. Сотрудник, который хочет получить пару цифр «здесь и сейчас» заходит на этот сайт и строит отчет в нужной ему форме. Если человеку нужно использовать этот отчет в дальнейшем – он может написать заявку, чтобы его отчет опубликовали в SSRS или сам сохраняет его в excel.


2.Посмотреть стандартный отчет, опубликованный в SQL Server Reporting Services ( SSRS )
3.Получить локальный куб – и вне офиса «вращать» данные с помощью excel
4.Подписаться на рассылку и получать стандартные отчеты из SSRS на e-mail
5.Отдел маркетинга кроме того использует программу 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-специалиста

Уменьшение рутинной работы. Большую часть отчетов пользователь получает самостоятельно.

Вот это как раз мне кажется мифом, который хотелось бы развеять: олап позволяет дать пользователю удобный и быстрый инструмент для интерактивного анализа информации за большие периоды времени. Ключевые слова: быстро, интерактивно.

Да я и не спорю что олап может дать хорошие возможности для анализа прошлого. Однако реальность такова, что реальный учёт просто не возможен на только олапе. В реальности нужно чтобы вот человек изменил данные, и тут же нажал кнопку и в течении трёх секунд отчёт показал обновлённые данные.

> А точность до секунд, при анализе информации за несколько лет, совершенно не важна.

Если бы всё было так просто… :0)

olap для маленькой компании
SQL*
В посте Многомерные кубы, OLAP и MDX Vitko написал: «тема очень интересная и с каждым днем становится все более актуальной». К сожалению, это заклинание произносится уже очень давно (по крайней мере я его слышу с 2004 года ), но olap проектов до сих пор очень мало. Возможно, потому что традиционно считается, что всё, что связанно с olap нужно только для крупных компаний с большими объемами накопленных данных и стоит очень дорого. Но это не совсем так. Я хочу рассказать о проекте, который внедрен в одной относительно небольшой компании.

Проект очень древний, начинался ещё в 2003 году. Про некоторые вещи можно сказать «так исторически сложилось». Но, мне кажется общая идея, может быть полезной.

Итак. Компания занимается оптовой торговлей кондитерскими изделиями. Опт кондитерки достаточно специфичный бизнес. При относительно небольших оборотах приходится иметь дело с большими объемами данных. Клиентами компании являются как крупные торговые сети, так и небольшие магазинчики в деревнях области. Плюс огромный ассортимент продукции. Причем клиент может купить любой объем товара – от одного сникерса до вагона печенья (были прецеденты, когда на склад возврата товара поступало половинка зефиринки (история умалчивает какой было причина возврата) ).

Основная учетная система — 1С «Торговля и склад» 7.0, причем dbf версия. Она достаточно успешно справляется с задачами учета товара. Но получить в ней отчеты за большие периоды времени практически нереально. Подобные попытки создают серьезную нагрузку на сервер, начинаются проблемы у операторов 1с, жалобы в It отдел.

Потребность в таких отчетах была постоянная. Сложилась идеальная ситуация для реализации bi проекта: большой объём информации + люди заинтересованные в её анализе.

Для начала, небольшой ролик, демонстрирующий, как пользователь может сам получать информацию.

Интересно-интересно. А как, в таком случае, отслеживаются изменения внесенные в периоды, для которых "кубы" уже рассчитаны, и изменения которых может потянуть пересчет "кубов" за измененным?