SYMPTOM:
When attempting to execute an MDX Cube report against Essbase, the Essbase server may shut down unexpectedly.
An example of such a report is illustrated below:
In the MDX Cube that is the source for this report, the attributes State Province and Product Category come from different hierarchies. (The attributes would be called Levels in multidimensional database terminology.) The report's MDX (MultiDimensional eXpression) shows that the qualifications are rendered as distinct sets that are combined in the SELECT and WHERE clauses.
with
-- Product Category element list (member list in MDX)
set [dim0_filter_elements] as '{[Product].[All Products].[Drink].[Beverages].[Carbonated Beverages], [Product].[All Products].[Drink].[Beverages].[Hot Beverages]}'
set [dim0_desc] as '{Generate([dim0_filter_elements], Descendants([Product].CurrentMember, [Product].[Product Category], SELF_BEFORE_AFTER))}'
set [dim0_select_members] as '{[Product].[Product Category].members}'
set [dim0_select] as 'Intersect({[dim0_desc]}, {[dim0_select_members]})'
-- Store States to display
set [dim1_select_members] as '{[Store].[Store State].members}'
-- State Province element (member) list
set [dim2_filter_elements] as '{[Customers].[All Customers].[USA].[CA], [Customers].[All Customers].[USA].[OR]}'
set [dim2_low] as '{Generate([dim2_filter_elements], Descendants([Customers].CurrentMember, [Customers].[State Province]))}'
member [Customers].[dim2_filter] as 'Sum([dim2_low])'
select {[Measures].[Unit Sales]} on columns,
non empty CROSSJOIN(hierarchize({[dim0_select]}), hierarchize({[dim1_select_members]})) on rows
from [FoodMart 2000].[Sales]
where ([Customers].[dim2_filter])
CAUSE:
This behavior is the result of a known issue in Essbase and is not specific to Strategy. If the MDX statement is pasted into any MDX execution tool, the Essbase server will shut down also.
The issue is under investigation within Strategy.
ACTION:
Currently this issue is still being reviewed for feasibility by our Technology team and it is not scoped for any upcoming scheduled Strategy release.
WORKAROUND:
One of the attribute qualifications may be redefined as a view filter qualification. In that case, the qualification would be removed from the MDX, producing a syntax that will function correctly against Essbase.
In some cases, this may alter the report results subject to the normal behavior of view filters in Strategy reports. Also, the attribute in the qualification must be present on the grid or in the Report Objects window, as shown below:
with set [dim0_filter_elements] as '{[Product].[All Products].[Drink].[Beverages].[Carbonated Beverages], [Product].[All Products].[Drink].[Beverages].[Hot Beverages]}'
set [dim0_desc] as '{Generate([dim0_filter_elements], Descendants([Product].CurrentMember, [Product].[Product Category], SELF_BEFORE_AFTER))}'
set [dim0_select_members] as '{[Product].[Product Category].members}'
set [dim0_select] as 'Intersect({[dim0_desc]}, {[dim0_select_members]})'
set [dim1_select_members] as '{[Store].[Store State].members}'
select {[Measures].[Unit Sales]} on columns,
non empty CROSSJOIN(hierarchize({[dim0_select]}), hierarchize({[dim1_select_members]})) on rows
from [FoodMart 2000].[Sales]
