Незапланированные запросы в Контур Корпорации

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

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

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

Существует несложное решение - дать пользователям возможность выполнения произвольных SQL-запросов. Для этого можно пойти совсем простым путем - дать окно для ввода выражения на языке SQL, или более цивилизованным - предоставить универсальный инструмент генерации запросов.

Указанное решение приемлемо при выполнении следующих условий:

1. Объем базы данных невелик.
2. Структура базы данных проста.
3. Пользователи имеют высокую квалификацию.

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

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

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

Такой путь не годится для Хранилищ данных и аналитических систем. В них часто применяются сложные механизмы выполнения незапланированных запросов.

В этой статье рассказывается о некоторых технологиях незапланированных запросов, примененных в системе Контур Корпорация компании Intersoft Lab.

Некоторые особенности БД системы Контур Корпорация

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

Таким образом, на момент разработки системы не известно точное количество таблиц. В связи с высокой сложностью внутренней структуры БД, а также для обеспечения защиты данных, доступ к таблицам запрещен на уровне СУБД. Разрешен только доступ к хранимым процедурам, которые, предоставляя данные, выполняют оптимизированные запросы, а, изменяя данные, соблюдают необходимые бизнес-правила.

Навигаторы

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

При настройке структуры Хранилища данных для решения управленческих задач конкретной организации или во время эксплуатации системы разработчик приложения или технолог организации создает типы объектов и их реквизиты.

Например, "Физическое лицо" и "Юридическое лицо". Реквизиты могут ссылаться на справочники. Далее создаются группы и иерархии объектов. Например, "Клиенты" и "Финансово-промышленные группы". При загрузке данных объекты помещаются в группы и связываются со своими родителями в иерархиях. Так создаются многомерные схемы типа "Звезда", где справочники становятся измерениями, а фактами могут быть количество объектов, суммы документов, значения показателей и счетов.

После настройки структуры Хранилища создаются "навигаторы" - именованные наборы реквизитов заданных групп информационных объектов.

Для этого в специальном интерфейсе выбирается группа или иерархия, например, "Клиенты". Далее в нужном порядке выбираются реквизиты объектов, например, "Страна", "Регион", "Город" и т. д. Навигатору присваивается название и код, в нашем случае навигатор может быть назван "География клиентов", а его кодом будет "ГК".

 

Рис. 2. Настройка навигатора

В этот момент создается прикладной индекс - кодовая таблица. В специальную таблицу записываются: код навигатора, числовые идентификаторы всех объектов, позиционные коды всех объектов в навигаторе. Позиционный код состоит из кодов реквизитов в справочниках, разделенных точкой. Например, запись об одном из клиентов выглядит так: "ГК | 19999999 | RU.50.01", что означает, что клиент с ИД 19999999 находится в России (RU), в Московской области (50), в городе Клин(01). По полю кода навигатора и строчному полю, в котором хранится позиционный код, строится физический индекс.

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

Рис. 3 Использование навигатора для поиска данных

Этот же механизм применяется для передачи параметров в хранимые процедуры и получения данных из программ. Например, для получения списка документов по заданной группе клиентов из программы нужно написать следующий код:

#Создание объекта класса Список документов

МоиДокументы = СписокДокументов ()

МоиДокументы.ДатаС = "01.01.2002"

МоиДокументы.Тип = "Договор"

МоиДокументы.КодНавигатораСубъектов = "ГК"

МоиДокументы.ЗначениеНавигатораСубъектов = "RU.50.01"

МоиДокументы.Дать()

Теперь объект МоиДокументы содержит в себе список Договоров с клиентам, находящимися в городе Клин. Если МоиДокументы.ЗначениеНавигатораСубъектов = "RU.50" , мы получим список наших клиентов в Московской области.

Фактографические объекты, такие как финансовые показатели или статьи бюджета, можно фильтровать по совокупности навигаторов, построенных по нескольким описательным объектам (измерениям).

Механизм навигаторов, кроме реализации техники поиска и фильтрации данных, позволяет выполнять структурный, или кластерный, анализ. Действительно, навигаторы группируют все объекты по произвольным сочетаниям их реквизитов. При помощи этого механизма очень легко вычислять как количество объектов в заданных группах, так и другие числовые величины, связанные с объектами, такие как остатки на счетах и пр.

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

Генератор view и хранимых процедур

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

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

Рис. 4. Дизайнер аналитических выборок

Используя сгенерированные view или хранимые процедуры, в специальном дизайнере интерфейсов можно построить новые аналитические модули. Для построения гибридного хранилища данных при создании многомерного куба view указывается в качестве источника данных. Подобным образом view используется для создания динамических отчетов в OLAP-клиентах, таких как Контур Стандарт, BusinessObjects или Cognos. При этом описание метаданных в рамках OLAP-клиента не требует знания сложной физической структуры Хранилища данных. Разработчик манипулирует простыми денормализованными таблицами, описывающими выбранную область данных.

Подобная техника используется не только разработчиками систем, основанных на базах данных, но и производителями универсальных инструментов, упрощающих аналитическую работу с базами данных, в том числе транзакционными. Пример такой системы описан в статье "Незапланированные запросы к Хранилищу данных на основе генерации View".

Автор: Владимир Некрасов, технический директор компании Intersoft Lab