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:
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. When you
include CASE
statements in report formulas, they aren't included in
the MDX. 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.
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 eachFILTER
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 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 the illustration ceal_dashboard_prompt_brand_case.jpg
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 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.