Запрос MDX для линейного графика PerformancePoint

Question

У меня проблемы с получением правильной линейной диаграммы в PerformancePoint с помощью Dashboard Designer на основе пользовательского запроса MDX.

Мы пытаемся отобразить последние 6 месяцев текущего года по сравнению с тем же 6-месячным периодом прошлого года. Я написал запрос, и он возвращает данные, которые мы ожидаем.

SELECT  NONEMPTY([Invoice Date].[Fiscal Week of Year].[Fiscal Week of Year])
                 ON COLUMNS
        ,NONEMPTY({[Invoice Date].[Hierarchy].[Fiscal Year] 
                        * [Measures].[Amount]})  ON ROWS
FROM    [Sales]

Это возвращает правильные данные.

Мы создаем линейную диаграмму для отображения данных. Мы получаем [Сумма] (в долларах) по оси Y и Неделю года. Неделя года - это, по сути, просто числа, поэтому номер текущей недели равен 22 (потому что мы используем не календарный финансовый год). Затем у нас есть временной ряд на основе года, который позволяет нам просматривать номера 22-й недели этого года по сравнению с 22-й неделей предыдущего года.

Наша проблема в том, что последние 6 месяцев пересекают "границу" года. И из-за этого наша ось X не имеет "правильного" хронологического вида. Наша нижняя ось выглядит так (я упростила количество недель):

1 | 2 | 3 |... | 20 | 21 | 22 | 48 | 49 | 50 | 51 | 52 |

Обратите внимание на скачок между 22 и 48. Это численно правильно, однако хронологически этот график должен быть:

48 | 49 | 50 | 51 | 52 | 1 | 2 | 3 |... | 20 | 21 | 22 |

Я знаю, почему это происходит. Финансовая неделя года не является частью нашей иерархии [Дата выставления счета].

Нашей иерархией был финансовый год => Финансовый квартал => Финансовый месяц => Финансовая дата

Итак, мы исправили это и добавили неделю в иерархию

Финансовый год => Финансовый квартал => Финансовый месяц => Финансовая неделя года => Финансовая дата

И новый запрос MDX выглядит так:

SELECT  NONEMPTY([Invoice Date].[Hierarchy].[Fiscal Week of Year])
                 ON COLUMNS
        ,NONEMPTY({[Invoice Date].[Fiscal Year].[Fiscal Year] 
                        * [Measures].[Amount]})  ON ROWS
FROM    [Sales]

Что ж, это решило нашу проблему сортировки, потому что теперь Неделя года имеет контекст относительно того, к какому году относится Неделя 22... но это представляет другую проблему

Из-за этого нового контекста наш график теперь не будет перекрывать два разных временных ряда и вместо этого будет представлять их в виде спины, потому что они больше не разделяют простое число, у них теперь есть год + неделя (даже если на диаграмме только отображаются номер недели)

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

48 | 49 | 50 | 51 | 52 | 1 | 2 | 3 | ... | 20 | 21 | 22 |48 | 49 | 50 | 51 | 52 | 1 | 2 | 3 | ... | 20 | 21 | 22 |

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

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

Я хочу, чтобы мы получили правильную сортировку, поэтому:

48 | 49 | 50 | 51 | 52 | 1 | 2 | 3 |... | 20 | 21 | 22 |

Но все же быть в состоянии иметь два разных года пересечения друг с другом.

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

Answer 1

Хорошо, у меня есть ответ на вопрос. Следующий запрос MDX вернет значение [Сумма] с финансовыми годами в строках, и в каждом столбце будет указана неделя года. Неделя года будет "отсортирована" по тому, как недели происходят в хронологическом порядке. Это действительно ужасное описание, лучше увидеть его.

WITH SET [Weeks] AS
EXTRACT([Invoice Date].[Dashboard Hierarchy].[Fiscal Week of Year], [Invoice Date].[Dashboard Hierarchy])
SET [Week of Year] AS
INTERSECT(STRTOSET("{" + GENERATE(  [Weeks]
                    ,"[Invoice Date].[Fiscal Week Of Year].&[" + [Weeks].CURRENT.NAME + "]"
                    ,",") + "}"), [Invoice Date].[Fiscal Week Of Year].MEMBERS)
SELECT  [Week of Year] ON COLUMNS
    ,{([Invoice Date].[Fiscal Year].[Fiscal Year], [Measures].[Amount])}   ON     ROWS
FROM    [Sales]

Когда вы выполните запрос, вы получите следующие результаты:

Обратите внимание, что столбцы 51, 52, 53, 1, 2, 3, что соответствует диапазону данных, которые я выбрал (от 6 месяцев до даты). Так, в моем примере 6 месяцев назад была 51-я неделя этого года.

Также обратите внимание, что есть данные, которые перекрываются на 51 неделе как для 2012, так и для 2013. Это важно, если вы хотите, чтобы линейный график в содержимом PerformancePoint фактически сравнивал эти точки данных. Вот итоговый график.

Вот что делает запрос

WITH SET [Weeks] AS
EXTRACT([Invoice Date].[Dashboard Hierarchy].[Fiscal Week of Year], [Invoice Date].[Dashboard Hierarchy])

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

INTERSECT(STRTOSET("{" + GENERATE(  [Weeks]
                ,"[Invoice Date].[Fiscal Week Of Year].&[" + [Weeks].CURRENT.NAME + "]"
                ,",") + "}"), [Invoice Date].[Fiscal Week Of Year].MEMBERS)

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

Функция Generate генерирует разделенную запятыми строку на основе ранее созданного вычисляемого члена Weeks. Эта строка фактически создается так, чтобы выглядеть как элемент [Fiscal Week of Year], который содержит только недели и не является частью иерархии дат. Это важно, потому что вы не можете вернуть недели как часть иерархии в линейный график, или это не позволит 51-й неделе, например, с 2012 по 2013 годы перекрываться, потому что они разные участники.

Затем новая строка, разделенная запятыми, преобразуется в набор с помощью функции StrToSet.

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

Новый вычисляемый член Week of Year затем возвращается НА КОЛОННЫ.

Я всегда открыт для предложений о том, как выполнить то же самое, но проще, потому что кажется, что я создал запрос Rube Goldberg MDX, но я просто не смог найти ни одного примера во всех интернетах о том, как выполнить это.

Answer 2

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

Первое решение будет работать следующим образом (я использовал куб Microsoft Adventure Works, а с ноября 2007 г. по февраль 2008 г.):

SELECT Extract(
      (
       [Date].[Calendar].[Month].&[2007]&[11] : [Date].[Calendar].[Month].&[2008]&[2]
      )
      * [Date].[Calendar Week of Year].[Calendar Week of Year].members
      , [Date].[Calendar Week of Year])
      on COLUMNS,
      [Date].[Calendar].[Calendar Year].Members
      on ROWS
FROM [Adventure Works]
WHERE [Measures].[Internet Sales Amount]

Второй будет работать следующим образом:

WITH Member [Measures].[PrevYearAmount] as
     (
     ParallelPeriod([Date].[Calendar Weeks].[Calendar Year], 1, [Date].[Calendar Weeks].CurrentMember),
     [Measures].[Internet Sales Amount]
     )
SELECT [Date].[Calendar Weeks].[Calendar Week].&[44]&[2007] : [Date].[Calendar Weeks].[Calendar Week].&[10]&[2008]
      on COLUMNS,
      {
      [Measures].[Internet Sales Amount],
      [Measures].[PrevYearAmount]
      }
      on ROWS
FROM [Adventure Works]

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