Ajuste del rendimiento de consultas de base de datos multidimensional

Si utiliza una base de datos multidimensional como origen de datos en Oracle Analytics, puede experimentar incidencias de rendimiento, lo que causaría la generación de consultas de expresión multidimensional (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. El modo de usar las funciones soportadas o no soportadas tiene un gran impacto en las consultas MDX generadas y, por tanto, en el rendimiento.

Debido a que cada caso de uso es único, el equipo de rendimiento debe 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

Oracle le recomienda que complete las siguientes tareas para aumentar el rendimiento. Es importante que comprenda la estructura de la consulta MDX, así como los logs de consultas que genera Oracle Analytics.

  • Simplificar las consultas MDX generadas.
  • Reducir el número de consultas MDX generadas.
  • Garantizar que se apliquen filtros y selecciones óptimos en la consulta 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.

En la siguiente figura se muestra un ejemplo de una comparación de pasos de selección optimizados y no optimizados.

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 las consultas MDX y se debe aplicar siempre en Oracle Analytics. La lógica que se explica en esta sección en relación con las sentencias CASE es válida para la mayoría de las funciones que no están soportadas en las consultas MDX (if null, etc.).

Existen ventajas y desventajas cuando se utilizan sentencias CASE. Cuando incluye sentencias CASE en fórmulas de informe, estas no se incluyen en la consulta 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.

A continuación se indican las 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 funcionalidad 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.

Ejemplo de CASE frente a FILTER

En este ejemplo, un usuario solicita un informe que muestra el beneficio por trimestre y la SKU de producto seleccionada. Además, las SKU se agrupan en 12 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 consulta MDX simple, en la que la sentencia CASE la procesa 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]

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]

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 tres métricas con los filtros correspondientes aplicados.

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]

Ejemplo de filtro de producto 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, utilice la sentencia CASE. En este escenario, se genera una única consulta MDX con las filas filtradas y, aunque la sentencia CASE la aplica Oracle Analytics, utiliza el subjuego de datos y no todos los registros.

Este es otro escenario 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 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 tipo escenario, Oracle recomienda que elimine la sentencia CASE y que renombre los miembros de la base de datos o cree alias.