Коллеги!

Задался я тут вопросом о том как с помощью разных средств OLAP строить диаграмму Парето.
Ежели кто не знает, что это такое, поясню на примере. Нужно развёрнуто ответить на вопрос "Какая доля топовых клиентов даёт какую долю выручки?".
В моём случае интересно посмотреть на это с точки зрения сумм кассовых чеков.
Соответственно, задача формулируется так: построить график, в котором по оси Х будут укрупнённые группы кассовых чеков, а по Y две величины - доля группы чеков в сумме по всем кассовым чекам и средняя сумма чека.
Группы чеков определяем путём разбиения всего множества чеков, отсортированного по убыванию, на N (например 10) интервалов, включающих переменное количество чеков так, чтобы каждая группа добавляла по 1/N к доле итоговой суммы по всем чекам. Таким образом, чеки, входящие в первую группу войдут и во все остальные.
В двух словах, по оси Х имеем такие деления - TOP 10%, TOP 20%, TOP 30%... TOP100% чеков, по Y - две кривые, одна возрастает - это доля группы в общей сумме, а другая убывает - это средняя сумма чека.

Вот такая задача.

Кто как будет решать?

2Jurii: не спешите с рекламированием свойства 20/80 в Cognos PowerPlay. Это задачка, как мне кажется, немного посложнее будет.

С уважением,
Константин Лисянский
http://lissianski.narod.ru

2Birkhoff:

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

Да, алгоритм вполне прозрачный. Без сомнения. Именно нарастающий итог.

Практически уверен что это можно запихнуть в один SQL запрос, с использованием аналитических функций.

А нарастающий итог как эскуэлём считать собираетесь? Или в Оракле есть такая функция? А если MS SQL?
Ну, и, наконец, вопрос-то стоял о том, как это сделать в OLAP-продукте.


С уважением,
Константин Лисянский
http://lissianski.narod.ru

2Birkhoff:

Да, в Oracle есть конструкция типа
SUM(Amount_sold) OVER(PARTITION BY YEAR ORDER BY Product , Amount_sold SUM DESC ROWS UNBOUNDED PRECEDING )
которая считает нарастющий итог.

Я очень рад за Oracle. Мне с MS такой функции не хватает.

Если у нас ROLAP, то запихнув во view такой SQL можно его использовать.

Смотря какой ROLAP. Я вот в своём ROLAPе этот отчёт мышкованием строю без всяческих вьюшек. Чем очень довлолен :)

Если MOLAP, то подобные задачи удавалось решать с помощью функционала Oracle Express Objects, но возможно это решается и более простым способом.

Возможно, кто-то из форума напишет, как.

В третьих, обнаружил среди списка новых возможностей Oracle 10g функцию с именем Pareto что-то. Что она делает пока выяснить пока не удалось.

Опять-таки, радует. Пора смотреть в сторону Oracle :)

Как это сделать в MS SQL не знаю.

Очевидно, писать ХП, которая будет считать нарастающий итог.


2 backfire & Quark:
Data Mining тут пока не пахнет, это вы погорячились :)

Удачи!

С уважением,
Константин Лисянский
http://lissianski.narod.ru

Юрий,

а сколькими записями Вы манипулировали?
Можете запостить сюда SQL, который сгенерировал Impromptu?
И ещё, Вы предлагаете использовать два продукта для решения этой задачи. А одним нельзя что ли?

Примерчик я делал на основе DBF-файла, для чистоты эксперимента, чтобы не было вопросов, что в одной СУБД какие-то функции есть, а каких-то нет

Это не очень чистый эксперимент. Я только один раз видел сумасшедшего, который аналитику на больших объёмах данных делал на DBF-файлах. У него расчёты часами длились.
Наверняка, у Вас под рукой есть какая-нибудь база данных побольше. Попробуйте повторить свой эксперимент. Интересно понять, насколько это будет работать в реальной жизни.

А как Вы думаете, сколько времени данная процедура займёт у Вас на 3-4 миллионах чеков? Вы ведь собираетесь среднее на клиенте считать. Это значит, вы всю базу чеков по сети будете лить на клиентскую станцию.
Каковы будут требования к клиентской станции?



С уважением,
Константин Лисянский
http://lissianski.narod.ru

2Jurii:

А вообще то я предлагаю Вам потестировать мой подход на Вашей базе данных

Предлагаете мне воспользоваться Impromptu?


С уважением,
Константин Лисянский
http://lissianski.narod.ru

2 Jurii:

Давайте посмотрим на SQL повнимательнее.
Мои комментарии прямо в тексте.

select
c1 as c1,
c2 as c2,
RSUM(c2) as c3,
c4 as c4,
c5 as c5,
c6 as c6,
c7 as c7
from
(
select
c1 as c1, -- идентификатор чека, непонятно, для чего выбирается, в конечном результате не нужно
c2 as c2, -- сумма чека, непонятно, для чего выбирается, в конечном результате не нужно
c4 as c4, -- итог по всем чекам, непонятно, для чего выбирается, в конечном результате не нужно
c5 as c5, -- доля суммы чека, непонятно, для чего выбирается, в конечном результате не нужно
c6 as c6, -- очевидно, ТОП-группа
XAVG(c2 for c6) as c7 -- средняя сумма чека для ТОП-группы, вычисляется на клиенте, аналог AVG(c2) GROUP BY c6
from
(
select
c1 as c1, -- идентификатор чека
c2 as c2, -- сумма чека
c4 as c4, -- итог по всем чекам
((RSUM(c2)) / c4) as c5, -- доля суммы чека
((round(((RSUM(c2)) / c4),1,0)) * 100) as c6 -- очевидно, округление до ближайшей десятки,
from
(
select
c10 as c1, -- идентификатор чека
c9 as c2, -- сумма чека
XSUM(c9) as c4 -- наверное, итог по всем чекам, считается на клиенте вместо того, чтобы считать в СУБД
from
(
select
T1."SUM" as c9,
T1."CHECK_ID" as c10
from
"cheks2" T1
) D2
order by
c2 desc, -- сортировка суммы чеков в порядке убывания
c1 asc -- сортировка идентификаторов чеков по алфавиту. Непонятно зачем. Лишняя работа
) D5
) D1
order by
c2 desc,
c1 asc
) D4


Основные выводы - всё делается на клиенте. Сомневаюсь в том, что на реальных объёмах данных это будет работать в принципе.
Impromptu делает много лишних манипуляций с данными, что должно сказываться на скорости также.

Основной вопрос остаётся открытым - где же та скорость, которую продукты Cognos предоставляют при решении сложных аналитических задач?
Поскольку, Юрий, Вы считаете эту задачу несложной, что же будет со скоростью в более сложных случаях?

Следующий вопрос - а если я захочу сделать drill down чтобы посмотреть на те чеки, которые входят в группу TOP 10% прямо в этом отчёте, как я смогу сделать это с помощью Impromptu?


Люди! Как в MS AS такую задачу решают?

С уважением,
Константин Лисянский
http://lissianski.narod.ru

Я предлагаю Вам для начала только посмотреть, как эксперт по Impromptu и по другим продуктам Cognos настроит Impromptu на Вашу базу данных. Если конечный результат Вам понравится - Вы закупите нужные лицензии, пройдете курс обучения и сами станете экспертом в области Cognos. Напомню, что задачи подобные вашим многократно решались с помощью Impromptu и PowerPlay, и количество записей в базах доходило до 100 миллионов.

Спасибо за предложение :)) Я не хочу становиться экспертом по Cognos. Мне хватает того, что я Cognos Certified Professional.

Разумеется в этом примере все делается на клиенте, поскольку СУБД - файл-серверная. Некоторое время назад я приводил пример SQL-запроса, сгенерированного Impromptu, и у этого запроса было 2 варианта, в зависимости от выбора опции, выполнять ли запрос на сервере или на клиенте.

То есть, Вы утверждаете, что, в случае, например, MS SQL все вычисления будут производиться на нём?

Поскольку запросы генерятся визуальными средствами, то есть сложные случаи, когда стоит зайти в окошко запроса Impromptu, и либо подредактировать его (оптимизировать), либо просто вставить туда собственный и оптимально написанный SQL-запрос.

Вы имеете в виду, что именно этот конкретный запрос нужно ещё дальше оптимизировать или просто рекламируете свойства Impromptu?


Скорость гарантируется при использовании OLAP-сервера PowerPlay.
То есть, данную задачу невозможно решить с использованием Cognos PowerPlay?

В Impromptu надо указать для поля или группы полей ссылку на смежный отчет (это будет не DrillDown, а DrillThrough).

То есть, мне надо придумать, по каким направлениям пользователь захочет делать drill и наделать на все случаи готовых отчётов?
А где же OLAP?


С уважением,
Константин Лисянский
http://lissianski.narod.ru

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

Самому интересно :)
Вот, диаграмму Парето сваял, чем вполне доволен.

Я не уверен что эту задачу удастся решить на сервере РСУБД на 100% - видимо Impromptu некоторые задачи будет решать "на клиенте".

Я даже скажу какую - нарастающий итог, поскольку эта функция отсутствует в MS SQL. Соответственно, все миллионы чеков будут литься на ту машину, где стоит Impromptu.


Радует однако тот факт, что Impromptu обычно стоит на одном мощном компьютере (зачастую на сервере РСУБД), и выполняет вспомогательную роль, позволяя проектировать, создавать и обновлять OLAP-кубы в PowerPlay, где проблем с производительностью практически нет.

Всяко, не будет он работать со скоростью СУБД, которая понимает, что такое параллелизм, например.
Да, и даже в пределах одного компьютера через ODBC или подобный интерфейс данные тоже не очень быстро выгрузятся в Impromptu.
Словом, у меня очень большие сомнения в масштабируемости этого решения.
30 чеков - не вопрос. С миллионами точно будут проблемы.

Эта задача для меня не типична, и я не исследовал глубоко, можно ли ее решить одним лишь PowerPlay. Я подумаю на эту тему, но видимо все же придется сделать виртуальную вьюшку в Impromptu и на ее основе проектировать куб в PowerPlay.

Проектировать целый куб для решения одной задачи?

Impromptu - это не OLAP-сервер.

Это я и так знаю. Пользовался.

При этом в Impromptu можно делать произвольные отчеты, но это не будут ОЛАПовские отчеты...

Ну, это Вы хватили насчёт произвольных. Вам рассказать про fan trap, раннее связывание и другие грабли, которые лежат в Impromptu на каждом шагу?

В общем, я понял для себя, что эта типичная аналитическая задача (хоть она и так нетипична для Вас) не решается на реальных объёмах данных с помощью продуктов Cognos.


Люди по MS AS! Как вы решаете эту задачу? У вас эе есть функция вычисления нарастающего итога!


С уважением,
Константин Лисянский
http://lissianski.narod.ru

2 Jurii:

Если ProClarity проводит все вычисления на OLAP-сервере (MDX-запросом), то не вижу в этом проблемы.
Если на клиенте - получаем вариант с Impromptu.

С уважением,
Константин Лисянский
http://lissianski.narod.ru