Эксперт-Клуб

1Вопрос:

Как сделать в формате планирования, чтобы для каждого филиала отображался свой набор статей (в SAP BPC NW/MS)? У разных филиалов статьи могут частично совпадать, частично отличаться.

Комментарии:

Дмитрий Гераськин (Рейтинг: 191) 11:46, 16 мая 2011


Комментарий эксперта

Вариант 1

Создать вспомогательный формат планирования. Структура формы должна представлять собой матрицу : статьи в строках, филиалы в столбцах, в ячейках – технический показатель “флаг релевантности”.В ячейки вносится “1” в том случае, если статья должна отображаться в процессе планирования для филиала.

В основном формате планирования использовать средства VBA (макрос) для удаления строк по условию “флаг релевантности”<>1. Настроить запуск макроса на момент  сразу после открытия формата планирования.

Альтернативно возможно использование средств условного форматирования Excel вместо VBA (отсортировать все записи по флагу и закрасить в цвет фона по условию “флаг релевантности”<>1).

Вариант 2

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

Применимо только для тех случаев, когда конкретный пользователь отвечает только за 1 филиал.

Вариант 3.

Тоже что и вариант 1, только вместо макросов или форматирования excel написать exit в badi, обрабатывающий вывод формата/отчета.

Андрей Лебедев (Рейтинг: 184) 13:18, 25 ноября 2011


Комментарий эксперта

Два варианта решения для NWверсии:

Вариант 1

Для решения данной задачи для измерения Статья можно создать столько свойств, у скольких филиалов данная статья должна отображаться в формате планирования. Затем в формате планирования с помощью функции EVDREв области параметров в поле MemberSetсделать фильтр по свойству равному значению Филиала выбираемого, например, в CV.

Например.

 

Филиал1

Филиал2

Филиал3

Статья 1

 

X

X

 

Статья 2

 

X

 

X

Статья 3

 

 

X

X

 

MemberSet

Филиал1=”X”

Где значение Филиал1 берется из CV

Минусы: Если филиалов достаточно много большое количество свойств затрудняет ведение НСИ.

 

Вариант 2

Чтобы для каждого филиала отображался свой набор статей, можно создать отдельное приложение,  в котором создать измерение, в котором в виде иерархии объединить 2 сущности Филиал  и Статья.

Например в компании есть 2 филиала:

X– Филиал1

Y– Филиал2

И 3 статьи:

A– Статья1

B– Статья2

C– Статья3

 

Их можно представить в следующей иерархии

Код

Наименование

Свойство1

Свойство2

 X

Филиал1

 

 

XA

Статья1

X

A

XB

Статья2

X

B

Y

Филиал2

 

 

YA

Статья1

Y

A

YC

Статья3

Y

C

Затем аналогично Варианту 1 в формате планирования с помощью функции EVDREв области параметров в поле MemberSetсделать фильтр по свойству равному значению Филиала выбираемого, например, в CV, при этом в Expandтакже будет выводиться Свойство2.

Минусы: ведение иерархии  Филиалов и Статей

 

Вариант решения для MSверсии

Решение данной задачи с помощью функции INSTR, отсутствующей в данный момент в NWверсии 7.5

Для измерения Филиал и Статья необходимо создать свойства, например,

Entity

Свойство

Филиал1

А

Филиал2

B

Филиал3

C

 

Account

Свойство

Статья1

AC

Статья2

AB

Статья3

BC

 

Затем в формате планирования с помощью функции EVEXPв параметре Filterуказать IntStr(Account.CurrentMember.Propreties(“Свойство”), “B”), где В – результат значения EVPRO(«Appname»,Entity,Свойство), соответственно мы получим развертку по тем статьям Филиала, в свойстве которых содержится «В», значение св-ва Филиала.

Павел Сухарев (Рейтинг: 32) 14:03, 25 ноября 2011


Комментарий эксперта

Постановка задачи

Предположим, что мы располагаем:

1. ИзмерениемP_CC (Planning Cost Center):

ID

NEWID

EVDESCRIPTION

All_CC

 

Все дирекции

DIR_WEST

 

Дирекция ЗАПАД

DIR_CENTRAL

 

Дирекция ЦЕНТР

DIR_EAST

 

Дирекция ВОСТОК

2. Измерением Product:

ID

NEWID

EVDESCRIPTION

Prod000

 

Вся продукция

Prod001

 

Конфеты

Prod002

 

Вафли

Prod003

 

Печенье

Prod004

 

Пряники

Наша задача состоит в построении отчета с неполным пересечением измерений P_CC и Product:

DIR_WEST

PROD001

Дирекция ЗАПАД

Конфеты

*

DIR_WEST

PROD002

Дирекция ЗАПАД

Вафли

*

DIR_WEST

PROD003

Дирекция ЗАПАД

Печенье

*

DIR_WEST

PROD004

Дирекция ЗАПАД

Пряники

 

DIR_CENTRAL

PROD001

Дирекция ЦЕНТР

Конфеты

*

DIR_CENTRAL

PROD002

Дирекция ЦЕНТР

Вафли

 

DIR_CENTRAL

PROD003

Дирекция ЦЕНТР

Печенье

*

DIR_CENTRAL

PROD004

Дирекция ЦЕНТР

Пряники

 

DIR_EAST

PROD001

Дирекция ВОСТОК

Конфеты

 

DIR_EAST

PROD002

Дирекция ВОСТОК

Вафли

 

DIR_EAST

PROD003

Дирекция ВОСТОК

Печенье

*

DIR_EAST

PROD004

Дирекция ВОСТОК

Пряники

*

* - выделены строки, которые должны отображаться в отчете. Для каждой дирекции выполняется условие – множество продуктов частично совпадает, частично отличается от других.

 

Несколько общих замечаний

Отчеты, формируемые на основе многомерных баз данных, содержат 3 оси:

- Axis1 – Ось X таблицы;

- Axis2 – Ось Y таблицы;

- Where – невидимая в отчете ось, которая соответствующая разделу фильтров.

На осях отчета располагаются "Наборы" - Sets, являющиеся подмножествами куба. Наборы могут быть 2-х типов:

1) набором элементов – экземпляры относятся к одному измерению:

{

 (DIR_WEST),

 (DIR_CENTRAL)

}

2) набором кортежей – упорядоченных последовательностей элементов из нескольких измерений:

{

 (DIR_WEST,PROD001),

 (DIR_WEST, PROD002),

 (DIR_CENTRAL, PROD003).

}

 

В терминологии OLAP задача сводится к размещению на оси Y отчета следующего набора:

{

(DIR_WEST, PROD001)

(DIR_WEST, PROD002)

(DIR_WEST, PROD003)

 (DIR_CENTRAL, PROD001)

 (DIR_CENTRAL, PROD003)

 (DIR_EAST, PROD003)

(DIR_EAST, PROD004)

}

 

Программа SAP BPC предоставляет пользователю множество способов сделать это.

 

Способ 1 (самый простой) – использование ключа NOEXPAND в функции EVDRE

Самый простой. Сначала создаем макет, в котором оставляем только нужные кортежи из набора, затем фиксируем их при помощи ключа NOEXPAND (отменяет развертывание измерения в отчете) в параметре MemberSet измерений P_CC и Product.

Рисунок 1

 

Способ 2 – совместное использование нескольких экземпляров функции EVDRE

Самый удобный с позиций пользователя, так позволяет динамически и раздельно менять состав продуктов для каждой из дирекций. Требует много времени на разработку формы, серьезно снижает производительность обработки запросов.

Рисунок 2

На листе книги MS Excel создаем 3 экземпляра функции EVDRE и замыкаем их на один ключевой диапазон. Указываем для всех функций один диапазон столбцов – ColKeyRange, а диапазоны строк RowKeyRange размещаем друг под другом без пробелов.

В каждой функции EvDRE указываем единственный элемент измерения P_CC – идентификатор дирекции. Таким образом, для каждой дирекции получаем возможность определить собственный набор элементов измерения Product. Параметр MemberSet является строковой переменной, которую можно обрабатывать стандартными функциями MS Excel.

Поэтому размещаем на листе элементы формы "Флажок" в количестве Число_Дирекций*Число_Продуктов.

Затем составляем формулу, объединяющую в одну строку все продукты одной дирекции, при условии, что они выбраны в форме (установлен соответствующий флажок):

=СЦЕПИТЬ(ЕСЛИ(I15;СЦЕПИТЬ($G15;",");"");ЕСЛИ(I16;СЦЕПИТЬ($G16;",");"");ЕСЛИ(I17;СЦЕПИТЬ($G17;",");"");ЕСЛИ(I18;$G18;""))

Составленная строка требует дополнительной обработки – нужно проверить, нет ли у нее в конце знака "," и в случае его наличия удалить из строки.

=ЕСЛИ(ПРАВСИМВ(PRODUCT_WEST_IN;1)=",";ПСТР(PRODUCT_WEST_IN;1;ДЛСТР(PRODUCT_WEST_IN)-1);PRODUCT_WEST_IN).

Полученную строку, можно передать в качестве аргумента для параметра MemberSet, просто в виде ссылки между ячейками книги MS Excel.

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

 

Способ 3 – использование опции подавления Suppress

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

В MDX такая манипуляция выполняется посредством выставления ключа NON EMPTY для оператора SELECT. Ключ NON EMPTY позволяет оставить в отчете только те элементы, у которых определены значения меры, указанной в условии WHERE.

Рисунок 3

В SAP BPC данный функционал реализовывается путем включения опции подавления Suppress для любого измерения, развертываемого по строкам отчета. В этом случае все "пустые" строки отчета будут скрыты.

 

Способ 4 – использование опции подавления Suppress для другого диапазона данных

Измерения P_CC и Product связаны соотношением "многие-ко-многим". Поэтому для установления связи между ними следует использовать таблицу фактов. Например, если в таблице фактов для кортежа (DIR_WEST, PROD001) для некоторой меры проставлено какое-либо значение, то они считаются связанными по данной мере.

Добавим в измерение P_DATASRC новый элемент REF_RELATION и изменим на него ракурс формы.

Рисунок 4

Для данного ракурса проставим для нужных пар "1" – установим связь между измерениями через таблицу фактов, затем запишем данные в БД.

Затем повторно меняем ракурс на меру UPLOAD, где будут храниться плановые значения.

Рисунок 5

В параметре Suppress пишем следующую инструкцию [UPLOAD,REF_RELATION], которая требует выполнять подавление строк по нулевым элементам куба, но в ракурсе REF_RELATION. Иными словами, остаются только строки с непустыми значениями. В итоге, как видно из рисунка, в разделе строк отчета присутствуют только интересующие нас кортежи, но при этом значения элементов по ракурсу UPLOAD равны "0".

 

Способ 5 – использование фильтров MS Excel

В ряде случаев оправданным представляется следующий подход – посредством функций обмена данными форму выводится полный набор элементов из нескольких измерений. Затем он фильтруется при помощи функций MS Excel.

Создадим в измерении Product пользовательское свойство "Подразделение" (SubUnit), в котором через запятую будем перечислять ID дирекций, занимающихся распространением выбранного продукта.

Рисунок 6

Таким образом, в ненормализованном формате можно установить связь "многие-ко-многим" без использования промежуточных таблиц.

В функции EVDRE расширим диапазон заголовков на 2 столбца (см. рисунок 7)

Рисунок 7

В первом столбце напишем формулу =EVPRO($H$2;H15;"SubUnit"), позволяющую получить значение свойства SubUnit для текущего элемента измерения Product. К примеру, такая операция выводит напротив каждого элемента Prod001 исходного набора строку "DIR_WEST, DIR_CENTRAL". Нам осталось понять, а входит ли первый элемент "Дирекция" из кортежа (Дирекция, Продукт) в допустимое множество значений. Такая проверка выполняется посредством функции поиска подстроки из арсенала функций MS Excel: =ЕСЛИОШИБКА(ПОИСК(G15;K15);0).

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

 

Способ 6 – явное определение набора в функции EVENE

В функции EvENE при построении динамического расширения имеется возможность в параметре SetExpression указать MDX-выражение для определения выводимого набора. Набор можно составить несколькими способами. Самый простой из них – явное перечисление нужных элементов.

Набор

{

(DIR_WEST, PROD001)

(DIR_WEST, PROD002)

(DIR_WEST, PROD003)

 (DIR_CENTRAL, PROD001)

 (DIR_CENTRAL, PROD003)

 (DIR_EAST, PROD003)

(DIR_EAST, PROD004)

}

в синтаксисе MS OLAP описывается следующей строкой:

{([P_CC].[H1].[DIR_WEST],[Product].[H1].[Prod001]),([P_CC].[H1].[DIR_WEST],[Product].[H1].[Prod002]),([P_CC].[H1].[DIR_WEST],[Product].[H1].[Prod003]),([P_CC].[H1].[DIR_Central],[Product].[H1].[Prod001]),([P_CC].[H1].[DIR_Central],[Product].[H1].[Prod003]),([P_CC].[H1].[DIR_EAST],[Product].[H1].[Prod003]),([P_CC].[H1].[DIR_EAST],[Product].[H1].[Prod004])}

Данное выражение можно использовать в качестве аргумента для функции TopCount, число "верхних" элементов следует указать равным мощности набора.

Функцию TopCount, в свою очередь, можно передать в качестве выражения набора в функцию EvENE.

Рисунок 8

 

Способ 7 – определение набора для функции EVENE посредством вычисления рангов элементов

Определить набор требуемых кортежей можно следующим способом:

1. Составить набор из прямого произведения измерений "Дирекция" и "Продукт";

2. Отобрать только нужные кортежи, указывая их ранг в наборе.

Добавляем в куб именованный набор, который назовем [Дир_Услуги].

Рисунок 9

Данный набор содержит все возможные сочетания элементов из измерений "Дирекция" и "Продукт" (см. рисунок 10).

Рисунок 10

Из данного множества можно выбрать любое подмножество, достаточно выбрать кортежи с нужным рангом – в MS OLAP нумерация начинается с нулевого элемента.

Интересующий нас набор состоит из кортежей со следующими рангами: 6; 7; 8; 11; 13; 18; 19.

На листе MS Excel создаем диапазон из выражений вида ", {[Дир_Услуги]}.Item(n)" (см. Рисунок 11)

Рисунок 11

Затем функцией MS Excel "Сцепить" формируем из отдельных выражений итоговую строку набора, которую обрабатываем также, как в способе 6.

Несмотря на большую трудоемкость, Способ 7 по сравнению со Способом 6 обладает одним существенным преимуществом. Так как выражения типа ", {[Дир_Услуги]}.Item(n)" являются обычными строками MS Excel, их также можно составлять динамически – передавать в качестве входного параметра число "N" – ранг нужного кортежа.

Таким образом, "на лету" можно составлять произвольную выборку элементов.

 

Способ 8 (Альтернативный) – через сводную таблицу MS Excel

Вопрос организации альтернативного доступа к данным BPC не является праздным. Интерфейс BPC в MS Excel можно использовать двумя способами:

- для ввода данных;

- для просмотра данных.

Если просмотр данных осуществляется вне процедур BPF, его также можно вести вне интерфейса BPC, сэкономив, таким образом, на клиентских лицензиях. В MS Excel есть возможность обращаться к данным из СУБД через интерфейс OLE DB, который поддерживает олицетворение. Соответственно, посредством OLE DB можно организовать множественный доступ к данным, используя единственную локальную учетную запись на сервере, что очень эффективно с позиций лицензирования – стоимость легализации равна стоимости 1 пользовательской лицензии SQL Server.

Создаем представление View_tbl_Task, объединяющее в себе данные из всех таблиц фактов:

Select * from  dbo.tblFACTWBTask

union all

select * from   dbo.tblFACTTask

union all

select * from  dbo.tblFAC2Task

Создадим на сервере SQL_Server внутреннего пользователя OLAP_User c аутентификацией средствами СУБД и дадим ему права на чтение View_tbl_Task.

Затем в MS Excel организуем источник данных к СУБД SQL Server, используя для авторизации данные пользователя OLAP_User

Рисунок 12

На базе данного источника организуем сводную таблицу (см. рисунок 13).

Рисунок 13

Как видно из рисунка в сводной таблице показываются только требуемые кортежи. Это объясняется тем, что в MDX-запросах, генерируемых объектом Pivot Cache при составлении макета сводного отчета, используется ключ NON EMPTY, действие которого аналогично функции подавления из Способа 3.

 

Способ 9 (самый сложный) – с использованием функции Filter() в MDX-выражении

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

В SAP BPC есть относительно удобная возможность выполнить такую операцию. Операция выполняется в 2 этапа:

1. При помощи функций EVEXP() создаются наборы из элементов одного измерения;

2. Данные наборы объединяются нужным образом посредством функции EVNXP().

Функция EVNXP() с точки зрения MDX (как показывает трассировщик) представляет собой функцию Filter(), в которой фильтруемым набором является декартовое произведение наборов, созданных функциями EVNXP(), а условие на фильтрацию берется из аргумента "Фильтр" функции EVNXP(). Условие фильтрации должно представлять собой выражение, возвращающее "истину" или "ложь" для каждого кортежа набора. Здесь возникают некоторые технические проблемы, о которых напрямую не сказано в документации на SAP BPC. Логическое условие отбора должно составляться в нотациях OLAP-куба. Во-первых, измерения в OLAP-кубе представляют собой набор иерархий, поэтому в выражении в выражении к атрибуту следует обращаться с точностью до иерархии. Во-вторых, у каждого атрибута измерения есть системное свойство "ID", в котором хранится идентификатор элемента. Но в SAP BPC для каждого измерения создается дополнительный атрибут, который, по своей сути, является пользовательским, но имеет такое же наименование – "ID". В данном атрибуте хранятся идентификаторы элементов SAP BPC, но они не являются ключами измерения.

Поэтому обращение к свойству "ID" при помощи инструкции [P_CC.H1].CurrentMember.Properties("ID") будет возвращать значения, отличные от тех, которые видны в конструкторе измерений SAP BPC.

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

1. Создадим в измерении P_CC новое пользовательское свойство "User_Key", в котором будем хранить дубликаты ключей элементов из атрибута ID.

Рисунок 14

2. Измерение Product преобразуем в иерархию Product_Ext вида:

Рисунок 15

В новом измерении продукты стали узлами, под ними на листовом уровне расположены технические элементы типа Prod001_W.

Кроме того, в измерение добавлено новое свойство "SubUnit", позволяющее указать дирекцию, которая занимается продажей выбранного продукта. Например, продажей Prod001_W занимается DIR_WEST, а Prod001_C – DIR_Central. Если соответствующая ячейка пустая, значит реализацией данного продукта ни одна из дирекций не занимается. Вспомогательный уровень позволил сделать элементы атрибута SubUnit скалярными величинами в отличие от списка, как это было в Способе 5.

Теперь при помощи 2 функций EVNXP() формируем наборы, состоящие из элементов листового уровня обоих измерений (см. рисунок 16).

Рисунок 16

Составляем условие фильтрации:

[Product_Ext.H1].CurrentMember.Properties("SUBUNIT")=[P_CC.H1].CurrentMember.Properties("User_Key").

Наконец, объединяем все эти 3 выражения в одной функции EVNXP().

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