Советы по повышению производительности для "лучших 5 Bs для каждого A" Запросы MDX? (Особенно SSAS)

Question

Типичные примеры MDX для "дайте мне 5 лучших B для каждого A" выглядят так:

-- return top 5 clients for each firm
select
  [Measures].[Amount] on columns,
  NON EMPTY generate(
    [Firms].[Firm Name].Children,
    crossjoin(
      [Firms].[Firm Name].CurrentMember,
      TopCount([Clients].[Client Name].Children, 5, [Measures].[Amount])
    )
  )
  on rows
from [FirmsAndClients]

Я создаю прототип пользовательского интерфейса, который выполняет большинство запросов типа "топ-5", поэтому я ищу какие-либо советы по их ускорению, особенно, когда перекрестное соединение (A, B) имеет в основном нулевые значения мер.

В этом конкретном случае Фирмы х Клиентов были большими (n=5000 x n=20000, более или менее) и разреженными, и я смог ускорить процесс примерно в 100 раз, заменив NON EMPTY фильтром (NOT ISEmpty) внутри перекрестка:

-- return top 5 clients for each firm
select
  [Measures].[Amount] on columns,
  generate(
    [Firms].[Firm Name].Children,
    crossjoin(
      [Firms].[Firm Name].CurrentMember,
      TopCount(
        filter([Clients].[Client Name].Children, NOT IsEmpty([Measures].[Amount]))
               5,
               [Measures].[Amount])
    )
  )
  on rows
from [FirmsAndClients]

Я надеялся получить дополнительное преимущество в производительности от предварительного прогрева кешей SSAS, выполняя похожие запросы, но обнаружил, что вышеупомянутый запрос (т. Е. Тот, который имеет "фильтр") одинаково медленен при работе с теплыми и холодными кэшами. Играя с Sql Profiler, я обнаружил одну из причин, почему это может быть так: хотя SSAS кэширует части данных базового куба, он, похоже, не кэширует результаты из запроса в целом и не кажется кэшировать промежуточные наборы, созданные с помощью Generate или Crossjoin. Таким образом, каждый раз, когда я повторяю запрос, приходится повторять генерацию, перекрестное соединение и верхний счет. И даже несмотря на то, что каждая верхняя учетная запись занимает 1 мс или меньше, эти миллисекунды складываются при итерации тысяч фирм.

Любые советы о том, что я могу сделать, на уровне MDX или уровне куба или уровне настройки Sql Server? В мире SQL я мог бы получить некоторое преимущество от создания индекса по столбцам, по которым я хотел отсортировать. Насколько я знаю, для OLAP ничего подобного нет.

Возможно, это более общий вопрос о том, что делать, если вы хотите ускорить запрос MDX, который включает в себя сложную сортировку и фильтрацию (которая, очевидно, не подходит на 100% кеширующей системе SSAS).

Answer 1

Я немного поиграл с SSAS 2008, и из своего опыта я разработал несколько принципов скорости для этих запросов:

• Фильтрация пустых кортежей может помочь, но это не всегда: как я упоминал в моем первоначальном посте, он может ускорить процесс на порядок, чтобы отфильтровать пустые кортежи до объединения двух разреженных наборов вместе, а не после. (Под "разреженным" я подразумеваю, когда вы пересекаете два, большинство значений меры равны нулю.) Если вы заранее знаете, что наборы MDX, которые вы пересекаете, не редки, тем не менее, оптимизация TopCount(NonEmpty) может замедлить процесс умеренно.
  • Примечание. Если вы используете пустую фильтрацию кортежей в качестве оптимизации производительности, лучшее место для фильтрации, по-видимому, до (то есть, вложенного внутрь) вызова TopCount.
  • Примечание. В своем исходном сообщении я использовал шаблон TopCount(Filter(... NOT IsEmpty(...))). Но шаблон, который может быть немного быстрее и кажется более совместимым с именованными наборами, - это использовать TopCount(NonEmpty(...), ....) вместо этого.
• Комбинация A) с использованием именованных наборов и B) с использованием [MySet].Current, а не [My Dimension].[My Hierarchy].CurrentMember в вызове Generate также, по-видимому, приводит к умеренному повышению производительности.
  • Примечание: ранее я сказал, что именованные наборы доставляют мне неприятности. Как только я переключился на NonEmpty() для фильтрации пустых кортежей, у меня не было проблем с ними.

Имея это в виду, вот как выглядят мои запросы сейчас.

WITH
SET [Set0] as [Firms].[Firm Name].Children
SET [Set1] as generate([Set0], crossjoin([Set0].Current, TopCount(NonEmpty([Clients].[Client Name].Children, [Measures].[Amount]), 5, [Measures].[Amount])))
SET [Set2] as generate([Set1], crossjoin([Set1].Current, TopCount([Time].[Year].Children, 5, [Measures].[Amount])))
SELECT
[Measures].[Amount] on columns,
NON EMPTY [Set2] on rows
FROM Lobbying2

В этом случае я использую NonEmpty для Set1, потому что Firms x Clients является разреженным, но я не использую NonEmpty для Set2, потому что FirmClientPairs x Time не является разреженным.

Я также заметил, что использование именованных наборов облегчает создание вложенных запросов типа "топ-пять-топ-пять-топ-пять...", что довольно забавно.

Answer 2

Как улучшить приведенную ниже производительность запроса MDX?

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

SELECT non empty([Measures].[HC_ALLOCATED_CNT_ASSIGNMENT]*[BI FCT PROJ ASSIGN].[ASSIGN CATEGORY 1].[ASSIGN CATEGORY 1].MEMBERS *[BI FCT PROJ ASSIGN].[ASSIGN CATEGORY 2].[ASSIGN CATEGORY 2].MEMBERS) ON AXIS(0),([BI DIM PROJECT].[PROJECT CODE].[PROJECT CODE].MEMBERS*[BI DIM PROJECT].[UNIT CODE].[UNIT CODE].MEMBERS*
        [BI DIM PROJECT].[SUBUNIT CODE].[SUBUNIT CODE].MEMBERS*
        [BI DIM PROJECT].[CURRENT PROJECT DESCRIPTION].[CURRENT PROJECT DESCRIPTION].MEMBERS*[BI DIM PROJECT].[CURRENT BILLING TYPE].[CURRENT BILLING TYPE].MEMBERS *[BI DIM PROJECT].[CURRENT MANAGED BY].[CURRENT MANAGED BY].MEMBERS *[BI DIM PROJECT].[CURRENT PROJECT OFFSHORE OU].[CURRENT PROJECT OFFSHORE OU].MEMBERS*[BI DIM PROJECT].[CURRENT ACCOUNT NAME].[CURRENT ACCOUNT NAME].MEMBERS*[BI DIM PROJECT].[CUSTOMER CODE].[CUSTOMER CODE].MEMBERS*     [BI DIM PROJECT].[CURRENT AD MAILID].[CURRENT AD MAILID].MEMBERS *[BI DIM PROJECT].[CURRENT AM MAILID].[CURRENT AM MAILID].MEMBERS *[BI DIM PROJECT].[CURRENT PD MAILID].[CURRENT PD MAILID].MEMBERS *[BI DIM PROJECT].[CURRENT PM MAILID].[CURRENT PM MAILID].MEMBERS *[BI DIM PROJECT].[CURRENT PROJECT STATUS].[CURRENT PROJECT STATUS].MEMBERS*[BI DIM ASSOCIATE].[FULLNAME].[FULLNAME])ON AXIS(1) FROM [CUBE_IAMIS]