Ajuste del rendimiento de consultas de base de datos multidimensional

Cuando Oracle Analytics utiliza una base de datos multidimensional como origen de datos, existen consideraciones de diseño adicionales que pueden tener un gran impacto en el rendimiento.

Es importante comprender que las soluciones de diseño de mejora del rendimiento varían en función del caso de uso. En este tema no se proporcionarán mejores prácticas ni una solución definitiva que deba aplicarse siempre. En su lugar, se ofrecen métodos de ajuste y técnicas para ayudarle a aumentar el rendimiento de los análisis y el código generado.

Es decisión del equipo de rendimiento revisar las opciones, analizar los logs de consultas de Oracle Analytics y seleccionar la mejor solución para su caso de uso.

En este tema no se abordan las incidencias de rendimiento causadas por la infraestructura, como redes, exploradores o presentación de informes.

Metodología

Le recomendamos que complete las siguientes tareas para aumentar el rendimiento. Como requisito para estas tareas, es importante comprender la estructura de consulta de expresión multidimensional (MDX), así como los logs de consulta que genera Oracle Analytics. Estas son las tareas principales:

  • Simplificar la MDX generada.
  • Reducir el número de consultas MDX generadas.
  • Garantizar que se apliquen filtros y selecciones óptimos en la MDX.
  • Realizar un ajuste del rendimiento con el administrador de base de datos (DBA) en la base de datos multidimensional y verificar por qué la base de datos de origen sigue teniendo un rendimiento deficiente.
  • Modificar el análisis basándose en los comentarios del DBA.

Optimización de los pasos de selección

Mediante la optimización de los pasos de selección, puede simplificar las consultas MDX, reducir el número de consultas MDX generadas y aumentar el rendimiento.

A continuación se incluye un ejemplo:

A continuación se muestra la descripción de GUID-43E6F348-B14C-40DC-8C21-DA34DAE44344-default.jpg
.jpg​

Sentencias CASE

La funcionalidad de la sentencia CASE no está soportada en MDX y se debe aplicar siempre en Oracle Analytics. La lógica que se explica a continuación en relación con la sentencia CASE es válida para la mayoría de las funciones que no están soportadas en MDX (if null, etc.).

Hay ventajas e inconvenientes al utilizar sentencias CASE. Cuando incluye sentencias CASE en fórmulas de informe, estas no se incluyen en la MDX. Esto puede simplificar la consulta MDX y mejorar el rendimiento. Sin embargo, la contrapartida es que no puede filtrar de forma tan efectiva, lo que significa que la consulta podría devolver más registros de lo necesario.

Como puede ver, cada caso de uso es único. El objetivo clave es simplificar las consultas MDX y, al mismo tiempo, aplicar filtros y selecciones óptimos.

Existen restricciones para el uso de la funcionalidad de la sentencia CASE:

  • Si la sentencia CASE no combina varios miembros, la columna base utilizada en la sentencia se debe incluir en la consulta y en las vistas como una columna independiente oculta.
  • Si la sentencia CASE combina varios miembros, la columna de base no se puede incluir en la vista sin que afecte al nivel de agregación. En este caso:
    • Si la regla de agregación de medida no es Agregación externa, la columna de base se debe excluir de la consulta.
    • Si la regla de agregación de medida es Agregación externa, la columna de base se debe incluir en la consulta y excluir de la vista. Debe cambiar la regla de agregación de medida del valor por defecto a una regla de agregación interna simple (SUM, MAX, MIN). Esto solo funciona si la regla de agregación interna se utiliza para combinar miembros y proporciona resultados correctos.

Función FILTER

A diferencia de la sentencia CASE, la función FILTER se puede enviar a la base de datos para su ejecución.

  • La principal ventaja de utilizar la función FILTER en fórmulas de informe es que la selección se aplica en la consulta MDX y se reduce el volumen de datos calculados y recuperados de la base de datos.
  • La principal desventaja de utilizar la función FILTER es que puede aumentar el número de consultas MDX ejecutadas. Por defecto, se ejecuta una consulta para cada función FILTER utilizada.

Recuerde que cada caso de uso es único. El objetivo es simplificar las consultas MDX y al mismo tiempo aplicar filtros y selecciones óptimos.

Escenario con CASE frente a FILTER

Continuamos con la observación del mismo escenario con los resultados del uso de la funcionalidad CASE frente a FILTER.

El usuario solicita un informe que muestra el beneficio por trimestre y la SKU de producto seleccionada. Además, las SKU se agrupan en doces categorías. La categoría “Other Cola” tiene los siguientes productos de LOB asignados: Cola, Diet Cola y Shared Diet Cola.

A continuación se muestra la descripción de GUID-7198F143-54E6-4A48-9579-96624936A94D-default.jpg
.jpg​

La consulta lógica de sentencia CASE es la siguiente:

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

No hay ninguna agrupación basada en la sentencia CASE, se genera una MDX simple, y la sentencia CASE se procesa en Oracle Analytics. La MDX generada es la siguiente:

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]

La sentencia CASE se ejecuta en BI Server, y esto se puede ver por el valor de base de datos definido en “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]

Escenario de la sentencia de filtro

Como alternativa, puede utilizar un filtro en la métrica de beneficio para recuperar solo los miembros de LOB necesarios. En este escenario, debe crear 3 métricas con el filtro correspondiente aplicado.

La consulta lógica de sentencia FILTER es la siguiente:

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

En este escenario se generan tres consultas, una para cada filtro, y experimentará incidencias de rendimiento.

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]

Escenario de filtro de productos aplicado.

Un mejor enfoque es incluir la columna de producto en el informe con una única columna de medida y sin filtro. A continuación se crea un filtro que incluye los productos necesarios. Si desea agrupar los productos en diferentes categorías, se puede utilizar la sentencia CASE. En este escenario, se generará una única consulta MDX con las filas filtradas y, aunque la sentencia CASE la aplica Oracle Analytics, utilizará el subjuego de datos y no todos los registros.

Veamos otro escenarios en el que las sentencias CASE provocan incidencias de rendimiento.

Un desarrollador aplica una sentencia CASE para renombrar marcas y una petición de datos del panel de control permite a los usuarios seleccionar la marca:

A continuación se muestra la descripción de GUID-56356AA9-2AF6-4A67-8ADD-FC4F7F70306C-default.jpg
.jpg​

A continuación se muestra la descripción de GUID-E63719C8-9936-412B-8228-F20E8F048C46-default.jpg
.jpg​

Dado que la sentencia CASE no está soportada en MDX, el filtro en ‘Brand2’ no se puede aplicar en la consulta MDX. Se seleccionan todas las marcas y no se optimiza.

A continuación se muestra la descripción de GUID-6BE1F274-8257-4E31-8D42-406357A07B2A-default.jpg
.jpg​

En este escenario, le recomendamos que elimine la sentencia CASE y que renombre los miembros de la base de datos o que cree alias.

Si utiliza una base de datos multidimensional como origen de datos, puede experimentas incidencias de rendimiento en Oracle Analytics, lo que daría lugar a consultas generadas por MDX no óptimas. Mediante la modificación del diseño, puede mejorar las consultas MDX que genera Oracle Analytics. Esto puede tener un enorme impacto no solo en el rendimiento de los informes, sino también en el volumen de recursos que se utilizan en la base de datos. Tenga cuidado a la hora de utilizar funciones soportadas y no soportadas en MDX, ya que esto tiene un gran impacto en las consultas MDX generadas y el rendimiento.