Добрый день, работаю на MS AS 2K

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

Есть куб, в котором хранятся дельты, расчет остатков выполняется следующим запросом:

with member [Measures].[Остатки] as
'Sum(nonemptycrossjoin(PeriodsToDate([Time].[Month].[(All)],
[Time].[Month].CurrentMember)), [Measures].[Остаток]) '

SELECT
crossjoin({[Time].[Month].[2005]}, {[Points].[зПТПД]}) ON
AXIS(1),
crossjoin({[SKU].[Весь ассортимент]}, {[Measures].[Остатки]}) ON
AXIS(0)
FROM Stock

Но запрос не очень подходит т.к. расчет остатков происходит по всем точкам которые посещались и не посещались, т.е. мы получаем накопительный остаток.

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

iif(IsLeaf([Points].Currentmember),

Tail (Filter(crossjoin({[Measures].[Stock]},
Descendants([Time].[Month].currentmember, [Time].[Month].Day)), (NOT
IsEmpty([Measures].[Stock]))), 1).item(0),

Sum(Descendants({[Points].Currentmember},[Points].[Point]), Tail
(Filter(crossjoin({[Measures].[Stock]},
Descendants([Time].[Month].currentmember, [Time].[Month].Day)), (NOT
IsEmpty([Measures].[Stock]))), 1).item(0)))

Но как Вы увидите из запроса он не оптимальный (выполняется не очень быстро) так как на каждом узле расчитываються суммы из остатков на конец периода.

Помогите, пожалуйста, написать правильный и быстрый запрос.

Например у нас всего 4 точек

На Неделе1 посещено: 4 точек все они входят в Город 1
При этом в
Точке 1 остаток на конец недели = 2
Точке 2 остаток на конец недели = 3
Точке 3 остаток на конец недели = 4
Точке 4 остаток на конец недели = 5

На Неделе2 посещено: 2 точки из 4
Точке 2 остаток на конец недели = 1
Точке 3 остаток на конец недели = 2

Например я хочу просмотреть какие остатки у меня по Городу 1 (т.е. по всем точкам входящим в него) на конец Недели1 в результате я должен получить 2+3+4+5=14

Например я хочу просмотреть какие остатки у меня по Городу 1 на конец Недели2 в результате должны получить сумму остатков только тех точек которые были посещены, т.е. Точка 2+Точка 3 = 1+2 =3

Именно так передо мной была поставлена задача считать остатки.

Остатки собираются каждый день.
Периоды по которым надо смотреть: Год, месяц, неделя, день.
Иерархия: Год - Месяц - День, Год - Неделя - День.

Остаток на конец месяца равен 2+1+2+5 = 10

Уважаемые проффесионалы, я очень расчитываю на Вашу помощь.

Дмитрий,спасибо за полезную информацию но к сожалению она не пременима к моей задаче.

Дмитрий, скажите пожалуйста чему будет равен остаток (необходимо учитывать остатки только последних посещений точек) используя приведенную Вами формулу на уровне Город1 за Неделю 2 в случае если

На Неделе2 посещено: 2 точки из 4
При первом визите остаток в Точке 2= 5
При втором визите остаток в Точке 2= 1

При первом визите остаток в Точке 3= 6
При втором визите остаток в Точке 3= 2

Заранее благодарен за ответ

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

Судя по ответам, я наверно попал в игнор лист

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

Я могу описать факт таблицу

КодТочки
Дата (День недели)
КодПродукта
Остаток

На основании этой таблицы построен куб.
Для решения моей задачи я использовал запрос

iif(IsLeaf([Points].Currentmember),

Tail (Filter(crossjoin({[Measures].[Stock]},
Descendants([Time].[Month].currentmember, [Time].[Month].Day)), (NOT
IsEmpty([Measures].[Stock]))), 1).item(0),

Sum(Descendants({[Points].Currentmember},[Points].[Point]), Tail
(Filter(crossjoin({[Measures].[Stock]},
Descendants([Time].[Month].currentmember, [Time].[Month].Day)), (NOT
IsEmpty([Measures].[Stock]))), 1).item(0)))

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

Господин backfire, спасибо за Ваш ответ, я с Вами согласен как вариант добавление нулей в точки которые не посещались можно рассматривать и согласен что это позволит использовать стандартные формулы расчетов остатков.

Но у данного подхода есть недостаток: на данный момент в факт таблице 500 000 записей, после добавление нулей к-во записей вырастит в
5 000 000, а соответственно размер куба также увеличится. Не подумайте, что я Вам морочу голову, но данный подход не очень подходит.

Скажите можно ли сделать так:

После расчета дельт, в стандартном случае мы используем следующее выражение для получение накопительных остатков

with member [Measures].[Остатки] as
'Sum(PeriodsToDate([Time].[Month].[(All)],
[Time].[Month].CurrentMember), [Measures].[Остаток]) '

Я же предлагаю поступить по другому, но к сожалению не знаю как это сделать, найти точки которые были посещены в указанном периоде и для них расчитывать остатки по указанной выше формуле
Да, но что делать в случае если анализировать в разрезе города? Если использовать Descendants, то тогда где?
И как тогда при таких расчетах должно выглядеть выражение "Sum(PeriodsToDate([Time].[Month].[(All)],
[Time].[Month].CurrentMember), [Measures].[Остаток])" ?

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

Дмитрий, извините но на форуме я что-то не нашел "более быстрая формула для остатков с ancestors". Если Вас не затруднит дайте пожалуйста ссылку

Формулу я нашел. Теперь у меня так же появился вопрос по поводу иерархий, учитывая то что у меня как раз иерархия (Год-Месяць-День; Год-Неделя-День)

Только я хотел уточнить формула с Ancestors или Ascendants?

Всем спасибо, к сожалению так и не удалось получить ответа на поставленный вопрос, кроме господина backfire, который предложил:

"А не проще ли писать в базу для непосещенных точек 0 и не морочить голову MDX-извращениями, а использовать хорошо обкатанные и известные формулы, которых на форуме, как гуталина на фабрике Матроскина - "ну просто завались"", к сожалению данный вариант не есть оптимальным с точки зрения к-ва данных.

Если все же интерес остался, давайте прододжим обсуждение.

Я достиг решения следующей формулой:

iif(IsLeaf([Points].Currentmember),

Tail (
Filter(
crossjoin(
{[Measures].[Stock]},
Descendants(
[Time]. [Month].currentmember, [Time].[Month].Day
)
)
, (NOT IsEmpty([Measures].[Stock]))
)
, 1).item(0),


Sum(
Tail (
Filter(
crossjoin(
{[Measures].[Stock]},
Descendants(
[Time]. [Month].currentmember, [Time].[Month].Day
)
)
, (NOT IsEmpty([Measures].[Stock]))
)
, 1).item(0)
)

В факт таблице хранятся просто остатки (дельты не расчитывались)

Основной недостаток формулы - длительность расчетов. Т.е. никаких сум от начала периода до текущего выбраного момента не используется,т.к. это дает накопительный остаток. А по условиям задачи необходимо выводить остаток по всем последним визитам за конкретный период времени без учета остатков из других периодов.

Дело в том что в факт таблице на каждый день хранятся только те точки которые посещались. Даже если в факт табицу ввести поле посещалась или нет, то это все равно не решит проблему в случае если мы захотим посмотреть срез для ГОРОДА в который входят точки посещаемые регулярно и которые перестали посещаться пол года назад.

Я честно говоря затрудняюсь модифицировать запрос

with member [Measures].[Остатки] as
'Sum(nonemptycrossjoin(PeriodsToDate([Time].[Month].[(All)],
[Time].[Month].CurrentMember)), [Measures].[Остаток]) '

SELECT
crossjoin({[Time].[Month].[2005].[Октябрь]}, {[Points].[Киев]}) ON
AXIS(1),
crossjoin({[SKU].[Весь ассортимент]}, {[Measures].[Остатки]}) ON
AXIS(0)
FROM Stock

так чтобы в данном случае остаток для города Киев считался только по тем точкам которые были посещены в октября месяце, даже если у меня будет измерение: Посещена, Непосещена.

[/quot]

А не проще ли писать в базу для непосещенных точек 0 и не морочить голову MDX-извращениями, а использовать хорошо обкатанные и известные формулы, которых на форуме, как гуталина на фабрике Матроскина - "ну просто завались"[/quot]

Вопрос к г-ну backfire
как добавить 0 для непосещенных точек при структуре факт таблицы

КодТочки
Дата (День недели)
КодПродукта
Остаток

в которой хранятся только посещенные точки на каждый день.

Спасибо, попробую.

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

Что-то оптимизировать не удается, Вы не могли бы привести пример запроса.

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

Что-то оптимизировать не удается, Вы не могли бы привести пример запроса.

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

Вопрос по поводу запроса к г-ну backfire по прежнему в силе.
Теперь по поводу LastChild: LastChild возможно будет использовать на уровне точек, на сколько я понимаю. А что делать на уровне Город, Регион? Ведь на этих уровнях должны считаться суммы из последних остатков точек входящих в этот регион, город.

Dmitry BiryukovSUM(Descendants(...),...)

Я пробовал таким образом считать, производительность реально очень низкая и к сожалению совершенно не удовлетворяет.

Г-н backfire с нетерпением жду от Вас пример запроса о котором я писал выше.

Дмитрий спасибо за совет. Но может Вы знаете еще какие-либо подходы?