Réglage des performances des requêtes de base de données multidimensionnelle

Lorsqu'Oracle Analytics utilise une base de données multidimensionnelle comme source de données, certains éléments supplémentaires relatifs à la conception doivent être pris en compte du fait de l'ampleur de leur potentielle incidence sur les performances.

Il est important de comprendre que les solutions d'amélioration des performances liées à la conception varient en fonction du cas d'emploi. Cette rubrique ne propose pas de meilleures pratiques ni de solution universelle à appliquer systématiquement. Nous fournissons plutôt des méthodes et techniques de réglage pour vous aider à améliorer les performances des analyses et du code généré.

C'est à l'équipe de développement d'examiner les différentes options, d'analyser les journaux des requêtes Oracle Analytics et de choisir la solution la plus adaptée à votre cas d'emploi.

Cette rubrique ne traite pas des problèmes de performances causés par l'infrastructure, comme les réseaux, les navigateurs ou la présentation de rapport.

Méthodologie

Nous vous recommandons d'effectuer les tâches suivantes pour améliorer les performances. Comme prérequis pour ces tâches, il est important de comprendre la structure de requête d'expression multidimensionnelle (MDX), ainsi que les journaux des requêtes générés par Oracle Analytics. Les tâches principales sont les suivantes :

  • Simplifiez l'expression MDX générée.
  • Réduisez le nombre de requêtes MDX générées.
  • Assurez-vous que les sélections et filtres optimaux sont appliqués dans l'expression MDX.
  • Réglez les performances avec l'administrateur de base de données du côté de la base de données multidimensionnelle et vérifiez pourquoi les performances de la base de données source sont toujours insuffisantes.
  • Modifiez l'analyse en fonction des commentaires de l'administrateur de base de données.

Optimisation des étapes de sélection

Lorsque vous optimisez les étapes de sélection, vous pouvez simplifier les requêtes MDX, réduire le nombre de requêtes MDX générées et améliorer les performances.

Voici un exemple :

La description de GUID-43E6F348-B14C-40DC-8C21-DA34DAE44344-default.jpg est la suivante
.jpg​

Instructions CASE

La fonctionnalité des instructions CASE n'est pas prise en charge dans les expressions MDX et doit toujours être appliquée dans Oracle Analytics. La logique décrite ci-dessous concernant les instructions CASE est valide pour la plupart des fonctions qui ne sont pas prises en charge dans les expressions MDX (if null, etc.).

L'utilisation des instructions CASE présente des avantages et des inconvénients . Lorsque vous incluez des instructions CASE dans des formules de rapport, elles ne sont pas incluses dans l'expression MDX. La requête MDX peut donc être simplifiée et les performances améliorées. Toutefois, le filtre ne fonctionne pas aussi efficacement, ce qui signifie que la requête peut renvoyer plus d'enregistrements que nécessaire.

Comme vous pouvez le constater, chaque cas d'emploi est unique. L'objectif principal est de simplifier les requêtes MDX tout en appliquant des sélections et filtres optimaux.

L'utilisation de la fonctionnalité des instructions CASE comporte des restrictions :

  • Si l'instruction CASE ne combine pas plusieurs membres, la colonne de base utilisée dans l'instruction doit être incluse dans la requête et les vues en tant que colonne distincte masquée.
  • Si l'instruction CASE combine plusieurs membres, la colonne de base ne peut pas être incluse dans la vue sans incidence sur le niveau d'agrégation. Dans ce cas :
    • Si la règle d'agrégation de l'indicateur n'est pas Agrégation externe, la colonne de base doit être exclue de la requête.
    • Si la règle d'agrégation de l'indicateur est Agrégation externe, la colonne de base doit être incluse dans la requête et exclue de la vue. Vous devez remplacer la règle d'agrégation de l'indicateur, en passant de la règle par défaut à une règle d'agrégation interne simple (SUM, MAX, MIN). Cette opération fonctionne uniquement si la règle d'agrégation interne est utilisée pour combiner des membres et offre des résultats corrects.

Fonction FILTER

Contrairement à l'instruction CASE, la fonction FILTER peut être envoyée à la base de données pour exécution.

  • L'avantage principal de l'utilisation de la fonction FILTER dans les formules de rapport est que la sélection est appliquée dans la requête MDX, et que le volume de données calculées et extraites de la base de données est réduit.
  • L'inconvénient principal de l'utilisation de la fonction FILTER est qu'elle peut augmenter le nombre de requêtes MDX exécutées. Par défaut, une requête est exécutée pour chaque fonction FILTER utilisée.

N'oubliez pas que chaque cas d'emploi est unique. L'objectif est de simplifier les requêtes MDX tout en appliquant des sélections et filtres optimaux.

Scénario d'utilisation de CASE ou de FILTER

Poursuivons en prenant le même scénario, et en observant les résultats de l'utilisation de la fonctionnalité CASE ou de la fonctionnalité FILTER.

L'utilisateur demande un rapport indiquant le bénéfice par trimestre et l'unité de gestion des stocks de produit sélectionnée. Par ailleurs, les unités de gestion des stocks sont regroupées dans douze catégories. Les produits suivants de la gamme sont affectés à la catégorie "Other Cola" : Cola, Diet Cola et Shared Diet Cola.

La description de GUID-7198F143-54E6-4A48-9579-96624936A94D-default.jpg est la suivante
.jpg​

La requête logique avec l'instruction CASE est la suivante :

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

Aucun regroupement n'est effectué sur la base de l'instruction CASE et une expression MDX simple est générée, avec l'instruction CASE traitée par Oracle Analytics. L'expression MDX générée est la suivante :

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]

L'instruction CASE est exécutée sur le serveur BI Server, ce qui est visible avec le paramètre de base de données défini sur "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]

Scénario d'instruction FILTER

Vous pouvez également utiliser un filtre sur la mesure de bénéfice pour extraire uniquement les membres requis de la gamme. Dans ce scénario, vous créez 3 mesures avec les filtres correspondants appliqués.

La requête logique avec l'instruction FILTER est la suivante :

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

Dans ce scénario, trois requêtes sont générées (une pour chaque filtre) et vous rencontrez des problèmes de performances.

Requête 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]

]]

Requête 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]

]]

Requête 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]

Scénario d'application de filtre de produit

Une meilleure approche consiste à inclure la colonne de produit dans le rapport avec une seule colonne d'indicateur sans filtre. Ensuite, créez un filtre qui inclut les produits requis. Si vous voulez regrouper les produits dans différentes catégories, vous pouvez utiliser une instruction CASE. Dans ce scénario, une seule requête MDX est générée avec les lignes filtrées, et même si l'instruction CASE est appliquée par Oracle Analytics, elle utilise le sous-ensemble de données, et non tous les enregistrements.

Penchons-nous sur un autre scénario dans lequel les instructions CASE provoquent des problèmes de performances.

Un développeur applique une instruction CASE pour renommer les marques, et une invite de tableau de bord permet aux utilisateurs de sélectionner la marque :

La description de GUID-56356AA9-2AF6-4A67-8ADD-FC4F7F70306C-default.jpg est la suivante
.jpg​

La description de GUID-E63719C8-9936-412B-8228-F20E8F048C46-default.jpg est la suivante
.jpg​

Etant donné que l'instruction CASE n'est pas prise en charge dans l'expression MDX, le filtre sur 'Brand2' ne peut pas être appliqué dans la requête MDX. Toutes les marques sont sélectionnées, sans optimisation.

La description de GUID-6BE1F274-8257-4E31-8D42-406357A07B2A-default.jpg est la suivante
.jpg​

Dans ce scénario, nous recommandons d'enlever l'instruction CASE, et de renommer les membres dans la base de données ou de créer des alias.

Lorsque vous utilisez une base de données multidimensionnelle en tant que source de données, vous pouvez rencontrer des problèmes de performances dans Oracle Analytics, ce qui aboutit à des requêtes MDX générées non optimales. En modifiant la conception, vous pouvez améliorer les requêtes MDX générées par Oracle Analytics. L'incidence peut être immense, non seulement sur les performances du rapport, mais également sur le volume de ressources utilisées dans la base de données. Faites attention à la façon dont vous utilisez les fonctions prises en charge ou non prises en charge dans les expressions MDX car l'incidence est grande sur les requêtes MDX générées et sur les performances.