Средний

13 мин

498

Финансовые данные в компании обычно уже есть: в 1С, в хранилище, витринах, аналитических кубах. Но в конце отчётного периода аналитики всё равно часто делают одно и то же: выгружают числа в Excel, собирают сводные таблицы, вручную считают отклонения и готовят презентации для руководства. В этой статье покажем, как перевести такой процесс в промышленный контур: создать OLAP-модель, вынести бизнес-логику в семантический слой и получать живые отчёты в Web, Excel и LibreOffice без ручного копипаста. Выгружать данные будем из 1С, но в виду большого количества способов это сделать мы коснёмся этой темы в следующей статье, а здесь сосредоточимся я на возможностях анализа данных.

Статья будет особенно полезна:

• BI-разработчикам;

• архитекторам данных;

• бизнес-аналитикам;

• финансистам;

• руководителям, которым важно быстро получать корректную управленческую отчётность.

Какую задачу решаем

Представим типичную ситуацию: в конце отчётного периода нужно быстро оценить финансовое состояние компании:

• как изменилась структура активов;

• где выросла дебиторская задолженность;

• нет ли признаков снижения ликвидности;

• какие статьи требуют внимания в первую очередь.

Формально данные для такого анализа уже есть. Но на практике значительную часть работы всё равно делают вручную: аналитики выгружают данные в Excel, создают сводные таблицы, сравнивают периоды, считают отклонения и пытаются понять, что из этого важно показать руководству.

Особенно заметна эта проблема, когда отчёт делают по иерархии счетов. Например, показатель «Активы» включает в себя оборотные активы, а внутри них — денежные средства, дебиторская задолженность, запасы и другие статьи. Если нужно посмотреть структуру активов по регионам, сравнить её с прошлым периодом и быстро выделить рискованные зоны, то ручная сборка почти гарантированно превращается в источник потерь времени и ошибок.

Рассмотрим практический пример на основе демо-набора Financeи покажем, как с помощью Platform V Analytics:

• собрать аналитическую модель;

• настроить семантический слой;

• рассчитать отклонения и темпы роста;

• сформировать динамические отчёты;

• и на той же модели подготовить форматированный управленческий отчёт.

В результате получим не просто BI-отчёт, а единый аналитический контур, который позволяет за секунды ответить на вопрос: где именно в структуре активов появляются потенциальные финансовые риски.

Что именно хотели получить на выходе

Если упростить, то задача была такой. Есть финансовые данные по периодам, регионам и счетам. Нужно:

1. Анализировать их по иерархии балансовых статей.

2. Сравнивать периоды и автоматически показывать отклонения.

3. Отдавать результат не только в аналитический интерфейс, но и в привычные инструменты — Excel и LibreOffice.

4. Чтобы форматированный отчёт для руководства обновлялся от модели, а не вручную.

Иными словами, цель была не просто «построить куб», а убрать разрыв между данными, бизнес-смыслом, аналитикой и финальной управленческой отчётностью.

Подготовка аналитической модели

Platform V Analytics — это корпоративная OLAP-платформа, которая закрывает весь аналитический цикл: от хранения и обработки многомерных кубов до семантического слоя с бизнес-логикой и интеграции с BI- и офисными инструментами, такими как Microsoft Excel, Microsoft PowerQuery, Microsoft PowerBI. В состав платформы также входят веб-интерфейс для формирования отчётов и расширение SberOLAP Suite (SBOS) для LibreOffice.

Ссылка на документацию.

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

В качестве демонстрационного набора мы использовали данные, выгруженные из 1С посредством их готового интерфейса, содержащие финансовые показатели компании в разрезе регионов и временных периодов (также предусмотрено прямое подключение к 1С на уровне приложения). На их основе создали куб «Финансы».

Добавление таблиц измерений в источник

В качестве измерений использовали справочники, описывающие ключевые бизнес-сущности:

• время — годы, кварталы, месяцы отчётности;

• валюта — единица измерения валюты;

• регион и филиал — географическая структура компании;

• дивизион — подразделения компании;

• финансовые счета — иерархия статей баланса;

• сценарий — версии пользовательских значений, используемые в том числе для анализа «что, если».

Загрузка этих справочников на физический уровень создаёт контекст для числовых показателей и позволяет в дальнейшем анализировать финансовые данные в любом нужном бизнес-срезе.

Особое значение здесь имеет справочник финансовых счетов. В отличие от простого плоского списка он создан как иерархия Parent-Child. Это позволяет естественно описать вложенность финансовых категорий, например:

• активы;

• оборотные активы;

• денежные средства;

• дебиторская задолженность;

• запасы;

• основные средства.

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

Добавление таблицы фактов в источник

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

Если упростить, то логическая структура данных выглядит так:

Добавление данных в систему автоматизировано и выполняется через графический интерфейс Platform V Analytics. Для этого нужно перейти в меню «Источники данных» → «Добавить источник»:

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

Создание модели «Финансы»

После успешного импортирования таблиц создаётся логическая модель — куб. На этом этапе разрозненные таблицы связываются в единую многомерную структуру, где:

• измерения формируют контекст и оси анализа;

• показатели становятся количественным наполнением модели.

При построении куба в визуальном редакторе (рисунок 3) важно правильно настроить связи между таблицами, определить типы соединений и поведение атрибутов. Например, LEFT JOIN позволяет сохранить в отчёте статьи справочника, даже если по ним временно отсутствуют фактические движения. Для финансовой аналитики это важно: пустая строка и отсутствующая строка — не одно и то же.

После обработки куба система уже может выполнять многомерные аналитические запросы: рассчитывать значения активов по регионам и периодам, анализировать структуру активов по финансовым статьям и поддерживать drill-down по иерархии счетов.

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

Формирование семантического слоя

Сам по себе куб уже умеет отвечать на запросы, но бизнесу нужны не просто суммы по полям. Ему нужны понятные сущности:

• активы;

• выручка;

• валовая прибыль;

• отклонение к прошлому году;

• темп роста;

• готовые правила агрегации.

Эту задачу решает семантический слой. На нём мы описываем бизнес-логику: создаём иерархии, рассчитываем меры, задаём локализацию, настраиваем форматирование. В результате у всех пользователей появляется единая версия показателей, а логика перестаёт жить в Excel-файлах конкретных аналитиков.

В нашем случае мы создали датасет за пять этапов (рисунок 4):

1. Настроили основную информацию. Задали имя датасета и определили тип чувствительности данных.

2. Определили связи. Сформировали состав датасета — на основе одного куба или нескольких сущностей.

3. Сформировали семантику. Создали иерархию, именованные наборы и вычисляемые меры, а также задали правила форматирования.

4. Перевели (локализовали). Технические имена преобразовали в понятные бизнес-названия.

5. Использовали измерения. Проверили связи между измерениями и фактами, особенно если модель содержит несколько таблиц фактов.

Для нашего сценария создали датасет FactFinance. На первом шаге ему назначили техническое имя и задали категорию чувствительности данных К3:

На этапе «Определения связей» сформировали структуру данных, лежащую в основе датасета. В нашем случае модель создаётся на основе одного куба, поэтому в рабочую область выводится только одна сущность FactFinance:

На этапе «Определения семантики» происходит основная трансформация технической модели в бизнес-ориентированную. Здесь создают иерархии, расчётные меры и форматы отображения:

Иерархии: календарь и счета

Для временного анализа настроили плоскую иерархиЮ «Календарь». Она имеет три уровня детализации:

• год — верхний уровень агрегации;

• квартал — промежуточный срез;

• месяц — уровень детальных значений.

Техническая основа для этого заложена в таблице «Календарь» (рисунок 8), где каждая запись содержит соответствующие атрибуты времени. Затем в интерфейсе системы эти поля связываются в логическую иерархию (рисунок 9).

Для финансовых статей использовали иерархию Parent-Child. В ней каждая строка содержит свой ключ и ссылку на родительский элемент. Так система собирает финансовое дерево и понимает, какие статьи в какие разделы входят. Это позволяет описывать сложные финансовые структуры с произвольной вложенностью:

Для корректной работы такой модели в окне настройки нужно определить два параметра:

• ключ элемента — уникальный идентификатор финансовой статьи;

• родительский ключ — поле, указывающее на вышестоящий элемент.

Настройка этих связей позволяет системе собрать отдельные строки в единое дерево баланса:

Уровень агрегации и унарные операторы

Отдельно настроили уровень агрегации «Счета». По сути, это механизм, который определяет, как именно считаются итоги по иерархии. Технологически он близок по смыслу к функции SCOPE в MSSAS и позволяет управлять логикой вычислений в конкретных срезах.

Особенно важна поддержка унарных операторов, задаваемых прямо в справочнике (рисунок 12):

• + — значение прибавляется к итогу;

• - — значение вычитается;

• ~ — значение не участвует в расчёте родительского узла.

Для финансовой модели это критично. Не все статьи должны просто суммироваться «в лоб»: где‑то нужно исключить двойной счёт, где‑то — корректно учесть регулирующие значения, где‑то — не включать статью в агрегат. Именно здесь проявляется одно из ключевых преимуществ промышленной модели перед ручной аналитикой в Excel: логика расчёта живёт в модели и работает одинаково для всех.

Расчётные меры

После настройки иерархий добавили вычисляемые показатели, необходимые для анализа динамики.

ФинСчета

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

Отклонение

Для анализа динамики добавили меру абсолютного отклонения. Она рассчитывается как разница между показателями текущего и предыдущего периода:

Отклонение = Значение(текущий год) – Значение(предыдущий год)

Эта мера показывает, на сколько в деньгах изменилась статья за период:

Отклонение Δ%

Вторая мера — относительное отклонение:

(Текущий год − Предыдущий год) / Предыдущий год

Она нужна для сравнивания между собой статей разного масштаба. Абсолютный прирост в миллионах не всегда отражает значимость изменения, а доля позволяет видеть динамику на единой шкале:

Условное форматирование

Чтобы отчётом можно было пользоваться без дополнительной расшифровки, для меры «Отклонение Δ%» настроили условное форматирование по принципу «светофора»:

• красный — более 25%;

• желтый — от 10% до 25%;

• зеленый — менее 10%.

Так аналитик или менеджер сразу видит, какие строки требуют внимания, и не тратит время на ручной просмотр всех показателей:

Бизнес-показатели для форматированных отчётов

Для подготовки форматированных и управленческих отчётов через функции КУБЗНАЧЕНИЕ и PALO.DATAC в семантическом слое сформировали набор ключевых вычисляемых мер:

• Выручка;

• Себестоимость;

• ФОТ;

• Численность персонала;

• Основные средства;

• Валовая прибыль.

Технически все они вычисляются по одной схеме: в MDX-выражении меняется только ссылка на уникальный ключ соответствующего узла в иерархии «Счета». Это упрощает поддержку модели и делает расчёты единообразными.

Пример создания меры «Выручка» (рисунок 17):

Aggregate(
{[DIMACCOUNT].[AGG_H-Hierarchy].&[4100]},
[Measures].[byAccount_AGG]
)
 
mdx

Здесь:

• 4100 — технический ID статьи «Выручка»;

• Aggregate собирает значение по узлу и всем его потомкам;

• [Measures].[byAccount_AGG] — агрегирующая мера, которая уже умеет корректно считать дерево счетов с учётом унарных операторов.

Локализация

На этапе «Перевода» мы адаптировали модель для конечных пользователей. Технические имена полей и мер заменяются на понятные бизнес-названия. Например, системное имя FactFinance_GrossMargin превращается в «Валовая прибыль».

Это позволяет аналитикам, финансистам и менеджерам работать в едином понятийном поле без необходимости разбираться в технических именах модели.

Использование измерений

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

После сохранения датасета техническая модель куба превращается в полноценный аналитический инструмент. На этом этапе данные перестают быть просто набором чисел и становятся основой для управленческой отчётности.

Построение динамических отчётов в трёх пользовательских поверхностях

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

• веб-интерфейс — для быстрого анализа;

• LibreOffice — для сценариев в open-source окружении;

• Excel — для привычной табличной аналитики и интеграции с управленческой отчётностью.

Веб-интерфейс

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

Для создания отчёта в веб-интерфейсе нужно перейти в раздел «Выполнить запрос», выбрать датасет «Балансовый отчёт» и перенести нужные атрибуты в области «Столбцы», «Строки» и «Значения»:

LibreOffice

Частью Platform V Analitycs является расширение Sber Olap Office Suite (SBOS), оно используется для построения отчётности в LibreOffice. После настройки подключения табличный редактор получает доступ к семантическому слою и может работать с теми же иерархиями и мерами, что и веб-интерфейс.

Таким образом, LibreOffice выступает не просто редактором таблиц, а полноценным клиентом к аналитической модели. Это особенно полезно там, где важно поддерживать open-source стек и при этом сохранять единые бизнес-правила.

Excel

Платформа Platform V Analytics реализует интеграцию на таком уровне, что при подключении из Excel, Power Query и Power BI воспринимают её как Microsoft Analysis Services. Как следствие, для подключения из Excel отдельные плагины не нужны: достаточно стандартного подключения к Analysis Services. Это важно не только для сводных таблиц, но и для интеграции с Power Query и Power BI. Семантический слой становится единым источником правды сразу для нескольких пользовательских инструментов.

Что видно в итоговом отчёте

Когда отчёт сформирован, система автоматически подсвечивает отклонения по настроенным правилам. На основе такого отчёта уже можно делать предметные выводы. Например:

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

• Увеличились денежные средства — позитивный сигнал с точки зрения ликвидности.

• Выросли запасы — возможный индикатор затоваривания или неэффективного отвлечения оборотного капитала.

• Относительные отклонения вышли в жёлтую зону — значит, изменение уже нельзя считать фоновым и его нужно объяснять.

Таким образом, отчёт перестает быть просто набором чисел и начинает работать как инструмент диагностики.

Сохранение ракурсов

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

Это избавляет от необходимости пересылать тяжёлые файлы и объяснять, какие фильтры нужно применить, чтобы увидеть тот же результат. Получатель открывает ссылку и сразу попадает в нужный срез данных.

Формирование форматированных отчётов

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

• фиксированными строками и колонками;

• заданным порядком показателей;

• специальным форматированием;

• выносом отдельных значений в презентацию для руководства.

Для этого в Excel и LibreOffice используются функции прямого обращения к модели:

• в Excel — КУБЗНАЧЕНИЕ (CUBEVALUE);

• в LibreOffice — PALO.DATAC.

Excel: КУБЗНАЧЕНИЕ и динамический ССП-отчёт

На основе этой схемы мы собрали форматированный отчёт ССП, который затем встроили в PowerPoint. Ключевая идея в том, что формула КУБЗНАЧЕНИЕ ссылается не на жёстко зашитый год, а на ячейку-параметр, например B4. Эта ячейка становится глобальным фильтром.

То есть вместо того чтобы каждый год пересобирать отчёт вручную, аналитик просто меняет год в одной ячейке — и все показатели обновляются автоматически (рисунок 24).

Пример формулы:

=КУБЗНАЧЕНИЕ(
"http___10.128.5.44_7080_mdxk_mdx_xmla_OLAPFactFinance";
"[dimdate].[FISCCAL].["&$B$4&"].[1]";
"[Measures].[NetSales]"
)

LibreOffice: DATAC

Для LibreOffice работает аналогичный сценарий через функцию PALO.DATAC. Пример:

=PALO.DATAC(
"OLAP/FactFinance";
"FactFinance";
"[dimdate].[FISCCAL].["&$B$4&"].[1]";
"[Measures].[NetSales]"
) 

Так можно встроить в LibreOffice Impress связанный объект из Calc, который берёт данные напрямую из аналитической модели:

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

Заключение

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

Для этого потребовалось:

• собрать физическую многомерную модель;

• описать иерархию счетов;

• настроить корректную агрегацию;

• вынести бизнес-логику в семантический слой;

• добавить расчётные меры и правила визуального контроля;

• подключить к модели привычные пользовательские инструменты;

• и на той же основе собрать форматированную отчётность.

В результате получилась единая аналитическая модель, которая:

• обеспечивает согласованность данных на всех уровнях;

• убирает дублирование расчетов в Excel;

• ускоряет подготовку управленческой отчётности;

• снижает влияние человеческого фактора;

• позволяет быстрее находить финансовые отклонения и риски.

Если смотреть шире, ценность здесь не только в OLAP как технологии. Главная ценность в том, что логика отчёта перестаёт жить в отдельных файлах и переносится в централизованную модель. А значит, одни и те же показатели одинаково считаются в веб-интерфейсе, LibreOffice, Excel и даже в презентации для руководства.

Именно в этот момент финансовые данные действительно превращаются в управленческие решения.