1. Visualizing Data and Building Reports in Oracle Analytics Cloud
  2. Reference
  3. Design Tips
  4. Tune Performance of Multidimensional Database Queries

Tune Performance of Multidimensional Database Queries

When Oracle Analytics uses a multidimensional database as a data source, there are additional design considerations that may have a big impact on performance.

It’s important to understand that performance improvement design solutions vary depending on the use case. This topic won’t provide you with best practices or a one-size-fits-all solution that should always be applied. Instead, we offer tuning methods and techniques to help you boost the performance of your analyses and generated code.

It’s up to the development team to review the options, analyze the Oracle Analytics query logs, and select the best solution for your use case.

This topic doesn’t address performance issues caused by your infrastructure, such as networks, browsers, or report presentation.

Methodology

We recommend you complete the following tasks to increase performance. As a prerequisite to these tasks, it’s important to understand multidimensional expression (MDX) query structure as well as the query logs that Oracle Analytics generates. These are the main tasks:

  • Simplify the MDX generated.
  • Reduce the number of MDX queries generated.
  • Ensure that optimal filters and selections are applied in the MDX.
  • Performance tune with the database administrator (DBA) on the multidimensional database side and verify why the source database is still performing poorly.
  • Modify the analysis based on DBA feedback.

Selection Steps Optimization

When you optimize selection steps you can simplify the MDX queries, reduce the number of MDX queries generated, and increase performance.

Here's an example:

Description of ceal_classic_optimized.jpg follows
Description of the illustration ceal_classic_optimized.jpg​

CASE Statements

CASE statement functionality isn’t supported in MDX and must always be applied in Oracle Analytics. The logic explained below with regards to CASE statements is valid for most functions that aren’t supported in MDX (if null, and so on).

There are pros and cons when using CASE statements:
  • The main benefit of using a CASE statement in report formulas is that it can’t be included in the MDX and may help to simplify the generated MDX query and improve performance.
  • The main drawback for using a CASE statement in reports formulas is that it can’t be included in the MDX and may prevent from you from applying optimal filters in MDX queries, resulting in more records being returned for processing.

As you can see, each use case is unique. The key objective is to simplify the MDX queries and at same time apply optimal filters and selections.

There are restrictions for using CASE statement functionality:

  • If the CASE statement doesn’t combine multiple members, the base column used in the statement should be included in the query and the views as a hidden separate column.
  • If the CASE statement combines multiple members, the base column can’t be included in the view without impacting the level of aggregation. If this is the case:
    • If the aggregation rule of measure isn't External Aggregation, the base column must be excluded from the query.
    • If the aggregation rule of measure is External Aggregation, the base column must be included in the query and excluded from the view. You must change the aggregation rule of measure from the default into a simple internal aggregation rule (SUM, MAX, MIN). This works only if the internal aggregation rule is used to combine members and provides correct results.

FILTER Function

Unlike the CASE statement, the FILTER function can be shipped to the database for execution.

  • The main benefit of using the FILTER function in report formulas is that the selection is applied in the MDX query and the volume of data calculated and retrieved from the database is reduced.
  • The main drawback of using the FILTER function is that it may increase the number of MDX queries executed. By default, one query is executed for each FILTER function used.

Remember, each use case is unique. The objective is to simplify MDX queries and at same time apply optimal filters and selections.

CASE verses FILTER Scenario

Let’s continue looking at the same scenario with the results of using the CASE versus FILTER functionality.

The user requests a report that shows profit by quarter and selected product SKU. In addition, the SKUs are grouped together into twelve categories. The category “Other Cola” has the following LOB’s products assigned: Cola, Diet Cola, and Shared Diet Cola.

Description of ceal_case_statement_example.jpg follows
Description of the illustration ceal_case_statement_example.jpg​

Here's the CASE statement logical query: 

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

There is no grouping based upon the CASE statement, a simple MDX is generated, with the CASE statement processed by Oracle Analytics. Here's the MDX generated: 

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]

The CASE statement is executed on the BI Server, and this is seen by the database setting set to “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]

Filter Statement Scenario

Alternatively, you can use a filter against the profit metric to retrieve only the required LOB members. In this scenario, you create 3 metrics with the corresponding filters applied.

Here's the FILTER statement logical query: 

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

In this scenario, three queries, one for each filter are generated, and you experience performance issues.

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

]]

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

]]

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

Product Filter Applied Scenario

A better approach is to include the product column in the report with a single measure column without a filter. Then create a filter that includes the required products. If you want to group the products into different categories a CASE statement can be used. In this scenario, there will be a single MDX query generated with the filtered rows and even though the CASE statement is applied by Oracle Analytics it will use the subset of data and not all records.

Let’s look at another scenario where CASE statements cause performance issues.

A developer applies a CASE statement to rename brands, and a dashboard prompt allows users to select the brand:

Description of ceal_dashboard_prompt_brand_case.jpg follows
Description of the illustration ceal_dashboard_prompt_brand_case.jpg​

Description of ceal_sql_select_brand_case.jpg follows
Description of the illustration ceal_sql_select_brand_case.jpg​

As the CASE statement is not supported in MDX, the filter on ‘Brand2’ can’t be applied in the MDX query. All brands are selected, and this is not optimized.

Description of ceal_case_not_optimized.jpg follows
Description of the illustration ceal_case_not_optimized.jpg​

In this scenario, we recommend you remove the CASE statement and rename members in the database or create aliases.

When you use a multidimensional database as a data source, you may experience performance issues in Oracle Analytics which result in suboptimal MDX generated queries. By modifying the design, you can improve the MDX queries that Oracle Analytics generates. This can have a huge impact, not only on your report performance but also on the volume of resources used in the database. Be careful of how you utilize supported or non-supported functions in MDX, as this greatly impacts the MDX queries generated and performance.