Настройка производительности запросов многомерных баз данных

При использовании многомерной базы данных в качестве источника данных можно столкнуться с проблемами производительности в Oracle Analytics, из-за которых возникают неоптимальные запросы, сгенерированные многомерным выражением.

Изменив макет, можно улучшить запросы многомерного выражения, которые создает Oracle Analytics. Это может существенно повлиять не только на производительность отчетов, но и на объем ресурсов, которые использует база данных. Способы использования поддерживаемых или неподдерживаемых функций существенно влияют на генерируемые запросы многомерного выражения и, как следствие, производительность.

Выбор лучшего решения для конкретного случая – задача команды разработчиков, которая должна рассмотреть все варианты и проанализировать журналы запросов Oracle Analytics.

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

Методология

Oracle рекомендует выполнить следующие задачи для повышения производительности. Важно понимать структуру запросов многомерного выражения, а также журналов запросов, которые генерирует Oracle Analytics.

  • Упрощайте создаваемые запросы многомерных выражений.
  • Сократите число создаваемых запросов многомерного выражения.
  • Убедитесь, что в запросе многомерного выражения применены оптимальные фильтры и выбранные элементы.
  • Настройте производительность вместе с администратором базы данных (DBA) на стороне многомерной базы данных и выясните, почему исходная база данных по-прежнему работает плохо.
  • Измените анализ, основываясь на обратной связи администратора базы данных.

Оптимизация этапов выбора

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

На следующей иллюстрации показан пример сравнения оптимизированных и неоптимизированных этапов выбора.

Описание GUID-43E6F348-B14C-40DC-8C21-DA34DAE44344-default.jpg ниже
.jpg​

Операторы CASE

Функциональность инструкции CASE не поддерживается в запросах многомерного выражения и всегда должна применяться в Oracle Analytics. Логика, описанная в этом разделе в отношении инструкций CASE, действительна для большинства функций, которые не поддерживаются в запросах многомерного выражения (if null и т. д.).

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

Ограничения на использование функциональности инструкции CASE:

  • Если инструкция CASE не объединяет несколько элементов, базовый столбец, используемый в инструкции, должен быть включен в запрос и представления как отдельный скрытый столбец.
  • Если инструкция CASE объединяет несколько элементов, базовый столбец невозможно включить в представление без оказания влияния на уровень агрегирования. Если это такой случай:
    • Если правилом агрегирования показателя не является Внешнее агрегирование, базовый столбец следует исключить из запроса.
    • Если правилом агрегирования показателя является Внешнее агрегирование, базовый столбец следует включить в запрос и исключить из представления. Следует изменить правило агрегирования показателя с установленного по умолчанию на простое правило внутреннего агрегирования (SUM, MAX, MIN). Это работает только в том случае, если для объединения элементов используется правило внутреннего агрегирования, которое обеспечивает правильные результаты.

Функция FILTER

В отличие от функциональности инструкции CASE функцию FILTER можно отправить в базу данных для выполнения.

Основное преимущество использования функции FILTER в формулах отчета заключается в том, что выборка применяется в запросе многомерного выражения и объем данных, вычисляемых и извлекаемых из базы данных, сокращается.

Основным недостатком использования функции FILTER является то, что она может увеличить количество выполняемых запросов многомерного выражения. По умолчанию для каждой используемой функции FILTER выполняется один запрос.

Пример сравнения CASE и FILTER

В этом примере пользователь запрашивает отчет, который показывает прибыль по кварталам и складские единицы выбранных продуктов. Кроме того, складские единицы сгруппированы в 12 категорий. К категории "Другая кола" относятся следующие продукты: "Кола", "Диетическая кола" и "Общая диетическая кола".

Описание GUID-7198F143-54E6-4A48-9579-96624936A94D-default.jpg ниже
.jpg​

Вот логические запросы инструкции CASE:

SELECT

   0 s_0,

   CASE when XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" in ('Cola','Diet Cola','Shared Diet Cola') THEN 'Other Cola' ELSE XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" END s_1,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Product"."Category") s_2,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU") s_3,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Year"."Quarter") s_4,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Product"."Category") s_5,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU") s_6,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Year"."Quarter") s_7,

   XSA('Admin'.'Sample.BasicPM')."Product"."Category" s_8,

   XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" s_9,

   XSA('Admin'.'Sample.BasicPM')."Year"."Quarter" s_10,

   XSA('Admin'.'Sample.BasicPM')."Basic"."Profit" s_11

FROM XSA('Admin'.'Sample.BasicPM')

ORDER BY 8 ASC NULLS LAST, 11 ASC NULLS LAST, 5 ASC NULLS LAST, 2 ASC NULLS LAST, 7 ASC NULLS LAST, 10 ASC NULLS LAST, 4 ASC NULLS LAST, 6 ASC NULLS LAST, 9 ASC NULLS LAST, 3 ASC NULLS LAST

FETCH FIRST 125001 ROWS ONLY

Группировка на основании инструкции CASE отсутствует. Создается простой запрос многомерного выражения, в котором инструкция CASE обрабатывается в Oracle Analytics:

With 
  set [_Product3]  as 'Descendants([Product], [Product].Generations(3), leaves)'
  set [_Year2]  as 'Descendants([Year], [Year].Generations(2), leaves)'
select 
  { [Measures].[Profit]
  } on columns,
  NON EMPTY {crossjoin({[_Year2]},{[_Product3]})} properties GEN_NUMBER, [Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor], [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows 
from [Sample.Basic]

Инструкция CASE выполняется на сервере BI Server. Это видно по настройке базы данных database 0:0,0:

 RqList <<11777451>> [for database 0:0,0]
                            D1.c6 as c6 [for database 0:0,0],
                            D1.c4 as c4 [for database 0:0,0],
                            case  when D1.c7 in ([ 'Cola', 'Diet Cola', 'Shared Diet Cola'] ) then 'Other Cola' else D1.c7 end  as c2 [for database 0:0,0],
                            D1.c5 as c5 [for database 0:0,0],
                            D1.c3 as c3 [for database 0:0,0],
                            D1.c1 as c1 [for database 0:0,0],
                            D1.c7 as c7 [for database 0:0,0],
                            D1.c8 as c8 [for database 0:0,0]

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

Вот логические запросы инструкции FILTER:

SELECT

   0 s_0,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Product"."Category") s_1,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU") s_2,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Year"."Quarter") s_3,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Product"."Category") s_4,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU") s_5,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Year"."Quarter") s_6,

   XSA('Admin'.'Sample.BasicPM')."Product"."Category" s_7,

   XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" s_8,

   XSA('Admin'.'Sample.BasicPM')."Year"."Quarter" s_9,

   FILTER(XSA('Admin'.'Sample.BasicPM')."Basic"."Profit" USING XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" in ('Cola','Diet Cola','Shared Diet Cola')) s_10,

   FILTER(XSA('Admin'.'Sample.BasicPM')."Basic"."Profit" USING XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" in ('Sasprilla','Birch Beer','Dark Cream')) s_11,

   FILTER(XSA('Admin'.'Sample.BasicPM')."Basic"."Profit" USING XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" in ('xxxxx')) s_12

FROM XSA('Admin'.'Sample.BasicPM')

ORDER BY 7 ASC NULLS LAST, 10 ASC NULLS LAST, 4 ASC NULLS LAST, 6 ASC NULLS LAST, 9 ASC NULLS LAST, 3 ASC NULLS LAST, 5 ASC NULLS LAST, 8 ASC NULLS LAST, 2 ASC NULLS LAST

FETCH FIRST 125001 ROWS ONLY

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

Запрос 1:

With

  set [_Product3]  as 'Filter([Product].Generations(3).members, ((IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "xxxxx")))'

  set [_Year2]  as 'Descendants([Year], [Year].Generations(2), leaves)'

select

  { [Measures].[Profit]

  } on columns,

  NON EMPTY {crossjoin({[_Year2]},{[_Product3]})} properties MEMBER_NAME, GEN_NUMBER, property_expr([Product], [MEMBER_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category_Null_Alias_Replacement"), property_expr([Product], [Default], Ancestor(currentaxismember(), [Product].Generations(2)), "Category"), property_expr([Product], [MEMBER_UNIQUE_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Member Key"), property_expr([Product], [Memnor], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Memnor"), [Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor], [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows

from [Sample.Basic]

]]

Запрос 2:

With

  set [_Product3]  as 'Filter([Product].Generations(3).members, ((IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Birch Beer") OR (IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Dark Cream") OR (IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Sasprilla")))'

  set [_Year2]  as 'Descendants([Year], [Year].Generations(2), leaves)'

select

  { [Measures].[Profit]

  } on columns,

  NON EMPTY {crossjoin({[_Year2]},{[_Product3]})} properties MEMBER_NAME, GEN_NUMBER, property_expr([Product], [MEMBER_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category_Null_Alias_Replacement"), property_expr([Product], [Default], Ancestor(currentaxismember(), [Product].Generations(2)), "Category"), property_expr([Product], [MEMBER_UNIQUE_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Member Key"), property_expr([Product], [Memnor], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Memnor"), [Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor], [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows

from [Sample.Basic]

]]

Запрос 3:

With

  set [_Product3]  as 'Filter([Product].Generations(3).members, ((IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Cola") OR (IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Diet Cola") OR (IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Shared Diet Cola")))'

  set [_Year2]  as 'Descendants([Year], [Year].Generations(2), leaves)'

select

  { [Measures].[Profit]

  } on columns,

  NON EMPTY {crossjoin({[_Year2]},{[_Product3]})} properties MEMBER_NAME, GEN_NUMBER, property_expr([Product], [MEMBER_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category_Null_Alias_Replacement"), property_expr([Product], [Default], Ancestor(currentaxismember(), [Product].Generations(2)), "Category"), property_expr([Product], [MEMBER_UNIQUE_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Member Key"), property_expr([Product], [Memnor], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Memnor"), [Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor], [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows

from [Sample.Basic]

Пример с применением фильтра продукта

Более эффективным подходом является включение столбца продукта в отчет с одним столбцом показателей без фильтра. Затем создайте фильтр, который включает требуемые продукты. Если необходимо сгруппировать продукты по разным категориям, используйте инструкцию CASE. В этом сценарии будет создан один запрос многомерного выражения с отфильтрованными строками, и хотя в Oracle Analytics применяется инструкция CASE, будет использоваться подмножество данных, а не все записи.

Вот еще один сценарий, в котором инструкции CASE вызывают проблемы с производительностью.

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

Описание GUID-56356AA9-2AF6-4A67-8ADD-FC4F7F70306C-default.jpg ниже
.jpg​

Описание GUID-E63719C8-9936-412B-8228-F20E8F048C46-default.jpg ниже
.jpg​

Поскольку инструкция CASE не поддерживается в многомерном выражении, фильтр по Brand2 невозможно применить в запросе многомерного выражения. Выбираются все бренды, и это не оптимизируется.

Описание GUID-6BE1F274-8257-4E31-8D42-406357A07B2A-default.jpg ниже
.jpg​

В этом типе сценария Oracle рекомендует удалить инструкцию CASE и переименовать компоненты в базе данных или создать псевдонимы.