В предыдущей статье мы рассмотрели несколько вариантов написания SQL-запросов по формированию данных для распространенного отчета, включающего кросстаблицу. В данной статье предлагается еще один вариант решения, в основе которого лежит использование технологии SAP Business Objects - Юниверса - промежуточного слоя метаданных, с помощью которого пользователи выполняют запросы к базе данных.
Из теории:
Юниверс - это файл, который содержит следующее:
- Параметры соединения для одного или нескольких компонентов доступа к базе данных.
- Структурами SQL называются объекты, которые отображают действительные структуры SQL в базе данных, например столбцы, таблицы и функции баз данных. Объекты сгруппированы в классы.
- Схема таблиц и объединений используется в базе данных.
- Объекты строятся из структур баз данных, которые включены в схему.
- Схема доступна только для пользователей Designer. Она не доступна для просмотра пользователей Web Intelligence и Desktop Intelligence.
- Пользователи Web Intelligence подключаются к юниверсу для выполнения запросов в базе данных. Они могут анализировать данные и создавать отчеты с помощью объектов, не просматривая и не зная ничего об основных структурах данных в базе данных.
Необходимо добавить, что построение отчета с помощью генератора отчетов CrystalReports на основе юниверса также возможно для тех пользователей, кто является частью системы SAP Business Objects Enterprise.
Итак, юниверс - промежуточный слой, которые представляет данные в разрезе бизнес-логики, а не хранения базы данных. Изначально реляционные СУБД ориентированы именно на хранение и обработку информации, и часто построение отчетов по таким структурам - чрезвычайно сложная задача. Имеет смысл ввести промежуточный уровень, в котором данные представляются в виде более удобном для бизнес-пользователя. В предыдущей статье мы использовали прямое соединение с базой данных, а сейчас рассмотрим юниверс.
Отчет за неделю по сотрудникам.
Напомним постановку задачи. Есть некоторые события, которые фиксируют сотрудники. Факт совершения события записывается таким образом: фиксируется дата совершения, какой сотрудник оформил событие, и что конкретно произошло - то есть, тип события. В нашем примере тип события не играет роли, нам необходимо видеть, сколько фактов совершения события зафиксировал тот или иной сотрудник в какой день недели.
1. События фиксируются только в момент совершения, то есть, некоторые дни могут отсутствовать в таблице, если не происходило событий. В отчет должны войти все дни недели.
2. Сотрудники могут не быть на работе или не фиксировать события. В отчете должен быть отражен весь список сотрудников.
3. В отчет необходимо передать два значения - начало периода и окончание периода. В примере, повторюсь, рассмотрим недельный отчет.
Таблица 1. Форма отчета.
Как и в предыдущем примере, используем тренировочную базу данных TEST, созданную на MS SQL Server 2005. Таблички Action и Employee - часть некоторой базы данных, содержащие необходимые нам данные. Связи между табличками осуществляются с помощью внешнего ключа Employee.EmployeeId - Employee.EmployeeId.
Данные.
Таблица 2. Action. Таблица фактов (событий), произошедших за определенное время.
|
DateAction |
Дата события |
|
TypeActionId |
Идентификатор типа события |
|
EmployeeId |
Идентификатор сотрудника |
|
Description |
Некое описание |
Таблица 3. Employee. Таблица Сотрудников, которые фиксируют факт совершения События
|
EmployeeId |
Идентификатор сотрудника |
|
EmployeeName |
ФИО сотрудника (упростим для примера) |
|
DepartmentId |
Идентификатор отдела |
И также мы будем использовать запрос, который возвращает нам список дат нашего диапазона.
В качестве инструмента используем Universe Designer SAP BusinessObjects 12.3.0.601.
Часть 1. Построение Юниверса.
1. Настраиваем соединение для нашей БД (Рис 1).
2. Создаем новый Юниверс, используя построенное соединение (Рис 2).
Оставим стандартные настройки стратегий (вкладка Стратегии).
3. Размещаем таблицы на панели структур Дизайнера Юниверсов и настраиваем связи между ними (Рис 3).
Свойства связи(Рис 4).
Добавляем производную таблицу, чтобы реализовать наш запрос диапазона дат(Рис 5).
4. Настраиваем связи между этими таблицами(Рис 6-7).
Связь по EmployeeId настроена аналогично.
5. Создаем классы и объекты для наших данных
- Сотрудник
o EmployeeId (Измерение)
o EmployeeName (Атрибут)
- Рабочая неделя
o DT (Измерение)
- Значения
o Количество операций (фактов) (Значение)
Используем Функцию Счетчик как пример, она может быть выбрана, например, Нет - то есть, не применять агрегирование для этого значения.
6. Переименуем объекты в терминах, понятных пользователям(Рис 11).
7. Сохраняем и экспортируем юниверс(Рис 12-13).
8. Наш юниверс построен и размещен (экспортирован) на сервере. Все необходимые нам поля "превращены" в объекты, понятные пользователю.
Часть 2. Построение отчета на основе юниверса.
1. Настройка источника данных.
При выборе юниверса в качестве источника данных необходимо выполнить вход в систему Enterprise, введя логин и пароль пользователя. Выбираем тип источника данных - Юниверс(Рис 14).
Выполняем вход в систему Enterprise(Рис 15).
2. Настраиваем запрос.
Выбираем наши объекты, перетаскивая их на панель результата. Для сотрудника выбираем и измерение, и атрибут - ФИО сотрудника (Рис 16).
Обратим внимание, что в этом случае пользователь пишет не запрос, а оперирует объектами юниверса, которые называются понятными пользователю терминами бизнес-логики. Нажимаем ОК, и размещаем получившиеся поля в отчете (Рис 17).
3. Просмотр отчета (Рис 18).
Обратим внимание, что не весь список сотрудников представлен в отчете. При составлении кросстаблицы мы получим такую картину (Рис 19).
Нас не устраивает 2 момента:
1. Наличие "пустой" строки
2. Отсутствие всего списка сотрудников.
Из-за чего такое происходит? Если мы посмотрим список строк, возвращаемый запросом (Рис 18), видно, что есть пустые записи (в поле сотрудник содержится null), которые и дают нам пустую строку в кросстаблице. Это те самые сочетания для дат и сотрудников, для которых не нашлось соответствия в таблице фактов.
SQL-запрос выглядит так:
Мы видим, что контексты, по которым строится запрос в юниверсе, не учитывают того, что нам необходимо CROSS JOIN соединение между таблицами сотрудников и таблицей дней недели, более того, в правилах юниверса намерено избегать декартова произведения множеств. То есть, необходимо добавить в юниверс таблицу, учитывающую такой запрос.
Часть 3. Внесение изменений в юниверс.
1. Добавление производной таблицы.
Добавляем производную таблицу на основании запроса (Рис 20).
Называем таблицу термином бизнес-логики. Пользователи юниверса будут видеть именно это название.
2. Устанавливаем связи (Рис 21).
И формируем объекты (Рис 22).
Теперь нам необходимо заменить таблицы в отчете - вместо Employee и Недели поставить Сотрудники по дням недели.
Важно! После изменения юниверса не забудьте повторно развернуть (экспортировать) этот юниверс на сервере.
Часть 4. Изменяем отчет.
1. Меняем запрос (Рис 23).
2. Отчет приобретает такой вид (Рис 24).
По 1 сотруднику уже видно, что перечислены все варианты дат. И только по тем сочетаниям, где в таблице фактов есть фиксирование действий, в столбце Операция (факт) отображается значение.
3. Кросстаблица принимает такой вид (Рис 25).
Запрос для этого отчета выглядит так:
В этом случае контекст, используемый в юниверсе, берет внутреннее соединение неизменным, и только потом подключает таблицу действий.
Итак, мы добились цели, используя источник данных юниверс. Еще раз отметим, что пользователю не требуется писать запрос, нужно только обратиться к юниверсу и использовать объекты, которые есть в нем. Запрос будет построен на основе контекстов, определенных в юниверсе его разработчиками.