1. Visualizing Data in Oracle Analytics Server
  2. Reference
  3. Design Tips
  4. Tune Performance of Multidimensional Database Queries

Tune Performance of Multidimensional Database Queries

When you use a multidimensional database as a data source in Oracle Analytics, you may experience performance issues which result in suboptimal multidimensional expression (MDX) queries being generated.

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. How you use supported or non-supported functions greatly impacts the MDX queries generated and therefore the performance.

Because each use case is unique, your development team should 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

Oracle recommends that you do the following tasks to increase performance. It's important that you understand MDX query structure as well as the query logs that Oracle Analytics generates.

  • Simplify the MDX queries generated.
  • Reduce the number of MDX queries generated.
  • Ensure that optimal filters and selections are applied in the MDX query.
  • Performance tune with the database administrator (DBA) on the multidimensional database 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.

The following figure shows an example of a comparison of optimized and not optimized selection steps.

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 queries and must always be applied in Oracle Analytics. The logic explained in this section with regards to CASE statements is valid for most functions that aren't supported in MDX queries (if null, and so on).

There are advantages and disadvantages when using CASE statements. When you include CASE statements in report formulas, they aren't included in the MDX query. This can simplify the MDX query and improve performance. However, the tradeoff is that you can't filter as effectively which means that the query might return more records than is necessary.

The following 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 functionality, 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.

CASE vs. FILTER Example

In this example, a user requests a report that shows profit by quarter and selected product SKU. In addition, the SKUs are grouped together into 12 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's no grouping based on the CASE statement. A simple MDX query is generated, with the CASE statement processed by 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]

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]

Alternatively, you can use a filter against the profit metric to retrieve only the required LOB members. In this scenario, you create three 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 Example

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, use a CASE statement. In this scenario, a single MDX query is generated with the filtered rows and even though the CASE statement is applied by Oracle Analytics, it uses the subset of data and not all records.

Here's 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​

Because the CASE statement isn't supported in the MDX, the filter on Brand2 can't be applied in the MDX query. All brands are selected, and this isn't optimized.

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

In this type of scenario, Oracle recommends you remove the CASE statement and rename members in the database or create aliases.