Пожалуй, для компании любой отрасли актуальна тема анализа остатков (остатков по счетам ПБУ, 302-П, остатки товаров и готовой продукции на складах, резервные остатки и т.д.), причем важно знать остатки как на конкретную дату, так и какова динамика остатков за произвольный период времени. Даже в разговорной речи мы оперируем термином "в сухом остатке".
Остаток - полуаддитивный показатель, в разрезе обычных измерений (договоры, центры финансового учета, менеджеры и т.д.) величины остатков суммируются, а в разрезе измерений времени (отчетные финансовые даты, календарные даты) значения сворачиваются: остаток на последнюю дату месяца = остаток на конец месяца, остаток на последнюю дату последнего месяца квартала = остаток на конец квартала и так далее вверх по иерархии.
Остатки могут:
- быть посчитаны и храниться в учетной системе;
- рассчитываться на каждую отчетную дату (или на начало каждой недели, или на начало каждого месяца) на этапе ETL и храниться в реляционном хранилище/витрине данных;
- вычисляться в OLAP-кубе на лету по формуле.
С точки зрения BI, если имеет место быть первый вариант и ему можно доверять, то воспринимаем это как данность и пользуемся готовыми данными. Между вторым и третьим вариантом у нас есть выбор:
| Вариант расчета остатков | Плюсы | Минусы |
| 2.Рассчитывать и хранить в DWH / Data Mart | удобно и легко использовать готовые остатки; хранимые готовые остатки используются как входные данные для OLAP, других прикладных систем, так и для Ad-hoc SQL-запросов | усложнение ETL; нагрузка на сервер баз данных, особенно, когда в учетных системах наблюдаются случаи правки операций задним числом, и требуется пересчитать остатки в DWH |
| 3.Вычислять в OLAP | простая и быстрая реализация | снижение производительности OLAP на больших объемах данных, когда вычисляемые остатки используются в других вычисляемых мерах и/или сложный дизайн куба; пользователи OLAP любят дриллиться до гранулярных данных - подневных остатков, но в данном случае будут лишены такой возможности; продвинутые аналитики вряд ли будут рады постоянно вычислять остатки в своих SQL-запросах к DWH / Data Mart, и начнут материализовывать рассчитываемые значения в своих песочницах со всеми вытекающими последствиями |
Остановимся на варианте №2 более подробно. Допустим, в витрине данных сформирована таблица Periodic Snapshot с остатками на каждый день по каждому кредитному договору в течение всего времени жизни отдельного договора. Поскольку в данном бизнес-кейсе имеем дело с небольшим конечным количеством видов счетов (ссудный счет, начисленные проценты и т.д.), то остатки по каждому из них храним в отдельном поле (получается транспонированная таблица, некоторые из полей будут сильно разреженными). Для удобства использования суммы остатков приведены к единой валюте эквивалента, например, национальной валюте.
В другом бизнес-кейсе (например, для задачи план-фактного анализа) в подобной таблице могут храниться остатки только на 1-ое число каждого месяца. Создадим простой куб, для физических мер - остатков назначим тип агрегирования LastChild и спрячем их:
Затем в MDX-скрипте пропишем вычисляемые меры, попутно решая задачу конвертации сумм остатков в эквиваленты других валют:
--------------------------------------------------------------------------------------------------------------------------------------------------- CALCULATE; -- виртуальная мера-константа назначается по умолчанию для того, чтобы при интерактивном использовании куба -- при набрасывании измерений по осям строк/столбцов сводной таблицы НЕ включалась в работу -- физическая мера куба, что особенно полезно для ProClarity Desktop CREATE MEMBER CURRENTCUBE.[MEASURES].VirtualDefaultMember AS 1, VISIBLE = 0 ; ALTER CUBE CURRENTCUBE UPDATE DIMENSION [MEASURES], DEFAULT_MEMBER = [MEASURES].VirtualDefaultMember ; -- для измерения "Отчетные даты" устанавливается выбранной по умолчанию дата, -- равная дате наиболее поздней, на которую есть контракты ALTER CUBE CURRENTCUBE UPDATE DIMENSION [Отчетные даты], DEFAULT_MEMBER ='TAIL( FILTER([Отчетные даты].[Год-квартал-месяц-дата].MEMBERS, [MEASURES].[MEASURES].[Количество_записей]<> NULL), 1 ).ITEM(0)' ; CREATE MEMBER CURRENTCUBE.[MEASURES].[Остаток ОД] AS [MEASURES].[Остаток_ОД] -- в реальном проекте устанавливаем пересчет остатков по курсу на дату в эквивалент выбранной валюты -- [MEASURES].[Остаток_ОД] / [MEASURES].[Курс_валюты] ,FORMAT_STRING = "Currency" ,ASSOCIATED_MEASURE_GROUP = 'Показатели' -- способ сведения мер из различных размерных групп в единую ветку ,DISPLAY_FOLDER = 'Остатки\Балансовые' -- пользователям нравится, когда все разложено по логическим папкам ,VISIBLE = 1; CREATE SET CURRENTCUBE.[Дата актуальности остатков] AS 'TAIL( FILTER([Отчетные даты].[Год-квартал-месяц-дата].MEMBERS, [MEASURES].[MEASURES].[Количество_записей]<> NULL), 1 ).ITEM(0)'; -- "Протягивание" полуаддитивной меры наверх по иерархии измерения "Отчетные даты" для незакрытого месяца: SCOPE ([MEASURES].[Остаток ОД]) ; TAIL( FILTER([Отчетные даты].[Год-квартал-месяц-дата].MEMBERS, [MEASURES].[MEASURES].[Количество_записей]<> NULL), 1 ).ITEM(0).Parent = TAIL( FILTER([Отчетные даты].[Год-квартал-месяц-дата].MEMBERS, [MEASURES].[MEASURES].[Количество_записей]<> NULL), 1 ).ITEM(0) ; TAIL( FILTER([Отчетные даты].[Год-квартал-месяц-дата].MEMBERS, [MEASURES].[MEASURES].[Количество_записей]<> NULL), 1 ).ITEM(0).Parent.Parent = TAIL( FILTER([Отчетные даты].[Год-квартал-месяц-дата].MEMBERS, [MEASURES].[MEASURES].[Количество_записей]<> NULL), 1 ).ITEM(0) ; TAIL( FILTER([Отчетные даты].[Год-квартал-месяц-дата].MEMBERS, [MEASURES].[MEASURES].[Количество_записей]<> NULL), 1 ).ITEM(0).Parent.Parent.Parent = TAIL( FILTER([Отчетные даты].[Год-квартал-месяц-дата].MEMBERS, [MEASURES].[MEASURES].[Количество_записей]<> NULL), 1 ).ITEM(0) ; TAIL( FILTER([Отчетные даты].[Год-квартал-месяц-дата].MEMBERS, [MEASURES].[MEASURES].[Количество_записей]<> NULL), 1 ).ITEM(0).Parent.Parent.Parent.Parent = TAIL( FILTER([Отчетные даты].[Год-квартал-месяц-дата].MEMBERS, [MEASURES].[MEASURES].[Количество_записей]<> NULL), 1 ).ITEM(0) ; FORMAT_STRING(This)= "Currency" ; END SCOPE ; -- впрочем, скрипт можно несколько упростить ---------------------------------------------------------------------------------------------------------------------------------------------------
Существенное замечание: если в таблице фактов нет значений на последнюю дату месяца (месяц не закрылся, события еще не наступили), то при навигации вдоль оси сводной таблицы по иерархии дат с раскрытием только до уровня месяц / квартал будут отсутствовать итоговые строки за последние месяц/квартал. Другими словами, если последняя дата, на которую есть остатки - 22.05.2011, то мы не увидим (как того, возможно, нам бы хотелось) итого за май 2011г., итого за 2 квартал 2011г. Поначалу это кажется странным, но поразмыслив, логика поведения полуадитивного типа агрегирования становится понятной. Если нужно видеть итог по незакрытому периоду, то следует применить SCOPE ... END SCOPE вычисление: определяется самая поздняя дата, на которую есть данные, и значение остатка присваивается родительским элементам.
После партиционирования размерной группы, создания дизайна агрегатов, сборки куба и прогрева кэша в итоге подобные отчеты будут откликаться за считанные секунды.