Otimizar o Desempenho de Consultas da Base de Dados Multidimensional

Quando o Oracle Analytics utiliza uma base de dados multidimensional como origem de dados, existem considerações adicionais ao nível do design que podem ter um grande impacto no desempenho.

É importante compreender que as soluções de design para melhorar o desempenho variam consoante o caso de utilização. Este tópico não irá fornecer os procedimentos recomendados ou uma solução uniformizada que deva ser sempre aplicada. Em vez disso, oferecemos métodos e técnicas de otimização para o ajudar a aumentar o desempenho das suas análises e do código gerado.

Cabe à equipa de desenvolvimento 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

Recomendamos que conclua as seguintes tarefas para aumentar o desempenho. Como pré-requisito para estas tarefas, é importante compreender a estrutura da consulta de MDX (Multidimensional Expression), bem como os diários de consultas que o Oracle Analytics gera. Seguem-se as tarefas principais:

  • Simplificar o MDX gerado.
  • Reduzir o número de consultas de MDX geradas.
  • Assegurar que são aplicados filtros e seleções ideais no MDX.
  • Otimizar o desempenho com o administrador da base de dados (DBA) no lado da base de dados multidimensional e verificar porque é que a base de dados de origem continua a ter um desempenho fraco.
  • Modificar 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.

Segue-se um exemplo:

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 no MDX e deve ser sempre aplicada no Oracle Analytics. A lógica explicada abaixo em relação às instruções CASE é válida para a maioria das funções que não são suportadas no 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 no 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.

Como pode ver, cada caso de utilização é único. O objetivo principal é simplificar as consultas de MDX e, ao mesmo tempo, aplicar filtros e seleções ideais.

Existem restrições para utilizar a 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 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.

Lembre-se de que cada caso de utilização é único. O objetivo é simplificar as consultas de MDX e, ao mesmo tempo, aplicar filtros e seleções ideais.

Cenário de CASE versus FILTER

Continuemos a observar o mesmo cenário com os resultados da utilização da funcionalidade CASE versus FILTER.

O 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 doze 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 agrupamento com base na instrução CASE, é gerado um MDX simples, com a instrução CASE processada pelo Oracle Analytics. Segue-se o MDX gerado:

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]

Cenário da Instrução Filter

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

Cenário 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, pode ser utilizada uma instrução CASE. Neste cenário, será gerada uma única consulta de MDX com as linhas filtradas e, apesar de a instrução CASE ser aplicada pelo Oracle Analytics, será utilizado o subconjunto de dados e não todos os registos.

Vejamos 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 cenário, recomendamos que retire a instrução CASE e que renomeie os membros na base de dados ou crie pseudónimos.

Ao utilizar uma base de dados multidimensional como origem de dados, poderão ocorrer problemas de desempenho no Oracle Analytics que resultam em consultas geradas de MDX 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. Tenha cuidado com a forma como utiliza as funções suportadas ou não suportadas no MDX, uma vez que isto tem um grande impacto nas consultas de MDX geradas e no desempenho.