Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / OLAP и DWH Новый топик    Ответить
 Как получить среднее (AVG) для calculation member? /SSAS Standard edition  [new]
Сергей NZ
Member

Откуда:
Сообщений: 3
Я новичок в SSAS, прошу сильно не пинать

Есть мера, назовем её "Цена производства ед. номенклатуры", которая приходит напрямую из БД
Есть вычисляемая мера "Стоимость производства", которая считается как "Цена производства ед. ..." x "Количество произведено"

На уровне "номенклатура" всё хорошо, но если сделать группировку по месяцу, то на уровне этой группировки происходит суммирование цен всех нижележащих членов и, соответственно, искаженная (многократно завышенная) стоимость производства за месяц.

Видимо, в формуле calculation member'а нужно использовать агрегирующую функцию AVG(), но в "стандартной" редакции её нет :(
Изменение параметра AverageOfChildren с "Sum" на "AverageOfChildren" в настройках меры почему-то ни к чему не приводит

Задачка типовая, наверняка как-то решается и в "стандартной" редакции. Может сталкивался кто?
29 май 18, 18:25    [21451484]     Ответить | Цитировать Сообщить модератору
 Re: Как получить среднее (AVG) для calculation member? /SSAS Standard edition  [new]
alexdr
Member

Откуда:
Сообщений: 214
Сергей NZ,
считать стоимость нужно в ETL как произведение цены на количество. Полученный результат тащить в куб и делать физической мерой "Стоимость".
А "готовую" цену (как факт/меру) тащить в куб не нужно. Цена должна быть в кубе калькулируемой и считаться как частное Цена = Стоимость/Количество. Количество не должно быть равным 0. Будет работать на всех уровнях иерархий измерений. Вот и получите "Среднюю цену" в зависимости от контекста, выбранного на осях куба.
29 май 18, 19:51    [21451659]     Ответить | Цитировать Сообщить модератору
 Re: Как получить среднее (AVG) для calculation member? /SSAS Standard edition  [new]
Сергей NZ
Member

Откуда:
Сообщений: 3
alexdr, спасибо за Ваш комментарий.
Мой пример несколько упрощен. На самом деле в расчетах принимают участие меры, приходящие из разных таблиц и с разной гранулярностью. Вариант посчитать всё в реляционке существует, но очень энергозатратен. MDX вроде для этого самый подходящий инструмент.

Вот одна из формул (их два десятка)

case [Sku].[Production Method].MemberValue --считаем с/с на единицу [MCOGs Production per QTY]
when 'IMPORT'then 0 --для импортного товара не считаем
when 'PACKAGING' then [Measures].[ATFPrice] --для собственного товара - забираем готовую стоимость производства из вьюшки Х
when 'FULL' THEN [Measures].[Prime Cost Per Amount] --для "смешанного" товара расчетную с-с из вьюшки Y
ELSE NULL
END

Если бы можно было как-то так:

case [Sku].[Production Method].MemberValue --считаем с/с на единицу [MCOGs Production per QTY]
when 'IMPORT'then 0 --для импортного товара не считаем
when 'PACKAGING' then AVG([Measures].[ATFPrice], SKU.CurrentMember)
when 'FULL' THEN AVG([Measures].[Prime Cost Per Amount], SKU.CurrentMember)
ELSE NULL
END
.. это бы избавило от необходимости протягивать все возможные варианты в одну сборную вьюшку. Если не получится, то, конечно, так и буду делать.
29 май 18, 20:06    [21451684]     Ответить | Цитировать Сообщить модератору
 Re: Как получить среднее (AVG) для calculation member? /SSAS Standard edition  [new]
alexdr
Member

Откуда:
Сообщений: 214
Сергей NZ,

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

Сильно не вникая в Ваш MDX, просто хочу напомнить, что Моша всегда предостерегал от использования в MDX конструкций типа CASE WHEN... Также не понимаю зачем вот это: "[Sku].[Production Method].MemberValue"? Боюсь, вы пытаетесь воспользоваться MDX не по назначению. Это может очень дорого обойтись.

и еще... я писал про ETL, не про "вьюшки". Возможно, это мои тараканы, но я всячески избегаю каких-либо "вьюшек" под кубом, которые делают какую либо значимую работу, имеют джоины с разными таблицами и т. д. и т. п. Считаю это правильным подходом, а сам куб OLAP считаю не более чем надстройкой над правильно спроектированным DWH, в котором данные лежат в готовом виде преимущественно в "звездной" схеме.
29 май 18, 21:00    [21451813]     Ответить | Цитировать Сообщить модератору
 Re: Как получить среднее (AVG) для calculation member? /SSAS Standard edition  [new]
Сергей NZ
Member

Откуда:
Сообщений: 3
Вы описываете, я так понял, классическую архитектуру DWH + OLAP, как её нужно строить с нуля. Я получил систему в наследство, как это часто бывает. Вьюшки принимают на себя часть ETL, плюс становится возможным "заглянуть" непосредственно в OLTP-систему. В общем такая архитектура тоже имеет свои преимущества.
Меры разбросаны по группам по смыслу, но чаще просто как попало :) Что построено, то построено, к этому уже привыкли.
CASE WHEN - базовая инструкция любого языка. Странно, что есть какие-то предостережения. Не поделитесь прямой ссылкой?
Часть конструкций мне, видимо, придётся действительно вытаскивать из базы данных :(
В любом случае, спасибо за советы
29 май 18, 23:15    [21452028]     Ответить | Цитировать Сообщить модератору
 Re: Как получить среднее (AVG) для calculation member? /SSAS Standard edition  [new]
alexdr
Member

Откуда:
Сообщений: 214
Сергей NZ
CASE WHEN - базовая инструкция любого языка. Странно, что есть какие-то предостережения.

Конструкция есть, а толку нет. Речь идет о "внутренностях" движка SSAS и оптимизатора запросов.
Сергей NZ
Не поделитесь прямой ссылкой?

Боюсь, что нет. Весь Мошин блог Performance of IIF function in MDX, похоже, был зачищен. Могу процитировать (выделение в тексте мое).

Microsoft OLAP by Mosha Pasumansky
IF is one of the most popular MDX functions, (this was proved by these surveys). Yet, it can cause significant performance degradation, which is often blamed on other parts of the system. Many times it is simple to rewrite the MDX expression to get rid of IIF altogether, and other times it is possible to slightly change the IIF to increase performance. While I and others mentioned this fact several times in blog entries, forum postings etc, people keep using and using it (and using it wrong). What is worse, after seeing CASE operator in samples and BOL, people start using CASE instead of IIF. From the performance point of view, CASE is always worse than IIF, at least IIF is possible to optimize in some case, but never CASE.


Т. е. эта конструкция - т. н. "performance killer".
30 май 18, 11:27    [21452987]     Ответить | Цитировать Сообщить модератору
 Re: Как получить среднее (AVG) для calculation member? /SSAS Standard edition  [new]
alexdr
Member

Откуда:
Сообщений: 214
Сорри, неправильно скопировалось а исправить текст не могу.
Первое слово в цитате следует читать не как "IF", а как "IIF".
Сергей NZ
Я получил систему в наследство, как это часто бывает.

Я ни к чему не призываю, и во многом это зависит от обстоятельств, но иногда проще переделать legacy solution на правильное решение, особенно если планируется его длительная поддержка и развитие.
30 май 18, 11:42    [21453082]     Ответить | Цитировать Сообщить модератору
Все форумы / OLAP и DWH Ответить