Ajustar Desempenho de Consultas ao Banco de Dados Multidimensional

Ao usar um banco de dados multidimensional como origem de dados no Oracle Analytics, você pode ter problemas de desempenho que resultam na geração de consultas MDX (multidimensional expression) não ideais.

Modificando o design, você pode melhorar as consultas MDX geradas pelo Oracle Analytics. Isso pode ter um grande impacto, não apenas no desempenho do seu relatório, mas também no volume de recursos usados no banco de dados. A maneira como você usa funções com ou sem suporte tem grande impacto nas consultas MDX geradas e, portanto, no desempenho.

Como cada caso de uso é único, sua equipe de desenvolvimento deve verificar as opções, analisar os logs de consulta do Oracle Analytics e selecionar a melhor solução para seu caso de uso.

Este tópico não trata de problemas de desempenho causadas por sua infraestrutura, como redes, browsers ou apresentação de relatório.

Metodologia

A Oracle recomenda que você execute as seguintes tarefas para aumentar o desempenho. É importante que você entenda a estrutura da consulta MDX, bem como os logs de consulta gerados pelo Oracle Analytics.

  • Simplifique as consultas MDX geradas.
  • Reduza o número de consultas MDX geradas.
  • Certifique-se de que os filtros e seleções ideais sejam aplicados na consulta MDX.
  • Ajuste o desempenho com o DBA (administrador do banco de dados) no banco de dados multidimensional e verifique se o banco de dados de origem ainda está com o desempenho ruim.
  • Modifique a análise com base no feedback do DBA.

Otimização de Etapas de Seleção

Ao otimizar etapas de seleção, você pode simplificar as consultas MDX, reduzir o número de consultas MDX geradas e melhorar o desempenho.

A figura a seguir mostra um exemplo de comparação de etapas de seleção otimizadas e não otimizadas.

Descrição de GUID-43E6F348-B14C-40DC-8C21-DA34DAE44344-default.jpg segue
.jpg​

Instruções CASE

Não há suporte para a funcionalidade da instrução CASE em consultas MDX e ela deve sempre ser aplicada no Oracle Analytics. A lógica explicada nesta seção com relação às instruções CASE é válida para a maioria das funções para as quais não há suporte na MDX (if null etc.).

Há vantagens e desvantagens ao usar instruções CASE. Quando você inclui instruções CASE em fórmulas de relatório, elas não são incluídas na consulta MDX. Essa opção pode simplificar a consulta MDX e melhorar o desempenho. Porém, a compensação é que você não pode filtrar com a mesma eficácia, o que significa que a consulta pode retornar mais registros do que o necessário.

Veja a seguir as restrições de uso da funcionalidade de instrução CASE:

  • Se a instrução CASE não combinar vários membros, a coluna de base usada na instrução deverá ser incluída na consulta e nas views como uma coluna separada oculta.
  • Se a instrução CASE combinar vários membros, a coluna de base não poderá ser incluída na view sem impactar o nível de agregação. Se este for o caso:
    • Se a regra de agregação da medida não for Agregação Externa, a coluna de base deverá ser excluída da consulta.
    • Se a regra de medida da agregação for Agregação Externa, a coluna de base deverá ser incluída na consulta e excluída da view. Você deverá alterar a regra de agregação do padrão para uma regra de agregação interna simples (SUM, MAX, MIN). Isso só vai funcionar se a regra de agregação interna for usada para combinar membros, e produzirá resultados corretos.

Função FILTER

Diferentemente da funcionalidade de instrução CASE, a função FILTER pode ser enviada ao banco de dados para execução.

O principal benefício de usar a função FILTER nas fórmulas de relatório é que a seleção é aplicada na consulta MDX e o volume de dados calculados e recuperados do banco de dados é reduzido.

A principal desvantagem de usar a função FILTER é que ela pode aumentar o número de consultas MDX executadas. Por padrão, uma consulta é executada para cada função FILTER usada.

Exemplo de CASE versus FILTER

Neste exemplo, um usuário solicita um relatório que mostra o lucro por trimestre e a SKU do produto selecionado. Além disso, as SKUs são agrupadas em 12 categorias. A categoria Outros Refrigerantes Cola tem os seguintes produtos LOB designados: Cola, Diet Cola e Shared Diet Cola.

Descrição de GUID-7198F143-54E6-4A48-9579-96624936A94D-default.jpg segue
.jpg​

Eis aqui a consulta lógica da instrução 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

Não há agrupamento baseado na instrução CASE. Uma consulta MDX simples é gerada, com a instrução CASE processada pelo 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]

A instrução CASE é executada no BI Server e isso é visto pela definição do banco de dados configurada como 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]

Alternativamente, você pode usar um filtro com base na métrica de lucro para recuperar apenas os membros LOB necessários. Neste cenário, você cria três métricas com os filtros correspondentes aplicados.

Eis aqui a consulta lógica da instrução 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

Neste cenário, são geradas três consultas, uma para cada filtro, e você tem problemas de desempenho.

Consulta 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]

]]

Consulta 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]

]]

Consulta 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]

Exemplo de Filtro de Produto Aplicado

Uma abordagem melhor é incluir a coluna do produto no relatório com uma única coluna de medida sem um filtro. Em seguida, crie um filtro que inclua os produtos necessários. Se você quiser agrupar os produtos em categorias diferentes, use uma instrução CASE. Neste cenário, uma única consulta MDX é gerada com as linhas filtradas e, embora a instrução CASE seja aplicada pelo Oracle Analytics, ela usa o subconjunto de dados e não todos os registros.

Este é outro cenário em que as instruções CASE causam problemas de desempenho.

Um desenvolvedor aplica uma instrução CASE para renomear marcas e um prompt do painel de controle permite que os usuários selecionem a marca.

Descrição de GUID-56356AA9-2AF6-4A67-8ADD-FC4F7F70306C-default.jpg segue
.jpg​

Descrição de GUID-E63719C8-9936-412B-8228-F20E8F048C46-default.jpg segue
.jpg​

Como não há suporte para a instrução CASE no MDX, o filtro em Brand2 não pode ser aplicado na consulta MDX. Todas as marcas são selecionadas, e isso não é otimizado.

Descrição de GUID-6BE1F274-8257-4E31-8D42-406357A07B2A-default.jpg segue
.jpg​

Nesse tipo de cenário, a Oracle recomenda que você remova a instrução CASE e renomeie os membros no banco de dados ou crie aliases.