Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / OLAP и DWH Новый топик    Ответить
 MDX не для слабонервных  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 3535
Написал сегодня MDX. Жестокий запрос, слабонервным не смотреть.

Суть его в следующем.

У группы мер продажи ([Measures].[Продажи по оплатам, руб])

Есть два измерения даты [Даты].[ГМД] -- собственно дата оплаты и [Даты По заказам].[ГМД] -- дата заказа. Разница между первой и второй время доставки.

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

Результат получился, но работает ну очень не быстро. На 50 регионов и 30 дат может работать несколько минут.

Кто может навскидку подсказать, что тут можно пытаться ускорить?

WITH 
MEMBER [Measures].[dds] as Datediff("d", [Даты По заказам].[ГМД].CurrentMember.Member_Value, [Даты].[ГМД].CurrentMember.Member_Value)
MEMBER [Measures].[LN] as [Даты По заказам].[ГМД].Level.Name
MEMBER [Measures].RunningTotal AS SUM( StrToMember('[Даты].[ГМД].[' + [Даты По заказам].[ГМД].CurrentMember.Member_name + ']'):[Даты].[ГМД].CurrentMember,[Measures].[Продажи по оплатам, руб])
MEMBER [Measures].SS AS 
	"{" + GENERATE(StrToSet('[Даты].[ГМД].[' + [Даты По заказам].[ГМД].CurrentMember.Member_name + ']:[Даты].[ГМД].[' + [Даты По заказам].[ГМД].CurrentMember.Member_name + '].Lead(30)'),  
		"(" + MEMBERTOSTR([Даты].[ГМД].CurrentMember) + "," +  MEMBERTOSTR([Даты По заказам].[ГМД].CurrentMember) + "), "
	) + "NULL}"
MEMBER [Measures].S2 AS 
	SUM(StrToSet([Measures].SS), [Measures].[Продажи по оплатам, руб])
MEMBER [Measures].S3 AS 
	SUM(TAIL(FILTER(StrToSet([Measures].SS), RunningTotal / [Measures].S2 < 0.5), 1), [Measures].DDS)
MEMBER S5 AS 
	SUM(TAIL(FILTER(StrToSet([Measures].SS), RunningTotal / [Measures].S2 < 0.5), 1), [Measures].RunningTotal)
MEMBER S6 AS 
	SUM(HEAD(FILTER(StrToSet(SS), RunningTotal / S2 >= 0.5), 1), RunningTotal)
MEMBER [Measures].[Дней до выкупа3] 
AS
	IIF([Measures].[LN] <> 'Дата' OR [Measures].S2 = 0, NULL, 
		IIF(([Measures].S6 - [Measures].S5) = 0, [Measures].S3, (0.5 * [Measures].S2 - [Measures].S5) / ([Measures].S6 - [Measures].S5) + [Measures].S3)
	)
SELECT {[Даты По заказам].[ГМД].[2015-01-10], [Даты По заказам].[ГМД].[2015-01-20], [Даты По заказам].[ГМД].[2015-02-01], [Даты По заказам].[ГМД].[2015-02-10], [Даты По заказам].[ГМД].[2015-02-20]} ON COLUMNS, 
NON EMPTY Hierarchize(DrilldownMember({{DrilldownLevel({[АдресФО].[КЛАДР].[All]},,,INCLUDE_CALC_MEMBERS)}}, 
{[АдресФО].[КЛАДР].&[1001983663]},,,INCLUDE_CALC_MEMBERS)) * 
{ 
	[Measures].[Дней до выкупа3]
	//,[Measures].S2, [Measures].S3, [Measures].S5, [Measures].S6,[Measures].[LN]
} ON ROWS
FROM [Куб для закупок]
11 мар 16, 19:19    [18921444]     Ответить | Цитировать Сообщить модератору
 Re: MDX не для слабонервных  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 31470
Блог
Ужасно.
Исходя из постановки:

a_voronin
Есть два измерения даты [Даты].[ГМД] -- собственно дата оплаты и [Даты По заказам].[ГМД] -- дата заказа. Разница между первой и второй время доставки.
Соответственно требовалось посчитать среднее время до выкупа в разрезе регионов.


Сразу на уровне запроса СУБД считается разница, например, в днях, которая идет в куб. На нее вешается формат даты. Дальше усреднить по чему угодно не сложно.
11 мар 16, 19:39    [18921501]     Ответить | Цитировать Сообщить модератору
 Re: MDX не для слабонервных  [new]
vikkiv
Member

Откуда: London / Zurich
Сообщений: 1141
если на вскидку и не имея куба то основная методология будет наверное добавлять и отображать каждую меру пошагово (начиная с null), и выполнив пару раз записать время обработки, дифференциал и будет показателем насколько тяжела мера, потом её уже ковырять.
кроме того на стадии разработки я-бы убрал все фишки Hierarchize/DrilldownMember/DrilldownLevel особенно INCLUDE_CALC_MEMBERS, начав с минимального crossjoin-a (поэтому в мерах и начинать с null, самую лёгкую физическую, потом уже наращивать расчётными).. т.к. пока неизвестно меры тормозят или структуры измерений генерируются долго..

кроме того имхо вроде как не очень-то рекомендуется создавать кучу подмер, в относительном большинстве случаев весь расчёт выпоненный в одной (или минимальном количестве) мере получается более производительным чем куча мелких собранная в одну (типа в противовес нормализации :P) хотя опять-же на уровне разработки - тестируется именно по кускам..
11 мар 16, 19:58    [18921555]     Ответить | Цитировать Сообщить модератору
 Re: MDX не для слабонервных  [new]
vikkiv
Member

Откуда: London / Zurich
Сообщений: 1141
то есть "...получается более производительным чем куча мелких по отдельности.."
11 мар 16, 20:03    [18921570]     Ответить | Цитировать Сообщить модератору
 Re: MDX не для слабонервных  [new]
vikkiv
Member

Откуда: London / Zurich
Сообщений: 1141
Критик,

на уровне SQL считать все возможные комбинации? честно говоря тут-же небольшой такой скептицизм вклинивается..
11 мар 16, 20:10    [18921597]     Ответить | Цитировать Сообщить модератору
 Re: MDX не для слабонервных  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 31470
Блог
vikkiv,
есть заказ, у него есть дата заказа, и дата отплаты, тут нет никаких комбинаций
12 мар 16, 07:19    [18922793]     Ответить | Цитировать Сообщить модератору
 Re: MDX не для слабонервных  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 3535
Критик
vikkiv,
есть заказ, у него есть дата заказа, и дата отплаты, тут нет никаких комбинаций


Ладно, поставим вопрос так, менять куб было трудоемко, а в существующем кубе все данные есть.

То есть есть кортеж или клетка куба (дата1, дата2, (другие измерения), показатель).

Требуется
1) Прогуляться скользящим окном вдоль измерения дата2 (данном случае дата2:дата2.Lead(30))
2) Сделать по этому скользящему некое вычисление , в данном случае найти медиану в нарастающем итого продаж.

Так вот мне надо вычислить разницу дат, то поскольку я гуляю в скользящем окне, то как мне заставить работать CurrentMember,

[Measures].[dds] as Datediff("d", [Даты По заказам].[ГМД].CurrentMember.Member_Value, [Даты].[ГМД].CurrentMember.Member_Value)

Мне удалось это сделать только используя generate , которая и является тут наиболее трудоемкой функцией, потому что они сначала сгенерировала строки, потом отпарзила их, потом стала по множеству вычислять. А как обойтись без перехода на строки.
12 мар 16, 12:54    [18923220]     Ответить | Цитировать Сообщить модератору
 Re: MDX не для слабонервных  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 3535
Критик
Ужасно.
Исходя из постановки:

a_voronin
Есть два измерения даты [Даты].[ГМД] -- собственно дата оплаты и [Даты По заказам].[ГМД] -- дата заказа. Разница между первой и второй время доставки.
Соответственно требовалось посчитать среднее время до выкупа в разрезе регионов.


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


Это можно сделать, но более интересным является вопрос: как писать это на MDX?
12 мар 16, 13:01    [18923237]     Ответить | Цитировать Сообщить модератору
 Re: MDX не для слабонервных  [new]
Владимир Штепа
Member

Откуда: Hannover
Сообщений: 6001
Лучший мдх это ненаписанный мдх
Если насчитать в хранилище или во вьюхе без особых тормозов при процессигнге куба не составляет особых затрат (времени/труда)
то надо делать там.

Если уж из любви к искусству (мдх) то для разтомаживания запроса надо избавиться от StrToХ и ХToStr, которые садят производительность
13 мар 16, 20:07    [18925952]     Ответить | Цитировать Сообщить модератору
 Re: MDX не для слабонервных  [new]
vikkiv
Member

Откуда: London / Zurich
Сообщений: 1141
если у тебя самая тормозная часть сам стринговый SET (хотя и производные тоже, так например TAIL используется 2 раза, а HEAD это TAIL.LEAD(1) из того-же SETa, т.е. это просто следующий RunningTotal (S6) от уже рассчитанного Tail (S5)) то простейший вариант будет использовать опцию назначения alias-а на SET внутри контейнера [..member хх as ()..] что даст возможность повторного использования уже задекларированной сущности. хотя и придётся фиктивные операции писать, но учитывая что у тебя это всё ради одной меры - наверное стоит это всё закопать в один member .. хотя конечно и DDL и LD сразу можно интегрировать (т.к. обращение всего один раз) - но там прирост производительности несущественный...
ну и TAIL можно до кучи тоже через alias .. хотя наверное как опцию можно TAIL и один раз просчитать с обеими мерами (runningtotal & ddl) ..
код конечно нечитабельным станет - но производительность подскочит.. другие чисто алгебраические преобразования/сокращения тоже навряд-ли что-то весомое дадут (типа упрощения IIF(S6=S5,S3,(0.5*S2-S5)/(S6-S5)+S3) .. кстати в этой последней части спорная математика округления до десятичных от дня IIF ...(сколько не хватило / сколько лишних на след шаге)+полных дней (т.е. S3) .. в смысле при условных IIF эта дробь 50rub/100rub будет +0.5 дней, а 50rub/25rub получится +2 дня (или какие у тебя там периоды, +10 дней шаг)?
[Даты].[ГМД].CurrentMember в приведённом скрипте не задано, так понимаю это какой-то default типа сегодняшнего дня?

в общем как выше уже заметили от strtoset() можно избавится оставшись с одним generate()as CS .. и потом вызывать этот CS несколько раз (естественно всё в пределах одного member) в том числе и TAIL(filter(CS..),,) AS TS и HEAD(..)..
14 мар 16, 10:00    [18926842]     Ответить | Цитировать Сообщить модератору
 Re: MDX не для слабонервных  [new]
vikkiv
Member

Откуда: London / Zurich
Сообщений: 1141
точнее DDS и LN конечно-же
14 мар 16, 10:02    [18926846]     Ответить | Цитировать Сообщить модератору
 Re: MDX не для слабонервных  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 3535
Владимир Штепа
Лучший мдх это ненаписанный мдх
Если насчитать в хранилище или во вьюхе без особых тормозов при процессигнге куба не составляет особых затрат (времени/труда)
то надо делать там.

Если уж из любви к искусству (мдх) то для разтомаживания запроса надо избавиться от StrToХ и ХToStr, которые садят производительность


Если вы ОЛАПОом занимаетесь долго, то должны понимать, что добавление нового поля на уровне DSV повлечет за собой тяжёлый деплой. Например, у меня выкладка куба с полным процессингом сейчас растягивается на 12-14 часов.

При этом, в прошлом у меня был по крайней мере один проект, где куб вообще было трогать запрещено. И любые вопли о том, что без этого никак упирались в тупизм службы безопасности банка, которая поднимала вопрос, а "кто они таким и зачем их вообще сюда пустили".

При этом добавить вычисляемый показатель или вставить его в Excel гораздо проще. Поэтому уметь что-то считать на уровне MDX важно.

Итак, мы так и не ответила на вопрос, как считать какие-то показатели, не использую currentmember? Или какие есть альтернативы функции generate, позволяющие обходить скользящее окно быстрее? Потому что если развернуть полную матрицу в Excel, то расчёт произойдёт явно быстрее.
14 мар 16, 12:44    [18927605]     Ответить | Цитировать Сообщить модератору
 Re: MDX не для слабонервных  [new]
alexdr
Member

Откуда:
Сообщений: 200
Удивлен оборотом:
a_voronin
Если вы ОЛАПОом занимаетесь долго, то должны понимать

, обращенным к Владимиру. Как по мне, так им были высказаны более чем разумные соображения.
14 мар 16, 19:54    [18930400]     Ответить | Цитировать Сообщить модератору
 Re: MDX не для слабонервных  [new]
vikkiv
Member

Откуда: London / Zurich
Сообщений: 1141
гы, всё чаще замечаю на форумах при вполне доходчивой постановке вопроса поведение типа
---
В: как снять приборную панель на ВАЗ-2111
О: лучший вариант сменить машину на BMW-305
---
понятно конечно что все советы с наилучшими пожеланиями да от всей души и зачастую вполне разумная альтернатива....

с физическими мерами против расчётных и прочих MDX запросов.. у меня примерно с 30-40 производных расчётных показателей чисто на периодические подписки с рассылками раз в неделю или месяц, если каждый на физический формат переводить то это +4% к времени процессинга что в сумме +150% по временным затратам (дисковое место как-бы не существенно) т.е. с полутора часа время возрастёт до почти четырёх (это только на последних партициях естественно..) , если с SQL их считать на многомиллиардных объёмах - это по от 10 минут до полу часа минимум, MDX - до минуты..
так что тоже не всё так однозначно, правильно распределять что где делать тоже с умом подходить надо.. эффективный баланс под требования дело тоже тонкое.

по сабжу: дали-же уже совет - делай через внутренний alias на SET без всяких generate и вызывай его внутри member контейнера сколько угодно, вопрос решен, например (чисто для рассматриваемой методологии а не как считать среднее и долю):
with member total as sum([customer].[Country].[All],[Internet Sales Amount])
member avrg as  ---присвоение имени внутреннему SETу = b , и вызов этого SETа 2 раза, в SUM() и Count()
iif(isempty(nonempty(([Customer].[State-Province].children,[customer].[Country].currentmember,[Internet Sales Amount]))as b),
	null,sum(b,[Internet Sales Amount])/b.count),format="currency"
member share as  ---присвоение имени внутреннему SETу = b , и вызов этого SETа в SUM() в комбинации с другой расчётной мерой
iif(isempty(nonempty(([Customer].[State-Province].children,[customer].[Country].currentmember,[Internet Sales Amount]))as b),
	null,sum(b,[Internet Sales Amount])/total),format="percent"
select{[Internet Sales Amount],avrg,total,share}on 0,[customer].[Country].children on 1from[Adventure Works]
тоже самое присвоение внутреннего aliasa с [ TAIL(с вызовом b) as c ] и [ HEAD(с вызовом b) as c ]
с SETами этот трюк прокатывает, с мерами к сожалению вроде как нет
15 мар 16, 07:07    [18931510]     Ответить | Цитировать Сообщить модератору
 Re: MDX не для слабонервных  [new]
vikkiv
Member

Откуда: London / Zurich
Сообщений: 1141
т.е. в твоём случае можно предварительно определив SET а получив в итоге что-то типа
member[Дней до выкупа3] as....
--- begin member definition [Дней до выкупа3]
--- где-то в процессе опеределяем и именуем SET a
(StrToSet('[Даты].[ГМД].['+[Даты По заказам].[ГМД].CurrentMember.Member_name+']:
[Даты].[ГМД].['+[Даты По заказам].[ГМД].CurrentMember.Member_name+'].Lead(30)'),
[Даты].[ГМД].CurrentMember,[Даты По заказам].[ГМД].CurrentMember)as a
--- именуем SET b
TAIL(FILTER(a,RunningTotal/S2<0.5),1))as b
-- именуем SET c
HEAD(FILTER(a,RunningTotal/S2>=0.5))as c
--- начальный IIF
IIF([Даты По заказам].[ГМД].Level.Name<>'Дата'OR SUM(a,[Продажи по оплатам, руб])=0,NULL,
IIF(S6=S5,S3,(0.5*S2-S5)/(S6-S5)+S3))
--- конечный IIF
IIF([Даты По заказам].[ГМД].Level.Name<>'Дата'OR SUM(a,[Продажи по оплатам, руб])=0,NULL,
IIF(SUM(c,RunningTotal)=SUM(b,RunningTotal),
SUM(b,Datediff("d",[Даты По заказам].[ГМД].CurrentMember.Member_Value,[Даты].[ГМД].CurrentMember.Member_Value)),
(0.5*SUM(a,[Продажи по оплатам, руб])-SUM(b,RunningTotal))/(SUM(c,RunningTotal)-SUM(b,RunningTotal))+
SUM(b,Datediff("d",[Даты По заказам].[ГМД].CurrentMember.Member_Value,[Даты].[ГМД].CurrentMember.Member_Value))))
--- end member definition [Дней до выкупа3]
может конечно ещё с алгеброй что-то можно сократить/вынести/упростить, и часть присвоений имён конечно будет в самом IIF..
15 мар 16, 07:36    [18931525]     Ответить | Цитировать Сообщить модератору
Все форумы / OLAP и DWH Ответить