Otimizar o Desempenho de Consultas da Base de Dados Multidimensional

Ao utilizar uma base de dados multidimensional como origem de dados no Oracle Analytics, poderão ocorrer problemas de desempenho que resultam na geração de consultas de MDX (Multidimensional Expression) não otimizadas.

Ao modificar o design, pode melhorar as consultas de MDX geradas pelo Oracle Analytics. Isto pode ter um enorme impacto, não apenas no desempenho do seu relatório, mas também no volume de recursos utilizados na base de dados. A forma como utiliza as funções suportadas ou não suportadas tem um grande impacto nas consultas de MDX geradas e, por conseguinte, no desempenho.

Como cada caso de utilização é exclusivo, a sua equipa de desenvolvimento deve rever as opções, analisar os diários de consultas do Oracle Analytics e selecionar a melhor solução para o seu caso de utilização.

Este tópico não aborda os problemas de desempenho causados pela sua infraestrutura, como redes, browsers ou apresentação de relatórios.

Metodologia

A Oracle recomenda que efetue as seguintes tarefas para aumentar o desempenho. É importante que compreenda a estrutura da consulta de MDX, bem como os diários de consultas que o Oracle Analytics gera.

  • Simplifique as consultas de MDX geradas.
  • Reduza o número de consultas de MDX geradas.
  • Certifique-se de que são aplicados filtros e seleções ideais na consulta de MDX.
  • Otimize o desempenho com o administrador da base de dados (DBA) na base de dados multidimensional e verifique porque é que a base de dados de origem continua a ter um desempenho fraco.
  • Modifique a análise com base no feedback do DBA.

Otimização dos Passos de Seleção

Ao otimizar os passos de seleção, pode simplificar as consultas de MDX, reduzir o número de consultas de MDX geradas e aumentar o desempenho.

A figura seguinte mostra um exemplo de uma comparação de passos de seleção otimizados e não otimizados.

Segue-se a descrição de GUID-43E6F348-B14C-40DC-8C21-DA34DAE44344-default.jpg
.jpg​

Instruções CASE

A funcionalidade da instrução CASE não é suportada nas consultas de MDX e deve ser sempre aplicada no Oracle Analytics. A lógica explicada nesta secção em relação às instruções CASE é válida para a maioria das funções que não são suportadas nas consultas de MDX (if null, etc.).

Existem vantagens e desvantagens na utilização de instruções CASE. Quando inclui instruções CASE em fórmulas de relatório, elas não são incluídas na consulta de MDX. Isto pode simplificar a consulta de MDX e melhorar o desempenho. No entanto, em contrapartida não é possível filtrar tão eficazmente, o que significa que a consulta poderá devolver mais registos do que o necessário.

Seguem-se as restrições à utilização da funcionalidade da instrução CASE:

  • Se a instrução CASE não combinar vários membros, a coluna base utilizada na instrução deve ser incluída na consulta e nas visualizações como uma coluna separada oculta.
  • Se a instrução CASE combinar vários membros, a coluna base não pode ser incluída na visualização sem afetar o nível de agregação. Se for este o caso:
    • Se a regra de agregação da medida não for Agregação Externa, a coluna base deve ser excluída da consulta.
    • Se a regra de agregação da medida for Agregação Externa, a coluna base deve ser incluída na consulta e excluída da visualização. Deve alterar a regra de agregação da medida do valor por omissão para uma regra de agregação interna simples (SUM, MAX, MIN). Isto só funciona se a regra de agregação interna for utilizada para combinar membros e fornecer resultados corretos.

Função FILTER

Ao contrário da funcionalidade da instrução CASE, a função FILTER pode ser enviada para a base de dados para execução.

A principal vantagem de utilizar a função FILTER em fórmulas de relatório é que a seleção é aplicada na consulta de MDX e o volume de dados calculados e obtidos da base de dados é reduzido.

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

Exemplo de CASE vs. FILTER

Neste exemplo, um utilizador pede um relatório que mostre o lucro por trimestre e a SKU do produto selecionada. Além disso, as SKUs são agrupadas em 12 categorias. A categoria Other Cola tem os seguintes produtos de LOB atribuídos: Cola, Diet Cola e Shared Diet Cola.

Segue-se a descrição de GUID-7198F143-54E6-4A48-9579-96624936A94D-default.jpg
.jpg​

Segue-se 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 existe nenhum agrupamento baseado na instrução CASE. É gerada uma consulta de MDX simples, 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 isto é visto pela definição da base de dados definida 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]

Em alternativa, pode utilizar um filtro em relação à métrica de lucro para obter apenas os membros LOB obrigatórios. Neste cenário, são criadas três métricas com os filtros correspondentes aplicados.

Segue-se 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 ocorrem 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 pretender agrupar os produtos em diferentes categorias, utilize uma instrução CASE. Neste cenário, é gerada uma única consulta de MDX com as linhas filtradas e, apesar de a instrução CASE ser aplicada pelo Oracle Analytics, é utilizado o subconjunto de dados e não todos os registos.

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

Um programador aplica uma instrução CASE para renomear marcas e um prompt do dashboard permite que os utilizadores selecionem a marca.

Segue-se a descrição de GUID-56356AA9-2AF6-4A67-8ADD-FC4F7F70306C-default.jpg
.jpg​

Segue-se a descrição de GUID-E63719C8-9936-412B-8228-F20E8F048C46-default.jpg
.jpg​

Como a instrução CASE não é suportada no MDX, o filtro em Brand2 não pode ser aplicado na consulta de MDX. Todas as marcas são selecionadas, sem qualquer otimização.

Segue-se a descrição de GUID-6BE1F274-8257-4E31-8D42-406357A07B2A-default.jpg
.jpg​

Neste tipo de cenário, a Oracle recomenda que retire a instrução CASE e que renomeie os membros na base de dados ou crie pseudónimos.