Блеск и нищета сводных таблиц
Часть 10
Немного финансовой терминологии
Настройка финансовых счетов в MS Analysis
Подготовка аналитического куба к использованию измерения Account
Создание измерения типа Account
Определение правил форматирования ячеек
В предыдущих статьях цикла мы довольно подробно рассмотрели основные операторы семейства КУБ(), посредством которых можно управлять выводом данных из многомерного хранилища в клиентское приложение.
В этой статье нам хотелось бы сделать небольшое отступление от основной темы повествования и обсудить вопрос разработки приложений, использующих измерения со встроенной бизнес-логикой. Как показывает практика, большинство OLAP-приложений базируются на применении обычных Regular-измерений и аддитивных мер. Подобные решения обладают рядом безусловных преимуществ. В частности, с их помощью можно оперативно создавать модели для всестороннего анализа многомерных данных. Более того, как мы теперь знаем, в распоряжении бизнес-пользователей есть целый набор инструментов, позволяющих настраивать представления этих данных в среде Microsoft Excel.
Теперь давайте посмотрим на аналитические приложения с позиции пользователя-практика. Без преувеличения можно утверждать, что одно из главных направлений их использования — различные задачи финансового планирования и анализа. Эта сфера деятельности отличается дуализмом. С одной стороны, в OLAP-кубах можно определить предметную область произвольной структуры. Для этого пользователю предоставлена возможность создавать собственные измерения, ориентируясь исключительно на свои потребности. Можно даже составить целый куб, содержащий одни пользовательские измерения, причем подобное встречается повсеместно. С другой стороны, конечная цель любого планирования состоит в формировании нескольких отчетных форм, называемых бюджетами. Структура бюджетов жестко регламентирована нормативными документами. Понятно, что сама по себе задача разработки отчетов с определенной структурой не является проблемой. Действительно, в качестве источника данных для измерения можно взять любую таблицу, в том числе составленную и заполненную по заранее оговоренным правилам. Трудность заключается совсем в другом. Типовой бюджет компании — это совокупность трех бюджетов: «Прибыли и убытки», «Движение денежных средств» (БДДС) и «Баланс». Каждый из них имеет самостоятельную ценность и заполняется по определенным правилам. Если не вдаваться в методические тонкости и технические подробности, то можно считать, что каждый последующий бюджет формируется на базе предыдущих: статьи «Бюджета денежных средств» рассчитываются на основании значений определенных статей из бюджета «Прибыли и убытки», а статьи «Баланса» — на основании статей бюджетов «Прибыли и убытки» и БДДС.
К примеру, значение статьи «Прибыль» бюджета «Баланс» определяется на основании статьи «Чистая прибыль» бюджета «Прибыли и убытки». Но сам бюджет «Баланс», в свою очередь, состоит из двух основных разделов — активов Asset и пассивов Liability & Equity, которые в любой момент должны быть равны друг другу. Поэтому увеличение одной из статей пассивов для сохранения нулевого сальдо должно сопровождаться либо уменьшением другой статьи пассива, либо увеличением какойлибо из статей активов.
Иными словами, статьи различных бюджетов связаны между собой правилами пересчета, которые довольно сложны. Одна базовая статья может влиять на несколько итоговых, и наоборот — значение итоговой статьи может зависеть от нескольких исходных.
В принципе, для организации автоматического пересчета значений статей из различных бюджетов достаточно объединить их в одно большое измерение, а затем преобразовать в измерение типа Parent-Child. Для элементов такого измерения возможно задание произвольных MDX-выражений для агрегирования значений ячеек — дополнительных формул пересчета (Customer member formulas).
Однако при попытке объединения статей из разных бюджетов в единый план счетов мы столкнемся с новой проблемой. Дело в том, что данные бюджеты имеют разную природу. В бюджете «Прибыли и убытки» учитываются обороты по статьям, а в «Балансе» — сальдо (итоговые значения за период).
Набор статей из бюджета «Прибыли и убытки» является обычным Regular-измерением. Смотреть балансовые статьи в отдельности от других бюджетов тоже не составляет труда, достаточно изменить правила агрегации для меры — назначить в качестве функции агрегирования вместо SUM() оператор LastChild. При такой настройке мера станет полуаддитивной — для всех измерений, кроме измерения Time, будет и дальше использоваться функция SUM(), а для измерения Time агрегированным значением станет последний потомок текущего элемента на атрибуте гранулярности.
Но единый план счетов, содержащий элементы как из бюджета «Прибыли и убытки», так и из бюджета «Баланс», приводит на первый взгляд к тупиковой ситуации. В кубе должно присутствовать измерение, у которого для одной части элементов используется стандартная функция агрегации, а для оставшейся части — функция LastChild.
Понятно, что стандартными средствами MS Analysis такую функциональность реализовать не получится. И здесь на помощь приходят измерения со встроенной бизнес-логикой, которые часто называют предопределенными измерениями (Predefined dimensions). В арсенале MS Analysis есть специальный тип измерения — Account. Это измерение предназначено для хранения плана счетов компании, и в нем изначально настроены все операции, которые были описаны выше.
Создатели аналитического сервера прекрасно понимают значение и большую прикладную ценность измерения с типом Account. Достаточно отметить, что это единственный тип измерения, конфигурирование которого осуществляется в специальном меню на уровне аналитической базы данных. К сожалению, на практике измерение данного типа несправедливо обойдено вниманием как разработчиков, так и пользователей. Возможно, это объясняется его двойственной природой. По идее измерение типа Account является инструментом экономистов, но они, как правило, принимают весьма опосредованное участие в проектировании аналитических кубов и часто не располагают полной информацией о возможностях, заложенных в систему. А для ИТ-специалистов, мало знакомых с финансовой спецификой, также неочевидны выгоды от использования этого измерения, которое к тому же требует весьма кропотливой настройки.
В реальности измерение типа Account является той опорой, если угодно — «скелетом», на котором держится всё серьезное финансовое планирование. Практика показывает, что техническим специалистам проще понять базовые постулаты финансового учета, чем работникам экономических специальностей всю совокупность технологий, на которых построен современный OLAP. Поэтому описание работы измерения Account видится уместным именно на страницах журнала компьютерной тематики, хотя и требует начальной экономической подготовки. Если читатели не сочли предыдущие абзацы скучными, то и последующий рассказ будет для них интересным и полезным.
Описание бюджетной модели
Всё дальнейшее изложение, как обычно, построено на обсуждении примера из практики. Бюджеты, которые рассматриваются в статье, намеренно упрощены, но в целом соответствуют жизненным реалиям.
Предположим, что планирование в компании ведется по двум направлениям (рис. 1):
Рис. 1. Структура бюджетов
- текущая операционная деятельность оценивается посредством бюджета «Прибыли и убытки»;
- активы и пассивы — при помощи бюджета «Прогнозный баланс».
Доходную часть бюджета «Прибыли и убытки» составляет валовая выручка, которая на рис. 1 представлена статьей «Выручка». Из нее последовательно вычитаются различные затраты (для удобства восприятия на рисунке они выделены красным цветом). Сначала выручка уменьшается на величину совокупных затрат (статья «Затраты» на рис. 1), а затем — налогов (статья «Налоги» на рис. 1). Итоговым результатом бюджета «Прибыли и убытки» является статья «Чистая прибыль».
Для простоты будем считать, что все средства, составляющие прибыль компании, представлены денежными средствами на расчетном счете. При таком допущении на базе бюджета «Прибыли и убытки» можно легко сформировать новый бюджет — «Прогнозный баланс».
Как уже говорилось, баланс компании состоит из двух разделов — активов и пассивов. В нашем случае каждый из них содержит единственную статью. Актив баланса формируется за счет денежных средств, а пассив — за счет прибыли. Оба показателя берутся без изменений из одной и той же статьи бюджета «Чистая прибыль», поэтому они равны между собой, что обеспечивает сбалансированность бюджета.
Разберем теперь, как ведут себя бюджеты с течением времени. Если во втором месяце квартала компания тоже получила прибыль в 32 единицы, то в конце февраля на счетах компании скопится 64 единицы денежных средств, при этом суммарная прибыль за два месяца также составит 64 единицы. А если и март был таким же успешным, как первые два месяца квартала, то концу отчетного периода на счете будет уже 96 единиц денежных средств, причем прибыль составит те же 96 единиц (рис. 2).
Рис. 2. Расчет квартальных показателей
Чему равны результаты квартала? Со статьями бюджета «Прибыли и убытки» всё просто. Выручка за квартал равна суммарной выручке по месяцам этого квартала, а чистая прибыль — сумме месячных показателей чистой прибыли. Балансовые показатели определяются по-другому. Если баланс компании на конец месяца считается по последнему дню месяца, то баланс на конец квартала — по последнему дню квартала. Но последний день квартала совпадает с последним днем третьего месяца, входящего в квартал. Другими словами, баланс I квартала совпадает с балансом марта месяца.
Если рассуждать о балансовых показателях в терминологии многомерных пространств, то получается, что в двухуровневой иерархии «Квартал-Месяц» значение элемента для уровня гранулярности «Квартал» определяется по последнему потомку подчиненного уровня «Месяц». Такая агрегация реализуется посредством функции LastChild. Подчеркнем, что вдоль всех других измерений функция агрегирования остается прежней. Если, к примеру, компания структурно состоит из нескольких подразделений, то ее суммарный баланс на конец квартала определяется посредством постатейного суммирования значений балансовых показателей подразделений. Вдоль других измерений статьи бюджетов «Прибыли и убытки» и «Прогнозный баланс» ведут себя одинаково.
В дополнение отметим, что при формировании балансовых статей только в первом месяце квартала можно напрямую взять месячный показатель чистой прибыли из бюджета «Прибыли и убытки». Активы февраля и марта рассчитываются так называемым накопительным итогом, то есть путем суммирования всех значений из периодов, предшествующих отчетному. Так, для февраля валюта баланса должна рассчитываться путем суммирования показателей чистой прибыли из бюджета «Прибыли и убытки» за январь и февраль, а для марта — уже за январь, февраль и март. Здесь важно разделять два расчета. Алгоритм, согласно которому определяются значения показателей на нижнем, листовом уровне (Leaf level) иерархии (месячные балансовые показатели), и функция, агрегирующая значения в иерархиях (квартальные балансовые показатели), представляют собой независимые расчеты. С практической точки зрения это означает, что каждый из них нужно настраивать отдельно.
Немного финансовой терминологии
Сервер Microsoft Analysis Services предназначен для западных систем финансового учета. Поэтому при настройке измерения Account используются англоязычные термины. В Microsoft Analysis Services применяются счета четырех базовых типов:
- Income — доходные счета бюджета «Прибыли и убытки»;
- Expense — расходные счета бюджета «Прибыли и убытки»;
- Asset — счета, формирующие актив баланса;
- Liability — счета, формирующие пассив баланса.
В балансах, составляемых в соответствии с требованиями Международных стандартов финансовой отчетности (МСФО), пассив делится на два раздела: капитал (Equity) и обязательства (Liability). Поэтому для пассивных счетов баланса часто используется аббревиатура LEQ (Liability EQuity).
Кроме того, в сервере Microsoft Analysis Services присутствуют вспомогательные счета:
- Flow — для обозначения статьи, которая в плане счетов является узловой для бюджета «Прибыли и убытки»;
- Balance — для обозначения статьи, которая в плане счетов является узловой для бюджета «Баланс»;
- Statistical — для выделения различных вспомогательных счетов.
Составляем единый план счетов
Разработка измерения типа Account начинается с составления единого плана счетов. Логика составления бюджетов, показанная на рис. 1, формально изложена в табл. 1.
Табл. 1 является основой для измерения Parent-Child. В ней присутствуют сразу два узловых элемента — «Прибыли и убытки» и «Баланс». Указанные элементы не имеют родителей — у них значение атрибута «Родительская статья: Код» имеет значение Null. Вся дальнейшая структура подчиненности определяется исходя из последовательности проведения расчетов в бюджетах. Сначала ищется показатель, который является завершающим звеном в цепочке вычислений. В нашем случае это «Чистая прибыль». Он непосредственно связывается со статьей «Прибыли и убытки» — код его родительского элемента равен «1». Затем определяются статьи, от которых зависит показатель «Чистой прибыли». Согласно рис. 1, такими статьями являются операционная прибыль и сумма начисленных налогов. Поэтому для этих статей код родительского элемента будет «6». На следующем шаге ищутся статьи, которые влияют на показатели «Операционная прибыль» и «Налоги», и им присваиваются соответствующие коды родительских элементов. Операция повторяется до тех пор, пока не будут охвачены все статьи, составляющие бюджет «Прибыли и убытки». Аналогичным образом следует поступить при классификации статей баланса. Затем для каждой статьи нужно задать ее унарный оператор. Напомним, что этот оператор определяет, каким образом статья должна учитываться при расчете родительского элемента. Поэтому для узловых статей унарным оператором должен быть «~», для счетов, увеличивающих родительские статьи, — «+», а уменьшающих — «–». Одним словом, первый этап подготовки измерения Account полностью соответствует разработке типового измерения Parent-Child.
Следующим шагом будет операция, присущая только измерениям типа Account. В отдельной колонке таблицы для каждой статьи следует указать соответствующий ей тип финансового счета. Проставим для всех «плюсовых» счетов (унарный оператор — «+») бюджета «Прибыли и убытки» в качестве типа счета код Revenue, а для всех «минусовых» счетов — код Expenditures. Для счета «Прибыли и убытки» установим код Flow. Для счетов бюджета «Баланс» принцип классификации будет несколько другим. Все активные счета обозначим кодом Аssets, а все пассивные — Liabilities. В данном случае уже неважно, какой оператор свертки установлен для выбранной статьи. Как видно из табл. 1, статьи «Пассивы» и «Прибыль» имеют различные унарные операторы, но при этом относятся к одной категории — Liabilities. Наконец, для счета «Баланс» проставим код Balance.
Настройка финансовых счетов в MS Analysis
Читатели наверняка обратили внимание на то, что коды, которые мы использовали для классификации счетов в табл. 1, отличаются от «канонического» написания финансовых терминов. Это действительно так. В принципе, для кодирования финансовых счетов в измерении Account допускается применять любые обозначения. Но все они в конечном счете должны быть сопоставлены c базовыми типами, жестко «зашитыми» в Microsoft Analysis Services.
Данная операция выполняется в разделе общих настроек аналитической базы. Для того чтобы вызвать соответствующую форму, нужно в редакторе проектов Visual Studio в окне Solution Explorer выбрать пиктограмму базы данных, правой клавишей мыши вызвать контекстное меню, а затем выбрать в нем пункт Edit Database.
В итоге должна появиться форма, представленная на рис. 3. В ней нас интересует раздел Account Type Mapping, содержащий таблицу из трех колонок:
Рис. 3. Настройка типов счетов в Analysis Server
- Name;
- Alias;
- Aggregation Function.
В строках таблицы задаются характеристики отдельных финансовых счетов. В колонке Name перечисляются названия типов счетов, заданных «по умолчанию», в колонке Alias — псевдонимы, под которыми они будут работать в пользовательских кубах. Если мы, например, хотим, чтобы доходные счета в измерении Account назывались термином «Выручка» (Revenue), то его нужно задать в качестве псевдонима для типа счета Income. При необходимости аналогичным образом можно переопределить и все остальные базовые счета.
Наконец, в столбце Aggregation Function определяется нужный тип агрегационной функции. Как видно из рисунка, для всех счетов, относящихся к балансу, в качестве такой функции выставлен оператор LastNonEmpty.
Подготовка аналитического куба к использованию измерения Account
Для полноценного использования измерения Account в составе аналитического приложения потребуется выполнить ряд подготовительных работ.
Как правило, бюджет компании составляется в два этапа. Сначала во вспомогательных операционных бюджетах планируются отдельные направления хозяйственной деятельности компании. В таких бюджетах используются управленческие аналитики, которые не входят непосредственно в план счетов. Затем на базе имеющихся функциональных бюджетов при помощи определенных правил трансформации формируются основные выходные бюджетные формы. Такой подход обеспечивает представление информации в понятном для менеджеров компании виде и при этом снижает общую трудоемкость вычислений.
В нашем случае планирование доходной части бюджета ведется с использованием измерения Dim_Service, в котором перечисляются услуги, оказываемые клиентам. Для того чтобы на базе бюджета, детализированного по типам услуг, составить выходные бюджеты «Прибыли и убытки» и «Баланс», необходимо каждую из них соотнести с одним из финансовых счетов.
Поскольку все услуги формируют общую выручку компании, они должны быть сопоставлены с одним и тем же счетом из табл. 1 — «Выручкой», который имеет код 2. Для задания такой связи состав атрибутов исходного измерения Dim_Service придется расширить еще одним — Account_N, а затем проставить в нем для каждой из статей сервисов значение «2» (табл. 2).
Все остальные статьи выходных бюджетов будем последовательно рассчитывать на базе статьи «Выручка». Допустим, что расходы компании определяются по формуле 1.
Формула 1
Расходы компании = (Доля_прямых_расходов_в_выручке + Доля_накладных_расходов_в_выручке) * Выручка.
Для обеспечения гибкости будущих расчетов необходимо, чтобы параметры, используемые в формулах, можно было менять с течением времени. Проще всего добиться этого, если хранить их в отдельном справочнике, к которому пользователь имеет доступ. Добавим в хранилище данных новую таблицу, назовем ее Account_Indicators (табл. 3). Она содержит всего три атрибута: код параметра, его наименование и собственно численное значение. В принципе, этого минимального набора достаточно для описания любой переменной.
Для вызова переменной из таблицы Account_Indicators саму таблицу следует добавить в источник данных многомерной модели, а затем создать на ее базе дополнительную меру аналитического куба — Indicator Value, а также еще одно, новое измерение — Indicators.
Мера Indicator Value организуется на основе численного атрибута Ind_Value из табл. 3, а измерение Indicators — на базе столбца Indicator_Key. После выполнения перечисленных манипуляций схема данных должна принять вид, похожий на схему, представленную на рис. 4.
Рис. 4. Добавление новой таблицы фактов для хранения параметров
Отличительной особенностью данной схемы является наличие в ней сразу двух таблиц фактов. Главной из них, безусловно, является таблица PF, предназначенная для хранения бюджетных значений. Эта таблица «обвязана» различными измерениями по схеме «Снежинка». Кроме того, в схеме присутствует еще одна отдельная таблица фактов, которая не связана ни с базовой таблицей фактов, ни с одним из основных измерений. Значения хранящейся в ней меры Ind_Value доступны только в разрезе единственного измерения Indicators, например, посредством выражения 1.
Выражение 1
select [Indicators].members on 1,
[Measures].[Ind Value] on 0
from [PF]
Результат отработки данного выражения представлен на рис. 5.
Рис. 5. Вывод элементов измерения Indicators
Описанная выше схема дает возможность хранить неограниченное количество различных переменных и, что особенно важно, использовать их в MDX-выражениях внутри многомерного пространства.
Создание измерения типа Account
После столь продолжительных, но необходимых подготовительных работ мы наконец можем перейти к созданию измерения типа Account. Для хранения элементов этого измерения в схеме данных выделим отдельную таблицу Dim_Account, структура которой представлена на рис. 4. В целом она соответствует табл. 1, которая обсуждалась выше, но в нее добавлены два новых атрибута:
- CustomMembers;
- Ext_Properties.
О назначении последних атрибутов мы поговорим чуть позже, а пока посмотрим на будущее измерение «Счета» как на обычное измерение типа Parent-Child.
Понятно, что ключевым атрибутом, посредством которого идентифицируются элементы измерения, нужно назначить атрибут Account_N. В нем хранятся номера финансовых счетов. Для этого присвоим базовому свойству Usage указанного атрибута значение Key.
Родительские элементы хранятся в колонке Account_N_Parent, соответственно ее следует определить в качестве родительской. Поэтому у этого атрибута свойство Usage должно иметь значение Parent (рис. 6).
Рис. 6. Определение свойств измерения Account
Большинство дальнейших операций по созданию измерения типа Account сводится к настройке определенных свойств различных атрибутов данного измерения.
Сначала для типа родительского атрибута следует установить значение Account. Напомним, что требуемый тип определяется в свойстве Type на закладке Properties в конструкторе измерений (см. рис. 6). Здесь мне хочется обратить внимание читателей на один принципиальный момент: тип Account должен быть назначен именно для родительского атрибута, в то время как ключевой атрибут измерения должен иметь стандартный тип — Regular. Одна из самых распространенных ошибок при создании измерений типа «Счет», неуклонно приводящая к его некорректной работе, состоит в том, что присвоение типов происходит в обратном порядке и тип Account назначается для ключевого атрибута измерения.
Аналогичным образом для атрибута Account_Type, хранящего названия типов финансовых счетов, в свойстве Type должно быть выбрано значение AccountType.
Вернемся теперь к родительскому атрибуту измерения. В поле UnaryOperatorColumn укажем колонку таблицы, в которой хранится оператор свертки. В рассматриваемом примере для этой цели используется колонка с именем Operator. Затем в поле CustomRollupColumn пропишем колонку таблицы CustomMembers, а поле CustomRollupPropertiesColumn — колонку Ext_Properties. Первое из перечисленных свойств позволяет определить источник для формул пересчета (customer member formulas), а второе предназначено для различных дополнительных параметров измерения.
На этом основную работу по настройке структуры измерения «Счет» можно считать законченной. Нам осталось лишь прописать в виде MDX-выражений правила расчета значений для отдельных статей бюджета.
Создание правил пересчета
Мы настроили вспомогательное измерение Dim_Service таким образом, чтобы аккумулировать доходы по всем статьям на одном счете «Выручка» измерения типа «Счет».
Кроме того, мы договорились, что общие расходы компании должны рассчитываться по формуле 1. Обозначим для удобства прямые расходы общепринятым сокращением COGS (Cost Of Good Sold), а накладные расходы — Indirect. Будем считать, что прямые расходы составляют 40% от выручки компании, а накладные — 20%.
Ранее на базе таблицы Account_Indicators были созданы новое измерение Indicators и мера Indicator Value. Если указанные выше параметры добавить в качестве элементов в измерение Indicators, то затем их можно будет вызывать в MDX-выражениях. Добавление новых членов в измерение Indicators осуществляется путем вставки строк в таблицу Account_Indicators. Формула 1 при этом переписывается следующим образом (формула 2).
Формула 2
[Затраты]=([Выручка],[Сумма])*(([COGS],[Ind Value])+([Indirect],[Ind Value]))
В формуле 2 кортежи ([COGS], [Ind Value]) и ([Indirect], [Ind Value]) обращаются к значениям меры Ind Value для элементов COGS и Indirect соответственно и возвращают нужные значения параметров — 0,4 и 0,2. Представленное выражение наглядно демонстрирует преимущества подхода по хранению формульных переменных в специально выделенной для этих целей таблице фактов. Если с течением временем меняются коэффициенты, определяющие долю различных расходов в выручке, то для пересчета общей величины затрат потребуется обновить соответствующие значения в таблице Account_Indicators. Если же изменится сама структура затрат, то адаптировать под нее формулу 2 также не составит труда — достаточно будет добавить в нее новое слагаемое.
Пропишем теперь формулу 2 в поле CustomMembers для элемента «Затраты» из таблицы Dim_Accounts (табл. 4).
Аналогичным образом поступим со статьей «Налоги», которая рассчитывается на базе показателя «Операционная прибыль».
А вот написание формулы пересчета для балансовых статей потребует больших усилий. Как уже отмечалось, все статьи листового уровня из бюджета «Прогнозный баланс» должны вычисляться по принципу накопительного итога. Лучше всего для задания зависимостей такого рода подходит оператор Sum(), который возвращает сумму числового выражения, рассчитанную по указанному набору.
В общем виде оператор Sum() имеет синтаксис: Sum( Set_Expression [ , Numeric_Expression ] ), где Set_Expression — многомерное выражение набора, а Numeric_Expression — выражение для координат ячейки, возвращающее число.
Бюджетная модель требует, чтобы в статьях пассива и актива для каждого месяца была указана величина чистой прибыли компании, заработанной за период с начала года до текущего месяца. Такой временной интервал описывается MDX-выражением (см. выражение 2):
Выражение 2
{[Дата].[Месяц].[Янв]:[Дата].[Месяц].CurrentMember}.
Соответственно набор, выбирающий из многомерного пространства все элементы, являющиеся показателями чистой прибыли за указанный период, может быть задан как прямое произведение элемента «Чистая прибыль» из измерения «Счета» и выражения 2 (см. выражение 3).
Выражение 3
{[Счета].[Cards_Of_Accounts].[Чистая_Прибыль]*{[Дата].[Месяц].[Янв]:[Дата].[Месяц].CurrentMember}}
Учитывая, что суммирование показателя чистой прибыли должно осуществляться по базовой мере «Сумма», оператор Sum() примет вид (см. выражение 4).
Выражение 4
sum({[Счета].[Cards_Of_Accounts].[Чистая_Прибыль]*{[Дата].[Месяц].[Янв]:[Дата].[Месяц].CurrentMember}},[Measures].[Сумма])
Добавим в измерение Dim_Accounts две служебных статьи: «Касса_Расчет» и «Прибыль_Расчет», сделаем их подчиненными для статей «Касса» и «Прибыль», а в поле CustomMembers добавим для них выражение 4 (см. табл. 4).
Определение правил форматирования ячеек
В принципе, после выполнения всех действий, описанных в предыдущем разделе, можно сразу переходить к финальному этапу — привязке измерения к базовой группе мер и процессингу аналитического куба. Однако сделаем небольшое отступление, чтобы обратить внимание читателей на одну полезную опцию — задание дополнительных параметров для формул пересчета при помощи свойства CustomRollupPropertiesColumn.
Измерение типа Account, по сути, является специальной версией измерения Parent-Child со всеми вытекающими отсюда последствиями. Характерной проблемой для измерений данного типа является сложность отображения его иерархической структуры в клиентских приложениях. Измерения Parent-Child, как правило, несбалансированы по своей природе — элементы листового уровня могут встречаться на различных уровнях иерархии. Поэтому при просмотре измерения в пользовательской форме часто бывает сложно понять, к какому уровню относится тот или иной элемент. Указанная проблема довольно успешно решается посредством задания собственных правил форматирования для различных элементов. Именно для этих целей и используется свойство CustomRollupPropertiesColumn, которое в рассматриваемом примере ассоциировано с полем Ext_Properties табл. 4.
Предположим мы хотим выделить все затратные статьи в бюджете «Прибыли и убытки» и статьи пассива бюджета «Прогнозный баланс» — сделать так, чтобы в пользовательской форме соответствующие суммы отображались красным цветом и были заключены в круглые скобки. Для этого нужно определить два параметра форматирования — FORMAT_STRING и FORE_COLOR. Первый из них задает формат отображения числа, а второй определяет цвет текста.
В нашем случае для всех статей, требующих выделения, в столбец Ext_Properties табл. 4 нужно вставить следующую строку:
FORMAT_STRING=”(#,##0)”, FORE_COLOR=”255”.
Последние штрихи
Нам осталось сделать совсем немного — добавить созданное измерение в аналитический куб и связать его с базовой группой мер. Процедура добавления измерения типа «Счет» в куб ничем не отличается от присоединения к кубу обычного (Regular) измерения. В редакторе кубов на вкладке Cube Stucture в окне Dimensions выбирается пиктограмма аналитического куба, а затем в контекстном меню вызывается команда Add Cube Dimension.
Для привязки добавленного измерения к группе мер используется вкладка Dimension Usage (Привязка измерений). Из рис. 4 видно, что измерение «Счет» связано с таблицей фактов не напрямую, а опосредованно — через измерение Dim_Service. Соответственно при определении связи в окне Define Relationship нужно выбрать ссылочный (Referenced) тип связи (рис. 7).
Рис. 7. Привязка измерения Account к мерам куба
После выполнения процессинга куба измерение «Счет» наконец можно использовать в пользовательских отчетах (рис. 8).
Рис. 8. Отчет, представляющий бюджеты компании
Как видно из рис. 8, нам удалось создать измерение «Счет», пригодное для планирования типовых бюджетов. Отметим, что указанное измерение работает совместно с другими аналитиками куба — в отчетной форме план представлен с детализацией по двум структурным подразделениям компании. Для каждого месяца валюта баланса равна суммарной величине чистой прибыли, начисленной в бюджете «Прибыли и убытки» за предшествующий период. Значение балансовых статей для квартала равно величине показателей для последнего месяца этого квартала, в то время как квартальные показатели статей бюджета «Прибыли и убытки » рассчитываются путем суммирования месячных значений. Кроме того, ряд бюджетных статей имеет специальное форматирование, которое было определено на уровне аналитического куба.
Заключение
Измерение типа Account является самым сложным типом измерения среди всех присутствующих в Microsoft Analysis Services. Оно требует серьезной теоретической подготовки и определенных навыков работы с многомерными базами данных.
Обычному пользователю преимущества применения такого измерения могут показаться сомнительными. В действительности же измерение «Счет» является тем компонентом, если угодно — «пятым элементом», без которого нельзя создать ни одну финансовую систему, удовлетворяющую современным требованиям к отчетности. Поэтому оно в обязательном порядке должно быть взято на вооружение разработчиками аналитических приложений, чему, надеюсь, будет способствовать данная статья.
 |
|
