Завдання 6. Розробка аналітичних звітів

ЗМІСТ

1. Мета завдання
2. Процес виконання завдання

🌐 Цей документ доступний українською та англійською мовами. Використовуйте перемикач у правому верхньому куті, щоб змінити версію.

1. Мета завдання

Мета завдання — навчити розробляти звіти у середовищі Redash.

В рамках цього завдання моделювальники мають:

змоделювати аналітичне представлення;
надати доступ до аналітичного представлення;
створити 3 запити (Query) в Redash;
створити дашборд в Redash;
вивантажити архів із дашбордом;
розпакувати архів у регламенті;
перенести зміни до віддаленого Gerrit-репозиторію;
перевірити сформований звіт.

Система формування аналітичної звітності Redash має доступ лише до репліки бази даних, і лише до аналітичних представлень. Для створення таких представлень використовуйте тег <ext:createAnalyticsView>, подібний до тегу для створення критеріїв пошуку (Search Conditions).

Детальну інформацію щодо створення аналітичних представлень та критеріїв пошуку на рівні моделі даних можна переглянути на сторінці за посиланнями:

Для аналітичних представлень створіть окремий файл createAnalyticsViews.xml із шаблону createAnalyticsViews.xml

Назва аналітичного представлення має починатися префіксом report_.

2.1.1. Створення аналітичного представлення "Розгорнута інформація по лабораторіях"

Назва аналітичного представлення: report_laboratory.
Інформація з таблиць: laboratory, koatuu (область, населений пункт), ownership.

Приклад. ХМL-шаблон створення аналітичного представлення

    <changeSet author="registry owner" id="create report_laboratory view">
        <ext:createAnalyticsView name="report_laboratory">
            <ext:table name="laboratory" alias="l">
                <ext:column name="laboratory_id"/>
                <ext:column name="name"/>
                <ext:column name="address"/>
                <ext:column name="edrpou"/>
                <ext:column name="koatuu_id"/>
                <ext:column name="ownership_id"/>
            </ext:table>
            <ext:table name="koatuu" alias="k">
                <ext:column name="name" alias="town"/>
                <ext:column name="level1" alias="obl_code"/>
            </ext:table>
            <ext:table name="koatuu" alias="ko">
                <ext:column name="koatuu_id" alias="region_id"/>
                <ext:column name="name" alias="region"/>
            </ext:table>
            <ext:table name="ownership" alias="o">
                <ext:column name="name" alias="ownership"/>
            </ext:table>
            <ext:join type="inner">
                <ext:left alias="l">
                    <ext:column name="koatuu_id"/>
                </ext:left>
                <ext:right alias="k">
                    <ext:column name="koatuu_id"/>
                </ext:right>
            </ext:join>
            <ext:join type="left">
                <ext:left alias="ko">
                    <ext:column name="code"/>
                </ext:left>
                <ext:right alias="k">
                    <ext:column name="level1"/>
                </ext:right>
            </ext:join>
            <ext:join type="inner">
                <ext:left alias="l">
                    <ext:column name="ownership_id"/>
                </ext:left>
                <ext:right alias="o">
                    <ext:column name="ownership_id"/>
                </ext:right>
            </ext:join>
            <ext:where>
                <ext:condition tableAlias="ko" columnName="type" operator="eq" value="'О'"/>
            </ext:where>
        </ext:createAnalyticsView>
    </changeSet>

Вихідний SQL-запит на базі XML-шаблону

SELECT l.laboratory_id,
       l.name,
       l.address,
       l.edrpou,
       l.koatuu_id,
       l.ownership_id,
       k.name   AS town,
       k.level1 AS obl_code,
       ko.koatuu_id AS region_id,
       ko.name  AS region,
       o.name   AS ownership
  FROM laboratory l
         JOIN koatuu k ON l.koatuu_id = k.koatuu_id
         LEFT JOIN koatuu ko ON ko.code = k.level1
         JOIN ownership o ON l.ownership_id = o.ownership_id
 WHERE ko.type = 'О';

2.1.2. Створення аналітичного представлення "Довідник КОАТУУ"

Назва аналітичного представлення: report_koatuu.
Інформація з таблиці: koatuu.

Приклад. ХМL-шаблон створення аналітичного представлення

    <changeSet author="registry owner" id="create report_koatuu view">
        <ext:createAnalyticsView name="report_koatuu">
            <ext:table name="koatuu">
                <ext:column name="koatuu_id"/>
                <ext:column name="code"/>
                <ext:column name="name"/>
                <ext:column name="type"/>
            </ext:table>
        </ext:createAnalyticsView>
    </changeSet>

Вихідний SQL-запит на базі XML-шаблону

SELECT koatuu.koatuu_id,
       koatuu.code,
       koatuu.name,
       koatuu.type
  FROM koatuu;

2.1.3. Створення аналітичного представлення "Довідник типів власності"

Назва аналітичного представлення: report_ownership.
Інформація з таблиці: ownership.

Приклад. ХМL-шаблон створення аналітичного представлення

    <changeSet author="registry owner" id="create report_ownership view">
        <ext:createAnalyticsView name="report_ownership">
            <ext:table name="ownership">
                <ext:column name="ownership_id"/>
                <ext:column name="name"/>
            </ext:table>
        </ext:createAnalyticsView>
    </changeSet>

Вихідний SQL-запит на базі XML-шаблону

SELECT ownership.ownership_id,
       ownership.name
FROM ownership;

2.2. Видача прав доступу до аналітичних представлень

Кожна роль, що вказана у файлі roles/officer.yml Gerrit-репозиторію реєстру, має користувача бази даних на репліці з префіксом analytics_ (наприклад, analytics_officer).

Для правильного функціонування звітів потрібно надати права до створених представлень відповідній ролі. Перевірте файл officer.yml, та додайте роль officer (якщо такої ще немає).

Приклад 1. Приклад вмісту файлу officer.yml із регламентною роллю officer

roles:
  - name: officer
    description: Officer role

Механізм видачі прав на платформі реєстрів версії 1.5. та вище

У файлі createAnalyticsViews.xml, додайте тег <ext:grantAll>, додавши в середину тег <ext:role> з атрибутом name="analytics_officer".

Приклад. ХМL-шаблон для видачі прав

 <changeSet author="registry owner" id="grants to all analytics users">
    <ext:grantAll>
        <ext:role name="analytics_officer"/>
    </ext:grantAll>
</changeSet>

Покладіть створений файл createAnalyticsViews.xml до папки data-model Gerrit-репозиторію.

2.3. Застосування змін до моделі бази даних

Виконайте наступні кроки для застосування змін:

У файлі main-liquibase.xml додайте тег <include> з обов’язковим вказанням атрибуту file="data-model/createAnalyticsViews.xml" у кінці тегу <databaseChangeLog>:
```
<databaseChangeLog...>
    <include file="data-model/createAnalyticsViews.xml"/>
</databaseChangeLog>
```
Застосуйте зміни до Gerrit (git commit, git push).
Проведіть процедуру рецензування коду вашого commit. За відсутності прав, попросіть про це відповідальну особу.
Дочекайтеся виконання Jenkins-пайплайну MASTER-Build-registry-regulations.

2.4. Процес створення звіту в Redash

Розробка аналітичної звітності ведеться на базі admin-екземпляра Redash. Необхідно мати роль redash-admin у реалмі -admin реєстру. Роль призначає адміністратор безпеки в інтерфейсі сервісу Keycloak. Зверніться до сторінки Внесення користувачів до системи за детальною інформацією щодо керування ролями.

Використовуйте екземпляр redash-viewer для перегляду аналітичної звітності, а також дослідження подій у журналах аудиту.

Користувачі Кабінету посадової особи мають роль officer за замовчуванням. Роль надає доступ до особистого кабінету, а також стандартних звітів відповідного реєстру.

Система передбачає додаткову розширену роль auditor. Вона призначена для перегляду журналів аудиту у сервісі Redash.
Використовуйте екземпляр redash-admin для створення та перегляду аналітичної звітності адміністраторами реєстру.

Адміністратор звітності отримує роль redash-admin у реалмі -admin реєстру. Після цього він матиме повний доступ до звітності у сервісі Redash.
Знайти посилання до екземплярів Redash можна в інтерфейсі Control Plane, у розділі Швидкі посилання.

Дізнайтеся більше деталей про формування посилань на сторінці Швидкі посилання до сервісів реєстру.

Альтернативно, відкрийте консоль OpenShift > Проєкт вашого реєстру > Networking > Routes та знайдіть потрібні посилання серед списку доступних.

2.4.1. Створення запита для параметра "Тип Власності"

На цьому кроці треба створити запит для параметра, який дозволить бачити лабораторії лише певного типу власності.

Найперше створіть Snippet (запит за замовчуванням):

Відкрийте Налаштування (Settings).
Оберіть вкладку Фрагменти запита, натисніть Новий запит Snippet та заповніть обов’язкові поля:
- Активатор — значенням select_query_based_dropdown_list
- Фрагмент — sql кодом:
  SQL-запит — шаблон
  WITH cte AS ( SELECT -1 AS rn, uuid_nil() AS value, '( Всі значення )' AS name UNION ALL SELECT 2 AS rn, <OBJ_PK_UUID> AS value, name AS name FROM <OBJ_NAME> ) SELECT value, name FROM cte ORDER BY rn, name;

Далі створіть новий запит:

В інтерфейсі адміністратора Redash (redash-admin) відкрийте секцію Запити та натисніть Новий Запит.
У полі для запита введіть select_, після чого виберіть з випадного списку готовий шаблон запита для створення параметрів у звітах — select_query_based_dropdown_list.

Змініть <OBJ_PK_UUID> та <OBJ_NAME> на → ownership_id та report_ownership_v (назви мають відповідати тим, що були визначені на кроці Створення аналітичного прошарку на рівні бази даних поточного завдання).

report_ownership — назва аналітичного представлення, створеного на кроці Створення аналітичного прошарку на рівні бази даних у файлі createAnalyticsViews.xml. Система автоматично додає постфікс _v при розгортанні. Тому завжди звертайтеся до будь-якого аналітичного представлення у Redash за такою схемою:

назва_аналітичного_представлення + _v.

Приклад 2. Назва, вказана при створенні аналітичного представлення

<ext:createAnalyticsView name="report_ownership">

Приклад 3. Назва представлення, фактично згенерована при розгортанні

task 6 2 2 redash

Натисніть на кнопку Виконати, щоб надіслати запит до БД.

У нижній частині ви побачите таблицю з даними.
Натисніть на назву запита вгорі — Новий Запит та вкажіть для нього нову назву, наприклад Вибір типу власності. Далі натисніть Enter.
Натисніть кнопку Зберегти, щоб зберегти запит.
Натисніть кнопку Опублікувати, щоб опублікувати запит.

2.4.2. Створення запита для параметра "Область"

На цьому кроці необхідно створити запит для параметра, який надасть можливість бачити лабораторії, розташовані у певній області.

Створіть новий запит:

В інтерфейсі адміністратора Redash (redash-admin) відкрийте секцію Запити та натисніть Новий Запит.
У полі для запита введіть select_, та оберіть з випадного списку готовий шаблон запита для створення параметрів у звітах — select_query_based_dropdown_list.
Змініть <OBJ_PK_UUID> та <OBJ_NAME> на → koatuu_id та report_koatuu_v, додавши умову WHERE type = 'О'. Вираз where має обмежити значення лише областями.

Будьте уважні. Буква 'О' тут — українська, не латиниця.
Натисніть кнопку Виконати, щоб надіслати запит до БД.

У нижній частині ви побачите таблицю з даними.
Натисніть назву запита вгорі — Новий Запит та вкажіть нове значення, наприклад Вибір області. Далі натисніть Enter.
Натисніть кнопку Зберегти, щоб зберегти запит.
Натисніть кнопку Опублікувати щоб опублікувати запит.

2.4.3. Створення основного запита для звіту

Основний запит посилається на попередні запити для їх використання як параметри фільтрації.

Створіть новий запит:

В інтерфейсі адміністратора Redash (redash-admin) відкрийте секцію Запити та натисніть Новий Запит.

У полі для запита введіть SQL-скрипт:

SQL-запит - шаблон

SELECT name AS "Назва лабораторії",
       edrpou AS "ЄДРПОУ",
       address AS "Адреса",
       ownership AS "Тип власності",
       town AS "Місто",
       region AS "Область"
  FROM report_laboratory_v
 WHERE region_id = ''

Налаштування параметра фільтрації за Областю

Перемістіть курсор між одинарних лапок та натисніть кнопку створення параметра (Add New Parameter):
Задайте наступні значення у формі:
- Keyword (Ключове слово) — region;
- Title (Заголовок) — Область;
- Type (Тип) — Query Based Dropdown List;
- Query (Запит) — Вибір області.
Натисніть на кнопку Add Parameter (Додати Параметр).

Ви отримаєте вираз вигляду: … WHERE region_id = '{{ region }}'.

Зображення 1. Фінальний вигляд випадного списку для вибору області
Додайте до отриманого виразу, що фільтрує, логічний предикат OR та наступний вираз — '{{ region }}'= uuid_nil(). Він необхідний для врахування та опрацювання умови “( Всі значення )”.
Загорніть вирази зліва та справа від OR у дужки.

Налаштування параметра фільтрації за Власником

З нового рядка додайте до отриманого виразу умову опрацювання фільтрації за власником лабораторії: AND ownership_id = ''.
Перемістіть курсор між одинарних лапок та натисніть кнопку створення параметра (Add New Parameter).
Задайте наступні значення у формі:
- Keyword (Ключове слово) — ownership;
- Title (Заголовок) — Власник;
- Type (Тип) — Query Based Dropdown List;
- Query (Запит) — Вибір типу власності.
Натисніть на кнопку Add Parameter (Додати Параметр). Ви отримаєте вираз вигляду: … AND ownership_id = '{{ ownership }}';

Зображення 2. Фінальний вигляд випадного списку для вибору власника
Додайте до отриманого виразу, що фільтрує, логічний предикат OR та наступний вираз — '{{ ownership }}'= uuid_nil() — необхідний для врахування та опрацювання умови “( Всі значення )”.
Загорніть вирази зліва та справа від предиката OR у дужки.
Вираз WHERE, який ви маєте отримати в результаті
```
WHERE (region_id = '{{ region }}' OR '{{ region }}' = uuid_nil() )
  AND (ownership_id = '{{ ownership }}' OR '{{ ownership }}' = uuid_nil())
```
Натисніть на кнопку Виконати.

У таблиці результатів мають з’явитися створені раніше лабораторії.
Натисніть назву запита вгорі — Новий Запит та вкажіть для нього нове значення, наприклад Перелік лабораторій. Далі натисніть Enter.
Натисніть кнопку Зберегти, щоб зберегти запит.
Натисніть кнопку Опублікувати щоб опублікувати запит.

2.4.4. Створення інформаційної панелі (Дашборду)

Створіть нову інформаційну панель (Dashboard):

В інтерфейсі адміністратора Redash (redash-admin) відкрийте секцію Дашборди та натисніть Нова інформаційна панель.
Вкажіть назву — Лабораторії.
Натисніть кнопку Add Widget (Додати віджет), оберіть запит Перелік лабораторій зі списку та натисніть кнопку Add to Dashboard (Додати до Панелі).
Розтягніть додану панель за шириною та довжиною екрана.
Натисніть кнопку Done Editing (Закінчити Редагування).
Опублікуйте створену панель кнопкою Publish (Опублікувати).

Особливості роботи із назвами дашбордів

Рекомендуємо використовувати латиницю для назв дашбордів у Redash.

Починаючи із версії Redash 10, нові дашборди створюються за шаблоном <порядковий_номер>-<slug_ім’я>. Раніше призначалося лише ім’я дашборда.

Параметр slug — псевдонім, який додається до ідентифікатора дашборда в URL через дефіс. У нашому прикладі "slug": "laboratories" (див. зображення нижче).

dashnoard naming 2 ua

Отримати JSON-представлення дашборда за його ID можна, передавши до Redash API кореневий шлях оточення, на якому розгорнуто redash-admin + /api/dashboards/<resource-id>.
Шаблон посилання до сервісу redash-admin
```
https://admin-tools-<registry-name>-main.<dns-wildcard>/reports
```
- <registry-name> — назва реєстру;
- -main — системна константа;
- <dns-wildcard> — визначає домен та піддомени середовища.
- /reports — ендпоінт доступу до сервісу.
  
  Наприклад: https://admin-tools-platform-demo-main.example.com/reports
  
  Див. детальніше про формування посилань: Швидкі посилання до сервісів реєстру.
  Приклад. Запит на отримання дашборда №8 із реєстру platform-demo, який розгорнуто у середовищі apps.envone.dev.registry.eua.gov.ua
  https://redash-admin-platform-demo.apps.envone.dev.registry.eua.gov.ua/api/dashboards/8
Якщо перейменувати назву дашборда кирилицею, то в URL ви отримаєте ідентифікатор дашборда та прочерк.

Детальніше про Redash API ви можете переглянути за посиланням: https://redash.io/help/user-guide/integrations-and-api/api.

2.4.5. Публікація створених об’єктів користувачам

Опублікуйте створені об’єкти в регламенті:

Увійдіть до Кабінету адміністратора регламентів.
Перейдіть на сторінку Шаблони звітів.
Натисніть ⤓ (іконку завантаження) на записі Лабораторії.

Розпакуйте отриманий архів та покладіть отримані файли до папки reports/officer Gerrit-репозиторію.

task 6 2 reports

task 6 3 reports

Файли із дашбордами повинні мати унікальні назви (dashboard_1.json, dashboard_2.json, dashboard_3.json тощо).

У папці reports/<назва-ролі>/queries/ завжди повинен бути лише один файл із назвою queries.json. Він повинен містити запити, що присутні у файлах queries.json із різних архівів. Тобто ви НЕ підміняєте один файл на інший, а розширюєте наявний файл новими запитами. Це може виглядати, наприклад, ось так:

{
   "count":172,
   "page_size":25,
   "page":1,
   "results":[
      {
         "інформація про запит": 1
      },
      {
         "інформація про запит": 2
      },
      {
         "інформація про запит": 3
      }
   ]
}

Зображення 3. Файл queries.json, що містить запити (queries) із різних архівів

Видаліть .zip-файл із папки reports/officer.

Застосуйте зміни до Gerrit (git commit, git push).
Проведіть процедуру рецензування коду вашого commit. За відсутності прав, попросіть про це відповідальну особу.
Дочекайтеся виконання Jenkins-пайплайну MASTER-Build-registry-regulations.
Перевірте наявність створеної інформаційної панелі на viewer-екземплярі Redash.

Додаткову інформацію щодо доступу до даних та розмежування прав ви можете переглянути на сторінці Керування аналітичними представленнями.