Как я укрощал Cognos OLAP, или записки дилетанта

Дмитрий Перепелов

Не боги горшки обжигают

Народная мудрость

Краткое предисловие

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

Тем не менее, каждый день многим людям приходится открывать для себя что-то, к чему раньше они не имели никакого отношения. И этим людям очень не хватает именно "первичных знаний", базиса, точки опоры. Они, разумеется, могут прочитать какую-нибудь книжку из серии "Для чайников", "Осваиваем за 5 минут" или "Сто вопросов про…" - но, как показывает опыт, такие книжки далеко не всегда несут в себе достаточно полезной информации для каждого конкретного случая. (Помнится, на заре моей туманной юности было очень много книг про персональный компьютер, в которых рассказывалось про Нортон Коммандер. Но далеко не в каждой из них объяснялось, как бороться со злосчастной русской буквой "р", а мне тогда это было очень нужно). С другой стороны, можно найти специалиста и спросить все у него - но сами представьте, каково объяснять "n+1" чайнику, как в Ворде показывать непечатаемые символы… А они еще потом удивляются, чего это вдруг мы такие злые… Наконец, есть еще Интернет, а в нем - бесчисленные FAQ-и на все случаи жизни - но там ведь надо запускать поиск… И выуживать информацию по крупицам…

Гораздо приятнее и удобней - сесть и прочесть впечатления такого же чайника, узнать все "кочки", на которых он споткнулся, - а уж потом читать книжки и задавать вопросы занятым людям. Я лично этот путь предпочитаю всем остальным. И для таких вот как я и написал статью, которую вы имеете счастье читать. Соответственно, всех лиц, считающих себя "профессионалами", прошу относиться к моему творчеству не слишком строго (конструктивную критику приму с удовольствием!), спрятать подальше заранее приготовленные гнилые овощи - или просто перейти на другую страничку.

Как все начиналось

В один прекрасный день в нашу компанию обратился наш старый и уважаемый клиент - Очень Важная Контора (далее - ОВК). Сфера деятельности этой самой ОВК - руление финансовыми потоками, т.е.: ей платят, а они распределяют. Плательщиков очень много - несколько сотен тысяч, получателей несколько меньше - тысячи. Специалисты ОВК захотели узнать: а) кто, из каких местностей, и в какие периоды времени платит хорошо, кто - не очень хорошо, а кто - совсем не платит; и б) кто из "потребителей" денег - самый "дорогостоящий", кто обходится дешевле всего, и с какими из n заранее известных факторов это связано. В общем - классическая задача анализа с использованием OLAP продуктов.

У нас к тому времени имелся в арсенале один пакет анализа - COGNOSäOLAP APPLICATIONS, продвижением коего мы потихоньку занимались. Представился случай, во-первых, опробовать этот пакет в деле, и сделать на нем нечто реальное и зримое, во-вторых - попытаться продать его клиенту. Итак, мы взялись построить для клиента гиперкубы на основе их данных и на них продемонстрировать все преимущества OLAP технологий. Дополнительной сложностью стало то, что все работы предполагалось провести на территории ОВК - клиент опасался, что его данные "утекут" наружу.

День первый. Первое знакомство

Начинаем, как обычно, с установки. Поскольку неизвестно еще, какие из продуктов понадобятся (а всего их 4 - Impromptu, PowerPlay , Scenario и 4Thought) - развертывать будем все. Установка проходит на удивление легко. В опциях обнаружил список интерфейсов к источникам данных (Oracle, Sybase, DB2, Informix, Ingres, MS SQL, DBF, Excel, конечно же TXT, а также ODBC), поддержку Hyperion Essbase (дружат, значит с конкурентами), а также демо-примеры и кучу документации. Что приятно, после установки перезагружаться не обязательно.

Как и подобает любому российскому юзеру, считающему себя "продвинутым", читать документацию мне лень. Хотя ее, повторяю, достаточно, причем как в варианте "Users Manual" (техописание), так и в виде "Step by Step" (пошаговое освоение), плюс мультимедиа-тур для самых ленивых. Но нормальные герои, как известно, всегда идут в обход - и поэтому начал я с изучения демо-примера. Есть в поставке файл, который называется "national" - в трех вариантах причем, Excel, CSV и TXT - содержащий готовые "сырые" данные для создания гиперкуба. Вот его-то я и взял. Открываем PowerPlay Transformer (именно этой утилитой создаются кубы из "сырых" данных"), нажимаем "New Model", указываем в качестве типа "Excel database", источника файл national.xls (не забываем при установить область данных в файле в самой нижней строчке, иначе данные не прочтутся). Устанавливаем флажок Run AutoDesign… Через 30 секунд на экране 4 окошка: верхнее, самое большое, с измерениями (Dimensions); Внизу, поменьше, с запросами (Queries), мерами (Measures) и гиперкубами (PowerCubes). По сути дела, перед нами готовая модель с четырьмя измерениями: Дата, Линия продукта, Территория, Рынок, и тремя мерами - Стоимость, Количество, Доход. Теперь в меню Run выбираем "Create PowerCubes" - получаем готовый гиперкуб, который можно тут же посмотреть, вызвав собственно PowerPlay Viewer правой мышкой на гиперкубе. Вся процедура заняла меньше пяти минут, включая восторженное созерцание результатов собственного труда.

Теперь попробуем сделать то же самое, но с нашими данными. Они у нас представлены в виде 10 DBF таблиц. Выбираем в качестве источника Dbase Table… Вот он, первый облом. Оказывается, Transformer "не понимает" формат DBF от Visual Foxpro (а именно под ним работает приложение у клиента). Забегая вперед скажу, что все остальные вышеперечисленные продукты Cognos также не смогли разобраться с этим форматом. Кто уж тут виноват - приснопамятный Microsoft, не предоставивший API, или "ленивый" Cognos, не сделавший конвертер - я не знаю, но таблички пришлось "переливать" в обычный "DBASE IV" формат. Сделать это можно с помощью ODBC драйверов и специальных конвертеров, входящих в комплект Borland Delphi 5.0.

Запускаем генерацию модели по новой. Исходной таблицей выбираем "главную", ту, в которую сбрасываются рабочие транзакции приложения. Вспомогательные таблицы и справочники добавим позже. Снова запускаем AutoDesign… Нда, картина резко отличается от "демо-примера". Ну, колонку с датами, положим, система распознала правильно. Но вот связь между Кодом Плательщика и его Названием - не уловила, создала два разных измерения. Чем-то не понравилось ей поле "код территории" - его она в измерения не записала, зато занесла туда "Списочную численность сотрудников". А в меры попали "ИНН" и "Код по ОКОНХ". В общем, как и ожидалось "мастер-волшебник" в данном случае оказал "медвежью услугу".

Придется делать все самому. Сначала удалим все измерения и меры, кроме нужных. Затем занесем вспомогательные таблицы в лист запросов. Проследим, чтобы связанные поля в разных таблицах имели одинаковые имена - так системе будет понятнее принцип формирования категорий. Если имена в исходных таблицах не совпадают - их можно переименовать уже в запросе. Вообще, лучше переобозначить все поля - очень любят у нас давать колонкам "нечитаемые" названия типа "CLNUM349", поди догадайся, что это "Номер клиента". А в Transformer-e полю можно дать любое, в том числе и русское, название. Удобно!

Занесли все таблицы - начинаем создавать измерения. Здесь принцип простой: группируем все поля в связанные по смыслу иерархии: например, "Страна-Область-Город", или "Вид-подвид-группа-подгруппа". Название измерения лучше давать по общему признаку иерархии - "Территория", "Вид и группа товара". Уровни в измерении размещаются "от общего к частному", т.е. в том же порядке, как они были описаны выше. Возможно описание и параллельных ветвей - например, один и тот же клиент может ранжироваться по типу (мелкий/крупный оптовик) и по месту нахождения. Главное условие здесь - чтобы КАЖДАЯ запись в исходных данных имела однозначное соответствие с каждой из параллельных групп. Другими словами, каждый клиент должен быть только ИЛИ крупным оптовиком ИЛИ мелким, и одновременно у каждого клиента должен быть уникальный код территории. Если у вас в базе есть клиенты без типа - система признает таких "сиротами" (orphans) и выведет в отдельную веточку на графе. При наличии же клиентов, имеющих офисы в разных местах (числящихся в таблице с одним и тем же кодом, но с разными городами/районами) система не сможет построить категории, т.к. нарушится принцип уникальности. В этом случае придется либо создавать новое поле, которое станет уникальным кодом в рамках всей базы (код конкретного офиса клиента), либо "распараллеливать" измерения.

Описание категорий измерений производится следующим образом. Берем поле, на основе которого будет создаваться категория - оно может быть как числовым, так и текстовым. Если есть и то и другое - например, в главной таблице стоит код, сопоставляемый с названием из справочника, - то поле кода заносится в Source Column, а название - в Label Column. Если есть описание (скажем, у клиента стоит поле Примечание) - суем его в Description Column. Краткие названия (скажем, краткое название организации) - в Short Name Column, эти названия появятся дальше на диаграммах. Отдельно надо сказать про Category Code Column. Вообще-то код конкретной категории Transformer создает сам. Однако же, бывают случаи, когда ему это сделать не удается - в частности, когда исходные записи неуникальны (есть несколько записей клиентов с одним и тем же кодом). Вот тут-то на помощь и приходит это поле.

Ну ладно, вроде бы измерения мы создали - теперь попробуем сгенерировать категории. Run -> Generate Categories. Вот это уже лучше. Выбрав правой кнопкой мышки на измерении Show Diagram, видим, что получилось некое подобие "дерева". Вершиной его является Название измерения, далее идут уровни категорий. Самый нижний уровень - уникальный признак (код клиента, территории и пр.).

Только вот не для всех измерений создание категорий прошло успешно. При обработке некоторых выдается сообщение о нарушении принципа уникальности. Это значит, что система не смогла разобраться в исходных данных. Если категорий должно получиться немного - можно посоздавать их и руками. Для этого на диаграмме надо встать на название уровня (мы прописывали его при описании измерения) и нажать правую кнопку, выбрать Insert Item. Тут, кстати, открывается масса возможностей - ведь можно не просто описать категорию, задав ее код и название, но и сделать "вычисляемую" категорию. Особенно это удобно при работе с датами. Например, надо создать категорию "разница между состоянием текущего и предыдущего квартала. Описываем категорию, делаем Change to Calculated Category, заносим формулу (Текущий квартал - Предыдущий квартал) - и все! Теперь, если в дальнейшем эта категория будет выбрана, PowerPlay покажет приращение выбранной меры!

Однако, мы отвлеклись от нашей задачи. После описания измерений и категорий необходимо сформировать меры. Тут все просто: заходим в окошко Measures, добавляем новый пункт. За основу, опять же можно взять либо существующее поле либо вычисляемое значение (скажем, стоимость вычислить как произведение цены на количество). Предусмотрены развитые возможности вычисления итогов: суммарно, средне, максимальное/минимальное значение…

После того, как все создано, запускаем генерацию гиперкуба. Это можно сделать либо снова через Create PowerCubes, либо последовательно добавив элемент в окно PowerCubes и выбором Create Selected PowerCube правой кнопкой. Дополнительно можно указать, какие меры и измерения следует использовать при создании гиперкуба, "привязать" дополнительные разрезы, задать параметры обработки данных и оптимизации.

После успешной генерации куба смотрим его - запускаем View Power Cube. Взору открывается интересная картина: измерения все сформированы правильно, меры тоже, но вот с цифрами явно какой-то непорядок. В частности, числа в строках одного столбца почему-то все одинаковы, и более того - совпадают с "Итого". Хм, здесь явно что-то не так. Надо переделывать модель…

День второй. Первые разочарования.

Ну и напортачили же мы вчера… Заходим снова в Transformer, встаем на Запросы, щелкаем правой кнопкой - выбираем Show Scope. Что мы видим? Некоторые измерения высвечиваются темно-желтым цветом: это значит, что данная категория соотносится с записью из запроса непосредственно и однозначно (скажем, записи запроса содержат код клиента - категория "Клиент" связана с запросом непосредственно). Если цвет светло-желтый - категория соотносится с запросом через нижележащие уровни. Это, в общем, тоже не так плохо. Гораздо хуже, если колонка с измерением окрашена в белый цвет - это значит, с данным запросом связи у нее нет. Если мы прописали связь "поле CodCln - Категория Клиент", а система такой связи не установила - значит, скорее всего, запись наша была неуникальной, надо делать другую связь. Есть еще случай, когда цвет красный - это означает, что связь установлена по отсутствующему столбцу в запросе. Обычно такое бывает после того, как из запроса удалили колонку, а в описании измерений исправить забыли.

Аналогичная ситуация и с мерами. Темно-желтый и белый цвета имеют те же значения, вместо светло-желтого - зеленый цвет, и он указывает на связь через верхние (а не нижние, как у запросов) уровни. Проблемы обычно у "белых" колонок - эти меры в формировании модели попросту не участвуют.

Кстати говоря, ту же самую информацию, только словами, можно получить, если запустить Check Model из меню Tools.

После продолжительного анализа ситуации, сопровождавшегося яростным чесанием в затылке и глубокомысленным почмокиванием, выяснилось, что корень проблемы - в структуре запроса. Хорошо авторам Cogmos-а - в их демо-примерах все "разложено по полочкам". Вот, к примеру, гиперкуб Outdoors (см. в подкаталоге Cubes and Reports каталога PP6.0 Samples). Есть главный файл - транзакции клиента: Код клиента, код продукта, дата, количество, цена. Есть вспомогательные файлы - справочники стран, городов, линий продуктов и пр. Здесь все просто, система генерит все категории "без запинки". Иное дело - наши данные. В одной таблице содержатся записи по платежам от каждого контрагента помесячно; в ней же помещены плановые цифры платежей на этот месяц. Разница между двумя типами записей - поле "TIP". Поле "Тип Контрагента" указывает на дополнительный справочник… являющийся "общим справочником системы". Одна из колонок его задает "вид справочной информации" (тип контрагента, тип валюты, код платежа, тип отношений), другая - собственно код ( в нашем случае, типа контрагента), третья - название. Коды у разных "разделов, разумеется, пересекаются. Естественно, приложение "знает", к какому именно разделу обращаться - но наш-то гиперкуб об этом не ведает!

В общем, похоже, в лоб проблему не решить. Данные, предоставленные клиентом, требуют дополнительного "причесывания" перед тем, как они попадут в Transformer. Будем думать дальше…

День второй. Снова надежда!

Что-то мы увлеклись изучением одного продукта - PowerPlay. Он, конечно же, мощный, но… он один из ЧЕТЫРЕХ компонент А что же все остальные.

Вот, к примеру, Impromptu. В аннотации к нему сказано, что это " инструмент для формирования бизнес-запросов и быстрого построения различных отчетов, обеспечивающих руководителей всех уровней оперативной информацией для принятия решений. Основой Impromptu является каталог деловых знаний и правил доступа к данным. Используя Impromptu, руководители получают доступ ко всем необходимым данным, описывающим модели бизнеса, особенности и тенденции его развития". Таким образом, получается, Impromptu может выдавать материал для последующей закачки в PowerPlay - тем более, и в Transformer'е предусмотрен такой источник данных А ну-ка попробуем, нельзя ли что-то сделать с нашими данными ?

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

Во-первых, перенесем все таблицы в одну директорию (если до сих пор не сделали этого). Создадим новую Базу данных (Catalog-Databases), указав директорию с таблицами в качестве каталога БД. Не забудем открыть "Convert OEM to ANSI" для DBF-ок, созданных в "досовских" версиях FoxPro, Clipper-а и иже с ними - иначе русские буквы будут выглядеть ну очень странно J. Это, кстати, можно сделать и "опосля" - только придется выходить из отчета и заходить снова, иначе изменения не подействуют.

Создадим новый каталог (Catalog-New)- он, по сути, и явится для Impromptu эквивалентом БД. Выберем таблицы, которые попадут в каталог (можно выбрать все). Для всех таблиц укажем ключевые поля (последнее необязательно, но желательно, т.к. ускорит работу). Кроме того, таблицы можно отранжировать по весу - что это даст я, правда, так и не понял.

Прорисуем связи между таблицами - это можно делать как "drag-n-drop"-ом, так и попросту написав SQL-выражение. Последний способ хорош для "сложных связей (типа описанных мною в предыдущей главе, с общим справочником). Созданные JOIN-ы можно проверить - на этом этапе легко вычисляются "петли" и висящие таблицы. Можно также посмотреть общий текст всего JOIN-а БД -помимо всего прочего, это повод слегка погордиться своими успехами J

Следующий важный этап - описание Папок (Folders). Собственно, у нас они уже есть - и соответствуют названиям таблиц. Можно работать и с такими… но лучше немножко "прибраться". Создадим новый каталог "DB Tables" и перенесем в него все таблицы. Второй каталог назовем "Dimensions". В него мы поместим все поля нашего будущего Большого Запроса для Transformer'а. Вот где можно развернуться! Хочешь создать вычисляемое поле – пожалуйста. С суммированием (усреднением, подсчетом количества) - нет проблем! Можно даже прописывать "условные" поля (скажем, если Сумма в пределах от 100 до 1000, то значение поля 1, если от 1000 до 2000 - то 2 и т.д.) Таким образом, сразу создается "ранжирующее поле", по которому можно в дальнейшем генерить категорию. Ну и конечно же, все это хозяйство можно отсортировать по подпапкам, подподпапкам,.. и т.д до бесконечности. Очень симпатично выходит!

Теперь перейдем к созданию собственно запроса. Сначала нужно создать отчет (New Report). Для нас сейчас непринципиально, как он будет выглядеть на экране, нам нужны только колонки. Поэтому то, как задавать группировку, сортировку, ширину колонок и шрифт я не рассказываю. В простейшем случае (как, собственно, я и поступил) просто переносим все вновь созданные поля из Dimensions в запрос (Query) и на этом удовлетворяемся. А вообще, здесь можно продолжить созданием различных "сложных полей", с тем же инструментарием, что и на этапе создания Папок.

Сохраняем отчет в виде Определения Запроса Imprompru (Impromptu Query Definitions, IQD). Теперь этот файл можно использовать в качестве источника данных для трансформера - причем, как отдельно, так и наравне со "старыми" DBF-ками.

"Заливаем" запрос в Transformer. Появилось новое окошечко, в котором отражается информация об используемой нами базе данных. Генерим категории, смотрим Show Scope. "Темно-желтых" полей значительно прибавилось, а анализ модели не дает больше такое количество ошибок и предупреждений.

Итак, использование Impromptu дало результаты. Полученный PowerCube уже гораздо ближе "к реальности", чем предыдущий - по крайней мере, цифры теперь разные, и "Итого", на первый взгляд, соответствует сумме столбца/строки.

Внимательные читатели заметили, что мы использовали один запрос. Действительно, для простоты работы все данные были запихнуты в Одну Большую Таблицу, по которой и генерились категории. Это позволило избежать неоднозначностей, однако существенно замедлило работу. К тому же, не всегда такое "объединение" технически возможно...

День четвертый. Итоги

Ну что же, пора "подбивать бабки". Модель мы построили, и Заказчику она, в целом, приглянулась. Он, конечно же, высказал ряд "ценных замечаний" - мол, тут бы надо еще отранжировать, а вот здесь добавить условие; и обязательно поменять цвет стрелок! Но в общем задача решена. Куда двигаться дальше ?

Во-первых, с построениям гиперкуба работа аналитика не заканчивается, а начинается. В самом PowerPlay-е столько различных "фичей" - от разного вида диаграмм, до наложения граничных условий и даже добавления новых категорий - что пытливый юзер без труда себя займет.

Во-вторых, данные мы в гиперкуб вкачали - но они ведь имеют поганое свойство меняться А каждый раз повторять процедуру вкачивания, сколь бы она проста ни была, занятие неблагодарное. Проще задать расписание обновления, - пусть этим занимается сам Cognos Благо есть еще один компонент - Cognos Scenario, по сути - обычный "Шедулер", типа майкрософтовского.

В-третьих, не одними гиперкубами жив аналитик, иногда ведь нужно и обычный отчет построить. Для этого опять же хорош Impromptu (собственно, как явствовало из предыдущих речей, он для того в первую очередь и предназначен). А коли надо на этот отчет навесить менюшки, выбор, задание исходных условий, и прочие программистские штучки - так пожалте вам Cognos Script Editor. Язык объектно-ориентированный, слегка поход на Jav'у, больше на Delphi, есть что-то и от VB. С его помощью очень даже запросто и веб-приложение сбацать (как, например, вот это), и …да много чего можно J

А в-четверых, есть еще 4Thought…

Но это уже совсем другая история…

Если кто захочет пообщаться на тему всего вышепрочтенного - милости прошу. Мой адрес: D.Perepelov@hostco.ru

P.S. Еще раз подчеркну, данные записки - личное мнение одного пользователя, и оно не всегда и не во всем совпадает с мнением создателей и продавцов указанного продукта. Помимо всего прочего, "пользователь" этот находится в стадии освоения продукта (коия, как представляется, может быть ооочень продолжительной), и поэтому может еще не подозревать о глубинных тонкостях продукта. Так что если хотите меня в чем-то поправить - то поправьте, а вот гнилыми овощами прошу в меня не кидать J