Вариант 1

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

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

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

Вариант 2

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

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

Вариант 3.

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

Два варианта решения для 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,Свойство), соответственно мы получим развертку по тем статьям Филиала, в свойстве которых содержится «В», значение св-ва Филиала.

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

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

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().

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